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.