Adding an identity Column

[nabeelaslam1994]

Hello Team,

Is there a way to add an identity column in qlik replicate? A target table will have new column and have 1,2,3,4 Row number generated by Qlik?

[Gerald_U]

Hello, 

When the source table contains an identity column, Replicate does not create the identity column on the target table. In this case, the table will need to be created manually on the target endpoint.

https://help.qlik.com/en-US/replicate/November2022/Content/Replicate/Main/Introduction/Limitations.h...

Regards,

Gerald 

[john_wang · Accepted solution]

Hello @nabeelaslam1994 ,

Yes, it's easy to do in Replicate - we can utilize the target side database itself auto-increase column properties. A sample :

1- In target side table, add an additional auto-increase column (in my sample it's "autoinc" in MySQL) and it's type is AUTO_INCREMENT

CREATE TABLE `scott`.`kitautoinc` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`autoinc` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`autoinc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

certainly the column type can be "identity(1,1)" in SQL Server, "generated by default as identity (start with 1 increment by 1)" in Oracle, "SERIAL" in PostgreSQL . Depends on the database type, you may refer to the corresponding database docs for exact syntax.

2- In Replicate task setting, set "If target table already exists" to "TRUNCATE before loading" (the default setting is "DROP and CREATE table")

3- In Replicate task table design, do not include the column, so far the target database will fill in the auto-increase column's values automatically, during both Full Load and CDC stage

4- In target side table the "autoinc" column values are the identify values

However please take note that the identity values maybe not continuous after some operations eg rows deletion  etc, that's the database specific behavior.

Hope this helps.

Regards,

John.

[nabeelaslam1994]

Hello

can we perform this auto-increment from Qlik replicate rather than creating a column in target database?

Nabeel

[john_wang]

Hello @nabeelaslam1994 ,

I'm afraid I did not get your question very well. if there are 2 columns in your source app (eg "id" and "name") then we need an additional column (eg "autoinc" in above sample) to store the identity column values.

If you are meaning the 'hidden' column eg 'rownum' in Oracle, then it's irrelevant to Replicate task at all. You may use it straightly in your query while consuming the target database data. 

Regards,

John.

[nabeelaslam1994]

Basically I am trying to see if there a setting in Qlik replicate that i can use to add auto increment feature rather than adding it on the target database(create script).

[john_wang]

Hello @nabeelaslam1994 ,

Well, maybe it's much easier for us if you can let me know your target database type, and if you wan to add such a column in all tables?

Regards,

John.

[nabeelaslam1994]

It is snowflake. Yes to all the tables.

[john_wang · Accepted solution]

Hello @nabeelaslam1994 ,

Please add an internal parameter in Snowflake target endpoint, the parameter name is 

$info.query_syntax.create_table

and set its value to

CREATE ${TABLE_TYPE} TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ( ${COLUMN_LIST} , autoinc integer NOT NULL AUTOINCREMENT (1,1) primary key)

where the additional column is "autoinc" in my above sample. please tune the SQL by following Snowflake create table syntax if you want to get more detailed control.

Regards,

John.

[lacdsil]

Thanks for posting it here. Works perfectly.