使用 Azure SQL 数据库进行数据虚拟化（预览版）

适用于：Azure SQL 数据库

使用 Azure SQL 数据库的数据虚拟化功能，可以针对以 CSV（无需使用带分隔符的文本）、Parquet 和 Delta（1.0）等常用数据格式的文件执行 Transact-SQL（T-SQL）查询。 可以在 Azure Data Lake Storage Gen2 或 Azure Blob 存储中查询此数据，并使用联接将其与本地存储的关系数据合并。 这样一来，你可以透明地访问外部数据（以只读模式），同时保持其原始格式和位置（也称为数据虚拟化）。

概述

数据虚拟化提供了两种方法来查询适用于不同方案集的文件：

• OPENROWSET 语法 - 针对文件的即席查询进行了优化。 通常用于快速浏览新文件集的内容和结构。
• CREATE EXTERNAL TABLE 语法 - 使用完全相同的语法对重复查询文件进行优化，就像数据存储在数据库本地一样。 与 OPENROWSET 语法相比，外部表需要几个准备步骤，但允许对数据访问进行更多的控制。 外部表通常用于分析工作负载和报表。

在任一情况下，都必须使用 CREATE EXTERNAL DATA SOURCE T-SQL 语法创建外部数据源，如本文中所示。

文件格式

直接支持 (CSV) 文件格式的 Parquet 和分隔文本。 通过指定 CSV 文件格式（其中的查询以单独的行形式返回每个文档），间接支持 JSON 文件格式。 可以使用 JSON_VALUE 和 OPENJSON 进一步分析行。

存储类型

文件可以存储在 Azure Data Lake Storage Gen2 或 Azure Blob 存储中。 若要查询文件，需要以特定格式提供位置，并使用与外部源和终结点/协议的类型相对应的位置类型前缀，如以下示例所示：

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet
--or
abs://<storage_account_name>.blob.core.windows.net/<container_name>/

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet
--or
adls://<storage_account_name>.dfs.core.windows.net/<container_name>/

开始吧

如果你不熟悉数据虚拟化，并且想要快速测试功能，请先查询 Azure 开放数据集中可用的公开数据集，如允许匿名访问的必应 COVID-19 数据集。

使用以下终结点查询必应 COVID-19 数据集：

• Parquet： abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
• CSV：abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

要快速入门，请运行此简单的 T-SQL 查询，以首先了解数据集。 此查询使用 OPENROWSET 查询存储在公开可用的存储帐户中的文件：

--Quick query on a file stored in a publicly available storage account:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet',
 FORMAT = 'parquet'
) AS filerows;

可以通过根据第一个查询的结果集追加WHEREGROUP BY其他子句来继续浏览数据集。

熟悉如何查询公共数据集后，可以考虑切换到需要提供凭据、授予访问权限和配置防火墙规则的非公共数据集。 在许多实际方案中，你将主要使用专用数据集进行操作。

访问非公共存储帐户

登录到 Azure SQL 数据库的用户必须有权访问和查询存储在非公共存储帐户中的文件。 授权步骤取决于 Azure SQL 数据库对存储进行身份验证的方式。 身份验证类型和任何相关参数不会直接随每个查询一起提供。 它们封装在存储在用户数据库中的数据库范围的凭据对象中。 数据库在查询执行时使用凭据访问存储帐户。

Azure SQL 数据库支持以下身份验证类型：

• 共享访问签名 (SAS)
• 托管标识
• 通过用户标识Microsoft Entra 直通身份验证

通过匿名帐户访问公共存储

如果所需的数据集允许公共访问（也称为匿名访问），则只要正确配置 Azure 存储，就不需要凭据，请参阅 为容器和 Blob 配置匿名读取访问权限。

外部数据源

外部数据源是可实现跨多个查询轻松引用文件位置的抽象。 若要查询公共位置，只需在创建外部数据源时指定文件位置：

CREATE EXTERNAL DATA SOURCE MyExternalDataSource
WITH (
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
);

访问非公开存储帐户以及位置时，还需要使用封装的身份验证参数引用数据库范围的凭据。 以下脚本创建指向文件路径的外部数据源，并引用数据库范围的凭据。

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
WITH (
    LOCATION = 'abs://<privatecontainer>@privatestorageaccount.blob.core.windows.net/dataset/' 
       CREDENTIAL = [MyCredential]
);

使用 OPENROWSET 查询数据源

OPENROWSET 语法允许即时即席查询，同时仅创建所需最少数量的数据库对象。

OPENROWSET 只需要创建外部数据源（可能还有凭据），不需要创建外部表，后者需要外部文件格式和“外部表”本身。

DATA_SOURCE参数值自动预置到 BULK 参数，以形成文件的完整路径。

当使用 OPENROWSET 时，请提供文件的格式，如以下示例，它将查询单个文件：

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'bing_covid-19_data.parquet',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

查询多个文件和文件夹

OPENROWSET命令还允许使用大容量路径中的通配符查询多个文件或文件夹。

以下示例使用纽约市黄色出租车行程记录公开数据集。

首先，创建外部数据源：

--Create the data source first:
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

现在，我们可以查询文件夹中具有 .parquet 扩展名的所有文件。 例如，此处仅查询与名称模式匹配的文件：

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

查询多个文件或文件夹时，使用单个 OPENROWSET 访问的所有文件必须具有相同的结构(如列数和数据类型相同）。 不能以递归方式遍历文件夹。

模式推理

如果你不了解文件架构，自动架构推理有助于快速编写查询，并浏览数据。 架构推理仅适用于 parquet 文件。

虽然方便，但推断的数据类型可能大于实际数据类型，因为源文件中可能有足够的信息来确保使用适当的数据类型。 这可能导致查询性能不佳。 例如，Parquet 文件不包含关于最大字符列长度的元数据，因此实例将它推理为 varchar(8000)。

使用 sp_describe_first_results_set 存储过程检查查询的结果数据类型，如以下示例：

EXEC sp_describe_first_result_set N'
 SELECT
 vendorID, tpepPickupDateTime, passengerCount
 FROM
 OPENROWSET(
  BULK ''yellow/*/*/*.parquet'',
  DATA_SOURCE = ''NYCTaxiExternalDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

知道数据类型后，就可以使用 WITH 子句来指定数据类型，从而提高性能：

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount
FROM
OPENROWSET(
 BULK 'yellow/*/*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000
tpepPickupDateTime datetime2,
passengerCount int
) AS nyc;

由于无法自动确定 CSV 文件的架构，因此必须始终使用 WITH 子句指定列：

SELECT TOP 10 id, updated, confirmed, confirmed_change
FROM OPENROWSET(
 BULK 'bing_covid-19_data.csv',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'CSV',
 FIRSTROW = 2
)
WITH (
 id int,
 updated date,
 confirmed int,
 confirmed_change int
) AS filerows;

文件元数据函数

查询多个文件或文件夹时，可以使用 filepath() 和 filename() 函数读取文件元数据，并获取结果集中的行所源自的文件的部分路径或完整路径和文件名称：

--Query all files and project file path and file name information for each row:
SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder],
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;

如果在不使用参数的情况下调用 filepath() 函数，此函数将返回行的来源文件的路径。 在 DATA_SOURCE 中使用 OPENROWSET 时，它返回相对于 DATA_SOURCE 的路径，否则返回完整文件路径。

如果在使用参数的情况下调用此函数，此函数将返回与该参数中指定的位置上的通配符相匹配的路径部分。 例如，参数值 1 将返回与第一个通配符匹配的路径部分。

filepath()函数还可用于筛选和聚合行：

SELECT
 r.filepath() AS filepath
 ,r.filepath(1) AS [year]
 ,r.filepath(2) AS [month]
 ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
DATA_SOURCE = 'NYCTaxiExternalDataSource',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2017')
 AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
 r.filepath()
 ,r.filepath(1)
 ,r.filepath(2)
ORDER BY
 filepath;

基于 OPENROWSET 创建视图

你可以创建和使用视图来包装 OPENROWSET 查询，以便可以轻松地重复使用基础查询：

CREATE VIEW TaxiRides AS
SELECT *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

还可以使用 filepath() 函数轻松将带有文件位置数据的列添加到视图中，以便进行更简单、更高效的筛选。 使用视图可减少文件数量，以及在视图顶层查询时需要读取和处理的数据量，因为这些列中的任何列都进行了筛选：

CREATE VIEW TaxiRides AS
SELECT *
 , filerows.filepath(1) AS [year]
 , filerows.filepath(2) AS [month]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

视图还启用报表和分析工具（如 Power BI）来使用 OPENROWSET 的结果。

外部表

外部表封装了对文件的访问权限，使查询体验几乎与查询用户表中存储的本地关系数据完全相同。 创建外部表需要外部数据源和外部文件格式对象存在：

--Create external file format
CREATE EXTERNAL FILE FORMAT DemoFileFormat
WITH (
 FORMAT_TYPE=PARQUET
);

--Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);

创建外部表后，可以像对任何其他表一样对其进行查询：

SELECT TOP 10 *
FROM tbl_TaxiRides;

就像 OPENROWSET 一样，外部表允许使用通配符查询多个文件和文件夹。 外部表不支持架构推理。

性能注意事项

对于可以查询的文件数量或数据量没有硬性限制，但查询性能取决于数据量、数据格式、数据的组织方式以及查询和联接的复杂性。

查询分区数据

数据通常以子文件夹（也称为分区）形式组织。 可以指示查询仅读取特定文件夹和文件。 此操作可减少查询需要读取和处理的文件数量和数据量，从而提高性能。 这种类型的查询优化称为分区修剪或分区清除。 可以使用查询子句中的WHERE元数据函数filepath()消除查询执行的分区。

下面的示例查询仅读取 2017 年最后三个月纽约市黄色出租车的数据文件：

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'yellow/puYear=*/puMonth=*/*.parquet',
        DATA_SOURCE = 'NYCTaxiExternalDataSource',
        FORMAT = 'parquet'
    )
WITH (
    vendorID INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

如果存储的数据未分区，请考虑将数据分区以提高查询性能。

如果使用的是外部表，则 filepath() 子句中不支持 filename() 和 WHERE 函数。

故障排除

查询执行问题通常是 Azure SQL 数据库无法访问文件位置的原因。 相关的错误消息可能会报告访问权限不足、位置或文件路径不存在、文件正在被其他进程占用，或者无法列出目录。 在大多数情况下，这表示网络流量控制策略阻止了对文件的访问，或者由于缺少访问权限。 应检查这一点：

• 错误或错误键入的位置路径。
• SAS 密钥有效性：可能已过期、包含拼写错误、以问号开头。
• 允许的 SAS 密钥权限：至少为“读取”权限，如果使用了通配符，还应具有“列出”权限。
• 阻止了存储帐户上的入站流量。 检查 如何管理 Azure 存储的虚拟网络规则。
• 托管标识访问权限：确保向 Azure SQL 数据库的托管标识授予对存储帐户的访问权限。
• 数据库的兼容级别必须为 130 或更高级别，数据虚拟化查询才能正常工作。

局限性

• 目前，Azure SQL 数据库中不支持外部表的统计信息。
• 目前， CREATE EXTERNAL TABLE AS SELECT Azure SQL 数据库上不可用。
• 外部表不支持行级安全功能。
• 无法为外部表中的列定义动态数据掩码规则。
• 如果 Azure 存储帐户位于其他租户中，则托管标识不支持跨租户方案，则共享访问签名是受支持的方法。

已知问题

• 在 SQL Server Management Studio (SSMS) 中启用 Always Encrypted 参数化时，数据虚拟化查询将失败，并显示“Incorrect syntax near 'PUSHDOWN'”错误消息。

相关内容

• OPENROWSET T-SQL
• CREATE EXTERNAL TABLE（创建外部表）
• 创建外部文件格式
• 创建外部数据源