Bulk Loading in Redshift, the delimiter issue
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Joao.
Do the records with \ in them still have the issue if you replace the \ with \\?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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';
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks Michael
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks MichaelCh, I posted it in the Idea Center!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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,
Principal Support Engineer -- Knowledge Management Coach
Alteryx, Inc.
![](https://www.thekcsacademy.net/secure/KCS_v6_Fundamentals_Logos/KCSv6Fundamentals_100x100.jpg)
![](/skins/images/5A278AAD67B5F68761E04A0A1AFFBB3D/responsive_peak/images/icon_anonymous_message.png)