{"id":3448,"date":"2021-07-13T07:51:03","date_gmt":"2021-07-13T07:51:03","guid":{"rendered":"https:\/\/cloudsurfers.it\/?p=3448"},"modified":"2021-07-13T09:08:47","modified_gmt":"2021-07-13T09:08:47","slug":"storicizzazione-del-dato-compressione-e-interrogazione-di-strutture-dati-json-in-sql-server","status":"publish","type":"post","link":"https:\/\/cloudsurfers.it\/index.php\/storicizzazione-del-dato-compressione-e-interrogazione-di-strutture-dati-json-in-sql-server\/","title":{"rendered":"Storicizzazione del dato, compressione e interrogazione di strutture dati JSON in SQL Server"},"content":{"rendered":"\n
\"\"<\/figure>\n\n\n\n

Durante lo sviluppo di software basati su strutture dati relazionali complesse, spesso si ha la necessit\u00e0 di effettuare una storicizzazione del dato, che sia per utilizzi futuri, oppure ai fini di analisi o statistica.<\/p>\n\n\n\n

Pi\u00f9 la mole di dati \u00e8 consistente, pi\u00f9 ci si scontra con la necessit\u00e0 di trovare un equilibrio tra prestazione di interrogazione del dato e dimensione che questo occupa sul disco.<\/p>\n\n\n\n

In questo articolo vediamo una tecnica di storicizzazione di informazioni in formato JSON<\/em>, effettuando una compressione del dato ed analizzando successivamente le tecniche per interrogarlo.<\/p>\n\n\n\n

Per fare ci\u00f2, utilizzeremo diversi strumenti introdotti con la versione SQL Server 2016<\/strong>: COMPRESS\/DECOMPRESS<\/strong> per la compressione, OPENJSON<\/strong> e JSON_VALUE<\/strong> per la lettura dei dati JSON<\/em>.<\/p>\n\n\n\n

COMPRESS\/DECOMPRESS<\/h3>\n\n\n\n

I metodi COMPRESS <\/strong>e DECOMPRESS <\/strong>utilizzano l’algoritmo di compressione GZIP <\/em>per comprimere un campo, che pu\u00f2 essere, per esempio, NVARCHAR <\/em>o NTEXT<\/em>, in un campo VARBINARY(MAX)<\/strong> e viceversa.<\/p>\n\n\n\n

Nel nostro caso effettuiamo una compressione di un campo NVARCHAR(MAX)<\/em> contente una stringa formattata in formato JSON<\/em>:<\/p>\n\n\n\n

DECLARE @JsonString = '{ \"chiave\": \"valore\" }'\nINSERT INTO Tabella (CampoCompresso) VALUES (COMPRESS(@JsonString))\n<\/code><\/pre>\n\n\n\n
Per decomprimere il campo \u00e8 sufficiente utilizzare il metodo DECOMPRESS <\/strong>ed effettuare il CAST <\/strong>al tipo di dato che era in origine prima della compressione:<\/p>\n\n\n\n
SELECT CAST(DECOMPRESS(CampoCompresso) AS NVARCHAR(MAX)) FROM Tabella<\/code><\/pre>\n\n\n\n
Preparazione della base dati<\/h2>\n\n\n\n
Vediamo ora un’esempio concreto su una base dati.<\/p>\n\n\n\n
Creiamo un nuovo database su SQL Server 2016<\/em> o successivo ed al suo interno creiamo due tabelle: TabellaInChiaro<\/strong> e TabellaCompressa<\/strong>, utilizzando i due comandi:<\/p>\n\n\n\n
CREATE TABLE [dbo].[TabellaInChiaro](\n\t[Id] [int] IDENTITY(1,1) NOT NULL,\n\t[Json] [nvarchar](max) NOT NULL,\n \tCONSTRAINT [PK_TabellaInChiaro] PRIMARY KEY CLUSTERED \n(\n\t[Id] ASC\n) 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]\n)\n<\/code><\/pre>\n\n\n\n
CREATE TABLE [dbo].[TabellaCompressa](\n\t[Id] [int] IDENTITY(1,1) NOT NULL,\n\t[Json] [varbinary](max) NOT NULL,\n\tCONSTRAINT [PK_TabellaCompressa] PRIMARY KEY CLUSTERED \n(\n\t[Id] ASC\n) 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]\n)<\/code><\/pre>\n\n\n\n
Entrambe le tabelle contengono due campi: un Id<\/strong> progressivo numerico ed un campo chiamato Json<\/strong>, che sar\u00e0 di tipo NVARCHAR(MAX)<\/strong> nella tabella con i dati in chiaro e VARBINARY(MAX)<\/strong> in quella con i dati compressi.<\/p>\n\n\n\n
Popoliamo ora le tabelle con molteplici dati utilizzando un ciclo WHILE<\/em>:<\/p>\n\n\n\n
DECLARE @Tot INT = 100000;\nDECLARE @Indice INT = 0;\nWHILE @Indice < @Tot\nBEGIN\n\tINSERT INTO TabellaInChiaro ([Json]) VALUES ('\n\t\t{\n\t\t\t\"Nome\": \"Prodotto' + CAST(@Indice + 1 AS NVARCHAR(MAX)) + '\",\n\t\t\t\"Prezzo\": 15,\n\t\t\t\"Qta\": 50,\n\t\t\t\"Descrizione\": \"Descrizione del prodotto ' + CAST(@Indice + 1 AS NVARCHAR(MAX)) + '\"\n\t\t}\n\t')\n\tSET @Indice = @Indice + 1\nEND\n<\/code><\/pre>\n\n\n\n
DECLARE @Tot INT = 100000;\nDECLARE @Indice INT = 0;\nWHILE @Indice < @Tot\nBEGIN\n\tINSERT INTO TabellaCompressa([Json]) VALUES (COMPRESS('\n\t\t{\n\t\t\t\"Nome\": \"Prodotto' + CAST(@Indice + 1 AS NVARCHAR(MAX)) + '\",\n\t\t\t\"Prezzo\": 15,\n\t\t\t\"Qta\": 50,\n\t\t\t\"Descrizione\": \"Descrizione del prodotto ' + CAST(@Indice + 1 AS NVARCHAR(MAX)) + '\"\n\t\t}\n\t'))\n\tSET @Indice = @Indice + 1\nEND\n<\/code><\/pre>\n\n\n\n
A questo punto abbiamo due tabelle che contengono i medesimi dati, da una parte in chiaro e dell’altra compressi.<\/p>\n\n\n\n
Interrogazione<\/h2>\n\n\n\n
Per interrogare una stringa contenente informazioni formattate in formato JSON<\/em>, SQL Server<\/em>, come gi\u00e0 detto dalla versione 2016<\/strong>, mette a disposizione diversi strumenti, tra cui: JSON_VALUE<\/strong> per estrarre un valore scalare da una stringa JSON <\/em>e JSON_QUERY<\/strong> per estrarre invece un oggetto.<\/p>\n\n\n\n
E’ possibile anche ottenere da un JSON <\/em>un set di righe ed interrogarle come fossero comuni campi di una tabella SQL<\/em>. Per fare ci\u00f2 si utilizza OPENJSON<\/strong>. <\/p>\n\n\n\n
Vediamo ora qualche esempio di interrogazione della nostra tabella con i dati in chiaro:<\/p>\n\n\n\n
SELECT JSON_VALUE([Json], '$.Nome') FROM TabellaInChiaro<\/code><\/pre>\n\n\n\n
1<\/td>Prodotto1<\/td><\/tr>
2<\/td>Prodotto2<\/td><\/tr>
3<\/td>Prodotto3<\/td><\/tr>
4<\/td>Prodotto4<\/td><\/tr>
…<\/td>…<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Questa QUERY <\/em>mostra l’elenco dei nomi dei prodotti accedendo direttamente al campo Nome <\/em>del JSON <\/em>sfruttando il metodo JSON_VALUE<\/strong>.<\/p>\n\n\n\n
Lo stesso risultato lo otteniamo anche utilizzando il metodo OPENJSON<\/strong> per trasformare la stringa JSON <\/em>in un set di dati:<\/p>\n\n\n\n
SELECT Nome FROM TabellaInChiaro CROSS APPLY OPENJSON([Json]) WITH (Nome NVARCHAR(MAX))<\/code><\/pre>\n\n\n\n
1<\/td>Prodotto1<\/td><\/tr>
2<\/td>Prodotto2<\/td><\/tr>
3<\/td>Prodotto3<\/td><\/tr>
4<\/td>Prodotto4<\/td><\/tr>
…<\/td>…<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Continuando ad utilizzare OPENJSON <\/strong>inseriamo qualche condizione:<\/p>\n\n\n\n
SELECT Nome, Prezzo, Qta FROM TabellaInChiaro\nCROSS APPLY OPENJSON([Json]) \nWITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT)\nWHERE Prezzo = 15\n<\/code><\/pre>\n\n\n\n
<\/td>Nome<\/strong><\/td>Prezzo<\/strong><\/td>Qta<\/strong><\/td><\/tr>
1<\/td>Prodotto1<\/td>15<\/td>50<\/td><\/tr>
2<\/td>Prodotto2<\/td>15<\/td>50<\/td><\/tr>
3<\/td>Prodotto3<\/td>15<\/td>50<\/td><\/tr>
4<\/td>Prodotto4<\/td>15<\/td>50<\/td><\/tr>
…<\/td>…<\/td>…<\/td>…<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Oppure qualche operazione:<\/p>\n\n\n\n
SELECT SUM(Prezzo), SUM(Qta) FROM TabellaInChiaro\nCROSS APPLY OPENJSON([Json]) \nWITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT)\n<\/code><\/pre>\n\n\n\n
1500000<\/td>5000000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
E i dati compressi?<\/h2>\n\n\n\n
Niente di pi\u00f9 semplice, basta effettuare la decompressione del campo Json <\/strong>prima di passarlo come argomento ai metodi JSON_VALUE<\/strong> e\/o OPENJSON<\/strong>. Per esempio:<\/p>\n\n\n\n
SELECT JSON_VALUE(CAST(DECOMPRESS([Json]) AS NVARCHAR(MAX)), '$.Nome') FROM TabellaCompressa<\/code><\/pre>\n\n\n\n
1<\/td>Prodotto1<\/td><\/tr>
2<\/td>Prodotto2<\/td><\/tr>
3<\/td>Prodotto3<\/td><\/tr>
4<\/td>Prodotto4<\/td><\/tr>
…<\/td>…<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
O ancora:<\/p>\n\n\n\n
SELECT SUM(Prezzo), SUM(Qta) FROM TabellaCompressa\nCROSS APPLY OPENJSON(CAST(DECOMPRESS([Json]) AS NVARCHAR(MAX))) \nWITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT)\n<\/code><\/pre>\n\n\n\n
Gi\u00e0 eseguendo queste semplici QUERY <\/em>si nota come il risultato non venga mostrato nell’immediato ma \u00e8 necessario qualche secondo in pi\u00f9 di elaborazione.<\/p>\n\n\n\n
Cerchiamo ora quindi di capire quali differenze ci sono in termini di prestazioni e in termini di dimensione del dato.<\/p>\n\n\n\n
Qualche cifra…<\/h2>\n\n\n\n
Le cifre che seguono sono state calcolate utilizzando una base dati simile a quella degli esempi precedenti ma con una struttura JSON <\/em>pi\u00f9 ricca di propriet\u00e0 e contenente oggetti nidificati.<\/p>\n\n\n\n
Hardware utilizzato per i test:<\/em>
– Laptop Lenovo X1 Carbon 6a generazione <\/em>
– Intel i7-8550U <\/em>
– 16GB di memoria RAM LPDDR3 <\/em>
– SSD Samsung.<\/em><\/p>\n\n\n\n
Dimensioni<\/h3>\n\n\n\n
TabellaInChiaro (byte)<\/strong><\/td>TabellaCompressa (byte)<\/strong><\/td><\/tr>
3368<\/td>227<\/td><\/tr>
3324<\/td>227<\/td><\/tr>
3324<\/td>225<\/td><\/tr>
3324<\/td>227<\/td><\/tr><\/tbody><\/table>
Dimensioni in byte di 4 record contenuti in entrambe le tabelle.<\/figcaption><\/figure>\n\n\n\n
Come si pu\u00f2 vedere, il record compresso \u00e8 del 93% circa pi\u00f9 piccolo<\/strong> rispetto al record non compresso.<\/p>\n\n\n\n
E quindi parlando di dimensioni totali della tabella:<\/p>\n\n\n\n
TabellaInChiaro (megabyte)<\/strong><\/td>TabellaCompressa (megabyte)<\/strong><\/td><\/tr>
316<\/td>21<\/td><\/tr><\/tbody><\/table>
Dimensioni in megabyte dell’intera tabella.<\/figcaption><\/figure>\n\n\n\n
Le dimensioni dei singoli record e della tabella sono state ricavate utilizzando il metodo DATALENGTH<\/strong>.<\/em><\/p>\n\n\n\n
Prestazioni<\/h3>\n\n\n\n
Eseguiamo ora una semplice QUERY SELECT<\/em> del Nome <\/strong>prodotto utilizzando il metodo OPENJSON<\/strong>:<\/p>\n\n\n\n
SELECT Nome FROM TabellaInChiaro\nCROSS APPLY OPENJSON([Json]) \nWITH (Nome NVARCHAR(MAX))\n<\/code><\/pre>\n\n\n\n
TabellaInChiaro (ms)<\/strong><\/td>TabellaCompressa (ms)<\/strong><\/td><\/tr>
2704<\/td>7916<\/td><\/tr><\/tbody><\/table>
Lettura del dato in chiaro 66% pi\u00f9 veloce.<\/figcaption><\/figure>\n\n\n\n
Ora proviamo ad ottenere lo stesso elenco ma utilizzando il metodo JSON_VALUE<\/strong>:<\/p>\n\n\n\n
SELECT JSON_VALUE([Json], '$.Nome') FROM TabellaInChiaro<\/code><\/pre>\n\n\n\n
TabellaInChiaro (ms)<\/strong><\/td>TabellaCompressa (ms)<\/strong><\/td><\/tr>
695<\/td>4933<\/td><\/tr><\/tbody><\/table>
Lettura del dato in chiaro 86% pi\u00f9 veloce.<\/figcaption><\/figure>\n\n\n\n
Si pu\u00f2 notare a colpo d’occhio come la QUERY <\/em>che utilizza il metodo OPENJSON <\/strong>sia meno performante di quella che utilizza il metodo JSON_VALUE<\/strong>. Questo perch\u00e9 la stringa JSON <\/em>con il primo metodo viene convertita in set di dati a differenza del secondo, dove si accede alla propriet\u00e0 Nome <\/strong>in maniera diretta e scalare.<\/p>\n\n\n\n
Proviamo ad effettuare ora una QUERY <\/em>un po’ pi\u00f9 complessa interrogando oggetti nidificati all’interno del JSON<\/em>:<\/p>\n\n\n\n
SELECT P.Nome, P.Prezzo, P.Qta, T.Nome, T.Prezzo, T.Qta  FROM TabellaInChiaro\nCROSS APPLY OPENJSON([Json]) \nWITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT, SottoProdotto NVARCHAR(MAX) AS JSON) P\nCROSS APPLY OPENJSON(SottoProdotto)\nWITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT, SottoProdotto NVARCHAR(MAX) AS JSON) T\n<\/code><\/pre>\n\n\n\n
TabellaInChiaro (ms)<\/strong><\/td>TabellaCompressa (ms)<\/strong><\/td><\/tr>
6279<\/td>12424<\/td><\/tr><\/tbody><\/table>
L’accesso ai dati compressi ha bisogno del doppio del tempo rispetto ai dati in chiaro.<\/figcaption><\/figure>\n\n\n\n
Conclusione<\/h2>\n\n\n\n
Trovare un’equilibrio tra prestazioni dell’interrogazione del dato e lo spazio che questo occupa sul disco non \u00e8 certamente semplice. Bisogna fare scelte, dettate anche da altre variabili, come ad esempio la quantit\u00e0 di volte che il dato deve essere interrogato oppure quali tipologie di interrogazioni vengono fatte pi\u00f9 spesso (esempio interrogazioni temporali).<\/p>\n\n\n\n
In questo articolo non c’\u00e8 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.<\/p>\n\n\n\n
Link utili<\/h3>\n\n\n\n
https:\/\/docs.microsoft.com\/it-it\/sql\/relational-databases\/data-compression\/data-compression?view=sql-server-ver15<\/a><\/p>\n\n\n\n
https:\/\/docs.microsoft.com\/it-it\/sql\/relational-databases\/json\/json-data-sql-server?view=sql-server-ver15<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Durante lo sviluppo di software basati su strutture dati relazionali complesse, spesso si ha la necessit\u00e0 di effettuare una storicizzazione del dato, che sia per utilizzi futuri, oppure ai fini di analisi o statistica.<\/p>\n
Pi\u00f9 la mole di dati \u00e8 consistente, pi\u00f9 ci si scontra con la necessit\u00e0 di trovare un equilibrio tra prestazione di interrogazione del dato e dimensione che questo occupa sul disco.<\/p>\n","protected":false},"author":3,"featured_media":3459,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"wds_primary_category":0,"footnotes":""},"categories":[130,139],"tags":[146,141,147,148,142,145,144,143,140,38],"class_list":["post-3448","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-insights","category-sql-server-insights","tag-compress","tag-database","tag-decompress","tag-gzip","tag-json","tag-json_query","tag-json_value","tag-openjson","tag-sql","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/cloudsurfers.it\/index.php\/wp-json\/wp\/v2\/posts\/3448","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cloudsurfers.it\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cloudsurfers.it\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cloudsurfers.it\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/cloudsurfers.it\/index.php\/wp-json\/wp\/v2\/comments?post=3448"}],"version-history":[{"count":0,"href":"https:\/\/cloudsurfers.it\/index.php\/wp-json\/wp\/v2\/posts\/3448\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cloudsurfers.it\/index.php\/wp-json\/wp\/v2\/media\/3459"}],"wp:attachment":[{"href":"https:\/\/cloudsurfers.it\/index.php\/wp-json\/wp\/v2\/media?parent=3448"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cloudsurfers.it\/index.php\/wp-json\/wp\/v2\/categories?post=3448"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cloudsurfers.it\/index.php\/wp-json\/wp\/v2\/tags?post=3448"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}