Replicating data to columnstore table (from row store table)

[quan_phan_insightsoftware]

Qlik replicate supports 'create columnstore tables' option when target is SingleStore(in advanced connection properties). The same option was not available for target MS SQL Server - is there some means of achieving this ?

The objective is to replicate data from row store tables on SQL Server/Oracle/DB2 to column store table in SqlServer ie tables with clustered, column store index on sql Server.

[john_wang · Accepted solution]

Hello @quan_phan_insightsoftware ,

Thanks for you reaching out!

1. In MySQL alike DBs, or in SingleStore, create the columnstore table syntax is fixed, it's "engine=columnstore", Replicate GUI provides the option in the target endpoint. However in SQL Server database, there are many different CREATE [CLUSTERED] COLUMNSTORE INDEX scenarios, and the syntax between the different scenarios are different too.

2. In current Replicate versions, Replicate GUI does not provide such options for MS SQL Server target endpoint. The workaround is create the COLUMNSTORE INDEX table manually prior to the task startup, and set task setting to "TRUNCATE before loading":

    I think this is the best option up to today.

3. (Optional) If the target side create COLUMNSTORE INDEX syntax is fixed (or the map with source side PK, or given columns are fixed) then we may custom the create primary key syntax to archive that. Please open support ticket and involve PS team. However it's some hard, you could see below (4).

4. If you like, you may raise a Feature Request in Qlik Ideation , PM team will review the requests.

Hope this helps.

Regards,

John.

[quan_phan_insightsoftware]

Thanks for the explanation.

Regards,

Sumeet