创建数据仓库表

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 池表上进行索引。

发运

Azure Synapse Analytics 专用 SQL 池使用大规模并行处理 (MPP) 体系结构，而不是大多数 OLTP 数据库系统中使用的对称多重处理 (SMP) 体系结构。在 MPP 系统中，表中的数据被分布在节点池中进行处理。 Synapse Analytics 支持以下类型的分布：

哈希：为指定的列计算一个确定的哈希值，用于将行分配给计算节点。
轮循机制：行在所有计算节点之间均匀分布。
复制：表的副本存储在每个计算节点上。

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

表类型	建议的分布选项
维度	将复制分布用于较小的表，以避免在联接到分布式事实数据表时发生数据混排。如果表太大，无法在每个计算节点上存储，可以使用哈希分布。
Fact	将哈希分布与聚集列存储索引配合使用，跨计算节点分布事实数据表。
临时过程	将轮循机制分布用于临时表，以便跨计算节点均匀分布数据。

提示

有关 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
);

提示

为日期创建维度表时，一个常见的模式是将 DDMMYYYY 或 YYYYMMDD 格式的数字日期用作整数代理键，将 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 使用外部表。

继续