topic Re: Add new column with Primarykey information Databricks Lakehouse(delta) in Qlik Replicate

Add new column with Primarykey information Databricks Lakehouse(delta)

toni_lajsner — Wed, 28 Feb 2024 15:03:27 GMT

We are using Databricks Lakehouse (Delta) as our target destination. As we have multiple sources, I created global roles that provide us with both 'createdtime' and 'updatedtime', along with a 'softdelete' flag. However, I cannot find a way to add primary key information to a new column.

Is it possible to do this? Additionally, we also use Azure Data Lake Storage (ADLS) as a target, where we have the ability to create metadata files that include primary keys. The idea now is to mirror that ability directly to a table in Databricks Lakehouse.

Re: Add new column with Primarykey information Databricks Lakehouse(delta)

john_wang — Thu, 29 Feb 2024 05:37:16 GMT

Hello @toni_lajsner ,

Thanks for reaching out to Qlik Community!

... with both 'createdtime' and 'updatedtime', along with a 'softdelete' flag. However, I cannot find a way to add primary key information to a new column.

Are you intend to make the 'createdtime' and/or 'updatedtime' as PK ? or another new column? any helpful information is welcome.

Is it possible to do this? Additionally, we also use Azure Data Lake Storage (ADLS) as a target, where we have the ability to create metadata files that include primary keys. The idea now is to mirror that ability directly to a table in Databricks Lakehouse.

Not sure what's the source DB type and if the source tables have PK (or Unique Index) or not? We'd like to confirm for you.

Thanks,

John.

Re: Add new column with Primarykey information Databricks Lakehouse(delta)

toni_lajsner — Thu, 29 Feb 2024 07:16:07 GMT

Createdtime and updatedtime are columns that I am adding to every table as part of our own processes after data lands on Databricks Delta. They are not and will not be used as part of the primary key.

My question was only regarding a new column that will inherit information from the source and give us a hint of which columns the primary key is created from. The metadata created while using ADLS provides us with "primaryKeyPos":1,"primaryKeyPos":2 etc.
So idea is to create an column that will give us same information ?

Re: Add new column with Primarykey information Databricks Lakehouse(delta)

john_wang — Thu, 29 Feb 2024 07:51:44 GMT

Hello @toni_lajsner ,

Thanks for the information.

In general Qlik Replicate will replicate below index information from source database to target database (by order):

1- Primary Key (if the source table has PK. The other Unique Indexes and Indexes will be ignored)

2- Unique Index (if the source table has NO PK but only one single UI)

3- The first Unique Index (in alphabetical name order, if the source table has NO PK but multiple UIs)

4- The desired column(s) as PK if you change the PK definition in table transformation, or in case the source table has no PK nor UI and you want some column(s) as PK in target side

Below is a sample while Qlik Replicate creating 2 columns (EMPLOYEE_ID & PERSON_ID) as PK in Databricks Delta table MY_TRANSACTION_TESTTABLE1:

ALTER TABLE `default`.`MY_TRANSACTION_TESTTABLE1` ADD CONSTRAINT `default_MY_TRANSACTION_TESTTABLE1_1709129364786094_PK` PRIMARY KEY (`EMPLOYEE_ID`, `PERSON_ID`) DISABLE NOVALIDATE

Feel free to let me know if you need any additional information.

John.

Re: Add new column with Primarykey information Databricks Lakehouse(delta)

toni_lajsner — Thu, 29 Feb 2024 09:09:48 GMT

Hi again,

We did not receive an 'alter table' command. I was looking at the logs and only noticed 'create or replace table' plus 'copy into' initialization.

Do we need to check in some other setting in qlik replicate

Re: Add new column with Primarykey information Databricks Lakehouse(delta)

john_wang — Thu, 29 Feb 2024 09:16:52 GMT

Hello @toni_lajsner ,

Please set TARGET_LOAD to Trace or Verbose mode then rerun the task again you may get it - please make sure the target table does not exist before the task running, or the Target Table Preparation option is set to "DROP and CREATE table".

Regards,

John.

Re: Add new column with Primarykey information Databricks Lakehouse(delta)

toni_lajsner — Thu, 29 Feb 2024 10:14:48 GMT

Hi @john_wang
I did all the steps , also using verbose , but we are not getting ALTER TABLE , only create or replace and copy into

Re: Add new column with Primarykey information Databricks Lakehouse(delta)

john_wang — Thu, 29 Feb 2024 10:20:03 GMT

Hello @toni_lajsner ,

Would you please open a support ticket with below information:

1- Set SOURCE_UNLOAD & TARGET_LOAD/TARGET_APPLY to Verbose mode, Collect the Diag Packages and attach it to case;

2- Please provide the source table creation DDL include the PK/Unique Index information

We'd like investigate further for you.

Regards,

John.

Re: Add new column with Primarykey information Databricks Lakehouse(delta)

toni_lajsner — Fri, 01 Mar 2024 07:24:48 GMT

Hi John , i have now created a ticket 00148242 hope you can investigate the issue further

/Toni

Re: Add new column with Primarykey information Databricks Lakehouse(delta)

DesmondWOO — Fri, 01 Mar 2024 07:31:25 GMT

Hi @toni_lajsner ,

Thanks for creating a ticket. Support team will handle it.

Regards,
Desmond