topic Re: Task failing with clob column in Qlik Replicate

Task failing with clob column

desmondchew — Mon, 01 Jul 2024 09:16:35 GMT

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"

Re: Task failing with clob column

sureshkumar — Mon, 01 Jul 2024 09:21:25 GMT

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

Re: Task failing with clob column

desmondchew — Mon, 01 Jul 2024 12:20:49 GMT

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]

Re: Task failing with clob column

sureshkumar — Wed, 03 Jul 2024 11:02:58 GMT

Hello @desmondchew

"NULL result in a non-nullable column" issue

please add the following Internal parameter to the snowflake endpoint

emptyFieldAsNull

Regards,

Suresh

Re: Task failing with clob column

desmondchew — Wed, 03 Jul 2024 11:12:24 GMT

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

Re: Task failing with clob column

sureshkumar — Thu, 04 Jul 2024 17:55:09 GMT

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

Re: Task failing with clob column

desmondchew — Thu, 08 Aug 2024 15:49:47 GMT

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

Re: Task failing with clob column

sureshkumar — Fri, 09 Aug 2024 02:05:28 GMT

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

Re: Task failing with clob column

john_wang — Fri, 09 Aug 2024 02:51:32 GMT

>> 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.