建立資料倉儲資料表

10 分鐘

在您已了解關聯式資料倉儲結構描述的基本架構準則之後，現在讓我們來探索如何建立資料倉儲。

建立專用 SQL 集區

若要在 Azure Synapse Analytics 中建立關聯式資料倉儲，您必須建立專用 SQL 集區。在現有 Azure Synapse Analytics 工作區中執行此作業的最簡單方式，就是使用 Azure Synapse Studio 中的 [管理] 頁面，如下所示：

A screenshot of the SQL pools tab in the Manage page of Synapse Studio.

佈建專用 SQL 集區時，您可以指定下列組態設定：

專用 SQL 集區的唯一名稱。
SQL 集區的效能等級，範圍可從「DW100c」到「DW30000c」，且其會決定集區執行時每小時的成本。
無論是從空集區開始，還是從備份中還原現有資料庫。
SQL 集區的「定序」，其會決定資料庫的排序次序和字串比較規則。 (您無法在建立之後變更定序)。

建立專用 SQL 集區之後，您可以在 Synapse Studio 的 [管理] 頁面中控制其執行狀態；在不需要時加以暫停，以避免產生不必要的成本。

當集區正在執行時，您可以在 [資料] 頁面上探索該集區，並建立 SQL 指令碼以在其中執行。

建立資料表的考量

若要在專用 SQL 集區中建立資料表，您可以使用 CREATE TABLE (或有時使用 CREATE EXTERNAL TABLE) Transact-SQL 陳述式。陳述式中使用的特定選項取決於您建立的資料表類型，其中包括：

事實資料表
維度資料表
暫存資料表

注意

資料倉儲是由「事實」和「維度」資料表所組成，如先前所述。「暫存」資料表通常會用來作為資料倉儲載入程序的一部分，以內嵌來源系統的資料。

在為中小型資料集設計星型結構描述模型時，您可以使用慣用的資料庫，例如 Azure SQL。如果是較大的資料集，在 Azure Synapse Analytics (而不是 SQL Server) 中實作資料倉儲可能會有好處。請務必了解在 Synapse Analytics 中建立資料表時的一些主要差異。

資料完整性條件約束

Synapse Analytics 中的專用 SQL 集區不支援可在 SQL Server 等其他關聯式資料庫系統中所找到的「外部索引鍵」和「唯一」條件約束。這表示用來載入資料的作業必須維持索引鍵的唯一性和參考完整性，而不需依賴資料庫中的資料表定義來執行此作業。

提示

如需 Azure Synapse Analytics 專用 SQL 集區中的條件約束詳細資訊，請參閱 Azure Synapse Analytics 中使用專用 SQL 集區的主索引鍵、外部索引鍵和唯一索引鍵。

索引數

雖然 Synapse Analytics 專用 SQL 集區支援在 SQL Server 中找到的「叢集」索引，但預設索引類型為「叢集資料行存放區」。此索引類型在查詢一般資料倉儲結構描述中的大量資料時，可提供顯著的效能優勢，而且應該盡可能使用。不過，某些資料表可能包括無法包括於叢集資料行存放區索引中的資料類型 (例如 VARBINARY (MAX))，在這類情況下可以改為使用叢集索引。

提示

如需在 Azure Synapse Analytics 專用 SQL 集區中編製索引的詳細資訊，請參閱 Azure Synapse Analytics 中專用 SQL 集區資料表上的索引。

Distribution

Azure Synapse Analytics 專用 SQL 集區會使用大量平行處理 (MPP) 架構，而不是大多數 OLTP 資料庫系統中使用的對稱式多處理 (SMP) 架構。在 MPP 系統中，資料表中的資料會散發於節點集區中以進行處理。 Synapse Analytics 支援下列類型的散發：

雜湊：確定性雜湊會針對指定的資料行進行計算，並用來將資料列指派給計算節點。
循環配置資源：資料列會在所有計算節點之間平均散發。
複寫：資料表的複本會儲存於每個計算節點上。

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

資料表類型	建議的散發選項
維度	針對較小型資料表使用複寫散發，以避免聯結至分散式事實資料表時發生資料隨機顯示。如果資料表太大而無法儲存於每個計算節點上，請使用雜湊散發。
事實	使用雜湊散發搭配叢集資料行存放區索引，將事實資料表散發到計算節點。
預備	針對暫存資料表使用循環配置資源散發，將資料平均散發到計算節點。

提示

如需 Azure Synapse Analytics 中資料表散發策略的詳細資訊，請參閱在 Azure Synapse Analytics 中使用專用 SQL 集區設計分散式資料表的指南。

建立維度資料表

當您建立維度資料表時，請確定資料表定義包括代理和替代索引鍵，以及適用於您要用來群組彙總之維度屬性的資料行。通常，最簡單的方式是使用 IDENTITY 資料行自動產生遞增的代理索引鍵 (否則，每次載入資料時都需產生唯一索引鍵)。下列範例顯示適用於假設性 DimCustomer 維度資料表的 CREATE TABLE 陳述式。

CREATE TABLE dbo.DimCustomer
(
    CustomerKey INT IDENTITY NOT NULL,
    CustomerAlternateKey NVARCHAR(15) NULL,
    CustomerName NVARCHAR(80) NOT NULL,
    EmailAddress NVARCHAR(50) NULL,
    Phone NVARCHAR(25) NULL,
    StreetAddress NVARCHAR(100),
    City NVARCHAR(20),
    PostalCode NVARCHAR(10),
    CountryRegion NVARCHAR(20)
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

注意

如有需要，您可以建立特定的「結構描述」作為資料表的命名空間。在此範例中，會使用預設的 dbo 結構描述。

如果您想要使用「雪花式」結構描述 (其中的維度資料表會彼此相關)，就應該在「子」維度資料表的定義中包括「父」維度的索引鍵。例如，下列 SQL 程式碼可用來將地理位址詳細資料從 DimCustomer 資料表移至個別的 DimGeography 維度資料表：

CREATE TABLE dbo.DimGeography
(
    GeographyKey INT IDENTITY NOT NULL,
    GeographyAlternateKey NVARCHAR(10) NULL,
    StreetAddress NVARCHAR(100),
    City NVARCHAR(20),
    PostalCode NVARCHAR(10),
    CountryRegion NVARCHAR(20)
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

CREATE TABLE dbo.DimCustomer
(
    CustomerKey INT IDENTITY NOT NULL,
    CustomerAlternateKey NVARCHAR(15) NULL,
    GeographyKey INT NULL,
    CustomerName NVARCHAR(80) NOT NULL,
    EmailAddress NVARCHAR(50) NULL,
    Phone NVARCHAR(25) NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

時間維度資料表

大多數資料倉儲都包括「時間」維度資料表，可讓您依多個階層式時間間隔層級彙總資料。例如，下列範例會建立 DimDate 資料表，其中包含與特定日期相關的屬性。

CREATE TABLE dbo.DimDate
( 
    DateKey INT NOT NULL,
    DateAltKey DATETIME NOT NULL,
    DayOfMonth INT NOT NULL,
    DayOfWeek INT NOT NULL,
    DayName NVARCHAR(15) NOT NULL,
    MonthOfYear INT NOT NULL,
    MonthName NVARCHAR(15) NOT NULL,
    CalendarQuarter INT  NOT NULL,
    CalendarYear INT NOT NULL,
    FiscalQuarter INT NOT NULL,
    FiscalYear INT NOT NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

提示

建立日期維度資料表時常見的模式是使用「DDMMYYYYY」或「YYYMMDD」格式的數值日期作為整數代理索引鍵，並使用 DATE 或 DATETIME 資料類型的日期作為替代索引鍵。

建立事實資料表

事實資料表包括其相關之每個維度的索引鍵，以及您想要分析之特定事件或觀察的屬性和數值量值。

下列程式碼範例會建立名為 FactSales 的假設性事實資料表，該資料表會透過索引鍵資料行 (日期、客戶、產品和商店) 來與多個維度相關。

CREATE TABLE dbo.FactSales
(
    OrderDateKey INT NOT NULL,
    CustomerKey INT NOT NULL,
    ProductKey INT NOT NULL,
    StoreKey INT NOT NULL,
    OrderNumber NVARCHAR(10) NOT NULL,
    OrderLineItem INT NOT NULL,
    OrderQuantity SMALLINT NOT NULL,
    UnitPrice DECIMAL NOT NULL,
    Discount DECIMAL NOT NULL,
    Tax DECIMAL NOT NULL,
    SalesAmount DECIMAL NOT NULL
)
WITH
(
    DISTRIBUTION = HASH(OrderNumber),
    CLUSTERED COLUMNSTORE INDEX
);

建立暫存資料表

暫存資料表會用來作為資料在載入資料倉儲期間的暫存儲存體。典型模式是建構資料表，使其盡可能有效率地將其外部來源 (通常是資料湖中的檔案) 的資料內嵌到關聯式資料庫中，然後使用 SQL 陳述式來將暫存資料表的資料載入維度和事實資料表。

下列程式碼範例會為最終將載入維度資料表的產品資料建立暫存資料表：

CREATE TABLE dbo.StageProduct
(
    ProductID NVARCHAR(10) NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    ProductCategory NVARCHAR(200) NOT NULL,
    Color NVARCHAR(10),
    Size NVARCHAR(10),
    ListPrice DECIMAL NOT NULL,
    Discontinued BIT NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

使用外部資料表

在某些情況下，如果要載入的資料位於具有適當結構的檔案中，建立參考檔案位置的外部資料表會更有效率。如此一來，就可以直接從來源檔案讀取資料，而不需載入關聯式存放區。下列範例示範如何建立外部資料表，以參考與 Synapse 工作區相關聯之資料湖中的檔案：


-- External data source links to data lake location
CREATE EXTERNAL DATA SOURCE StagedFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/stagedfiles/'
);
GO

-- External format specifies file format
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

-- External table references files in external data source
CREATE EXTERNAL TABLE dbo.ExternalStageProduct
(
    ProductID NVARCHAR(10) NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    ProductCategory NVARCHAR(200) NOT NULL,
    Color NVARCHAR(10),
    Size NVARCHAR(10),
    ListPrice DECIMAL NOT NULL,
    Discontinued BIT NOT NULL
)
WITH
(
    DATA_SOURCE = StagedFiles,
    LOCATION = 'products/*.parquet',
    FILE_FORMAT = ParquetFormat
);
GO

注意

如需使用外部資料表的詳細資訊，請參閱 Azure Synapse Analytics 文件中的搭配 Synapse SQL 使用外部資料表。