SAS LIBNAME connection to Snowflake temporary table fails

[mrenner]

Working in SAS. Am able to create PROC SQL script that creates a temporary table on Snowflake and inserts records into that temporary table. When I try to move data from the Snowflake temporary table to a SAS Work table receive error message ERROR: File SNOW.TMP_TABLE.DATA does not exist.

According to this year-old thread Snowflake temporary tables are not compatible in SAS. Do you know if this is still the case? Without this support we are looking at inefficient joins across multiple platforms.

https://communities.sas.com/t5/SAS-Programming/Temporary-Table-support-in-Snowflake-from-SAS/td-p/73...

PROC SQL NOPRINT;
 
/* Connect to Snowflake for pass throughs */
 
CONNECT TO ODBC AS conn (COMPLETE=&sfconn);
 
/* Create temporary table on Snowflake */
 
EXECUTE (
	CREATE TEMPORARY TABLE TMP_TABLE (HHID NUMBER(22,0))
	ON COMMIT PRESERVE ROWS;
) by conn;
 
EXECUTE (
	COMMIT WORK;
) by conn;
 
EXECUTE (
	INSERT INTO TMP_TABLE (HHID) 
	VALUES (311100);
) by conn;
 
/* Create SAS Work table from temporary Snowflake table */
 
CREATE TABLE WORK.TMP_TABLE_SAS AS
SELECT *
FROM SNOW.TMP_TABLE;
 
/* Disconnect from Snowflake */
 
DISCONNECT FROM conn;
 
QUIT;

[Tom]

Why did you make a separate connection?  The temporary table was probably deleted when you closed the connection.

What SCHEMA does snowflake store the temporary tables into?  Is that the SCHEMA you used in defining the SNOW libref?  If not you might need to use a dataset option when referencing the temporary table using the SNOW libref so it looks in the right SCHEMA.

Try something like this instead:

libname snow odbc .... ;
PROC SQL NOPRINT;
CONNECT using snow;
EXECUTE by snow (
	CREATE TEMPORARY TABLE TMP_TABLE (HHID NUMBER(22,0))
	ON COMMIT PRESERVE ROWS;
);
 
EXECUTE by snow (
	COMMIT WORK;
) ;

EXECUTE by snow (
	INSERT INTO TMP_TABLE (HHID) 
	VALUES (311100);
);
 
/* Create SAS Work table from temporary Snowflake table */
 
CREATE TABLE WORK.TMP_TABLE_SAS AS
SELECT *
FROM SNOW.TMP_TABLE
;
 
QUIT;

PS You cannot store 22 digit integers uniquely in a SAS dataset.  Your HHID variable should probably be a character string instead of a number.

[mrenner]

Thank you for the suggestion. I receive the same error with your code. (In my example I did not disconnect until the end, btw).

Snowflake syntax is DATABASE.SCHEMA.TABLE. I have the correct DATABASE and SCHEMA set up for libname SNOW. Interestingly the schema name is actually "DATA". And error message we are getting is "ERROR: File SNOW.TMP_TABLE.DATA does not exist" in which the schema name is put behind the table name as in DATABASE.TABLE.SCHEMA.

As the thread I linked to asserts, SAS is not compatible with Snowflake temporary tables. I hope this is not the case but that was their conclusion and a submission was made to SAS. Maybe there is a hotfix.

[Tom]

The .DATA in the error message is just its way of saying it was trying to access data and not some other type of object.  It has nothing to do with Snowflake's concept of DATABASE or SCHEMA.

What SCHEMA did you connect to with the LIBNAME statement? Is that the DATA you mentioned?  The SNOWFLAKE documentation makes it sound like you can use any schema name when making the temporary table.  So what happens if you make the table as DATA.TMP_TABLE instead of just TMP_TABLE?

[mrenner]

Thank you for the clarification on .DATA in the SAS error message.

These are all good questions, yes SCHEMA=DATA in the connection string. I've tried just about every combination of including or excluding the database and schema from every statement in the program. It's either the error I posted or an error regarding a syntax not allowed. Cross-posted on Snowflake forums but haven't heard anything yet. 

LIBNAME SNOW ODBC COMPLETE = "
DRIVER=SnowflakeDSIIDriver;
AUTHENTICATOR=SNOWFLAKE_JWT;
SERVER=xxxxxxxx;
UID=&userid;
PRIV_KEY_FILE=&spwd;
PRIV_KEY_FILE_PWD=;
Warehouse=xxxxxxxx;
Role=&datasrc;
Schema=DATA;
Database=xxxxxxxx;
";

[Reeza]

According the post you linked to, which linked to the documentation below, temporary table support for Snowflake looks like it was implemented?

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0irpkyp22l7vzn1il9lx6f4wmx9.htm

I don't see the DBMSTEMP option in your libname statement though? The default is No. 

[mrenner]

Hmm... good catch. I added DBMSTEMP=YES; to connection string however same error. It must be possible though because SAS indicates support. Will continue to search for other examples.

/* Get credentials */

%get_system_credentials(source="xxxxxxxx", envir="prod");

/* Snowflake libname connection */

LIBNAME SNOW ODBC COMPLETE = "
DRIVER=SnowflakeDSIIDriver;
AUTHENTICATOR=SNOWFLAKE_JWT;
SERVER=xxxxxxxx;
UID=&userid;
PRIV_KEY_FILE=&spwd;
PRIV_KEY_FILE_PWD=;
Warehouse=xxxxxxxx;
Role=&datasrc;
Database=xxxxxxxx;
CONNECTION=GLOBAL;
DBMSTEMP=YES;
";

PROC SQL NOPRINT;

/* Connect to Snowflake */

CONNECT USING SNOW;

/* Create temporary table on Snowflake */

EXECUTE by SNOW (USE DATABASE xxxxxxxx;);
EXECUTE by SNOW (USE SCHEMA DATA;);

EXECUTE by SNOW (
	CREATE TEMPORARY TABLE TMP_TABLE (HHID NUMBER(13,0))
	ON COMMIT PRESERVE ROWS;
);

EXECUTE by SNOW (
	COMMIT WORK;
);

EXECUTE by SNOW (
	INSERT INTO TMP_TABLE (HHID) 
	VALUES (311100);
);

/* Create SAS Work table from temporary Snowflake table */

CREATE TABLE WORK.TMP_TABLE_SAS AS
SELECT *
FROM SNOW.TMP_TABLE;

QUIT;

[Reeza]

The DB2 example from the docs may provide some insight. Also means you can contact SAS tech support for help and they should help you out.

[Reeza]

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p0he4t6yjfmkhpn16qrf0cdhllu6.htm

It also looks like you're using SQL explicit pass through rather than PROC SQL. If you use PROC SQL without explicit pass through does it work?

[mrenner]

Not sure what you mean by not using PROC SQL - I'm a bit of a novice in SAS.

My goal is to create a temporary table in Snowflake and insert data into it from a SAS Work table.

Right now I can create the temporary table in Snowflake ok. I'm now just trying to get the PROC SQL to recognize that temporary Snowflake table but it won't.

[mrenner]

I tried to just create a table directly but it says I have insufficient privileges. Going to have to hit this with a fresh mind on Monday and start googling again. Thanks for everyone's help.

/* Source data libname connection */
%get_system_credentials(source="xxxxxxxx",envir="&env");
libname OV_TEST sqlsvr user="&userid" pw="&spwd" datasrc="&datasrc" schema="&schema" insertbuff=32767;

/* Snowflake libname connection */
%get_system_credentials(source="xxxxxxxx", envir="prod");

LIBNAME SNOW ODBC COMPLETE = "
DRIVER=SnowflakeDSIIDriver;
AUTHENTICATOR=SNOWFLAKE_JWT;
SERVER=xxxxxxxx;
UID=&userid;
PRIV_KEY_FILE=&spwd;
PRIV_KEY_FILE_PWD=;
Warehouse=xxxxxxxx;
Role=&datasrc;
SCHEMA=DATA;
Database=xxxxxxxx;
CONNECTION=GLOBAL;
DBMSTEMP=YES;
";

PROC SQL NOPRINT;

CREATE TABLE SNOW.TMP_TABLE as (
	SELECT a.HHID
	FROM OV_TEST.SOURCE_TABLE a
	WHERE a.HHID = 311100
	);

QUIT;

[SASKiwi]

This SAS Note may be relevant: https://support.sas.com/kb/68/155.html

What SAS version are you running on? If it is 9.4M7 a fix for Snowflake temporary tables is available. Check with your SAS administrator to confirm if it has been applied or not.

[mrenner]

I confirmed we are only on SAS version 9.4M6. It doesn't look we plan to move to M8 until next year. Internal rules on installing software make it more difficult applying hotfixes (plus everyone using the program would need to do that).

It's still not clear to me if that is the issue or it's a coding issue. I will have to reach out to people within our org and see if anyone has come up with a solution... without temporary tables you can't really run cross-platform transactions efficiently.

[Quentin]

You should reach out to SAS support as well.  It's free, and they're excellent.  And SAS is really committed to snowflake integration, as evidenced by the recently announcement of running Viya scoring models inside a snowflake container.  Tech support will definitely help you with this.

[SASKiwi]

Tech Support will be able to confirm if the problem in the SAS Note also applies to M6 as well as M7.