You may have heard that SAS is developing a new SAS/ACCESS engine for Snowflake. If you haven't, let me be the first to tell you that SAS is developing a new SAS/ACCESS engine for Snowflake!
In the meantime, we will be using SAS/ACCESS Interface to ODBC (or the new JDBC product) to access our Snowflake data. It is going to be fun but it is more fun to know what I am about to show you.
Let's start with some SAS code:
libname snow odbc complete="DRIVER={SnowflakeDSIIDriver};
SERVER=mysnowflakeaccount.snowflakecomputing.com;
WAREHOUSE=mywarehouse;
UID=myuser;
PWD=mypassword;
DATABASE=mydatabase;"
SCHEMA=myuser;
/* Let's load the cars data into snowflake */
data snow.cars;
set sashelp.cars;
run;
Here is what that happens:
175 libname snow odbc complete="DRIVER={SnowflakeDSIIDriver};
176 SERVER=mysnowflakeaccount.snowflakecomputing.com;
177 WAREHOUSE=mywarehouse;
178 UID=myuser;
179 PWD=mypassword;
180 DATABASE=mydatabase;"
181 SCHEMA=myuser;
NOTE: Libref SNOW was successfully assigned as follows:
Engine: ODBC
Physical Name:
182 data snow.cars;
183 set sashelp.cars;
184 run;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: The data set SNOW.CARS has 428 observations and 15 variables.
NOTE: DATA statement used (Total process time):
real time 44:24.54
cpu time 1.07 seconds
44 minutes and 25 seconds is a lot of time! It's crazy. Yes, I went to lunch while this was running. This is the first experience that SAS users had with Snowflake. If you have ever heard complaints, this is why.
Let's try something different:
libname snow odbc complete="DRIVER={SnowflakeDSIIDriver};
SERVER=mysnowflakeaccount.snowflakecomputing.com;
WAREHOUSE=mywarehouse;
UID=myuser;
PWD=mypassword;
DATABASE=mydatabase;"
SCHEMA=myuser
dbcommit=10000 autocommit=no
readbuff=200 insertbuff=200;
/* Let's load the cars data into snowflake */
data snow.cars2;
set sashelp.cars;
run;
And the results are (much better)!:
185 libname snow odbc complete="DRIVER={SnowflakeDSIIDriver};
186 SERVER=mysnowflakeaccount.snowflakecomputing.com;
187 WAREHOUSE=mywarehouse;
188 UID=myuser;
189 PWD=mypassword;
190 DATABASE=mydatabase;"
191 SCHEMA=myuser
192 dbcommit=10000 autocommit=no
193 readbuff=200 insertbuff=200;
NOTE: Libref SNOW was successfully assigned as follows:
Engine: ODBC
Physical Name:
194 data snow.cars2;
195 set sashelp.cars;
196 run;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: The data set SNOW.CARS2 has 428 observations and 15 variables.
NOTE: DATA statement used (Total process time):
real time 10.70 seconds
cpu time 0.07 seconds
The key take-away is this. When using SAS/ACCESS Interface to ODBC with Snowflake it is a great idea to add the following options:
- DBCOMMIT=
- AUTOCOMMIT=no
- READBUFF=
- INSERTBUFF=
The heavy hitters are READBUFF= and INSERTBUFF=. It is important to avoid the "bigger is better" trap. Notice the I set the values to 200; a simple guess. I am pretty sure that I could make this faster. It is possible that setting the values to a smaller numbers would improve performance.
If you are using Linux/UNIX you may encounter issues with character sets. If you run code and see something similar to this you will need to change your encoding.
OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
72
73 proc sql;
74 select * from someuser.persons;
ODBC: AUTOCOMMIT turned ON for connection id 0 4 1544636964 no_name 0 SQL (2)
5 1544636964 no_name 0 SQL (2)
ODBC_1: Prepared: on connection 0 6 1544636964 no_name 0 SQL (2)
SELECT * FROM SOMEUSER.PERSONS 7 1544636964 no_name 0 SQL (2)
8 1544636964 no_name 0 SQL (2)
ERROR: CLI prepare error: SQL compilation error:syntax error line 1 at position 0 unexpected '䕓䕌'.
SQL statement: SELECT * FROM SOMEUSER.PERSONS.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
75
76 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
Here is the fix for the encoding issue (your sasv9.cfg file may be in a different location).
Modify the /opt/sas/viya/config/etc/workspaceserver/default/sasv9.cfg file to add the following:
-encoding latin1
I hope this helps.
Best wishes,
Jeff
@JBailey
