查询文件夹和多个文件

项目
03/04/2024

在本文中，你将了解如何在 Azure Synapse Analytics 中使用无服务器 SQL 池编写查询。

无服务器 SQL 池支持使用通配符（类似于 Windows 操作系统中使用的通配符）读取多个文件/文件夹。但是，由于允许使用多个通配符，因此需要更大的灵活性。

先决条件

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

将使用文件夹 csv/taxi 执行示例查询。它包含 2016 年 7 月到 2018 年 6 月的 NYC Taxi - Yellow Taxi Trip Records 数据。 csv/taxi 中的文件按以下模式以年份和月份命名：yellow_tripdata_<year>-<month>.csv

读取文件夹中的所有文件

下面的示例从 csv/taxi 文件夹中读取所有 NYC Yellow Taxi 数据文件，并返回每年乘客总数和搭乘总次数。它还显示聚合函数的使用情况。

SELECT 
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/taxi/*.csv',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        pickup_datetime DATETIME2 2, 
        passenger_count INT 4
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

注意

使用单个 OPENROWSET 访问的所有文件必须具有相同的结构（例如列数及其数据类型）。

读取文件夹中的文件子集

下面的示例使用通配符读取 csv/taxi 文件夹中的 2017 NYC Yellow Taxi 数据文件，并返回每个付款类型的总费用金额。

SELECT 
    payment_type,  
    SUM(fare_amount) AS fare_total
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        payment_type INT 10,
        fare_amount FLOAT 11
    ) AS nyc
GROUP BY payment_type
ORDER BY payment_type;

注意

使用单个 OPENROWSET 访问的所有文件必须具有相同的结构（例如列数及其数据类型）。

使用多个文件路径读取文件夹中的文件子集

下面的示例使用两个文件路径从“csv/taxi”文件夹中读取 2017 年纽约市黄色出租车数据文件，第一个文件路径为包含一月数据的文件的完整路径，第二个文件路径包含通配符，读取十一月和十二月的数据并返回每种付款类型的总费用金额。

SELECT 
    payment_type,  
    SUM(fare_amount) AS fare_total
FROM OPENROWSET(
        BULK (
            'csv/taxi/yellow_tripdata_2017-01.csv',
            'csv/taxi/yellow_tripdata_2017-1*.csv'
        ),
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        payment_type INT 10,
        fare_amount FLOAT 11
    ) AS nyc
GROUP BY payment_type
ORDER BY payment_type;

注意

使用单个 OPENROWSET 访问的所有文件必须具有相同的结构（例如列数及其数据类型）。

读取文件夹

为 OPENROWSET 提供的路径也可以是指向文件夹的路径。以下部分包含这些查询类型。

读取特定文件夹中的所有文件

可以使用文件级别通配符读取文件夹中的所有文件，如读取文件夹中所有文件所示。但有一种方法可以查询文件夹并使用该文件夹中的所有文件。

如果 OPENROWSET 中提供的路径指向文件夹，则该文件夹中的所有文件都将用作查询的源。以下查询将读取 csv/taxi 文件夹中的所有文件。

注意

请注意以下查询中路径的末尾是否存在 /。它表示文件夹。如果省略了 /，则查询将改为以名为 taxi 的文件作为目标。

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/taxi/',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

注意

使用单个 OPENROWSET 访问的所有文件必须具有相同的结构（例如列数及其数据类型）。

读取多个文件夹中的所有文件

可以通过使用通配符从多个文件夹读取文件。下面的查询读取 csv 文件夹中所有以 t 开头并以 i 结尾的文件夹中的所有文件。

注意

请注意以下查询中路径的末尾是否存在 /。它表示文件夹。如果省略 /，则查询将改为以名为“t*i”的文件作为目标。

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/t*i/', 
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

注意

使用单个 OPENROWSET 访问的所有文件必须具有相同的结构（例如列数及其数据类型）。

由于只有一个文件夹与条件匹配，因此查询结果与读取文件夹中的所有文件的相同。

以递归方式遍历文件夹

如果在路径末尾指定 /**，则无服务器 SQL 池可以递归方式遍历文件夹。以下查询读取 csv/taxi 文件夹中所有文件夹和子文件夹中的所有文件。

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/taxi/**', 
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

注意

使用单个 OPENROWSET 访问的所有文件必须具有相同的结构（例如列数及其数据类型）。

多个通配符

可以在不同的路径级别使用多个通配符。例如，可以将之前的查询扩充为仅读取所有以 t 开头并以 i 结尾的文件夹中包含 2017 数据的文件。

注意

请注意以下查询中路径的末尾是否存在 /。它表示文件夹。如果省略 /，则查询将改为以名为“t*i”的文件作为目标。每个查询最多有 10 个通配符。

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/t*i/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

注意

使用单个 OPENROWSET 访问的所有文件必须具有相同的结构（例如列数及其数据类型）。

由于只有一个文件夹与条件匹配，因此查询结果与读取文件夹中文件的子集和读取特定文件夹中的所有文件的相同。查询 Parquet 文件中介绍了更复杂的通配符使用方案。

后续步骤

有关详细信息，可参阅查询特定文件一文。

查询文件夹和多个文件

先决条件

读取文件夹中的所有文件

读取文件夹中的文件子集

使用多个文件路径读取文件夹中的文件子集

读取文件夹

读取特定文件夹中的所有文件

读取多个文件夹中的所有文件

以递归方式遍历文件夹

多个通配符

后续步骤

其他资源