Hi,
I'm loading data to Redshift via the Bulk connection. It works fine until it encounters some records with weird characters, in this case | and \.
Writing a simple copy command with DELIMITER '\t' (tab) solves the issue, but I cant specify the delimiter in the bulk Redshift output.
Have you encountered this issue?
Thanks
Joao
Solved! Go to Solution.
Hi Joao.
Do the records with \ in them still have the issue if you replace the \ with \\?
Hi Michael,
I haven't checked. I have many columns and I dont want to add a filter for each edge case but a solution that solves everything.
The COPY command with theDELIMITER with TAB solves it but there is no DELIMITER configuration for the Redshift Bulk Output.
Joao
It would be good if we can customize the COPY command bulk issues. This is my COPY command and it loads successfully, bulk doesn't.
COPY scratch.table
FROM 's3://xxxxxxxx-etl-staging/mssql/2017/'
CREDENTIALS 'aws_access_key_id=xxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxx'
GZIP
TRUNCATECOLUMNS
IGNOREHEADER AS 1
CSV QUOTE AS '"'
TIMEFORMAT AS 'YYYY-MM-DD HH:MI:SS'
ACCEPTINVCHARS AS '^'
DELIMITER '\t';
We don't support customizing the COPY command. I am not sure what is causing the issue with \. However, | is the delimiter we currently use for our COPY command. Being able to customize the delimiter is a great idea, though; you should post it in the Idea Center.
In the meantime, you could use a MultiField formula to replace all the |s (and \s) in your string fields with some other delimiter (like \t) before running your data into the Bulk Loader.
Thanks Michael
Thanks MichaelCh, I posted it in the Idea Center!
Just for future users, the error that shows up when backslashes (\) occur in the data can be as nondescript as
Output Data (6) The COPY failed with error: [Amazon][RedShift ODBC] (30) Error occurred while trying to execute a query: ERROR: Load into table 'opex' failed. Check 'stl_load_errors' system table for details.¶ Last entry in stl_load_errors: 0,