Sdílet prostřednictvím

Import dokumentů JSON do SQL Serveru

  • Článek

platí pro: SQL Server 2016 (13.x) a novější azure SQL Databaseazure SQL Managed Instance

Tento článek popisuje, jak importovat soubory JSON do SQL Serveru. Dokumenty JSON ukládají mnoho typů dat, například aplikační protokoly, data snímačů atd. Je důležité, abyste mohli číst data JSON uložená v souborech, načítat data do SQL Serveru a analyzovat je.

Příklady v tomto článku používají soubor JSON z ukázkového GitHubu obsahující seznam knih.

Dovolení

Na úrovni instance tato funkce vyžaduje členství bulkadmin pevnou roli serveru nebo ADMINISTER BULK OPERATIONS oprávnění.

Pro úroveň databáze tato funkce vyžaduje ADMINISTER DATABASE BULK OPERATIONS oprávnění.

Přístup ke službě Azure Blob Storage vyžaduje přístup pro čtení i zápis.

Import dokumentu JSON do jednoho sloupce

OPENROWSET(BULK) je funkce s hodnotou tabulky, která může číst data z libovolného souboru na místní jednotce nebo síti, pokud má SQL Server přístup ke čtení do daného umístění. Vrátí tabulku s jedním sloupcem, který obsahuje obsah souboru. S funkcí OPENROWSET(BULK) můžete použít různé možnosti, například oddělovače. V nejjednodušším případě ale stačí načíst celý obsah souboru jako textovou hodnotu. (Tato velká hodnota je známá jako jeden znakový velký objekt neboli "SINGLE_CLOB.")

Tady je příklad funkce OPENROWSET(BULK), která čte obsah souboru JSON a vrací ho uživateli jako jednu hodnotu:

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

OPENJSON(BULK) přečte obsah souboru a vrátí ho v BulkColumn.

Obsah souboru můžete také načíst do místní proměnné nebo do tabulky, jak je znázorněno v následujícím příkladu:

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

Po načtení obsahu souboru JSON můžete text JSON uložit do tabulky.

Import dokumentů JSON ze služby Azure File Storage

Můžete také použít OPENROWSET(BULK), jak je popsáno výše, ke čtení souborů JSON z jiných umístění souborů, ke kterým má SQL Server přístup. Azure File Storage například podporuje protokol SMB. V důsledku toho můžete pomocí následujícího postupu namapovat místní virtuální jednotku na sdílenou složku Azure File Storage:

  1. Vytvořte účet úložiště souborů (například mystorage), sdílenou složku (například sharejson) a složku ve službě Azure File Storage pomocí webu Azure Portal nebo Azure PowerShellu.

  2. Nahrajte některé soubory JSON do sdílené složky úložiště souborů.

  3. Na počítači vytvořte odchozí pravidlo brány Windows Firewall, které umožní přístup přes port 445. Tento port může blokovat váš poskytovatel internetových služeb. Pokud se v následujícím kroku zobrazí chyba DNS (chyba 53), port 445 není otevřený nebo ho váš poskytovatele internetových služeb blokuje.

  4. Připojte sdílenou složku Azure File Storage jako místní disk (například T:).

    Tady je syntaxe příkazu:

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

    Tady je příklad, který přiřadí písmeno místního disku T: sdílené složce Azure File Storage:

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

    Klíč účtu úložiště a primární nebo sekundární přístupový klíč účtu úložiště najdete v části Klíče nastavení na webu Azure Portal.

  5. Teď můžete přistupovat ke svým souborům JSON ze sdílené složky Azure File Storage pomocí namapované jednotky, jak je znázorněno v následujícím příkladu:

    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

Další informace o službě Azure File Storage najdete v tématu File Storage.

Import dokumentů JSON ze služby Azure Blob Storage

platí pro: SQL Server 2017 (14.x) a novější verze a Azure SQL

Soubory můžete načíst přímo do Služby Azure SQL Database ze služby Azure Blob Storage pomocí příkazu T-SQL BULK INSERT nebo funkce OPENROWSET.

Nejprve vytvořte externí zdroj dat, jak je znázorněno v následujícím příkladu.

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

Potom spusťte příkaz BULK INSERT s možností DATA_SOURCE.

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

Parsování dokumentů JSON do řádků a sloupců

Místo čtení celého souboru JSON jako jedné hodnoty ho můžete chtít analyzovat a vrátit knihy v souboru a jejich vlastnosti v řádcích a sloupcích.

Příklad 1

V nejjednodušším příkladu stačí načíst celý seznam ze souboru.

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

Předchozí OPENROWSET přečte jednu textovou hodnotu ze souboru. OPENROWSET vrátí hodnotu jako BulkColumn a předá bulkColumn funkci OPENJSON. OPENJSON iteruje polem objektů JSON v poli BulkColumn a vrátí jednu knihu v každém řádku. Každý řádek je formátovaný jako JSON, který je zobrazený dále.

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

Příklad 2

Funkce OPENJSON může analyzovat obsah JSON a transformovat ho do tabulky nebo sady výsledků. Následující příklad načte obsah, parsuje načtený JSON a vrátí pět polí jako sloupce:

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;

V tomto příkladu OPENROWSET(BULK) přečte obsah souboru a předá tento obsah funkci OPENJSON s definovaným schématem pro výstup. OPENJSON odpovídá vlastnostem v objektech JSON pomocí názvů sloupců. Například vlastnost price je vrácena jako price sloupec a převedena na datový typ float. Tady jsou výsledky:

Id Jméno cena pages_i Autor
978-0641723445 Zloděj blesku 12.5 384 Rick Riordan
978-1423103349 Moře monstra 6.49 304 Rick Riordan
978-1857995879 Sophie's World : Řecké filozofie 3.07 64 Jostein Gaarder
978-1933988177 Lucene v Akci, druhé vydání 30.5 475 Michael McCandless

Teď můžete tuto tabulku vrátit uživateli nebo načíst data do jiné tabulky.

Související obsah