Mengkueri file menggunakan kumpulan SQL tanpa server

10 menit

Anda dapat menggunakan kumpulan SQL tanpa server untuk mengkueri file data dalam berbagai format file umum, termasuk:

Teks yang dibatasi, seperti file nilai yang dipisahkan koma (CSV).
File notasi objek JavaScript (JSON).
File parket.

Sintaks dasar untuk kueri sama untuk semua jenis file ini, dan dibangun pada fungsi OPENROWSET SQL; yang menghasilkan set baris tabular dari data dalam satu atau beberapa file. Misalnya, kueri berikut dapat digunakan untuk mengekstrak data dari file CSV.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

Fungsi OPENROWSET mencakup lebih banyak parameter yang menentukan faktor-faktor seperti:

Skema himpunan baris yang dihasilkan
Opsi pemformatan tambahan untuk file teks yang dibatasi.

Petunjuk / Saran

Anda akan menemukan sintaks lengkap untuk fungsi OPENROWSET dalam dokumentasi Azure Synapse Analytics.

Output dari OPENROWSET adalah set baris tempat alias harus ditetapkan. Dalam contoh sebelumnya, alias baris digunakan untuk memberi nama set baris yang dihasilkan.

Parameter massal menyertakan URL lengkap ke lokasi di data lake yang berisi file data. Ini bisa berupa file individual, atau folder dengan ekspresi kartubebas untuk memfilter jenis file yang harus disertakan. Parameter FORMAT menentukan jenis data yang sedang dikueri. Contoh di atas membaca teks yang dibatasi dari semua file .csv di folder file.

Nota

Contoh ini mengasumsikan bahwa pengguna memiliki akses ke file di penyimpanan yang mendasar, Jika file dilindungi dengan kunci SAS atau identitas kustom, Anda harus membuat kredensial cakupan server.

Seperti yang terlihat dalam contoh sebelumnya, Anda dapat menggunakan kartubebas dalam parameter massal untuk menyertakan atau mengecualikan file dalam kueri. Daftar berikut ini memperlihatkan beberapa contoh bagaimana hal ini dapat digunakan:

https://mydatalake.blob.core.windows.net/data/files/file1.csv: Hanya sertakan file1.csv dalam folder file.
https://mydatalake.blob.core.windows.net/data/files/file*.csv: Semua file .csv di folder file dengan nama yang dimulai dengan "file".
https://mydatalake.blob.core.windows.net/data/files/*: Semua file di folder file.
https://mydatalake.blob.core.windows.net/data/files/**: Semua file di folder file, dan secara rekursif subfoldernya.

Anda juga dapat menentukan beberapa jalur file dalam parameter massal, memisahkan setiap jalur dengan koma.

Mengkueri file teks yang dibatasi

File teks yang dibatasi adalah format file umum dalam banyak bisnis. Pemformatan tertentu yang digunakan dalam file yang dibatasi dapat bervariasi, misalnya:

Dengan dan tanpa baris header.
Nilai koma dan dibatasi tab.
Akhiran garis gaya Windows dan Unix.
Nilai yang tidak dikutip dan dikutip, serta karakter pelepasan.

Terlepas dari jenis file berbatas yang Anda gunakan, Anda dapat membaca data dari file tersebut dengan menggunakan fungsi OPENROWSET dengan parameter format csv, dan parameter lain yang diperlukan untuk menangani detail pemformatan tertentu untuk data Anda. Contohnya:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

PARSER_VERSION digunakan untuk menentukan bagaimana kueri menginterpretasikan pengodean teks yang digunakan dalam file. Versi 1.0 adalah default dan mendukung berbagai pengodean file, sementara versi 2.0 mendukung lebih sedikit pengodean tetapi menawarkan performa yang lebih baik. Parameter FIRSTROW digunakan untuk melewati baris dalam file teks, untuk menghilangkan teks yang tidak terstruktur atau untuk mengabaikan baris yang berisi judul kolom.

Parameter tambahan yang mungkin Anda perlukan saat bekerja dengan file teks yang dibatasi meliputi:

FIELDTERMINATOR - karakter yang digunakan untuk memisahkan nilai bidang di setiap baris. Misalnya, file yang dibatasi tab memisahkan bidang dengan karakter TAB (\t). Terminator bidang default adalah koma (,).
ROWTERMINATOR - karakter yang digunakan untuk menandakan akhir baris data. Misalnya, file teks Windows standar menggunakan kombinasi pengembalian pengangkutan (CR) dan umpan baris (LF), yang ditunjukkan oleh kode \n; sementara file teks bergaya UNIX menggunakan karakter umpan baris tunggal, yang dapat ditunjukkan menggunakan kode 0x0a.
FIELDQUOTE - karakter yang digunakan untuk mengapit nilai string yang dikutip. Misalnya, untuk memastikan bahwa koma dalam nilai bidang alamat 126 Main St, apt 2 tidak ditafsirkan sebagai pemisah bidang, Anda mungkin mengapit seluruh nilai bidang dalam tanda kutip seperti ini: "126 Main St, apt 2". Tanda kutip ganda (") adalah karakter kutipan bidang default.

Petunjuk / Saran

Untuk detail parameter tambahan saat bekerja dengan file teks yang dibatasi, lihat dokumentasi Azure Synapse Analytics.

Menentukan skema set baris

Umum untuk file teks yang dibatasi untuk menyertakan nama kolom di baris pertama. Fungsi OPENROWSET dapat menggunakan ini untuk menentukan skema untuk kumpulan baris yang dihasilkan, dan secara otomatis menyimpulkan jenis data kolom berdasarkan nilai yang dikandungnya. Misalnya, pertimbangkan teks yang dibatasi berikut:

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

Data terdiri dari tiga kolom berikut:

product_id (bilangan bulat)
product_name (string)
list_price (angka desimal)

Anda dapat menggunakan kueri berikut untuk mengekstrak data dengan nama kolom yang benar dan jenis data SQL Server yang disimpulkan dengan tepat (dalam hal ini INT, NVARCHAR, dan DESIMAL)

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

Parameter HEADER_ROW (yang hanya tersedia saat menggunakan parser versi 2.0) menginstruksikan mesin kueri untuk menggunakan baris pertama data di setiap file sebagai nama kolom, seperti ini:

product_id	product_name	harga daftar
123	Widget	12.9900
124	Gadget	3.9900

Sekarang pertimbangkan data berikut:

123,Widget,12.99
124,Gadget,3.99

Kali ini, file tidak berisi nama kolom dalam baris header; jadi sementara jenis data masih dapat disimpulkan, nama kolom akan diatur ke C1, C2, C3, dan sebagainya.

C1	C2	C3
123	Widget	12.9900
124	Gadget	3.9900

Untuk menentukan nama kolom dan tipe data eksplisit, Anda dapat mengambil alih nama kolom default dan jenis data yang disimpulkan dengan memberikan definisi skema dalam klausa WITH, seperti ini:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

Kueri ini menghasilkan hasil yang diharapkan:

product_id	product_name	harga daftar
123	Widget	12.99
124	Gadget	3,99

Petunjuk / Saran

Saat bekerja dengan file teks, Anda mungkin mengalami ketidakcocokan dengan data yang dikodekan UTF-8 dan kolase yang digunakan dalam database master untuk kumpulan SQL tanpa server. Untuk mengatasi hal ini, Anda dapat menentukan kolaborasi yang kompatibel untuk kolom VARCHAR individual dalam skema. Lihat panduan pemecahan masalah untuk detail selengkapnya.

Mengkueri file JSON

JSON adalah format populer untuk aplikasi web yang bertukar data melalui antarmuka REST atau menggunakan penyimpanan data NoSQL seperti Azure Cosmos DB. Jadi, tidak jarang mempertahankan data sebagai dokumen JSON dalam file di data lake untuk analisis.

Misalnya, file JSON yang menentukan produk individual mungkin terlihat seperti ini:

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

Untuk mengembalikan data produk dari folder yang berisi beberapa file JSON dalam format ini, Anda dapat menggunakan kueri SQL berikut:

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

OPENROWSET tidak memiliki format khusus untuk file JSON, jadi Anda harus menggunakan format csv dengan FIELDTERMINATOR, FIELDQUOTE, dan ROWTERMINATOR diatur ke 0x0b, dan skema yang menyertakan kolom NVARCHAR (MAX) tunggal. Hasil kueri ini adalah kumpulan baris yang berisi satu kolom dokumen JSON, seperti ini:

dok
{"product_id":123,"product_name":"Widget","list_price": 12.99}
{"product_id":124,"product_name":"Gadget","list_price": 3.99}

Untuk mengekstrak nilai individual dari JSON, Anda dapat menggunakan fungsi JSON_VALUE dalam pernyataan SELECT, seperti yang ditunjukkan di sini:

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

Kueri ini akan mengembalikan set baris yang mirip dengan hasil berikut:

produk	harga
Widget	12.99
Gadget	3,99

Mengkueri file Parquet

Parquet adalah format yang umum digunakan untuk pemrosesan big data pada penyimpanan file terdistribusi. Ini adalah format data efisien yang dioptimalkan untuk kompresi dan kueri analitik.

Dalam kebanyakan kasus, skema data disematkan dalam file Parquet, jadi Anda hanya perlu menentukan parameter MASSAL dengan jalur ke file yang ingin Anda baca, dan parameter FORMAT; Seperti ini:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

Data yang dipartisi kueri

Ini umum dalam data lake untuk mempartisi data dengan memisahkan beberapa file dalam subfolder yang mencerminkan kriteria partisi. Ini memungkinkan sistem pemrosesan terdistribusi bekerja secara paralel pada beberapa partisi data, atau untuk dengan mudah menghilangkan pembacaan data dari folder tertentu berdasarkan kriteria pemfilteran. Misalnya, Anda perlu memproses data pesanan penjualan secara efisien, dan sering kali perlu memfilter berdasarkan tahun dan bulan di mana pesanan ditempatkan. Anda dapat mempartisi data menggunakan folder, seperti ini:

/Pesanan
- /tahun=2020
  - /month=1
    - /01012020.parquet
    - /02012020.parquet
    - ...
  - /month=2
    - /01022020.parquet
    - /02022020.parquet
    - ...
  - ...
- /year=2021
  - /month=1
    - /01012021.parquet
    - /02012021.parquet
    - ...
  - ...

Untuk membuat kueri yang memfilter hasil untuk menyertakan hanya pesanan untuk Januari dan Februari 2020, Anda dapat menggunakan kode berikut:

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');

Parameter filepath bernomor dalam klausul WHERE mereferensikan wildcard dalam nama folder di jalur MASSAL -so parameter 1 adalah * dalam nama folder year=*, dan parameter 2 adalah * dalam nama folder month=*.

Saran dan Komentar

Apakah halaman ini membantu?