Bagikan melalui

File penyimpanan kueri dengan kumpulan SQL tanpa server di Azure Synapse Analytics

  • Artikel

Kumpulan SQL tanpa server memungkinkan Anda untuk mengkueri data dalam data lake Anda. Kumpulan ini menawarkan area permukaan kueri T-SQL yang mengakomodasi kueri data semi terstruktur dan tidak terstruktur. Untuk kueri, aspek T-SQL berikut didukung:

Untuk informasi selengkapnya tentang apa yang sedang vs. apa yang saat ini tidak didukung, baca artikel ringkasan kumpulan SQL tanpa server, atau artikel berikut:

Gambaran Umum

Untuk mendukung pengalaman yang mulus untuk kueri data yang terletak di file Azure Storage, kumpulan SQL tanpa server menggunakan fungsi OPENROWSET dengan kemampuan tambahan:

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

Tinjau artikel Kueri file Parquet untuk contoh penggunaan.

Kueri file CSV

Untuk mengkueri data sumber CSV, gunakan FORMAT = 'CSV'. Anda dapat menentukan skema file CSV sebagai bagian dari fungsi OPENROWSET 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 melepaskan karakter tersebut 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 akan diterapkan baik saat FIELDQUOTE diaktifkan atau tidak. Parameter ini tidak akan digunakan untuk melepaskan karakter tanda kutip. Karakter tanda kutip harus dipisahkan dengan karakter tanda kutip lainnya. 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 defaultnya 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. Tinjau artikel kueri format Delta Lake untuk contoh penggunaan.

Skema file

Bahasa SQL dalam Synapse SQL memungkinkan Anda menentukan skema file sebagai bagian dari fungsi OPENROWSET 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 dapat memberikan klausul WITH opsional dalam pernyataan OPENROWSET Anda.

  • Jika ada file data CSV, untuk membaca semua kolom, masukkan nama kolom dan jenis datanya. Jika Anda menginginkan subset kolom, gunakan nomor urut untuk memilih kolom dari file data asal berdasarkan urutan. Kolom akan terikat oleh penunjukan urutan.
  • Jika ada file data Parquet, masukkan nama kolom yang sesuai dengan nama kolom di file data asal. Kolom akan 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 klausul WITH dari pernyataan OPENROWSET, Anda dapat menginstruksikan layanan untuk mendeteksi (menyimpulkan) skema secara otomatis dari file yang mendasarinya.

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 akan disertakan dalam set file yang dihasilkan.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

Lihat Kueri folder dan beberapa file untuk contoh penggunaan.

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 ditampilkan adalah nvarchar(1024). Demi 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 bersarang atau berulang, seperti dalam file Parquet, kumpulan SQL tanpa server telah menambahkan ekstensi yang mengikuti.

Memproyeksikan data bersarang atau berulang

Untuk memproyeksikan data, jalankan pernyataan SELECT terhadap file Parquet yang berisi kolom jenis data bersarang. Pada output, nilai bersarang akan diserialisasikan menjadi 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 bersarang atau berulang dari artikel Kueri jenis Parquet bersarang.

Mengakses elemen dari kolom bersarang

Untuk mengakses elemen bersarang dari kolom bersarang, seperti Struct, gunakan "notasi titik" untuk menggabungkan nama bidang ke dalam jalur. Masukkan 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 default, fungsi OPENROWSET ini cocok dengan nama dan jalur bidang sumber dengan nama kolom yang disediakan dalam klausul WITH. Elemen yang terkandung pada tingkat bersarang lain dalam file Parquet sumber yang sama dapat diakses melalui klausul WITH.

Mengembalikan nilai

  • Fungsi akan menampilkan nilai skalar, seperti int, desimal, dan varchar, dari elemen yang ditentukan, dan pada jalur yang ditentukan, untuk semua jenis Parquet yang tidak berada dalam grup Jenis Bersarang.
  • Jika jalur menunjuk ke elemen yang berasal dari Jenis Bersarang, fungsi akan mengembalikan fragmen JSON dimulai dari elemen atas pada jalur yang ditentukan. Fragmen JSON adalah dari jenis varchar (8000).
  • Jika properti tidak dapat ditemukan di halaman column_name yang ditentukan, fungsi akan menampilkan kesalahan.
  • Jika properti tidak dapat ditemukan di column_path yang ditentukan, bergantung pada Mode jalur, fungsi akan menampilkan kesalahan saat dalam mode ketat atau null saat dalam mode lax.

Untuk sampel kueri, tinjau elemen Akses dari bagian kolom bersarang di artikel Kueri jenis Parquet bersarang.

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 proyeksi dan berikan:

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

Untuk mengakses elemen non-skalar dari kolom berulang, gunakan fungsi JSON_QUERY untuk setiap elemen non-skalar yang perlu Anda proyeksikan dan berikan:

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

Lihat fragmen sintaks di bawah ini:

    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.

Kueri sampel

Anda dapat mempelajari selengkapnya tentang kueri berbagai jenis data menggunakan kueri sampel.

Alat

Alat yang Anda perlukan untuk mengeluarkan kueri: - Azure Synapse Studio - Azure Data Studio - SQL Server Management Studio

Penyiapan demo

Langkah pertama Anda adalah membuat database tempat Anda akan menjalankan kueri. Kemudian Anda akan menginisialisasi objek dengan menjalankan skrip penyiapan pada database tersebut.

Skrip penyiapan ini akan membuat sumber data, kredensial lingkup database, dan format file eksternal yang digunakan untuk membaca data dalam sampel ini.

Catatan

Database hanya digunakan untuk menampilkan metadata, bukan untuk data aktual. Tuliskan nama database yang Anda gunakan, Anda akan membutuhkannya nanti.

CREATE DATABASE mydbname;

Data demo yang disediakan

Data demo berisi set data berikut ini:

  • NYC Taxi - Yellow Taxi Trip Records - bagian dari data NYC publik yang diatur dalam format CSV dan Parquet
  • Set data populasi dalam format CSV
  • File Parquet sampel dengan kolom bersarang
  • Buku dalam format JSON
Jalur folder Deskripsi
/csv/ Folder induk untuk data dalam format CSV
/csv/population/
/csv/population-unix/
/csv/population-unix-hdr/
/csv/population-unix-hdr-escape
/csv/population-unix-hdr-quoted		 Folder dengan file data Populasi dalam format CSV yang berbeda.
/csv/taxi/ Folder dengan file data publik NYC dalam format CSV
/parquet/ Folder induk untuk data dalam format Parquet
/parquet/taxi File data publik NYC dalam format Parquet, yang dibagi berdasarkan tahun, dan bulan menggunakan skema partisi Apache Hive/Hadoop.
/parquet/nested/ File Parquet sampel dengan kolom bersarang
/json/ Folder induk untuk data dalam format JSON
/json/books/ File JSON dengan data buku

Langkah berikutnya

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