在 Azure Synapse Analytics 中使用專用 SQL 集區設計數據表

發行項
01/23/2025

本文提供在專用 SQL 集區中設計數據表的重要簡介概念。

判斷資料表類別

星型架構會將數據組織成事實和維度數據表。某些數據表會用於整合或暫存數據，再移至事實或維度數據表。當您設計資料表時，決定資料表資料是屬於事實資料表、維度資料表還是整合資料表。此決策會通知適當的數據表結構和散發。

事實數據表 包含經常在交易系統中產生的量化數據，然後載入專用SQL集區。例如，零售企業每天會產生銷售交易，然後將數據載入專用SQL集區事實數據表進行分析。
維度資料表包含可能會變更但通常不常變更的屬性資料。例如，客戶的名稱和地址會儲存在維度資料表中，而且只有在客戶的設定檔變更時才會更新。若要降低大型事實資料表的大小，則不需將客戶的名稱和地址放在事實資料表的每個資料列中。相反，事實資料表和維度資料表可以共用客戶識別碼。查詢可以聯結這兩個資料表，來關聯客戶的設定檔和交易。
整合資料表提供了整合或暫存資料的位置。您可以將整合資料表建立為一般數據表、外部數據表或臨時表。例如，您可以將資料載入至暫存表格、對暫存中的資料執行轉換，然後將資料插入到生產資料表。

架構和數據表名稱

架構是將數據表分組的好方法，以類似的方式一起使用。如果您要將多個資料庫從內部部署解決方案移轉至專用 SQL 集區，最好將所有事實、維度和整合數據表移轉至專用 SQL 集區中的一個架構。

例如，您可以將所有數據表儲存在WideWorldImportersDW範例專用SQL集區內一個名為的架構wwi中。下列程式碼會建立名為 wwi 的使用者定義的結構描述。

CREATE SCHEMA wwi;

若要在專用 SQL 集區中顯示資料表的組織，您可以使用事實、dim 和 int 做為數據表名稱的前置詞。下表顯示的 WideWorldImportersDW一些架構和數據表名稱。

WideWorldImportersDW 數據表	資料表類型	專用 SQL 集區
縣/市	維度	wwi.DimCity
訂單	事實	wwi.FactOrder

數據表持續性

數據表會將數據永久儲存在 Azure 儲存體中、暫時儲存在 Azure 儲存體，或儲存在專用 SQL 集區外部的數據存放區中。

一般數據表

一般數據表會將數據儲存在 Azure 儲存體，作為專用SQL集區的一部分。不論會話是否開啟，數據表和數據都會保存。下列範例會建立具有兩個數據行的一般數據表。

CREATE TABLE MyTable (col1 int, col2 int );

暫存資料表

臨時表只存在於會話的持續時間內。您可以使用臨時表來防止其他使用者看到暫存結果，也減少清除的需求。

臨時表會利用本機記憶體來提供快速的效能。如需詳細資訊，請參閱臨時表。

外部資料表 (部分內容可能是機器或 AI 翻譯)

外部數據表會指向位於 Azure 儲存體 Blob 或 Azure Data Lake Store 中的數據。搭配 CREATE TABLE AS SELECT 語句使用時，從外部數據表選取會將數據匯入專用 SQL 集區。

因此，外部數據表對於載入數據很有用。如需載入教學課程，請參閱使用PolyBase從 Azure Blob 記憶體載入數據。

資料類型

專用 SQL 集區支援最常用的數據類型。如需支持的數據類型清單，請參閱 CREATE TABLE 語句中的 CREATE TABLE 參考中的數據類型。如需使用數據類型的指引，請參閱數據類型。

分散式資料表

專用 SQL 集區的基本功能是它可以跨散發來儲存及操作數據表的方式。專用 SQL 集區支援三種方法來散發數據：迴圈配置資源（預設）、哈希和複寫。

雜湊分散式資料表

哈希分散式數據表會根據散發數據行中的值來散發數據列。哈希分散式數據表的設計目的是要針對大型數據表的查詢達到高效能。選擇散發數據行時，需要考慮幾個因素。

如需詳細資訊，請參閱分散式數據表的設計指引。

複寫資料表

復寫數據表具有每個計算節點上可用的數據表完整複本。查詢會在復寫的數據表上快速執行，因為複寫數據表上的聯結不需要移動數據。不過，復寫需要額外的記憶體，而且對於大型數據表來說並不實用。

如需詳細資訊，請參閱複寫資料表的設計指引。

迴圈配置資源數據表

迴圈配置資源數據表會將數據表數據列平均分散到所有散發。數據列會隨機散發。將數據載入迴圈配置資源數據表的速度很快。請記住，查詢可能需要比其他散發方法更多的數據移動。

如需詳細資訊，請參閱分散式數據表的設計指引。

數據表的常見散發方法

資料表類別通常會決定應選擇哪個選項來散發資料表。

數據表類別	建議的散發選項
事實	將雜湊散發與資料行存放區索引搭配使用。在同一個散發資料行上聯結兩個雜湊表時，即可提高效能。
維度	針對較小的資料表使用複寫資料表。如果資料表太大而無法儲存於每個計算節點上，請使用雜湊分散式資料表。
預備	針對暫存表格使用循環配置資源。 CTAS 的載入速度很快。一旦資料進入暫存表格，就使用 INSERT...SELECT 將資料移至生產資料表。

注意

如需根據工作負載採用最佳資料表散發策略的相關建議，請參閱 Azure Synapse SQL Distribution Advisor。

表格分割區

數據分割數據表會根據數據範圍來儲存及執行數據表數據列的作業。例如，數據表可以依日、月或年分割。您可以透過數據分割消除來改善查詢效能，這會將查詢掃描限制為數據分割內的數據。您也可以透過分割區切換來維護數據。由於 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。檢查專用 SQL 集區數據表條件約束。

用於建立數據表的命令

您可以將資料表建立為新的空白資料表。您也可以建立資料表，並使用 select 陳述式的結果填入該資料表。以下是用來建立資料表的 T-SQL 命令。

T-SQL 語句	說明
CREATE TABLE	透過定義所有資料表資料行和選項來建立空白資料表。
CREATE EXTERNAL TABLE	建立外部資料表。數據表的定義會儲存在專用SQL集區中。數據表數據會儲存在 Azure Blob 記憶體或 Azure Data Lake Store 中。
CREATE TABLE AS SELECT	將 select 陳述式的結果填入新資料表。資料表資料行和資料類型基於 select 陳述式結果。若要匯入資料，可以從外部資料表中選取此陳述式。
CREATE EXTERNAL TABLE AS SELECT	藉由將 select 語句的結果匯出至外部位置，以建立新的外部數據表。位置為 Azure Blob 記憶體或 Azure Data Lake Store。

將源數據與專用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 集區，並檢閱 Azure Synapse Analytics 中專用 SQL 集區的數據載入策略。

共用方式為

在 Azure Synapse Analytics 中使用專用 SQL 集區設計數據表

判斷資料表類別

架構和數據表名稱

數據表持續性

一般數據表

暫存資料表

外部資料表 (部分內容可能是機器或 AI 翻譯)

資料類型

分散式資料表

雜湊分散式資料表

複寫資料表

迴圈配置資源數據表

數據表的常見散發方法

表格分割區

資料行存放區索引

統計資料

主鍵和唯一索引鍵

用於建立數據表的命令

將源數據與專用SQL集區對齊

不支持的數據表功能

資料表大小查詢

數據表空間摘要

依分佈類型排序的數據表空間

依索引類型排序的數據表空間

分佈空間摘要

意見反應

其他資源

共用方式為

在 Azure Synapse Analytics 中使用專用 SQL 集區設計數據表

判斷資料表類別

架構和數據表名稱

數據表持續性

一般數據表

暫存資料表

外部資料表 (部分內容可能是機器或 AI 翻譯)

資料類型

分散式資料表

雜湊分散式資料表

複寫資料表

迴圈配置資源數據表

數據表的常見散發方法

表格分割區

資料行存放區索引

統計資料

主鍵和唯一索引鍵

用於建立數據表的命令

將源數據與專用SQL集區對齊

不支持的數據表功能

資料表大小查詢

數據表空間摘要

依分佈類型排序的數據表空間

依索引類型排序的數據表空間

分佈空間摘要

相關內容

意見反應

其他資源