TimescaleDB Entidades: TimescaleDB como Motor TimeSeries sobre Ontologías

1 ¿Qué es TimescaleDB?
- 1.1 Hypertables
- 1.2 Índices hypertable
- 1.3 Soporte JSON en Hypertable
- 1.4 Inserciones múltiples y upserts
- 1.5 Consultas
2 Integración en la Plataforma
- 2.1 General information
- 2.2 Properties
- 2.3 Timeseries properties
- 2.4 Agregados temporales continuos
3 En proceso: paso a Cold Data

¿Qué es TimescaleDB?

Timescale (ser TimeScaleDB) es una base de datos open source para almacenamiento y análisis de Time Series con la potencia y ventajas de usar SQL. TimescaleDB está construida sobre PostgreSQL lo que tiene la ventaja de poder usar las herramientas del ecosistema Postgresql.

A nivel de ingesta, TimescaleDB está preparada para grandes ingestas de puntos usando particionado time-space automático. Este particionado automático optimiza el tiempo de ingesta y por otro lado los usuarios ven todos sus datos como una única hipertabla.

Con la abstracción de la hypertable sobre los datos time-series el usuario se relaciona con esta estructura como si fuera una tabla SQL normal, lo que permite creación de tabas, índices,… la hipertabla usa compresión nativa y puede escalar a nivel de TBs.

TimescaleDB incluye:

Capacidades de gestión de datos específicas, como retención de datos, downsamplings, compresión nativa, gestión del ciclo de vida de los datos, políticas de agregado,…
Funciones orientadas a analítica Time Series, como la creación de ventanas, relleno de huecos, queries LOCF,…
Y al estar construida sobre PostgreSQL puede almacenar tus datos de negocio en la misma base de datos permitiendo hacer JOINS.
Ofrece una herramienta que permite migrar de una de las bases de datos Time Series más usada en la actualidad: InfluxDB: https://www.outfluxdata.com

TimescaleDB se ofrece como servicio en los 3 principales Clouds:

Hypertables

TimescaleDB de hecho se monta como una extensión de Postgresql:

Al ser una extensión puedo utilizar el mismo driver de Postgresql (el JDBC en nuestro caso).

Como decíamos el concepto principal de TimescaleDB es el hypertable.

Una vez tenemos la extensión para crear una hypertable crearé mi tabla normalmente:

Y luego la convierto a hypertable indicando el campo por el que particionamos:

Una vez hecho esto puedo hacer las operaciones SQL normalmente:

A la hora de crear la hypertable puedo configurar un conjunto de opciones, como el intervalo de tiempo por defecto (que si no se establece es de 7 días), podemos cambiarlo así:

También puedo configurar las space partitions.

Podéis leer más sobre las Best Practices en Hypertables aquí: https://docs.timescale.com/v1.3/using-timescaledb/hypertables#best-practices

Índices hypertable

Cuando se crea una hypertable se crea un índice temporal así:

Si a la hora de crear la hypertable especificamos un space partition se creará un índice de este tipo:

Soporte JSON en Hypertable

TimescaleDB puede trabajar con los mismos tipos de datos que Postgresql, incluido JSON y JSONB lo que es una opción muy interesante para muchos escenarios.

CREATE TABLE metrics (
  time TIMESTAMPTZ,
  user_id INT,
  device_id INT,
  data JSONB
);

Se recomienda:

Usar el tipo de dato JSONB (JSON binario) que son más eficientes en almacenamiento y búsqueda
Una aproximación semiestructurada donde los campos como time, user_id, device_id estén fuera de la estructura JSONB ya que el acceso es más eficiente

TimescaleDB también soporta crear índices sobre una estructura JSONB entera (índices GIN) o incluso sobre un campo dentro del JSONB.

Inserciones múltiples y upserts

Además de los INSERTs normales TimescaleDB soporta inserciones de múltiples filas en una hypertable con un único INSERT:

También tengo el concepto de UPSERT, que me permite gestionar conflictos en las inserciones:

Consultas

TimescaleDB y sus hypertables se comportan igual que una tabla Postgresql, por tanto puedo hacer JOINS entre tablas normales e hypertables:

Además de queries complejas:

Además de queries analíticas como la medias, mediana, percentil, sumatorios, histogramas, relleno de huecos, etc.

Integración en la Plataforma

A continuación, vamos a ver cómo funciona la integración de TimescaleDB en Plataforma y cómo podemos definir una Entidad TimeSeries sobre TimescaleDB.

General information

A la hora de crear las TimeSeries, añadiremos una nueva opción en la selección del motor de bases de datos. El resto de campos de la sección permanecen igual.

Properties

En lo que a TAGs y Señales se refiere, todo permanecerá al igual que para las timeseries de MongoDB.

Esta selección compondrá la estructura de la tabla, donde siempre habrá un campo "timestamp". El ejemplo de arriba se traduciría a la siguiente consulta:

CREATE TABLE timeserieExample (
  timestamp TIMESTAMP NOT NULL,
  tag1 TEXT NOT NULL,
  tag2 TEXT NOT NULL,
  signal1 numeric null,
  signal2 numeric null
  unique(timestamp, tag1, tag2)
);

Por defecto, todo TAG y el timestamp no podrán ser nulos ya que formarían parte de la clave que compone un registro único. En el caso de eventos (ver más adelante) no se generarán campos únicos.

El conjunto de campos únicos define la clave por la cual vamos a permitir hacer UPSERT. A día de hoy, para las timeseries MongoDB, una inserción para un timestamp es realmente un UPSERT para el valor indicado a nivel/clave timestamp+TAGS. Para TimescaleDB es un requerimiento definir estos campos únicos para poder realizar UPSERTS. Para cada registro que se vaya a insertar, por debajo plataforma obtendrá los campos de la clave para componer el criterio de "conflicto".

Supongamos el ejemplo de arriba y que insertamos los siguientes datos:

{"Timeserie":{"timestamp":"2021-05-01T00:00:00Z","tag1":"valor1",tag2":"valor2","signal1":1,"signal2":2}}

Esto se traduce a una sentencia SQL:

insert into timeserieExample values(TIMESTAMP '2021-05-01 00:00:00','tag1','tag2', 1, 2);

Si volvemos a insertar por ejemplo un dato para la primera señal y marcamos la agregación como "SUM" (ver más adelante).

{"Timeserie":{"timestamp":"2021-05-01T00:00:00Z","tag1":"valor1",tag2":"valor2","signal1":2}}

La Plataforma detectará la clave y generará un UPSERT como el siguiente:

insert into timeserieExample values(TIMESTAMP '2021-05-01 00:00:00','tag1','tag2', 2) on conflict (timestamp, tag1, tag2) do update set signal1=timeserieExample.signal1+excluded.signal1;

Timeseries properties

Tras seleccionar los campos y señales de la timeserie, vamos a definir los siguientes parámetros relativos a cómo agrupamos los datos:

Chunk time interval: este parámetro define la ventana temporal que se tiene en cuenta para crear los chunks. Es recomendable que los chnks contengan entre 1 y 10 millones de registros (en función del tamaño).

Window frequency: es este caso, definimos si queremos que la timeserie tenga intervalos discretos a la hora de almacenar las medidas o no. Las posibilidades son:

No aggregation (EVENTS): no habrá ventana como tal, es decir, los datos se almacenarán tal cual se inserten. Esto sigue un patrón de datos como el de eventos, donde no hay una frequencia. En este caso, no se establecerá una función de agregación, ya que no habrá una clave definida.
El resto de valores indicará cómo agregamos los datos en una frequencia seleccionada. En este caso, los timestamps se "redondearán" al periodo/frecuencia a la que pertenecen (al igual que hacemos actualmente para MongoDB en las timeseries).
Funciones de agregación: en el caso de seleccionar una frecuencia, se podrá seleccionar entre distintas funciones de agregación (LAST, SUM) para saber cómo agregar los datos de cada señal si se reciben más de un registro para un conjunto de TAGs y una frecuenca/timestap en concreto.

En el caso de MongoDB, se permiten crear distintas ventanas de agregación. Por cómo almacena los datos TimescaleDB, esto no tiene sentido. En el caso de TimescaleDB solo se permitirá definir una ventana temporal por timeserie.

Una vez definida la ventana, generaremos la consulta de creación de hypertabla, acorde con las selecciones anteriores. El usuario podrá cambiar lo que estime necesario (particiones, propiedades, etc) y en última instancia esto es lo que definirá la hypertabla en TimescaleDB

Agregados temporales continuos

Para TimescaleDB añadiremos la posibilidad de crear agregados continuos. Ésta es una funcionalidad propia de TimescaleDB que crea elementos similares a vistas materializadas de hypertablas.
Con ellas, podremos definir vistas que contengan los datos agregados de nuestras timeseries de manera temporal. Por ejemplo, si tenemos una timeserie cuyos datos son 15 minutales podemos definir un agregado continuo a nivel horario, para que las consultas que saquen información a ese nivel, vayan contra la nueva vista, siendo la consulta más optima.
Partiendo de los ejemplos anteriores, vamos a crear un agregado horario sobre la timeserie "timeserieExample", sacando la media, máximo, mínimo, último y primer valor de la señal "signal1".

CREATE MATERIALIZED VIEW timeserieExample_hourly
WITH (timescaledb.continuous) AS
SELECT tag1, 
tag2,
time_bucket(INTERVAL '1 hour', timestamp) AS timestamp,
AVG(signal1) as signal1Avg,
MAX(signal1) as signal1Max,
MIN(signal1) as signal1Min,
LAST(signal1, timestamp) as signal1Last,
FIRST(signal1,timestamp) as signal1First
FROM timeserieExample
GROUP BY tag1, tag2, time_bucket(INTERVAL '1 hour', timestamp);

Para ello accederemos a la edición de la entidad creada previamente

Seleccionaremos un nombre para la vista, que se concatenará con el nombre de la ontología timeserie con el carácter '_'. El campo "bucket Aggregation" indicará a que frecuencia de dato agregamos, en nuestro caso queremos un dato cada hora.

Con la selección inicial, se creará una plantilla donde el usuario podrá modificar los campos que quiere añadir al agregado (parte del SELECT). Es obligatorio tener los TAGS y bucket dentro del SELECT y GROUP BY para que la clave de consulta sea la misma. Es posible usar todas las funciones temporales que nos permite TimescaleDB como First y last que lo harán en base al intervalo de tiempo definido.

La actualización de la vista puede realizarse a mano, pero en nuestro caso, definiremos una política de actualización, que se encargará de refrescar los datos según definamos:

Schedule Policy: Cada cuanto se recalcula el agregado.
Start Offset y End offset : definen el intervalo de tiempo relativo al momento actual (de ejecución) que cogerá el proceso. En el ejemplo recalcularemos solo los datos del último mes.

Esta planificación acabará lanzando la siguiente orden a TimescaleDB:

SELECT add_continuous_aggregate_policy('timeserieExample_hourly',
start_offset => INTERVAL '1 month',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 minute');

Añadimos el tema del PUSH de otra señal

Gestión de CHUNKS, compresión y políticas de borrado

En proceso: paso a Cold Data

En cuanto al paso a la BBDD Cold, a falta de investigar un poco más a fondo, con las mismas herramientas de psql se puede hacer un COPY a csv por ejemplo:

psql -d old_db \ -c "\COPY (SELECT * FROM conditions) TO data.csv DELIMITER ',' CSV"

En el GitHub hay propuestas de un export_chnks() pero está rechazado porque según ellos se puede hacer con user defined actions, lo que podría implicar ue hacerlo con herramientas de Postgres y luego hacer el drop_chunks() de TimescaleDB.