使用 SQL 加载和查询示例数据

简介

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 can access a pre-loaded Snowsight template worksheet to complete these tasks.

The following illustration provides an overview of Tasty Bytes.

Contains an overview of Tasty Bytes, a global food truck network with 15 brands of localized food truck options several countries and cities. The image describes the company's mission, vision, locations, current state, and future goals.

Note

Snowflake bills a minimal amount for the on-disk storage used for any sample data in this tutorial. The tutorial provides steps to drop objects and minimize storage cost. Snowflake requires a virtual warehouse to load the data and execute queries. A running virtual warehouse consumes Snowflake credits.

If you are using a 30-day trial account (https://signup.snowflake.com/), which provides free credits, you won’t incur any costs.

您将学习的内容

在本教程中，您将学习如何：

  • 使用角色，从授予的权限中获取对功能的访问权限。
  • 使用仓库访问资源。
  • 创建数据库和架构。
  • 创建表。
  • 将数据加载到表中。
  • 查询表中的数据。

先决条件

本教程假定满足以下条件：

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 sample data

You can use worksheets to write and run SQL commands on your Snowflake database. You can access a pre-loaded template worksheet for this tutorial. The worksheet has the SQL commands that you will run to use 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 template worksheet, follow these steps:

  1. In the navigation menu, select Projects » Templates.

  2. Find and open Load sample data from Amazon AWS S3 with SQL.

    The beginning of your worksheet looks similar to the following image:

The SQL load and query worksheet, which contains the SQL commands for this tutorial, along with descriptive comments.

第 3 步：设置要使用的角色和仓库

The role you use determines the privileges you have. In this tutorial, use the SNOWFLAKE_LEARNING_ROLE role so that you can view and manage objects in your account. For more information, see Snowsight templates.

A warehouse provides the required resources to create and manage objects and run SQL commands. These resources include CPU, memory, and temporary storage. You have access to the SNOWFLAKE_LEARNING_WH virtual warehouse that you can use for this tutorial. For more information, see Virtual warehouses.

要设置要使用的角色和仓库，请执行以下操作：

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

    USE ROLE SNOWFLAKE_LEARNING_ROLE;

  2. At the top of the worksheet, select Run.

    Note

    In this tutorial, run SQL statements one at a time. Don’t select Run All.

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

    USE WAREHOUSE SNOWFLAKE_LEARNING_WH;

Step 4. Use 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 use the database SNOWFLAKE_LEARNING_DB, a schema that concatenates your username with _LOAD_SAMPLE_DATA_FROM_S3, and a table that you create named menu.

To use this database, schema, and table, do the following:

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

    USE DATABASE SNOWFLAKE_LEARNING_DB;

  2. Place your cursor in the SET line, then select Run.

    SET schema_name = CONCAT(current_user(), '_LOAD_SAMPLE_DATA_FROM_S3');

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

    USE SCHEMA IDENTIFIER($schema_name);

  4. Place your cursor in the CREATE TABLE lines, then select Run.

    CREATE OR REPLACE TABLE 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
);

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

    SELECT * FROM menu;

    Your output shows the columns of the table you created. At this point in the tutorial, the table doesn’t have any rows.

第 5 步：创建暂存区并加载数据

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 more information, see Bulk loading from Amazon S3.

要创建暂存区，请执行以下操作：

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

    CREATE OR REPLACE STAGE blob_stage
url = 's3china://sfquickstarts/tastybytes/'
file_format = (type = csv);

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

    LIST @blob_stage/raw_pos/menu/;

    Your output looks similar to the following image:

    Table output with the following columns: name, size, md5, last_modified. One row shows the details for the stage.

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

    COPY INTO menu
FROM @blob_stage/raw_pos/menu/;

第 6 步：查询数据

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.

例如，要返回表中的行数，请运行以下查询：

SELECT COUNT(*) AS row_count FROM menu;

Your output looks similar to the following image:

Table output with the following column: ROW_COUNT. One row with the following value: 100.

运行此查询以返回表中的前十行：

SELECT TOP 10 * FROM menu;

Your output looks similar to the following image:

Table output with the following columns: MENU_ID, MENU_TYPE_ID, MENU_TYPE, TRUCK_BRAND_NAME, MENU_ITEM_ID, MENU_ITEM_NAME. The first row has the following values: 10001, 1, Ice Cream, Freezing Point, 10, Lemonade.

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.

第 7 步：清理、总结和其他资源

恭喜！您已成功完成此试用账户教程。

请花几分钟时间查看简短的总结和教程中涵盖的要点。考虑删除在本教程中创建的所有对象以进行清理。通过查看 Snowflake 文档中的其他主题了解更多信息。

If the objects you created in this tutorial are no longer needed, you can remove them from the system with DROP <object> commands. To remove the table you created, run the following command:

DROP TABLE IF EXISTS menu;

总结和要点

In summary, you used a pre-loaded template worksheet in Snowsight to complete the following steps:

  1. 设置角色和仓库的环境。
  2. Use a database, schema, and table.
  3. 创建暂存区，并将该暂存区中的数据加载到数据库中。
  4. 查询数据。

以下是有关加载和查询数据的一些要点：

  • You need the required permissions to create and manage objects in your account. In this tutorial, you use the SNOWFLAKE_LEARNING_ROLE role, which is provided with the template environment.
  • You need a warehouse for the resources required to create and manage objects and run SQL commands. This tutorial uses the SNOWFLAKE_LEARNING_WH warehouse included with the template environment.
  • You used a database to store the data and a schema to group the database objects logically.
  • 您创建了一个暂存区来加载 CSV 文件中的数据。
  • 将数据加载到数据库后，您可以使用 SELECT 语句查询数据。

下一步是什么？

请使用以下资源继续了解 Snowflake ：

下一步
先决条件