I have to import a csv file in redshift from s3. One of the columns in the table is JSON format.
While using COPY command, I get the following error -
Invalid timestamp format or value [YYYY-MM-DD HH24:MI:SS]
The command used is -
COPY api_log FROM 's3://X/Y' credentials 'aws_access_key_id=;aws_secret_access_key=' delimiter ',' maxerror as 250 TRUNCATECOLUMNS;
COMMIT;
Sample data is -
c1 c2 c3
X Y {a:b,c:d}
If you add quotes to the json and REMOVEQUOTES to the copy then it works.
Put test file in s3:
echo x,y,'"{a:b,c:d}"' | aws s3 cp - s3://[bucket]/json/test_file.csv
Create table:
CREATE TABLE IF NOT EXISTS api_log
(c1 varchar,
c2 varchar,
json_colum varchar(65535));
Load:
COPY api_log
FROM 's3://[bucket]/json/'
CREDENTIALS 'aws_access_key_id=;aws_secret_access_key='
delimiter ','
maxerror as 250
TRUNCATECOLUMNS
REMOVEQUOTES;
Or, if you don't like quotes, you can use tabs or some other character that will not be in the data. E.g.:
echo -e "x\ty\t{a:b,c:d}" | aws s3 cp - s3://[bucket]/json/test_file.csv
Then:
COPY api_log
FROM 's3://[bucket]/json/'
CREDENTIALS 'aws_access_key_id=;aws_secret_access_key='
delimiter '\t'
maxerror as 250
TRUNCATECOLUMNS
REMOVEQUOTES;
Either way, you get:
select * from api_log;
c1 | c2 | json_colum
----+----+------------
x | y | {a:b,c:d}
