topic How to use bulkload option for SAS/Interface to Snowflake in SAS Data Management

How to use bulkload option for SAS/Interface to Snowflake

karthic2612 — Thu, 16 Jan 2020 13:32:49 GMT

Hi All,

Could someone give sample code snippet to use bulkload option for SAS/Access interface to Snowflake.

I am able to connect to Snow flake successfully using libname statement.

libname bulk sasiosnf dsn='snowflake_dev' user=xxxxxx

74 pwd=XXXXXXXXXXXX schema=xxxxx' role=xxxxx warehouse=xxxxx bulkload=yes

74 ! bulkunload=yes bl_internal_stage="user/test1";

NOTE: Libref BULK was successfully assigned as follows:

Engine: SASIOSNF

Physical Name: snowflake_dev

proc sql;

74 connect using bulk;

75 PUT file:///data/user_data/hari_thatavarthy/SASTABLE2.csv @~/staged;

___

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

76 COPY INTO mytable from @~/staged FILE_FORMAT = (FORMAT_NAME = 'my_csv_format');

____

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

Getting above error.

I am not sure , how to use PUT and COpy command. Could someone help me on this for Bulkload.

Re: How to use bulkload option for SAS/Interface to Snowflake

JBailey — Fri, 17 Jan 2020 19:17:02 GMT

Hi @karthic2612

It just so happens that I am writing an SGF paper on SAS/ACCESS Interface to Snowflake. I am using the following code to test some ideas. Keep in mind, you will need to create STAGES for some of this code to work. Check out the Snowflake DDL doc for details. The S3 example requires a valid bucket.

/* Snowflake SGF Paper - Experiments - Jeff Bailey */
/* Test the various Snowflake load techniques with largish data */
/* 1 GB */

/* Generate non-trivial data from performance experiments */
/* 4.5M = 1 GB */

data work.large_table_1GB;
   format dt date.;

   do i = 1 to 4500000;
      dt = i + 1;
      mynum1 = i;
      text1  = "abcdefghijklmnopqrstuvwxyz0123456789";
      mynum2 = i * 10;
	  text2  = "abcdefghijklmnopqrstuvwxyz0123456789";
      mynum3 = i * 10;
      text3  = "abcdefghijklmnopqrstuvwxyz0123456789";
      mynum4 = i * 10;
      text4  = "abcdefghijklmnopqrstuvwxyz0123456789";
      mynum5 = i * 10;
	  text5  = "abcdefghijklmnopqrstuvwxyz0123456789";
	  mynum6 = i * 10; 
      output;
	end;
run;

/* If you want to do inserts add INSERT=32000 and DBCOMMIT=0 to LIBNAME statement */
libname snow SNOW server="something.snowflakecomputing.com" 
db=TESTDB
warehouse=TESTWH
schema=THISUSER 
user=THISUSER 
pw=MyPassword123! 
preserve_tab_names=no;

/* Get a baseline for performance */
data snow.large_table_1GB;
   set large_table_1GB;
run;

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

/* Load via a Snowflake internal named stage */
/* Must create an internal stage in Snowflake (via SQL) for this to work. */
data snow.large_table_1GB_named_stage (bulkload=yes bl_stage="MY_STAGE" BL_COMPRESS=yes);
   set large_table_1GB;
run;

/* Load via a Snowflake table stage */
data snow.large_table_1GB_table_stage (bulkload=yes bl_internal_stage="table/sometable" BL_COMPRESS=yes);
   set large_table_1GB;
run;

/* Load via a Snowflake user stage */
data snow.large_table_1GB_user_stage (bulkload=yes bl_internal_stage="user/someuser" BL_COMPRESS=yes);
   set large_table_1GB;
run;

/* Load via an S3 bucket */
/* Must create an S3 bucket for this to work */
/* Must ssh into the Linux machine where SAS is running and obtain new credentials - kinit, getaswkey */
data snow.large_table_1GB_s3_stage (bulkload=yes bl_bucket="snowbulk-mybucket" bl_compress=yes);
   set large_table_1GB;
run;

Good luck,
Jeff

Re: How to use bulkload option for SAS/Interface to Snowflake

LinusH — Mon, 20 Jan 2020 13:46:14 GMT

Looking forward to the presentation @JBailey !

But do I undrestand it right, that SAS doesn't set up a Snowflake stage on the fly in the bulk-load process...?

Re: How to use bulkload option for SAS/Interface to Snowflake

JBailey — Mon, 20 Jan 2020 14:24:38 GMT

Hi @LinusH,

It depends on the STAGE. Table and User Stages are always present in Snowflake. Internal Named Stages must be created by the DBA, or someone with proper privileges. Configuring S3 as a Stage requires AWS configuration. It is a very interesting topic; I am beginning to write this section of the paper today.

<things_say_every_time_I_write_an_SGF_paper>I swear this is the last SGF paper I will ever write.</things_say_every_time_I_write_an_SGF_paper>

Best wishes,

Jeff