使用 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.

下图提供了 Tasty Bytes 的概述。

包含 Tasty Bytes 的概述，该公司是一个全球食品卡车网络，拥有 15 个品牌的本地化食品卡车选项，可供多个国家和城市选择。该图像描述了公司的使命、愿景、所在位置、当前状态和未来目标。

备注

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.

您将学习的内容¶

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

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

先决条件¶

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

第 1 步：使用 Snowsight 登录¶

要通过公共互联网访问 Snowsight，请执行以下操作：

在支持的 Web 浏览器中，导航至 https://app.snowflake.cn。
提供账户标识符或账户 URL。如果您之前登录过 Snowsight，可能会看到一个可以选择的账户名称。
使用 Snowflake 账户凭据登录。

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 工作表入门.

To open the pre-loaded template worksheet, follow these steps:

In the navigation menu, select Projects » Templates.
Find and open Load sample data from Amazon AWS S3 with SQL.

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

第 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 虚拟仓库.

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

In the open worksheet, place your cursor in the USE ROLE line.
```
USE ROLE SNOWFLAKE_LEARNING_ROLE;
```
Copy
At the top of the worksheet, select Run.

备注

In this tutorial, run SQL statements one at a time. Don't select Run All.
Place your cursor in the USE WAREHOUSE line, then select Run.
```
USE WAREHOUSE SNOWFLAKE_LEARNING_WH;
```
Copy

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

数据库将数据存储在您可以管理和查询的表中。架构是数据库对象（如表和视图）的逻辑分组。例如，架构可能包含特定应用程序所需的数据库对象。有关更多信息，请参阅数据库、表和视图 – 概览。

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:

In the open worksheet, place your cursor in the USE DATABASE line, then select Run.
```
USE DATABASE SNOWFLAKE_LEARNING_DB;
```
Copy

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

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

Copy

Place your cursor in the USE SCHEMA IDENTIFIER line, then select Run.
```
USE SCHEMA IDENTIFIER($schema_name);
```
Copy

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
);

Copy

To confirm that the table was created successfully, place your cursor in the SELECT line, then select Run.
```
SELECT * FROM menu;
```
Copy
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 从 Amazon S3 批量加载.

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

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

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

Copy

To confirm that the stage was created successfully, place your cursor in the LIST line, then select Run.
```
LIST @blob_stage/raw_pos/menu/;
```
Copy
Your output looks similar to the following image:
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/;
```
Copy

第 6 步：查询数据¶

现在数据已加载，您可以在 menu 表上运行查询了。

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;

Copy

Your output looks similar to the following image:

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

SELECT TOP 10 * FROM menu;

Copy

Your output looks similar to the following image:

包含以下列的表输出：MENU_ID、MENU_TYPE_ID、MENU_TYPE、TRUCK_BRAND_NAME、MENU_ITEM_ID、MENU_ITEM_NAME。第一行具有以下值：10001、1、Ice Cream、Freezing Point、10、Lemonade。

有关运行返回指定行数的查询的更多信息，请参阅 TOP <n>。

您可以在工作表中运行其他查询来浏览 menu 表中的数据。

第 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;

Copy

总结和要点¶

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

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

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

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 ：

完成 Snowflake 提供的其他教程：
- Snowflake 入门教程
Familiarize yourself with key Snowflake concepts and features, and the SQL commands used to perform queries and insert/update data:
尝试 Snowflake 提供的 Tasty Bytes Quickstarts：
- Tasty Bytes Quickstarts