Udostępnij przez

Wykonywanie zapytań względem plików JSON

Dotyczy:✅ końcowego punktu analizy SQL i magazynu danych w usłudze Microsoft Fabric

W tym artykule dowiesz się, jak wykonywać zapytania dotyczące plików JSON przy użyciu usługi Fabric SQL, w tym usługi Fabric Data Warehouse i punktu końcowego analizy SQL.

JSON (JavaScript Object Notation) to uproszczony format danych częściowo ustrukturyzowanych, powszechnie używany w danych big data na potrzeby strumieni czujników, konfiguracji IoT, dzienników i danych geoprzestrzennych (na przykład GeoJSON).

Używanie zestawu OPENROWSET do bezpośredniego wykonywania zapytań dotyczących plików JSON

W usłudze Fabric Data Warehouse i punkcie końcowym analizy SQL dla Lakehouse można wykonywać zapytania dotyczące plików JSON bezpośrednio w lake przy użyciu funkcji OPENROWSET.

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

Podczas wykonywania zapytań dotyczących plików JSON za pomocą funkcji OPENROWSET należy zacząć od określenia ścieżki pliku, która może być bezpośrednim adresem URL lub wzorcem wieloznacznymi przeznaczonym dla co najmniej jednego pliku. Domyślnie Fabric projektuje każdą właściwość najwyższego poziomu w dokumencie JSON jako oddzielną kolumnę w zestawie wyników. W przypadku plików JSON Lines każdy wiersz jest traktowany jako pojedynczy wiersz, co czyni go idealnym rozwiązaniem w scenariuszach przesyłania strumieniowego.

Jeśli potrzebujesz większej kontroli:

  • Użyj opcjonalnej klauzuli WITH, aby jawnie zdefiniować schemat i mapować kolumny na specyficzne właściwości JSON, w tym ścieżki zagnieżdżone.
  • Użyj DATA_SOURCE do odwoływania się do głównej lokalizacji dla ścieżek względnych.
  • Skonfiguruj parametry obsługi błędów, takie jak MAXERRORS, aby płynnie zarządzać problemami związanych z analizowaniem.

Typowe przypadki użycia plików JSON

Typowe typy plików JSON i przypadki użycia, które można obsłużyć w usłudze Microsoft Fabric:

  • Rozdzielane wierszami pliki JSON ("Linie JSON"), w których każdy wiersz jest autonomicznym, prawidłowym dokumentem JSON (na przykład zdarzeniem, odczytem lub wpisem dziennika).
    • Cały plik nie musi być pojedynczym prawidłowym dokumentem JSON, a raczej sekwencją obiektów JSON oddzielonych znakami nowego wiersza.
    • Pliki w tym formacie zwykle mają rozszerzenia .jsonl, .ldjsonlub .ndjson. Idealne do przesyłania strumieniowego i scenariuszy typu tylko do dołączania – piszący mogą dołączyć nowe zdarzenie jako nową linię bez ponownego zapisywania pliku lub naruszania jego struktury.
  • Pliki pojedynczego dokumentu JSON ("klasyczny JSON") z rozszerzeniem .json, gdzie cały plik stanowi jeden prawidłowy dokument JSON — pojedynczy obiekt lub tablica obiektów (potencjalnie zagnieżdżonych).
    • Jest często używany do konfigurowania, migawek i zestawów danych eksportowanych w całości.
    • Na przykład pliki GeoJSON często przechowują pojedynczy obiekt JSON opisujący cechy i ich geometrie.

Wykonywanie zapytań dotyczących plików JSONL przy użyciu zestawu OPENROWSET

Usługa Fabric Data Warehouse i punkt końcowy analizy SQL dla Lakehouse umożliwiają deweloperom SQL wykonywanie zapytań dotyczących plików JSON Lines (.jsonl, .ldjson, .ndjson) bezpośrednio z magazynu data lake przy użyciu OPENROWSET funkcji.

Te pliki zawierają jeden prawidłowy obiekt JSON w każdym wierszu, co czyni je idealnymi do przesyłania strumieniowego i scenariuszy tylko z dopisywaniem. Aby odczytać plik wierszy JSON, podaj jego adres URL w argumencie BULK :

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'
);

Domyślnie OPENROWSET używa wnioskowania schematu, automatycznie odnajdując wszystkie właściwości najwyższego poziomu w każdym obiekcie JSON i zwracając je jako kolumny.

Można jednak jawnie zdefiniować schemat, aby kontrolować, które właściwości są zwracane i zastępować wywnioskowane typy danych:

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'
);

Jawna definicja schematu jest przydatna, gdy:

  • Chcesz zastąpić domyślne typy wnioskowane (na przykład wymusić typ danych daty zamiast varchar).
  • Potrzebne są stabilne nazwy kolumn i projekcja selektywna.
  • Chcesz mapować kolumny na określone właściwości JSON, w tym ścieżki zagnieżdżone.

Odczytywanie złożonych (zagnieżdżonych) struktur JSON za pomocą zestawu OPENROWSET

Usługa Fabric Data Warehouse i punkt końcowy analizy SQL dla usługi Lakehouse umożliwiają deweloperom SQL odczytywanie kodu JSON z zagnieżdżonych obiektów lub podarray bezpośrednio z magazynu typu lake przy użyciu polecenia OPENROWSET.

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

W poniższym przykładzie wykonaj zapytanie dotyczące pliku zawierającego przykładowe dane i użyj klauzuli WITH, aby jawnie odwzorować jego właściwości na poziomie liścia.

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'
  );

Uwaga / Notatka

W tym przykładzie użyto ścieżki względnej bez źródła danych, które działa podczas wykonywania zapytań dotyczących plików w usłudze Lakehouse za pośrednictwem punktu końcowego analizy SQL. W Fabric Data Warehouse musisz wykonać jedną z następujących czynności:

  • Użyj ścieżki bezwzględnej do pliku lub
  • Określ główny adres URL w zewnętrznym źródle danych i odwołaj się do niego w poleceniu OPENROWSET przy użyciu opcji DATA_SOURCE.

Rozwiń zagnieżdżone tablice (od JSON do wierszy) za pomocą funkcji OPENROWSET

Usługa Fabric Data Warehouse i punkt końcowy analizy SQL dla usługi Lakehouse umożliwiają odczytywanie plików JSON z zagnieżdżonych tablic przy użyciu polecenia OPENROWSET. Następnie można rozwinąć (rozłożyć) te tablice przy użyciu polecenia CROSS APPLY OPENJSON. Ta metoda jest przydatna, kiedy w dokumencie najwyższego poziomu znajduje się tablica podrzędna, którą chcesz umieścić jako jeden wiersz na element.

W poniższym uproszczonym przykładzie wejściowym dokument podobny do geoJSON ma tablicę cech:

{
  "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]]]
      }
    }
  ]
}

Następujące zapytanie:

  1. Odczytuje dokument JSON z jeziora przy użyciu metody OPENROWSET, wyświetlając właściwość typu najwyższego poziomu wraz z tablicą nieprzetworzonych cech.
  2. Stosuje CROSS APPLY OPENJSON do rozszerzenia tablicy cech, aby każdy element przekształcił się w osobny wiersz w zestawie wyników. W ramach tego rozszerzenia zapytanie wyodrębnia zagnieżdżone wartości przy użyciu wyrażeń ścieżki JSON. Wartości, takie jak shapeName, shapeISO, i geometry oraz szczegóły jak geometry.type i coordinates, są teraz płaskimi kolumnami w celu łatwiejszej analizy.
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;

Treści powiązane

  • Last updated on