Does not appear tables when creating a Snowflake Connection in Talend Open Studio for Data Integration

Highlighted
Four Stars

Does not appear tables when creating a Snowflake Connection in Talend Open Studio for Data Integration

Hello, I downloaded yesterday Talend Open Studio for Data Integration and wanted to connect to Snowflake to do a PoC.

 

I create a Snowflake Connection and the "test connection" it says "Connection successful", but in the next page, there're no tables to be added. The database and schema I use are upper case.

 

If I try to save data using tDBOutput into a table, it doesn't find the database. The database exists.

 

If I try to read data from a table, it doesn't find the table. But the table exists and I can query it with Snowflake web UI without any problem and I can see all databases, schemas and tables from there. Is any special permission required? What is happening? With other ETL sofware I didn't have permission problems.

 

Above I show the messages I got:

 

If I use a tDBOutput Snowflake component to export data into a table and I run it, it says:

 

[statistics] connecting to socket on port 3896
[statistics] connected
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by org.talend.components.api.component.runtime.JarRuntimeInfo (file:/C:/Users/myself/.eclipse/org.talend.rcp.branding.tos.product_7.2.1.20190620_1446_891994592_win32_win32_x86_64/configuration/.m2/repository/org/talend/components/components-api/0.27.3/components-api-0.27.3.jar) to field java.net.URL.factory
WARNING: Please consider reporting this to the maintainers of org.talend.components.api.component.runtime.JarRuntimeInfo
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
Exception in component tDBOutput_1 (OnBoardingDemoJob)
java.io.IOException: SQL compilation error:
Database 'MY_DATABASE' does not exist.
at org.talend.components.snowflake.runtime.SnowflakeWriter.execTableAction(SnowflakeWriter.java:228)
at org.talend.components.snowflake.runtime.SnowflakeWriter.tableActionManagement(SnowflakeWriter.java:234)
at org.talend.components.snowflake.runtime.SnowflakeWriter.write(SnowflakeWriter.java:266)
at local_project.onboardingdemojob_0_1.OnBoardingDemoJob.tFileInputExcel_1Process(OnBoardingDemoJob.java:1073)
at local_project.onboardingdemojob_0_1.OnBoardingDemoJob.runJobInTOS(OnBoardingDemoJob.java:1480)
at local_project.onboardingdemojob_0_1.OnBoardingDemoJob.main(OnBoardingDemoJob.java:1330)
Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
Database 'MY_DATABASE' does not exist.
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:135)
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:60)
at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:503)
at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:370)
at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:474)
at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:230)
at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:172)
at net.snowflake.client.core.SFStatement.execute(SFStatement.java:663)
at net.snowflake.client.jdbc.SnowflakeStatementV1.executeInternal(SnowflakeStatementV1.java:205)
at net.snowflake.client.jdbc.SnowflakeStatementV1.execute(SnowflakeStatementV1.java:243)
at org.talend.components.common.tableaction.TableActionManager.exec(TableActionManager.java:66)
at org.talend.components.snowflake.runtime.SnowflakeWriter.execTableAction(SnowflakeWriter.java:222)
... 5 more
[statistics] disconnected

 

 

If I try to query data, specifying the table manually and a pair of columns:

 

Exception in component tDBInput_1 (simple_snowflake_to_snowflake)
java.io.IOException: Error processing query: select FIELD_A, FIELD_B from MY_TABLE
at org.talend.components.snowflake.runtime.SnowflakeReader.start(SnowflakeReader.java:116)
at org.talend.codegen.flowvariables.runtime.FlowVariablesReader.start(FlowVariablesReader.java:73)
at local_project.simple_snowflake_to_snowflake_0_1.simple_snowflake_to_snowflake.tDBInput_1Process(simple_snowflake_to_snowflake.java:1036)
at local_project.simple_snowflake_to_snowflake_0_1.simple_snowflake_to_snowflake.runJobInTOS(simple_snowflake_to_snowflake.java:1589)
at local_project.simple_snowflake_to_snowflake_0_1.simple_snowflake_to_snowflake.main(simple_snowflake_to_snowflake.java:1439)
Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
Object 'MY_TABLE' does not exist.

 

 

Thank you,

 

Josep


Accepted Solutions
Seventeen Stars

Re: Does not appear tables when creating a Snowflake Connection in Talend Open Studio for Data Integration

Regarding the missing tables, I agree with you.

Sometimes it has something to do with missing rights to read meta data tables. Did you have tried to get the list of tables with any other tool (using the same user credentials) like e.g. DBeaver ?


All Replies
Moderator

Re: Does not appear tables when creating a Snowflake Connection in Talend Open Studio for Data Integration

Hello,

Could you please let us know what's JDK version you are using?

Best reards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: Does not appear tables when creating a Snowflake Connection in Talend Open Studio for Data Integration

Hello Sabrina,

 

I'm using:

    openjdk 11.0.2 2019-01-15
    OpenJDK Runtime Environment 18.9 (build 11.0.2+9)
    OpenJDK 64-Bit Server VM 18.9 (build 11.0.2+9, mixed mode)

 

Best regards,

 

Josep

 

Seventeen Stars

Re: Does not appear tables when creating a Snowflake Connection in Talend Open Studio for Data Integration

The problem is for sure an error in your SQL code. It looks like you use demo SQL code and such tables  and databases simply does not exist.

Please ask someone with knowledge about your database for a valid query and try it again.

Four Stars

Re: Does not appear tables when creating a Snowflake Connection in Talend Open Studio for Data Integration

Hi Sabina,

 

I obviously change the real SQL code to avoid share private things.

 

I don't get any table in the Snowflake connection (as seen in the screenshot I attach below) and it's really strange and it has nothing to do with SQL querys.

 

Regards,

 

Josep

 

Talend_add_tables_to_Snowflake connection.png

Seventeen Stars

Re: Does not appear tables when creating a Snowflake Connection in Talend Open Studio for Data Integration

Regarding the missing tables, I agree with you.

Sometimes it has something to do with missing rights to read meta data tables. Did you have tried to get the list of tables with any other tool (using the same user credentials) like e.g. DBeaver ?

Four Stars

Re: Does not appear tables when creating a Snowflake Connection in Talend Open Studio for Data Integration

Thank you! The rights were find, but I had to specify the role in advance settings tap.

I realized that because in DBeaver by default I couldn't see the tables, but it gave me the option to change role quite easily.

 

Best regards,

 

Josep

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