Bagikan melalui

Melakukan kueri pada file JSON

Berlaku untuk:✅ Titik akhir analitik SQL dan Gudang di Microsoft Fabric

Dalam artikel ini, Anda akan mempelajari cara mengkueri file JSON menggunakan Fabric SQL, termasuk Fabric Data Warehouse dan titik akhir analitik SQL.

JSON (JavaScript Object Notation) adalah format ringan untuk data semi-terstruktur, banyak digunakan dalam big data untuk aliran sensor, konfigurasi IoT, log, dan data geospasial (misalnya, GeoJSON).

Menggunakan OPENROWSET untuk mengkueri file JSON secara langsung

Di Fabric Data Warehouse dan titik akhir analitik SQL untuk Lakehouse, Anda dapat mengkueri file JSON langsung di data lake menggunakan fungsi OPENROWSET.

OPENROWSET( BULK '{{filepath}}', [ , <options> ... ])
[ WITH ( <column schema and mappings> ) ];

Saat Anda menggunakan OPENROWSET untuk mengakses file JSON, Anda mulai dengan menentukan jalur file, yang dapat berupa URL langsung atau pola wildcard yang menargetkan satu atau beberapa file. Secara default, Fabric memproyeksikan setiap properti tingkat atas dalam dokumen JSON sebagai kolom terpisah dalam tataan hasil. Untuk file JSON Lines, setiap baris diperlakukan sebagai baris individual, menjadikannya ideal untuk skenario streaming.

Jika Anda memerlukan lebih banyak kontrol:

  • Gunakan klausa opsional WITH untuk menentukan skema secara eksplisit dan memetakan kolom ke properti JSON tertentu, termasuk jalur berlapis.
  • Gunakan DATA_SOURCE untuk mereferensikan lokasi akar untuk jalur relatif.
  • Konfigurasikan parameter penanganan kesalahan seperti MAXERRORS untuk mengelola masalah penguraian dengan anggun.

Kasus penggunaan file JSON umum

Jenis file JSON umum dan kasus penggunaan yang dapat Anda tangani di Microsoft Fabric:

  • File JSON yang dibatasi baris ("Garis JSON") di mana setiap baris adalah dokumen JSON mandiri dan valid (misalnya, peristiwa, pembacaan, atau entri log).
    • Seluruh file belum tentu merupakan satu dokumen JSON yang valid, melainkan merupakan urutan objek JSON yang dipisahkan oleh karakter baris baru.
    • File dengan format ini biasanya memiliki ekstensi .jsonl, , .ldjsonatau .ndjson. Ideal untuk streaming dan skenario tambah-saja - penulis dapat menambahkan peristiwa baru sebagai baris baru tanpa menulis ulang file atau merusak strukturnya.
  • File JSON dokumen tunggal ("JSON klasik") dengan ekstensi .json, di mana seluruh file adalah satu dokumen JSON yang valid - baik berupa satu objek atau array objek (potensial bersarang).
    • Ini umumnya digunakan untuk konfigurasi, rekam jepret, dan himpunan data yang diekspor dalam satu bagian.
    • Misalnya, file GeoJSON biasanya menyimpan satu objek JSON yang menjelaskan fitur dan geometrinya.

Mengkueri file JSONL dengan OPENROWSET

Fabric Data Warehouse dan endpoint analitik SQL untuk Lakehouse memungkinkan pengembang SQL untuk menjalankan kueri file JSON Lines (.jsonl, .ldjson, .ndjson) langsung dari data lake dengan menggunakan fungsi OPENROWSET.

File-file ini berisi satu objek JSON yang valid per baris, membuatnya ideal untuk skenario streaming dan khusus tambahan. Untuk membaca file Baris JSON, berikan URL-nya dalam BULK argumen:

SELECT TOP 10 *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl'
);

Secara default, OPENROWSET menggunakan inferensi skema, secara otomatis menemukan semua properti tingkat atas di setiap objek JSON dan mengembalikannya sebagai kolom.

Namun, secara eksplisit Anda dapat menentukan skema untuk menentukan properti yang dikembalikan dan menggantikan jenis data yang disimpulkan.

SELECT TOP 10 *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl'
) WITH (
    country_region VARCHAR(100),
    confirmed INT,
    date_reported DATE '$.updated'
);

Definisi skema eksplisit berguna ketika:

  • Anda ingin menggantikan jenis data bawaan yang disimpulkan (misalnya, untuk memaksa jenis data tanggal, bukan varchar).
  • Anda memerlukan nama kolom yang stabil dan proyeksi selektif.
  • Anda ingin memetakan kolom ke properti JSON tertentu, termasuk jalur berlapis.

Membaca struktur JSON kompleks (berlapis) dengan OPENROWSET

Fabric Data Warehouse dan titik akhir analitik SQL untuk Lakehouse memungkinkan pengembang SQL membaca JSON dengan objek atau subarray berlapis langsung dari danau dengan menggunakan OPENROWSET.

{
  "type": "Feature",
  "properties": {
    "shapeName": "Serbia",
    "shapeISO": "SRB",
    "shapeID": "94879208B25563984444888",
    "shapeGroup": "SRB",
    "shapeType": "ADM0"
  }
}

Dalam contoh berikut, lakukan kueri pada file yang berisi data sampel, kemudian gunakan klausul WITH untuk memproyeksikan secara eksplisit properti pada tingkat daun.

SELECT
    *
FROM
  OPENROWSET(
    BULK '/Files/parquet/nested/geojson.jsonl'
  )
  WITH (
    -- Top-level field
    [type]     VARCHAR(50),
    -- Leaf properties from the nested "properties" object
    shapeName  VARCHAR(200) '$.properties.shapeName',
    shapeISO   VARCHAR(50)  '$.properties.shapeISO',
    shapeID    VARCHAR(200) '$.properties.shapeID',
    shapeGroup VARCHAR(50)  '$.properties.shapeGroup',
    shapeType  VARCHAR(50)  '$.properties.shapeType'
  );

Nota

Contoh ini menggunakan jalur relatif tanpa sumber data, yang berfungsi saat mengkueri file di Lakehouse Anda melalui titik akhir analitik SQL-nya. Di Fabric Data Warehouse, Anda harus:

  • Gunakan jalur absolut ke file, atau
  • Tentukan URL akar di sumber data eksternal dan referensikan dalam OPENROWSET pernyataan dengan menggunakan DATA_SOURCE opsi .

Perluas array berlapis (JSON ke baris) dengan OPENROWSET

Fabric Data Warehouse dan titik akhir analitik SQL untuk Lakehouse memungkinkan Anda membaca file JSON dengan array berlapis dengan menggunakan OPENROWSET. Kemudian, Anda dapat memperluas (unnest) array tersebut dengan menggunakan CROSS APPLY OPENJSON. Metode ini berguna ketika dokumen tingkat atas berisi sub-array yang Anda inginkan sebagai satu baris per elemen.

Dalam contoh input berikut yang disederhanakan, dokumen seperti GeoJSON memiliki array fitur:

{
  "type": "FeatureCollection",
  "crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
  "features": [
    {
      "type": "Feature",
      "properties": {
        "shapeName": "Serbia",
        "shapeISO": "SRB",
        "shapeID": "94879208B25563984444888",
        "shapeGroup": "SRB",
        "shapeType": "ADM0"
      },
      "geometry": {
        "type": "Line",
        "coordinates": [[[19.6679328, 46.1848744], [19.6649294, 46.1870428], [19.6638492, 46.1890231]]]
      }
    }
  ]
}

Kueri berikut:

  1. Membaca dokumen JSON dari danau dengan menggunakan OPENROWSET, memproyeksikan properti jenis tingkat atas bersama dengan array fitur mentah.
  2. Menerapkan CROSS APPLY OPENJSON untuk memperluas array fitur sehingga setiap elemen menjadi barisnya sendiri dalam set hasil. Dalam ekspansi ini, kueri mengekstrak nilai berlapis dengan menggunakan ekspresi jalur JSON. Nilai seperti shapeName, , shapeISOdan geometry detail seperti geometry.type dan coordinates, sekarang merupakan kolom datar untuk analisis yang lebih mudah.
SELECT
  r.crs_name,
  f.[type] AS feature_type,
  f.shapeName,
  f.shapeISO,
  f.shapeID,
  f.shapeGroup,
  f.shapeType,
  f.geometry_type,
  f.coordinates
FROM
  OPENROWSET(
      BULK '/Files/parquet/nested/geojson.jsonl'
  )
  WITH (
      crs_name    VARCHAR(100)  '$.crs.properties.name', -- top-level nested property
      features    VARCHAR(MAX)  '$.features'             -- raw JSON array
  ) AS r
CROSS APPLY OPENJSON(r.features)
WITH (
  [type]           VARCHAR(50),
  shapeName        VARCHAR(200)  '$.properties.shapeName',
  shapeISO         VARCHAR(50)   '$.properties.shapeISO',
  shapeID          VARCHAR(200)  '$.properties.shapeID',
  shapeGroup       VARCHAR(50)   '$.properties.shapeGroup',
  shapeType        VARCHAR(50)   '$.properties.shapeType',
  geometry_type    VARCHAR(50)   '$.geometry.type',
  coordinates      VARCHAR(MAX)  '$.geometry.coordinates'
) AS f;

Konten terkait

  • Last updated on