Importer des documents JSON dans SQL Server

  • Article

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure 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 d’application, 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.

Autorisations

Au niveau de l’instance, cette fonctionnalité nécessite l’appartenance au rôle serveur fixe bulkadmin ou les autorisations ADMINISTRATION BULK OPERATIONS.

Pour le niveau de la base de données, cette fonctionnalité nécessite des autorisations ADMINISTRATION 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 lecteur local ou le réseau, si SQL Server a un accès en lecture à cet emplacement. Elle retourne une table avec une colonne unique où figure le contenu du fichier. Il existe différentes options que vous pouvez utiliser avec la OPENROWSET(BULK) fonction, comme les 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 sous forme de 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 ci-dessus pour lire des fichiers JSON à partir d’autres emplacements de fichiers auxquels SQL Server peut accéder. 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. Votre fournisseur de services Internet peut bloquer ce port. Si vous recevez une erreur DNS (erreur 53) à l’étape suivante, vous n’avez pas ouvert le port 445 ou votre isp 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

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

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. L’exemple suivant utilise un fichier JSON provenant de ce site et contenant une liste de livres.

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

Le précédent OPENROWSET lit une seule valeur de texte à partir du fichier. OPENROWSET retourne la valeur en tant que BulkColumn et transmet BulkColumn à la OPENJSON fonction. OPENJSON effectue une itération dans le tableau d’objets JSON dans le tableau BulkColumn et retourne un livre dans 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 OPENJSON fonction peut analyser le contenu JSON et le transformer en une table ou un 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 OPENJSON fonction avec un schéma défini pour la sortie. OPENJSON correspond aux propriétés des objets JSON à l’aide de 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 :

Id Nom price pages_i Auteur
978-0641723445 The Lightning Thief 12.5 384 Rick Riordan
978-1423103349 The Sea of Monsters 6.49 304 Rick Riordan
978-1857995879 Sophie’s World : The Greek Philosophers 3.07 64 Jostein Gaarder
978-1933988177 Lucene in Action, Second Edition 30.5 475 Michael McCandless

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

Voir aussi