Temporary Table support in Snowflake from SAS

[pchegoor1]

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.

[SASKiwi]

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;

[pchegoor1]

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

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

[SASKiwi]

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

[pchegoor1]

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

[JBailey]

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

[pchegoor1]

@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

[LinusH]

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.

[JBailey]

Hi @pchegoor1 

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

Best wishes,

Jeff

[prashanthi]

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

[pchegoor1]

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

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

[JBailey]

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