使用 Azure Synapse Analytics 的 Synapse SQL 設計資料表

本文件包含使用專用 SQL 集區和無伺服器 SQL 集區設計資料表的重要概念。

無伺服器 SQL 集區是針對資料湖資料進行的查詢服務， 沒有用於資料擷取的本機儲存體。 專用 SQL 集區代表使用 Synapse SQL 時佈建的分析資源集合。 專用 SQL 集區的大小取決於資料倉儲單位 (DWU)。

下表列出與專用 SQL 集區與無伺服器 SQL 集區相關的主題：

主題	專用 SQL 集區	無伺服器 SQL 集區
決定資料表類別	是	否
結構描述名稱	是	是
資料表名稱	是	否
資料表持續性	是	否
一般資料表	是	否
暫存資料表	是	是
外部資料表	是	是
資料類型	是	是
分散式資料表	是	否
雜湊分散式資料表	是	否
複寫資料表	是	否
循環配置資源資料表	是	否
資料表常用的散發方法	是	否
資料分割	是	是
資料行存放區索引	是	否
統計資料	是	是
主索引鍵和唯一索引鍵	是	否
建立資料表的命令	是	否
找出資料倉儲對應的資料來源	是	否
不支援的資料表功能	是	否
資料表大小查詢	是	否

決定資料表類別

星狀結構描述會將資料分類為事實和維度資料表。 移至事實或維度資料表前，部分資料表會作為整合或暫存資料。 設計資料表時，請決定資料表的資料將屬於事實、維度還是整合資料表。 此決定將使資料表具有適當的結構和散發機制。

• 事實資料表包含通常在交易式系統中產生、接著載入至資料倉儲的量化資料。 例如，零售商每天都會產生銷售交易，然後將資料載入資料倉儲事實資料表中，以進行分析。

• 維度資料表包含可能會變更、但變更頻率通常不高的屬性資料。 例如，客戶的名稱和地址會儲存在維度資料表中，但只有在客戶的設定檔有所變更時，才會更新。 若要降低大型事實資料表的大小，則不需將客戶的名稱和地址放在事實資料表的每個資料列中。 此時，事實資料表與維度資料表可以共用客戶識別碼。 查詢可以聯結兩個資料表，使客戶的設定檔與交易產生關聯。

• 整合資料表可用來整合或暫存資料。 您可以建立整合資料表作為一般資料表、外部資料表或暫存資料表。 例如，您可以將資料載入至暫存資料表、對暫存的資料執行轉換，然後將該資料插入生產資料表中。

結構描述名稱

結構描述很適合用於群組類似使用方式的物件。 下列程式碼會建立名為 wwi 的使用者定義結構描述。

CREATE SCHEMA wwi;

資料表名稱

如果您要移轉內部署方案的多個資料庫至專用 SQL 集區，最佳做法是移轉所有事實、維度和整合資料表至一個 SQL 集區結構描述。 例如，您可以將所有資料表儲存在 WideWorldImportersDW 範例資料倉儲中一個名為 wwi 的結構描述內。

若要顯示專用 SQL 集區的資料表組織，您可使用 fact、dim、int 作為資料表名稱的前置詞。 下表顯示 WideWorldImportersDW 部分的結構描述和資料表名稱。

WideWorldImportersDW 資料表	資料表類型	專用 SQL 集區
City	尺寸	wwi.DimCity
順序	事實	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 集區支援三種發佈資料的方法：

• 循環配置資源 (預設)
• 雜湊
• 複寫

雜湊分散式資料表

雜湊分散資料表會根據散發資料行中的值來散發資料列。 雜湊分散資料表的目的是實現大型資料表查詢的高效能。 選擇散發資料行時，要考慮幾個因素。

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

複寫資料表

複寫資料表有一個可在每個計算節點上使用的完整資料表複本。 因為複寫資料表的聯結不需要移動資料，複寫資料表可以快速執行查詢。 但複寫需要額外的儲存體，所以不適用大型資料表。

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

循環配置資源資料表

循環配置資源資料表會將資料表的資料列平均散發於所有散發。 資料列會隨機分散。 資料可以快速載入循環配置資源資料表。 但查詢可能比其他發佈方法需要更多的資料移動。

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

資料表常用的散發方法

資料表類別通常會決定資料表發佈的最佳選項。

資料表類別	建議的散發選項
事實	使用具有叢集資料行存放區索引的雜湊散發。 在相同的散發資料行上聯結兩個雜湊資料表時，可以改善效能。
維度	對較小的資料表使用複寫。 如果資料表太大而無法儲存在每個計算節點上，請使用雜湊散發。
預備	對暫存資料表使用循環配置資源。 使用 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 時支援主索引鍵。 只在使用 NOT ENFORCED 時支援 UNIQUE 限制式。 如需詳細資訊，請參閱專用 SQL 集區資料表條件約束一文。

建立資料表的命令

若是專用 SQL 集區，您可以將資料表建立為新的空白資料表。 您也可以在建立資料表後填入 Select 陳述式的結果。 以下是用來建立資料表的 T-SQL 命令。

T-SQL 陳述式	Description
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 集區中，識別每 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 集區。