Bagikan melalui

Mengkueri file penyimpanan menggunakan kumpulan SQL tanpa server

  • Artikel

Kumpulan SQL tanpa server memungkinkan Anda untuk mengkueri data di data lake Anda. Ini menawarkan area permukaan 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 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 untuk kueri data di tempat 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 File Parquet Kueri.

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 meloloskan dirinya sendiri dan semua nilai pemisah 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 adalah atau tidak diaktifkan. Ini tidak digunakan untuk menghindari karakter kutipan. Karakter kutipan harus di-escape dengan karakter kutipan lain. Karakter kutipan dapat muncul dalam nilai kolom hanya jika nilai dienkapsulasi dengan karakter kutipan.
  • FIELDTERMINATOR ='field_terminator' Menentukan terminator bidang 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 contoh penggunaan, lihat File Query 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 penugasan ordinal.
  • Jika ada file data Parquet, masukkan nama kolom yang sesuai dengan nama kolom di file data asal. Kolom terikat menurut 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 kartubebas, maka file dalam semua jalur yang cocok disertakan dalam kumpulan file yang dihasilkan.
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 file.

Fungsi metadata file

Fungsi filename

Fungsi ini menampilkan nama file asal baris tersebut.

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

Jenis data yang ditampilkan 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, menampilkan bagian dari jalur yang cocok dengan kartubebas pada posisi yang ditentukan dalam parameter. Misalnya, nilai parameter 1 akan mengembalikan bagian dari jalur yang cocok dengan kartubebas 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 berlapis Query Parquet.

Mengakses elemen dari kolom bersarang

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

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 default, OPENROWSET fungsi cocok dengan nama dan jalur bidang sumber dengan nama kolom yang disediakan dalam WITH klausa. Elemen yang terkandung pada tingkat berlapis yang berbeda dalam file Parquet sumber yang sama dapat diakses menggunakan WITH klausul .

Mengembalikan nilai

  • Fungsi mengembalikan nilai skalar, seperti int, , decimaldan varchar, dari elemen yang ditentukan, dan pada jalur yang ditentukan, untuk semua jenis Parquet yang tidak berada dalam grup Tipe Berlapis.
  • Jika jalur menunjuk ke elemen yang merupakan Jenis Berlapis, fungsi mengembalikan fragmen JSON yang dimulai dari elemen atas 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, tergantung pada mode Jalur, fungsi mengembalikan kesalahan saat dalam mode ketat atau null saat dalam mode lax.

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

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 sampel kueri untuk mengakses elemen dari kolom berulang di 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: