Talend call seq.NEXTVAL for Snowflake database in tMAP?

amba1 — Fri, 15 Nov 2024 23:33:07 GMT

Hello,

I'm having issues with autoincrement and sequences in snowflake because of Talend.

I have a job where I constantly drop a table and recreate it. When using autoincrement, the id is never reset, so after 10 imports I'm already at id 48724, whereas with sequence, I could reset my id whenever i drop the table.

Unfortunately I cannot reset an autoincrement value.

Here's my sequence and my table.

CREATE OR REPLACE sequence dimsysteminterface_seq start = 1 increment = 1;

DROP TABLE DIMSYSTEMINTERFACE;

CREATE TABLE IF NOT EXISTS dimsysteminterface (

id INTEGER DEFAULT dimsysteminterface_seq.NEXTVAL NOT NULL PRIMARY KEY,

systeminterfaceid bigint,

definitionkey character varying(60),

name character varying(80),

defname character varying(80),

servicename character varying(255),

systemmanufacturer character varying(80),

systemname character varying(30),

systemversion character varying(15),

systemdeftype character varying(30),

status character varying(15),

scd_start timestamp without time zone,

scd_end timestamp without time zone,

scd_version integer,

scd_active boolean

);

Here's my job:

Here's my tMAP:

Here's the actual output in my database:

What I'm trying to do is to call my sequence and get the nextvalue for each new record like (SELECT dimsysteminterface_seq.NEXTVAL;).

It works perfectly with a postgres database since I can define something like this in the advanced settings:

In snowflake, I can't.

So how to achieve that?

Re: Talend call seq.NEXTVAL for Snowflake database in tMAP?

Anonymous — Fri, 05 Nov 2021 06:26:09 GMT

The tSnowflakeOutput component does not support Additional columns fields. As a workaround, you need to iterate each input row and use tSnowflakeInput to execute the below query to return the sequence id for each row, but the performance is not good for a big volume of data.

"select dimsysteminterface_seq.nextval from dual"

Regards

Shong

topic Talend call seq.NEXTVAL for Snowflake database in tMAP? in Talend Studio

Talend call seq.NEXTVAL for Snowflake database in tMAP?

Re: Talend call seq.NEXTVAL for Snowflake database in tMAP?