Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Bulk Loading in Redshift, the delimiter issue

joaocorreia
6 - 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

7 REPLIES 7
MichaelCh
Alteryx
Alteryx

Hi Joao.

 

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

joaocorreia
6 - 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

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

MichaelCh
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.

joaocorreia
6 - Meteoroid

Thanks Michael

joaocorreia
6 - Meteoroid

Thanks MichaelCh, I posted it in the Idea Center!

lepome
Alteryx Alumni (Retired)

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
Principal Support Engineer -- Knowledge Management Coach
Alteryx, Inc.
Labels