- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-12-2021 01:59 AM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-12-2021 10:06 AM
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=…).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-12-2021 10:06 AM
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=…).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-13-2021 09:39 AM
Thanks @mbowen This helps… !!