Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SanjeevAsher
Partner - Contributor III
Partner - Contributor III

Add new column to existing table via Qlik Replicate and Qlik Compose

There is a new column added in the source tables. How do we I get that replicated in via Qlik Replicate and then via Qlik Compose

Labels (1)
4 Replies
Brian_Jones
Employee
Employee

Hi Sanjeev,

Qlik Replicate has a DDL handling policy in the Apply Change Processing settings to perform an "alter target table" when the source table is altered. This will add the new columns from the source tables to the Replicate target. (The Replicate target is also the Compose Source database "landing zone" when you are sourcing from Replicate.)

The answer for Compose depends on whether it is a data lake or data warehouse project.

With Qlik Compose data lake projects, schema evolution was just added in the May 2021 release. (Schema evolution also existed previously in Qlik Compose for Data Lakes.) A scan for metadata changes like your added column can be initiated from the GUI or the CLI.

With Qlik Compose data warehouse projects, the new columns must be added by the warehouse developer deliberately to the Compose warehouse model. This can be done either manually in the GUI (e.g. during a development phase) or the CLI can be used automate the process later on to import changes from csv files that contain lists of added attribute domains and attributes, updated mappings, etc. 

Hope this helps. 

SanjeevAsher
Partner - Contributor III
Partner - Contributor III
Author

Hi Brian,
Thanks for the suggestions. Will try these out. We are using Qlik Compose for data warehouse.

-Sanjeev

SumitSingh
Partner - Contributor III
Partner - Contributor III

Hi Brian , 

In replicate I have kept my full load and store change processing as ON . My requirement here is to add column to existing table in the source . When adding column to the source Replicate capture the changes in the CT(change table) . In compose FOR DW , I manually add the my column in the model area and adjust my datawarehouse .

 In datawarehouse when I select my table and do the clear landing cache .when I move to the table mapping,  I couldn't found corresponding mapping for my added column in landing area as my column gets added into the staging area and  I have to map it with the landing area column present.

If i do the apply change processing setting and turn off the store change , then If I try to add column in the source table Replicate captures the adds column into the main landing table and In Compose for datawarehouse ,I can add column in model and my mapping is visible in datawarehouse for column added. But the increase my table and Database size as same number of record will be present in my landing table and in the DWH table.

 

Is there any work arround so I can add column in existing table with replicate setting as store changes and following with compose.?

 

Brian_Jones
Employee
Employee

Hi Sanjeev,

>>Is there any work around so I can add column in existing table with replicate setting as store changes and following with compose.?

Yes. In Replicate, BOTH Apply Changes and Store Changes options should be turned on for this use case.

As you found, when the Source Type is Table, the CDC ETL task source metadata is derived from the Full load ETL task. (the Compose code just changes the source code to table__ct). Having the Apply Changes option turned on with the "Alter Target Table DDL policy" in Replicate will ensure the new column will appear in the  Compose mapping.

Brian_Jones_0-1622148883851.png

You should also have the Replicate Store Changes option enabled with the Apply change to target table enabled so the new column also goes to the __ct table. 

Brian_Jones_1-1622149060924.png

In Compose you'll need to add the attribute to the model, validate and adjust the warehouse and update the mappings. (Those steps can be done either in the GUI or programmatically using the CLI. If you are using the Compose GUI, as you correctly noted, you'll need to clear landing cache to make the new column appear in the landing table)

Brian_Jones_3-1622149982015.png

 

Brian_Jones_2-1622149828341.png

You'll map the new attribute to landing in the Full Load and CDC ETL Task versions of the mapping. (After all if you have to reload the table from source at some point you'll want the new attribute mapped to the landing in both places.)

In Compose, you will still load change data from the __ct table using the CDC ETL task, NOT the full load ETL task.

I understand your concern about having a full copy of all the source data in the target full load “base” table. Some implementations use that full copy of the source data as an operational data store, but Compose doesn’t use that full load “base” landing table data unless you are running the Compose Full Load ETL task, and even then you can truncate those tables after a successful load into Compose if you wish.

Since you will be running incremental updates using the CDC ETL task, so you can manage the growth of the full load “base” landing table after the initial load of the warehouse caused by the Apply Changes setting in Replicate as a post-process routine of a successful CDC ELT task run. (New INSERTs will continue to be added to the base table.)

If you are processing changes from the CDC ETL Task, you won't need the full load “base” landing table until one or more tables in the warehouse might need to be reloaded. You’ll likely start that process by reloading the full load “base table” from Replicate before running the Full Load Compose ETL set anyway. It's a balance between storing a copy of the landing data you likely won't need for a long time and refreshing it if you do. 

I tested this by using a Post-Load ETL set, but that's one of several ways you could implement this truncate. 

Brian_Jones_4-1622151437882.png

 

Hope this helps.