topic How to use Snowflake BULKUPLOAD and BL_NUM_READ_THREADS in libname and connections to Snowflake. in SAS Programming

How to use Snowflake BULKUPLOAD and BL_NUM_READ_THREADS in libname and connections to Snowflake.

Vic1809 — Wed, 03 Jan 2024 20:34:52 GMT

I would like to be able to use BULKUPLOAD and BL_NUM_READ_THREADS when connected to Snowflake. Sample code follows.

%let cmsid = &_METAUSER;

proc sql noprint;

connect to snow as x1 (DATABASE=ADM_PRD DSN=Snowflake_OPI

CONOPTS="authenticator=https://xxx.xxx.xxx?snowflake=okta" role=OPIC_ADM_OPI_RWC_P

SCHEMA=CMS_ADM_OPI authdomain=SNOW_Auth bl_internal_stage="user/&cmsid" BULKUNLOAD=YES);

EXECUTE (create or replace table FIVE_YEARS_HOS as … (many more fields specified)

SAS will not complain when I submit this code and it completes in 7.87s. The query never shows up in the Snowflake Classic UI History. Without the BULKUNLOAD=YES option the query runs for 32min 30s. and the table is created.

Re: How to use Snowflake BULKUPLOAD and BL_NUM_READ_THREADS in libname and connections to Snowflake.

SASKiwi — Wed, 03 Jan 2024 21:14:28 GMT

Where is your source data coming from? A SAS table or some other source? If its a SAS table then I suggesting trying an APPEND. If the table doesn't already exist it should be created:

libname MySnow snow DATABASE=ADM_PRD  DSN=Snowflake_OPI 
  CONOPTS="authenticator=https://xxx.xxx.xxx?snowflake=okta" role=OPIC_ADM_OPI_RWC_P
  SCHEMA=CMS_ADM_OPI authdomain=SNOW_Auth bl_internal_stage="user/&cmsid" insertbuff = 10000;

proc datasets library = MySnow nolist;
  append base = MySnowTable data = WORK.MySASTable;
run;
quit;

Once it is working without bulk loading then try with options BULKLOAD = Y and BULKUNLOAD = Y.

Re: How to use Snowflake BULKUPLOAD and BL_NUM_READ_THREADS in libname and connections to Snowflake.

LinusH — Fri, 05 Jan 2024 16:31:14 GMT

Assuming that BULKUPLOAD in the subject is a typo for BULKUNLOAD.

I am a little confused, and probably due to ignorance.

SAS doc ties these options to LIBNAME statement.

I guess they could work wit an PROC SQL CONNECT TO as well.

But code within an excute block is usually executed only in the target DBMS, so I would assume that your SAS specific options shouldn't affect the execution.

It would help to see more of the code inside the EXECUTE block, but perhaps not all column names...

Why are you using CONNECT TO? What happens if you try to use a libref instead? Then you could use these option to track Snowflake behaviour:

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

If there's any unclarity in the doc or connection behaviour, you might want to try contacting SAS tech support.