在无服务器 SQL 池查询中使用文件元数据

根据查询文件夹和多个文件一文中所述，无服务器 SQL 池可处理多个文件和文件夹。 本文介绍如何在查询中使用有关文件和文件夹名称的元数据信息。

有时，可能会需要知道哪个文件或文件夹源与结果集中的某个特定行相关。

可以使用函数 filepath 和 filename 在结果集中返回文件名称和/或路径。 或者，可以使用它们根据文件名和/或文件夹路径来筛选数据。 语法部分 filename 函数和 filepath 函数中对这些函数进行了介绍。 在以下部分中，你将找到有关示例的简短说明。

先决条件

第一步是创建数据库，其中包含了引用存储帐户的数据源。 然后通过对该数据库执行安装脚本来初始化这些对象。 此安装脚本将创建数据源、数据库范围的凭据以及在这些示例中使用的外部文件格式。

函数

文件名

此函数返回该行所源自的文件的名称。

下面的示例读取 2017 年九月纽约市黄色出租车的数据文件，并按文件返回搭乘数。 查询的 OPENROWSET 部分指定将读取哪些文件。

SELECT
    nyc.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM  
    OPENROWSET(
        BULK 'parquet/taxi/year=2017/month=9/*.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) nyc
GROUP BY nyc.filename();

下面的示例演示如何在 WHERE 子句中使用 filename()，以筛选要读取的文件。 它访问查询的 OPENROWSET 部分中的整个文件夹，并在 WHERE 子句中筛选文件。

得到的结果将与前面的示例相同。

SELECT
    r.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        FIRSTROW = 2) 
        WITH (C1 varchar(200) ) AS [r]
WHERE
    r.filename() IN ('yellow_tripdata_2017-10.csv', 'yellow_tripdata_2017-11.csv', 'yellow_tripdata_2017-12.csv')
GROUP BY
    r.filename()
ORDER BY
    [filename];

Filepath

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

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

下面的示例读取 2017 年最后三个月纽约市黄色出租车的数据文件。 它按文件路径返回搭乘数。 查询的 OPENROWSET 部分指定将读取哪些文件。

SELECT
    r.filepath() AS filepath
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-1*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id INT
    ) AS [r]
GROUP BY
    r.filepath()
ORDER BY
    filepath;

下面的示例演示如何在 WHERE 子句中使用 filepath()，以筛选要读取的文件。

可以在查询的 OPENROWSET 部分中使用通配符，并在 WHERE 子句中筛选文件。 得到的结果将与前面的示例相同。

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',        
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) 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;

后续步骤

下一篇文章介绍如何查询 Parquet 文件。