Bagikan melalui

Mengkueri file penyimpanan menggunakan kumpulan SQL tanpa server

Kumpulan SQL tanpa server memungkinkan Anda untuk mengkueri data di data lake Anda. Ini menawarkan cakupan kueri Transact-SQL (T-SQL) yang mengakomodasi kueri data semi terstruktur dan tidak terstruktur. Untuk kueri, aspek T-SQL berikut didukung:

Untuk informasi selengkapnya tentang apa yang saat ini didukung atau tidak didukung, baca gambaran umum kumpulan SQL tanpa server, atau artikel berikut:

  • Kembangkan akses penyimpanan di mana Anda dapat menggunakan tabel Eksternal dan fungsi OPENROWSET untuk membaca data dari penyimpanan.
  • Mengontrol akses penyimpanan tempat Anda dapat mempelajari cara mengaktifkan Synapse SQL untuk mengakses penyimpanan menggunakan autentikasi SAS atau Identitas Terkelola ruang kerja.

Gambaran Umum

Untuk mendukung pengalaman yang lancar dalam kueri data secara langsung yang terletak di file Azure Storage, kumpulan SQL tanpa server menggunakan fungsi OPENROWSET dengan lebih banyak kemampuan:

Kueri file PARQUET

Untuk mengkueri data sumber Parquet, gunakan FORMAT = 'PARQUET':

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Untuk contoh penggunaan, lihat Kueri File Parquet.

Kueri file CSV

Untuk mengkueri data sumber CSV, gunakan FORMAT = 'CSV'. Anda dapat menentukan skema file CSV sebagai bagian OPENROWSET dari fungsi saat Anda mengkueri file CSV:

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.csv', FORMAT = 'CSV', PARSER_VERSION='2.0') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Ada beberapa opsi tambahan yang dapat digunakan untuk menyesuaikan aturan penguraian ke format CSV kustom:

  • ESCAPE_CHAR = 'char' Menentukan karakter dalam file yang digunakan untuk menghindari karakter itu sendiri dan semua nilai pembatas dalam file. Jika karakter escape diikuti oleh nilai selain nilainya sendiri, atau salah satu nilai pembatas, karakter escape akan dihilangkan saat membaca nilai. Parameter ESCAPE_CHAR diterapkan apakah FIELDQUOTE diaktifkan atau tidak. Ini tidak digunakan untuk menghindari karakter kutipan. Karakter kutipan harus di-escape dengan karakter kutipan lain. Karakter kutipan hanya dapat muncul dalam nilai kolom jika nilainya dienkapsulasi dengan karakter kutipan.
  • FIELDTERMINATOR ='field_terminator' Menentukan pemisah field yang akan digunakan. Terminator bidang default adalah koma (,).
  • ROWTERMINATOR ='row_terminator' Menentukan terminator baris yang akan digunakan. Terminator baris default adalah karakter baris baru (\r\n).

Kueri format DELTA LAKE

Untuk mengkueri data sumber Delta Lake, gunakan FORMAT = 'DELTA' dan referensikan folder akar yang berisi file Delta Lake Anda.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Folder akar harus berisi subfolder yang disebut _delta_log. Untuk melihat contoh penggunaan, lihat Query File Delta Lake (v1).

Skema file

Bahasa SQL dalam Synapse SQL memungkinkan Anda menentukan skema file sebagai bagian OPENROWSET dari fungsi dan membaca semua atau subset kolom, atau mencoba menentukan jenis kolom secara otomatis dari file menggunakan inferensi skema.

Membaca subset kolom yang dipilih

Untuk menentukan kolom yang ingin Anda baca, Anda bisa memberikan klausa opsional WITH dalam pernyataan Anda OPENROWSET .

  • Jika ada file data CSV, berikan nama kolom dan jenis datanya untuk membaca semua kolom. Jika Anda menginginkan subset kolom, gunakan nomor urut untuk memilih kolom dari file data asal berdasarkan urutan. Kolom terikat oleh penetapan ordinal.
  • Jika ada file data Parquet, masukkan nama kolom yang sesuai dengan nama kolom di file data asal. Kolom diikat berdasarkan nama.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (
      C1 int, 
      C2 varchar(20),
      C3 varchar(max)
) as rows;

Untuk setiap kolom, Anda perlu menentukan nama kolom dan mengetikkan klausul WITH. Untuk sampel, lihat Membaca file CSV tanpa menentukan semua kolom.

Inferensi skema

Dengan menghilangkan WITH klausul dari OPENROWSET pernyataan, Anda dapat menginstruksikan layanan untuk mendeteksi (menyimpulkan) skema secara otomatis dari file yang mendasar.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET')

Pastikan jenis data yang disimpulkan yang sesuai digunakan demi performa optimal.

Kueri beberapa file atau folder

Untuk menjalankan kueri T-SQL terhadap suatu set file dalam folder atau suatu set folder selagi memperlakukannya sebagai entitas tunggal atau rowset, sediakan jalur ke folder atau pola (menggunakan kartubebas) terhadap suatu set file atau folder.

Aturan berikut ini akan berlaku:

  • Pola dapat muncul baik di sebagian jalur direktori atau dalam nama file.
  • Beberapa pola dapat muncul di langkah direktori atau nama file yang sama.
  • Jika ada beberapa wildcard, maka file dalam semua jalur yang cocok disertakan dalam kumpulan file hasil.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

Untuk contoh penggunaan, lihat Folder kueri dan beberapa berkas.

Fungsi metadata file

Fungsi nama berkas

Fungsi ini menampilkan nama file asal baris tersebut.

Untuk mengkueri file tertentu, baca bagian Filename di artikel Kueri file tertentu.

Tipe data yang dikembalikan adalah nvarchar(1024). Demi performa optimal, selalu transmisikan hasil fungsi filename ke jenis data yang sesuai. Jika Anda menggunakan jenis data karakter, pastikan menggunakan panjang yang sesuai.

Fungsi filepath

Fungsi ini menampilkan jalur lengkap atau bagian dari jalur:

  • Ketika dipanggil tanpa parameter, menampilkan jalur file lengkap asal baris tersebut.
  • Ketika dipanggil dengan parameter, ini mengembalikan bagian dari jalur yang cocok dengan wildcard pada posisi yang ditentukan dalam parameter. Misalnya, nilai parameter 1 akan mengembalikan bagian dari jalur yang sesuai dengan wildcard pertama.

Untuk informasi tambahan, baca bagian Filepath di artikel Kueri file tertentu.

Jenis data yang dikembalikan adalah nvarchar(1024). Untuk performa optimal, selalu transmisikan hasil fungsi filepath ke jenis data yang sesuai. Jika Anda menggunakan jenis data karakter, pastikan menggunakan panjang yang sesuai.

Bekerja dengan jenis kompleks dan struktur data bersarang atau berulang

Untuk mengaktifkan pengalaman yang lancar dengan data yang disimpan dalam jenis data berlapis atau berulang, seperti dalam file Parquet , kumpulan SQL tanpa server telah menambahkan ekstensi berikut.

Memproyeksikan data berlapis atau berulang

Untuk memproyeksikan data, jalankan SELECT pernyataan melalui file Parquet yang berisi kolom jenis data berlapis. Pada output, nilai berlapis diserialisasikan ke JSON dan dikembalikan sebagai jenis data SQL varchar(8000).

    SELECT * FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

Untuk informasi selengkapnya, lihat bagian Memproyeksikan data berlapis atau berulang dari artikel Jenis Bersarang Query Parquet.

Mengakses elemen dari kolom bersarang

Untuk mengakses elemen berlapis dari kolom berlapis, seperti Struct, gunakan notasi titik untuk menggabungkan nama bidang menjadi jalur. Berikan jalur sebagai column_name dalam klausul WITH dari fungsi OPENROWSET.

Contoh fragmen sintaksnya adalah sebagai berikut:

    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    WITH ('column_name' 'column_type')
    [AS alias]
    'column_name' ::= '[field_name.] field_name'

Secara bawaan, fungsi OPENROWSET mencocokkan nama dan jalur bidang sumber dengan nama kolom yang disediakan dalam klausa WITH. Elemen yang terkandung pada tingkat berlapis berbeda dalam file Parquet sumber yang sama dapat diakses menggunakan klausa WITH.

Nilai Pengembalian

  • Fungsi mengembalikan nilai skalar, seperti int, decimal, dan varchar, dari elemen yang ditentukan, dan pada jalur yang ditentukan, untuk semua jenis Parquet yang tidak berada dalam grup Tipe Tersarang.
  • Jika jalur menunjuk ke elemen yang merupakan Tipe Bersarang, fungsi mengembalikan fragmen JSON yang dimulai dari elemen teratas pada jalur yang ditentukan. Fragmen JSON berjenis varchar(8000).
  • Jika properti tidak dapat ditemukan pada column_name, fungsi mengembalikan kesalahan.
  • Jika properti tidak dapat ditemukan pada column_path, fungsi akan mengembalikan kesalahan pada mode ketat atau null pada mode tidak ketat, tergantung pada mode Jalur.

Untuk sampel kueri, lihat bagian Baca properti dari kolom objek berlapis di artikel Jenis Sarang Parquet.

Mengakses elemen dari kolom berulang

Untuk mengakses elemen dari kolom berulang, seperti elemen array atau peta, gunakan fungsi JSON_VALUE untuk setiap elemen skalar yang perlu Anda proyeksikan dan sediakan:

  • Kolom bersarang atau berulang, sebagai parameter pertama
  • Jalur JSON yang menentukan elemen atau properti yang akan diakses, sebagai parameter kedua

Untuk mengakses elemen nonscalar dari kolom berulang, gunakan fungsi JSON_QUERY untuk setiap elemen nonscalar yang perlu Anda proyeksikan dan sediakan:

  • Kolom bersarang atau berulang, sebagai parameter pertama
  • Jalur JSON yang menentukan elemen atau properti yang akan diakses, sebagai parameter kedua

Lihat fragmen sintaks berikut:

    SELECT
       JSON_VALUE (column_name, path_to_sub_element),
       JSON_QUERY (column_name [ , path_to_sub_element ])
    FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

Anda dapat menemukan contoh kueri untuk mengakses elemen dari kolom berulang dalam artikel Kueri jenis Parquet bersarang.

Konten terkait

Untuk informasi selengkapnya tentang cara mengkueri berbagai jenis file, dan untuk membuat serta menggunakan tampilan, lihat artikel berikut ini:

  • Last updated on