如何将本地CSV文件（Local CSV file）加载至Snowflake表（Snowflake table），以及无需调度器每日自动加载本地CSV至Snowflake内部阶段（internal stage）的实现方法

Got it, let's tackle your two Snowflake CSV loading questions with practical, step-by-step solutions—no fluff, just what you need to get things working:

I'll focus on using SnowSQL (Snowflake's command-line client) here because it's the most flexible for local file operations, but I'll also include a quick GUI alternative if you prefer clicking over typing.

Prerequisites First #

• Install SnowSQL and set up your connection (run snowsql -a <your_account_id> -u <your_username> to walk through the setup wizard).
• Make sure your CSV matches the target table's schema (same column count, compatible data types) and uses a consistent delimiter (usually commas).
• Have the right permissions: CREATE TABLE (if you're making a new table), INSERT on the target table, and USAGE access to the database, schema, and any stages you use.

Step 1: Create Your Target Table (If It Doesn't Exist) #

First, define your table to match your CSV structure. For example:

CREATE OR REPLACE TABLE MY_DB.MY_SCHEMA.CUSTOMER_DATA (
    CUST_ID INT,
    FULL_NAME VARCHAR(100),
    SIGNUP_DATE DATE,
    MONTHLY_SPEND FLOAT
);

Step 2: Set Up a Temporary Internal Stage (Optional but Smart) #

Stages act as a middleman for files before loading into tables. A temporary stage gets auto-cleaned when your session ends, so no clutter:

CREATE OR REPLACE TEMPORARY STAGE MY_DB.MY_SCHEMA.CSV_UPLOAD_STAGE;

Step 3: Upload Your Local CSV to the Stage #

Use the PUT command to transfer your file from your machine to Snowflake. Adjust the path based on your OS:

• Unix/macOS: PUT file:///home/you/data/customers.csv @MY_DB.MY_SCHEMA.CSV_UPLOAD_STAGE AUTO_COMPRESS=FALSE;
• Windows: PUT file://C:\Users\you\data\customers.csv @MY_DB.MY_SCHEMA.CSV_UPLOAD_STAGE AUTO_COMPRESS=FALSE;

Pro tip: AUTO_COMPRESS=FALSE keeps the file uncompressed for easier debugging if something goes wrong.

Step 4: Copy the Staged File to Your Table #

Use COPY INTO to load the data. Tweak the file format options to match your CSV (like header presence or delimiter):

COPY INTO MY_DB.MY_SCHEMA.CUSTOMER_DATA
FROM @MY_DB.MY_SCHEMA.CSV_UPLOAD_STAGE/customers.csv
FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' HEADER = TRUE SKIP_HEADER = 1);

Quick GUI Alternative (Snowflake Web UI) #

If you hate command lines:

1. Go to Databases > [Your DB] > [Your Schema] > Tables > [Your Table]
2. Click Load Data
3. Follow the wizard to upload your CSV, map columns, and start the load—super straightforward.

You don't need a fancy third-party scheduler here—your OS already has a built-in tool for this. We'll pair a simple script (Bash/PowerShell/Python) with Windows Task Scheduler or Linux/macOS Cron to run the load daily.

High-Level Plan #

1. Write a script that handles:
   • Checking if the daily CSV exists
   • Uploading it to a permanent Snowflake internal stage (temp stages die with sessions, so we need something persistent)
   • Copying the staged file to your target table
   • Logging results (so you can debug if things break)
2. Schedule the script to run daily with your OS's built-in scheduler.

Example Bash Script (Linux/macOS)

Save this as snowflake_daily_sync.sh:

#!/bin/bash

# Snowflake connection details (use SnowSQL config instead of hardcoding passwords for security!)
ACCOUNT="your_account_id"
USER="your_username"
WAREHOUSE="your_warehouse"
DB="MY_DB"
SCHEMA="MY_SCHEMA"
STAGE="PERMANENT_CSV_STAGE"  # Must be a permanent stage
TABLE="CUSTOMER_DATA"
LOCAL_CSV="/home/you/daily_data/new_customers.csv"
LOG_FILE="/var/log/snowflake_sync.log"

# Check if CSV exists first
if [ ! -f "$LOCAL_CSV" ]; then
    echo "$(date): ERROR - CSV file not found at $LOCAL_CSV" >> $LOG_FILE
    exit 1
fi

# Upload CSV to stage
snowsql -a $ACCOUNT -u $USER -w $WAREHOUSE -d $DB -s $SCHEMA -q "PUT file://$LOCAL_CSV @$STAGE AUTO_COMPRESS=FALSE OVERWRITE=TRUE;"

# Copy to table
snowsql -a $ACCOUNT -u $USER -w $WAREHOUSE -d $DB -s $SCHEMA -q "COPY INTO $TABLE FROM @$STAGE/new_customers.csv FILE_FORMAT = (TYPE=CSV FIELD_DELIMITER=',' HEADER=TRUE SKIP_HEADER=1);"

# Log success
echo "$(date): Sync completed successfully" >> $LOG_FILE

Security note: Store your password in SnowSQL's config file (~/.snowsql/config) instead of hardcoding it here. You can also use key pair authentication for even better security.

Schedule with Cron (Linux/macOS)

1. Make the script executable: chmod +x snowflake_daily_sync.sh
2. Open the crontab editor: crontab -e
3. Add this line to run the script daily at 3 AM (adjust the time to fit your needs):

0 3 * * * /home/you/scripts/snowflake_daily_sync.sh

Use a tool like crontab.guru to generate the right cron expression if you need a different schedule (e.g., every weekday at midnight).

Example PowerShell Script (Windows)

Save this as Snowflake-DailySync.ps1:

# Snowflake connection details
$Account = "your_account_id"
$User = "your_username"
$Warehouse = "your_warehouse"
$DB = "MY_DB"
$Schema = "MY_SCHEMA"
$Stage = "PERMANENT_CSV_STAGE"
$Table = "CUSTOMER_DATA"
$LocalCsv = "C:\Users\you\daily_data\new_customers.csv"
$LogFile = "C:\logs\snowflake_sync.log"

# Check if CSV exists
if (-not (Test-Path $LocalCsv)) {
    "$(Get-Date): ERROR - CSV file not found at $LocalCsv" | Out-File -FilePath $LogFile -Append
    exit 1
}

# Upload CSV to stage
snowsql -a $Account -u $User -w $Warehouse -d $DB -s $Schema -q "PUT file://$LocalCsv @$Stage AUTO_COMPRESS=FALSE OVERWRITE=TRUE;"

# Copy to table
snowsql -a $Account -u $User -w $Warehouse -d $DB -s $Schema -q "COPY INTO $Table FROM @$Stage/new_customers.csv FILE_FORMAT = (TYPE=CSV FIELD_DELIMITER=',' HEADER=TRUE SKIP_HEADER=1);"

# Log success
"$(Get-Date): Sync completed successfully" | Out-File -FilePath $LogFile -Append

Schedule with Windows Task Scheduler

1. Open Task Scheduler > Create Basic Task
2. Name your task (e.g., "Snowflake Daily CSV Sync") and set the trigger to Daily at your desired time
3. Set the action to Start a program, browse to powershell.exe, and add arguments: -ExecutionPolicy Bypass -File "C:\Users\you\scripts\Snowflake-DailySync.ps1"
4. Under Settings, check "Run whether user is logged on or not" (make sure the user has access to the CSV file and can run SnowSQL)

Key Tips for Reliability #

• Use a permanent internal stage—temp stages only exist for the duration of a session, which won't work for scheduled tasks that run in fresh sessions.
• Add error handling to your script (like checking if the CSV exists) so you don't get silent failures.
• Enable logging so you can troubleshoot if the sync doesn't run as expected.

内容的提问来源于stack exchange，提问作者Paban Agarwal