Snowflake - Upsert from Staged Files
I'm Working on Building a Bulk Upsert Functionality for Personal Use. I'm Currently Uploading Multiple Files into S3, and from There I'm Creating a Stage Using...
I'm working on building a BULK UPSERT functionality for personal use. I'm currently uploading multiple files into S3, and from there I'm creating a stage using CREATE STAGE command.
CREATE OR REPLACE TEMPORARY STAGE {stage}
URL={s3_dir}
CREDENTIALS=(aws_key_id='{aws_key_id}' aws_secret_key='{aws_secret_key}')
{file_format}
Now, I'd like to use this stage to do an UPSERT in the destination table in Snowflake. Can somebody provide an example of the same?
TIA
2 Answers
Snowflake uses MERGE command for UPSERT. I believe the example you're after can be found here:
and here's the actual merge doc:
Apparently, MERGE supports following kind of DB objects to move data from source to destination. Sources supported are:
- Internal stage
- External stage
- Table
- Subquery
Following is the SQL snippet that I've used in the codebase:
CREATE OR REPLACE TEMPORARY STAGE DUMMY_STAGE
FILE_FORMAT=(
TYPE=CSV
COMPRESSION=AUTO
FIELD_DELIMITER='^'
VALIDATE_UTF8=TRUE
EMPTY_FIELD_AS_NULL=TRUE
ENCODING=UTF8
ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE
RECORD_DELIMITER='\r\n'
ESCAPE=NONE
ESCAPE_UNENCLOSED_FIELD=NONE
FIELD_OPTIONALLY_ENCLOSED_BY='"'
NULL_IF = ('0000-00-00', '', 'None')
);
PUT file://data_files/random.txt @DUMMY_STAGE;
MERGE INTO DUMMY_A
USING
(
SELECT
$1 foo
, $2 bar
FROM @DUMMY_STAGE
) DUMMY_TMP
ON
DUMMY_A.FOO=DUMMY_TMP.FOO
WHEN MATCHED THEN
UPDATE SET
foo = DUMMY_TMP.foo
WHEN NOT MATCHED THEN
INSERT
(
foo
, bar
)
VALUES
(
DUMMY_TMP.foo
, DUMMY_TMP.bar
)
;
Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.
