topic Re: Error while trying to load a new table from MySQL into Snowflake in Qlik Replicate

Error while trying to load a new table from MySQL into Snowflake

desmondchew — Tue, 29 Oct 2024 08:20:54 GMT

The source endpoint is AWS RDS MySQL 8 while target is Snowflake endpoint.

The datatype for column expected_event_date is "datetime" in table "booking_event_data".

The table failed to load due to error below. Can you please help and suggest a workaround fix?

RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized
File '7/LOAD00000001.csv.gz', line 6, character 15
Row 6, column ""booking_event_data""["expected_event_date":7]

Handling End of table 'LZ_SCHEMA'.'booking_event_data' loading failed by subtask 2 thread 1
Failed to copy data of file D:\Attunity\Replicate\data\tasks\BridgeNet\cloud\7\LOAD00000001.csv to database
Failed to load LZ_SCHEMA.booking_event_data from stage, file name: LOAD00000001.csv
RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized
File '7/LOAD00000001.csv.gz', line 6, character 15
Row 6, column ""booking_event_data""["expected_event_date":7]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: COPY INTO "LZ_SCHEMA"."booking_event_data"("id", "booking_id", "container_id", "process_event_id", "process_id", "event_id", "expected_event_date", "revised_event_date", "actual_event_date", "location", "vessel", "voyage", "created_by", "created_at", "updated_by", "updated_at", "is_revised_fix", "is_actual_fix", "is_revised_update", "is_actual_update") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 FROM '@"DEV"."LZ_LEGACYODS"."ATTREP_IS_DEV_31b7412f_d101_3a49_8e6a_f3a6e796d55f"/7/') files = ('LOAD00000001.csv.gz') force=true
RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized
File '7/LOAD00000001.csv.gz', line 6, character 15
Row 6, column ""booking_event_data""["expected_event_date":7]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: COPY INTO "LZ_SCHEMA"."booking_event_data"("id", "booking_id", "container_id", "process_event_id", "process_id", "event_id", "expected_event_date", "revised_event_date", "actual_event_date", "location", "vessel", "voyage", "created_by", "created_at", "updated_by", "updated_at", "is_revised_fix", "is_actual_fix", "is_revised_update", "is_actual_update") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 FROM '@"DEV"."LZ_LEGACYODS"."ATTREP_IS_DEV_31b7412f_d101_3a49_8e6a_f3a6e796d55f"/7/') files = ('LOAD00000001.csv.gz') force=true
RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized
File '7/LOAD00000001.csv.gz', line 6, character 15
Row 6, column ""booking_event_data""["expected_event_date":7]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: COPY INTO "LZ_SCHEMA"."booking_event_data"("id", "booking_id", "container_id", "process_event_id", "process_id", "event_id", "expected_event_date", "revised_event_date", "actual_event_date", "location", "vessel", "voyage", "created_by", "created_at", "updated_by", "updated_at", "is_revised_fix", "is_actual_fix", "is_revised_update", "is_actual_update") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 FROM '@"DEV"."LZ_LEGACYODS"."ATTREP_IS_DEV_31b7412f_d101_3a49_8e6a_f3a6e796d55f"/7/') files = ('LOAD00000001.csv.gz') force=true
RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized
File '7/LOAD00000001.csv.gz', line 6, character 15
Row 6, column ""booking_event_data""["expected_event_date":7]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: COPY INTO "LZ_SCHEMA"."booking_event_data"("id", "booking_id", "container_id", "process_event_id", "process_id", "event_id", "expected_event_date", "revised_event_date", "actual_event_date", "location", "vessel", "voyage", "created_by", "created_at", "updated_by", "updated_at", "is_revised_fix", "is_actual_fix", "is_revised_update", "is_actual_update") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 FROM '@"DEV"."LZ_LEGACYODS"."ATTREP_IS_DEV_31b7412f_d101_3a49_8e6a_f3a6e796d55f"/7/') files = ('LOAD00000001.csv.gz') force=true
RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized
File '7/LOAD00000001.csv.gz', line 6, character 15
Row 6, column ""booking_event_data""["expected_event_date":7]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: COPY INTO "LZ_SCHEMA"."booking_event_data"("id", "booking_id", "container_id", "process_event_id", "process_id", "event_id", "expected_event_date", "revised_event_date", "actual_event_date", "location", "vessel", "voyage", "created_by", "created_at", "updated_by", "updated_at", "is_revised_fix", "is_actual_fix", "is_revised_update", "is_actual_update") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 FROM '@"DEV"."LZ_LEGACYODS"."ATTREP_IS_DEV_31b7412f_d101_3a49_8e6a_f3a6e796d55f"/7/') files = ('LOAD00000001.csv.gz') force=true
RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized
File '7/LOAD00000001.csv.gz', line 6, character 15
Row 6, column ""booking_event_data""["expected_event_date":7]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: COPY INTO "LZ_SCHEMA"."booking_event_data"("id", "booking_id", "container_id", "process_event_id", "process_id", "event_id", "expected_event_date", "revised_event_date", "actual_event_date", "location", "vessel", "voyage", "created_by", "created_at", "updated_by", "updated_at", "is_revised_fix", "is_actual_fix", "is_revised_update", "is_actual_update") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 FROM '@"DEV"."LZ_LEGACYODS"."ATTREP_IS_DEV_31b7412f_d101_3a49_8e6a_f3a6e796d55f"/7/') files = ('LOAD00000001.csv.gz') force=true

Thank you.
Desmond

Re: Error while trying to load a new table from MySQL into Snowflake

john_wang — Tue, 29 Oct 2024 08:38:26 GMT

Hello @desmondchew ,

Not sure if the column expected_event_date is nullable, or it contains empty values. You may verify the corresponding columns values by check the CSV files, or query the original source table row. if it's null/empty, you may use transformation to replace it by a specific date eg 9999-12-31 or something like that.

Hope this helps.

John.

Re: Error while trying to load a new table from MySQL into Snowflake

desmondchew — Tue, 29 Oct 2024 09:29:08 GMT

I queried from the source MySQL database.

SELECT * FROM booking_event_data WHERE expected_event_date IS NULL;

it didn't return any records.

Desc the table show that the column is nullable.

Please share how to workaround by transforming?

Thank you.
Desmond

Re: Error while trying to load a new table from MySQL into Snowflake

john_wang — Tue, 29 Oct 2024 10:07:53 GMT

Hi @desmondchew ,

It's hard to tell a WA without knowing the values in the column. Would you please confirm what's the real values in this column?

thanks,

John.

Re: Error while trying to load a new table from MySQL into Snowflake

desmondchew — Wed, 30 Oct 2024 05:53:09 GMT

Hi John,

Please see attached value showing "0000-00" values with the datetime column. How can we transform them?

Thank you.
Desmond

Re: Error while trying to load a new table from MySQL into Snowflake

john_wang — Wed, 30 Oct 2024 06:51:01 GMT

Hello @desmondchew ,

There are several options:

1. In MySQL set NO_ZERO_DATE

2. In Qlik Replicate, adding a global transformation :

'0000-00-00 00:00:00' is not a valid timestamp in Snowflake. A transformation will need to be added to change this to your desired value.

Here is an example, in this case we are replacing the timestamp to '1970-01-01 00:00:00':

coalesce( CASE WHEN substr($AR_M_SOURCE_COLUMN_DATA,1,4) = '0000' THEN '1970-01-01 00:00:00' WHEN substr($AR_M_SOURCE_COLUMN_DATA,1,4) = '' THEN '1970-01-01 00:00:00' ELSE $AR_M_SOURCE_COLUMN_DATA END ,$AR_M_SOURCE_COLUMN_DATA)

Hope this helps.

John.

Re: Error while trying to load a new table from MySQL into Snowflake

desmondchew — Wed, 30 Oct 2024 07:18:29 GMT

John,

One of the table was able to process after applying the global transformation. However another table encountered error.

Handling End of table 'SCHEMA'.'booking_event_data' loading failed by subtask 1 thread 1
Failed to copy data of file D:\Attunity\Replicate\data\tasks\BridgeNet\cloud\7\LOAD00000001.csv to database
Failed to load SCHEMA.booking_event_data from stage, file name: LOAD00000001.csv
RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0001-00-00 00:00:00' is not recognized
File '7/LOAD00000001.csv.gz', line 1, character 1
Row 1, column ""booking_event_data""["id":1]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: COPY INTO "SCHEMA"."booking_event_data"("id", "booking_id", "container_id", "process_event_id", "process_id", "event_id", "expected_event_date", "revised_event_date", "actual_event_date", "location", "vessel", "voyage", "created_by", "created_at", "updated_by", "updated_at", "is_revised_fix", "is_actual_fix", "is_revised_update", "is_actual_update") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 FROM '@"DEV"."LZ_LEGACYODS"."ATTREP_IS_DEV_31b7412f_d101_3a49_8e6a_f3a6e796d55f"/7/') files = ('LOAD00000001.csv.gz') force=true
RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0001-00-00 00:00:00' is not recognized
File '7/LOAD00000001.csv.gz', line 1, character 1
Row 1, column ""booking_event_data""["id":1]

Can help please?

Desmond

Re: Error while trying to load a new table from MySQL into Snowflake

john_wang — Wed, 30 Oct 2024 07:24:30 GMT

Hi @desmondchew ,

Please add one more CASE WHEN entry to transfer year '0001' to '1970-01-01 as well.

Regards,

John.

Re: Error while trying to load a new table from MySQL into Snowflake

desmondchew — Wed, 30 Oct 2024 08:01:25 GMT

John,

I added additional condition yet still hitting error.

coalesce(
CASE WHEN substr($AR_M_SOURCE_COLUMN_DATA,1,4) = '0000'
THEN '1970-01-01 00:00:00'
WHEN substr($AR_M_SOURCE_COLUMN_DATA,1,4) = '0001'
THEN '1970-01-01 00:00:00'
WHEN substr($AR_M_SOURCE_COLUMN_DATA,1,4) = ''
THEN '1970-01-01 00:00:00'
ELSE
$AR_M_SOURCE_COLUMN_DATA
END
,$AR_M_SOURCE_COLUMN_DATA)

SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0001-00-00 00:00:00' is not recognized

Desmond

Re: Error while trying to load a new table from MySQL into Snowflake

john_wang — Wed, 30 Oct 2024 08:28:25 GMT

Hi @desmondchew ,

Looks to me the expression is good. did you try to reload the task?

thanks,

John.

Re: Error while trying to load a new table from MySQL into Snowflake

desmondchew — Wed, 30 Oct 2024 08:48:58 GMT

yes I did a reload but it still fail.

Re: Error while trying to load a new table from MySQL into Snowflake

SushilKumar — Wed, 30 Oct 2024 09:06:29 GMT

Hello Team,

Thanks for reaching out to Qlik via Qlik Community Support page. Such issue requires through analysis of Settings and logs, hence request you to reach out to technical Support via a case and provide Diagnostics package. And request you to not to upload any Log here as it can be access by larger Community member then the intended Support personal.

Please refer to Replicate https://community.qlik.com/t5/Knowledge/How-to-collect-Diagnostics-Package-from-Qlik-Replicate/ta-p/1815056

Regards,

Sushil Kumar