ACCESS to Snowflake using DSN

[LinusH]

Hi all, I’m struggling a bit how get my connection from SAS 9.4M3 – Win64 to Snowflake to work properly. This is how I specifed the Snowflake DSN:

Then I can assign a libref (I have stored my credentials in my user profile in the meta data for the authdomain SnowFlake):

32         LIBNAME infoflak ODBC DATAsrc=infoflake AUTHDOMAIN="SnowFlake";
NOTE: Libref INFOFLAK was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: infoflake

But no tables appear in the Server tree in Enterprise Guide, and PROC DATASETS gives me this:

33         proc datasets lib=infoflak;
ODBC: Called SQLTables with schema of NULL
WARNING: No matching members in directory.
34         quit;

But if I know the name of the table I wish to query, it is found via the libref by code:

35         proc sql;
36         select max(rowno), 'Short name'n
37         from infoflak.customers
38         group by 'Short name'n;

ODBC_6: Prepared: on connection 2
SELECT * FROM CUSTOMERS
 
ODBC_7: Prepared: on connection 2
 select MAX(TXT_1."ROWNO"), TXT_1."Short name" from CUSTOMERS TXT_1 group by TXT_1."Short name"
 
ODBC_8: Executed: on connection 2
Prepared statement ODBC_7
 
ACCESS ENGINE:  SQL statement was passed to the DBMS for fetching data. 
39         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.88 seconds
      cpu time            0.01 seconds

Q: why are my tables visible to EG, nor PROC DATASETS? Can it be fixed?

The end game is to have the libaname registrered in metadata and the ability to perform "Register tables..." in SMC/DIS.

Also, I know some recommends nowadays is to use DNS-less connections, but I still want to understand how this works. Also, this is a part of an internal POC, and I hope we can put together article with tips and conclusions. This also mean that I most probably will come back with additional inquiries regarding SAS/Snowflake….

[Tom]

Why are you setting the schema to PUBLIC if you want to use the libref to see actual tables?  Try pointing it at an actual schema and then see if it can find the tables in that schema.

[Ksharp]

As @Tom said , Try to add option schema= .

LIBNAME infoflak ODBC DATAsrc=infoflake schema=xxxx ;

And you could check schema with this code:

proc sql;
   connect to ODBC(user=xxxxx  password=xxxxx   dsn=ksharp );
   create table work.odbcSchema as
          select * from connection to ODBC 
          (ODBC::SQLTables "","","","");
quit;

[LinusH]

I perhaps getting this wrong, but PUBLIC is actually a schema. A special one (it's always created when creating a database, and it's the default schema if no schema is specified).

And my tables are actually stored in the public schema.

But I created a table in a separate schema in the same database just to test, specified it in the DSN, but it gives the same behaviour. Connection successful, PROC DATSETS leaves nothing, but I can query the table. Also, it makes no difference if I specify the schema in then DSN, or in the libname statement.

And now the interesting part, it seems that SAS (or ODBC?) doesn't give a damn about the schema, even if I specify the schema in the DSN, I can still query all tables from different schemas in the same database.

If I use the same DSN in Excel, it seem working the same way. Even with schema defined, I need to drill down in the dialogue to the schema I wish to query.

Also, tried @Ksharp's code, and gives me all tables from all databases (in my mind it should be limited to the database specified in the DSN, right?

32         proc sql;
33            connect to ODBC(user=linus  password=XXXXXXXX dsn=infoflake );
ODBC: AUTOCOMMIT is YES for connection 2
34            create table work.odbcSchema as
35                   select * from connection to ODBC
36                   (ODBC::SQLTables "","","","");
ODBC: Called SQLTables with schema of NULL
NOTE: Table WORK.ODBCSCHEMA created, with 369 rows and 5 columns.

So...I'm still confused... 😕

[Tom]

You can access any schema your account has access to on that database/server. Same as how it would work on any other relational database system.  The use of a specific schema for a libref is so it knows which schema to use when you use only a one-level name.  But even when using a libref you should be able to use dataset options to change the schema.

I am not sure why SAS and/or ODBC cannot let you see the metadata with tools like PROC DATASETS. But I seem to remember having this issue with Snowflake and the ODBC driver when I tried it a couple of years ago.   I resorted to using SQLTABLES  ODBC method call to get at the metadata.

SAS now has an access engine for Snowflake so you might check if your site has a license or can get one so that you can try using that  instead of accessing through ODBC.

[LinusH]

I would love a specific ACCESS engine, but I don't think that has been released yet, at least not for 9.4. Last I heard it was scheduled for second half this year.

SQLTables works in ad-hoc situations, by experienced users. But I think "normal" ad-hoc users prefer browsing the available tables in an explorer. I might open a track to get this resolved.

As for metadata, the "Register tables..." action in SMC/DIS doesn't seem to use SQLTables, it returns no tables to register, much like PROC DATASETS:

1          LIBNAME infoflak ODBC  DATAsrc=infoflake  SCHEMA=public  AUTHDOMAIN="SnowFlake" ;
NOTE: Libref INFOFLAK was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: infoflake
2          

[LinusH · Accepted solution]

With a little nudge from SAS tech support I was able to resolve this.

Basically, object names in Snowflake are case insensitive,  Only when the user/client application enclose name within quotes they become case sensitive. I'm not sure how, but it must be something around this that happens when PROC DATASETS and the library listings fail. Bottom line is, if I specify the schema in upper case, PROC DATASETS, library listing in EG, and Register Tables wizard in SMC/DIS works.

Some takeaways:

• SAS doesn't take the schema specified in the DSN into account, even when case is correct
• You can make up any non existent schema name in your libname. Libname and PROC DATASETS won't through any error. I would suspect/like that the connection actually verifies that my connection parameters are correct, or?
• Even if schema name is case sensitve using SAS, it doesn't seem that other boject names are (tables, view columns)

[Ksharp]

Ou. It is like DB2. Schema must be  upper case .