Snaplogic Redshift Copy Command

[makeshwar]

Hi Team,

When I read data from a file/csv and do a bulk load, into redshift I know that at the backend it’s trying to copy the data and tries to do the bulk load.

Here…I need to know what was the full set of options the Snaplogic code used for the Redshift Copy command at its backend. Where I can get that details…

Thanks in advance.
Makesh

[mbowen · Accepted solution]

Hi @makeshwar

The COPY sql is written to the Snaplex logs which you can’t see directly. You need to file a support ticket to access them. However, it’s also written to the queries log in Redshift.

If you have access to the Redshift cluster, there is a Queries option in the left nav bar. It will display all the SQL issued by the SnapLogic Bulk snap. For Upsert, you will see a COPY, UPDATE, and then an INSERT.

Here is an example query on the Redshift side.

    COPY "public"."swat30322_update_temp_table_f96515a3_4ecb_4d70_a3b9_d5468fcbcb07" ("id", "name", "price")
    FROM 's3://swat-3032/datalake/raw/Redshift_load_temp/ccb70565-fb2c-4282-9946-39a8dd61fcaf.csv.gz' 
    CREDENTIALS '' DELIMITER '' 
    ESCAPE ACCEPTANYDATE ACCEPTINVCHARS MAXERROR 100 GZIP TRUNCATECOLUMNS
    EMPTYASNULL BLANKSASNULL 
    DATEFORMAT AS 'auto' TIMEFORMAT AS 'auto'

The CREDENTIALS clause is scrubbed, as is the DELIMITER (ex: ‘\u0001’). If you’re
using IAM roles, you’ll see them listed here (ex: aws_iam_role=…).

[makeshwar]

Thanks @mbowen This helps… !!