Importare documenti JSON in SQL ServerImport JSON documents into SQL Server

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2016)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Questo argomento descrive come importare file JSON in SQL Server.This topic describes how to import JSON files into SQL Server. Attualmente esistono moltissimi documenti JSON archiviati in file.Currently there are lots of JSON documents stored in files. Le applicazioni registrano informazioni in file JSON, i sensori generano informazioni archiviate in file JSON e così via.Applications log information in JSON files, sensors generate information that's stored in JSON files, and so forth. È importante essere in grado di leggere i dati JSON archiviati in file, caricare i dati in SQL Server e quindi analizzarli.It's important to be able to read the JSON data stored in files, load the data into SQL Server, and analyze it.

Importare un documento JSON in una singola colonnaImport a JSON document into a single column

OPENROWSET(BULK) è una funzione con valori di tabella che consente di leggere i dati da qualsiasi file nell'unità locale o in rete, se SQL Server ha accesso in lettura a tale percorso.OPENROWSET(BULK) is a table-valued function that can read data from any file on the local drive or network, if SQL Server has read access to that location. Restituisce una tabella con una sola colonna con il contenuto del file.It returns a table with a single column that contains the contents of the file. Sono disponibili diverse opzioni che è possibile usare con la funzione OPENROWSET(BULK), ad esempio i separatori.There are various options that you can use with the OPENROWSET(BULK) function, such as separators. Nel caso più semplice, tuttavia, è possibile limitarsi a caricare l'intero contenuto di un file come un valore di testo,But in the simplest case, you can just load the entire contents of a file as a text value. Questo singolo valore di grandi dimensioni è noto come singolo oggetto CLOB (Character Large Object) o SINGLE_CLOB.(This single large value is known as a single character large object, or SINGLE_CLOB.)

Di seguito è riportato un esempio della funzione OPENROWSET(BULK) che legge il contenuto di un file JSON e lo restituisce all'utente come singolo valore:Here's an example of the OPENROWSET(BULK) function that reads the contents of a JSON file and returns it to the user as a single value:

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

OPENJSON(BULK) legge il contenuto del file e lo restituisce in BulkColumn.OPENJSON(BULK) reads the content of the file and returns it in BulkColumn.

È anche possibile caricare il contenuto del file in una variabile locale o in una tabella, come illustrato nell'esempio seguente:You can also load the contents of the file into a local variable or into a table, as shown in the following example:

-- Load file contents into a variable
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

Dopo aver caricato il contenuto del file JSON è possibile salvare il testo JSON in una tabella.After loading the contents of the JSON file, you can save the JSON text in a table.

Importare più documenti JSONImport multiple JSON documents

È possibile usare lo stesso approccio per caricare un set di file JSON dal file system in una variabile locale, un file alla volta.You can use the same approach to load a set of JSON files from the file system into a local variable one at a time. Si supponga che i file siano denominati book<index>.json.Assume that the files are named book<index>.json.

DECLARE @i INT = 1
DECLARE @json AS NVARCHAR(MAX)

WHILE(@i < 10)
BEGIN
    SET @file = 'C:\JSON\Books\book' + cast(@i AS VARCHAR(5)) + '.json';
    SELECT @json = BulkColumn FROM OPENROWSET (BULK (@file), SINGLE_CLOB) AS j
    SELECT * FROM OPENJSON(@json) AS json
    -- Optionally, save the JSON text in a table.
    SET @i = @i + 1 ;
END

Importare documenti JSON dall'archiviazione file di AzureImport JSON documents from Azure File Storage

È anche possibile usare OPENROWSET(BULK) come descritto in precedenza per leggere i file JSON da altri percorsi di file a cui SQL Server è in grado di accedere.You can also use OPENROWSET(BULK) as described above to read JSON files from other file locations that SQL Server can access. L'archiviazione file di Azure, ad esempio, supporta il protocollo SMB.For example, Azure File Storage supports the SMB protocol. Di conseguenza è possibile mappare un'unità virtuale locale alla condivisione di archiviazione file di Azure tramite la procedura seguente:As a result you can map a local virtual drive to the Azure File storage share using the following procedure:

  1. Creare un account di archiviazione di file (ad esempio, mystorage), una condivisione file (ad esempio, sharejson) e una cartella nell'archiviazione file di Azure tramite il portale di Azure o Azure PowerShell.Create a file storage account (for example, mystorage), a file share (for example, sharejson), and a folder in Azure File Storage by using the Azure portal or Azure PowerShell.
  2. Caricare alcuni file JSON nella condivisione di archiviazione di file.Upload some JSON files to the file storage share.
  3. Creare una regola del firewall in uscita in Windows Firewall nel computer per consentire la porta 445.Create an outbound firewall rule in Windows Firewall on your computer that allows port 445. Si noti che il provider di servizi Internet potrebbe bloccare questa porta.Note that your Internet service provider may block this port. Se si verifica un errore DNS (errore 53) nel passaggio seguente, significa che la porta 445 non è stata aperta o è bloccata dall'ISP.If you get a DNS error (error 53) in the following step, then you have not opened port 445, or your ISP is blocking it.
  4. Montare la condivisione dell'archiviazione file di Azure come unità locale (ad esempio T:).Mount the Azure File Storage share as a local drive (for example T:).

    Questa è la sintassi del comando:Here is the command syntax:

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

    Di seguito è riportato un esempio che assegna la lettera di unità locale T: alla condivisione dell'archiviazione file di Azure:Here's an example that assigns local drive letter T: to the Azure File Storage share:

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

    La chiave dell'account di archiviazione e la chiave di accesso all'account di archiviazione primario o secondario sono disponibili nella sezione Chiavi in Impostazioni nel portale di Azure.You can find the storage account key and the primary or secondary storage account access key in the Keys section of Settings in the Azure portal.

  5. È ora possibile accedere ai file JSON dalla condivisione dell'archiviazione file di Azure usando l'unità mappata, come illustrato nell'esempio seguente:Now you can access your JSON files from the Azure File Storage share by using the mapped drive, as shown in the following example:

    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
    

Per altre informazioni sull'archiviazione file di Azure, vedere Archiviazione file.For more info about Azure File Storage, see File storage.

Importare documenti JSON da Archiviazione BLOB di AzureImport JSON documents from Azure Blob Storage

È possibile caricare i file direttamente nel database SQL di Azure da Archiviazione BLOB di Azure con il comando T-SQL BULK INSERT o la funzione OPENROWSET.You can load files directly into Azure SQL Database from Azure Blob Storage with the T-SQL BULK INSERT command or the OPENROWSET function.

Per prima cosa creare un'origine dati esterna, come illustrato nell'esempio seguente.First, create an external data source, as shown in the following example.

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

Eseguire poi un comando BULK INSERT con l'opzione DATA_SOURCE.Next, run a BULK INSERT command with the DATA_SOURCE option.

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

Per altre informazioni e un esempio che usa OPENROWSET, vedere il post di blog sul caricamento di file da Archiviazione BLOB di Azure nel database SQL di Azure.For more info and an example that uses OPENROWSET, see Loading files from Azure Blob Storage into Azure SQL Database.

Analizzare i documenti JSON come righe e colonneParse JSON documents into rows and columns

Anziché leggere un intero file JSON come singolo valore, può essere utile analizzarlo e restituire i libri nel file e le relative proprietà in righe e colonne.Instead of reading an entire JSON file as a single value, you may want to parse it and return the books in the file and their properties in rows and columns. L'esempio seguente usa un file JSON di questo sito contenente un elenco di libri.The following example uses a JSON file from this site containing a list of books.

Esempio 1Example 1

Nell'esempio più semplice, è possibile caricare semplicemente l'intero elenco dal file.In the simplest example, you can just load the entire list from the file.

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

Esempio 2Example 2

OPENROWSET legge un singolo valore di testo dal file, lo restituisce come BulkColumn e lo passa alla funzione OPENJSON.OPENROWSET reads a single text value from the file, returns it as a BulkColumn, and passes it to the OPENJSON function. OPENJSON esegue l'iterazione della matrice di oggetti JSON nella matrice BulkColumn e restituisce un libro, formattato come JSON, in ogni riga:OPENJSON iterates through the array of JSON objects in the BulkColumn array and returns one book in each row, formatted as JSON:

{"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 … 

Esempio 3Example 3

La funzione OPENJSON può analizzare il contenuto JSON e trasformarlo in una tabella o un set di risultati.The OPENJSON function can parse the JSON content and transform it into a table or a result set. L'esempio seguente carica il contenuto, analizza i dati JSON caricati e restituisce i cinque campi come colonne:The following example loads the content, parses the loaded JSON, and returns the five fields as columns:

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 questo esempio, OPENROWSET(BULK) legge il contenuto del file e passa il contenuto alla funzione OPENJSON con uno schema definito per l'output.In this example, OPENROWSET(BULK) reads the content of the file and passes that content to the OPENJSON function with a defined schema for the output. OPENJSON stabilisce le corrispondenze per le proprietà negli oggetti JSON usando i nomi di colonna.OPENJSON matches properties in the JSON objects by using column names. Ad esempio, la proprietà price viene restituita come una colonna price e convertita nel tipo di dati float.For example, the price property is returned as a price column and converted to the float data type. Ecco i risultati:Here are the results:

IDId NomeName priceprice pages_ipages_i AutoreAuthor
978-0641723445978-0641723445 The Lightning ThiefThe Lightning Thief 12,512.5 384384 Rick RiordanRick Riordan
978-1423103349978-1423103349 The Sea of MonstersThe Sea of Monsters 6,496.49 304304 Rick RiordanRick Riordan
978-1857995879978-1857995879 Sophie’s World : The Greek PhilosophersSophie’s World : The Greek Philosophers 3,073.07 6464 Jostein GaarderJostein Gaarder
978-1933988177978-1933988177 Lucene in Action, Second EditionLucene in Action, Second Edition 30,530.5 475475 Michael McCandlessMichael McCandless

A questo punto è possibile restituire questa tabella all'utente o caricare i dati in un'altra tabella.Now you can return this table to the user, or load the data into another table.

Altre informazioni sul supporto JSON integrato in SQL ServerLearn more about the built-in JSON support in SQL Server

Per soluzioni specifiche, casi d'uso e indicazioni, vedere i post del blog sul supporto JSON integrato in SQL Server e nel database SQL di Azure redatti da Jovan Popovic, Microsoft Program Manager.For lots of specific solutions, use cases, and recommendations, see the blog posts about the built-in JSON support in SQL Server and in Azure SQL Database by Microsoft Program Manager Jovan Popovic.

Vedere ancheSee Also

Convertire dati JSON in righe e colonne con la funzione OPENJSONConvert JSON Data to Rows and Columns with OPENJSON