在 Azure SQL 数据仓库中设计表

了解有关在 Azure SQL 数据仓库中设计表的重要概念。

确定表类别

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

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

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

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

架构和表名称

在 SQL 数据仓库中,数据仓库是一种数据库。 数据仓库中的所有表包含在同一个数据库中。 无法跨多个数据仓库联接表。 此行为不同于支持跨数据库联接的 SQL Server。

在 SQL Server 数据库中,可将 fact、dim 或 integrate 用于架构名称。 若要将 SQL Server 数据库迁移到 SQL 数据仓库,最好是将所有事实数据表、维度表和集成表迁移到 SQL 数据仓库中的一个架构内。 例如,可将所有表存储在 WideWorldImportersDW 示例数据仓库中一个名为 wwi 的架构内。 以下代码创建名为 wwi 的用户定义的架构

CREATE SCHEMA wwi;

若要在 SQL 数据仓库中显示表的组织方式,可以使用 fact、dim 和 int 作为表名称的前缀。 下表显示了 WideWorldImportersDW 的一些架构和表名称。 它将 SQL Server 中的名称和 SQL 数据仓库中的名称进行了对比。

WideWorldImportersDW 表 表类型 SQL Server SQL 数据仓库
城市 维度 Dimension.City wwi.DimCity
顺序 Fact Fact.Order wwi.FactOrder

表暂留

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

常规表

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

CREATE TABLE MyTable (col1 int, col2 int );  

临时表

临时表只在会话持续期间存在。 可以使用临时表来防止其他用户查看临时结果,以及减少清理需求。 由于临时表也利用本地存储,因此对于某些操作来说,临时表可以提供更快速的性能。 有关详细信息,请参阅临时表

外部表

外部表指向 Azure Blob 存储中的数据。 与 CREATE TABLE AS SELECT 语句结合使用时,从外部表中选择数据可将数据导入到 SQL 数据仓库。 因此,外部表可用于加载数据。 有关加载教程,请参阅使用 PolyBase 从 Azure Blob 存储加载数据

数据类型

SQL 数据仓库支持最常用的数据类型。 有关受支持数据类型的列表,请参阅 CREATE TABLE 语句中的 CREATE TABLE 引用中的数据类型。 最小化数据类型的大小有助于提高查询性能。 有关使用数据类型的指导,请参阅数据类型

分布式表

SQL 数据仓库的一个基本功能是它可以跨 60 个分布区对表进行存储和操作的方式。 表将使用循环方法、哈希方法或复制方法进行分布。

哈希分布表

哈希分布根据分布列中的值分布行。 对于基于大型表的查询联接,哈希分布表可以实现高性能。 有几种因素会影响分布列的选择。

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

复制表

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

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

循环表

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

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

表的常用分布方法

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

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

表分区

分区表存储根据数据范围存储表行并对其执行操作。 例如,可以按日、月或年将某个表分区。 可以通过分区消除来提高查询性能,否则查询扫描范围将限制为分区中的数据。 还可以通过分区切换来维护数据。 由于 SQL 数据仓库中的数据已经是分布式的,过多的分区可能会降低查询性能。 有关详细信息,请参阅分区指南

列存储索引

默认情况下,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 数据仓库不支持的一些表功能。

表大小查询

若要确定这 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]
)
, 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 数据仓库