使用 Azure Synapse Analytics 中的 Synapse SQL 设计表

  • 项目

本文档包含用于通过专用 SQL 池和无服务器 SQL 池设计表的关键概念。

无服务器 SQL 池是针对数据湖中的数据运行的查询服务。 它没有用于数据引入的本地存储。 专用 SQL 池代表使用 Synapse SQL 时预配的分析资源的集合。 专用 SQL 池的大小由数据仓库单位 (DWU) 决定。

下表列出了与专用 SQL 池和无服务器 SQL 池相关的主题：

主题 专用 SQL 池 无服务器 SQL 池
确定表类别 是 否
架构名称 是 是
表名 是 否
表暂留 是 否
常规表 是 否
临时表 是 是
外部表 是 是
数据类型 是 是
分布式表 是 否
哈希分布表 是 否
复制表 是 否
循环表 是 否
表的常用分布方法 是 否
分区 是 是
列存储索引 是 否
统计信息 是 是
主键和唯一键 是 否
用于创建表的命令 是 否
使源数据与数据仓库相符 是 否
不支持的表功能 是 否
表大小查询 是 否

确定表类别

星型架构将数据组织成事实数据表和维度表。 某些表在转移到事实数据表或维度表之前已用于集成或暂存数据。 设计某个表时，请确定该表的数据是属于事实数据表、维度表还是集成表。 此项决策可以明确相应的表结构和分布方式。

  • 事实数据表包含定量数据，这些数据通常在事务系统中生成，然后加载到数据仓库中。 例如，零售企业每天会生成销售事务，然后将数据载入数据仓库事实数据表进行分析。

  • 维度表包含属性数据，这些数据可能会更改，但一般不会经常更改。 例如，客户的姓名和地址存储在维度表中，仅当客户的个人资料发生更改时，这些数据才会更新。 为了尽量缩小大型事实数据表的大小，不要求将客户的姓名和地址输入到事实数据表的每一行中。 事实数据表和维度表可以共享一个客户 ID。 查询可以联接两个表，以关联客户的个人资料和事务。

  • 集成表为集成或暂存数据提供位置。 可以将集成表创建为常规表、外部表或临时表。 例如，可将数据加载到临时表，在暂存位置对数据执行转换，然后将数据插入生产表中。

架构名称

可通过架构将以相似方式使用的对象组合在一起。 以下代码创建名为 wwi 的用户定义的架构。

CREATE SCHEMA wwi;

表名

若要将多个数据库从本地解决方案迁移到专用 SQL 池，最佳做法是将所有事实数据表、维度表和集成表迁移到一个 SQL 池架构。 例如，可将所有表存储在 WideWorldImportersDW 示例数据仓库中一个名为 wwi 的架构内。

为了显示表在专用 SQL 池中的组织方式，可以使用 fact、dim 和 int 作为表名前缀。 下表显示了 WideWorldImportersDW 的一些架构和表名称。

WideWorldImportersDW 表 表类型 专用 SQL 池
城市 维度 wwi.DimCity
订单 Fact wwi.FactOrder

表暂留

表将数据永久或临时存储在 Azure 存储中，或者存储在数据仓库外部的数据存储中。

常规表

常规表将 Azure 存储中的数据存储为数据仓库的一部分。 不管是否打开了会话，表和数据都会持久保留。 以下示例创建一个包含两个列的常规表。

CREATE TABLE MyTable (col1 int, col2 int );

临时表

临时表只在会话持续期间存在。 可以使用临时表来防止其他用户查看临时结果。 使用临时表还可以减少清理需求。 临时表利用本地存储，在专用 SQL 池中操作起来更快。

无服务器 SQL 池支持临时表。 但是，虽然你可以从临时表中进行选择，但不能将其与存储中的文件联接，因此其使用受到限制。

有关详细信息，请参阅临时表。

外部表

外部表指向位于 Azure 存储 Blob 或 Azure Data Lake Storage 中的数据。

使用 CREATE TABLE AS SELECT 语句，将外部表中的数据导入专用 SQL 池。 有关加载教程，请参阅使用 PolyBase 从 Azure Blob 存储加载数据。

对于无服务器 SQL 池，可以使用 CETAS 将查询结果保存到 Azure 存储中的外部表。

数据类型

专用 SQL 池支持最常用的数据类型。 有关受支持数据类型的列表，请参阅 CREATE TABLE 语句中的 CREATE TABLE 引用中的数据类型。 若要详细了解如何使用数据类型，请参阅数据类型。

分布式表

专用 SQL 池的一个基本功能是可以跨分布区以特定方式对表进行存储和运算。 专用 SQL 池支持使用以下三种方法来分配数据：

  • 轮循机制（默认）
  • 哈希
  • 复制

哈希分布表

哈希分布表根据分布列中的值来分布行。 根据设计，在对大型表进行查询时，哈希分布表可以实现高性能。 选择分布列时，需考虑多项因素。

有关详细信息，请参阅分布式表的设计准则。

复制表

复制表在每个计算节点上提供表的完整副本。 对复制表运行的查询速度较快，因为复制表中的联接不需要移动数据。 不过，复制需要额外的存储，并且对于大型表不可行。

有关详细信息，请参阅复制表的设计准则。

循环表

循环表将表行均匀地分布到所有分布区中。 行将随机分布。 将数据加载到循环表中的速度很快。 但是，与其他分布方法相比，查询可能需要进行更多的数据移动。

有关详细信息，请参阅分布式表的设计准则。

表的常用分布方法

表类别通常决定了表分布的最佳选项。

表类别 建议的分布选项
Fact 结合聚集列存储索引使用哈希分布。 在同一个分布列中联接两个哈希表时，可以提高性能。
维度 对小型表使用复制表。 如果表太大，以致无法在每个计算节点上存储，可以使用哈希分布式表。
过渡 对临时表使用轮循机制表。 使用 CTAS 执行加载的速度较快。 将数据存储到临时表后，可以使用 INSERT...SELECT 将数据移到生产表。

分区

在专用 SQL 池中，分区表存储根据数据范围存储表行并对其执行操作。 例如，可以按日、月或年将某个表分区。 可以通过分区消除来提高查询性能，否则查询扫描范围将限制为分区中的数据。

还可以通过分区切换来维护数据。 由于专用 SQL 池中的数据已经是分布式的，过多的分区可能会降低查询性能。 有关详细信息，请参阅分区指南。

提示

以分区切换的方式切换成不为空的表分区时，若要截断现有数据，可考虑在 ALTER TABLE 语句中使用 TRUNCATE_TARGET 选项。

以下代码将已转换的日常数据切换成 SalesFact 分区，覆盖任何现有的数据。

ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);

在无服务器 SQL 池中，你可以限制将由查询读取的文件/文件夹（分区）。 可以使用查询存储文件中所述的 filepath 和 fileinfo 函数按路径进行分区。 以下示例读取包含 2017 年的数据的文件夹：

SELECT
    nyc.filepath(1) AS [year],
    payment_type,
    SUM(fare_amount) AS fare_total
FROM  
    OPENROWSET(
        BULK 'https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS nyc
WHERE
    nyc.filepath(1) = 2017
GROUP BY
    nyc.filepath(1),
    payment_type
ORDER BY
    nyc.filepath(1),
    payment_type

列存储索引

默认情况下，专用 SQL 池将表存储为聚集列存储索引。 对于大型表而言，这种数据存储形式可以实现较高的数据压缩率和查询性能。 聚集列存储索引通常是最佳选择，但在某些情况下，聚集索引或堆是适当的存储结构。

提示

堆表可能特别适用于加载临时数据，例如将转换成最终表的临时表。

有关列存储功能的列表，请参阅列存储索引的新增功能。 若要提高列存储索引性能，请参阅最大化列存储索引的行组质量。

统计信息

查询优化器在创建用于执行查询的计划时，使用列级统计信息。 若要提高查询性能，必须有基于各个列（尤其是查询联接中使用的列）的统计信息。 Synapse SQL 支持自动创建统计信息。

不会自动进行统计信息更新。 添加或更改了大量的行之后更新统计信息。 例如，在执行加载后更新统计信息。 统计信息指南一文中提供了其他信息。

主键和唯一键

对于专用 SQL 池，仅当同时使用 NONCLUSTERED 和 NOT ENFORCED 时才支持 PRIMARY KEY。 仅在使用 NOT ENFORCED 时才支持 UNIQUE 约束。 有关详细信息，请参阅专用 SQL 池表约束一文。

用于创建表的命令

对于专用 SQL 池，你可以创建一个表作为新的空表。 还可以创建一个表并在其中填充 select 语句的结果。 下面是用于创建表的 T-SQL 命令。

T-SQL 语句 说明
CREATE TABLE 通过定义所有表列和选项来创建空表。
CREATE EXTERNAL TABLE 创建外部表。 表定义存储在专用 SQL 池中。 表数据存储在 Azure Blob 存储或 Azure Data Lake Storage 中。
CREATE TABLE AS SELECT 在新表中填充 select 语句的结果。 表列和数据类型基于 select 语句的结果。 若要导入数据，此语句可从外部表中进行选择。
CREATE EXTERNAL TABLE AS SELECT 通过将 select 语句的结果导出到外部位置，来创建新的外部表。 该位置为 Azure Blob 存储或 Azure Data Lake Storage。

使源数据与数据仓库相符

从其他数据源加载数据可以填充专用 SQL 池表。 若要成功进行加载，源数据中列的数目和数据类型必须与数据仓库中的表定义相符。

注意

使数据相符可能是设计表时的最难部分。

如果数据来自多个数据存储，可将数据移植到数据仓库中，并将其存储在集成表中。 将数据存储到集成表中后，可以使用专用 SQL 池的功能来执行转换操作。 准备好数据后，可以将其插入到生产表中。

不支持的表功能

专用 SQL 池支持其他数据库提供的许多（但不是全部）表功能。 以下列表显示了专用 SQL 池不支持的一些表功能。

表大小查询

在专用 SQL 池中，若要确定这 60 个分布中每个分布的表所占用的空间和行，一个简单的方法是使用 DBCC PDW_SHOWSPACEUSED。

DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

请记住，使用 DBCC 命令可能会受到很大限制。 动态管理视图 (DMV) 显示的信息比 DBCC 命令更详细。 首先创建以下视图。

CREATE VIEW dbo.vTableSizes
AS
WITH base
AS
(
SELECT
 GETDATE()                                                             AS  [execution_time]
, DB_NAME()                                                            AS  [database_name]
, s.name                                                               AS  [schema_name]
, t.name                                                               AS  [table_name]
, QUOTENAME(s.name)+'.'+QUOTENAME(t.name)                              AS  [two_part_name]
, nt.[name]                                                            AS  [node_table_name]
, ROW_NUMBER() OVER(PARTITION BY nt.[name] ORDER BY (SELECT NULL))     AS  [node_table_name_seq]
, tp.[distribution_policy_desc]                                        AS  [distribution_policy_name]
, c.[name]                                                             AS  [distribution_column]
, nt.[distribution_id]                                                 AS  [distribution_id]
, i.[type]                                                             AS  [index_type]
, i.[type_desc]                                                        AS  [index_type_desc]
, nt.[pdw_node_id]                                                     AS  [pdw_node_id]
, pn.[type]                                                            AS  [pdw_node_type]
, pn.[name]                                                            AS  [pdw_node_name]
, di.name                                                              AS  [dist_name]
, di.position                                                          AS  [dist_position]
, nps.[partition_number]                                               AS  [partition_nmbr]
, nps.[reserved_page_count]                                            AS  [reserved_space_page_count]
, nps.[reserved_page_count] - nps.[used_page_count]                    AS  [unused_space_page_count]
, nps.[in_row_data_page_count]
    + nps.[row_overflow_used_page_count]
    + nps.[lob_used_page_count]                                        AS  [data_space_page_count]
, nps.[reserved_page_count]
 - (nps.[reserved_page_count] - nps.[used_page_count])
 - ([in_row_data_page_count]
         + [row_overflow_used_page_count]+[lob_used_page_count])       AS  [index_space_page_count]
, nps.[row_count]                                                      AS  [row_count]
from
    sys.schemas s
INNER JOIN sys.tables t
    ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.indexes i
    ON  t.[object_id] = i.[object_id]
    AND i.[index_id] <= 1
INNER JOIN sys.pdw_table_distribution_properties tp
    ON t.[object_id] = tp.[object_id]
INNER JOIN sys.pdw_table_mappings tm
    ON t.[object_id] = tm.[object_id]
INNER JOIN sys.pdw_nodes_tables nt
    ON tm.[physical_name] = nt.[name]
INNER JOIN sys.dm_pdw_nodes pn
    ON  nt.[pdw_node_id] = pn.[pdw_node_id]
INNER JOIN sys.pdw_distributions di
    ON  nt.[distribution_id] = di.[distribution_id]
INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps
    ON nt.[object_id] = nps.[object_id]
    AND nt.[pdw_node_id] = nps.[pdw_node_id]
    AND nt.[distribution_id] = nps.[distribution_id]
LEFT OUTER JOIN (select * from sys.pdw_column_distribution_properties where distribution_ordinal = 1) cdp
    ON t.[object_id] = cdp.[object_id]
LEFT OUTER JOIN sys.columns c
    ON cdp.[object_id] = c.[object_id]
    AND cdp.[column_id] = c.[column_id]
WHERE pn.[type] = 'COMPUTE'
)
, size
AS
(
SELECT
   [execution_time]
,  [database_name]
,  [schema_name]
,  [table_name]
,  [two_part_name]
,  [node_table_name]
,  [node_table_name_seq]
,  [distribution_policy_name]
,  [distribution_column]
,  [distribution_id]
,  [index_type]
,  [index_type_desc]
,  [pdw_node_id]
,  [pdw_node_type]
,  [pdw_node_name]
,  [dist_name]
,  [dist_position]
,  [partition_nmbr]
,  [reserved_space_page_count]
,  [unused_space_page_count]
,  [data_space_page_count]
,  [index_space_page_count]
,  [row_count]
,  ([reserved_space_page_count] * 8.0)                                 AS [reserved_space_KB]
,  ([reserved_space_page_count] * 8.0)/1000                            AS [reserved_space_MB]
,  ([reserved_space_page_count] * 8.0)/1000000                         AS [reserved_space_GB]
,  ([reserved_space_page_count] * 8.0)/1000000000                      AS [reserved_space_TB]
,  ([unused_space_page_count]   * 8.0)                                 AS [unused_space_KB]
,  ([unused_space_page_count]   * 8.0)/1000                            AS [unused_space_MB]
,  ([unused_space_page_count]   * 8.0)/1000000                         AS [unused_space_GB]
,  ([unused_space_page_count]   * 8.0)/1000000000                      AS [unused_space_TB]
,  ([data_space_page_count]     * 8.0)                                 AS [data_space_KB]
,  ([data_space_page_count]     * 8.0)/1000                            AS [data_space_MB]
,  ([data_space_page_count]     * 8.0)/1000000                         AS [data_space_GB]
,  ([data_space_page_count]     * 8.0)/1000000000                      AS [data_space_TB]
,  ([index_space_page_count]  * 8.0)                                   AS [index_space_KB]
,  ([index_space_page_count]  * 8.0)/1000                              AS [index_space_MB]
,  ([index_space_page_count]  * 8.0)/1000000                           AS [index_space_GB]
,  ([index_space_page_count]  * 8.0)/1000000000                        AS [index_space_TB]
FROM base
)
SELECT *
FROM size
;

表空间摘要

此查询返回行以及按表划分的空间。 使用表空间摘要可以查看哪些表是最大的表， 以及这些表是按轮循机制分布的、按复制分布的还是按哈希分布的。 对于哈希分布式表，此查询会显示分布列。

SELECT
     database_name
,    schema_name
,    table_name
,    distribution_policy_name
,      distribution_column
,    index_type_desc
,    COUNT(distinct partition_nmbr) as nbr_partitions
,    SUM(row_count)                 as table_row_count
,    SUM(reserved_space_GB)         as table_reserved_space_GB
,    SUM(data_space_GB)             as table_data_space_GB
,    SUM(index_space_GB)            as table_index_space_GB
,    SUM(unused_space_GB)           as table_unused_space_GB
FROM
    dbo.vTableSizes
GROUP BY
     database_name
,    schema_name
,    table_name
,    distribution_policy_name
,      distribution_column
,    index_type_desc
ORDER BY
    table_reserved_space_GB desc
;

按分布类型划分的表空间

SELECT
     distribution_policy_name
,    SUM(row_count)                as table_type_row_count
,    SUM(reserved_space_GB)        as table_type_reserved_space_GB
,    SUM(data_space_GB)            as table_type_data_space_GB
,    SUM(index_space_GB)           as table_type_index_space_GB
,    SUM(unused_space_GB)          as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY distribution_policy_name
;

按索引类型划分的表空间

SELECT
     index_type_desc
,    SUM(row_count)                as table_type_row_count
,    SUM(reserved_space_GB)        as table_type_reserved_space_GB
,    SUM(data_space_GB)            as table_type_data_space_GB
,    SUM(index_space_GB)           as table_type_index_space_GB
,    SUM(unused_space_GB)          as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY index_type_desc
;

分布空间摘要

SELECT
    distribution_id
,    SUM(row_count)                as total_node_distribution_row_count
,    SUM(reserved_space_MB)        as total_node_distribution_reserved_space_MB
,    SUM(data_space_MB)            as total_node_distribution_data_space_MB
,    SUM(index_space_MB)           as total_node_distribution_index_space_MB
,    SUM(unused_space_MB)          as total_node_distribution_unused_space_MB
FROM dbo.vTableSizes
GROUP BY     distribution_id
ORDER BY    distribution_id
;

后续步骤

为数据仓库创建表后，接下来可将数据载入该表。 有关加载的教程，请参阅将数据加载到专用 SQL 池中。