查詢資料夾和多個檔案

發行項
06/01/2023

在本文中，您將瞭解如何使用 Azure Synapse Analytics 中的無伺服器 SQL 集區來編寫查詢。

無伺服器 SQL 集區支援使用萬用字元 (類似於 Windows 作業系統中使用的萬用字元) 讀取多個檔案/資料夾。不過，因為允許多個萬用字元，所以會存在更大的彈性。

必要條件

您的第一個步驟是建立資料庫，您將在其中執行查詢。然後在該資料庫上執行安裝指令碼，將物件初始化。此安裝指令碼會建立資料來源、資料庫範圍認證，以及用於這些範例中的外部檔案格式。

您將使用資料夾 csv/taxi 來遵循範例查詢。它包含從 2016 年 7 月到 2018 年 6 月的紐約計程車 - 黃色計程車的行程記錄資料。 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 存取的所有檔案都必須具有相同的結構 (例如，資料行的數目和其資料類型)。

使用多個檔案路徑讀取資料夾中的檔案子集

下列範例使用 2 個檔案路徑從 csv/taxi 資料夾讀取 2017 NYC Yellow Taxi 資料檔案，其中第一個路徑為包含一月資料的檔案完整路徑，第二個路徑則具有萬用字元可讀取十一月與十二月資料，以傳回各付款類型的總費用金額。

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 檔案中涵蓋更複雜的萬用字元使用案例。

下一步

如需詳細資訊，請參閱查詢特定檔案一文。

查詢資料夾和多個檔案

必要條件

讀取資料夾中的所有檔案

讀取資料夾中的檔案子集

使用多個檔案路徑讀取資料夾中的檔案子集

讀取資料夾

讀取特定資料夾中的所有檔案

讀取多個資料夾中的所有檔案

以遞迴方式周遊遍歷資料夾

多個萬用字元

下一步

其他資源