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 like CSV, JSON, XML, Avro, ORC, Parquet.

This approach is limited 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, click the DATABSES icon present at the top ribbon. Click the table name where you want to upload data. It shows the number of columns and definition.

The following screenshot shows the Load Data functionality −

Reverify the sample file with respect to columns. Click 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 click the Next button.

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

Now create the File Format by clicking + 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 utilized in the given schema only.

• Format Type − Name of file format.

• Column separator − If CSV file is separated, provide file delimiter.

• Row separator − How to identify a new line.

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

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

The following screenshot displays the above details −

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

After loading the results, you will get the summary, as shown below. Click 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 click on Download as shown below −

Click the CLI Client (snowsql) and click the Snowflake Repository as shown in the following screenshot −

User can move to bootstrap → 1.2 → windows_x86_64 → click 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 line −

<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.