使用 Synapse SQL 集區設計數據表

提示

Microsoft Fabric Data Warehouse 是一個企業規模的關聯式倉庫，建立在資料湖基礎上，具備未來準備架構、內建 AI 及新功能。 如果你是資料倉儲新手，建議先從Fabric Data Warehouse開始。 現有的 專用 SQL 工作負載可升級至 Fabric，以取得資料科學、即時分析與報告等多項新功能。

• 開始Fabric免費試用。
• Fabric Data Warehouse 的遷移助手。

本文說明在 Azure Synapse Analytics 中使用專用 SQL 集區和無伺服器 SQL 集區來設計數據表的重要概念。

• 無伺服器 SQL 集區 是一種在您的資料湖中運行的查詢服務。 它沒有數據擷取的本機記憶體。
• 專用 SQL 集 區代表使用 Synapse SQL 時所布建的分析資源集合。 專用 SQL 集區的大小取決於 資料倉儲 單位（DWU）。

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

主題	專用 SQL 集區	無伺服器 SQL 集區
數據表類別	是	否
架構名稱	是	是
數據表名稱	是	不
數據表持續性	是	否
一般數據表	是	不
臨時表	是	是
外部資料表	是	是
資料類型	是	是
分散式數據表	是	否
循環表	是	No
哈希分散式數據表	是	不
複寫的數據表	是	No
數據表的常見散發方法	是	No
分區	是	是
資料行存放區索引	是	No
統計資料	是	是
主鍵和唯一鍵	是	No
用於建立數據表的命令	是	No
將源數據與數據倉儲對齊	是	No
不支持的數據表功能	是	No
資料表大小查詢	是	No

資料表類別

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

• 事實資料表包含通常在交易系統中產生的量化資料，然後載入到資料倉儲中。 例如，零售業務每天會產生銷售交易，然後將資料載入到資料倉儲事實資料表進行分析。

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

• 整合資料表提供了整合或暫存資料的位置。 您可以將整合資料表建立為一般數據表、外部數據表或臨時表。 例如，您可以將資料載入至暫存表格、對暫存中的資料執行轉換，然後將資料插入到生產資料表。

模式名稱

架構是將類似方式使用的物件分組的好方法。 下列程式代碼會建立名為 wwi 的使用者定義架構。

CREATE SCHEMA wwi;

資料表名稱

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

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

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

數據表持續性

數據表會將數據永久儲存在 Azure 儲存體、暫時儲存在 Azure 儲存體 或數據倉儲外部的數據存放區中。

一般表格

一般數據表會將數據儲存在數據倉儲 Azure 儲存體 中。 無論工作階段是否開啟，資料表和資料都會持續存在。 下列範例會建立具有兩個數據行的一般數據表。

CREATE TABLE MyTable (col1 int, col2 int );  

暫存資料表

臨時表只存在於會話的持續時間內。 您可以使用臨時表來防止其他使用者看到暫存結果。 使用臨時表也能減少清理的需要。 臨時表會利用本機記憶體，並在專用 SQL 集區中提供更快的效能。

無伺服器 SQL 集區支持臨時表，但其使用方式有限，因為您可以從臨時表選取，但無法將它與記憶體中的檔案聯結。

如需詳細資訊，請參閱 臨時表。

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

外部數據表會指向位於 Azure 儲存體 Blob 或 Azure Data Lake Storage 中的數據。

您可以使用 CREATE TABLE AS SELECT （CTAS） 語句，將數據從外部數據表匯入專用 SQL 集區。 如需載入教學課程，請參閱 載入紐約 Taxicab 數據集。

針對無伺服器 SQL 集區，您可以使用 CREATE EXTERNAL TABLE AS SELECT （CETAS） 將查詢結果儲存至 Azure 儲存體 中的外部數據表。

資料類型

專用 SQL 集區支援最常用的數據類型。 如需支持的數據類型清單，請參閱 CREATE TABLE 參考中的數據類型。 如需使用數據類型的詳細資訊，請參閱 Synapse SQL 中的數據表數據類型。

分散式資料表

專用 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 支援自動建立統計數據。

統計更新不會自動進行。 您可以在新增或變更大量數據列之後更新統計數據。 例如，在載入之後更新統計資料。 如需詳細資訊，請參閱 Synapse SQL 中的統計數據。

主鍵和唯一鍵

針對專用 SQL 集區，只有在同時使用 NONCLUSTERED 和 NOT ENFORCED 的情況下才支援 PRIMARY KEY。 UNIQUE 條件約束只有在使用 NOT ENFORCED 時才受到支持。 如需詳細資訊，請參閱 使用專用 SQL 集區的主鍵、外鍵和唯一密鑰。

用於建立數據表的命令

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

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

將來源資料與資料倉儲對齊

從另一個數據源載入數據，以填入專用 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 集區