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

[desmondchew]

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

[john_wang]

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.

[desmondchew]

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 

[john_wang]

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.

[desmondchew]

Hi John,

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

Thank you.
Desmond

[john_wang · Accepted solution]

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.

[desmondchew]

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

[john_wang]

Hi @desmondchew ,

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

Regards,

John.

[desmondchew]

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

[john_wang]

Hi @desmondchew ,

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

thanks,

John.