Snowflake upload local parquet file to table (in python)

This is a quick example of how to upload a parquet from local with python to snowflakes.

Connect

# Connecting to Snowflake
conn = snowflake.connector.connect(
    user=USER,
    password=PASSWORD,
    account=ACCOUNT,
)

Select database object and create a stage

database_text = "use database DATABASE_NAME" 
print(database_text)
conn.cursor().execute(database_text)

conn.cursor().execute("use schema SCHEMA_NAME")

conn.cursor().execute(
    "create or replace stage STAGE_NAME")

Put file in stage

Simply make filepath as a variable pointed to which file you want to upload.

filepath = 'Users/username/Downloads/df.parquet'

put_text = "PUT " + "file://" + filepath + " @STAGE_NAME"
print(put_text)

conn.cursor().execute(put_text)

File format permissions:

Create a file format if necessary. Parquet example here:

create file format if not exists FILE_FORMAT_NAME type = parquet

Then grant privileges to it:

grant all privileges on file format FILE_FORMAT_NAME to role ROLE_NAME

Create table using infer_schema

conn.cursor().execute(
    " CREATE OR REPLACE TABLE TABLE_NAME"
    " USING TEMPLATE("
    "     SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*)) WITHIN GROUP(ORDER BY ORDER_ID)"
    "     FROM TABLE("
    "         INFER_SCHEMA("
    "             LOCATION=> '@test_stage',"
    "             FILE_FORMAT=> 'FILE_FORMAT_NAME' )))")

table_name = type_of_data
stage_name = "test_stage"

Copy from stage into table

copy_query = f"""
    COPY INTO TABLE_NAME
    FROM @STAGE_NAME
    FILE_FORMAT = FILE_FORMAT_NAME
    MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
"""
conn.cursor().execute(copy_query)