表设计注意事项¶

本主题提供设计和管理表时的最佳实践、一般准则和重要注意事项。

本主题内容：

列的日期/时间数据类型¶

定义包含日期或时间戳的列时，Snowflake 建议选择日期或时间戳数据类型，而不是字符数据类型。与 VARCHAR 相比，Snowflake 存储 DATE 和 TIMESTAMP 数据的效率更高，因此查询性能更好。根据所需的粒度级别，选择适当的日期或时间戳数据类型。

参照完整性约束¶

Snowflake 中的参照完整性约束仅供传达信息之用，除了 NOT NULL 之外，不强制执行。NOT NULL 以外的约束创建为禁用。

不过，约束会提供宝贵的元数据。通过主键和外键，项目团队成员可以了解架构设计的方向，熟悉表之间的关系。

此外，大多数商业智能 (BI) 和可视化工具将外键定义与表一起导入，并构建适当的联接条件。这种方法可以节省时间，而且比事后猜测如何联接表然后手动配置工具更不容易出错。基于主键和外键的连接也有助于确保设计的完整性，因为联接不会留给不同的开发者去解释。一些 BI 和可视化工具还利用约束信息将查询重写为更有效的形式，例如联接消除。

使用 CREATE | ALTER TABLE ... CONSTRAINT 命令创建或修改表时指定约束。

在下面的示例中，第二个表 (salesorders) 的 CREATE TABLE 语句定义了一个行外外键约束，该约束引用了第一个表 (salespeople) 中的一列：

create or replace table salespeople (
  sp_id int not null unique,
  name varchar default null,
  region varchar,
  constraint pk_sp_id primary key (sp_id)
);
create or replace table salesorders (
  order_id int not null unique,
  quantity int default null,
  description varchar,
  sp_id int not null unique,
  constraint pk_order_id primary key (order_id),
  constraint fk_sp_id foreign key (sp_id)
  references salespeople(sp_id)
);

Copy

查询 GET_DDL 函数以检索可用于重新创建指定表的 DDL 语句。该语句包括当前在表上设置的约束。

例如：

select get_ddl('table', 'mydb.public.salesorders');

+-----------------------------------------------------------------------------------------------------+
| GET_DDL('TABLE', 'MYDATABASE.PUBLIC.SALESORDERS')                                                   |
|-----------------------------------------------------------------------------------------------------|
| create or replace TABLE SALESORDERS (                                                               |
|   ORDER_ID NUMBER(38,0) NOT NULL,                                                                   |
|   QUANTITY NUMBER(38,0),                                                                            |
|   DESCRIPTION VARCHAR(16777216),                                                                    |
|   SP_ID NUMBER(38,0) NOT NULL,                                                                      |
|   unique (SP_ID),                                                                                   |
|   constraint PK_ORDER_ID primary key (ORDER_ID),                                                    |
|   constraint FK_SP_ID foreign key (SP_ID) references MYDATABASE.PUBLIC.SALESPEOPLE(SP_ID)           |
| );                                                                                                  |
+-----------------------------------------------------------------------------------------------------+

Copy

或者，通过查询 Information Schema 中的 TABLE_CONSTRAINTS 视图视图，按架构（或数据库中的所有架构）检索所有表约束的列表。

例如：

select table_name, constraint_type, constraint_name
  from mydb.information_schema.table_constraints
  where constraint_schema = 'PUBLIC'
  Order by table_name;

+-------------+-----------------+-----------------------------------------------------+
| TABLE_NAME  | CONSTRAINT_TYPE | CONSTRAINT_NAME                                     |
|-------------+-----------------+-----------------------------------------------------|
| SALESORDERS | UNIQUE          | SYS_CONSTRAINT_fce2257e-c343-4e66-9bea-fc1c041b00a6 |
| SALESORDERS | FOREIGN KEY     | FK_SP_ID                                            |
| SALESORDERS | PRIMARY KEY     | PK_ORDER_ID                                         |
| SALESORDERS | UNIQUE          | SYS_CONSTRAINT_bf90e2b3-fd4a-4764-9576-88fb487fe989 |
| SALESPEOPLE | PRIMARY KEY     | PK_SP_ID                                            |
+-------------+-----------------+-----------------------------------------------------+

Copy

何时设置群集密钥¶

对于大多数表，不需要指定群集密钥。Snowflake 通过优化引擎和微分区执行自动调整。在许多情况下，数据会按日期或时间戳加载并组织到微分区中，并按照相同的维度进行查询。

何时应为表指定群集密钥？首先要注意的是，对小型表进行聚类通常不会显著提高查询性能。

对于较大的数据集，在以下情况下，可以考虑为表指定群集密钥：

数据的加载顺序与最常查询数据的维度不匹配（例如，数据按日期加载，但报表按 ID 筛选数据）。如果现有脚本或报表按日期和 ID （可能还有第三列或第四列）查询数据，那么创建多列群集密钥可能会提高性能。
查询配置文件显示，在对表进行典型查询的总持续时间中，有很大一部分时间用于扫描。这适用于对一个或多个特定列进行筛选的查询。

请注意，重聚类会以不同的顺序重写现有数据。之前的排序将存储 7 天，以提供故障安全保护。对表进行重聚类会产生与重新排序的数据大小相关的计算成本。

有关更多信息，请参阅自动聚类。

何时指定列长度¶

Snowflake 可有效压缩列数据；因此，创建大于必要长度的列对数据表大小的影响微乎其微。同样，具有最大长度声明（例如 VARCHAR(16777216)）的列与较小精度的列之间没有查询性能差异。

但是，当列数据的大小可预测时，Snowflake 建议定义适当的列长度，原因如下：

数据加载操作有很大可能检测到诸如列加载顺序有误之类的问题，例如，将 50 个字符的字符串错误地加载到 VARCHAR(10) 列中。此类问题会产生错误。
如果未指定列长度，某些第三方工具可能会预期会使用最大大小值，这可能会导致客户端内存使用量增加或异常行为。

在 VARIANT 列中存储半结构化数据与展平嵌套结构¶

如果还不确定要对半结构化数据执行哪种类型的操作，Snowflake 建议暂时将数据存储在 VARIANT 列中。对于大多数常规数据，并且只使用本机数据类型（字符串和整数），对关系数据和 VARIANT 列中的数据的操作的存储要求和查询性能非常相似。

为了更好地削减和减少存储消耗，如果半结构化数据包含以下内容，Snowflake 建议对象和关键数据平展到单独的关系列中：

日期和时间戳，尤其是非 ISO 8601 日期和时间戳，作为字符串值
字符串中的数字
数组

非本机值（如日期和时间戳）在加载到 VARIANT 列中时存储为字符串，因此对这些值的操作可能比存储在具有相应数据类型的关系列中时更慢，并且占用更多空间。

如果您知道数据的用例，请对典型数据集执行测试。将数据集加载到表中的 VARIANT 列中。使用 FLATTEN 函数将计划查询的对象和键提取到单独的表中。对这两个表运行一组典型的查询，查看哪种结构可提供最佳性能。

将永久表转换为瞬态表或反之¶

目前，无法使用 ALTER TABLE 命令将永久表更改为瞬态表。TRANSIENT 属性在创建表时设置，无法修改。

同样，也无法直接将瞬态表更改为永久表。

要将现有的永久表转换为瞬时表（或反之），同时保留数据和其他特征（如列默认值和授予权限），可以创建一个新表并使用 COPY GRANTS 子句，然后复制数据：

CREATE TRANSIENT TABLE my_new_table LIKE my_old_table COPY GRANTS;
INSERT INTO my_new_table SELECT * FROM my_old_table;

Copy

如果要保留所有数据，但不保留授予的权限和其他特征，可以使用 CREATE TABLE AS SELECT (CTAS)，例如：

CREATE TRANSIENT TABLE my_transient_table AS SELECT * FROM mytable;

Copy

另一种复制表（但将生命周期从永久改为瞬态）的方法是 CLONE 表，例如：

CREATE TRANSIENT TABLE foo CLONE bar COPY GRANTS;
Copy
旧分区不受影响（它们不会成为瞬态），但克隆中添加的新分区将遵循瞬态生命周期。

不能将瞬态表克隆到永久表。