error when passing a large table from SAS Fusion to Snowflake using bulkload

[mmawwamm]

We have a huge table need to be copied from SAS Server to Snowflake. If we use insert into SN.TEST table, it takes almost 3 hours to pass 80M rows with 31 columns.

I also try to use PUT command in EXECUTE () BY SN; it also give another type of error and will cause disconnection.

Then I try to add bulkload option into the libname statement, however, it still now works.

427 libname SN sasiosnf SERVER="XXXXXXXXXXX"
428 conopts="uid=XXXXXXXXXXX;authenticator=snowflake_jwt;
429 priv_key_file=XXXXXXXXXXXrsa_key.p8;
430 priv_key_file_pwd=XXXXXXXXXXX;"
431 Role="XXXXXXXXXXX"
432 warehouse="XXXXXXXXXXX"
433 database="XXXXXXXXXXX"
434 schema="XXXXXXXXXXX"
435 READBUFF=32000
436 INSERTBUFF=32000
437 DBCOMMIT=32000
438 CONNECTION=GLOBAL BULKLOAD=YES bl_internal_stage="table/TEST";
NOTE: Libref SN was successfully assigned as follows:
Engine: SASIOSNF
Physical Name: XXXXXXXXXXX
12 The SAS System 16:27 Friday, September 27, 2024

439
440
441 PROC SQL;
442 CONNECT USING SN;
443 Execute (REMOVE @test) by SN;
444 DELETE FROM SN.TEST;
NOTE: No data found/modified.
445 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.76 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 5457.46k
OS Memory 19880.00k
Timestamp 09/27/2024 04:27:27 PM
Step Count 92 Switch Count 0
Page Faults 0
Page Reclaims 676
Page Swaps 0
Voluntary Context Switches 21
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

446
447 PROC APPEND BASE = SN.TEST DATA = INPUT.CLAIMS;
448 RUN;

NOTE: Appending INPUT.CLAIMS to SN.TEST.
...
NOTE: FORCE is specified, so dropping/truncating will occur.
NOTE: There were 83056675 observations read from the data set INPUT.CLAIMS.
NOTE: 83056675 observations added.
NOTE: The data set SN.TEST has . observations and 31 variables.
ERROR: Error executing COPY command: Failed to decrypt. Check file key and master key.
NOTE: Statements not processed because of errors noted above.
13 The SAS System 16:27 Friday, September 27, 2024

NOTE: PROCEDURE APPEND used (Total process time):
real time 16:43.26
user cpu time 13:40.28
system cpu time 20.37 seconds
memory 6913.90k
OS Memory 20888.00k
Timestamp 09/27/2024 04:44:11 PM
Step Count 93 Switch Count 0
Page Faults 2
Page Reclaims 79594
Page Swaps 0
Voluntary Context Switches 6327
Involuntary Context Switches 6031
Block Input Operations 1049600
Block Output Operations 27968608

NOTE: The SAS System stopped processing this step because of errors.
NOTE: Due to ERROR(s) above, SAS set option OBS=0, enabling syntax check mode.
This prevents execution of subsequent data modification statements.

[Patrick]

What happens if you subset your source table to a 1000 rows and load these? Does this work?

For the Proc Append:

I can’t see the Force option specified but it’s mentioned in the Log. Is this really the log from the Proc Append statement you shared?

Did you try bulk load? Do you really want to commit after every 32K rows? Or could you eventually use dbcommit=0?

Is the target table empty? Are there a lot of indexes and constraints on the table (eventually disable them prior to the load and then re-enable them once loaded)?

[Kurt_Bremser]

Instead of using PROC APPEND, upload the CLAIMS dataset to a temporary table in the database, and run the INSERT in explicit pass-through.

[mmawwamm]

It does work for one table. I have 3 claim tables, with the same structure. When I use the same code upload the other two tables, I got copy error again.

428 libname SNOW sasiosnf SERVER="XXXX"
429 conopts="uid=&SNOWACNT.;authenticator=snowflake_jwt;
430 priv_key_file=XXXX/rsa_key.p8;
431 priv_key_file_pwd=&KeyPW.;"
432 Role="XXXX_ROLE"
433 warehouse="XXXX_WH"
434 database="XXXX_DB"
12 The SAS System 13:38 Monday, September 30, 2024

435 schema="XXXX"
436 READBUFF=32000
437 INSERTBUFF=32000
438 DBCOMMIT=32000
439 CONNECTION=GLOBAL BULKLOAD = YES BL_INTERNAL_STAGE = "SAS_LOAD";
NOTE: Libref SNOW was successfully assigned as follows:
Engine: SASIOSNF
Physical Name: XXXX
440
441 PROC SQL;
442 CONNECT USING SNOW;
443 Execute (REMOVE @Sas_LOAD) by SNOW;

SNOWFLAKE_1: Executed: on connection 5
REMOVE @Sas_LOAD

SNOWFLAKE: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement.
444 DELETE FROM SNOW.TEST;

SNOWFLAKE_2: Prepared: on connection 5
SELECT * FROM "XXXX"."TEST"


SNOWFLAKE_3: Executed: on connection 5
delete from "XXXX"."TEST"

SNOWFLAKE: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement.
SNOWFLAKE: COMMIT performed on connection 5.
NOTE: No data found/modified.
445 EXECUTE (CREATE OR REPLACE TEMPORARY TABLE CLAIM (
446 COL1 VARCHAR(1000),
...
477 )
478 on commit preserve rows) by SNOW;

SNOWFLAKE_4: Executed: on connection 5
CREATE OR REPLACE TEMPORARY TABLE CLAIM ( ...) on commit preserve rows

479 INSERT INTO SNOW.CLAIM
480 (
481 COL1,
482 ...
512 )
513 SELECT
514 COL1,
515 ...

545 FROM DATA.claim1;

SNOWFLAKE_5: Prepared: on connection 5
SELECT * FROM "XXXX"."CLAIM"

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.

SNOWFLAKE_6: Prepared: on connection 5
SELECT * FROM "XXXX"."CLAIM"

SNOWFLAKE: snudesc, failed to match primary key column(s)
NOTE: 25686270 rows were inserted into SNOW.CLAIM.

SNOWFLAKE_7: Executed: on connection 5
PUT 'file:///data1/saswork/SAS_util0001001A3C0C_apslp200158.XXX.com/SASSNBL_55821E19-CECE-AD46-9D5C-65B3B78074C6-00.dat'
'@SAS_LOAD/' AUTO_COMPRESS = FALSE PARALLEL=4 OVERWRITE = TRUE


SNOWFLAKE_8: Executed: on connection 5
PUT 'file:///data1/saswork/SAS_util0001001A3C0C_apslp200158.XXX.com/SASSNBL_55821E19-CECE-AD46-9D5C-65B3B78074C6-01.dat'
'@SAS_LOAD/' AUTO_COMPRESS = FALSE PARALLEL=4 OVERWRITE = TRUE


SNOWFLAKE_9: Executed: on connection 5
COPY INTO "XXXX"."CLAIM"
("COL1",...) FROM '@SAS_LOAD/' FILE_FORMAT=(
TYPE=CSV FIELD_DELIMITER='\007' COMPRESSION=NONE ) FORCE=TRUE FILES = (
'SASSNBL_55821E19-CECE-AD46-9D5C-65B3B78074C6-00.dat','SASSNBL_55821E19-CECE-AD46-9D5C-65B3B78074C6-01.dat' )

SNOWFLAKE: COMMIT performed on connection 5.

15 The SAS System 13:38 Monday, September 30, 2024

SNOWFLAKE_10: Executed: on connection 5
REMOVE '@SAS_LOAD/SASSNBL_55821E19-CECE-AD46-9D5C-65B3B78074C6-00.dat'


SNOWFLAKE_11: Executed: on connection 5
REMOVE '@SAS_LOAD/SASSNBL_55821E19-CECE-AD46-9D5C-65B3B78074C6-01.dat'

SNOWFLAKE: COMMIT performed on connection 5.
546 EXECUTE(INSERT INTO TEST
547 (COL1,
...)
578 SELECT COL1,...

609 FROM CLAIM) by SNOW;

SNOWFLAKE_12: Executed: on connection 5
INSERT INTO TEST (COL1, ...)
SELECT COL1, ... FROM CLAIM

SNOWFLAKE: 25686270 row(s) affected by INSERT/UPDATE/DELETE or other statement.
610 DISCONNECT FROM SNOW;
611 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 8:22.21
user cpu time 4:47.14
system cpu time 14.26 seconds
memory 7542.53k
OS Memory 21400.00k
Timestamp 09/30/2024 01:46:54 PM
Step Count 92 Switch Count 0
Page Faults 5
Page Reclaims 389612
Page Swaps 0
Voluntary Context Switches 35392
Involuntary Context Switches 10794
Block Input Operations 7158128
Block Output Operations 12381184

[mmawwamm]

I try to use it load the other table and got error.

428 libname SNOW sasiosnf SERVER="XXXX"
429 conopts="uid=&SNOWACNT.;authenticator=snowflake_jwt;
430 priv_key_file=XXXX/rsa_key.p8;
431 priv_key_file_pwd=&KeyPW.;"
432 Role="XXXX_ROLE"
433 warehouse="XXXX_WH"
434 database="XXXX_DB"
12 The SAS System 11:31 Monday, September 30, 2024

435 schema="XXXX"
436 READBUFF=32000
437 INSERTBUFF=32000
438 DBCOMMIT=32000
439 CONNECTION=GLOBAL BULKLOAD = YES BL_INTERNAL_STAGE = "SAS_LOAD";
NOTE: Libref SNOW was successfully assigned as follows:
Engine: SASIOSNF
Physical Name: XXXX

440
441 PROC SQL;
442 CONNECT USING SNOW;
443 Execute (REMOVE @Sas_LOAD) by SNOW;

SNOWFLAKE_1: Executed: on connection 5
REMOVE @Sas_LOAD

SNOWFLAKE: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement.
444 DELETE FROM SNOW.TEST;

SNOWFLAKE_2: Prepared: on connection 5
SELECT * FROM "XXXX"."TEST"


SNOWFLAKE_3: Executed: on connection 5
delete from "XXXX"."TEST"

SNOWFLAKE: 25686270 row(s) affected by INSERT/UPDATE/DELETE or other statement.
SNOWFLAKE: COMMIT performed on connection 5.
445 EXECUTE (CREATE OR REPLACE TEMPORARY TABLE CLAIM (
446 COL1 VARCHAR(1000),
447 ...

477 )
478 on commit preserve rows) by SNOW;

SNOWFLAKE_4: Executed: on connection 5
CREATE OR REPLACE TEMPORARY TABLE CLAIM ( COL1 VARCHAR(1000), ... ) on commit preserve rows

479 INSERT INTO SNOW.CLAIM
480 (
481 COL1,
482 ...
512 )
513 SELECT
514 COL1,
515 ...

545 FROM DATA.claim2;

SNOWFLAKE_5: Prepared: on connection 5
SELECT * FROM "XXXX"."CLAIM"

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.

SNOWFLAKE_6: Prepared: on connection 5
SELECT * FROM "XXXX"."CLAIM"

SNOWFLAKE: snudesc, failed to match primary key column(s)
NOTE: 83056675 rows were inserted into SNOW.CLAIM.

SNOWFLAKE_7: Executed: on connection 5
PUT 'file:///data1/saswork/SAS_util0001000DA128_apslp200161.XXX.com/SASSNBL_42FF7AE0-3064-1A4E-93FF-4448A194848E-00.dat'
'@SAS_LOAD/' AUTO_COMPRESS = FALSE PARALLEL=4 OVERWRITE = TRUE


SNOWFLAKE_8: Executed: on connection 5
PUT 'file:///data1/saswork/SAS_util0001000DA128_apslp200161.XXX.com/SASSNBL_42FF7AE0-3064-1A4E-93FF-4448A194848E-01.dat'
'@SAS_LOAD/' AUTO_COMPRESS = FALSE PARALLEL=4 OVERWRITE = TRUE


SNOWFLAKE_9: Executed: on connection 5
COPY INTO "XXXX"."CLAIM"
("COL1",...) FROM '@SAS_LOAD/' FILE_FORMAT=(
TYPE=CSV FIELD_DELIMITER='\007' COMPRESSION=NONE ) FORCE=TRUE FILES = (
'SASSNBL_42FF7AE0-3064-1A4E-93FF-4448A194848E-00.dat','SASSNBL_42FF7AE0-3064-1A4E-93FF-4448A194848E-01.dat' )


SNOWFLAKE_10: Executed: on connection 5
REMOVE '@SAS_LOAD/SASSNBL_42FF7AE0-3064-1A4E-93FF-4448A194848E-00.dat'
15 The SAS System 11:31 Monday, September 30, 2024

SNOWFLAKE_11: Executed: on connection 5
REMOVE '@SAS_LOAD/SASSNBL_42FF7AE0-3064-1A4E-93FF-4448A194848E-01.dat'

ERROR: Error executing COPY command: Failed to decrypt. Check file key and master key.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
546 EXECUTE(INSERT INTO TEST
547 (COL1,
...)
578 SELECT COL1,

...

609 FROM CLAIM) by SNOW;
NOTE: Statement not executed due to NOEXEC option.
610 DISCONNECT FROM SNOW;
NOTE: Statement not executed due to NOEXEC option.
611 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Due to ERROR(s) above, SAS set option OBS=0, enabling syntax check mode.
This prevents execution of subsequent data modification statements.
NOTE: PROCEDURE SQL used (Total process time):
real time 18:55.14
user cpu time 16:14.11
system cpu time 44.04 seconds
memory 7464.15k
OS Memory 21400.00k
Timestamp 09/30/2024 11:51:14 AM
Step Count 92 Switch Count 0
Page Faults 2
Page Reclaims 325557
Page Swaps 0
Voluntary Context Switches 59760
Involuntary Context Switches 83206
Block Input Operations 10482640
Block Output Operations 39972888

[Kurt_Bremser]

You do not upload the dataset to its own temporary database table, you try to use it directly in the INSERT. As you already noticed, this does not work.

[mmawwamm]

Would you please show me some sample code so that I could understand better?

[Kurt_Bremser]

If I interpret your previous post correctly, you upload one dataset (which works), and then try to insert a second and third dataset. Use PROC APPEND or a DATA step first on the SAS side (with no use of the database), and then upload the single resulting dataset.

[mmawwamm]

I did not use proc append this time. I create SNOWFLAKE TEMP Table, insert rows to temp table, and then insert rows to target table from the temp table.

It works for one claim table, but failed for the other two.

Here is my code, if I change the claim1 in red into claim2 or claim3. Then I get error.

libname SNOW sasiosnf SERVER="XXXX"

conopts="uid=&SNOWACNT.;authenticator=snowflake_jwt;

priv_key_file=/hpsasfin/users/&SYSUSERID./keys/rsa_key.p8;

priv_key_file_pwd=&KeyPW.;"

Role="XXXX_ROLE"

warehouse="XXXX_WH"

database="XXXX_DB"

schema="XXXX"

READBUFF=32000

INSERTBUFF=32000

DBCOMMIT=32000

CONNECTION=GLOBAL BULKLOAD = YES BL_INTERNAL_STAGE = "SAS_LOAD";

PROC SQL;

CONNECT USING SNOW;

Execute (REMOVE @Sas_LOAD) by SNOW;

DELETE FROM SNOW.TEST;

EXECUTE (CREATE OR REPLACE TEMPORARY TABLE CLAIM (

COL1 VARCHAR(1000),

...

) 

on commit preserve rows) by SNOW;

INSERT INTO SNOW.CLAIM 

(

COL1,

... 

)

SELECT 

COL1,

...

FROM DATA.claim1;

EXECUTE(INSERT INTO TEST

(COL1,

...)

SELECT COL1,

...

FROM CLAIM) by SNOW;

DISCONNECT FROM SNOW;

QUIT;

[LinusH]

If you execute using debug, you <might> get some more information out:

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

What cloud does Snowflake reside on?

And out of curiosity, what i SAS Fusion?

I stumbled upon this , not sure if it's applicable:

Multi Row Insert fails with "Failed to decrypt. Check file key and master key." · Issue #374 · snowf...

A track to SAS tech support could be a good idea.

[mmawwamm]

I do think it might be the issue. Because I also try the following. I could see the .csv file had been loaded to Snowflake internal stage. However, when I try COPY INTO, I get "100109 (XX000): Failed to decrypt. Check file key and master key." in Snowflake WebUI. Or "ERROR: Error executing COPY command: Failed to decrypt. Check file key and master key." in SAS EG.

PROC SQL;
CONNECT USING SNOW;
Execute (REMOVE @Sas_LOAD) by SNOW;
DELETE FROM SNOW.TEST;
Execute (PUT file:///XXXX/data/claim1.csv @Sas_LOAD
AUTO_COMPRESS = FALSE PARALLEL=4 OVERWRITE = TRUE) by SNOW;
Execute (COPY INTO TEST from @Sas_LOAD FORCE = TRUE) by SNOW;
QUIT;

[mmawwamm · Accepted solution]

Finally I found this solution:

(resolved auto_compress not working, and potential invalid UTF8 detected in string error)

Run following in Snowflake first (one-time creation):

CREATE OR REPLACE FILE FORMAT LOAD_FORMAT

  TYPE = 'CSV'

  FIELD_DELIMITER = ','

  SKIP_HEADER = 1

  FIELD_OPTIONALLY_ENCLOSED_BY = '"'

  REPLACE_INVALID_CHARACTERS = TRUE;

CREATE OR REPLACE STAGE SAS_LOAD

  FILE_FORMAT = LOAD_FORMAT;

The use following SAS code to upload:

x gzip -cf /myfolder/myfile.csv > /myfolder/myfile.csv.gz;

PROC SQL;

   CONNECT USING SNOW;

   Execute (REMOVE @Sas_LOAD) by SNOW;

   DELETE FROM SNOW.MYTABLE;

   Execute (PUT file:///myfolder/myfile.csv.gz @Sas_LOAD

AUTO_COMPRESS = FALSE PARALLEL=4 OVERWRITE = TRUE) by SNOW;

   Execute (COPY INTO MYTABLE from @Sas_LOAD/myfile.csv.gz FORCE = TRUE ON_ERROR = CONTINUE) by SNOW;

QUIT;

x rm /myfolder/myfile.csv.gz;