Snowflake Upload Local Files from Python

snowflake_upload_local.py

import os
import snowflake.connector

ctx = snowflake.connector.connect(
    authenticator="snowflake",
    user=os.getenv("SNOWSQL_USER"),
    password=os.getenv("SNOWSQL_PWD"),
    account=os.getenv("SNOWSQL_ACCOUNT"),
    warehouse=os.getenv("SNOWSQL_WAREHOUSE")
    )

cs = ctx.cursor()

# Params
schemaName = "DEV.DEMO"
tblName = "UPLOAD_TEST"
filePath = "./data/Users_20191224.csv"

# Create the temp table
createStmt = f"""CREATE OR REPLACE TRANSIENT TABLE {schemaName}.{tblName} (
    title_name string,
    period_start_date_id string,
    period_end_date_id string,
    country_name string,
    region_name string,
    platform_name string,
    total_unique_users string,
    new_unique_users  string
);"""
cs.execute(createStmt)

# Upload the local file to the internal table stage
uploadStmt = f'put file://{filePath} @%{tblName};'
cs.execute(f"use schema {schemaName};") # or set the Db & Scheme for the whole connection directly
cs.execute(uploadStmt)

# Check the uploads
cs.execute(f"list @%{tblName};")
uploadedFiles = cs.fetchall()
uploadedFiles

# Import the data from the CSV to the table
importStmt = f"""COPY INTO {schemaName}.{tblName}
    PATTERN = '.*.gz'
    FORCE=TRUE
    ON_ERROR = CONTINUE
    FILE_FORMAT = (TYPE = CSV SKIP_HEADER=1);"""
cs.execute(importStmt)
rsult = cs.fetchall()
rsult

# Check the imported data
cs.execute(f"select * from {schemaName}.{tblName} limit 10;")
rsult = cs.fetchall()
rsult

cs.close()
ctx.close()

This is just an f-String available in Python 3 - it allows you to format it easily and insert variables into the string. The one above just inserts the values of “schemaName” and “tblName” into the string instead of hard-coding them.