Forum Discussion

makeshwar's avatar
makeshwar
New Contributor II
5 years ago
Solved

Snaplogic Redshift Copy Command

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...
aws
redshift
s3
  • mbowen's avatar
    mbowen
    5 years ago

    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=…).

mbowen's avatar
mbowen
Employee
5 years ago
Copy Link

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=…).