Tutorial: Diseño de un panel de análisis en tiempo real con el clúster elástico

2024-11-19

SE APLICA A: Azure Database for PostgreSQL con servidor flexible

En este tutorial, usará el clúster elástico en Azure Database for PostgreSQL para aprender a diseñar un panel en tiempo real y paralelizar consultas.

Requisitos previos
Uso de la utilidad psql para crear un esquema
Particiones de tablas entre nodos
Generación de datos de ejemplo
Realización de acumulaciones
Consulta de datos sin procesar y agregados
Expiración de los datos

Requisitos previos

Cree un clúster elástico de una de las maneras siguientes:

Uso de la utilidad psql para crear un esquema

Una vez conectado al clúster elástico mediante psql, puede completar algunas tareas básicas. Este tutorial lo guiará a través de la ingesta de datos de tráfico provenientes del análisis web y posterior acumulación de los datos para proporcionar paneles en tiempo real basados en esos datos.

Vamos a crear una tabla que consuma todos nuestros datos de tráfico web sin procesar. Ejecute los comandos siguientes en el terminal psql:

CREATE TABLE http_request (
  site_id INT,
  ingest_time TIMESTAMPTZ DEFAULT now(),

  url TEXT,
  request_country TEXT,
  ip_address TEXT,

  status_code INT,
  response_time_msec INT
);

También vamos a crear una tabla que contenga los agregados por minuto y una tabla que mantenga la posición de nuestro último paquete acumulativo. Ejecute los siguientes comandos en psql:

CREATE TABLE http_request_1min (
  site_id INT,
  ingest_time TIMESTAMPTZ, -- which minute this row represents

  error_count INT,
  success_count INT,
  request_count INT,
  average_response_time_msec INT,
  CHECK (request_count = error_count + success_count),
  CHECK (ingest_time = date_trunc('minute', ingest_time))
);

CREATE INDEX http_request_1min_idx ON http_request_1min (site_id, ingest_time);

CREATE TABLE latest_rollup (
  minute timestamptz PRIMARY KEY,

  CHECK (minute = date_trunc('minute', minute))
);

Ahora puede ver las tablas recién creadas en la lista de tablas con este comando de psql:

\dt

Particiones de tablas entre nodos

Una implementación de clúster elástico almacena filas de tabla en distintos nodos en función del valor de una columna designada por el usuario. Esta "columna de distribución" marca cómo los datos se particionan entre los distintos nodos.

Establezcamos la columna de distribución en site_id, la clave de la partición. En psql, ejecute estas funciones:

SELECT create_distributed_table('http_request',      'site_id');
SELECT create_distributed_table('http_request_1min', 'site_id');

Nota:

Es necesario distribuir tablas o usar particiones basadas en esquemas para aprovechar las ventajas de los clústeres elásticos con características de rendimiento de Azure Database for PostgreSQL. Si no distribuye tablas o esquemas, los nodos no pueden ayudar a ejecutar consultas que impliquen sus datos.

Generación de datos de ejemplo

Ahora, nuestro clúster debería estar listo para ingerir algunos datos. Podemos ejecutar lo siguiente de manera local desde la conexión de psql para insertar datos continuamente.

DO $$
  BEGIN LOOP
    INSERT INTO http_request (
      site_id, ingest_time, url, request_country,
      ip_address, status_code, response_time_msec
    ) VALUES (
      trunc(random()*32), clock_timestamp(),
      concat('http://example.com/', md5(random()::text)),
      ('{China,India,USA,Indonesia}'::text[])[ceil(random()*4)],
      concat(
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2)
      )::inet,
      ('{200,404}'::int[])[ceil(random()*2)],
      5+trunc(random()*150)
    );
    COMMIT;
    PERFORM pg_sleep(random() * 0.25);
  END LOOP;
END $$;

La consulta inserta aproximadamente ocho filas cada segundo. Las filas se almacenan en distintos nodos de trabajo según las indicaciones de la columna de distribución, site_id.

Nota

Deje que se ejecute la consulta de generación de datos y abra una segunda conexión de psql para el resto de los comandos de este tutorial.

Consultar

Azure Database for PostgreSQL con el clúster elástico permite que varios nodos procesen consultas en paralelo para obtener velocidad. Por ejemplo, la base de datos calcula agregados como SUM y COUNT en nodos de trabajo y combina los resultados en una respuesta final.

Esta es una consulta para contar las solicitudes web por minuto junto con algunas estadísticas. Intente ejecutarla en psql y observe los resultados.

SELECT
  site_id,
  date_trunc('minute', ingest_time) as minute,
  COUNT(1) AS request_count,
  SUM(CASE WHEN (status_code between 200 and 299) THEN 1 ELSE 0 END) as success_count,
  SUM(CASE WHEN (status_code between 200 and 299) THEN 0 ELSE 1 END) as error_count,
  SUM(response_time_msec) / COUNT(1) AS average_response_time_msec
FROM http_request
WHERE date_trunc('minute', ingest_time) > now() - '5 minutes'::interval
GROUP BY site_id, minute
ORDER BY minute ASC;

Acumulación de datos

La consulta anterior funciona bien en las primeras etapas, pero su rendimiento se degrada a medida que aumentan los datos. Incluso con el procesamiento distribuido, resulta más rápido calcular previamente estos datos que volver a calcularlos varias veces.

Es posible garantizar que el panel siga siendo rápido si acumulamos de manera frecuente los datos sin procesar en una tabla de agregado. Puede experimentar con la duración de la agregación. Hemos utilizado una tabla de agregación por minuto, pero los datos se pueden dividir en 5, 15 o 60 minutos en su lugar.

Para ejecutar esta acumulación más fácilmente, vamos a ponerlo en una función plpgsql. Ejecute estos comandos en psql para crear la función rollup_http_request.

-- initialize to a time long ago
INSERT INTO latest_rollup VALUES ('10-10-1901');

-- function to do the rollup
CREATE OR REPLACE FUNCTION rollup_http_request() RETURNS void AS $$
DECLARE
  curr_rollup_time timestamptz := date_trunc('minute', now());
  last_rollup_time timestamptz := minute from latest_rollup;
BEGIN
  INSERT INTO http_request_1min (
    site_id, ingest_time, request_count,
    success_count, error_count, average_response_time_msec
  ) SELECT
    site_id,
    date_trunc('minute', ingest_time),
    COUNT(1) as request_count,
    SUM(CASE WHEN (status_code between 200 and 299) THEN 1 ELSE 0 END) as success_count,
    SUM(CASE WHEN (status_code between 200 and 299) THEN 0 ELSE 1 END) as error_count,
    SUM(response_time_msec) / COUNT(1) AS average_response_time_msec
  FROM http_request
  -- roll up only data new since last_rollup_time
  WHERE date_trunc('minute', ingest_time) <@
          tstzrange(last_rollup_time, curr_rollup_time, '(]')
  GROUP BY 1, 2;

  -- update the value in latest_rollup so that next time we run the
  -- rollup it will operate on data newer than curr_rollup_time
  UPDATE latest_rollup SET minute = curr_rollup_time;
END;
$$ LANGUAGE plpgsql;

Una vez agregada la función, ejecútela para acumular los datos:

SELECT rollup_http_request();

Y, con los datos en un formulario previamente agregado, podemos consultar la tabla de acumulación para obtener el mismo informe que antes. Ejecute la siguiente consulta:

SELECT site_id, ingest_time as minute, request_count,
       success_count, error_count, average_response_time_msec
  FROM http_request_1min
 WHERE ingest_time > date_trunc('minute', now()) - '5 minutes'::interval;

Expiración de datos antiguos

Las acumulaciones agilizan las consultas, pero de todos modos es necesario que los datos antiguos expiren para evitar costos de almacenamiento ilimitados. Debe decidir durante cuánto tiempo quiere conservar los datos para cada granularidad y usar consultas estándar para eliminar los datos expirados. En el ejemplo siguiente, decidimos conservar los datos sin procesar durante un día y las agregaciones por minuto, durante un mes:

DELETE FROM http_request WHERE ingest_time < now() - interval '1 day';
DELETE FROM http_request_1min WHERE ingest_time < now() - interval '1 month';

En el entorno de producción, podría encapsular estas consultas en una función y llamarla cada minuto en un trabajo de cron.

Paso siguiente

En este tutorial, ha aprendido a crear un clúster elástico. Se conectó a él con psql, creó un esquema y distribuyó datos. Ha aprendido a consultar datos en formato sin procesar, a agregar esos datos de manera habitual, a consultar las tablas de agregados y a hacer expirar los datos antiguos.

Más información sobre los clústeres elásticos

Compartir a través de