Durante lo sviluppo di software basati su strutture dati relazionali complesse, spesso si ha la necessità di effettuare una storicizzazione del dato, che sia per utilizzi futuri, oppure ai fini di analisi o statistica.
Più la mole di dati è consistente, più ci si scontra con la necessità di trovare un equilibrio tra prestazione di interrogazione del dato e dimensione che questo occupa sul disco.
In questo articolo vediamo una tecnica di storicizzazione di informazioni in formato JSON, effettuando una compressione del dato ed analizzando successivamente le tecniche per interrogarlo.
Per fare ciò, utilizzeremo diversi strumenti introdotti con la versione SQL Server 2016: COMPRESS/DECOMPRESS per la compressione, OPENJSON e JSON_VALUE per la lettura dei dati JSON.
COMPRESS/DECOMPRESS
I metodi COMPRESS e DECOMPRESS utilizzano l’algoritmo di compressione GZIP per comprimere un campo, che può essere, per esempio, NVARCHAR o NTEXT, in un campo VARBINARY(MAX) e viceversa.
Nel nostro caso effettuiamo una compressione di un campo NVARCHAR(MAX) contente una stringa formattata in formato JSON:
DECLARE @JsonString = '{ "chiave": "valore" }'
INSERT INTO Tabella (CampoCompresso) VALUES (COMPRESS(@JsonString))
Per decomprimere il campo è sufficiente utilizzare il metodo DECOMPRESS ed effettuare il CAST al tipo di dato che era in origine prima della compressione:
SELECT CAST(DECOMPRESS(CampoCompresso) AS NVARCHAR(MAX)) FROM TabellaPreparazione della base dati
Vediamo ora un’esempio concreto su una base dati.
Creiamo un nuovo database su SQL Server 2016 o successivo ed al suo interno creiamo due tabelle: TabellaInChiaro e TabellaCompressa, utilizzando i due comandi:
CREATE TABLE [dbo].[TabellaInChiaro](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Json] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_TabellaInChiaro] PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)
CREATE TABLE [dbo].[TabellaCompressa](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Json] [varbinary](max) NOT NULL,
CONSTRAINT [PK_TabellaCompressa] PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)Entrambe le tabelle contengono due campi: un Id progressivo numerico ed un campo chiamato Json, che sarà di tipo NVARCHAR(MAX) nella tabella con i dati in chiaro e VARBINARY(MAX) in quella con i dati compressi.
Popoliamo ora le tabelle con molteplici dati utilizzando un ciclo WHILE:
DECLARE @Tot INT = 100000;
DECLARE @Indice INT = 0;
WHILE @Indice < @Tot
BEGIN
INSERT INTO TabellaInChiaro ([Json]) VALUES ('
{
"Nome": "Prodotto' + CAST(@Indice + 1 AS NVARCHAR(MAX)) + '",
"Prezzo": 15,
"Qta": 50,
"Descrizione": "Descrizione del prodotto ' + CAST(@Indice + 1 AS NVARCHAR(MAX)) + '"
}
')
SET @Indice = @Indice + 1
END
DECLARE @Tot INT = 100000;
DECLARE @Indice INT = 0;
WHILE @Indice < @Tot
BEGIN
INSERT INTO TabellaCompressa([Json]) VALUES (COMPRESS('
{
"Nome": "Prodotto' + CAST(@Indice + 1 AS NVARCHAR(MAX)) + '",
"Prezzo": 15,
"Qta": 50,
"Descrizione": "Descrizione del prodotto ' + CAST(@Indice + 1 AS NVARCHAR(MAX)) + '"
}
'))
SET @Indice = @Indice + 1
END
A questo punto abbiamo due tabelle che contengono i medesimi dati, da una parte in chiaro e dell’altra compressi.
Interrogazione
Per interrogare una stringa contenente informazioni formattate in formato JSON, SQL Server, come già detto dalla versione 2016, mette a disposizione diversi strumenti, tra cui: JSON_VALUE per estrarre un valore scalare da una stringa JSON e JSON_QUERY per estrarre invece un oggetto.
E’ possibile anche ottenere da un JSON un set di righe ed interrogarle come fossero comuni campi di una tabella SQL. Per fare ciò si utilizza OPENJSON.
Vediamo ora qualche esempio di interrogazione della nostra tabella con i dati in chiaro:
SELECT JSON_VALUE([Json], '$.Nome') FROM TabellaInChiaro| 1 | Prodotto1 |
| 2 | Prodotto2 |
| 3 | Prodotto3 |
| 4 | Prodotto4 |
| … | … |
Questa QUERY mostra l’elenco dei nomi dei prodotti accedendo direttamente al campo Nome del JSON sfruttando il metodo JSON_VALUE.
Lo stesso risultato lo otteniamo anche utilizzando il metodo OPENJSON per trasformare la stringa JSON in un set di dati:
SELECT Nome FROM TabellaInChiaro CROSS APPLY OPENJSON([Json]) WITH (Nome NVARCHAR(MAX))| 1 | Prodotto1 |
| 2 | Prodotto2 |
| 3 | Prodotto3 |
| 4 | Prodotto4 |
| … | … |
Continuando ad utilizzare OPENJSON inseriamo qualche condizione:
SELECT Nome, Prezzo, Qta FROM TabellaInChiaro
CROSS APPLY OPENJSON([Json])
WITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT)
WHERE Prezzo = 15
| Nome | Prezzo | Qta | |
| 1 | Prodotto1 | 15 | 50 |
| 2 | Prodotto2 | 15 | 50 |
| 3 | Prodotto3 | 15 | 50 |
| 4 | Prodotto4 | 15 | 50 |
| … | … | … | … |
Oppure qualche operazione:
SELECT SUM(Prezzo), SUM(Qta) FROM TabellaInChiaro
CROSS APPLY OPENJSON([Json])
WITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT)
| 1500000 | 5000000 |
E i dati compressi?
Niente di più semplice, basta effettuare la decompressione del campo Json prima di passarlo come argomento ai metodi JSON_VALUE e/o OPENJSON. Per esempio:
SELECT JSON_VALUE(CAST(DECOMPRESS([Json]) AS NVARCHAR(MAX)), '$.Nome') FROM TabellaCompressa| 1 | Prodotto1 |
| 2 | Prodotto2 |
| 3 | Prodotto3 |
| 4 | Prodotto4 |
| … | … |
O ancora:
SELECT SUM(Prezzo), SUM(Qta) FROM TabellaCompressa
CROSS APPLY OPENJSON(CAST(DECOMPRESS([Json]) AS NVARCHAR(MAX)))
WITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT)
Già eseguendo queste semplici QUERY si nota come il risultato non venga mostrato nell’immediato ma è necessario qualche secondo in più di elaborazione.
Cerchiamo ora quindi di capire quali differenze ci sono in termini di prestazioni e in termini di dimensione del dato.
Qualche cifra…
Le cifre che seguono sono state calcolate utilizzando una base dati simile a quella degli esempi precedenti ma con una struttura JSON più ricca di proprietà e contenente oggetti nidificati.
Hardware utilizzato per i test:
– Laptop Lenovo X1 Carbon 6a generazione
– Intel i7-8550U
– 16GB di memoria RAM LPDDR3
– SSD Samsung.
Dimensioni
| TabellaInChiaro (byte) | TabellaCompressa (byte) |
| 3368 | 227 |
| 3324 | 227 |
| 3324 | 225 |
| 3324 | 227 |
Come si può vedere, il record compresso è del 93% circa più piccolo rispetto al record non compresso.
E quindi parlando di dimensioni totali della tabella:
| TabellaInChiaro (megabyte) | TabellaCompressa (megabyte) |
| 316 | 21 |
Le dimensioni dei singoli record e della tabella sono state ricavate utilizzando il metodo DATALENGTH.
Prestazioni
Eseguiamo ora una semplice QUERY SELECT del Nome prodotto utilizzando il metodo OPENJSON:
SELECT Nome FROM TabellaInChiaro
CROSS APPLY OPENJSON([Json])
WITH (Nome NVARCHAR(MAX))
| TabellaInChiaro (ms) | TabellaCompressa (ms) |
| 2704 | 7916 |
Ora proviamo ad ottenere lo stesso elenco ma utilizzando il metodo JSON_VALUE:
SELECT JSON_VALUE([Json], '$.Nome') FROM TabellaInChiaro| TabellaInChiaro (ms) | TabellaCompressa (ms) |
| 695 | 4933 |
Si può notare a colpo d’occhio come la QUERY che utilizza il metodo OPENJSON sia meno performante di quella che utilizza il metodo JSON_VALUE. Questo perché la stringa JSON con il primo metodo viene convertita in set di dati a differenza del secondo, dove si accede alla proprietà Nome in maniera diretta e scalare.
Proviamo ad effettuare ora una QUERY un po’ più complessa interrogando oggetti nidificati all’interno del JSON:
SELECT P.Nome, P.Prezzo, P.Qta, T.Nome, T.Prezzo, T.Qta FROM TabellaInChiaro
CROSS APPLY OPENJSON([Json])
WITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT, SottoProdotto NVARCHAR(MAX) AS JSON) P
CROSS APPLY OPENJSON(SottoProdotto)
WITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT, SottoProdotto NVARCHAR(MAX) AS JSON) T
| TabellaInChiaro (ms) | TabellaCompressa (ms) |
| 6279 | 12424 |
Conclusione
Trovare un’equilibrio tra prestazioni dell’interrogazione del dato e lo spazio che questo occupa sul disco non è certamente semplice. Bisogna fare scelte, dettate anche da altre variabili, come ad esempio la quantità di volte che il dato deve essere interrogato oppure quali tipologie di interrogazioni vengono fatte più spesso (esempio interrogazioni temporali).
In questo articolo non c’è la soluzione ma semplicemente una possibile strada da poter percorrere. Un’idea per migliorare la storicizzazione potrebbe per esempio essere una gestione ibrida dove i dati necessari per filtrare i record o con un alto numero di accessi vengono salvati in chiaro, mentre i restanti vengono compressi per evitare consumo inutile di spazio.
Pingback: Storicizzazione del dato, differenze fra strutture dati JSON in SQL Server e strutture NoSQL con CosmosDB | Fontana Marco IT Consulting