Bagikan melalui

Menelusuri folder dan beberapa file

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

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

Prasyarat

Langkah pertama Anda adalah membuat database tempat Anda dapat menjalankan kueri. Kemudian, inisialisasi objek dengan menjalankan skrip penyiapan pada database tersebut. Skrip penyiapan ini membuat sumber data, kredensial cakupan database, dan format file eksternal yang digunakan dalam sampel ini.

Gunakan folder csv/taxi untuk mengikuti kueri sampel. Ini berisi data Catatan Perjalanan Taksi NYC - Taksi Kuning dari Juli 2016 hingga Juni 2018. File di dalam csv/taxi dinamai menurut tahun dan bulan dengan menggunakan pola berikut:

yellow_tripdata_<year>-<month>.csv*

Membaca semua file dalam folder

Contoh berikut membaca semua file data Taksi Kuning NYC dari folder csv/taksi , lalu 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 tunggal OPENROWSET harus memiliki struktur yang sama (jumlah kolom dan jenis datanya).

Membaca subkumpulan file dalam folder

Contoh berikut membaca file data Taksi Kuning NYC 2017 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 tunggal OPENROWSET harus memiliki struktur yang sama (jumlah kolom dan jenis datanya).

Baca subset file dalam folder menggunakan beberapa jalur file

Contoh berikut membaca file data Taksi Kuning NYC 2017 dari folder csv/taxi menggunakan dua jalur file. Yang pertama menggunakan jalur lengkap ke file yang berisi data dari bulan Januari, dan yang kedua menggunakan wildcard untuk membaca bulan Oktober, November, dan Desember. Untuk setiap jalur, jumlah total tarif per jenis pembayaran dikembalikan.

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 tunggal OPENROWSET harus memiliki struktur yang sama (jumlah kolom dan jenis datanya).

Baca folder

Jalur yang Anda berikan ke OPENROWSET juga dapat menjadi jalur menuju folder. Bagian berikut ini menyertakan jenis kueri ini.

Membaca semua file dari folder tertentu

Anda dapat membaca semua file dalam folder menggunakan wildcard untuk file seperti yang ditunjukkan dalam Membaca semua file dalam folder. Tapi, ada cara untuk mengakses folder dan mengolah semua file dalam folder tersebut.

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

Catatan

Perhatikan keberadaan / di akhir jalur dalam kueri. Ini menunjukkan sebuah folder. Jika dihilangkan / , kueri menargetkan file bernama taksi sebagai gantinya.

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 tunggal OPENROWSET harus memiliki struktur yang sama (jumlah kolom dan jenis datanya).

Membaca semua file dari beberapa folder

Membaca file dari beberapa folder dapat dilakukan dengan menggunakan wildcard. Kueri berikut membaca semua file dari semua folder yang terletak di folder csv yang memiliki nama yang dimulai dengan t dan diakhir dengan i.

Catatan

Perhatikan keberadaan / di akhir jalur dalam kueri. Ini menandakan folder. Jika dihilangkan / , kueri 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 tunggal OPENROWSET harus memiliki struktur yang sama (jumlah kolom dan jenis 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 melintasi folder secara rekursif jika Anda menentukan /** di akhir jalur. Kueri berikut membaca semua file dari semua folder dan subfolder yang terletak di 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 tunggal OPENROWSET harus memiliki struktur yang sama (jumlah kolom dan jenis datanya).

Gunakan beberapa wildcard

Anda dapat menggunakan beberapa wildcards pada jalur yang berbeda tingkatnya. Misalnya, Anda dapat memperkaya kueri sebelumnya untuk membaca file dengan data 2017 saja, dari semua folder di mana nama dimulai dengan t dan diakhir dengan i.

Catatan

Perhatikan keberadaan / di akhir jalur dalam kueri. Ini menandakan sebuah folder. Jika dihilangkan / , kueri menargetkan file bernama t*i sebagai gantinya. Ada batas maksimum 10 wildcard untuk setiap 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 tunggal OPENROWSET harus memiliki struktur yang sama (jumlah kolom dan jenis 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. Untuk skenario penggunaan kartu bebas yang lebih kompleks, lihat Kueri Berkas Parquet.

Langkah selanjutnya

Menggunakan metadata file dalam kueri kumpulan SQL tanpa server

  • Last updated on