This blog comes from the Snowflake Architecture and SQL book by Tom Coffing and David Cook.
This blog is a multi-part series that will arm you with extensive knowledge about data loading. In our last blog, we loaded data using a user stage. Today, we will use a table stage to load data.
What do I know about data loading into Snowflake? First, check out the picture below where Nexus is migrating from Oracle to Snowflake. Also, check out the many different systems in the systems tab on the left. Nexus automates migrations between every system. First, users pick the source system, the tables they want to move, and the target system. Then, Nexus converts the table structures (DDL) and the data types, builds the load scripts, and automates everything.
The picture above shows Nexus migrating 24 tables from Oracle to Snowflake. Nexus migrates every system in the systems tree (on the left) to every system.
Here is a video showing exactly how we automate data migration to Snowflake from any data source. https://www.youtube.com/watch?v=_9ABivdwJPk.
Now back to the blog! In the picture below, you can see that Snowflake uses an @% in front of a table's name when referring to it in a Snowflake table stage. This is because every table has a table stage that uses the exact table name, except it begins with @%.
In the picture below, you see three commands. The first creates a table with the name PIVOT_TEST2, which is an empty table. We then have the PUT command, which puts the data from our flat file to the table stage in Snowflake. The final query does a SELECT from the table stage. Yes, one of the reasons we have the @% prepended on a table name is to query the staging table. Unfortunately, the put command does not work on the Snowflake browser query tool.
The picture above shows Nexus using the PUT command to place the flat file on our PC to the Snowflake table stage.
We are at the most important part of the Snowflake data loading blog post for table stages. The picture below runs through the entire process of loading a table using a table stage. First, we create an empty table on Snowflake (named PIVOT_TEST). Second, we use the PUT command to place our flat file into the Snowflake table stage using the @%PIVOT_TEST. Third, and optionally, we can LIST @%PIVOT_TEST to see if it exists. The fourth part is the big move because we use the COPY INTO command to populate the table from the stage. And finally, we can SELECT from the table because it is fully populated on Snowflake.
The picture above shows Nexus using the PUT command to place the flat file on our PC to the Snowflake table stage. We then run the COPY INTO command to load the table from the table stage.
You can also see from the example below that we can use the COPY INTO command with different options. Below, we specify the file format as CSV, the GZIP pattern, and tell Snowflake what to do when an error occurs.
Download a CSV from my Website to Practice Loading
Go to my NexusDataServer.com website (not my CoffingDW.com website), and press on CLASSES in the top menu.
Locate the Snowflake Architecture and SQL class schedule and book outline, and press the COURSE OUTLINE button.
Like the picture below, you can press the DOWNLOAD FLAT FILE for the DATA LOAD Exercise button. The flat file you are downloading is a CSV file containing approximately 6,000 rows of sales information from a company selling boots, jackets, etc. You want to save this file on your PC or laptop so we can use it to load it into Snowflake. Please place the file in an easy place to remember because you will be selecting it later in this exercise to load into Snowflake.
You can try the Nexus Query Chameleon for Free, as well. Download the Nexus at CoffingDW.com and query all systems in your enterprise from the best tool on the market. Nexus also migrates data and can join data across platforms.
The Snowflake query browser is light years behind the 18 years of development of the Nexus Query Chameleon. And the best part is that Nexus can query all systems in your enterprise and has a Super Join Builder that builds the SQL automatically as the user's point-and-click. And the most amazing part is that Nexus can easily join Snowflake tables with Excel, Access, and tables and views from all other database platforms. Check out just some of the amazing features of Nexus here.