snowflake tdboutput temp table

[Anonymous]

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!

[manodwhb]

@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.

[Anonymous · Accepted solution]

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.

[Anonymous]

@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

[Anonymous]

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>

[Anonymous]

@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

[Anonymous]

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.