topic Re: Read snowflake table where table name is morethan 32 byte in SAS Programming

Read snowflake table where table name is morethan 32 byte

saswiki — Wed, 12 Oct 2022 17:52:45 GMT

Hi Team,

I am not able to access one particular table in snowflake due to the length of the table name is more than 32 byte.

I am connecting to Snowflake using libname; could you help me to give some idea on reading this table.

%let sf_driver=**********************;
%let sf_account=********************;
%let sf_server=*********************************;
%let sf_database=********************;
%let sf_schema=***************************;
%let sf_stage=***************;
%let sf_uid=*******************;
%let sf_pwd=**************************;

/* libnames for implicit connections */
libname snow ODBC complete="driver=&sf_driver;uid=&sf_uid;pwd=&sf_pwd;server=&sf_server;database=&sf_database"
schema=&sf_schema
autocommit=no
insertbuff=32767
readbuff=32767
preserve_tab_names=yes;

data _1;
set snow.table_name_where_length_is_more_than_32_byte;
run;

Re: Read snowflake table where table name is morethan 32 byte

Tom — Wed, 12 Oct 2022 19:46:49 GMT

This has been answered before. After first berating the idiot that thinks it a reasonable way to run a computer system to use object names that are so long you just need to switch to using pass thru SQL to access the datasets ("tables") and variables ("columns") with the ridiculous names.

proc sql;
connect using snow;
create table _1 as select * from connection to snow
  (select name1
        , name2
        , variable_name_where_length_is_more_than_32_bytes as name3
   from table_name_where_length_is_more_than_32_bytes
  )
;
quit;

Re: Read snowflake table where table name is morethan 32 byte

saswiki — Thu, 13 Oct 2022 08:08:45 GMT

Thanks for the reply. I have already tried this pass through method but i am getting the below error;

ERROR: The SNOW engine cannot be found.
ERROR: A Connection to the snow DBMS is not currently supported, or is not installed at your site.

I have used libname to connect with snowflake table first and then using connect statement in PROC SQL to get the column names.

Thanks

Re: Read snowflake table where table name is morethan 32 byte

LinusH — Thu, 13 Oct 2022 12:00:08 GMT

If you have a working libref, you can take a shortcut using "USING":

CONNECT USING libref <AS alias>;

Re: Read snowflake table where table name is morethan 32 byte

Tom — Thu, 13 Oct 2022 12:28:16 GMT

No need to re-connect to the database if you already have a libref defined that connects to the database. The CONNECT USING syntax allows you to connect using an existing libref. That way you don't have to repeat your credentials again. And also you reduce the load on the database by opening only one connection instead of two.

Your code used SNOW as the libref for the connection you made to the database that I why I used that name in my code.

Pattern:

libname SNOW ......;
proc sql;
connect using SNOW ;
select * from connection to SNOW (...);
execute by SNOW (...);
quit;

Re: Read snowflake table where table name is morethan 32 byte

saswiki — Thu, 13 Oct 2022 13:52:53 GMT

Thanks again.

I have tried with CONNECT USING, but still i am not able to view the table.
I just want to confirm if it is working for other tables which i can access from snowflake schema, for those also, ths code is not working.

libname snow ODBC complete="driver=&sf_driver;uid=&sf_uid;pwd=&sf_pwd;server=&sf_server;database=&sf_database"
schema=&sf_schema
autocommit=no
;

proc sql;
connect using snow;
select *
from connection to snow
(select *
from table_name
);
disconnect from snow;
quit;

I am getting the below error:

ERROR: CLI prepare error: SQL compilation error: Object 'Table Name' does not exist or not authorized.

Thanks again for your time and help.

Re: Read snowflake table where table name is morethan 32 byte

saswiki — Thu, 13 Oct 2022 13:54:55 GMT

I am able to view the tables in sas libraries, but using the pass through method, it is not working.

Re: Read snowflake table where table name is morethan 32 byte

Tom — Thu, 13 Oct 2022 14:04:19 GMT

You probably need to include the schema name when referencing the table in the pass thru sql.

Perhaps &sf_schema..table_name ?

Test the passthru SQL syntax using some other way to run SQL code in Snowflake. Such as the browser based tool that Snowflake has.

The other thing to check is the name of the variables in the snowflake dataset. Using SELECT * you are trying to retrieve them as is. If any of those are also not valid SAS names you will have problems. But I would expect a different error message.