Loading data in Snowflake using bind variables

Question

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?

What error are you seeing? what does your SQL statement evaluate to after python does its substitutions? Why do you want to create a stage dynamically instead of using a table stage? — Simon D

demircioglu demircioglu · Accepted Answer · 2021-02-25T18:48:24

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"""

Loading data in Snowflake using bind variables

1 Answers