Share via

Stocker des documents JSON dans SQL Server ou SQL Database

  • Article

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

Le moteur de base de données SQL fournit des fonctions JSON natives qui vous permettent d'analyser les documents JSON à l'aide du langage SQL standard. Vous pouvez stocker des documents JSON dans SQL Server ou SQL Database et interroger les données JSON comme dans une base de données NoSQL. Cet article décrit les options de stockage des documents JSON.

Format de stockage JSON

La première décision de conception du stockage concerne la manière de stocker des documents JSON dans les tables. Deux options sont disponibles :

  • Stockage LOB - Les documents JSON peuvent être stockés tels quels dans des colonnes avec le type de données JSON or NVARCHAR. Il s’agit de la meilleure façon de charger et d’ingérer rapidement les données, car la vitesse de chargement correspond à la vitesse de chargement des colonnes de chaînes. Cette approche peut entraîner une dégradation supplémentaire des performances au moment de la requête/analyse si l’indexation des valeurs JSON n’est pas effectuée, car les documents JSON bruts doivent être analysés pendant l’exécution des requêtes.

  • Stockage relationnel - Les documents JSON peuvent être analysés pendant qu’ils sont insérés dans la table à l’aide de fonctions OPENJSON, JSON_VALUE ou JSON_QUERY. Les fragments des documents JSON d'entrée peuvent être stockés dans les colonnes contenant des sous-éléments JSON avec des types de données JSON ou NVARCHAR. Cette approche augmente le temps de chargement, car l’analyse JSON est effectuée pendant le chargement. Toutefois, les performances des requêtes sont identiques à celles des requêtes classiques sur les données relationnelles.

  • Actuellement dans SQL Server, JSON n’est pas un type de données intégré.

  • Actuellement, le type de données JSON est disponible dans Azure SQL Database.

Tables classiques

La façon la plus simple de stocker des documents JSON dans SQL Server ou la base de données Azure SQL consiste à créer une table de deux colonnes qui contient l’ID et le contenu du document. Par exemple :

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max)
);

Ou, si pris en charge :

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] json
);

Cette structure est équivalente aux collections que vous pouvez trouver dans les bases de données de document classiques. La clé primaire _id est une valeur à incrémentation automatique qui fournit un identificateur unique pour chaque document et permet d’effectuer des recherches rapides. Cette structure est un bon choix pour les scénarios NoSQL classiques dans lesquels vous souhaitez récupérer un document par son ID ou mettre à jour un document stocké par son ID.

  • Utilisez le type de données JSON natif dans lequel stocker des documents JSON.
  • Le type de données nvarchar(max) vous permet de stocker des documents JSON ayant une taille maximale de 2 Go. Toutefois, si vous êtes sûr que la taille de vos documents JSON n’est pas supérieure à 8 Ko, nous vous recommandons d’utiliser NVARCHAR(4000) au lieu de NVARCHAR(max) pour des raisons de performances.

L’exemple de table créé dans l’exemple précédent suppose que des documents JSON valides sont stockés dans la colonne log. Si vous souhaitez que soit enregistré du JSON valide dans la colonne log, vous pouvez ajouter une contrainte CHECK sur cette colonne. Par exemple :

ALTER TABLE WebSite.Logs
    ADD CONSTRAINT [Log record should be formatted as JSON]
                   CHECK (ISJSON([log])=1)

Chaque fois qu’un utilisateur insère ou met à jour un document dans la table, cette contrainte vérifie que le document JSON est correctement mis en forme. Sans la contrainte, la table est optimisée pour les insertions, car tout document JSON est ajouté directement à la colonne sans aucun traitement.

Quand vous stockez vos documents JSON dans la table, vous pouvez utiliser le langage Transact-SQL standard pour les interroger. Par exemple :

SELECT TOP 100 JSON_VALUE([log], '$.severity'), AVG( CAST( JSON_VALUE([log],'$.duration') as float))
 FROM WebSite.Logs
 WHERE CAST( JSON_VALUE([log],'$.date') as datetime) > @datetime
 GROUP BY JSON_VALUE([log], '$.severity')
 HAVING AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) > 100
 ORDER BY AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) DESC

La possibilité d’utiliser n’importe quelle clause de requête et fonction T-SQL pour interroger des documents JSON est un réel avantage. SQL Server et SQL Database n’introduisent aucune contrainte dans les requêtes que vous pouvez utiliser pour analyser des documents JSON. Vous pouvez extraire des valeurs d’un document JSON avec la fonction JSON_VALUE et les utiliser dans la requête comme toute autre valeur.

Cette possibilité d’utiliser une syntaxe de requête T-SQL étendue est la principale différence entre d’une part SQL Server et SQL Database, et d’autre part les bases de données NoSQL classiques : dans Transact-SQL, vous avez probablement toutes les fonctions dont vous avez besoin pour traiter les données JSON.

Index

Si vous constatez que vos requêtes parcourent souvent les documents en fonction d’une propriété (par exemple, une propriété severity dans un document JSON), vous pouvez ajouter un index non cluster classique sur la propriété pour accélérer les requêtes.

Vous pouvez créer une colonne calculée qui expose des valeurs JSON à partir des colonnes JSON sur le chemin spécifié (autrement dit, sur le chemin $.severity) et créer un index standard sur cette colonne calculée. Par exemple :

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max),
    [severity] AS JSON_VALUE([log], '$.severity'),
    index ix_severity (severity)
);

La colonne calculée utilisée dans cet exemple est une colonne non persistante ou virtuelle qui n’entraîne aucune augmentation de l’espace occupé par la table. Elle est utilisée par l’index ix_severity pour améliorer les performances des requêtes, comme dans l’exemple suivant :

SELECT [log]
FROM Website.Logs
WHERE JSON_VALUE([log], '$.severity') = 'P4'

Une caractéristique importante de cet index est qu’il prend en charge les classements. Si votre colonne NVARCHAR d’origine a une propriété COLLATION (par exemple, un classement respectant la casse ou en fonction de la langue japonaise), l’index est organisé selon les règles de la langue ou du respect de la casse associées à la colonne NVARCHAR. Cette prise en compte du classement peut être une fonctionnalité importante si vous développez des applications à l’échelle internationale qui doivent utiliser des règles linguistiques personnalisées lors du traitement de documents JSON.

Tables de grande taille et mise en forme de columnstore

Si vous prévoyez d'avoir un grand nombre de documents JSON dans votre collection, nous vous recommandons d'ajouter un index columnstore en cluster sur la collection, comme le montre l'exemple suivant :

create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
    [_id] bigint default(next value for WebSite.LogID),
    [log] json,
    INDEX cci CLUSTERED COLUMNSTORE
);

Un index columnstore en cluster permet une compression élevée des données (jusqu'à 25x), ce qui peut réduire considérablement l'espace de stockage nécessaire, diminuer le coût du stockage et augmenter les performances d'E/S de votre charge de travail. En outre, les index columnstore en cluster sont optimisés pour les balayages de table et l'analyse de vos documents JSON, de sorte que ce type d'index peut être la meilleure option pour l'analytique des journaux d'activité.

L’exemple précédent utilise un objet de séquence pour attribuer des valeurs à la colonne _id. Les séquences et les identités sont des options valides pour la colonne d’ID.

Changement fréquent de documents et de tables à mémoire optimisée

Si vous prévoyez un grand nombre d’opérations de suppression, d’insertion et de mise à jour dans vos collections, vous pouvez stocker vos documents JSON dans des tables optimisées en mémoire. Les collections JSON à mémoire optimisée conservant toujours les données en mémoire, il n’existe aucune surcharge d’E/S pour le stockage. De plus, les collections JSON à mémoire optimisée sont complètement dépourvues de verrou ; autrement dit, les actions sur les documents ne bloquent aucune autre opération.

La seule chose à faire pour convertir une collection classique en collection optimisée en mémoire est de spécifier l’option WITH (MEMORY_OPTIMIZED=ON) après la définition de table, comme indiqué dans l’exemple suivant. Vous disposez alors d’une version à mémoire optimisée de la collection JSON.

CREATE TABLE WebSite.Logs (
  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] json
) WITH (MEMORY_OPTIMIZED=ON)

Une table à mémoire optimisée est la meilleure option pour les documents qui changent fréquemment. Quand vous envisagez des tables à mémoire optimisée, prenez également en considération le niveau de performance. Si possible, utilisez le type de données NVARCHAR(4000) au lieu de NVARCHAR(max) pour les documents JSON dans vos collections à mémoire optimisée, car cela peut améliorer considérablement le niveau de performance. Le type de données JSON n’est pas pris en charge avec les tables optimisées en mémoire.

Comme avec les tables classiques, vous pouvez ajouter des index sur les champs que vous exposez dans des tables optimisées en mémoire avec des colonnes calculées. Par exemple :

CREATE TABLE WebSite.Logs (

  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] json,

  [severity] AS cast(JSON_VALUE([log], '$.severity') as tinyint) persisted,
  INDEX ix_severity (severity)

) WITH (MEMORY_OPTIMIZED=ON)

Pour optimiser les performances, effectuez un cast de la valeur JSON vers le plus petit type possible pouvant contenir la valeur de la propriété. Dans l’exemple précédent, tinyint est utilisé.

Vous pouvez également placer les requêtes SQL qui mettent à jour les documents JSON dans des procédures stockées pour tirer parti de la compilation native. Par exemple :

CREATE PROCEDURE WebSite.UpdateData(@Id int, @Property nvarchar(100), @Value nvarchar(100))
WITH SCHEMABINDING, NATIVE_COMPILATION

AS BEGIN
    ATOMIC WITH (transaction isolation level = snapshot,  language = N'English')

    UPDATE WebSite.Logs
    SET [log] = JSON_MODIFY([log], @Property, @Value)
    WHERE _id = @Id;

END

Cette procédure compilée en mode natif prend la requête et crée un code .DLL qui exécute la requête. Une procédure compilée en mode natif est l’approche la plus rapide pour interroger et mettre à jour des données.

Conclusion

Les fonctions JSON natives dans SQL Server et SQL Database vous permettent de traiter des documents JSON comme dans les bases de données NoSQL. Chaque base de données - relationnelle ou NoSQL - présente des avantages et des inconvénients pour le traitement des données JSON. Le principal avantage du stockage des documents JSON dans SQL Server ou SQL Database est la prise en charge complète du langage SQL. Vous pouvez utiliser toute la palette du langage Transact-SQL pour traiter les données et configurer de nombreuses options de stockage des index columnstore pour la compression à un taux élevé et l’analytique rapide aux tables à mémoire optimisée pour le traitement sans verrou. Parallèlement, vous tirez parti de fonctionnalités de sécurité et d’internationalisation matures, que vous pouvez facilement réutiliser dans votre scénario NoSQL. Les raisons décrites dans cet article justifient pleinement d’envisager de stocker les documents JSON dans SQL Server ou SQL Database.

En savoir plus sur JSON dans SQL Server et Azure SQL Database

Pour obtenir une présentation visuelle de la prise en charge intégrée de JSON dans SQL Server et Azure SQL Database, consultez les vidéos suivantes :

Contenu connexe