Load CSV from Local to Snowflake

Snowflake is the most famous database and CSV is the most common file format to store data. In this article, we will figure out how easy it is to load the data from a CSV file stored at your local machine into the Snowflake.

Snowflake refers to the file location as a STAGE. If we are referring to the location of the files stored in the cloud in this case a STAGE is called EXTERNAL STAGE

If files are locally available in the system in that case stage is called an INTERNAL STAGE .

let's create a CSV file with two columns NAME and AGE and store the CSV file in the Document folder.

Now we need to create a STAGE for accessing the CSV file stored in the Document folder and a table in snowflake for loading the CSV data into it.

-- Stage creation DDL 
CREATE STAGE RAW.SAMPLE_LOAD_CSV
file_format =(TYPE = 'CSV'
              field_delimiter = ','
              skip_header = 1
             SKIP_BLANK_LINES = TRUE)
COPY_OPTIONS=(ON_ERROR = 'SKIP_FILE')
COMMENT='This satge loads the student and their age into the snowflake table';

--Table creation DDL
CREATE OR REPLACE TABLE RAW.SAMPLE_DATA
(NAME VARCHAR,
 AGE INTEGER )

By running SHOW STAGES the command you can verify whether the stage has been created successfully or not.

In this stage, I have used the most common options like SKIP_HEADER, etc but snowflake provides tons of options that can be used while creating the INTERNAL and EXTERNAL stages (Have a look at this link https://docs.snowflake.com/en/sql-reference/sql/create-stage.html#copy-options-copyoptions ).

For uploading the CSV into the Snowflake open any ide ( e.g Dbeaver), SnowSQL CLI, etc, and run the PUT command.

The syntax for the PUT command:

PUT file://<path_to_file>/<filename> internalStage
    [ PARALLEL = <integer> ]
    [ AUTO_COMPRESS = TRUE | FALSE ]
    [ SOURCE_COMPRESSION = AUTO_DETECT | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE ]
    [ OVERWRITE = TRUE | FALSE ]

Run the below PUT command to load the CSV into Snowflake Stage.

PUT 'file:///Users/datageeks/Documents/sample.csv' --file location

@RAW.SAMPLE_LOAD_CSV   --STAGE NAME

OVERWRITE = TRUE

PARALLEL = 1 --If file is huge then you can increase this number

Verify if files are staged into the STAGE or not.

LIST @RAW.SAMPLE_LOAD_CSV --Stage name

Use COPY INTO command to load the staged data into the Snowflake Table.

COPY INTO "DEV_DWH"."RAW"."SAMPLE_DATA" FROM @RAW.SAMPLE_LOAD_CSV

Summary In this article, We have seen how to load the data from a local machine into the Snowflake Database, If you think that this article is informative and helped you with what you are looking then give a clap and follow my medium account( datageeks.medium.com ) and feel free to write in comments if you have any doubts about this topic.

What Next? Check out unloading the data from Snowflake to S3 https://datageeks.medium.com/export-data-from-snowflake-to-s3-3ce408659734