Task failing with clob column

[desmondchew]

We have a table EX92. The column MSGTEXT is a CLOB. The source endpoint is MySQL 8 and target endpoint is Snowflake AWS.

Do you know how to fix this error? I have tried numerous time to reload but kept on failing.

Table 'DB'.'EX92' (subtask 12 thread 1) is suspended. Failed (retcode -1) to execute statement: COPY INTO "LZ_SCHEMA"."EX92"("COL1","COL2","COL3", "MSGTEXT") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9 FROM '@"PROD"."LZ_LEGACY"."ATTREP_IS_PROD_08634774_a961_0b47_abb5_3c4361aa4a27"/3/12/') files = ('LOAD00000001.csv.gz') force=true; RetCode: SQL_ERROR SqlState: 22000 NativeError: 100078 Message: String 'HEADER
Print Date: Jan 06, 2020 06:13:45 AM

DBK304H1 - Header Record
ORDER NUMBER :' is too long and would be truncated
File '3/12/LOAD00000001.csv.gz', line 5988429, character 82
Row 52715, column "EX92"["MSGTEXT":9]
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"

[sureshkumar]

Hello @desmondchew 
Compare column "EX92" datatype length at the MYSQL Source and at the Snowflake target end.

You can also reproduce the issue with SOURCE_UNLOAD and TARGET_LOAD  set to VERBOSE and check for what reason the task is failing.

And also enable internal parameters keepCSVFiles and keepErrorFiles to check how the data is replicating.

Regards,

Suresh

[desmondchew]

It's happening in the CONO92 column which is a primary key. Transformation screen says it uses WSTRING(4).

ATTREP_IS_PROD_e402d77b_6838_b648_825e_2cb5b5669bf6"/1/16/') files = ('LOAD00000001.csv.gz') force=true; RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column
File '1/16/LOAD00000001.csv.gz', line 1, character 1
Row 1, column "EX92"["CONO92":1]

[sureshkumar]

Hello @desmondchew 

"NULL result in a non-nullable column" issue

please add the following Internal parameter to the snowflake endpoint

emptyFieldAsNull

Regards,

Suresh

[desmondchew]

We are already using "emptyFieldAsNull" over at Snowflake internal parameter.

[sureshkumar]

Hello @desmondchew 

You still facing the issue even after enabling the internal parameter then kindly check the Constraints defined in both Source and target DB.

OR You can create a support ticket with us to understand the issue.

Regards,
Suresh

[desmondchew]

The target endpoing is Snowflake. Could it be Qlik replicate doesn't support replicating CLOB into Snowflake?

[sureshkumar]

Hello @desmondchew 

Qlik Replicate does support replicating CLOB column into Snowflake. To understand the issue better please open a support ticket with us.

Regards,

Suresh

[john_wang]

>> It's happening in the CONO92 column which is a primary key. Transformation screen says it uses >> WSTRING(4).

>> ATTREP_IS_PROD_e402d77b_6838_b648_825e_2cb5b5669bf6"/1/16/') files = ('LOAD00000001.csv.gz') force=true; RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column
File '1/16/LOAD00000001.csv.gz', line 1, character 1
Row 1, column "EX92"["CONO92":1]

IN target table the column EX92 is defined as NON-NULLABLE however the value sending to it is NULL, so you may use table level, or global level transformation to convert the NULL to a single space, for example:

ifnull($EX92,' ')

there is a space in the single quotes.

Regards,

John.