Sep 28, 2016
If you are like me and fairly new to this whole cloud thing, then one of your main questions is likely:
“How do I get data from my desktop (or server) into Snowflake so I can query it?”
Which, in reality, translates to:
“How do I load data in the cloud?”
Well, there are several methods, including a variety of integration and ETL tools. In this tech tip I will show you the simplest way to accomplish this via the Snowflake Web UI.
Our brilliant engineers built a nice little wizard to make it simple for cloud novices (like me) to easily upload a data file from your desktop and load it into a table in Snowflake.
First off, you need a table in the database that matches the structure of the data file you are going to load (you modeled that table right?). You can build that by running a create table DDL in a worksheet once you have logged in.
So something like this:
CREATE TABLE Region_t1 ( Name VARCHAR2 (200) NOT NULL , Code VARCHAR2 (2) NOT NULL , Abbreviation VARCHAR2 (2) NOT NULL , Sort_Order NUMBER (2) NOT NULL , External_Reference VARCHAR2 (1000) NOT NULL , Record_Source VARCHAR2 (100) NOT NULL , Record_Owner NUMBER (1) NOT NULL , Comments VARCHAR2 (2000) ) ; ALTER TABLE Region_t1 ADD CONSTRAINT PK_Region_t1 PRIMARY KEY ( Code ) ; ALTER TABLE Region_t1 ADD CONSTRAINT UK_Region_t1 UNIQUE ( Name ) ;
Invoke the Load Wizard
To begin the load process, navigate to the Database tab in the UI, drill into your database and then click on the table name. Once you do that, the Load Table widget is displayed (fourth from the left on the toolbar). Click on the widget, and the Load Data wizard opens and asks for a warehouse. Select a virtual warehouse to use from the drop-down list. (Note: The list displays any warehouse you have the USAGE privilege on.)
Select the File to Load
In the next step, you select the source file to load. You have options to get a file from your file system, or from an AWS S3 bucket if you have already moved some files to the cloud.
Note that you can also load multiple files! The select dialog box supports multi-select. But keep in mind that the files must all be the same type with the same structure because they’re all going into the same table.
In the next step, you pick a file format from the drop-down list. In this case, I have selected a format that specifies a CSV type file with comma delimiters.
Creating a File Format (if necessary)
If you do not have an appropriate file format already, simply click the plus sign (+) next to the drop-down arrow in the UI to define a new one. For a standard CSV file that uses commas as the delimiter, you really just need to give the file format a name and click Finish. However, if the first row of the file includes column headings, be sure to set Header lines to skip to 1.
Setting Load Options
The next step of the wizard allows you to choose how to handle load errors. There are four options. I chose to have the load continue by skipping any problem records (which I will deal with after the load).
At this point, you are set to go. If you want to see the code for the load (so you can learn something new, right?), just click the Show SQL link before you click Load.
Running the Load
Once you click Load, the process starts and does the following:
- Uses PUT to copy the file to a Snowflake internal staging area, automatically encrypting the data during the process.
- Uses COPY to load the data into the target table and deletes the file from the staging area upon completion.
- Reports the results.
And voila, you have now loaded data from your desktop into the cloud-native Snowflake Elastic Data Warehouse. Congratulations.
For a much deeper dive into loading data into Snowflake check out the Data Loading topic in our user guide.
As always, keep an eye on this blog site, our Snowflake Twitter feeds (@SnowflakeDB), (@kentgraziano), and (@cloudsommelier) for more tips and tricks, along with updates on all the action and activities here at Snowflake Computing.