topic Re: Function Columns as Key in Qlik Replicate

Function Columns as Key

pguddera21 — Fri, 22 Nov 2024 20:32:31 GMT

Using Nov 2022 Qlik Replicate Version from Oracle 19c to Snowflake on AWS.

We are taking data from an application table and creating a hash on the concatenation of the key columns eg:

ifnull(colA,'')||ifnull(colB,'')||ifnull(colC,'') for all key columns and then set the hash of this as our primary key in snowflake (Target). However, I see that when a record gets updated, QLIK tries to set the target key column as null. This calls a not null constraint on our Snowflake DDL.

Our source has the same 3 columns as a unique index, but we hash and concat them into our solo target key.

Is there any way around having QLIK temporarily set the column to NULL or should we remove the not null constraint on our target table? The exception table is showing that is it updating our hash_key to null where hash_key = theoldvalue.

Re: Function Columns as Key

john_wang — Sat, 23 Nov 2024 03:23:26 GMT

Hello @pguddera21 ,

Thanks for reaching out to Qlik Community!

Please check the table supplemental logging to see if the 3 columns or all columns are added. Or you try to run:

ALTER TABLE <tableName> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Re-run the Change again to see if it works.

Good luck,

John.

Re: Function Columns as Key

pguddera21 — Thu, 05 Dec 2024 15:43:44 GMT

I see our table in oracle has all columns enabled for supplemental logging. In our CT table we see the update but even the before/update record in the CT table have that key column populated

Re: Function Columns as Key

john_wang — Fri, 06 Dec 2024 01:23:45 GMT

Hello @pguddera21 ,

Thanks for the update. Seems I did not get it well, would you please share the table creation DDL and the UPDATE sample SQL? We'd like to confirm the behavior for you.

Regards,

John.

Re: Function Columns as Key

pguddera21 — Fri, 06 Dec 2024 13:26:27 GMT

Hi John,

The Snowflake (Target DDL) has only one NOT NULL column:

QLIK_BUSINESS_KEY_HASH VARCHAR(256) NOT NULL,
constraint TABLE_A primary key (QLIK_BUSINESS_KEY_HASH);

In the exceptions table this is the query being run:
UPDATE "SCHEMA_A"."TABLE_A" SET "QLIK_BUSINESS_KEY_HASH"=NULL WHERE "QLIK_BUSINESS_KEY_HASH"='C2E054A0ACCA352D0C909457AE4297D0A7DA86A1A712A562FE2EA259BED99FE2'

Also when I look into the __ct table for these records I see an Update -> Beforeimage -> Update -> Beforeimage

Re: Function Columns as Key

john_wang — Fri, 06 Dec 2024 13:38:53 GMT

Hello @pguddera21 ,

Sorry I still did not get it completely. Would you please share the Oracle source side table creation DDL and a sample UPDATE SQL statement. Or you may open a support ticket with above information and also the task Diagnostics Package, Our support team will be more than happy to assist you.

Thanks,

John.

Re: Function Columns as Key

Heinvandenheuvel — Sat, 07 Dec 2024 03:04:49 GMT

>>> Function Columns as Key

Please explain. Is the source column a "virtual column" (calculated with an expression) ?

>>> However, I see that when a record gets updated, QLIK tries to set the target key column as null.

Please provide more details. Does initial load work (I suspect YES). Are INSERT correctly replicated?

Anyways, it seems to me this is a documented restriction:

"Qlik Replicate Setup and User Guide
Qlik ReplicateTM
November 2023"

---> Limitations when using Replicate Log Reader to access the redo logs

---------> "Virtual columns are not supported. "

Hein.