BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sai212
Fluorite | Level 6

Hi,

We are moving away from Greenplum database to Snowflake. Testing a few data retrieving queries, I noticed the run time is longer compared to what it was in Greenplum. I realized this was because the lengths of a few variables were huge (32767). I used to limit these variables length using VARCHAR or CAST statements for Greenplum but am not sure how to do it for Snowflake. There's a thread within the forum discussing the use of readbuff and other options to improve connectivity for Snowflake and I wanted to how could these be used in SQL Passthrough queries. Appreciate your inputs on this.

PROC SQL;

CONNECT TO GREENPLM(DSN = GREENPLM USER = USERID PASSWORD=PASSWORD);

CREATE TABLE CUST_INFO AS SELECT * FROM CONNECTION TO GREENPLM(

SELECT FIRST_NAME :: VARCHAR(50) AS FIRST_NAME,

LAST_NAME :: VARHCAR(50) AS LAST_NAME /* these fields are limited to a length of 50 */

FROM CUST_DATA);

 

PROC SQL;

CONNECT TO ODBC AS SNWFLK (DSN =SNOWFLAKE USER=USERID PASSWORD=PASSWORD);

CREATE TABLE CUST_INFO AS SELECT * FROM CONNECTION TO SNWFLK(

SELECT FIRST_NAME, LAST_NAME  /* these names come with 32767 lengths */

FROM CUST_DATA);

 

https://communities.sas.com/t5/SAS-Data-Management/Slow-performance-of-ODBC-connection-to-SNOWFLAKE/...

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

Did you try with changing:

... AS SELECT * FROM CONNECTION TO SNWFLK(...

to:

... AS SELECT First_name length 50, Last_name length 50 FROM CONNECTION TO SNWFLK(...

?

To tell to SAS what is Your expectation about lengths.

 

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

9 REPLIES 9
yabwon
Onyx | Level 15

Did you try with changing:

... AS SELECT * FROM CONNECTION TO SNWFLK(...

to:

... AS SELECT First_name length 50, Last_name length 50 FROM CONNECTION TO SNWFLK(...

?

To tell to SAS what is Your expectation about lengths.

 

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



sai212
Fluorite | Level 6

Thank you! This works. How do I use this piece in case, I want to select every variable from the table and also define the lengths?

I tried

 

SELECT *, CUST_FIRST_NAME LENGTH 20, CUST_LAST_NAME LENGTH 20 FROM CONNECTION TO SNWFLK(---

but it didn't change saying those variables already exist in the table. Am I missing something here?

Tom
Super User Tom
Super User

Try connecting to SnowFlake with a LIBNAME instead of the SQL CONNECT statement.  Then you can try using the DBSASTYPE dataset option.

 

Another advantage of using the LIBNAME is that you can run PROC CONTENTS on the SnowFlake table to get the list of variables.

 

If you have the list of variables in a dataset then you can use that to generate the code that lists each variable explicitly and sets the appropriate lengths. Either as an SQL query or using the DBSASTYPE option.

 

 

sai212
Fluorite | Level 6

Thank you, Tom! But isn't a SQL Passthrough much more efficient than accessing via a LIBNAME statement?

SASKiwi
PROC Star

Using LIBNAME statements is not necessarily less efficient than PASSTHRU, particularly if you are just doing simple, single table queries.

 

One option that might help you is DBMAX_TEXT: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=acreldb&docsetTarget=p14o...

 

This will enable you to enforce a maximum length on all character variables being read.

sai212
Fluorite | Level 6

I agree LIBNAME not being bad in case of a single table. I'll look into the DBMAX_TEXT option. Thanks for your input! Appreciate it.

asadiq
Calcite | Level 5

we are in the similar situation.

DBMAX_TEXT or TEXTSIZE option is not worked for us. I believe it works for BLOB types.

did any of you get it work or any other system options to restrict length of the column?

sai212
Fluorite | Level 6
It kind of worked for me using CAST statements..CAST(VARIABLE AS VARCHAR()) AS
asadiq
Calcite | Level 5

Yes, everyone have to setup the CAST for their query.

But Looking for SAS system options otherwise we may need to setup the views for the users.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 3101 views
  • 0 likes
  • 5 in conversation