在 Azure Synapse Analytics 中使用无服务器 SQL 池查询存储文件

使用无服务器 SQL 池可以查询数据湖中的数据。 SQL 按需版本提供一个可以适应半结构化和非结构化数据查询的 T-SQL 查询外围应用。 对于查询,T-SQL 的以下方面受支持:

  • 完整的 SELECT 外围应用,包括大部分 SQL 函数和运算符
  • CREATE EXTERNAL TABLE AS SELECT (CETAS) 会创建一个外部表,然后将 Transact-SQL SELECT 语句的结果并行导出到 Azure 存储。

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

概述

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

查询 PARQUET 文件

若要查询 Parquet 源数据,请使用 FORMAT = 'PARQUET':

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.chinacloudapi.cn//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.chinacloudapi.cn/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.chinacloudapi.cn/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

根文件夹必须包含名为 _delta_log 的子文件夹。 有关用法示例,请查看查询 Delta Lake 格式一文。

文件架构

Synapse SQL 中的 SQL 语言允许你将文件的模式定义为 OPENROWSET 函数的一部分,并读取所有列或列的子集,或者尝试使用架构推理自动确定文件中的列类型。

读取选定的列子集

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

  • 如果存在 CSV 数据文件,若要读取所有列,请提供列名及其数据类型。 如果需要列的子集,请使用序号按顺序从来源数据文件中选取列。 列将按序号指定值绑定。
  • 如果存在 Parquet 数据文件,请提供与来源数据文件中的列名匹配的列名。 列将按名称绑定。
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.chinacloudapi.cn/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.chinacloudapi.cn/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 

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

查询多个文件或文件夹

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

下列规则适用:

  • 模式可以出现在目录路径的某个部分中,或者出现在文件名中。
  • 多种模式可以出现在同一个目录步骤或文件名中。
  • 如果有多个通配符,则所有匹配路径中的文件将包含在生成的文件集中。
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.chinacloudapi.cn/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 嵌套类型一文中查找有关访问重复列中的元素的查询示例。

查询示例

你可以通过示例查询了解有关查询各种类型数据的更多信息。

工具

发出查询所需的工具:- Azure Synapse Studio - Azure Data Studio - SQL Server Management Studio

演示设置

第一步是创建将在其中执行查询的数据库。 然后,可以通过对该数据库执行安装脚本来初始化这些对象。

此安装脚本将创建数据源、数据库范围的凭据和用于读取这些示例中数据的外部文件格式。

注意

数据库仅用于查看元数据,而不用于实际数据。 请记下使用的数据库名称,因为稍后需要用到。

CREATE DATABASE mydbname;

提供的演示数据

演示数据包含以下数据集:

  • 纽约市出租车 - 黄色出租车行程记录 - 采用 CSV 和 Parquet 格式的纽约市公共数据集的一部分
  • 采用 CSV 格式的人口数据集
  • 包含嵌套列的 Parquet 文件示例
  • JSON 格式的书籍
文件夹路径 说明
/csv/ 采用 CSV 格式的数据的父文件夹
/csv/population/
/csv/population-unix/
/csv/population-unix-hdr/
/csv/population-unix-hdr-escape
/csv/population-unix-hdr-quoted
采用不同 CSV 格式的人口数据文件的文件夹。
/csv/taxi/ 采用 CSV 格式的纽约市公共数据文件的文件夹
/parquet/ 采用 Parquet 格式的数据的父文件夹
/parquet/taxi 采用 Parquet 格式的纽约市公共数据文件,已使用 Hive/Hadoop 分区方案按年份和月份分区。
/parquet/nested/ 包含嵌套列的 Parquet 文件示例
/json/ 采用 JSON 格式的数据的父文件夹
/json/books/ 包含书籍数据的 JSON 文件

后续步骤

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