SAS Support Communities

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;     ... View more

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; ... View more

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

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 ... View more

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 ... View more