Freigeben über

Importieren von JSON-Dokumenten in SQL Server

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure SQL Managed Instance

In diesem Artikel wird das Importieren von JSON-Dateien in SQL Server beschrieben. JSON-Dokumente speichern viele Arten von Daten, z. B. Anwendungsprotokolle, Sensordaten usw. Es ist wichtig, in der Lage zu sein, die in Dateien gespeicherten JSON-Daten zu lesen, in SQL Server zu laden und sie zu analysieren.

Die Beispiele in diesem Artikel verwenden eine JSON-Datei aus einem GitHub-Beispiel mit einer Liste von Büchern.

Berechtigungen

Auf Instanzebene erfordert dieses Feature die Mitgliedschaft der festen Serverrolle bulkadmin oder ADMINISTER BULK OPERATIONS-Berechtigungen.

Für die Datenbankebene erfordert dieses Feature ADMINISTER DATABASE BULK OPERATIONS-Berechtigungen.

Für den Zugriff auf Azure Blob Storage ist Lese-/Schreibzugriff erforderlich.

Importieren eines JSON-Dokuments in eine einzelne Spalte

OPENROWSET(BULK) ist eine Tabellenwertfunktion, die Daten aus einer beliebigen Datei auf dem lokalen Laufwerk oder im Netzwerk lesen kann, wenn SQL Server über Lesezugriff für diesen Speicherort verfügt. Sie gibt eine Tabelle mit einer einzelnen Spalte zurück, die den Inhalt der Datei enthält. Es gibt verschiedene Optionen, die Sie mit der OPENROWSET(BULK)-Funktion verwenden können, z. B. Trennzeichen. Im einfachsten Fall können Sie einfach den gesamten Inhalt einer Datei als Textwert laden. (Dieser einzelne große Wert wird als ein „Single Character Large Object“ oder SINGLE_CLOB bezeichnet.)

Hier finden Sie ein Beispiel für die OPENROWSET(BULK)-Funktion, die den Inhalt einer JSON-Datei liest und als einzelnen Wert an den Benutzer zurückgibt:

SELECT BulkColumn
FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j;

OPENJSON(BULK) liest den Inhalt der Datei und gibt ihn in BulkColumn zurück.

Sie können auch den Inhalt der Datei in eine lokale Variable oder in eine Tabelle laden, wie im folgenden Beispiel gezeigt:

-- Load file contents into a variable
DECLARE @json NVARCHAR(MAX);
SELECT @json = BulkColumn
 FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

-- Load file contents into a table
SELECT BulkColumn
INTO #temp
FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

Nachdem Sie den Inhalt der JSON-Datei geladen haben, können Sie den JSON-Text in einer Tabelle speichern.

Importieren von JSON-Dokumenten aus Azure File Storage

Sie können auch wie bereits beschrieben OPENROWSET(BULK) zum Lesen von JSON-Dateien aus anderen Dateispeicherorten verwenden, auf die SQL Server zugreifen kann. Azure File Storage unterstützt z.B. das SMB-Protokoll. Daher können Sie der Azure File Storage-Freigabe mithilfe der folgenden Vorgehensweise eine virtuelle Festplatte zuordnen:

  1. Erstellen Sie mithilfe des Azure-Portals oder Azure PowerShell ein Dateispeicherkonto (z.B. mystorage), eine Dateifreigabe (z.B. sharejson) und einen Ordner in Azure File Storage.

  2. Laden Sie einige JSON-Dateien in die Dateispeicherfreigabe hoch.

  3. Erstellen Sie auf Ihrem Computer eine ausgehende Firewallregel in Windows-Firewall, die Port 445 zulässt. Ihr Internetdienstanbieter könnte diesen Port möglicherweise blockieren. Wenn ein DNS-Fehler (Fehler 53) im folgenden Schritt auftritt, dann ist Port 445 nicht geöffnet, oder Ihr Internetdienstanbieter blockiert ihn.

  4. Binden Sie die Azure File Storage-Dateifreigabe als lokales Laufwerk (z.B. T:) ein.

    Hier finden Sie die Befehlssyntax:

    net use [drive letter] \\[storage name].file.core.windows.net\[share name] /u:[storage account name] [storage account access key]

    Hier finden Sie ein Beispiel, in dem der lokale Laufwerkbuchstabe T: der Azure File Storage-Dateifreigabe zugewiesen wird:

    net use t: \\mystorage.file.core.windows.net\sharejson /u:myaccount hb5qy6eXLqIdBj0LvGMHdrTiygkjhHDvWjUZg3Gu7bubKLg==

    Der Speicherkontoschlüssel und der primäre und sekundäre Speicherkonto-Zugriffsschlüssel befinden sich im Abschnitt „Schlüssel“ unter „Einstellungen“ im Azure-Portal.

  5. Sie können jetzt über die Azure File Storage-Dateifreigabe auf die JSON-Dateien zugreifen, indem Sie das zugeordnete Laufwerk verwenden, wie im folgenden Beispiel gezeigt:

    SELECT book.*
FROM OPENROWSET(BULK N't:\books\books.json', SINGLE_CLOB) AS json
CROSS APPLY OPENJSON(BulkColumn) WITH (
    id NVARCHAR(100),
    name NVARCHAR(100),
    price FLOAT,
    pages_i INT,
    author NVARCHAR(100)
) AS book

Weitere Informationen zu Azure File Storage finden Sie unter File Storage.

Importieren von JSON-Dokumenten aus Azure BLOB-Speicher

Gilt für: SQL Server 2017 (14.x) und höhere Versionen sowie Azure SQL

Sie können Dateien mit dem Befehl T-SQL BULK INSERT oder der OPENROWSET-Funktion direkt aus Azure Blob Storage in Azure SQL-Datenbank laden.

Erstellen Sie zunächst eine externe Datenquelle, wie im folgenden Beispiel gezeigt.

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://myazureblobstorage.blob.core.windows.net',
    CREDENTIAL = MyAzureBlobStorageCredential
);

Führen Sie anschließend einen BULK INSERT-Befehl mit der Option DATA_SOURCE aus.

BULK INSERT Product
FROM 'data/product.dat'
WITH ( DATA_SOURCE = 'MyAzureBlobStorage');

Analysieren von JSON-Dokumenten in Zeilen und Spalten

Statt die gesamte JSON-Datei als einzelnen Wert zu lesen, sollten Sie sie analysieren und die Bücher in der Tabelle sowie die Eigenschaften in Reihen und Zeilen zurückgeben.

Beispiel 1

Im einfachsten Beispiel können Sie einfach die gesamte Liste aus der Datei laden.

SELECT value
FROM OPENROWSET(BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn);

Beim vorangehenden OPENROWSET wird aus der Datei ein einzelner Textwert gelesen. OPENROWSET gibt den Wert als BulkColumn zurück und übergibt es weiter an die Funktion OPENJSON. OPENJSON durchläuft das Array von JSON-Objekten im BulkColumn-Array und gibt in jeder Zeile ein Buch zurück. Jede Zeile wird, wie nachfolgend gezeigt, als JSON formatiert.

{"id":"978-0641723445", "cat":["book","hardcover"], "name":"The Lightning Thief", ... }
{"id":"978-1423103349", "cat":["book","paperback"], "name":"The Sea of Monsters", ... }
{"id":"978-1857995879", "cat":["book","paperback"], "name":"Sophie's World : The Greek", ... }
{"id":"978-1933988177", "cat":["book","paperback"], "name":"Lucene in Action, Second", ... }

Beispiel 2

Die OPENJSON-Funktion kann den JSON-Inhalt analysieren und ihn in eine Tabelle oder ein Resultset transformieren. Im folgenden Beispiel wird der Inhalt geladen, die geladene JSON-Datei analysiert und die fünf Felder als Spalten zurückgegeben:

SELECT book.*
FROM OPENROWSET(BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn) WITH (
    id NVARCHAR(100),
    name NVARCHAR(100),
    price FLOAT,
    pages_i INT,
    author NVARCHAR(100)
) AS book;

In diesem Beispiel liest OPENROWSET(BULK) den Inhalt der Datei und übergibt den Inhalt mit einem definierten Schema für die Ausgabe an die OPENJSON-Funktion. OPENJSON vergleicht Eigenschaften in JSON-Objekten mithilfe der Spaltennamen. Die Eigenschaft price wird z.B. als price-Spalte zurückgegeben und in den float-Datentyp konvertiert. Dies sind die Ergebnisse:

Kennung Name Preis Seiten_i Autor
978-0641723445 The Lightning Thief (Diebe im Olymp) 12,5 384 Rick Riordan
978-1423103349 The Sea of Monsters (Im Bann des Zyklopen) 6.49 304 Rick Riordan
978-1857995879 Sophie’s World : The Greek Philosophers (Sofies Welt: Roman über die Geschichte der Philosophie) 3.07 64 Jostein Gaarder
978-1933988177 Lucene in Action, Zweite Auflage (nur englisch) 30.5 475 Michael McCandless

Jetzt können Sie die Tabelle an den Benutzer zurückgeben oder die Daten in eine andere Tabelle laden.

Zugehöriger Inhalt