Importieren von JSON-Dokumenten in SQL Server

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-DatenbankAzure 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.

Berechtigungen

Auf Instanzebene erfordert dieses Feature die Mitgliedschaft der Festen Serverrolle "Bulkadmin" oder "BULK OPERATIONS"-Berechtigungen.At the instance level, this feature requires membership of the bulkadmin fixed server role, or ADMINISTER BULK OPERATIONS permissions.

Für die Datenbankebene erfordert dieses Feature DIE BERECHTIGUNGEN "ADMINISTER DATABASE BULK OPERATIONS".

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 Netzwerk lesen kann, wenn SQL Server Lesezugriff auf diesen Speicherort hat. 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 sehen 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 sie in BulkColumn.

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 OPENROWSET(BULK) wie oben beschrieben JSON-Dateien von anderen Dateispeicherorten lesen, 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 kann diesen Port blockieren. Wenn sie im folgenden Schritt einen DNS-Fehler (Fehler 53) erhalten, haben Sie den Port 445 nicht geöffnet, oder Ihr ISP 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 und Azure SQL

Sie können Dateien direkt in Azure SQL-Datenbank aus Azure Blob Storage mit dem T-SQL BULK INSERT-Befehl oder der OPENROWSET Funktion 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. Im folgenden Beispiel wird eine JSON-Datei mit einer Liste von Büchern von dieser Website verwendet.

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

Im vorherigen OPENROWSET Abschnitt wird ein einzelner Textwert aus der Datei gelesen. OPENROWSET gibt den Wert als BulkColumn zurück und übergibt BulkColumn an die OPENJSON Funktion. OPENJSON durchläuft das Array von JSON-Objekten im BulkColumn-Array und gibt ein Buch in jeder Zeile 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 in eine Tabelle oder ein Resultset umwandeln. 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 OPENROWSET(BULK) wird der Inhalt der Datei gelesen und dieser Inhalt mit einem definierten Schema für die Ausgabe an die OPENJSON Funktion übergeben. OPENJSON gleicht Eigenschaften in den JSON-Objekten mithilfe von Spaltennamen ab. Die Eigenschaft price wird z.B. als price-Spalte zurückgegeben und in den float-Datentyp konvertiert. Dies sind die Ergebnisse:

Id 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.

Siehe auch