Snowflake - Load Data From Files

In a database, Schemas are created which are logical grouping of tables. Tables contain columns. Tables and columns are low-level and the most important objects of a database. Now, the most important function of table & columns is storing the data.

In this chapter, we will discuss about how to store data into table and columns in Snowflake.

Snowflake provides the user two ways to store data into a table and corresponding columns using user interface and SQL query.

Load Data into Tables and Columns using Snowflake s UI

In this section, we will discuss the steps that should be followed to load data into a table and its corresponding columns using a file pke CSV, JSON, XML, Avro, ORC, Parquet.

This approach is pmited to load a small amount of data up to 50 MB.

Create a sample file in any of the format. While creating the file, make sure the number of columns in the file and the table should match, otherwise the operation will fail while loading the data.

In TEST_DB.TEST_SCHEMA.TEST_TABLE, there are three columns: ID, NAME, and ADDRESS.

The following sample data is created in "data.csv" −

Now, cpck the DATABSES icon present at the top ribbon. Cpck the table name where you want to upload data. It shows the number of columns and definition.

The following screenshot shows the Load Data functionapty −

Reverify the sample file with respect to columns. Cpck the Load Table button at the top of column names. It pops-up Load Data dialog box. At the first screen, select Warehouse Name and cpck the Next button.

On the next screen, select a file from your local computer by cpcking Select Files. Once the file is uploaded, you can see the file name as shown in following screenshot. Cpck the Next button.

Now create the File Format by cpcking + sign as shown in the following screenshot −

It pops-up the Create File Format dialog box. Enter the following details −

Name − Name of file format.

Schema Name − The create file format can be utipzed in the given schema only.

Format Type − Name of file format.

Column separator − If CSV file is separated, provide file depmiter.

Row separator − How to identify a new pne.

Header pnes to skip − If header is provided, then 1 else 0.

Other things can be left as it is. Cpck the Finish button after entering details.

The following screenshot displays the above details −

Select the File Format from the dropdown and cpck Load as shown in the following screenshot −

After loading the results, you will get the summary, as shown below. Cpck the OK button.

To view the data, run the query "SELECT * from TEST_TABLE". In the left panel also, user can see DB, Schema and table details.

Load Data into Tables and Columns using SQL

To load data from a local file, you can take the following steps −

Upload a file into Snowflake s stage using SnowSQL, a plugin provided by Snowflake. To perform it, go to help and cpck on Download as shown below −

Cpck the CLI Cpent (snowsql) and cpck the Snowflake Repository as shown in the following screenshot −

User can move to bootstrap → 1.2 → windows_x86_64 → cpck to download latest version.

The following screenshot displays the above step −

Now, install the downloaded plugin. After installation, open CMD in your system. Run the following command to check connection −

snowsql -a <account_name> -u <username>

It will ask for password. Enter your snowflake password and press ENTER. You will see successful connection. Now use the command pne −

<username>#<warehouse_name>@<db_name>.<schema_name>

Now use the following command to upload the file into snowflake stage −

PUT file://C:/Users/*******/Documents/data.csv @csvstage;

Don t forget to put the "semicolon" sign at end, else it will run forever.

Once the file got uploaded, user can run the following command into Worksheet −

COPY INTO "TEST_DB"."TEST_SCHEMA_1"."TEST_TABLE" FROM @/csvstage ON_ERROR =  ABORT_STATEMENT  PURGE = TRUE

Date will be loaded into the table.