This video will familiarize you with the fundamentals
of loading data into Snowflake tables.
Snowflake supports four options for loading data.
You can use the web interface to load limited amounts of data.
You can bulk load large amounts of data using SQL commands and SnowSQL--
the Snowflake command-line interface.
You can also automate bulk loading using Snowpipe.
And you can use third-party tools to bulk load data from external sources.
This video focuses on SQL,
but we will touch on the other three methods at the end.
Using SQL, you can bulk load data from any delimited plain text files,
such as comma delimited CSV files.
You can also bulk load semi-structured data
from JSON, Avro, Parquet, or ORC files.
However, this video focuses on loading from CSV files.
Bulk loading is performed in two phases.
First, you upload your data files to a location
where Snowflake can access the files.
This is referred to as staging your files.
Then you load the data from these staged files into your table.
Snowflake lets you stage files in internal locations called Stages.
Each table end user has a Stage.
Snowflake also supports creating named Stages.
Internal Stages enable convenient and secure storage of data files
without requiring any external resources.
However, if your data files are already staged in a supported cloud storage location,
you can skip Phase One and load directly from these external locations.
You just need to supply the URL for the location,
as well as access credentials If the location is protected.
You can also create named stages that point to your external locations.
Phase Two requires a running virtual warehouse.
The warehouse extracts the data from each file
and inserts it as rows in the table.
Warehouse size can impact loading performance.
When loading large numbers of files or large files,
you may want to choose a larger warehouse.
We will now demonstrate, using SQL and SnowSQL,
how to load CSV files from a local machine into a table named Contact
in the demo DB database.
We will use a named internal stage to store the files before loading.
All the files used in this video are available to try for yourself.
See the Snowflake documentation for step-by-step instructions.
The table was created using the following SQL,
and the files contain records like this.
Notice that the columns in the tables correspond to the fields in the records in the files.
We'll start by creating an internal stage named CSV Files.
Next we'll execute a Put command to stage the files in CSV files.
Let's take a closer look at the command
File specifies the full directory path and names of the files to stage.
Notice that we use a wild-card to load multiple files.
@ symbol specifies where to stage the files.
By default, the Put command compresses data files using Gzip.
To see that the files are staged, you can use the List command.
Now we're ready to load data from the stage files into the Contacts table.
First, specify a virtual warehouse to use,
then use the Copy command to load the files.
Looking more closely at this command, "Into" specifies the table where the data will be loaded.
"From" identifies the location of the staged files, in this case the CSV files stage.
"Pattern" specifies the data files to load.
Here we're loading from data files with names that include the numbers 1 through 4.
"On Error" tells the command what to do when it encounters errors in the file.
Snowflake provides powerful options for error handling while data loading,
which will be covered in a later video.
The load was successful. You can now query your table using SQL.
As we mentioned earlier, you can use Snowpipe for bulk loading data,
particularly from files staged in external locations.
Snowpipe uses the "Copy" command,
but with additional features that let you automate the process.
Snowpipe also eliminates the need for a virtual warehouse.
Instead it uses internal compute resources to continuously load data as files are staged.
And you are charged only for the actual data loaded.
We also mentioned using third-party tools to bulk load data. Often referred to as ETL or ELT,
Snowflake supports an ever-expanding ecosystem of applications and services for loading data
from a wide range of external sources.
If you are interested in using an ETL tool but haven't selected one yet,
Snowflake Partner Connect lets you quickly and easily sign up for free trials with selected partners.
The fourth and final option for loading data
is the data loading wizard in the Snowflake web interface.
Simply select the table you want to load, and click the load button.
The wizard simplifies loading by combining the two phases into a single operation,
and automatically deleting all the stage files after loading.
However, the wizard is intended for loading only small numbers of files
containing small amounts of data.
For large amounts of data, it's best to use one of the other options.
This concludes this quick introduction on data loading.
For more information about data loading, see the Snowflake documentation.
Thanks for watching.