Importer des documents JSON dans SQL Server

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL Database Azure SQL Managed Instance

Cet article décrit comment importer des fichiers JSON dans SQL Server. Les documents JSON stockent de nombreux types de données, par exemple les journaux des applications, les données de capteur, etc. Il est important de pouvoir lire les données JSON stockées dans des fichiers, charger les données dans SQL Server et les analyser.

Les exemples de cet article utilisent un fichier JSON à partir d’un exemple GitHub contenant une liste de livres.

autorisations

Au niveau de l’instance, cette fonctionnalité nécessite d’appartenir au rôle serveur fixe bulkadmin ou d’avoir des autorisations ADMINISTER BULK OPERATIONS.

Pour le niveau de la base de données, cette fonctionnalité nécessite des autorisations ADMINISTER DATABASE BULK OPERATIONS.

L’accès à Stockage Blob Azure nécessite un accès en lecture/écriture.

Importer un document JSON dans une seule colonne

OPENROWSET(BULK) est une fonction table qui peut lire des données à partir de n’importe quel fichier sur le réseau ou le lecteur local, si SQL Server dispose d’un accès en lecture à cet emplacement. Elle retourne une table avec une colonne unique où figure le contenu du fichier. Vous pouvez utiliser différentes options avec la fonction OPENROWSET(BULK), comme des séparateurs. Mais dans le cas le plus simple, vous pouvez charger tout le contenu d’un fichier en tant que valeur de texte, (Cette valeur unique élevée est appelée objet volumineux à caractère unique, ou SINGLE_CLOB.)

Voici un exemple de fonction OPENROWSET(BULK) qui lit le contenu d’un fichier JSON et le retourne à l’utilisateur en tant que valeur unique :

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

OPENJSON(BULK) lit le contenu du fichier et le retourne dans BulkColumn.

Vous pouvez également charger le contenu du fichier dans une variable locale ou dans une table, comme indiqué dans l’exemple suivant :

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

Après avoir chargé le contenu du fichier JSON, vous pouvez enregistrer le texte JSON dans une table.

Importer des documents JSON à partir du Stockage de fichiers Azure

Vous pouvez également utiliser OPENROWSET(BULK) comme décrit précédemment pour lire les fichiers JSON à partir d’autres emplacements accessibles à SQL Server. Par exemple, le Stockage de fichiers Azure prend en charge le protocole SMB. Ainsi, vous pouvez mapper un lecteur virtuel local au partage de Stockage de fichiers Azure à l’aide de la procédure suivante :

1. Créez un compte de stockage de fichier (par exemple, mystorage), un partage de fichiers (par exemple, sharejson) et un dossier dans le Stockage de fichiers Azure à l’aide du portail Azure ou d’Azure PowerShell.

2. Chargez des fichiers JSON vers le partage de stockage de fichiers.

3. Créez sur votre ordinateur une règle de pare-feu sortante dans le Pare-feu Windows qui autorise l’utilisation du port 445. Il est possible que votre fournisseur de services Internet bloque ce port. Si vous obtenez une erreur DNS (erreur 53) à l’étape suivante, cela signifie que le port 445 n’est pas ouvert ou que votre fournisseur de services Internet le bloque.

4. Montez le partage Stockage Fichier Azure en tant que lecteur local (par exemple T:).

   Voici la syntaxe de commande :

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

   Voici un exemple qui affecte la lettre de lecteur local T: au partage Stockage Fichier Azure :

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

   Vous trouverez la clé de compte de stockage et la clé d’accès au compte de stockage primaire ou secondaire dans la section Clés des Paramètres dans le portail Azure.

5. Vous pouvez maintenant accéder à vos fichiers JSON à partir du partage Stockage Fichier Azure à l’aide du lecteur mappé, comme indiqué dans l’exemple suivant :

   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
   

Pour plus d’informations sur le Stockage de fichiers Azure, consultez Stockage de fichiers.

Importer des documents JSON à partir de Stockage Blob Azure

S’applique à : SQL Server 2017 (14.x) et versions ultérieures et Azure SQL Database

Vous pouvez charger des fichiers directement dans Azure SQL Database à partir de Stockage Blob Azure avec la commande T-SQL BULK INSERT ou la fonction OPENROWSET.

Commencez par créer une source de données externe, comme illustré dans l’exemple suivant.

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

Ensuite, exécutez une commande BULK INSERT avec l’option DATA_SOURCE.

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

Analyser des documents JSON dans des lignes et des colonnes

Au lieu de lire un fichier JSON entier en tant que valeur unique, vous pouvez l’analyser et retourner les livres figurant dans le fichier, ainsi que leurs propriétés, dans des lignes et des colonnes.

Exemple 1

Dans l’exemple le plus simple, vous pouvez charger la liste entière à partir du fichier.

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

La fonction OPENROWSET précédente lit une valeur de texte unique à partir du fichier. OPENROWSET retourne la valeur en tant que BulkColumn, puis passe BulkColumn à la fonction OPENJSON. OPENJSON itère au sein du tableau d’objets JSON dans le tableau BulkColumn et retourne un seul livre sur chaque ligne. Chaque ligne est au format JSON, comme indiqué ci-dessous.

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

Exemple 2

La fonction OPENJSON peut analyser le contenu JSON et le transformer en tableau ou en jeu de résultats. L’exemple suivant charge le contenu, analyse le JSON chargé et retourne les cinq champs sous forme de colonnes :

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;

Dans cet exemple, OPENROWSET(BULK) lit le contenu du fichier et transmet ce contenu à la fonction OPENJSON avec un schéma défini pour la sortie. OPENJSON établit une correspondance avec les propriétés dans les objets JSON en utilisant des noms de colonnes. Par exemple, la propriété price est retournée en tant que colonne price et convertie vers le type de données float. Voici les résultats :

Vous pouvez maintenant retourner cette à l’utilisateur, ou charger les données dans une autre table.

Contenu connexe

• JSON : un pont entre les mondes relationnel et NoSQL
• Analyser et transformer des données JSON avec OPENJSON