Value list does not match column list

amba1 — Fri, 15 Nov 2024 23:47:58 GMT

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:

topic Value list does not match column list in Talend Studio

Value list does not match column list