topic Re: Talend job does not commit snowflake data! in Talend Studio

Talend job does not commit snowflake data!

amba1 — Fri, 15 Nov 2024 23:48:34 GMT

Hi everyone!

I'm having an Issue with Talend trying to commit to a snowflake database.

Here's my job:

As you can see, the job works fine and does not throw any error. It should be writing 9.6k rows to the snowflake table named EVENTS. But that does not happen, heres what I see on my snowflake server, read from bottom to top:

Here's a description step by step of what happens in Snowflake when I run my Talend job if you can't read from the screenshot.

SHOW GRANTS TO USER identifier('"AMBA"'); -- my user
SELECT * FROM EVENTS
SELECT CURRENT_DATABASE(), CURRENT_SCHEMA()
alter session /* JDBC:SnowflakeConnectionV1.setAutoCommit*/ set autocommit=false
show /* JDBC:DatabaseMetaData.getPrimaryKeys() */ primary keys in table "ANALYTICS"."PUBLIC"."EVENTS"
show /* JDBC:DatabaseMetaData.getColumns() */ columns in table "ANALYTICS"."PUBLIC"."EVENTS"
PUT 'file://C:\\Users\\amba\\AppData\\Local\\Temp\\snowflake\\stage\\EVENTS\\INSERT\\20210826_185008_230_NLF1NE_1\\stream_20210826_185008_2300.gz' '@~/EVENTS/INSERT/20210826_185008_230_NLF1NE_1' parallel=10 overwrite=true auto_compress=false SOURCE_COMPRESSION=gzip
show columns in "EVENTS"
CREATE TEMPORARY TABLE "EVENTS_20210826_185008_230_1" LIKE "EVENTS"
COPY INTO "EVENTS_20210826_185008_230_1" FROM '@~/EVENTS/INSERT/20210826_185008_230_NLF1NE_1' on_error='CONTINUE' file_format=( field_optionally_enclosed_by='"' empty_field_as_null=true)
COPY INTO "EVENTS_20210826_185008_230_1" FROM '@~/EVENTS/INSERT/20210826_185008_230_NLF1NE_1' validation_mode='return_all_errors' file_format=(field_optionally_enclosed_by='"'empty_field_as_null=true) -- HERE I CAN SEE MY 9.6K ROWS
INSERT INTO "EVENTS"("ID","ODB_CREATED_AT","EVENT_TIME","DEVICE_TYPE","EVENT_TYPE","TICKET_TYPE","CARD_NR","COUNT","MANUFACTURER","CARPARK_ID") SELECT * FROM "EVENTS_20210826_185008_230_1"
RM '@~/EVENTS/INSERT/20210826_185008_230_NLF1NE_1'
commit
commit

Understanding the steps here, the data should be copied from the temp table to the event table, but that just does not happen. My Talend job seems to be fine, the data arrives to snowflake, but is never copied from that temp table.

Amba

Re: Talend job does not commit snowflake data!

Anonymous — Thu, 26 Aug 2021 23:16:43 GMT

Can you add a tLogRow to the "reject" row coming out of the tDBOutput_1 component and run it? I suspect that you will see errors there.

Re: Talend job does not commit snowflake data!

amba1 — Thu, 26 Aug 2021 23:26:20 GMT

Thanks for your answer, here's the output of the reject:

And here's the normal output:

My table Schema in Talend are the same as in Snowflake. On the first screenshot, I see that all of my rows are rejected, but when I connect a tlogrow to my tmap, it does work wihtout crashing for inserting nulls in a non-nullable column.

The error would mean that one of the column in snowflake is not nullable but I try to insert null values in it. That cannot be since they have the same schema as in talend?

Re: Talend job does not commit snowflake data!

Anonymous — Fri, 27 Aug 2021 00:29:47 GMT

What you are seeing is an error from Snowflake and not from Talend. So something is wrong with the insert statement being used. I notice that your id is always 0. Are you supplying that? Is the ID configured as auto-increment? If so, have you tried removing it from the schema of the tDBOutput_1?

I notice the Snowflake error 100072. This does seem to point to an auto-increment primary key if you check the Snowflake documentation/comments.

Re: Talend job does not commit snowflake data!

amba1 — Fri, 27 Aug 2021 08:54:19 GMT

Here's the reason and solution.

I tried to import the id as a primary key with Talend to Snowflake. Snowflake does not allow you to utilize and id column insert, since those are generated automatically as we define it while creating the table.

Example:

CREATE TABLE IF NOT EXISTS public.events (

id bigint NOT NULL autoincrement start 1 increment 1 PRIMARY KEY,

odb_created_at timestamp without time zone NOT NULL,

event_time timestamp without time zone NOT NULL,

device_type integer NOT NULL,

event_type integer NOT NULL,

ticket_type integer NOT NULL,

card_nr character varying(100),

count integer DEFAULT 1 NOT NULL,

manufacturer character varying(200),

carpark_id bigint

I had to remove my ID inserts generated by Talend and let Snowflake do the work.

The reason the NOT NULL error code occurred was due to the fact id in SQL was set to NOT NULL(of course because it is an ID). DO NOT USE IDS WITH SNOWFLAKE.

Solution: Use autoincrement in Snowflake directly or use sequences with no autoincrement.

Re: Talend job does not commit snowflake data!

Anonymous — Fri, 27 Aug 2021 09:18:34 GMT

This aligns with what I had read about that error with Snowflake. Thanks @Not defined Not defined for coming back and writing up the solution.

Re: Talend job does not commit snowflake data!

amba1 — Sat, 28 Aug 2021 09:34:32 GMT

You're welcome. Your hint made me realize!