Menggunakan metadata file dalam kueri kumpulan SQL tanpa server

Dalam artikel ini, Anda mempelajari cara mengkueri file atau folder tertentu dengan menggunakan metadata. Kumpulan SQL tanpa server dapat mengatasi beberapa file dan folder. Untuk informasi selengkapnya, lihat Folder kueri dan beberapa file.

Terkadang, Anda mungkin perlu mengetahui file atau sumber 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 kumpulan hasil, atau Anda dapat menggunakannya untuk memfilter data berdasarkan nama file atau jalur folder. Fungsi-fungsi ini dijelaskan dalam fungsi nama file dan fungsi filepath.

Bagian berikut memberikan deskripsi singkat dan sampel kode.

Prasyarat

Langkah pertama Anda adalah membuat database dengan sumber data yang mereferensikan akun penyimpanan. 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.

Fungsi

Filename

Fungsi mengembalikan filename nama file tempat baris berasal.

Sampel berikut membaca file data Taksi Kuning NYC untuk September 2017 dan mengembalikan jumlah tumpangan per file. Bagian OPENROWSET kueri menentukan file mana yang 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 cara filename() dapat digunakan dalam WHERE klausul untuk memfilter file yang akan dibaca. Ini mengakses seluruh folder di OPENROWSET bagian kueri dan memfilter file dalam WHERE klausa.

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 mengembalikan jalur penuh atau parsial:

• Ketika dipanggil tanpa parameter, ia mengembalikan jalur file lengkap tempat baris berasal. Ketika DATA_SOURCE digunakan dalam OPENROWSET, jalur akan mengembalikan jalur relatif terhadap DATA_SOURCE.
• Ketika dipicu dengan parameter, fungsi ini menampilkan bagian dari jalur yang cocok dengan wildcard pada posisi yang ditentukan dalam parameter. Misalnya, nilai parameter 1 mengembalikan bagian dari jalur yang cocok dengan wildcard pertama.

Sampel berikut membaca file data Taksi Kuning NYC selama tiga bulan terakhir tahun 2017. Pembacaan ini menampilkan jumlah perjalanan per jalur file. Bagian OPENROWSET kueri menentukan file mana yang 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 cara filepath() dapat digunakan dalam WHERE klausul untuk memfilter file yang akan dibaca.

Anda bisa menggunakan kartubebas di OPENROWSET bagian kueri dan memfilter file dalam WHERE klausa. 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 selanjutnya