topic Re: snowflake tdboutput temp table in Talend Studio

snowflake tdboutput temp table

Anonymous — Mon, 18 Mar 2019 20:14:43 GMT

hello:

I noticed that the snowflake tDBOutput always creates a temp table them loads the data in snowflake.

My benchmarks show faster performance and less credit usage NOT using a temp table.

Is there a way to over-ride this behavior and have it forgo creating a temp table and have it load directly to my target table?

Is there a reason for the loading temp table step (just for error handling?) ?

thanks!

Re: snowflake tdboutput temp table

manodwhb — Tue, 19 Mar 2019 09:12:12 GMT

@mhodent ,when you were trying to load data into main table,is it creating a first temp table and loading to temp first and then loading to main table ?

Re: snowflake tdboutput temp table

manodwhb — Tue, 19 Mar 2019 09:12:45 GMT

@mhodent ,can you post your job design ?

Re: snowflake tdboutput temp table

Anonymous — Tue, 19 Mar 2019 11:10:33 GMT

attached - thanks. the tdboutput components create the temp tables using native snowflake commands after the put.

DI_Demo_CSV_to_Snowflake_0.1.zip

Re: snowflake tdboutput temp table

manodwhb — Tue, 19 Mar 2019 11:14:51 GMT

@mhodent ,the attachment is export of the code ? if yes i am getting error like There is no valid items to import, and make sure the selected path contain valid items.

Re: snowflake tdboutput temp table

Anonymous — Tue, 19 Mar 2019 11:21:19 GMT

no, that was a export > generate HTML Doc.. unzip and look at the design.

the problem is that the tdboutput generates a temp table as an intermediary step (on snowflake instance), and I would like to bypass creating the temp table & have the output just do a put, then a load directly to my target table

thanks

Re: snowflake tdboutput temp table

manodwhb — Tue, 19 Mar 2019 11:39:06 GMT

@mhodent ,useally we will not specify any temp table in talend job,may be that will create in snowflake and need to check the snowflake settings? can you show me where it was creating?

Re: snowflake tdboutput temp table

Anonymous — Tue, 19 Mar 2019 11:45:31 GMT

when I use the tdboutput to push the file into snowflake, I can see that a temp table is created for before the insert statement when looking at query history within snowflake history interface.

thanks

Re: snowflake tdboutput temp table

manodwhb — Tue, 19 Mar 2019 12:05:38 GMT

@mhodent ,it may be work in that way at snowflake DB side,we can not do in Talend side. need to verify at snowflake settings.

Re: snowflake tdboutput temp table

Anonymous — Tue, 19 Mar 2019 12:08:00 GMT

Ok thanks

Re: snowflake tdboutput temp table

manodwhb — Wed, 20 Mar 2019 12:30:36 GMT

@mhodent ,I have tested this and temp table was creating first and then it is loading to main table,I am confirming that from talend we cannot set anything and it was specific to snowflakeDB.

Re: snowflake tdboutput temp table

Anonymous — Wed, 20 Mar 2019 12:36:54 GMT

I have confirmed that it is the Snowflake API that is doing this and the output component has no control over this. Thank you for investigating. I will look at the API Spec, & may end up contacting Snowflake to see if this behavior can be controlled thru the API. Until then, I will control this behavior by creating a joblet without the component.

Thanks for the help.

Re: snowflake tdboutput temp table

Anonymous — Mon, 15 Apr 2019 19:21:05 GMT

@mhodent

Were you able to stop Snowflake from creating Temporary tables ? . Why and How it was done can you please share your ideas. Also Snowflake always recommend to have a stage before lading into the main table.

Regards,

Gopi

Re: snowflake tdboutput temp table

Anonymous — Mon, 15 Apr 2019 20:35:14 GMT

As of this point, Snowflake has advised that they do not enforce the use of a temp tables in either the API or at all, so I am still "arm waving" over the source for temp table creation. I currently have an open ticket with Talend to confirm one way or the other.

Watching query history in the SF UI, I can see the temp table creation comes from the same session as the Talend session, so It has to be connected to the session. As to how the call is made, I can't find it in the java code. I will post again when I get an answer from Talend.

Our work-around was to manually script the desired steps in a Talend Joblet and it works like a charm.

Put<files>

COPY INTO <table>

RM <files>

Re: snowflake tdboutput temp table

Anonymous — Tue, 16 Apr 2019 05:10:25 GMT

@mhodent - Thanks for the Quick reply it helps.

You had mentioned that you had consolidated the SnowSQL Command in you joblets.

Hence did you dump the tables into files in your local directory and Used the Joblet to load the SnowFlake tables ?

Where did you write all your COPY, PUT RM commands? did you tJDBC to combine write in the query override?

Thanks again

Regards,

Gopi

Re: snowflake tdboutput temp table

Anonymous — Tue, 16 Apr 2019 13:22:06 GMT

Hello.

I do not have the specifics for the Joblet. Another member wrote it using the sequence mentioned.

This can be written to suit your needs - for example, if you want to land the files locally or into Azure Blog or AWS S3 you can do that.

IF I were to do this personally, I would create an external named stage area, on S3 or Azure Blob, push the files there, then try tJDBCRow or some other component that supports SQL Statements on Snowflake to import the data.