CODEWITHSUNDEEP
snowflake-cloud-data-platform
Joris Van den Borre
Joris Van den Borre

Reputation: 11

Loading data in Snowflake using bind variables

We're building dynamic data loading statements for Snowflake using the Python interface.

We want to create a stage at query runtime, and use that stage in a subsequent statement. Table and stage names are dynamic using bind variable.

Yet, it doens't seem like we can find the correct syntax as we tried everything on https://docs.snowflake.com/en/user-guide/python-connector-api.html

COPY INTO IDENTIFIER( %(table_name)s )(SRC, LOAD_TIME, ROW_HASH) FROM (SELECT t.$1, CURRENT_TIMESTAMP(0), MD5(t.$1) FROM "'%(stage_name)s'" t) PURGE = TRUE;

Is this even possible? Does it work for anyone?

Upvotes: 1

Views: 318

Answers (1)

demircioglu
demircioglu

Reputation: 3465

Your code does not create stage as you mentioned, and you don't need create a stage, instead use table stage or user stage. The SQL below uses table stage.

You also need to change your syntax a little and use more pythonic way : f-strings

sql = f"""COPY INTO {table_name} (SRC, LOAD_TIME, ROW_HASH) 
FROM (SELECT t.$1, CURRENT_TIMESTAMP(0), MD5(t.$1) FROM @%{table_name} t)
PURGE = TRUE"""

Upvotes: 0

Related Questions