Azure SQL 托管实例的数据虚拟化

  • 项目

适用于：Azure SQL 托管实例

借助 Azure SQL 托管实例的数据虚拟化功能，你可以对 Azure Data Lake Storage Gen2 或 Azure Blob 存储中以通用数据格式存储数据的文件执行 Transact-SQL (T-SQL) 查询，并将其与使用联接的本地存储的关系数据合并在一起。 这样一来，你可以透明地访问外部数据（以只读模式），同时保持其原始格式和位置（也称为数据虚拟化）。

概述

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

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

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

还可以使用适用于 Azure SQL 托管实例的 CREATE EXTERNAL TABLE AS SELECT 语法，以将 T-SQL SELECT 语句的结果导出至 Azure Blob 存储或 Azure Data Lake Storage (ADLS) Gen2 中的 Parquet 或 CSV 文件，并在这些文件的顶部创建外部表。

文件格式

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

存储类型

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

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.chinacloudapi.cn/<path>/<file_name>.parquet

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.chinacloudapi.cn/<path>/<file_name>.parquet

重要

提供的位置类型前缀用于选择最佳通信协议并利用特定存储类型提供的任何高级功能。 禁止使用泛型 https:// 前缀。 始终使用特定于终结点的前缀。

入门

如果你不熟悉数据虚拟化，并且想要快速测试功能，请先查询 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

可以通过根据第一个查询的结果集追加 WHERE、GROUP BY 和其他子句来继续探索数据集。

如果托管实例上的第一个查询失败，则该实例对 Azure 存储帐户的访问可能受限，你需要与网络专家联系以启用访问权限，然后才能继续查询。

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

访问非公共存储帐户

必须向登录到托管实例的用户授予访问和查询存储在非公共存储帐户中的文件的权限。 授权步骤取决于托管实例对存储进行身份验证的方式。 在每个查询中，不会直接提供身份验证类型和任何相关参数。 它们封装在存储在用户数据库中的数据库范围的凭据对象中。 数据库在查询执行时使用凭据访问存储帐户。 Azure SQL 托管实例支持以下两种身份验证类型：

托管标识是 Microsoft Entra ID（以前称为 Azure Active Directory）的一项功能，它通过 Microsoft Entra ID 中托管的标识提供 Azure 服务（如 Azure SQL 托管实例）。 此标识可用于对有关访问非公共存储帐户中的数据的请求授权。 Azure SQL 托管实例等服务具有系统分配的托管标识，还可以具有一个或多个用户分配的托管标识。 可以将系统分配的托管标识或用户分配的托管标识用于 Azure SQL 托管实例的数据虚拟化。

Azure 存储管理员必须先向托管标识授予访问数据的权限。 向托管实例的系统分配的托管标识授予权限的方式与向任何其他 Microsoft Entra 用户授予权限的方式相同。 例如：

  1. 在 Azure 门户中，在存储帐户的“访问控制(IAM)”页中，选择“添加角色分配”。
  2. 选择“存储 Blob 数据读取者”内置的 Azure RBAC 角色。 这将为必要的 Azure Blob 存储容器提供对托管标识的读取访问权限。
    • 你还可以授予对一部分文件的更精细权限，而不是向托管标识授予“存储 Blob 数据读取者”Azure RBAC 角色。 需要访问此容器中某些数据的“读取”单个文件的所有用户还必须对所有父文件夹（直至根目录，即容器）具有“执行”权限。 详细了解如何在 Azure Data Lake Storage Gen2 中设置 ACL。
  3. 在下一页上，选择“将访问权限分配给托管标识”。 “+ 选择成员”，然后在“托管标识”下拉列表下，选择所需的托管标识。 有关详细信息，请参阅使用 Azure 门户分配 Azure 角色。
  4. 然后，创建用于托管标识身份验证的数据库范围的凭据非常简单。 请注意，在下面的示例中，'Managed Identity' 是硬编码的字符串。
-- Optional: Create MASTER KEY if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>'
GO
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'Managed Identity'

外部数据源

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

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://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
        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;
--List all paths:
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;

如果在不使用参数的情况下调用 filepath() 函数，此函数将返回行的来源文件的路径。 在 OPENROWSET 中使用 DATA_SOURCE 时，它返回相对于 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
)
GO

--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
);
GO

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

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;

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

如果使用的是外部表，则 WHERE 子句中不支持 filepath() 和 filename() 函数。 你仍可以按 filename 或 filepath 进行筛选，前提是在计算列中使用它们。 下面的示例演示这一操作：

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,
 [Year]  AS CAST(filepath(1) AS INT), --use filepath() for partitioning
 [Month]  AS CAST(filepath(2) AS INT) --use filepath() for partitioning
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

SELECT *
      FROM tbl_TaxiRides
WHERE
      [year]=2017            
      AND [month] in (10,11,12);

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

统计信息

收集外部数据的统计信息对于查询优化而言是最重要的操作之一。 实例对数据了解得越多，执行查询的速度就越快。 SQL 引擎查询优化器是基于成本的优化器。 此优化器会对各种查询计划的成本进行比较，并选择成本最低的计划。 在大多数情况下，所选计划也是执行速度最快的计划。

自动创建统计信息

Azure SQL 托管实例会分析传入的用户查询，确定是否缺少统计信息。 如果缺少统计信息，查询优化器会在查询谓词或联接条件中各个列上自动创建统计信息，以改进查询计划的基数估计。 自动创建统计信息的过程是以同步方式完成的，因此，如果列中缺少统计信息，查询性能可能会轻微下降。 为单个列创建统计信息所耗用的时间取决于目标文件的大小。

OPENROWSET 手动统计信息

使用 sys.sp_create_openrowset_statistics 存储过程可以创建 OPENROWSET 路径的单列统计信息，方法是将单列作为参数传递 select 查询：

EXEC sys.sp_create_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
';

默认情况下，实例使用数据集中提供的 100% 的数据创建统计信息。 你可以选择使用 TABLESAMPLE 选项指定样本大小作为百分比。 若要为多个列创建单列统计信息，请为每个列执行 sys.sp_create_openrowset_statistics。 不能为 OPENROWSET 路径创建多列统计信息。

若要更新现有统计信息，请先使用 sys.sp_drop_openrowset_statistics 存储过程将其删除，然后使用 sys.sp_create_openrowset_statistics 重新创建：

EXEC sys.sp_drop_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
';

外部表手动统计信息

用于在外部表上创建统计信息的语法与用于普通用户表的语法相似。 若要基于某个列创建统计信息，需要提供统计信息对象的名称和列的名称：

CREATE STATISTICS sVendor
ON tbl_TaxiRides (vendorID)
WITH FULLSCAN, NORECOMPUTE;

WITH选项是必需的，对于示例大小，允许的选项为百分之 SAMPLE n 和 FULLSCAN。

  • 若要为多个列创建单列统计信息，请为每个列执行 CREATE STATISTICS。
  • 不支持多列统计信息。

疑难解答

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

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

CREATE EXTERNAL TABLE AS SELECT (CETAS)

使用 CREATE EXTERNAL TABLE AS SELECT (CETAS) 可以将数据从 SQL 托管实例导出到外部存储帐户。 你可以使用 CETAS 在 Azure Blob 存储或 Azure Data Lake Storage (ADLS) Gen2 中基于 Parquet 或 CSV 文件创建外部表。 CETAS 还可以将 T-SQL SELECT 语句的结果并行导出到创建的外部表中。 这些功能可能存在数据外泄风险，因此，Azure SQL 托管实例默认禁用 CETAS。 若要启用，请参阅 CREATE EXTERNAL TABLE AS SELECT (CETAS)。

限制

已知问题

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

相关内容