JSON-documenten importeren in SQL Server

Artikel
2025-03-05

van toepassing op: SQL Server 2016 (13.x) en hoger Azure SQL Database Azure SQL Managed Instance

In dit artikel wordt beschreven hoe u JSON-bestanden importeert in SQL Server. JSON-documenten slaan veel soorten gegevens op, bijvoorbeeld toepassingslogboeken, sensorgegevens, enzovoort. Het is belangrijk dat u de JSON-gegevens kunt lezen die zijn opgeslagen in bestanden, de gegevens in SQL Server kunt laden en analyseren.

In de voorbeelden in dit artikel wordt een JSON-bestand uit een GitHub-sample met een lijst boeken gebruikt.

Machtigingen

Op instantieniveau vereist deze functie lidmaatschap van de bulkadmin vaste serverrol of ADMINISTER BULK OPERATIONS machtigingen.

Voor het databaseniveau is voor deze functie ADMINISTER DATABASE BULK OPERATIONS machtigingen vereist.

Voor toegang tot Azure Blob Storage is lees-/schrijftoegang vereist.

Een JSON-document importeren in één kolom

OPENROWSET(BULK) is een tabelwaardefunctie waarmee gegevens uit elk bestand op het lokale station of netwerk kunnen worden gelezen als SQL Server leestoegang tot die locatie heeft. Het retourneert een tabel met één kolom die de inhoud van het bestand bevat. Er zijn verschillende opties die u kunt gebruiken met de functie OPENROWSET(BULK), zoals scheidingstekens. Maar in het eenvoudigste geval kunt u alleen de volledige inhoud van een bestand als tekstwaarde laden. (Deze enkele grote waarde wordt een groot object met één teken of SINGLE_CLOB genoemd.)

Hier volgt een voorbeeld van de functie OPENROWSET(BULK) die de inhoud van een JSON-bestand leest en retourneert aan de gebruiker als één waarde:

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

OPENJSON(BULK) de inhoud van het bestand leest en retourneert het in BulkColumn.

U kunt de inhoud van het bestand ook laden in een lokale variabele of in een tabel, zoals wordt weergegeven in het volgende voorbeeld:

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

Nadat u de inhoud van het JSON-bestand hebt geladen, kunt u de JSON-tekst opslaan in een tabel.

JSON-documenten importeren uit Azure File Storage

U kunt ook OPENROWSET(BULK) gebruiken zoals eerder beschreven om JSON-bestanden te lezen van andere bestandslocaties waartoe SQL Server toegang heeft. Azure File Storage ondersteunt bijvoorbeeld het SMB-protocol. Als gevolg hiervan kunt u een lokaal virtueel station toewijzen aan de Azure File Storage-share met behulp van de volgende procedure:

Maak een bestandsopslagaccount (bijvoorbeeld mystorage), een bestandsshare (bijvoorbeeld sharejson) en een map in Azure File Storage met behulp van Azure Portal of Azure PowerShell.
Upload enkele JSON-bestanden naar de bestandsopslagshare.
Maak een uitgaande firewallregel in Windows Firewall op uw computer die poort 445 toestaat. Uw internetprovider kan deze poort blokkeren. Als u in de volgende stap een DNS-fout (fout 53) krijgt, is poort 445 niet geopend of blokkeert uw internetprovider deze.
Koppel de Azure File Storage-share als een lokaal station (bijvoorbeeld T:).

Dit is de syntaxis van de opdracht:
```
net use [drive letter] \\[storage name].file.core.windows.net\[share name] /u:[storage account name] [storage account access key]
```
Hier is een voorbeeld waarin de lokale stationsletter T: aan de Azure File Share wordt toegewezen.
```
net use t: \\mystorage.file.core.windows.net\sharejson /u:myaccount hb5qy6eXLqIdBj0LvGMHdrTiygkjhHDvWjUZg3Gu7bubKLg==
```
U vindt de sleutel van het opslagaccount en de toegangssleutel voor het primaire of secundaire opslagaccount in de sectie Sleutels van Instellingen in Azure Portal.

U hebt nu toegang tot uw JSON-bestanden vanuit de Azure File Storage-share met behulp van het toegewezen station, zoals wordt weergegeven in het volgende voorbeeld:

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

Zie File Storagevoor meer informatie over Azure File Storage.

JSON-documenten importeren uit Azure Blob Storage

van toepassing op: SQL Server 2017 (14.x) en latere versies, en Azure SQL

U kunt bestanden rechtstreeks vanuit Azure Blob Storage laden in Azure SQL Database met de opdracht T-SQL BULK INSERT of de OPENROWSET-functie.

Maak eerst een externe gegevensbron, zoals wordt weergegeven in het volgende voorbeeld.

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

Voer vervolgens een opdracht BULK INSERT uit met de optie DATA_SOURCE.

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

JSON-documenten parseren in rijen en kolommen

In plaats van een heel JSON-bestand als één waarde te lezen, wilt u het misschien parseren en de boeken in het bestand en de bijbehorende eigenschappen in rijen en kolommen retourneren.

Voorbeeld 1

In het eenvoudigste voorbeeld kunt u alleen de hele lijst uit het bestand laden.

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

De voorgaande code OPENROWSET leest een enkele tekstwaarde uit het bestand. OPENROWSET retourneert de waarde als bulkcolumn en geeft BulkColumn door aan de functie OPENJSON. OPENJSON doorloopt de matrix van JSON-objecten in de BulkColumn-matrix en retourneert één boek in elke rij. Elke rij is opgemaakt als JSON, weergegeven volgende.

{"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", ... }

Voorbeeld 2

De functie OPENJSON kan de JSON-inhoud parseren en transformeren in een tabel of een resultatenset. In het volgende voorbeeld wordt de inhoud geladen, wordt de geladen JSON geparseerd en worden de vijf velden als kolommen geretourneerd:

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 dit voorbeeld leest OPENROWSET(BULK) de inhoud van het bestand en geeft deze inhoud door aan de OPENJSON-functie met een gedefinieerd schema voor de uitvoer. OPENJSON komt overeen met eigenschappen in de JSON-objecten met behulp van kolomnamen. De eigenschap price wordt bijvoorbeeld geretourneerd als een price kolom en geconverteerd naar het gegevenstype float. Dit zijn de resultaten:

Identiteitsbewijs	Naam	prijs	pages_i	Auteur
978-0641723445	De Bliksemdief	12.5	384	Rick Riordan
978-1423103349	De Zee van Monsters	6.49	304	Rick Riordan
978-1857995879	Sophie's Wereld : De Griekse filosofen	3.07	64	Jostein Gaarder
978-1933988177	Lucene in Actie, Tweede editie	30.5	475	Michael McCandless

U kunt deze tabel nu naar de gebruiker retourneren of de gegevens in een andere tabel laden.

JSON als een brug tussen NoSQL en relationele werelden
JSON-gegevens parseren en transformeren met OPENJSON-

Delen via