使用 PolyBase 虚拟化增量表

  • 项目

适用于：SQL Server 2022 (16.x) 及更高版本

SQL Server 2022 (16.x) 可以直接从 delta 表文件夹查询数据。 此概念通常称为数据虚拟化，它允许将数据保留在其原始位置，但可以使用 T-SQL 命令从 SQL Server 实例进行查询，就像任何其他表一样。 此功能使用 PolyBase 连接器，并最大程度地减少通过 ETL 进程复制数据的需求。

在下面的示例中，增量表文件夹存储在 Azure Blob 存储上，并通过 OPENROWSET 或外部表进行访问。

有关数据虚拟化的详细信息，请参阅 PolyBase 数据虚拟化简介。

预配置

1.在 sp_configure 中启用 PolyBase

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

RECONFIGURE;

2.创建用户数据库

本练习使用默认设置和位置创建示例数据库。 你将使用此空示例数据库来处理数据并存储限定范围的凭证。 此示例将使用名为 Delta_demo 的新的空数据库。

CREATE DATABASE [Delta_demo];

3.创建主密钥和数据库范围的凭据

需要用户数据库中的数据库主密钥来加密数据库范围的凭据机密，delta_storage_dsc。 对于此示例，增量表驻留在 Azure Data Lake Storage Gen2 上。

USE [Delta_demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

4.创建外部数据源

数据库范围的凭证用于外部数据源。 在此示例中，Delta 表驻留在 Azure Data Lake Storage Gen2 中，因此请使用前缀 adls 和 SHARED ACCESS SIGNATURE 标识方法。 有关连接器和前缀的详细信息，包括 SQL Server 2022 (16.x) 的新设置，请参阅创建外部数据源。

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

例如，如果存储帐户命名为 delta_lake_sample 且容器命名为 sink，则代码会为：

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://sink@delta_lake_sample.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

使用 OPENROWSET 访问数据

此示例中的模拟运算表文件夹名为 Contoso。

因为外部数据源 Delta_ED 映射到容器级别。 Contoso 增量表文件夹位于根目录中。 要查询文件夹结构中的文件，请提供相对于外部数据源的 LOCATION 参数的文件夹映射。

SELECT * FROM OPENROWSET
(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS [result];

使用外部表查询数据

“创建外部表”还可用于虚拟化 SQL Server 中的增量表数据。 必须定义列并将其强类型化。 虽然创建外部表需要付出更多努力，但它们也比使用 OPENROWSET 查询外部数据源具有其他优势。 你可以：

  • 加强给定列的数据键入的定义
  • 定义为 Null 性
  • 定义排序规则
  • 为列创建统计信息以优化查询计划的质量
  • 在 SQL Server 中创建更精细的模型，以便进行数据访问，以增强安全模型

有关详细信息，请参阅创建外部表。

以下示例将使用相同的数据源。

1.创建外部文件格式

要定义文件的格式设置，需要外部文件格式。 出于可重用性，也建议使用外部文件格式。 有关详细信息，请参阅创建外部文件格式。

CREATE EXTERNAL FILE FORMAT DeltaTableFormat WITH(FORMAT_TYPE = DELTA);

2.创建外部表

增量表文件位于 /delta/Delta_yob/ ，此示例的外部数据源是 S3 兼容的对象存储，以前在数据源 s3_eds下配置。 PolyBase 可以将其用作增量表文件夹或绝对文件本身（位于 delta/Delta_yob/_delta_log/00000000000000000000.json）的 LOCATION。

-- Create External Table using delta
CREATE EXTERNAL TABLE extCall_Center_delta (
    id INT,
    name VARCHAR(200),
    dob DATE
)
WITH (
        LOCATION = '/delta/Delta_yob/',
        FILE_FORMAT = DeltaTableFormat,
        DATA_SOURCE = s3_eds
);
GO

限制

如果创建指向已分区 Delta 表的外部表，则用于分区的列将在查询外部表时返回 NULL。 但如果使用 OPENROWSET 查询，则会正确返回列值。 要解决此限制，可为 OPENROWSET 查询创建一个视图，然后查询该视图来获取分区列的值以确保能够正确返回。

查询外部 Delta 表时可能会遇到以下错误：

Msg 2571, Level 14, State 3, Line 1
User '<user>' does not have permission to run DBCC TRACEON.
Msg 16513, Level 16, State 0, Line 1
Error reading external metadata.

之所以发生这种情况，是因为有一个 QUERYTRACEON 查询提示可以添加到 Delta 文件元数据查询，并且需要 sysadmin 服务器角色才能执行。 如果发生这种情况，可以通过全局启用跟踪标志 14073 来解决此问题，这将阻止添加查询提示。

相关内容