使用无服务器 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 参数指定要查询的数据类型。上面的示例从文件文件夹中的所有 .csv 文件中读取带分隔符的文本。

注释

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

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

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

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

查询带分隔符的文本文件

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

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

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

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 编码数据和 master 数据库中用于无服务器 SQL 池的排序规则不兼容。若要解决此问题，可以为架构中的单个 VARCHAR 列指定兼容的排序规则。有关详细信息，请参阅故障排除指南。

查询 JSON 文件

JSON 是 Web 应用程序的热门格式，可通过 REST 接换数据或使用 Azure Cosmos DB 等 NoSQL 数据存储。因此，将数据作为 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 文件没有特定格式，因此必须使用 csv 格式和 FIELDTERMINATOR、FIELDQUOTE，ROWTERMINATOR 设置为 0x0b，以及包含单个 NVARCHAR（MAX）列的架构。此查询的结果是包含单个 JSON 文档列的行集，如下所示：

医生
{“product_id”：123，“product_name”：“Widget”，“list_price”： 12.99}
{“product_id”：124，“product_name”：“小工具”，“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

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

产品	价格
控件	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

查询分区数据

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

/订单
- /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');

WHERE 子句中编号的文件路径参数引用 BULK 路径中文件夹名称中的通配符，-so 参数 1 是 year=* 文件夹名称中的 *，参数 2 是 month=* 文件夹名称中的 * 。