Abfragen von JSON-Dateien mit einem serverlosen SQL-Pool in Azure Synapse Analytics

Artikel
04/27/2023

In diesem Artikel erfahren Sie, wie Sie eine Abfrage mit einem serverlosen SQL-Pool in Azure Synapse Analytics schreiben können. Das Ziel der Abfrage ist das Lesen von JSON-Dateien mithilfe von OPENROWSET.

JSON-Standarddateien, in denen mehrere JSON-Dokumente als JSON-Array gespeichert sind.
Durch Zeilen getrennte JSON-Dateien, in denen JSON-Dokumente durch Zeilenumbruchzeichen getrennt sind. Gängige Erweiterungen für diese Dateitypen sind jsonl, ldjsonund ndjson.

Lesen von JSON-Dokumenten

Am einfachsten können Sie den Inhalt Ihrer JSON-Datei anzeigen, indem Sie der Funktion OPENROWSET die Datei-URL bereitstellen, CSV als FORMAT angeben und den Wert 0x0b für fieldterminator und fieldquote festlegen. Wenn Sie durch Zeilen getrennte JSON-Dateien lesen müssen, ist dies ausreichend. Wenn Sie über eine klassische JSON-Datei verfügen, müssen Sie für rowterminator den Wert 0x0b festlegen. Die Funktion OPENROWSET analysiert die JSON-Datei und gibt jedes Dokument im folgenden Format zurück:

DOC
{"date_rep":"2020-07-24","day":24,"month":7,"year":2020,"cases":3,"deaths":0,"geo_id":"AF"}
{"date_rep":"2020-07-25","day":25,"month":7,"year":2020,"cases":7,"deaths":0,"geo_id":"AF"}
{"date_rep":"2020-07-26","day":26,"month":7,"year":2020,"cases":4,"deaths":0,"geo_id":"AF"}
{"date_rep":"2020-07-27","day":27,"month":7,"year":2020,"cases":8,"deaths":0,"geo_id":"AF"}

Wenn die Datei öffentlich verfügbar ist oder Ihre Microsoft Entra-Identität auf diese Datei zugreifen kann, sollten Sie den Inhalt der Datei mithilfe einer Abfrage wie im folgenden Beispiel anzeigen können.

Lesen von JSON-Dateien

Die folgende Beispielabfrage liest JSON- und durch Zeilen getrennte JSON-Dateien und gibt jedes Dokument als separate Zeile zurück.

select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.jsonl',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.json',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
    ) with (doc nvarchar(max)) as rows

Das JSON-Dokument in der obigen Beispielabfrage enthält ein Array von Objekten. Die Abfrage gibt jedes Objekt als separate Zeile im Resultset zurück. Stellen Sie sicher, dass Sie auf diese Datei zugreifen können. Wenn Ihre Datei mit einem SAS-Schlüssel oder einer benutzerdefinierten Identität geschützt ist, müssen Sie Anmeldeinformationen auf Serverebene für die SQL-Anmeldung einrichten.

Datenquellennutzung

Im vorherigen Beispiel wird der vollständige Pfad zur Datei verwendet. Alternativ können Sie eine externe Datenquelle mit dem Speicherort erstellen, der auf den Stammordner des Speichers verweist, und diese Datenquelle sowie den relativen Pfad zur Datei in der Funktion OPENROWSET verwenden:

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.json',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
    ) with (doc nvarchar(max)) as rows

Wenn eine Datenquelle mit einem SAS-Schlüssel oder einer benutzerdefinierten Identität geschützt ist, können Sie die Datenquelle mit datenbankweit gültigen Anmeldeinformationen konfigurieren.

In den folgenden Abschnitten erfahren Sie, wie Sie verschiedene Typen von JSON-Dateien abfragen.

Analysieren von JSON-Dokumenten

Die Abfragen in den vorherigen Beispielen geben jedes JSON-Dokument als einzelne Zeichenfolge in einer separaten Zeile des Resultsets zurück. Mit den Funktionen JSON_VALUE und OPENJSON können Sie die Werte in JSON-Dokumenten analysieren und als relationale Werte zurückgeben, wie es im folgenden Beispiel gezeigt wird:

date_rep	cases	geo_id
2020-07-24	3	AF
2020-07-25	7	AF
2020-07-26	4	AF
2020-07-27	8	AF

JSON-Beispieldokument

Die Abfragebeispiele lesen JSON-Dateien, die Dokumente mit der folgenden Struktur enthalten:

{
    "date_rep":"2020-07-24",
    "day":24,"month":7,"year":2020,
    "cases":13,"deaths":0,
    "countries_and_territories":"Afghanistan",
    "geo_id":"AF",
    "country_territory_code":"AFG",
    "continent_exp":"Asia",
    "load_date":"2020-07-25 00:05:14",
    "iso_country":"AF"
}

Hinweis

Wenn diese Dokumente als eine durch Trennzeichen getrennte JSON-Datei gespeichert werden, müssen Sie FIELDTERMINATOR und FIELDQUOTE auf „0x0b“ festlegen. Wenn Sie über ein JSON-Standardformat verfügen, müssen Sie ROWTERMINATOR auf „0x0b“ festlegen.

Abfragen von JSON-Dateien mit JSON_VALUE

Die folgende Abfrage zeigt, wie Sie mithilfe von JSON_VALUE skalare Werte (date_rep, countries_and_territories, cases) aus einem JSON-Dokument abrufen:

select
    JSON_VALUE(doc, '$.date_rep') AS date_reported,
    JSON_VALUE(doc, '$.countries_and_territories') AS country,
    CAST(JSON_VALUE(doc, '$.deaths') AS INT) as fatal,
    JSON_VALUE(doc, '$.cases') as cases,
    doc
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
order by JSON_VALUE(doc, '$.geo_id') desc

Nachdem Sie die JSON-Eigenschaften aus einem JSON-Dokument extrahiert haben, können Sie Spaltenaliase definieren und optional den Textwert in einen anderen Typ umwandeln.

Abfragen von JSON-Dateien mit OPENJSON

Die folgende Abfrage verwendet OPENJSON. Die in Serbien gemeldeten COVID-Statistiken werden abgerufen:

select
    *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
    cross apply openjson (doc)
        with (  date_rep datetime2,
                cases int,
                fatal int '$.deaths',
                country varchar(100) '$.countries_and_territories')
where country = 'Serbia'
order by country, date_rep desc;

Die Ergebnisse sind funktionsgleich mit den Ergebnissen, die von der JSON_VALUE-Funktion zurückgegeben werden. In einigen Fällen kann OPENJSON Vorteil gegenüber JSON_VALUE bieten:

In der WITH-Klausel können Sie die Spaltenaliase und die Typen für jede Eigenschaft explizit festlegen. Sie müssen die CAST-Funktion nicht in jeder Spalte der SELECT-Liste einfügen.
OPENJSON ist möglicherweise schneller, wenn eine große Anzahl von Eigenschaften zurückgegeben wird. Wenn die Rückgabe nur ein oder zwei Eigenschaften umfasst, verursacht die OPENJSON-Funktion möglicherweise einen Overhead.
Um das Array aus jedem Dokument zu analysieren, müssen Sie die OPENJSON-Funktion verwenden und mit der übergeordneten Zeile verknüpfen.

Nächste Schritte

Die nächsten Artikel in dieser Reihe veranschaulichen Folgendes: