Membuat kueri folder dan beberapa file

Artikel
06/01/2023

Dalam artikel ini, Anda akan mempelajari cara membuat kueri menggunakan kumpulan SQL tanpa server di Azure Synapse Analytics.

Kumpulan SQL tanpa server mendukung pembacaan beberapa file/ folder dengan menggunakan wildcard, mirip dengan wildcard yang digunakan di Windows OS. Namun, dengan fleksibilitas yang lebih besar karena beberapa wildcard diizinkan.

Prasyarat

Langkah pertama Anda adalah membuat database tempat Anda akan menjalankan kueri. Kemudian menginisialisasi objek dengan mengeksekusi skrip pengaturan pada database itu. Skrip pengaturan ini akan membuat sumber data, info masuk tercakup database, dan format file eksternal yang digunakan dalam sampel ini.

Anda akan menggunakan folder csv/taxi untuk mengikuti contoh kueri. Kueri ini berisi data NYC Taxi - Rekaman Perjalanan Yellow Taxi dari Juli 2016 hingga Juni 2018. File dalam csv/taxi diberi nama menurut tahun dan bulan menggunakan pola berikut: yellow_tripdata_<year>-<month>.csv

Membaca semua file dalam folder

Contoh di bawah ini membaca semua file data NYC Yellow Taxi dari folder csv/taxi dan mengembalikan jumlah total penumpang dan perjalanan per tahun. Ini juga menunjukkan penggunaan fungsi agregat.

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);

Catatan

Semua file yang diakses dengan OPENROWSET tunggal harus memiliki struktur yang sama (yaitu, jumlah kolom dan tipe datanya).

Membaca subset file dalam folder

Contoh di bawah ini membaca file data 2017 NYC Yellow Taxi dari folder csv/taxi menggunakan wildcard dan mengembalikan jumlah total tarif per jenis pembayaran.

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;

Catatan

Semua file yang diakses dengan OPENROWSET tunggal harus memiliki struktur yang sama (yaitu, jumlah kolom dan tipe datanya).

Baca subset file dalam folder menggunakan beberapa jalur file

Contoh di bawah ini membaca file data NYC Yellow Taxi 2017 dari folder csv/taksi menggunakan 2 jalur file pertama dengan jalur penuh ke file yang berisi data dari bulan Januari dan kedua dengan bulan pembacaan kartubebas November dan Desember yang mengembalikan jumlah tarif total per jenis pembayaran.

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;

Catatan

Semua file yang diakses dengan OPENROWSET tunggal harus memiliki struktur yang sama (yaitu, jumlah kolom dan tipe datanya).

Membaca folder

Jalur yang Anda sediakan ke OPENROWSET juga bisa menjadi jalur ke folder. Bagian berikut menyertakan tipe kueri ini.

Membaca semua file dari folder tertentu

Anda dapat membaca semua file dalam folder menggunakan wildcard tingkat file seperti yang diperlihatkan di Membaca semua file dalam folder. Tapi, ada cara untuk membuat kueri folder dan mengonsumsi semua file dalam folder tersebut.

Jika jalur yang diberikan di titik OPENROWSET menunjuk ke folder, semua file di folder tersebut akan digunakan sebagai sumber untuk kueri Anda. Kueri berikut akan membaca semua file dalam folder csv/taxi.

Catatan

Perhatikan keberadaan / di akhir jalur dalam kueri di bawah ini. Tanda tersebut menunjukkan folder. Jika / dihilangkan, kueri akan menargetkan file bernama 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);

Catatan

Semua file yang diakses dengan OPENROWSET tunggal harus memiliki struktur yang sama (yaitu, jumlah kolom dan tipe datanya).

Membaca semua file dari beberapa folder

Dimungkinkan untuk membaca file dari beberapa folder dengan menggunakan wildcard. Kueri berikut akan membaca semua file dari semua folder yang berada dalam folder csv yang memiliki nama yang dimulai dengan t dan diakhiri dengan i.

Catatan

Perhatikan keberadaan / di akhir jalur dalam kueri di bawah ini. Ini menunjukkan folder. Jika / dihilangkan, kueri akan menargetkan file bernama t*i sebagai gantinya.

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);

Catatan

Semua file yang diakses dengan OPENROWSET tunggal harus memiliki struktur yang sama (yaitu, jumlah kolom dan tipe datanya).

Karena Anda hanya memiliki satu folder yang cocok dengan kriteria, hasil kueri sama dengan Membaca semua file dalam folder.

Menelusuri folder secara rekursif

Kumpulan SQL tanpa server dapat secara rekursif menelusuri folder jika Anda menentukan /** di akhir jalur. Kueri berikut akan membaca semua file dari semua folder dan subfolder yang berada dalam folder 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);

Catatan

Semua file yang diakses dengan OPENROWSET tunggal harus memiliki struktur yang sama (yaitu, jumlah kolom dan tipe datanya).

Beberapa wildcard

Anda dapat menggunakan beberapa wildcard di tingkat jalur yang berbeda. Misalnya, Anda dapat memperkaya kueri sebelumnya untuk membaca file dengan data 2017 saja, dari semua folder yang namanya dimulai dengan t dan diakhiri dengan i.

Catatan

Perhatikan keberadaan / di akhir jalur dalam kueri di bawah ini. Ini menunjukkan folder. Jika / dihilangkan, kueri akan menargetkan file bernama t*i sebagai gantinya. Ada batas maksimum 10 kartubebas per kueri.

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);

Catatan

Semua file yang diakses dengan OPENROWSET tunggal harus memiliki struktur yang sama (yaitu, jumlah kolom dan tipe datanya).

Karena Anda hanya memiliki satu folder yang cocok dengan kriteria, hasil kueri sama dengan Membaca subset file dalam folder dan Membaca semua file dari folder tertentu. Skenario penggunaan wildcard yang lebih kompleks dibahas dalam Membuat kueri file Parquet.

Langkah berikutnya

Informasi selengkapnya bisa dibaca di artikel Membuat kueri file tertentu.

Membuat kueri folder dan beberapa file

Prasyarat

Membaca semua file dalam folder

Membaca subset file dalam folder

Baca subset file dalam folder menggunakan beberapa jalur file

Membaca folder

Membaca semua file dari folder tertentu

Membaca semua file dari beberapa folder

Menelusuri folder secara rekursif

Beberapa wildcard

Langkah berikutnya

Sumber Daya Tambahan: