topic Every column is NULL when inserting to Snowflake in Talend Studio

Every column is NULL when inserting to Snowflake

Papademuchos — Fri, 15 Nov 2024 21:49:25 GMT

I have a job that passes records to a Joblet. The Joblet makes a connection to Snowflake and then tDBOutput uses that connection to insert to a table. However, every column is inserted with a NULL value instead of the actual value. I've seen other posts with the same issue, but one refers to the "Convert empty strings to null" option. That is NOT checked. Another had a problem with a mismatch of column names - that is not my issue. Here is my flow:

II am trying to do a simple insert to a target table:

The Advanced settings:

tLogRow_2 shows 4 records with data values. But the 4 records that get inserted to the target table in Snowflake are all null values.

Here's my schema in the tDBOutput component:

Here's the schema in Snowflake. The only difference is the ID which is auto-generated:

Any ideas on why NULL values go in instead of the real values? Thanks in advance!

Re: Every column is NULL when inserting to Snowflake

jlolling — Wed, 10 May 2023 19:41:37 GMT

I guess tLogRow_1 does not show any records?

Thats weird. The only way to find out what went wrong would be inspecting the generated code and see if I can recognise any hidden error.

Re: Every column is NULL when inserting to Snowflake

jlolling — Wed, 10 May 2023 19:42:52 GMT

Could you please check the die-on-error option in the tDBOutput (remove the reject flow before) and test again?

Re: Every column is NULL when inserting to Snowflake

Papademuchos — Wed, 10 May 2023 20:07:57 GMT

Correct - tLogRow_1 does not show any records.

I removed tLogRow_1 so there is nothing in the reject flow, then I checked the die-on-error option and re-ran it. Nothing - no errors reported. The job reports finishing successfully.

Here is one of the lines where it appears to be building the output values:

String getSchemaValue() {

StringBuilder s = new StringBuilder();

a("{\"type\":\"record\",", s);

a("\"name\":\"tDBOutput_1\",\"fields\":[{", s);

a("\"name\":\"DEPLOYMENT_ID\",\"type\":[\"int\",\"null\"],\"di.table.comment\":\"\",\"AVRO_TECHNICAL_KEY\":\"DEPLOYMENT_ID\",\"talend.field.dbColumnName\":\"DEPLOYMENT_ID\",\"di.column.talendType\":\"id_Integer\",\"di.column.isNullable\":\"true\",\"talend.field.pattern\":\"\",\"talend.field.length\":\"38\",\"di.column.relationshipType\":\"\",\"di.table.label\":\"DEPLOYMENT_ID\",\"talend.field.precision\":\"0\",\"di.column.relatedEntity\":\"\"},{",

s);

Re: Every column is NULL when inserting to Snowflake

Papademuchos — Thu, 11 May 2023 21:22:16 GMT

I figured out my problem. I don't understand why, but it works now.

I had a test job for writing data to Snowflake. I circled back around to that job and tried it out again, and it works! I set it up for the same table and used a tFixedFlowInput component to pass in data. 1 record inserted.

Then I copied that component and pasted it into my job referenced above. I redirected the flow to the new component and tried it out - it worked! So then I compared the two. The ONLY difference is the component that doesn't work as all of the columns in uppercase. The component that does work has them all in lowercase. I had thought about this before, but the Advanced Option "Allow Snowflake to convert columns to uppercase" was checked. I thought this was needed because when you look at the structure in Snowflake they are displayed in uppercase.

This one did not work

This one worked

I know both java and snowflake are case sensitive, but I would have thought with that option it wouldn't have mattered. Especially since when I used the component to retrieve my table it auto-populated the columns in uppercase. Yet that doesn't seem to work...???

Re: Every column is NULL when inserting to Snowflake

Papademuchos — Thu, 11 May 2023 21:43:05 GMT

A little more info - the input column definition has the match the output column definition in case... I had lowercase on the input side and upper case on the output side. That didn't match so nulls were passed through. As long as they match on both sides then it's good