使用无服务器 SQL 池查询文件

10 分钟

可使用无服务器 SQL 池查询采用各种通用文件格式的数据文件，包括：

带分隔符的文本，例如逗号分隔值 (CSV) 文件。
JavaScript 对象表示法 (JSON) 文件。
Parquet 文件。

对于所有这些类型的文件，查询的基本语法都是相同的，并且建立在 OPENROWSET SQL 函数的基础之上；它从一个或多个文件中的数据生成表格行集。例如，以下查询可用于从 CSV 文件中提取数据。

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

OPENROWSET 函数包含更多参数，这些参数决定了如下因素：

生成的行集的架构
分隔文本文件的其他格式设置选项。

提示

可以在 Azure Synapse Analytics 文档中找到 OPENROWSET 函数的完整语法。

OPENROWSET 的输出是必须向其分配别名的行集。在前面的示例中，别名行用于命名生成的行集。

BULK 参数包括指向数据湖中包含数据文件的位置的完整 URL。这可以是单个文件，也可以是带有通配符表达式以筛选应包含的文件类型的文件夹。 FORMAT 参数指定要查询的数据的类型。上面的示例从 files 文件夹中的所有 .csv 文件中读取带分隔符的文本。

注意

此示例假定用户有权访问基础存储中的文件，如果文件受 SAS 密钥或自定义标识保护，你需要创建服务器范围的凭据。

如上一示例中所示，可以使用 BULK 参数中的通配符在查询中包含或排除文件。以下列表显示了如何使用它的几个示例：

https://mydatalake.blob.core.windows.net/data/files/file1.csv：仅在 files 文件夹中包含 file1.csv。
https://mydatalake.blob.core.windows.net/data/files/file*.csv：files 文件夹中名称以“file”开头的所有 .csv 文件。
https://mydatalake.blob.core.windows.net/data/files/*：files 文件夹中的所有文件。
https://mydatalake.blob.core.windows.net/data/files/**：files 文件夹及其子文件夹中的所有文件。

你也可以在 BULK 参数中指定多个文件路径，用逗号分隔每个路径。

查询带分隔符的文本文件

带分隔符的文本文件是许多企业中的常见文件格式。带分隔符的文件中使用的特定格式可能会有所不同，例如：

有和没有标题行。
逗号分隔值和制表符分隔值。
Windows 和 Unix 样式行尾。
不带引号和带引号的值，以及转义字符。

无论使用哪种类型的带分隔符的文件，你都可以使用带有 csv FORMAT 参数的 OPENROWSET 函数以及处理数据的特定格式详细信息所需的其他参数来从中读取数据。例如：

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

PARSER_VERSION 用于确定查询如何解释文件中使用的文本编码。 1.0 版是默认版本，支持多种文件编码，而 2.0 版支持的编码较少，但性能更好。 FIRSTROW 参数用于跳过文本文件中的行、消除任何非结构化的报头文本或忽略包含列标题的行。

使用带分隔符的文本文件时，可能需要的其他参数包括：

FIELDTERMINATOR - 用于分隔每行中的字段值的字符。例如，制表符分隔文件使用 TAB (\t) 字符分隔字段。默认的字段终止符为逗号 (,)。
ROWTERMINATOR - 用于表示数据行末尾的字符。例如，标准 Windows 文本文件使用回车 (CR) 和换行 (LF) 的组合，由代码 \n 表示；而 UNIX 样式的文本文件使用单个换行符，可使用代码 0x0a 来表示。
FIELDQUOTE - 用于括住带引号的字符串值的字符。例如，为确保地址字段值“126 Main St, apt 2”中的逗号不会被解释为字段分隔符，可将整个字段值用引号括起来，如下所示：“126 Main St, apt 2”。双引号 (") 是默认的字段引号字符。

提示

有关使用带分隔符的文本文件时的其他参数的详细信息，请参阅 Azure Synapse Analytics 文档。

指定行集架构

带分隔符的文本文件通常在第一行中包含列名。 OPENROWSET 函数可使用它定义结果行集的架构，并根据它们包含的值自动推断列的数据类型。例如，考虑以下带分隔符的文本：

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

数据包含以下三列：

product_id（整数）
product_name（字符串）
list_price（十进制数）

可使用以下查询来提取具有正确列名和适当推断的 SQL Server 数据类型（在本例中为 INT、NVARCHAR 和 DECIMAL）的数据

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

HEADER_ROW 参数（仅在使用解析器 2.0 版时可用）指示查询引擎使用每个文件中的第一行数据作为列名，如下所示：

product_id	product_name	list_price
123	小组件	12.9900
124	小工具	3.9900

现在，考虑以下数据：

123,Widget,12.99
124,Gadget,3.99

这一次，文件不包含标题行中的列名；因此，虽然仍然可以推断数据类型，但列名将设置为 C1、C2、C3 等。

C1	C2	C3
123	小组件	12.9900
124	小工具	3.9900

若要指定显式列名和数据类型，可通过在 WITH 子句中提供架构定义来覆盖默认列名和推断的数据类型，如下所示：

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

此查询生成预期结果：

product_id	product_name	list_price
123	小组件	12.99
124	小工具	3.99

提示

处理文本文件时，你可能会遇到一些与 UTF-8 编码数据和无服务器 SQL 池的 master 数据库中使用的排序规则不兼容的问题。若要解决此问题，可为架构中的单个 VARCHAR 列指定兼容的排序规则。有关更多详细信息，请参阅故障排除指南。

查询 JSON 文件

JSON 是用于通过 REST 接口交换数据或使用 NoSQL 数据存储（如 Azure Cosmos DB）的 Web 应用程序的常用格式。因此，将数据作为 JSON 文档保存在数据湖中的文件中以供分析的情况并不少见。

例如，定义单个产品的 JSON 文件可能如下所示：

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

若要从包含多个采用此格式的 JSON 文件的文件夹中返回产品数据，可使用以下 SQL 查询：

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

OPENROWSET 没有 JSON 文件的特定格式，因此必须使用 FIELDTERMINATOR、FIELDQUOTE 和 ROWTERMINATOR 设置为 0x0b 的 csv 格式，以及包含单个 NVARCHAR(MAX) 列的架构。此查询的结果是一个包含单列 JSON 文档的行集，如下所示：

文档
{"product_id":123,"product_name":"Widget","list_price": 12.99}
{"product_id":124,"product_name":"Gadget","list_price": 3.99}

若要从 JSON 中提取单个值，可在 SELECT 语句中使用 JSON_VALUE 函数，如下所示：

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

此查询将返回类似于以下结果的行集：

product	price
小组件	12.99
小工具	3.99

查询 Parquet 文件

Parquet 是分布式文件存储上大数据处理的常用格式。它是一种高效的数据格式，针对压缩和分析查询进行了优化。

在大多数情况下，数据架构嵌入在 Parquet 文件中，因此你只需指定 BULK 参数和要读取的文件的路径，以及 parquet 的 FORMAT 参数；如下所示：

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

查询分区数据

在数据湖中，通过跨反映分区标准的子文件夹中的多个文件进行拆分来对数据进行分区是很常见的。这使分布式处理系统能够在数据的多个分区上并行工作，或者能够根据筛选标准轻松消除从特定文件夹读取数据的操作。例如，假设你需要高效处理销售订单数据，并且经常需要根据下订单的年份和月份进行筛选。可以使用文件夹对数据进行分区，如下所示：

/orders
- /year=2020
  - /month=1
    - /01012020.parquet
    - /02012020.parquet
    - ...
  - /month=2
    - /01022020.parquet
    - /02022020.parquet
    - ...
  - ...
- /year=2021
  - /month=1
    - /01012021.parquet
    - /02012021.parquet
    - ...
  - ...

若要创建筛选结果以仅包括 2020 年 1 月和 2 月订单的查询，可使用以下代码：

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');