Compartir a través de

在 Azure Synapse Analytics 中使用专用 SQL 池设计表

  • Artículo

本文提供有关在专用 SQL 池中设计表的重要初步概念。

确定表类别

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

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

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

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

架构和表名称

可通过架构将以相似方式使用的表组合在一起。 若要将多个数据库从本地解决方案迁移到专用 SQL 池，最好是将所有事实数据表、维度表和集成表迁移到专用 SQL 池中的一个架构内。

例如，可将所有表存储在 WideWorldImportersDW 示例专用 SQL 池中一个名为 wwi 的架构内。 以下代码创建名为 wwi 的用户定义的架构。

CREATE SCHEMA wwi;

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

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

表暂留

表将数据永久或临时存储在 Azure 存储中，或者存储在专用 SQL 池外部的数据存储中。

常规表

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

CREATE TABLE MyTable (col1 int, col2 int );

临时表

临时表只在会话持续期间存在。 可以使用临时表来防止其他用户查看临时结果，以及减少清理需求。

临时表利用本地存储来提供快速操作的性能。 有关详细信息，请参阅临时表。

外部表

外部表指向 Azure 存储 blob 中的数据。 与 CREATE TABLE AS SELECT 语句配合使用时，从外部表中进行选择即可将数据导入到专用 SQL 池。

因此，外部表可用于加载数据。 有关加载教程，请参阅使用 PolyBase 从 Azure Blob 存储加载数据。

数据类型

专用 SQL 池支持最常用的数据类型。 有关受支持数据类型的列表，请参阅 CREATE TABLE 语句中的 CREATE TABLE 引用中的数据类型。 有关使用数据类型的指导，请参阅数据类型。

分布式表

专用 SQL 池的一个基本功能是可以跨分布区以特定方式对表进行存储和运算。 专用 SQL 池支持使用以下三种方法来分配数据：轮询机制（默认）、哈希和复制。

哈希分布表

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

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

复制表

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

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

循环表

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

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

表的常用分布方法

表类别通常确定了要选择哪个选项来分布表。

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

表分区

分区表存储根据数据范围存储表行并对其执行操作。 例如，可以按日、月或年将某个表分区。 可以通过分区消除来提高查询性能，否则查询扫描范围将限制为分区中的数据。 还可以通过分区切换来维护数据。 由于 SQL 池中的数据已经是分布式的，过多的分区可能会降低查询性能。 有关详细信息，请参阅分区指南。 以分区切换的方式切换成不为空的表分区时，若要截断现有数据，可考虑在 ALTER TABLE 语句中使用 TRUNCATE_TARGET 选项。 以下代码将已转换的日常数据切换成 SalesFact，覆盖任何现有的数据。

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

列存储索引

默认情况下，专用 SQL 池将表存储为聚集列存储索引。 对于大型表而言，这种数据存储形式可以实现较高的数据压缩率和查询性能。

聚集列存储索引通常是最佳选择，但在某些情况下，聚集索引或堆是适当的存储结构。

提示

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

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

统计信息

查询优化器在创建用于执行查询的计划时，使用列级统计信息。

若要提高查询性能，必须有基于各个列（尤其是查询联接中使用的列）的统计信息。 创建统计信息的过程是自动发生的。

不会自动更新统计信息。 添加或更改了大量的行之后更新统计信息。 例如，在执行加载后更新统计信息。 有关详细信息，请参阅统计信息指南。

主键和唯一键

仅当同时使用 NONCLUSTERED 和 NOT ENFORCED 时才支持 PRIMARY KEY。 仅在使用 NOT ENFORCED 时才支持 UNIQUE 约束。 请查看专用 SQL 池表约束。

用于创建表的命令

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

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

使源数据与专用 SQL 池相符

从其他数据源加载数据可以填充专用 SQL 池表。 若要成功执行加载操作，源数据中列的数目和数据类型必须与专用 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]
    AND i.[index_id] = nps.[index_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 池创建表后，下一步是将数据加载到该表中。 有关加载的教程，请参阅将数据加载到专用 SQL 池。