Snowflake 简明教程

Snowflake - Table & Columns

在数据库中，创建了作为表逻辑分组的模式。表包含列。表和列是数据库的低级且最重要的对象。在本章中，我们将讨论如何在 Snowflake 中创建表和列。

In a database, Schemas are created which are logical grouping of tables. Tables contain columns. Tables and columns are low-level and most important objects of a database. In this chapter, we will discuss about how to create a table and columns in Snowflake.

Snowflake 为用户提供了两种方法，可以使用用户界面和 SQL 查询来创建表和相应的列。如果不提供列的详细信息，用户将无法创建表。

Snowflake provides the user two ways, to create a table and corresponding columns using user interface and SQL query. Without providing details of columns, user can’t create a table.

Working with Tables and Columns using Snowflake’s UI

让我们看看如何使用 Snowflake 的用户界面来处理表和列。

Let’s see how to work with tables and columns using Snowflake’s UI.

Create Table and Columns

使用唯一 URL 登录 Snowflake 帐户。单击 Databases 按钮，位于顶部功能区。它导航到数据库视图屏幕。

Login into Snowflake account using the unique URL. Click the Databases button, present at top ribbon. It navigates to database view screen.

单击要创建新表的数据库名称。它将导航到数据库属性页面，您可以在其中看到在数据库内创建的表/视图/模式等。

Click the database name where you want to create a new table. It will navigate to database properties page where you can see tables/views/schemas etc. created inside the database.

如果未选择 Tables ，请单击它，默认情况下，选择“表”。您可以看到在同一数据库中创建的表的列表，否则为空。

Click the Tables if it is not selected, by default, Table is selected. You can see the list of tables created in the same database, otherwise it is blank.

create table

单击 Create 按钮添加表。它将弹出 Create Table 对话框。输入以下字段 -

Click the Create button to add a table. It will pop up the Create Table dialog box. Enter the following fields −

  1. Table Name − test_table

  2. Schema Name − select from available list – PUBLIC

  3. Columns − Click the Add button, then enter Name, Type, Not Null or any default value.

  4. To add multiple columns, keep clicking the Add button and then enter details. Now, click the Finish button.

以下屏幕截图描述了如何添加表和列 -

The following screenshot depicts how to add table and columns −

adding tables and columns

您可以在视图面板中看到创建的表。

You can see the created table in the view panel.

View Table and Columns

在本节中，我们将讨论如何 view 表格和列的详细信息、如何 create a like 表格、如何 clone 它以及如何 delete 表格。

In this section, we will discuss how to view the details of tables and columns, how to create a like table, how to clone it, and how to delete a table.

单击 Databases 位于顶部的功能区。它将显示数据库的“视图”面板，其中列出了所有数据库。单击包含表的数据库名称。例如， TEST_DB 如下面屏幕截图所示 −

Click Databases present at the top ribbon. It will display the View panel of databases where all the databases are listed. Click the name of a database, where tables are present. For example, TEST_DB as shown in the following screenshot −

view tables

它将显示数据库中列出的所有表。使用 Create 按钮创建新表。使用 Create Like 按钮创建一个具有与现有表相同元数据的表。

It will display all the listed tables in the database. Use the Create button for creating a new table. Use the Create Like button to create a table that has the same metadata as an existing table.

通过单击 Create Like 按钮，将弹出 Create Table Like 对话框。输入新表的名称并单击 Finish 按钮。

By clicking the Create Like button, the Create Table Like dialog box will pop up. Enter the name of new table and click on Finish button.

以下屏幕截图说明了此功能 −

The following screenshot explains this functionality −

create table like

在“视图”面板中，可以看到新表。在本例中，TABLE_TEST_1。

In the View panel, you can see the new table. In this case, TABLE_TEST_1.

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

  2. Clone Table dialog box will pop up on the screen. Enter the name of the new table and click the Finish button.

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

The following screenshot displays the clone functionality.

clone table

您可以在“视图”面板中看到新表。

You can see the new table in the View panel.

Clone 和 Create Like 的区别是“列数据”。克隆从现有表中获取实际数据，而“创建与之类似”只复制表的元数据，它不会复制表中已存在的数据。

The difference in Clone and Create Like is "column data". Clone brings the actual data from the existing table, whereas Create Like copies only the metadata of a table. It doesn’t copy existing data present in the table.

用户还可以通过选择表并单击 Drop 按钮来删除表。 Drop Table 对话框弹出以进行确认。单击“是”进行删除，否则单击“否”。

User can delete a table as well, by selecting a table and clicking the Drop button. The Drop Table dialog box pops-up for confirmation. Click YES for deletion, else NO.

drop table

Working on Tables and Columns using Snowflake’s SQL Interface

一旦用户开始处理表格和列，各个数据库和架构就成为重要因素。如果没有提供数据库和架构的详细信息，则查询将无法成功执行。

Once the user starts working on tables and columns, respective databases and schemas become important factor. If details of database and schema is not provided, query won’t execute successfully.

有两种方法可以设置数据库和架构详细信息 - 一种使用 Snowflake 的 UI，另一种是在查询中的表名前提供数据库名称和架构名称，如下面的示例所示 −

There are two ways to set up database and schema details – One using Snowflake’s UI and another is providing database name and schema name before table names in query as shown in following examples −

SELECT * FROM DATABSE_NAME.SCHEMA_NAME.TABLE_NAME.

在 UI 中，需要执行以下步骤 −

In the UI, the following steps need to be performed −

单击“选择架构”旁边的右上角处的向下箭头。它弹出对话框，用户可以在其中提供以下详细信息 −

Click at the Down arrow present at the top-right corner beside the Select Schema. It pops up a dialog box where the user can provide the following details −

  1. ROLE

  2. Warehouse

  3. Database

  4. Schema

以下屏幕截图说明了上述步骤 −

The following screenshot describes the above steps −

snowflake sql interface

现在，当用户在查询中不提供数据库名称和架构名称时，它将针对如上所示的已设置数据库和架构运行。如果您需要切换到另一个数据库/架构，您可以随时进行更改。

Now, when the user runs a query without providing a database name and schema name in the query, it runs against set up database and schema as shown above. You can change it frequently if you need to switch over to another database/schema.

Setup Database, Warehouse and Schema in SQL

使用以下查询为会话设置 warehouse −

Use the following query to set up a warehouse for a session −

USE WAREHOUSE <WAREHOUSE_NAME>

使用以下查询设置会话中的 database −

Use the following query to set up a database for a session −

USE DATABASE <DATABASE_NAME>

使用以下查询设置会话中的 schema −

Use the following query to set up a schema for a session −

USE SCHEMA <SCHEMA_NAME>

Create TABLE and COLUMNS

登录 Snowflake 并导航到工作表。默认情况下，工作表在您登录后打开，否则单击顶部功能区中存在的 Worksheets 图标。

Login into Snowflake and navigate to Worksheets. By default, Worksheet is opened once you login, else click the Worksheets icon present at the top ribbon.

使用以下查询在 TEST_DB 数据库和 TEST_SCHEMA_1 模式下创建表和列−

Use the following query to create a table and columns under the database TEST_DB and schema TEST_SCHEMA_1 −

CREATE TABLE "TEST_DB"."TEST_SCHEMA_1"."TEST_TABLE"
   ("ID" NUMBER (10,0) NOT NULL DEFAULT 0, "NAME" VARCHAR (50), "ADDRESS" VARCHAR (100))

单击 Run 按钮来执行查询。结果将显示在 Results 面板中，因为已成功创建 TEST_TABLE。

Click the Run button to execute the query. Result will be displayed in the Results panel as TEST_TABLE was successfully created.

View Table and Columns

要查看所有列出的表，可以使用以下 SQL。它带来了所有列出模式的详细信息。

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

SHOW TABLES

要查看列定义，请使用以下 SQL−

To view the column definition, use the following SQL −

DESCRIBE TABLE TEST_DB.TEST_SCHEMA_1.TEST_TABLE

要克隆表，请使用以下 SQL−

To clone a table, use the following SQL −

CREATE TABLE "TEST_DB"."TEST_SCHEMA_1".TEST_TABLE_2 CLONE "TEST_DB"."TEST_SCHEMA_1"."TEST_TABL_1"

要创建 Like 表，请使用以下查询−

To create a Like table, use the following query −

CREATE TABLE "TEST_DB"."TEST_SCHEMA_1".TEST_TABL_1 LIKE "TEST_DB"."TEST_SCHEMA_1"."TEST_TABLE"

要删除表，请使用以下 SQL−

To delete a table, use the following SQL −

DROP TABLE "TEST_DB"."TEST_SCHEMA_1"."TEST_TABLE_2"

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

User can run SHOW TABLES query after each operation to verify whether operation is completed.