Dela via

Importera JSON-dokument till SQL Server

  • Artikel

gäller för: SQL Server 2016 (13.x) och senare Azure SQL DatabaseAzure SQL Managed Instance

Den här artikeln beskriver hur du importerar JSON-filer till SQL Server. JSON-dokument lagrar många typer av data, till exempel programloggar, sensordata och så vidare. Det är viktigt att kunna läsa JSON-data som lagras i filer, läsa in data i SQL Server och analysera dem.

Exemplen i den här artikeln använder en JSON-fil från ett GitHub-exempel som innehåller en lista med böcker.

Behörigheter

På instansnivå kräver den här funktionen medlemskap i bulkadmin fast serverroll eller ADMINISTER BULK OPERATIONS behörigheter.

För databasnivån kräver den här funktionen ADMINISTER DATABASE BULK OPERATIONS behörigheter.

Åtkomst till Azure Blob Storage kräver läs- och skrivåtkomst.

Importera ett JSON-dokument till en enda kolumn

OPENROWSET(BULK) är en tabellvärdesfunktion som kan läsa data från valfri fil på den lokala enheten eller nätverket, om SQL Server har läsbehörighet till den platsen. Den returnerar en tabell med en enda kolumn som innehåller innehållet i filen. Det finns olika alternativ som du kan använda med funktionen OPENROWSET(BULK), till exempel avgränsare. Men i det enklaste fallet kan du bara läsa in hela innehållet i en fil som ett textvärde. (Det här enkla stora värdet kallas för ett enkelteckensstort objekt, eller SINGLE_CLOB.)

Här är ett exempel på funktionen OPENROWSET(BULK) som läser innehållet i en JSON-fil och returnerar den till användaren som ett enda värde:

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

OPENJSON(BULK) läser innehållet i filen och returnerar den i BulkColumn.

Du kan också läsa in innehållet i filen i en lokal variabel eller till en tabell, som du ser i följande exempel:

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

När du har läst in innehållet i JSON-filen kan du spara JSON-texten i en tabell.

Importera JSON-dokument från Azure File Storage

Du kan också använda OPENROWSET(BULK) enligt beskrivningen tidigare för att läsa JSON-filer från andra filplatser som SQL Server kan komma åt. Azure File Storage stöder till exempel SMB-protokollet. Därför kan du mappa en lokal virtuell enhet till Azure File Storage-resursen med hjälp av följande procedur:

  1. Skapa ett fillagringskonto (till exempel mystorage), en filresurs (till exempel sharejson) och en mapp i Azure File Storage med hjälp av Azure-portalen eller Azure PowerShell.

  2. Ladda upp några JSON-filer till fildelningslagret.

  3. Skapa en regel för utgående brandvägg i Windows-brandväggen på datorn som tillåter port 445. Internetleverantören kan blockera den här porten. Om du får ett DNS-fel (fel 53) i följande steg är port 445 inte öppen eller så blockerar din ISP det.

  4. Montera Azure File Storage-resursen som en lokal enhet (till exempel T:).

    Här är kommandosyntaxen:

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

    Här är ett exempel som tilldelar den lokala enhetsbeteckningen T: till Azure File Storage-resursen:

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

    Du hittar lagringskontonyckeln och åtkomstnyckeln för det primära eller sekundära lagringskontot i avsnittet Nycklar i Inställningar i Azure-portalen.

  5. Nu kan du komma åt dina JSON-filer från Azure File Storage-resursen med hjälp av den mappade enheten, som du ser i följande exempel:

    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

Mer information om Azure File Storage finns i File Storage.

Importera JSON-dokument från Azure Blob Storage

gäller för: SQL Server 2017 (14.x) och senare versioner och Azure SQL

Du kan läsa in filer direkt i Azure SQL Database från Azure Blob Storage med kommandot T-SQL BULK INSERT eller funktionen OPENROWSET.

Skapa först en extern datakälla, som du ser i följande exempel.

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

Kör sedan ett BULK INSERT-kommando med alternativet DATA_SOURCE.

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

Parsa JSON-dokument i rader och kolumner

I stället för att läsa en hel JSON-fil som ett enda värde kanske du vill parsa den och returnera böckerna i filen och deras egenskaper i rader och kolumner.

Exempel 1

I det enklaste exemplet kan du bara läsa in hela listan från filen.

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

Föregående OPENROWSET läser ett enda textvärde från filen. OPENROWSET returnerar värdet som en BulkColumn och skickar BulkColumn till funktionen OPENJSON. OPENJSON itererar genom matrisen med JSON-objekt i BulkColumn-matrisen och returnerar en bok på varje rad. Varje rad formateras som JSON, som visas härnäst.

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

Exempel 2

Funktionen OPENJSON kan parsa JSON-innehållet och omvandla det till en tabell eller en resultatuppsättning. I följande exempel läses innehållet in, den inlästa JSON tolkas och de fem fälten returneras som kolumner:

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;

I det här exemplet läser OPENROWSET(BULK) innehållet i filen och skickar innehållet till funktionen OPENJSON med ett definierat schema för utdata. OPENJSON matchar egenskaper i JSON-objekten med hjälp av kolumnnamn. Egenskapen price returneras till exempel som en price kolumn och konverteras till flyttalsdatatypen. Här är resultatet:

Id Namn pris pages_i Författare
978-0641723445 Blixttjuven 12.5 384 Rick Riordan
978-1423103349 Monsterhavet 6.49 304 Rick Riordan
978-1857995879 Sophies värld : De grekiska filosoferna 3.07 64 Jostein Gaarder
978-1933988177 Lucene in Action, Second Edition 30.5 475 Michael McCandless

Nu kan du returnera den här tabellen till användaren eller läsa in data i en annan tabell.

Relaterat innehåll