topic Re: Improve code efficiency when retrieving data from Snowflake via SAS in SAS Data Management

Improve code efficiency when retrieving data from Snowflake via SAS

sai212 — Tue, 01 Dec 2020 10:13:42 GMT

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/m-p/702618#M19668

Re: Improve code efficiency when retrieving data from Snowflake via SAS

yabwon — Tue, 01 Dec 2020 11:22:51 GMT

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

Re: Improve code efficiency when retrieving data from Snowflake via SAS

sai212 — Tue, 01 Dec 2020 14:24:54 GMT

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?

Re: Improve code efficiency when retrieving data from Snowflake via SAS

Tom — Tue, 01 Dec 2020 17:46:34 GMT

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.

Re: Improve code efficiency when retrieving data from Snowflake via SAS

sai212 — Tue, 01 Dec 2020 18:52:06 GMT

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

Re: Improve code efficiency when retrieving data from Snowflake via SAS

SASKiwi — Tue, 01 Dec 2020 23:08:01 GMT

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=p14o73fqwbby7xn1iqvjbdiixuyj.htm&locale=en

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

Re: Improve code efficiency when retrieving data from Snowflake via SAS

sai212 — Wed, 02 Dec 2020 10:38:29 GMT

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.

Re: Improve code efficiency when retrieving data from Snowflake via SAS

asadiq — Wed, 03 Mar 2021 22:27:39 GMT

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?

Re: Improve code efficiency when retrieving data from Snowflake via SAS

sai212 — Thu, 04 Mar 2021 02:34:57 GMT

It kind of worked for me using CAST statements..CAST(VARIABLE AS VARCHAR()) AS

Re: Improve code efficiency when retrieving data from Snowflake via SAS

asadiq — Thu, 04 Mar 2021 16:02:23 GMT

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.