Menggunakan metadata file dalam kueri kumpulan SQL tanpa server

Artikel
06/01/2023

Kumpulan SQL tanpa server dapat menangani beberapa file dan folder seperti yang dijelaskan di artikel Kueri folder dan beberapa file. Dalam artikel ini, Anda mempelajari cara menggunakan informasi metadata tentang nama file dan folder dalam kueri.

Terkadang, Anda mungkin perlu mengetahui sumber file atau folder mana yang berkorelasi dengan baris tertentu dalam tataan hasil.

Anda dapat menggunakan fungsi filepath dan filename untuk mengembalikan nama file dan/atau jalur dalam tataan hasil. Atau, Anda dapat menggunakannya untuk memfilter data berdasarkan nama file dan/atau jalur folder. Fungsi-fungsi ini dijelaskan dalam bagian sintaks fungsi nama file dan fungsi jalur file. Di bagian berikut, Anda akan menemukan deskripsi singkat bersama sampel.

Prasyarat

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

Fungsi

Filename

Fungsi ini menampilkan nama file tempat baris tersebut berasal.

Contoh berikut membaca file data Taksi Kuning NYC selama tiga bulan terakhir di tahun 2017 dan menampilkan jumlah perjalanan per file. Bagian OPENROWSET dari kueri menentukan file yang akan dibaca.

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

Contoh berikut menunjukkan bagaimana filename() dapat digunakan dalam klausa WHERE untuk memfilter file yang akan dibaca. Contoh ini mengakses seluruh folder di bagian OPENROWSET dari kueri dan memfilter file dalam klausa WHERE.

Hasil Anda akan sama dengan contoh sebelumnya.

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

Fungsi filepath menampilkan jalur penuh atau parsial:

Ketika dipicu tanpa parameter, fungsi ini menampilkan jalur file lengkap yang merupakan asal dari baris tersebut. Apabila DATA_SOURCE digunakan dalam OPENROWSET, jalur yang relatif terhadap DATA_SOURCE akan ditampilkan.
Ketika dipicu dengan parameter, fungsi ini menampilkan bagian dari jalur yang cocok dengan wildcard pada posisi yang ditentukan dalam parameter. Misalnya, parameter value 1 akan menampilkan bagian dari jalur yang cocok dengan wildcard pertama.

Contoh berikut membaca file data Taksi Kuning NYC selama tiga bulan terakhir di tahun 2017. Pembacaan ini menampilkan jumlah perjalanan per jalur file. Bagian OPENROWSET dari kueri menentukan file yang akan dibaca.

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;

Contoh berikut menunjukkan bagaimana filepath() dapat digunakan dalam klausa WHERE untuk memfilter file yang akan dibaca.

Anda dapat menggunakan wildcard di bagian OPENROWSET dari kueri dan memfilter file di klausa WHERE. Hasil Anda akan sama dengan contoh sebelumnya.

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;

Langkah berikutnya

Di artikel berikutnya, Anda akan mempelajari cara mengkueri file Parquet.

Share via