Load and query sample data using SQL¶

Introduction¶

This tutorial uses a fictitious food truck brand named Tasty Bytes to show you how to load and query data in Snowflake using SQL. You use a pre-loaded worksheet in Snowsight to complete these tasks.

The following illustration provides an overview of Tasty Bytes.

Prerequisites¶

This tutorial assumes the following:

• You have a supported browser.

• You have a trial account. If you do not have a trial account yet, you can sign up for a free trial (https://signup.snowflake.com/). You can choose any Snowflake Cloud Region.

• Your user is the account administrator and is granted the ACCOUNTADMIN system role. For more information, see Using the ACCOUNTADMIN Role.

Step 1. Sign in using Snowsight¶

To access Snowsight over the public Internet, do the following:

1. In a supported web browser, navigate to https://app.snowflake.cn.

2. Provide your account identifier or account URL. If you’ve previously signed in to Snowsight, you might see an account name that you can select.

3. Sign in using your Snowflake account credentials.

Step 2. Open the SQL worksheet for loading and querying data¶

You can use worksheets to write and run SQL commands on your Snowflake database. Your trial account has access to a pre-loaded worksheet for this tutorial. The worksheet has the SQL commands that you will run to create a database, load data into it, and query the data. For more information about worksheets, see Getting started with worksheets.

To open the pre-loaded tutorial worksheet:

1. Select Projects » Worksheets to open the list of worksheets.

2. Open [Tutorial] Using SQL to load and query sample data.

   Your worksheet looks similar to the following image.

Step 3. Set the role and warehouse to use¶

The role you use determines the privileges you have. In this tutorial, use the ACCOUNTADMIN system role so that you can view and manage objects in your account. For more information, see Using the ACCOUNTADMIN Role.

A warehouse provides the required resources to create and manage objects and run SQL commands. These resources include CPU, memory, and temporary storage. Your trial account has a virtual warehouse (compute_wh) that you can use for this tutorial. For more information, see Virtual warehouses.

To set the role and warehouse to use, do the following:

1. In the open worksheet, place your cursor in the USE ROLE line.

   USE ROLE accountadmin;
   

   Copy

2. In the upper-right corner of the worksheet, select Run.

   Note

   In this tutorial, run SQL statements one at a time. Do not select Run All.

3. Place your cursor in the USE WAREHOUSE line, then select Run.

   USE WAREHOUSE compute_wh;
   

   Copy

Step 4. Create a database, schema, and table¶

A database stores data in tables that you can manage and query. A schema is a logical grouping of database objects, such as tables and views. For example, a schema might contain the database objects required for a specific application. For more information, see Databases, Tables and Views - Overview.

In this tutorial, you create a database named tasty_bytes_sample_data, a schema named raw_pos, and a table named menu.

To create the database, schema, and table, do the following:

1. In the open worksheet, place your cursor in the CREATE OR REPLACE DATABASE line, then select Run.

   CREATE OR REPLACE DATABASE tasty_bytes_sample_data;
   

   Copy

2. Place your cursor in the CREATE OR REPLACE SCHEMA line, then select Run.

   CREATE OR REPLACE SCHEMA tasty_bytes_sample_data.raw_pos;
   

   Copy

3. Place your cursor in the CREATE OR REPLACE TABLE lines, then select Run.

   CREATE OR REPLACE TABLE tasty_bytes_sample_data.raw_pos.menu
   (
       menu_id NUMBER(19,0),
       menu_type_id NUMBER(38,0),
       menu_type VARCHAR(16777216),
       truck_brand_name VARCHAR(16777216),
       menu_item_id NUMBER(38,0),
       menu_item_name VARCHAR(16777216),
       item_category VARCHAR(16777216),
       item_subcategory VARCHAR(16777216),
       cost_of_goods_usd NUMBER(38,4),
       sale_price_usd NUMBER(38,4),
       menu_item_health_metrics_obj VARIANT
   );
   

   Copy

4. To confirm that the table was created successfully, place your cursor in the SELECT line, then select Run.

   SELECT * FROM tasty_bytes_sample_data.raw_pos.menu;
   

   Copy

   Your output shows the columns of the table you created. At this point in the tutorial, the table does not have any rows.

Step 5. Create a stage and load the data¶

A stage is a location that holds data files to load into a Snowflake database. This tutorial creates a stage that loads data from an Amazon S3 bucket. This tutorial uses an existing bucket with a CSV file that contains the data. You load the data from this CSV file into the table you created previously. For information, see Bulk loading from Amazon S3.

To create a stage, do the following:

1. In the open worksheet, place your cursor in the CREATE OR REPLACE STAGE lines, then select Run.

   CREATE OR REPLACE STAGE tasty_bytes_sample_data.public.blob_stage
   url = 's3://sfquickstarts/tastybytes/'
   file_format = (type = csv);
   

   Copy

2. To confirm that the stage was created successfully, place your cursor in the LIST line, then select Run.

   LIST @tasty_bytes_sample_data.public.blob_stage/raw_pos/menu/;
   

   Copy

   Your output looks similar to the following image.

   

3. To load the data into the table, place your cursor in the COPY INTO lines, then select Run.

   COPY INTO tasty_bytes_sample_data.raw_pos.menu
   FROM @tasty_bytes_sample_data.public.blob_stage/raw_pos/menu/;
   

   Copy

Step 6. Query the data¶

Now that the data is loaded, you can run queries on the menu table.

To run a query in the open worksheet, select the line or lines of the SELECT command, and then select Run.

For example, to return the number of rows in the table, run the following query:

SELECT COUNT(*) AS row_count FROM tasty_bytes_sample_data.raw_pos.menu;

Your output looks similar to the following image.

Run this query to return the top ten rows in the table:

SELECT TOP 10 * FROM tasty_bytes_sample_data.raw_pos.menu;

Your output looks similar to the following image.

For more information about running a query that returns the specified number of rows, see TOP <n>.

You can run other queries in the worksheet to explore the data in the menu table.

Step 7. Clean up, summary, and additional resources¶

Congratulations! You have successfully completed this tutorial for trial accounts.

Take a few minutes to review a short summary and the key points covered in this tutorial. Consider cleaning up by dropping any objects you created in this tutorial. Learn more by reviewing other topics in the Snowflake Documentation.

DROP DATABASE IF EXISTS tasty_bytes_sample_data;