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

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

2 Replies

  • mbowen's avatar
    mbowen
    Employee
    5 years ago
    Copy LinkReport

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