Talend job does not commit snowflake data!

[amba1]

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.

1. SHOW GRANTS TO USER identifier('"AMBA"'); -- my user

2. SELECT * FROM EVENTS

3. SELECT CURRENT_DATABASE(), CURRENT_SCHEMA()

4. alter session /* JDBC:SnowflakeConnectionV1.setAutoCommit*/ set autocommit=false

5. show /* JDBC:DatabaseMetaData.getPrimaryKeys() */ primary keys in table "ANALYTICS"."PUBLIC"."EVENTS"

6. show /* JDBC:DatabaseMetaData.getColumns() */ columns in table "ANALYTICS"."PUBLIC"."EVENTS"

7. 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

8. show columns in "EVENTS"

9. CREATE TEMPORARY TABLE "EVENTS_20210826_185008_230_1" LIKE "EVENTS"

10. 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)
11. 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
    1. 

12. 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"

13. RM '@~/EVENTS/INSERT/20210826_185008_230_NLF1NE_1'

14. commit

15. 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.

BR

Amba

[Anonymous]

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.

[amba1]

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?

[Anonymous]

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.

[amba1 · Accepted solution]

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.

[Anonymous]

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.

[amba1]

You're welcome. Your hint made me realize!