Call the next sequence for a Snowflake database in Talend's tMAP

user_11423137 0
Viewed 268

I'm having issues with auto increment and sequences in Snowflake because of Talend.

I have a job where I constantly drop a table and recreate it. When using auto increment, 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 auto increment value in Snowflake. I also read that it was better practice to use sequences.

Here's my sequence and my table.

CREATE OR REPLACE sequence dimsysteminterface_id_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:

JobTalend

Here's my tMAP:

tMap

Here's the actual output in my database:

DBOutput

What I'm trying to do is to call my sequence and get the next value for each new record like once I run my job. Same behavior as SELECT dimsysteminterface_seq.NEXTVAL;

It works perfectly with a Postgres database, since I can define something like this in the tdbOutput's advanced settings:

tdbOUTPUT

In snowflake, I can't.

How to achieve that? I also added Snowflake as tag, maybe I'm not seeing something. Thank you!

talend snowflake-cloud-data-platform
1 Answers

I have a job where I constantly drop a table and recreate it. When using auto increment, the ID is never reset

When table is recreated the AUTOINCREMENT/IDENTITY property IS reset to its default value:

-- to be run multiple times
CREATE OR REPLACE TABLE  dimsysteminterface COPY GRANTS (
id INTEGER AUTOINCREMENT(1,1) NOT NULL PRIMARY KEY,
systeminterfaceid bigint
/*...*/
);

INSERT INTO dimsysteminterface(systeminterfaceid) VALUES (10),(10),(10);

SELECT * FROM dimsysteminterface;
/* 
   1 10
   2 10
   3 10 
*/

EDIT:

"Resetting the sequence":

CREATE OR REPLACE SEQUENCE dimsysteminterface_seq;
CREATE OR REPLACE SEQUENCE dummy;

CREATE OR REPLACE TABLE  dimsysteminterface COPY GRANTS (
id INTEGER DEFAULT (dimsysteminterface_seq.NEXTVAL)  NOT NULL PRIMARY KEY,
systeminterfaceid bigint
/*...*/
);

INSERT INTO dimsysteminterface(systeminterfaceid) VALUES (10),(10),(10);

SELECT * FROM dimsysteminterface;

-- reset
ALTER TABLE dimsysteminterface ALTER COLUMN id SET DEFAULT dummy.NEXTVAL;

CREATE OR REPLACE SEQUENCE dimsysteminterface_seq;

ALTER TABLE dimsysteminterface ALTER COLUMN id 
                               SET DEFAULT dimsysteminterface_seq.NEXTVAL;
user_50708790
Related