Tutorial: Diseño de un panel de análisis en tiempo real on Azure Database for PostgreSQL: hiperescala (Citus) (versión preliminar)Tutorial: Design a real-time analytics dashboard by using Azure Database for PostgreSQL – Hyperscale (Citus) (preview)

En este tutorial, usa Azure Database for PostgreSQL: hiperescala (Citus) (versión preliminar) para obtener información sobre lo siguiente:In this tutorial, you use Azure Database for PostgreSQL - Hyperscale (Citus) (preview) to learn how to:

  • Creación de un grupo de servidores Hiperescala (Citus)Create a Hyperscale (Citus) server group
  • Uso de la utilidad psql para crear un esquemaUse psql utility to create a schema
  • Particiones de tablas entre nodosShard tables across nodes
  • Generación de datos de ejemploGenerate sample data
  • Realización de acumulacionesPerform rollups
  • Consulta de datos sin procesar y agregadosQuery raw and aggregated data
  • Expiración de los datosExpire data

Requisitos previosPrerequisites

Si no tiene una suscripción a Azure, cree una cuenta gratuita antes de empezar.If you don't have an Azure subscription, create a free account before you begin.

Inicio de sesión en Azure PortalSign in to the Azure portal

Inicie sesión en el Azure Portal.Sign in to the Azure portal.

Creación de una instancia de Azure Database for PostgreSQL: Hiperescala (Citus)Create an Azure Database for PostgreSQL - Hyperscale (Citus)

Para crear un servidor de Azure Database for PostgreSQL, siga estos pasos:Follow these steps to create an Azure Database for PostgreSQL server:

  1. Haga clic en Crear un recurso de la esquina superior izquierda de Azure Portal.Click Create a resource in the upper left-hand corner of the Azure portal.

  2. En la página Nuevo, seleccione Bases de datos y, en la página Bases de datos, seleccione Azure Database for PostgreSQL.Select Databases from the New page, and select Azure Database for PostgreSQL from the Databases page.

  3. Para la opción de implementación, haga clic en el botón Crear en Hyperscale (Citus) server group - PREVIEW (Grupo de servidores Hiperescala (Citus): VERSIÓN PRELIMINAR).For the deployment option, click the Create button under Hyperscale (Citus) server group - PREVIEW.

  4. Rellene el formulario del nuevo servidor con la siguiente información:Fill out the new server details form with the following information:

    • Grupo de recursos: haga clic en el vínculo Crear nuevo que está debajo de este cuadro de texto para este campo.Resource group: click the Create new link below the text box for this field. Escriba un nombre como myresourcegroup.Enter a name such as myresourcegroup.
    • Nombre del grupo de servidores: escriba un nombre exclusivo para el nuevo grupo de servidores, que también se usará para un subdominio de servidor.Server group name: enter a unique name for the new server group, which will also be used for a server subdomain.
    • Nombre de usuario de administrador: actualmente, se necesita el valor citus y no se puede cambiar.Admin username: currently required to be the value citus, and can't be changed.
    • Contraseña: tiene que tener al menos ocho caracteres de las tres categorías siguientes: letras mayúsculas del alfabeto inglés, letras minúsculas del alfabeto inglés, números (0-9) y caracteres no alfanuméricos (!, $, # o %, entre otros)Password: must be at least eight characters long and contain characters from three of the following categories – English uppercase letters, English lowercase letters, numbers (0-9), and non-alphanumeric characters (!, $, #, %, and so on.)
    • Ubicación: use la ubicación más cercana a los usuarios para que puedan acceder de la forma más rápida posible a los datos.Location: use the location that is closest to your users to give them the fastest access to the data.

    Importante

    Se precisa la contraseña de administrador del servidor que especifique aquí para iniciar sesión en el servidor y sus bases de datos.The server admin password that you specify here is required to log in to the server and its databases. Recuerde o grabe esta información para su uso posterior.Remember or record this information for later use.

  5. Haga clic en Configurar grupo de servidores.Click Configure server group. No modifique los valores de esa sección y haga clic en Guardar.Leave the settings in that section unchanged and click Save.

  6. Haga clic en Revisar y crear y luego en Crear para aprovisionar el servidor.Click Review + create and then Create to provision the server. El aprovisionamiento tarda unos minutos.Provisioning takes a few minutes.

  7. La página irá a la supervisión de la implementación.The page will redirect to monitor deployment. Cuando el estado activo cambia de La implementación está en curso a Se completó la implementación, haga clic en el elemento de menú Salidas que se encuentra a la izquierda de la página.When the live status changes from Your deployment is underway to Your deployment is complete, click the Outputs menu item on the left of the page.

  8. La página de resultados incluirá un nombre de host de coordinación junto a un botón para copiar el valor en el Portapapeles.The outputs page will contain a coordinator hostname with a button next to it to copy the value to the clipboard. Anote esta información para usarla más adelante.Record this information for later use.

Configuración de una regla de firewall de nivel de servidorConfigure a server-level firewall rule

El servicio Azure Database for PostgreSQL Hiperescala (Citus) (versión preliminar) usa un firewall en el nivel de servidor.The Azure Database for PostgreSQL – Hyperscale (Citus) (preview) service uses a firewall at the server-level. De manera predeterminada, el firewall impide que todas las herramientas y aplicaciones externas se conecten al nodo de coordinación y a cualquier base de datos interna.By default, the firewall prevents all external applications and tools from connecting to the coordinator node and any databases inside. Tenemos que agregar una regla para abrir el firewall en un intervalo específico de direcciones IP.We must add a rule to open the firewall for a specific IP address range.

  1. Desde la sección Salidas donde anteriormente copió el nombre de host del nodo de coordinación, haga clic en Atrás para volver al elemento de menú Información general.From the Outputs section where you previously copied the coordinator node hostname, click back into the Overview menu item.

  2. Busque el nombre del grupo de servidores de la implementación y haga clic en él.Find the name of your deployment's server group and click it. (El nombre del grupo de servidores no tendrá un sufijo.(The server group name will not have a suffix. Los elementos con nombres que terminan en, por ejemplo, "-c", "-w0" o "-w1" no son el grupo de servidores).Items with names ending in, for example, "-c", "-w0", or "-w1" are not the server group.)

  3. Haga clic en Firewall en Seguridad, en el menú de la izquierda.Click Firewall under Security in the left-hand menu.

  4. Haga clic en el vínculo + Agregar regla de firewall para la dirección IP del cliente actual.Click the link + Add firewall rule for current client IP address.

  5. Por último, haga clic en el botón Guardar.Finally, click the Save button.

    Nota

    El servidor Azure PostgreSQL se comunica a través de puerto 5432.Azure PostgreSQL server communicates over port 5432. Si intenta conectarse desde una red corporativa, es posible que el firewall de la red no permita el tráfico saliente a través del puerto 5432.If you are trying to connect from within a corporate network, outbound traffic over port 5432 may not be allowed by your network's firewall. En ese caso, no puede conectarse al servidor de Azure SQL Database, salvo que el departamento de TI abra el puerto 5432.If so, you cannot connect to your Azure SQL Database server unless your IT department opens port 5432.

Conexión a la base de datos mediante psqlConnect to the database using psql

Al crear el servidor de Azure Database for PostgreSQL, también se crea la base de datos predeterminada denominada citus.When you create your Azure Database for PostgreSQL server, a default database named citus is created. Para conectarse al servidor de bases de datos, necesita una cadena de conexión y la contraseña de administrador.To connect to your database server, you need a connection string and the admin password.

  1. Obtenga la cadena de conexión.Obtain the connection string. En la página del grupo de servidores, haga clic en el elemento de menú Cadenas de conexión.In the server group page click the Connection strings menu item. (Se encuentra en Configuración). Busque la cadena marcada como C++ (libpq) .(It's under Settings.) Find the string marked C++ (libpq). Tendrá el formato:It will be of the form:

    host=hostname.postgres.database.azure.com port=5432 dbname=citus user=citus password={your_password} sslmode=require
    

    Copie la cadena.Copy the string. Deberá reemplazar "{su_contraseña}" por la contraseña administrativa que eligió anteriormente.You will need to replace "{your_password}" with the administrative password you chose earlier. El sistema no almacena la contraseña de texto no cifrado y, por lo tanto, no puede mostrarla en la cadena de conexión.The system doesn't store your plaintext password and so can't display it for you in the connection string.

  2. Abra una ventana del terminal en el equipo local.Open a terminal window on your local computer.

  3. En el símbolo de sistema, conéctese al servidor de Azure Database for PostgreSQL con la utilidad psql.At the prompt, connect to your Azure Database for PostgreSQL server with the psql utility. Pase la cadena de conexión entre comillas, asegurándose de que contiene la contraseña:Pass your connection string in quotes, being sure it contains your password:

    psql "{connection_string}"
    

    Por ejemplo, el siguiente comando se conecta al nodo de coordinación del grupo de servidores mydemoserver:For example, the following command connects to the coordinator node of the server group mydemoserver:

    psql "host=mydemoserver-c.postgres.database.azure.com port=5432 dbname=citus user=citus password={your_password} sslmode=require"
    

Uso de la utilidad psql para crear un esquemaUse psql utility to create a schema

Una vez conectado a Azure Database for PostgreSQL: hiperescala (Citus) (versión preliminar) mediante psql, puede completar algunas tareas básicas.Once connected to the Azure Database for PostgreSQL - Hyperscale (Citus) (preview) using psql, you can complete some basic tasks. 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.This tutorial walks you through ingesting traffic data from web analytics, then rolling up the data to provide real-time dashboards based on that data.

Vamos a crear una tabla que consumirá todos los datos de tráfico web sin procesar.Let's create a table that will consume all of our raw web traffic data. Ejecute los comandos siguientes en el terminal psql:Run the following commands in the psql terminal:

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 contendrá los agregados por minuto y una tabla que conserva la posición de la última acumulación.We're also going to create a table that will hold our per-minute aggregates, and a table that maintains the position of our last rollup. Ejecute los siguientes comandos en psql:Run the following commands in psql as well:

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:You can see the newly created tables in the list of tables now with this psql command:

\dt

Particiones de tablas entre nodosShard tables across nodes

Una implementación de hiperescala almacena filas de tablas en distintos nodos en función del valor de una columna designada por el usuario.A hyperscale deployment stores table rows on different nodes based on the value of a user-designated column. Esta "columna de distribución" marca cómo los datos se particionan entre los distintos nodos.This "distribution column" marks how data is sharded across nodes.

Establezcamos que la columna de distribución sea site_id, la clave de partición.Let's set the distribution column to be site_id, the shard key. En psql, ejecute estas funciones:In psql, run these functions:

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

Generación de datos de ejemploGenerate sample data

Ahora, el grupo de servidores debería estar listo para ingerir algunos datos.Now our server group should be ready to ingest some data. Podemos ejecutar lo siguiente de manera local desde la conexión de psql para insertar datos continuamente.We can run the following locally from our psql connection to continuously insert data.

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.The query inserts approximately eight rows every second. Las filas se almacenan en distintos nodos de trabajo según las indicaciones de la columna de distribución, site_id.The rows are stored on different worker nodes as directed by the distribution column, 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.Leave the data generation query running, and open a second psql connection for the remaining commands in this tutorial.

ConsultarQuery

La opción de hospedaje de hiperescala permite que varios nodos procesen consultas en paralelo para así acelerar el proceso.The hyperscale hosting option allows multiple nodes to process queries in parallel for speed. Por ejemplo, la base de datos calcula agregados como SUM y COUNT en nodos de trabajo y combina los resultados en una respuesta final.For instance, the database calculates aggregates like SUM and COUNT on worker nodes, and combines the results into a final answer.

Esta es una consulta para contar las solicitudes web por minuto junto con algunas estadísticas.Here's a query to count web requests per minute along with a few statistics. Intente ejecutarla en psql y observe los resultados.Try running it in psql and observe the results.

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 datosRolling up data

La consulta anterior funciona bien en las primeras etapas, pero su rendimiento se degrada a medida que aumentan los datos.The previous query works fine in the early stages, but its performance degrades as your data scales. Incluso con el procesamiento distribuido, resulta más rápido calcular previamente estos datos que volver a calcularlos varias veces.Even with distributed processing, it's faster to pre-compute the data than to recalculate it repeatedly.

Es posible garantizar que el panel siga siendo rápido si acumulamos de manera frecuente los datos sin procesar en una tabla de agregado.We can ensure our dashboard stays fast by regularly rolling up the raw data into an aggregate table. Puede experimentar con la duración de la agregación.You can experiment with the aggregation duration. Hemos utilizado una tabla de agregación por minuto, pero los datos se pueden dividir en 5, 15 o 60 minutos en su lugar.We used a per-minute aggregation table, but you could break data into 5, 15, or 60 minutes instead.

Para ejecutar esta acumulación más fácilmente, vamos a ponerlo en una función plpgsql.To run this roll-up more easily, we're going to put it into a plpgsql function. Ejecute estos comandos en psql para crear la función rollup_http_request.Run these commands in psql to create the rollup_http_request function.

-- 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:With our function in place, execute it to roll up the data:

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.And with our data in a pre-aggregated form we can query the rollup table to get the same report as earlier. Ejecute la siguiente consulta:Run the following query:

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 antiguosExpiring old data

Las acumulaciones agilizan las consultas, pero de todos modos es necesario que los datos antiguos expiren para evitar costos de almacenamiento ilimitados.The rollups make queries faster, but we still need to expire old data to avoid unbounded storage costs. Debe decidir durante cuánto tiempo quiere conservar los datos para cada granularidad y usar consultas estándar para eliminar los datos expirados.Decide how long you’d like to keep data for each granularity, and use standard queries to delete expired data. En el ejemplo siguiente, decidimos conservar los datos sin procesar durante un día y las agregaciones por minuto, durante un mes:In the following example, we decided to keep raw data for one day, and per-minute aggregations for one month:

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.In production, you could wrap these queries in a function and call it every minute in a cron job.

Limpieza de recursosClean up resources

En los pasos anteriores, creó recursos de Azure en un grupo de servidores.In the preceding steps, you created Azure resources in a server group. Si no cree que vaya a necesitar estos recursos en el futuro, elimine el grupo de servidores.If you don't expect to need these resources in the future, delete the server group. Presione el botón Eliminar en la página Información general del grupo de servidores.Press the Delete button in the Overview page for your server group. Cuando aparezca una página emergente en la que se le pida hacerlo, confirme el nombre del grupo de servidores y haga clic en el botón Eliminar final.When prompted on a pop-up page, confirm the name of the server group and click the final Delete button.

Pasos siguientesNext steps

En este tutorial, aprendió a aprovisionar un grupo de servidores Hiperescala (Citus).In this tutorial, you learned how to provision a Hyperscale (Citus) server group. Se conectó a él con psql, creó un esquema y distribuyó datos.You connected to it with psql, created a schema, and distributed data. Aprendió a consultar datos en formato sin procesar, agregar esos datos de manera habitual, consultar las tablas de agregados y hacer expirar los datos antiguos.You learned to query data both in the raw form, regularly aggregate that data, query the aggregated tables, and expire old data.

A continuación, conozca los conceptos de hiperescala.Next, learn about the concepts of hyperscale.