JSON-dokumentumok importálása az SQL Serverbe

Vonatkozik a következőkre: SQL Server 2016 (13.x) és későbbi verziók Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Ez a cikk bemutatja, hogyan importálhat JSON-fájlokat az SQL Serverbe. A JSON-dokumentumok számos adattípust tárolnak, például alkalmazásnaplókat, érzékelőadatokat stb. Fontos, hogy képes legyen olvasni a fájlokban tárolt JSON-adatokat, betölteni az adatokat az SQL Serverbe, és elemezni.

A cikkben szereplő példák egy GitHub-minta JSON-fájlját használják, amely a könyvek listáját tartalmazza.

Permissions

A példányszinten ez a funkció a bulkadmin rögzített kiszolgálói szerepkör tagságát, vagy ADMINISTER BULK OPERATIONS jogosultságokat igényli.

Az adatbázis szintjén ez a funkció ADMINISTER DATABASE BULK OPERATIONS engedélyeket igényel.

Az Azure Blob Storage eléréséhez olvasási-írási hozzáférés szükséges.

JSON-dokumentum importálása egyetlen oszlopba

OPENROWSET(BULK) egy táblaértékű függvény, amely képes adatokat olvasni a helyi meghajtón vagy hálózaton található bármely fájlból, ha az SQL Server olvasási hozzáféréssel rendelkezik ehhez a helyhez. Egyetlen oszlopból álló táblát ad vissza, amely a fájl tartalmát tartalmazza. A OPENROWSET(BULK) függvénnyel különböző lehetőségeket használhat, például elválasztókat. A legegyszerűbb esetben azonban egyszerűen betöltheti egy fájl teljes tartalmát szöveges értékként. (Ez az egyetlen nagy érték egyetlen karakterből álló nagy objektumként vagy SINGLE_CLOB néven ismert.)

Íme egy példa az OPENROWSET(BULK) függvényre, amely beolvassa egy JSON-fájl tartalmát, és egyetlen értékként adja vissza a felhasználónak:

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

OPENJSON(BULK) beolvassa a fájl tartalmát, és visszaadja azt BulkColumn-ben.

A fájl tartalmát egy helyi változóba vagy egy táblába is betöltheti, ahogyan az a következő példában látható:

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

A JSON-fájl tartalmának betöltése után mentheti a JSON-szöveget egy táblába.

JSON-dokumentumok importálása az Azure File Storage-ból

A korábban ismertetett OPENROWSET(BULK) is használhatja JSON-fájlok olvasásához más olyan fájlhelyekről, amelyekhez az SQL Server hozzáférhet. Az Azure File Storage például támogatja az SMB protokollt. Ennek eredményeképpen a következő eljárással rendelhet hozzá egy helyi virtuális meghajtót az Azure File Storage-megosztáshoz:

1. Hozzon létre egy fájltárfiókot (például mystorage), egy fájlmegosztást (például sharejson) és egy mappát az Azure File Storage-ban az Azure Portal vagy az Azure PowerShell használatával.

2. Töltsön fel néhány JSON-fájlt a fájltároló-megosztásba.

3. Hozzon létre egy kimenő tűzfalszabályt a windowsos tűzfalban a számítógépen, amely engedélyezi a 445-ös portot. Az internetszolgáltató letilthatja ezt a portot. Ha a következő lépésben DNS-hiba (53-os hiba) jelenik meg, akkor a 445-ös port nem nyílik meg, vagy az internetszolgáltató blokkolja azt.

4. Csatlakoztassa az Azure File Storage-megosztást helyi meghajtóként (például T:).

   A parancs szintaxisa a következő:

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

   Íme egy példa, amely T: helyi meghajtóbetűjelet rendel az Azure File Storage-megosztáshoz:

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

   A tárfiókkulcsot és az elsődleges vagy másodlagos tárfiók hozzáférési kulcsát az Azure Portal Beállítások kulcsok szakaszában találja.

5. A JSON-fájlokat most az Azure File Storage-megosztásból érheti el a leképezett meghajtóval, ahogyan az alábbi példában látható:

   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
   

További információ az Azure File Storage-ról: Fájltároló.

JSON-dokumentumok importálása az Azure Blob Storage-ból

A következőkre vonatkozik: SQL Server 2017 (14.x) és újabb verziók, valamint az Azure SQL

A T-SQL BULK INSERT paranccsal vagy a OPENROWSET függvénnyel közvetlenül betölthet fájlokat az Azure SQL Database-be az Azure Blob Storage-ból.

Először hozzon létre egy külső adatforrást az alábbi példában látható módon.

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

Ezután futtasson egy BULK INSERT parancsot a DATA_SOURCE beállítással.

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

JSON-dokumentumok elemzése sorokba és oszlopokba

Ahelyett, hogy egy teljes JSON-fájlt olvasna egyetlen értékként, érdemes lehet elemezni, és visszaadni a fájlban lévő könyveket és azok tulajdonságait sorokban és oszlopokban.

1. példa

A legegyszerűbb példában egyszerűen betöltheti a teljes listát a fájlból.

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

Az előző OPENROWSET egyetlen szöveges értéket olvas be a fájlból. OPENROWSET az értéket BulkColumnként adja vissza, és átadja a BulkColumn értéket a OPENJSON függvénynek. OPENJSON végigfut a BulkColumn tömb JSON-objektumainak tömbén, és minden sorban egy könyvet ad vissza. Minden sor JSON formátumban van formázva, a következő képen látható.

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

2. példa

A OPENJSON függvény elemezheti a JSON-tartalmat, és táblázattá vagy eredményhalmazsá alakíthatja azt. Az alábbi példa betölti a tartalmat, elemzi a betöltött JSON-t, és oszlopként adja vissza az öt mezőt:

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;

Ebben a példában OPENROWSET(BULK) beolvassa a fájl tartalmát, és átadja a tartalmat a OPENJSON függvénynek a kimenethez megadott sémával. OPENJSON oszlopnevek használatával egyezik a JSON-objektumok tulajdonságaival. A price tulajdonság például price oszlopként lesz visszaadva, és lebegőpontos adattípussá lesz konvertálva. Az eredmények a következők:

Id	Name	price	pages_i	Author
978-0641723445	A Villámtolvaj	12.5	384	Rick Riordan
978-1423103349	A szörnyek tengere	6.49	304	Rick Riordan
978-1857995879	Sophie világa : A görög filozófusok	3.07	64	Jostein Gaarder
978-1933988177	Lucene in Action, Second Edition	30.5	475	Michael McCandless

Most visszaadhatja ezt a táblát a felhasználónak, vagy betöltheti az adatokat egy másik táblába.

Kapcsolódó tartalom

• A JSON a NoSQL és a relációs világok közötti híd
• JSON-adatok elemzése és átalakítása OPENJSON használatával