topic Re: Temporary Table support in Snowflake from SAS in SAS Programming

Temporary Table support in Snowflake from SAS

pchegoor1 — Fri, 16 Apr 2021 01:06:46 GMT

Hello Everyone,

I am trying to test the creation of TEMPORARY Tables in Snowflake using SAS/ACCESS Interface to Snowflake but it does not seem to work as expected. TEMPOARARY Table in Snowflake ( https://docs.snowflake.com/en/user-guide/tables-temp-transient.html) only persists till the duration of the Session and is similar to the concept VOLATILE Table in Teradata. We are planning to move away from Teradata and embrace Snowflake for our database needs.

Below is my Code . I am just trying to insert 2 records from a SAS dataset into a TEMPORARY Table whose definition is created first within Snowflake.

LIBNAME T snow DSN=SF_Test user=User01 password="XXXXXX" schema=test  connection=GLOBAL ;


Data Sample;
Infile Datalines truncover;
Input ID Name $20.;
Datalines;
100 John Doe
200 Jim John
;
Run;

Proc Sql;
Connect to Snow(DSN=SF_Test user=User01 password="XXXXXX" schema=test  connection=GLOBAL );


 execute ( Create TEMPORARY TABLE SF_Temp( ID NUMBER , Name VARCHAR(20)) ON COMMIT PRESERVE ROWS ) by snow;
 execute ( COMMIT WORK ) by snow;

Quit;

Proc Sql;
Insert into T.SF_Temp
select  *
from  Sample;
Quit;

I get the ERROR: File T.SF_Temp.DATA does not exist. Obviously the TEMPORARAY Table SF_Temp does not seem to persist during the INSERT step above. I am using CONNECTION=GLOBAL option in both the LIBNAME and CONNECT TO statements as seen above . I tried using PROC APPEND too but that too did not succeed.

@JBailey and Others , do you have any thoughts on the above? Thanks.

Re: Temporary Table support in Snowflake from SAS

SASKiwi — Fri, 16 Apr 2021 01:46:04 GMT

Try this:

LIBNAME T snow DSN=SF_Test user=User01 password="XXXXXX" schema=test  connection=GLOBAL ;

Data Sample;
Infile Datalines truncover;
Input ID Name $20.;
Datalines;
100 John Doe
200 Jim John
;
Run;

Proc Sql;
Connect using T;

 execute ( Create TEMPORARY TABLE SF_Temp( ID NUMBER , Name VARCHAR(20)) ON COMMIT PRESERVE ROWS ) by T;
 execute ( COMMIT WORK ) by T;

Quit;

Proc Sql;
Insert into T.SF_Temp
select  *
from  Sample;
Quit;

Re: Temporary Table support in Snowflake from SAS

pchegoor1 — Fri, 16 Apr 2021 03:36:49 GMT

@SASKiwi Thanks for the Reply but unfortunately this too ends up with same Error :

ERROR: File T.SF_Temp.DATA does not exist.

Re: Temporary Table support in Snowflake from SAS

SASKiwi — Fri, 16 Apr 2021 06:46:44 GMT

@pchegoor1 - How about this? See doc

LIBNAME T snow DSN=SF_Test user=User01 password="XXXXXX" schema=test  connection=GLOBAL DBMSTEMP=YES ;

Proc Sql;
Connect using T;

 execute ( Create TEMPORARY TABLE SF_Temp( ID NUMBER , Name VARCHAR(20)) ON COMMIT PRESERVE ROWS ) by T;
 execute ( COMMIT WORK ) by T;
select * from connection to T 
(select count(*) from SF_Temp)
;
Quit;

Unfortunately I don't see Snowflake listed under Temporary Table Support

I suggest you open a Tech Support track to confirm this.

Re: Temporary Table support in Snowflake from SAS

pchegoor1 — Fri, 16 Apr 2021 17:37:24 GMT

@SASKiwi

This code below works as expected . But unfortunately the inserting of records using a SAS dataset does not work.

Also the DBMSTEMP=YES option is not supported on the snowflake Libname statement.

I hope SAS will make changes via some HOTFIX to support the insertion of data from SAS Dataset into a TEMPORARY Table in Snowflake via SAS. I have a ticket with SAS Tech support on this .Yet to hear back from them.

Libname _ALL_ clear;
LIBNAME T snow DSN=SF_Test user=User01 password="XXXXXX" schema=test  connection=GLOBAL ;

Proc Sql;
Connect USING T;


 execute ( Create TEMPORARY TABLE SF_Temp( ID NUMBER , Name VARCHAR(50)) ON COMMIT PRESERVE ROWS ) by T;
 execute ( COMMIT WORK ) by T;
 execute ( INSERT INTO SF_Temp Values (100, 'John Doe' ),(200, 'Jim John')  ) by T;



 select *  from connection to T
 (
   Select *  from SF_Temp

 );

Quit;

Output:

Re: Temporary Table support in Snowflake from SAS

JBailey — Mon, 19 Apr 2021 15:22:57 GMT

Hi @pchegoor1

I am no longer a Product Manager, so I don't keep up with these things like I used to. That being said, I think TEMP TABLES are a recent addition to Snowflake.

SAS/ACCESS Interface to Snowflake doesn't support TEMP TABLES. I will create a feature request and see if I can have this capability added to the roadmap.

Best wishes,

Jeff

Re: Temporary Table support in Snowflake from SAS

pchegoor1 — Mon, 19 Apr 2021 17:06:53 GMT

@JBailey

Thanks Jeff. At our company , we are planning to replace Teradata with Snowflake . We have several SAS codes written which are currently using the VOLATILE table in Teradata as means of quickly loading a SAS dataset ( 30K records or less) so that it can be joined with another Teradata table using Pass through in an efficient manner. But if we convert such a code to using SNOWFLAKE the inability of SAS/ACCESS Interface to SNOWFLAKE currently not supporting the loading of a SAS dataset into a TEMPORARY Table in SNOWFLAKE would cause the user to rely on the DBA giving permissions to create and drop TRANSIENT Table in SNOWFLAKE and then load the SAS dataset to such a table to achieve the same join . Also the user has to make sure the TRANSIENT table is explicitly dropped at end each SAS code so that it does not carry over to next session and incur charges . This is ok for few users but with several SAS Users this becomes a burden for the DBA to grant such access and ensure users drop the table explicitly .

According to this blog post date , the TEMPORARY Table concept in SNOWFLAKE existed since 2018 :
https://copycoding.com/d/temporary-vs-transient-tables-in-snowflake . Maybe it has undergone few changes since then

I hope SAS R&D takes the above into account and implements this much needed feature as soon as possible.

Thanks

Re: Temporary Table support in Snowflake from SAS

LinusH — Tue, 20 Apr 2021 09:21:20 GMT

One problem in some of the examples is that the temporary table is deleted when the connection goes down (like in the SQL pass-through example).

Isn't it possible to use a libname together with DBCREATE_TABLE_OPTS= Data Set Option?

I don't have access to Snowflake at the moment so I was unable to test this.

Re: Temporary Table support in Snowflake from SAS

JBailey — Tue, 20 Apr 2021 16:57:22 GMT

Hi @pchegoor1

I have requested the feature. Hopefully, it will be released soon.

Best wishes,

Jeff

Re: Temporary Table support in Snowflake from SAS

prashanthi — Mon, 22 Apr 2024 19:19:05 GMT

@JBailey Do you know if this feature is added in SAS?

Re: Temporary Table support in Snowflake from SAS

pchegoor1 — Mon, 22 Apr 2024 19:56:27 GMT

Yes . The Temporary Table Support has been added to Snowflake in SAS

https://documentation.sas.com/doc/en/sasstudiocdc/3.8/pgmsascdc/acreldb/n0irpkyp22l7vzn1il9lx6f4wmx9.htm

Re: Temporary Table support in Snowflake from SAS

JBailey — Wed, 24 Apr 2024 20:15:33 GMT

Hi @prashanthi,

As @pchegoor1 points out, support for Snowflake Temporary Tables was added to SAS/ACCESS. I retired from SAS before this support was added, so I don't have any example code to share.

Best wishes,
Jeff