Step 2. Create Snowflake Objects

A database and table are required before you can load data. This tutorial loads data into a table in a database named sf_tuts.

In addition, loading and querying data requires a virtual warehouse, which provides the necessary compute resources to perform these tasks. You can use your own warehouse, if one is available; otherwise, this topic includes a SQL command that creates an X-Small warehouse.

When you have completed the tutorial, you can drop these objects to remove them from your account.

In this Topic:

Creating a Database

Create the sf_tuts database using the CREATE DATABASE command:

CREATE OR REPLACE DATABASE sf_tuts;

Note that you do not need to create a schema in the database because each database created in Snowflake contains a default schema named public.

Also, note that the database and schema you just created are now in use for your current session. This information is displayed in your SnowSQL command prompt, but can also be viewed using the following context functions:

SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();

+--------------------+------------------+
| CURRENT_DATABASE() | CURRENT_SCHEMA() |
|--------------------+------------------|
| SF_TUTS            | PUBLIC           |
+--------------------+------------------+

Creating a Table

Create a table named emp_basic in sf_tuts.public using the CREATE TABLE command:

CREATE OR REPLACE TABLE emp_basic (
  first_name STRING ,
  last_name STRING ,
  email STRING ,
  streetaddress STRING ,
  city STRING ,
  start_date DATE
  );

Note that the number of columns in the table, their positions, and their data types correspond to the fields in the sample CSV data files that you will be staging in the next step in this tutorial.

Creating a Virtual Warehouse

Create an X-Small warehouse named sf_tuts_wh using the CREATE WAREHOUSE command:

CREATE OR REPLACE WAREHOUSE sf_tuts_wh WITH
  WAREHOUSE_SIZE='X-SMALL'
  AUTO_SUSPEND = 180
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED=TRUE;

Note that the warehouse is not started initially, but it is set to auto-resume, so it will automatically start running when you execute your first SQL command that requires compute resources.

Also, note that the warehouse is now in use for your current session. This information is displayed in your SnowSQL command prompt, but can also be viewed using the following context function:

SELECT CURRENT_WAREHOUSE();

+---------------------+
| CURRENT_WAREHOUSE() |
|---------------------|
| SF_TUTS_WH          |
+---------------------+

Next: Step 3. Stage the Data Files