通过

你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。

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

  • 项目

使用无服务器 SQL 池，可以查询数据湖中的数据。 它提供了可处理半结构化和非结构化数据查询的 Transact-SQL (T-SQL) 查询范围。 对于查询，T-SQL 的以下方面受支持：

有关当前支持或不支持的功能的详细信息，请参阅无服务器 SQL 池概述或以下文章：

概述

为了支持在就地查询 Azure 存储文件中的数据方面提供顺畅的体验，无服务器 SQL 池将使用具有以下更多功能的 OPENROWSET 函数：

查询 PARQUET 文件

要查询 Parquet 源数据，请使用 FORMAT = 'PARQUET'：

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

有关用法示例，请参阅查询 Parquet 文件。

查询 CSV 文件

要查询 CSV 源数据，请使用 FORMAT = 'CSV'。 查询 CSV 文件时，可以将 CSV 文件的架构指定为 OPENROWSET 函数的一部分：

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.csv', FORMAT = 'CSV', PARSER_VERSION='2.0') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

可以使用一些其他选项将分析规则调整为自定义 CSV 格式：

  • ESCAPE_CHAR = 'char' 指定文件中用于将自身及文件中所有分隔符值转义的字符。 如果转义字符后接除本身以外的某个值或者任何分隔符值，则读取值时会删除该转义字符。 无论是否启用 FIELDQUOTE，均应用 ESCAPE_CHAR 参数。 它不用于转义引号字符。 必须使用其他引号字符来转义引号字符。 要让引号字符出现在列值内，必须将值放在引号中。
  • FIELDTERMINATOR ='field_terminator' 指定要使用的字段终止符。 默认的字段终止符为逗号 (,)。
  • ROWTERMINATOR ='row_terminator' 指定要使用的行终止符。 默认的行终止符为换行符 (\r\n)。

查询 DELTA LAKE 格式

要查询 Delta Lake 源数据，请使用 FORMAT = 'DELTA' 并引用包含 Delta Lake 文件的根文件夹。

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

根文件夹必须包含名为 _delta_log 的子文件夹。 有关用法示例，请参阅查询 Delta Lake (v1) 文件。

文件架构

借助 Synapse SQL 中的 SQL 语言，可以将文件的架构定义为 OPENROWSET 函数的一部分，并读取所有列或列的子集，或尝试使用架构推理自动确定文件中的列类型。

读取选定的列子集

要指定要读取的列，可以在 OPENROWSET 语句中提供可选的 WITH 子句。

  • 如果存在 CSV 数据文件，则请提供列名及其数据类型来读取所有列。 如果需要列的子集，请使用序号按顺序从来源数据文件中选取列。 列将按序号指定值绑定。
  • 如果存在 Parquet 数据文件，请提供与来源数据文件中的列名匹配的列名。 列按名称绑定。
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (
      C1 int, 
      C2 varchar(20),
      C3 varchar(max)
) as rows;

对于每个列，你需要在 WITH 子句中指定列名称和类型。 有关示例，请参阅在不指定所有列的情况下读取 CSV 文件。

架构推理

通过从 OPENROWSET 语句中省略 WITH 子句，可以指示服务从基础文件中自动检测（推理）架构。

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET')

请确保使用适当的推断数据类型以获得最佳性能。

查询多个文件或文件夹

若要针对一个文件夹或一组文件夹中的一组文件运行 T-SQL 查询，并将这些文件视为单个实体或行集，请提供某个文件夹的路径，或者提供针对一组文件或文件夹的模式（使用通配符）。

下列规则适用：

  • 模式可以出现在目录路径的某个部分中，或者出现在文件名中。
  • 多种模式可以出现在同一个目录步骤或文件名中。
  • 如果存在多个通配符，则所有匹配路径中的文件将包含在生成的文件集中。
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

有关用法示例，请参阅查询文件夹和多个文件。

文件元数据函数

Filename 函数

此函数返回行的来源文件的名称。

若要查询特定的文件，请阅读查询特定文件一文中的“Filename”部分。

返回数据类型为 nvarchar(1024)。 为了获得最佳性能，请始终将 filename 函数的结果强制转换为适当的数据类型。 如果使用字符数据类型，请确保使用适当的长度。

Filepath 函数

此函数返回完整路径或一部分路径：

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

有关更多信息，请阅读查询特定文件一文的“Filepath”部分。

返回数据类型为 nvarchar(1024)。 为了获得最佳性能，请始终将 filepath 函数的结果强制转换为适当的数据类型。 如果使用字符数据类型，请确保使用适当的长度。

处理复杂类型以及嵌套或重复的数据结构

为了在处理以嵌套的或重复的数据类型存储的数据（例如在 Parquet 文件中）时实现顺畅的体验，无服务器 SQL 池添加了以下扩展。

投影嵌套数据或重复数据

要投影数据，请对包含嵌套数据类型列的 Parquet 文件运行 SELECT 语句。 在输出时，嵌套值将序列化为 JSON，并按 varchar (8000) SQL 数据类型返回。

    SELECT * FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

有关详细信息，请参阅查询 Parquet 嵌套类型一文中的“投影嵌套数据或重复数据”部分。

访问嵌套列中的元素

要访问嵌套列中的嵌套元素（例如 Struct），请使用点表示法将字段名称串联成路径。 以 OPENROWSET 函数 WITH 子句中的 column_name 的形式提供路径。

语法片段示例如下所示：

    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    WITH ('column_name' 'column_type')
    [AS alias]
    'column_name' ::= '[field_name.] field_name'

默认情况下，OPENROWSET 函数会将源字段名称和路径与 WITH 子句中提供的列名进行匹配。 可以通过 WITH 子句访问同一源 Parquet 文件中包含在不同嵌套级别的元素。

返回值

  • 对于不在“嵌套类型”组中的所有 Parquet 类型，函数将从指定元素以及指定路径上返回某个标量值，例如 int、decimal 和 varchar。
  • 如果该路径指向嵌套类型的元素，则函数将返回指定路径中从顶部元素开始的 JSON 片段。 JSON 片段的类型为 varchar (8000)。
  • 如果在指定的 column_name 中找不到该属性，则函数将返回错误。
  • 如果在指定的 column_path 中找不到该属性，则函数将根据路径模式在严格模式下返回错误，或在宽松模式下返回 null。

有关查询示例，请参阅查询 Parquet 嵌套类型一文中的“从嵌套对象列读取属性”部分。

访问重复列中的元素

要访问重复列中的元素（例如数组或映射的元素），请对需要投影的每个标量元素使用 JSON_VALUE 函数，并提供：

  • 嵌套列或重复列（作为第一个参数）
  • 用于指定要访问的元素或属性的 JSON 路径（作为第二个参数）

要访问重复列中的非标量元素，请对需要投影的每个非标量元素使用 JSON_QUERY 函数，并提供：

  • 嵌套列或重复列（作为第一个参数）
  • 用于指定要访问的元素或属性的 JSON 路径（作为第二个参数）

请参阅以下语法片段：

    SELECT
       JSON_VALUE (column_name, path_to_sub_element),
       JSON_QUERY (column_name [ , path_to_sub_element ])
    FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

可以在查询 Parquet 嵌套类型一文中查找有关访问重复列中的元素的查询示例。

相关内容

有关如何查询不同文件类型以及创建和使用视图的详细信息，请参阅以下文章：