Snowflake - empty strings converted to NULL

Highlighted
Six Stars

Snowflake - empty strings converted to NULL

Hi,

I am using Talend to load data into Snowflake. Data is being passed using tSnowflakeOutput component.

If there are empty strings in the source data, I'm getting an error:

 

java.io.IOException: net.snowflake.client.loader.Loader$ConnectionError: 

State: INGEST_DATA, INSERT INTO ... SELECT * FROM ..., NULL result in a non-nullable column

at org.talend.components.snowflake.runtime.SnowflakeWriter.close


No nulls are allowed on source, destination, or anywhere on data pipeline

 

Looks like somehow empty strings are being converted to NULLS automatically.

Is there any way to prevent this?

I can't see any setting that would control this behavior.

 

Thanks,

Rimvis


Accepted Solutions
Employee

Re: Snowflake - empty strings converted to NULL

Hi,

 

    The screen shot I had shared was for version 7.1 of Talend. It is having significant improvements for Snowflake both from additional functionalities and performance perspective.

 

     If you are not in a position to upgrade to 7.1, my suggestion will be to try to verify whether you can add the data with single space and verify it is overcoming the null issue. Of course it is not a great option like the first one but still you can manage the overall flow.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

View solution in original post


All Replies
Employee

Re: Snowflake - empty strings converted to NULL

Hi,

 

   Could you please verify whether you have unchecked the below check box in tDBOutput(Snowflake)?

 

image.png

 

This should fix your issue.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

Six Stars

Re: Snowflake - empty strings converted to NULL

Hi Nikhil ,

 

I can't see this option anywhere. I am using Talend Open Studio 6.5.1 if that helps.

 

tSnowflakeOutput.png

 

Employee

Re: Snowflake - empty strings converted to NULL

Hi,

 

    The screen shot I had shared was for version 7.1 of Talend. It is having significant improvements for Snowflake both from additional functionalities and performance perspective.

 

     If you are not in a position to upgrade to 7.1, my suggestion will be to try to verify whether you can add the data with single space and verify it is overcoming the null issue. Of course it is not a great option like the first one but still you can manage the overall flow.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

View solution in original post

Six Stars

Re: Snowflake - empty strings converted to NULL

Thanks Nikhil,

 

Upgrading to 7.1 is certainly worth considering.

Adding a space or any other character to make the string non-empty does help. But for now I simply changed target column to allow nulls and processing the data in later stages.

 

Thanks for your help,

Rimvis

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog