failed to COPY data into redshift

Seven Stars

failed to COPY data into redshift

Hi
I use tRedshiftOutputBulkExec_1 to load data into redshift. Firstly the data is loaded into S3 bucket. The log shows COPY is successful. 
2016-03-22 16:13:51,395 - lab_one.transfer_bet_settlement_5_0.transfer_bet_settlement -6358 INFO   - tRedshiftOutputBulkExec_1_tROB - Done.
2016-03-22 16:13:51,395 - lab_one.transfer_bet_settlement_5_0.transfer_bet_settlement -6358 INFO   - tRedshiftOutputBulkExec_1_tRBE - Start to work.
2016-03-22 16:13:51,395 - lab_one.transfer_bet_settlement_5_0.transfer_bet_settlement -6358 DEBUG  - tRedshiftOutputBulkExec_1_tRBE - Parameters:USE_EXISTING_CONNECTION = true | CONNECTION = tRedshiftConnection_3 | TABLE_ACTION = CLEAR | ACCESS_KEY = "" | SECRET_KEY =  | BUCKET = "bucket_name" | KEY = "filename.csv" | IMPORT_TYPE = DELIMITED_OR_CSV | FIELDSEPARATOR = ',' | TEXT_ENCLOSURE = DOUBLE_QUOTE | COMPRESS = false | ENCRYPT = false | ENCODING = "UTF8" | DATEFORMAT = NONE | TIMEFORMAT = NONE | OTHER_CONFIGURATION = [] |
2016-03-22 16:13:51,399 - lab_one.transfer_bet_settlement_5_0.transfer_bet_settlement -6362 INFO   - tRedshiftOutputBulkExec_1_tRBE - Uses an existing connection with username 'xxxxx'. Connection URL: jdbcSmiley Tongueostgresql://url:5439/db_name.
2016-03-22 16:13:51,404 - lab_one.transfer_bet_settlement_5_0.transfer_bet_settlement -6367 INFO   - tRedshiftOutputBulkExec_1_tRBE - Clearing table "schema_name"."table_name".
2016-03-22 16:13:51,444 - lab_one.transfer_bet_settlement_5_0.transfer_bet_settlement -6407 INFO   - tRedshiftOutputBulkExec_1_tRBE- Clear table "schema_name"."table_name" has succeeded.
2016-03-22 16:13:52,046 - lab_one.transfer_bet_settlement_5_0.transfer_bet_settlement -7009 INFO   - tRedshiftOutputBulkExec_1_tRBE - Finish loading data to table : schema_name.table_name.
2016-03-22 16:13:52,046 - lab_one.transfer_bet_settlement_5_0.transfer_bet_settlement -7009 INFO   - tRedshiftOutputBulkExec_1_tRBE - Done.
2016-03-22 16:13:52,047 - lab_one.transfer_bet_settlement_5_0.transfer_bet_settlement -7010 INFO   - TalendJob: 'transfer_bet_settlement' - Done.
However, 
* Nothing is loaded into the table in redshift.
* the QUERY is flagged as terminated/ABORTED in redshift.
* This is the query sent from talend.
 COPY schema_name.table_name (clname1, clname2,clname3) FROM 's3://bucket_name/filename.csv' credentials '' DELIMITER ',' CSV QUOTE '"' ENCODING UTF8
* If I run the exact same COPY command manually by login as the same user into redshift, it works fine.
I guess somehow talend stopped the session. I already turn on TRACE in log4j. How can I troubleshoot from here?
Thanks,
Bin
Seven Stars

Re: failed to COPY data into redshift

I found the issue:
* tRedshiftConnection_3 didn't have auto-commit on.
* tRedshiftOutputBulkExec didn't commit. 
More detail is in

If the SQL client’s default behaviour is to disable autocommit, you can resolve the issue in either of these ways:• Enable autocommit for the SQL client – This is not always an appropriate solution, but it immediately addresses the issue without requiring modification of existing SQL commands. In some cases, however, such as when performing multiple ad hoc data loads, this may be the most practical solution.• Explicitly commit the transaction after the COPY command with the SQL statements COMMIT or END. If the command is executed programmatically, the command syntax should be similar to the Python connection class conn.commit() statement as described in the Psycopg 2.6 documentation.I resolve it by option 1: turn autocommit on in tRedshiftConnection_3. However, I normally want to control the commit. I cannot find a method in tRedshiftOutputBulkExec to control commit behaviour. Is it possible? Thanks,
Seven Stars

Re: failed to COPY data into redshift

Found the solution:
Add tRedshiftCommit in the end.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Talend Integration with Databricks

Take a look at this video about Talend Integration with Databricks

Watch Now