PostgreSQL posee dos tipos de datos para soportar el formato JSON: json y jsonb. Ambos tipos permiten almacenar información en formato JSON dentro de tablas de una base de datos PostgreSQL. Pero además, estos formatos permiten que el motor de bases de datos sea capaz de interpretar datos JSON y recuperar sólo una parte de los mismos. Efectivamente examinando, descomponiendo e interpretando la estructura JSON.

La diferencia entre los tipos json y jsonb es la forma en que son almacenados y procesados. El tipo json almacena una copia exacta en formato de texto, lo cual acelera las escrituras, pero demora las consultas (pues es necesario re-parsear cada campo durante cada ejecución). El tipo jsonb, en cambio, almacena los datos JSON en un formato binario descompuesto, el cual demora las escrituras, pero acelera las lecturas (consultas) pues cada dato se parsea una sola vez. Esto además permite el indexado de etiquetas JSON. Realmente increíble, indexar etiquetas JSON para acelerar consultas a lo largo de múltiples datos. Este es el tipo de características avanzadas que hacen de Postgres un motor de bases de datos superior. Cabe destacar que por ejemplo MySQL soporta el tipo JSON, sin embargo no permite implementar este tipo de índices sobre tags JSON.



Veamos cómo aprovechar el tipo de datos jsonb para almacenar datos JSON en una base de datos PostgreSQL y realizar consultas sobre un conjunto de datos (problema que implicaría un desarrollo monumental si se quisiera resolver de forma manual).

La semana pasada compartí mi primer script Python con pyVmomi, se trata de un script que lista el espacio utilizado y disponible de todos los datastores de un host ESX/ESXi/vCenter. Este script retorna la siguiente salida en formato JSON:

{"ds-esxi-1": {"used": "971.0 MB", "capacity": "62.8 GB", "free": "61.8 GB", "pfree": "98.49"}, "ds-esxi-2": {"used": "971.0 MB", "capacity": "62.8 GB", "free": "61.8 GB", "pfree": "98.49"}, "ds-esxi-3": {"used": "4.2 GB", "capacity": "62.8 GB", "free": "58.6 GB", "pfree": "93.32"}, "ds-prod": {"used": "2.8 TB", "capacity": "3.9 TB", "free": "1.1 TB", "pfree": "27.98"}, "ds-desa": {"used": "1.3 TB", "capacity": "1.9 TB", "free": "582.5 GB", "pfree": "30.54"}, "ds-prod-2": {"used": "2.4 TB", "capacity": "3.0 TB", "free": "606.2 GB", "pfree": "19.73"}, "ds-backup": {"used": "1.9 TB", "capacity": "2.8 TB", "free": "945.1 GB", "pfree": "32.59"}}

Notar que existe un tag para cada datastore, el cual incluye a su vez los campos "used", "capacity", "free" y "pfree".

El objetivo de todo este desarrollo consiste en tomar muestras periódicas del uso de datastores y almacenarlas en una base de datos. Esto permitiría tener un histórico de la evolución de uso de los diferentes datastores, y más importante poder predecir (de acuerdo a la estadística que se vaya haciendo) cómo van a crecer a futuro, a fin de determinar el nivel de escalabilidad de la infraestructura actual. Un dato clave para el sustento de la organización, pues los datastores (cajones de discos) no son un equipamiento para nada económico, ni que se pueda adquirir con frecuencia.

A fin de almacenar estos datos en una base Postgres, he creado la tabla "datastore_space":

vmware=> \d datastore_space 
                                   Table "public.datastore_space"
 Column |            Type             |                          Modifiers
--------+-----------------------------+--------------------------------------------------------------
 id     | integer                     | not null default nextval('datastore_space_id_seq'::regclass)
 fecha  | timestamp without time zone | not null
 jdata  | jsonb                       | not null
Indexes:
    "datastore_space_fecha_idx" btree (fecha)

Esta tabla posee tres campos, un "id" auto-incrementado, la fecha de la muestra y la propia muestra en formato JSON (columna "jdata").

Ahora bien, luego de haber insertado algunas muestras, es posible recuperar los datos JSON con una consulta SELECT:

vmware=> select jdata FROM datastore_space limit 1;
                                                                                                                                                                                                                                                                                                                                                jdata
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"ds-prod": {"free": "1.1 TB", "used": "2.8 TB", "pfree": "27.94", "capacity": "3.9 TB"}, "ds-backup": {"free": "944.8 GB", "used": "1.9 TB", "pfree": "32.58", "capacity": "2.8 TB"}, "ds-esxi-1": {"free": "61.8 GB", "used": "971.0 MB", "pfree": "98.49", "capacity": "62.8 GB"}, "ds-esxi-2": {"free": "61.8 GB", "used": "971.0 MB", "pfree": "98.49", "capacity": "62.8 GB"}, "ds-esxi-3": {"free": "58.6 GB", "used": "4.2 GB", "pfree": "93.32", "capacity": "62.8 GB"}, "ds-desa": {"free": "581.4 GB", "used": "1.3 TB", "pfree": "30.49", "capacity": "1.9 TB"}, "ds-prod-2": {"free": "605.4 GB", "used": "2.4 TB", "pfree": "19.71", "capacity": "3.0 TB"}}
(1 row)

Esto no tiene nada de especial respecto a cualquier otro tipo de datos. Hemos almacenado el dato JSON y lo recuperamos con una consulta, como se hace con cualquier otro dato.

Lo interesante comienza aquí. Más allá de recuperar todo el dato JSON, es posible examinar y descomponer su estructura. Por ejemplo, quiero recuperar sólo los datos correspondientes al datastore (tag JSON) "ds-esxi-1":

vmware=> select jdata->'ds-esxi-1' FROM datastore_space;
                                     ?column?
----------------------------------------------------------------------------------
 {"free": "61.8 GB", "used": "971.0 MB", "pfree": "98.49", "capacity": "62.8 GB"}
 {"free": "61.8 GB", "used": "971.0 MB", "pfree": "98.49", "capacity": "62.8 GB"}
 {"free": "61.8 GB", "used": "971.0 MB", "pfree": "98.49", "capacity": "62.8 GB"}
 {"free": "61.8 GB", "used": "971.0 MB", "pfree": "98.49", "capacity": "62.8 GB"}
 {"free": "61.8 GB", "used": "971.0 MB", "pfree": "98.49", "capacity": "62.8 GB"}
(5 rows)

Se observa que se selecciona el tag "ds-esxi-1" de la columna o campo de tipo JSON "jdata" con la expresión jdata->'ds-esxi-1'.

De igual forma es posible seguir adentrándose en la estructura JSON, por ejemplo para recuperar sólo el espacio disponible ("free"):

vmware=> select jdata->'ds-esxi-1'->'free' FROM datastore_space;
 ?column?  
-----------
 "61.8 GB"
 "61.8 GB"
 "61.8 GB"
 "61.8 GB"
 "61.8 GB"
(5 rows)

Por supuesto es posible combinar datos de un campo de tipo JSON con cualquier otra columna:

vmware=> select fecha,jdata->'ds-esxi-1'->'free' FROM datastore_space;
           fecha            | ?column?  
----------------------------+-----------
 2018-05-31 12:08:19.507687 | "61.8 GB"
 2018-05-31 14:33:36.344125 | "61.8 GB"
 2018-06-01 11:15:02.217824 | "61.8 GB"
 2018-06-02 11:15:03.422823 | "61.8 GB"
 2018-06-03 11:15:03.386598 | "61.8 GB"
(5 rows)

Con esta información puedo generar las gráficas necesarias para visualizar la evolución de espacio disponible de cualquier datastore a lo largo del tiempo.

Por último resta aclarar que los datos de tipo json y jsonb se insertan como cualquier otro capo de texto plano.

Referencias


Tal vez pueda interesarte


Compartí este artículo