community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
SOLVED

Bulk Loading in Redshift, the delimiter issue

Meteoroid

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

Alteryx
Alteryx

Hi Joao.

 

Do the records with \ in them still have the issue if you replace the \ with \\?

Meteoroid

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

Meteoroid

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';

Alteryx
Alteryx

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.

Meteoroid

Thanks Michael

Meteoroid

Thanks MichaelCh, I posted it in the Idea Center!

Alteryx
Alteryx

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,  

Lisa LePome
Customer Support Engineer -- Knowledge Management Coach
Alteryx, Inc.
Labels