Snowflake 简明教程

Snowflake - Schema

架构是对表、视图等数据库对象的分组。每个架构都属于单个数据库。“数据库架构”是 Snowflake 中的命名空间。在执行任何操作时，需要在查询中直接提供命名空间或在 Snowflake 的 UI 中设置命名空间。

Schema is grouping of database objects like tables, views etc. Each schema belongs to a single database. “Database.Schema” is the namespace in Snowflake. While performing any operation, the namespace is required to provide either directly in query or setting up in Snowflake’s UI.

本章我们将讨论如何创建数据库以及查看详细信息。Snowflake 为用户提供了创建数据库的两种方式，第一种方法是使用用户界面，第二种方法是使用 SQL 查询。

In this chapter, we will discuss how to create a database, and view details. Snowflake provides the user two ways to create a database, first one is by using user interface and the second one is by using SQL query.

Working with Schema using Snowflake’s UI

让我们看看如何使用 GUI 功能创建架构。

Let’s see how to create a Schema using the GUI features.

Creating a schema

使用唯一 URL 登录 Snowflake 帐户。现在单击功能区顶部显示的图标 Databases 。它会导航到数据库视图屏幕。然后单击要创建新架构的数据库名称，如下图所示：

Login into Snowflake account using the unique URL. Now click the Databases icon present at the top ribbon. It will navigate to the database view screen. Then click the database name where the you want to create a new schema as shown in the following screenshot −

creating a schema

单击数据库名称后，它会导航到数据库属性页面，您可以在其中看到数据库内部创建的表/视图/架构等。现在单击图标 Schemas ，默认情况下选择表，如下图所示：

After clicking the database name, it will navigate you to the database properties page where you can see tables/views/schemas, etc., created inside the database. Now click the Schemas icon, by default, Table is selected as shown in the following screenshot −

click the schemas option

它会显示为所选数据库创建的架构列表。现在单击架构列表上方的图标 Create 以创建新架构，如下图所示：

It displays list of schemas already created for selected database. Now click the Create icon above the list of schemas to create a new schema, as shown in the following screenshot −

create a new schema

单击图标 Create 后，您将能够看到对话框 Create Schema 。输入架构名称并单击按钮 Finish ，如下图所示：

After you click the Create icon, you will be able to see the Create Schema dialog box. Enter the schema name and click the Finish button, as shown in the following screenshot −

create schema dialog box

将创建一个新架构并与其他架构的列表一起使用。

A new schema would be created and available with list of other schemas.

Edit / Modify / Alter Schema

Snowflake 提供修改或更改架构名称的功能。让我们看看如何修改架构名称。

Snowflake provides the facility to modify or alter the schema names. Let’s see how we can modifiy a Schema name.

单击功能区顶部显示的图标 Database 。它会显示数据库页面详细信息。现在单击数据库的名称。它会导航到数据库属性视图页面。单击 Schemas 以查看可用架构的列表。选择一个架构以更改其名称，然后单击图标 Alter ，如下所示。

Click the Database icon present at the top ribbon. It displays the database page details. Now click the name of a database. It will navigate you to database properties view page. Click Schemas to see the list of available schemas. Select a schema to alter its name and click the Alter icon as shown below.

alter schema

它会弹出对话框 Alter Schema 。用户可以修改名称。单击完成按钮，如下图所示。

Its pop ups the Alter Schema dialog box. User can modify the Name. Click the Finish button as shown below.

alter schema popup

现在，它会显示更新的架构名称。

Now, it displays the updated schema name.

View Schema

架构存在于数据库中。要查看架构，我们必须导航到数据库。让我们看看如何使用 UI 来查看架构。

Schemas are present inside a database. To view schemas, we must navigate to Database. Let’s see how you can use the UI to view the schemas.

单击功能区顶部显示的图标 Databases 。它将显示数据库的面板 View ，其中存在所有已创建的数据库。选择一个数据库并单击其名称以查看其下的架构。

Click the Databases icon present at the top ribbon. It will display the View panel of Database where all the created databases are present. Select a database and click its name to view the schemas under it.

单击模式列表正上方的 Schemas 以显示所有可用模式。创建数据库后，它默认生成 two schemas - Information Schema 和 Public 。Information Schema 包含数据库的所有元数据。

Click Schemas present just above the list of schemas. It will display all the available schemas. Once a database is created, it will generate two schemas by default – Information Schema and Public. Information Schema contains all the metadata of a database.

  1. Use the Create button to create a new schema under the same database. User can create N number of schemas.

  2. Use the Clone button to create another copy of existing schema. To perform this operation, select a schema and click the Clone icon.

以下屏幕截图演示了此功能 −

The following screenshot demonstrates this feature −

view schema

它会弹出 Clone Schema 对话框，输入新模式的名称，然后单击 Finish 按钮。

It pops up the Clone Schema dialog box, enter the name of the new schema and click the Finish button.

以下屏幕截图显示了克隆功能 −

The following screenshot displays the clone functionality −

clone schema

在视图面板中，可以看到克隆模式。用户还可以选择一个模式并单击 Drop 图标来删除该模式，如下图所示 −

In the View panel, you can see the cloned schema. User can delete a schema as well, by selecting a schema and clicking the Drop icon, as shown in the following screenshot −

delete schema

将弹出确认对话框。单击是删除，否则单击否。

It pops up a dialog box for confirmation. Click Yes for deletion, else No.

Working on Schemas Using Snowflake’s SQL Interface

让我们了解如何使用 SQL 界面功能创建模式。

Let’s see how to create a Schema using the SQL interface features.

Create Schema

首先登录 Snowflake 并导航到 Worksheets 。用户登录后默认打开工作表，否则单击顶部功能区中显示的 Worksheets 图标。

First login into Snowflake and navigates to Worksheets. By default, Worksheet is opened once the user logs in else click on Worksheets icon present at top ribbon.

编写以下查询以在数据库 TSET_DB 下创建模式 TEST_SCHEMA −

Write a following query to create a schema TEST_SCHEMA under database TSET_DB −

CREATE SCHEMA "TEST_DB"."TEST_SCHEMA"

单击 Run 按钮执行查询。 Result 将以 "成功创建模式 TEST_SCHEMA" 的形式显示在结果面板中。

Click the Run button to execute the query. Result will be displayed in result panel as "Schema TEST_SCHEMA" successfully created.

Edit/Modify/Alter Schema

要 alter/modify 模式名称，使用以下查询并运行它 −

To alter/modify the schema name, use following query and run it −

ALTER SCHEMA "TEST_DB"."TEST_SCHEMA" RENAME TO "TEST_DB"."TEST_SCHEMA_RENAME"

用户可以转到视图面板并验证更新后的名称。

User can go to view panel and verify the updated name.

View Schema

要查看所有列出的模式，用户可以使用以下 SQL。它提供所有列出模式的详细信息。

To view all listed schemas, user can use the following SQL. It brings details of all listed schemas.

SHOW SCHEMAS

要 clone 模式，使用以下 SQL −

To clone a schema, use following SQL −

CREATE SCHEMA "TEST_DB"."TEST2" CLONE "TEST_DB"."TEST_SCHEMA_RENAME"

要 delete 模式，使用以下 SQL −

To delete a schema, use following SQL −

DROP SCHEMA "TEST_DB"."TEST2"

用户可以在每次操作后运行 SHOW SCHEMAS 查询，以验证操作是否已完成。

User can run SHOW SCHEMAS query after each operation to verify whether operation is completed or not.