Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I use Talend to import some data to a Snowflake database.
Here's my target table:
CREATE TABLE IF NOT EXISTS public.revenues (
id bigint NOT NULL autoincrement start 1 increment 1 PRIMARY KEY,
odb_created_at timestamp without time zone NOT NULL,
payment_time timestamp without time zone NOT NULL,
count integer DEFAULT 1 NOT NULL,
ticket_type integer NOT NULL,
amount integer NOT NULL,
payment_type integer NOT NULL,
manufacturer character varying(200),
carpark_id bigint,
ipcp_productid integer,
net_amount integer,
report_type integer,
nativeid bigint
);
And here's my source from the mapping.
Here's my Snowflake output schema
Trying to import the data, I get the same error again and again, here's the log
Aug 30, 2021 2:23:18 PM net.snowflake.client.loader.ProcessQueue run
SEVERE: State: INGEST_DATA, INSERT INTO "REVENUES"("ODB_CREATED_AT","PAYMENT_TIME","COUNT","TICKET_TYPE","AMOUNT","PAYMENT_TYPE","MANUFACTURER","CARPARK_ID","IPCP_PRODUCTID","NET_AMOUNT","REPORT_TYPE","NATIVE_ID") SELECT * FROM "REVENUES_20210830_142314_557_1", SQL compilation error:
Insert value list does not match column list expecting 12 but got 11
Exception in component tDBOutput_10 (Copy_of_unibail_designa_revenue_import)
java.io.IOException: net.snowflake.client.loader.Loader$ConnectionError: State: INGEST_DATA, INSERT INTO "REVENUES"("ODB_CREATED_AT","PAYMENT_TIME","COUNT","TICKET_TYPE","AMOUNT","PAYMENT_TYPE","MANUFACTURER","CARPARK_ID","IPCP_PRODUCTID","NET_AMOUNT","REPORT_TYPE","NATIVE_ID") SELECT * FROM "REVENUES_20210830_142314_557_1", SQL compilation error:
Insert value list does not match column list expecting 12 but got 11
at org.talend.components.snowflake.runtime.SnowflakeWriter.close(SnowflakeWriter.java:375)
at org.talend.components.snowflake.runtime.SnowflakeWriter.close(SnowflakeWriter.java:55)
at analytics12.copy_of_unibail_designa_revenue_import_1_2.Copy_of_unibail_designa_revenue_import.tFileInputDelimited_1Process(Copy_of_unibail_designa_revenue_import.java:7796)
at analytics12.copy_of_unibail_designa_revenue_import_1_2.Copy_of_unibail_designa_revenue_import.runJobInTOS(Copy_of_unibail_designa_revenue_import.java:16079)
at analytics12.copy_of_unibail_designa_revenue_import_1_2.Copy_of_unibail_designa_revenue_import.main(Copy_of_unibail_designa_revenue_import.java:15637)
Caused by: net.snowflake.client.loader.Loader$ConnectionError: State: INGEST_DATA, INSERT INTO "REVENUES"("ODB_CREATED_AT","PAYMENT_TIME","COUNT","TICKET_TYPE","AMOUNT","PAYMENT_TYPE","MANUFACTURER","CARPARK_ID","IPCP_PRODUCTID","NET_AMOUNT","REPORT_TYPE","NATIVE_ID") SELECT * FROM "REVENUES_20210830_142314_557_1", SQL compilation error:
Insert value list does not match column list expecting 12 but got 11
at net.snowflake.client.loader.ProcessQueue.run(ProcessQueue.java:388)
at java.lang.Thread.run(Unknown Source)
Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
Insert value list does not match column list expecting 12 but got 11
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:152)
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:77)
at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:495)
at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:372)
at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:575)
at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:265)
at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:203)
at net.snowflake.client.core.SFStatement.execute(SFStatement.java:874)
at net.snowflake.client.jdbc.SnowflakeStatementV1.executeInternal(SnowflakeStatementV1.java:309)
at net.snowflake.client.jdbc.SnowflakeStatementV1.execute(SnowflakeStatementV1.java:373)
at net.snowflake.client.loader.ProcessQueue.run(ProcessQueue.java:336)
... 1 more
Batch entry 0 INSERT INTO "public"."logcatcher" ("moment","pid","root_pid","father_pid","project","job","context","priority","type","origin","message","code") VALUES ('2021-08-30 14:23:18.685+02','lOciR1','lOciR1','lOciR1','ANALYTICS12','Copy_of_unibail_designa_revenue_import','DEV',6,'Java Exception','tDBOutput_10','java.io.IOException:net.snowflake.client.loader.Loader$ConnectionError: State: INGEST_DATA, INSERT INTO "REVENUES"("ODB_CREATED_AT","PAYMENT_TIME","COUNT","TICKET_TYPE","AMOUNT","PAYMENT_TYPE","MANUFACTURER","CARPARK_ID","IPCP_PRODUCTID","NET_AMOUNT","REPORT_TYPE","NATIVE_ID") SELECT * FROM "REVENUES_20210830_142314_557_1", SQL compilation error:
Insert value list does not match column list expecting 12 but got 11',1) was aborted: FEHLER: Wert zu lang für Typ character varying(255) Call getNextException to see other errors in the batch.
caused by: FEHLER: Wert zu lang für Typ character varying(255)
[statistics] disconnected
-------
It says that I'm trying to import 11 columns in a table that contains 12. That is not the case as you can see on my screenshots.
In german it also says that my value is too long for character varying, but im not using any varchar.
Debug looks ok too: