SQL Server 2022 中的 PolyBase 入门

项目
02/12/2024

适用于：SQL Server 2016 (13.x) - Windows 及更高版本SQL Server 2017 (14.x) - Linux 及更高版本

本文通过有关在 SQL Server 2022 (16.x) 中使用 PolyBase 处理多个文件夹和文件的教程来为提供相关指导。这组教程查询演示了 PolyBase 的各种功能。

借助 SQL Server 中的 PolyBase 数据虚拟化，可以利用元数据文件函数查询多个文件夹、文件或执行文件夹消除。将架构发现与文件夹和文件消除功能结合使用具有十分强大的功能，将使 SQL 可以从任何 Azure 存储帐户或 S3 兼容对象存储解决方案中提取所需的数据。

先决条件

必须首先满足下列条件，然后才能使用本教程中的 PolyBase：

在 Windows 上安装 PolyBase 或在 Linux 上安装 PolyBase。
在 sp_configure中启用 PolyBase（如有必要）。
允许外部网络访问权限以访问位于 pandemicdatalake.blob.core.windows.net 和 azureopendatastorage.blob.core.windows.net 的公开可用的 Azure Blob 存储。

示例数据集

如果你不熟悉数据虚拟化，并且想要快速测试功能，请先查询 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 查询存储在公开可用的存储帐户中的文件：

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 和其他子句来继续探索数据集。

如果第一个查询在 SQL Server 实例上失败，则可能会阻止对公共 Azure 存储帐户的网络访问。请联系网络专家以启用访问权限，然后才能进行查询。

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

外部数据源

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

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

注意

如果收到错误消息 46530，External data sources are not supported with type GENERIC, 将会检查 SQL Server 实例中的配置选项 PolyBase Enabled。它应为 1。

运行以下命令以在 SQL Server 实例中启用 PolyBase：

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

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

--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命令还允许使用大容量路径中的通配符查询多个文件或文件夹。

下面的示例使用 NYC 的黄色出租车行程记录开放式数据集：

首先，创建外部数据源：

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

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

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

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

架构推理

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

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

使用 sys.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 的路径，否则返回完整文件路径。
带参数调用 filepath() 函数时，该函数将返回与该参数中指定的位置上的通配符相匹配的路径部分。例如，第一个参数值将返回与第一个通配符匹配的路径部分。

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 查询，以便可以轻松地重复使用基础查询。视图还启用报表和分析工具（如 Power BI）来使用 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;

外部表

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

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

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

SELECT TOP 10 * 
FROM tbl_TaxiRides;

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

外部数据源

有关为各种数据源创建外部数据源和外部表的更多教程，请参阅 PolyBase Transact-SQL 参考。

有关各种外部数据源的更多教程，请参阅：

SQL Server 2022 中的 PolyBase 入门

先决条件

示例数据集

外部数据源

使用 OPENROWSET 查询数据源

查询多个文件和文件夹

架构推理

文件元数据函数

基于 OPENROWSET 创建视图

外部表

外部数据源

反馈

反馈

其他资源

SQL Server 2022 中的 PolyBase 入门

先决条件

示例数据集

外部数据源

使用 OPENROWSET 查询数据源

查询多个文件和文件夹

架构推理

文件元数据函数

基于 OPENROWSET 创建视图

外部表

外部数据源

相关内容

反馈

反馈

其他资源