SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Any experience with Snowflake

Accepted Solution Solved
Reply
Super User
Super User
Posts: 7,942
Accepted Solution

Any experience with Snowflake

[ Edited ]

Has any one tried to use SAS with https://www.snowflake.net/ cloud data warehousing service?

 

For now I was going to begin by trying to access it via ODBC driver.

Do we know if SAS is working on any integration tools for Snowflake to allow pushing computing into the snowflake engine?

 


Accepted Solutions
Solution
‎04-25-2018 11:00 AM
SAS Employee
Posts: 273

Re: Any experience with Snowflake

[ Edited ]

Hi @Tom,

 

There are no current plans to create a SAS/ACCESS product specifically for Snowflake. If there is an ODBC 3.x compliant Snowflake driver then SAS/ACCESS Interface to ODBC is a good choice.

 

The following LIBNAME statement options will help increase performance:

INSERTBUFF= Number of rows in an insert statement. Play around with the number. Bigger does not always mean better (start in the 100 plus range and be ready to experiment)

READBUFF=  You definitely want to set this. Play around with the number (start in the 100 plus range and be ready to experiment).

DBCOMMIT= May help. I haven't tried this one. 

 

If you are performing CREATE TABLE AS processing you will absolutely want to use DBIDIRECTEXEC. Ditto INSERT AS SELECT, UPDATE, and DELETE. You can read about it here.

 

If you have to move large amounts of data (5GB plus) it may be a good idea to use PROC S3 to move the file to AWS and then using the Snowflake LOAD command. This requires creating a JSON file and some explicit pass-through but it could be worth it. I haven't done this but I plan to try it once my Snowflake account is unlocked. It appears that Jeff can't remember his password. 

 

I have run some performance numbers on this but can't find them. I will rerun and post an article in the SAS Communities library or an external blog.

 

Best wishes,

Jeff

View solution in original post


All Replies
Occasional Contributor
Posts: 5

Re: Any experience with Snowflake

Hi Tom, we are also starting to use Snowflake cloud data warehousing service, would you be so kind to share how you are connecting?

 

Thanks

Mauricio

Solution
‎04-25-2018 11:00 AM
SAS Employee
Posts: 273

Re: Any experience with Snowflake

[ Edited ]

Hi @Tom,

 

There are no current plans to create a SAS/ACCESS product specifically for Snowflake. If there is an ODBC 3.x compliant Snowflake driver then SAS/ACCESS Interface to ODBC is a good choice.

 

The following LIBNAME statement options will help increase performance:

INSERTBUFF= Number of rows in an insert statement. Play around with the number. Bigger does not always mean better (start in the 100 plus range and be ready to experiment)

READBUFF=  You definitely want to set this. Play around with the number (start in the 100 plus range and be ready to experiment).

DBCOMMIT= May help. I haven't tried this one. 

 

If you are performing CREATE TABLE AS processing you will absolutely want to use DBIDIRECTEXEC. Ditto INSERT AS SELECT, UPDATE, and DELETE. You can read about it here.

 

If you have to move large amounts of data (5GB plus) it may be a good idea to use PROC S3 to move the file to AWS and then using the Snowflake LOAD command. This requires creating a JSON file and some explicit pass-through but it could be worth it. I haven't done this but I plan to try it once my Snowflake account is unlocked. It appears that Jeff can't remember his password. 

 

I have run some performance numbers on this but can't find them. I will rerun and post an article in the SAS Communities library or an external blog.

 

Best wishes,

Jeff

Occasional Contributor
Posts: 5

Re: Any experience with Snowflake

Hi @JBailey

 

Opening a Snowflake table in SAS Enterprise Guide 7.15 takes a really long time, Character variable length in Snowflake seems to be the problem as it shows that they are created by default with a length of VARCHAR(16777216). 

We have tried creating a SAS view which solves the speed problem, but it demands a manually intensive process to determine the right length of each character variable and it only works after the Snowflake table has been completely scanned which could take a long time. Are there any other approaches or configuration changes to the ODBC parameters that could help? 

SAS Employee
Posts: 273

Re: Any experience with Snowflake

Hi @curaloco

 

If you don't mind, can you open a new topic because this one has be solved and we don't want to overload it. Be sure to include my user name in the post. That way I will get a notification when the topic shows up.

 

Best wishes,
Jeff

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1042 views
  • 3 likes
  • 3 in conversation