Tutorial: Desenhe um painel de análise em tempo real com clusters elásticos

Neste tutorial, utiliza clusters elásticos do Azure Database para PostgreSQL para aprender a projetar um painel em tempo real e paralelizar consultas.

Pré-requisitos
Use o utilitário psql para criar um esquema
Particionar tabelas pelos nós
Gerar dados de exemplo
Executar agregações
Consultar dados brutos e agregados
Dados de expiração

Pré-requisitos

Crie um cluster elástico de uma das seguintes maneiras:

Use o utilitário psql para criar um esquema

Uma vez ligado ao cluster elástico usando psql, pode configurar o seu cluster elástico. Este tutorial guia o leitor pela ingestão de dados de tráfego das análises da web e, em seguida, agrega os dados para fornecer painéis em tempo real com base nesses dados.

Vamos criar uma tabela que consuma todos os nossos dados brutos de tráfego da Web. Execute os seguintes comandos no 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
);

Também vamos criar uma tabela que contém nossos agregados por minuto e uma tabela que mantém a posição do nosso último rollup. Execute os seguintes comandos no psql também:

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))
);

Você pode ver as tabelas recém-criadas na lista de tabelas agora com este comando psql:

\dt

Particionar tabelas pelos nós

Uma implantação de cluster elástico armazena linhas de tabela em nós diferentes com base no valor de uma coluna designada pelo usuário. Esta "coluna de distribuição" indica como os dados são fragmentados entre os nós.

Vamos configurar a coluna de distribuição (chave do fragmento) como site_id. No psql, execute estas funções:

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

Observação

Distribuir tabelas ou usar fragmentação baseada em esquema é necessário para tirar partido dos clusters elásticos do Azure Database para as funcionalidades de desempenho do PostgreSQL. Até distribuires tabelas ou esquemas, os teus nós do cluster não irão executar consultas distribuídas envolvendo os seus dados.

Gerar dados de exemplo

Agora, nosso cluster deve estar pronto para ingerir alguns dados. Podemos executar o seguinte localmente a partir da nossa psql conexão para inserir dados 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 $$;

A consulta insere aproximadamente oito linhas a cada segundo. As linhas são armazenadas em diferentes nós de trabalho em função da sua coluna de distribuição, site_id.

Observação

Deixe a consulta de geração de dados em execução e abra uma segunda conexão psql para os comandos restantes neste tutorial.

Query

Azure Database para clusters elásticos PostgreSQL permite que múltiplos nós processem consultas em paralelo para maior velocidade. Por exemplo, o banco de dados calcula agregados como "SUM" e "COUNT" em nós de processamento e combina os resultados numa resposta final.

Aqui está uma consulta para contar solicitações da Web por minuto, juntamente com algumas estatísticas. Tente executá-lo em psql e observe os 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;

Acumulando dados

A consulta anterior funciona bem nos estágios iniciais, mas seu desempenho diminui à medida que seus dados são dimensionados. Mesmo com processamento distribuído, é mais rápido pré-calcular os dados do que recalculá-los repetidamente.

Podemos garantir que nosso painel permaneça rápido acumulando regularmente os dados brutos em uma tabela agregada. Você pode experimentar a duração da agregação. Usamos uma tabela de agregação por minuto, mas você pode dividir os dados em 5, 15 ou 60 minutos.

Para executar esse roll-up mais facilmente, vamos colocá-lo em uma função plpgsql. Execute esses comandos em psql para criar a rollup_http_request função.

-- 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;

Com a nossa função implementada, execute-a para acumular os dados:

SELECT rollup_http_request();

E com nossos dados em um formulário pré-agregado, podemos consultar a tabela cumulativa para obter o mesmo relatório anterior. Execute a seguinte 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;

Expirando dados antigos

Os rollups tornam as consultas mais rápidas, mas ainda precisamos expirar dados antigos para evitar custos de armazenamento ilimitados. Decida por quanto tempo deseja manter os dados para cada granularidade e use consultas padrão para excluir dados expirados. No exemplo a seguir, decidimos manter dados brutos por um dia e agregações por minuto por um mês:

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

Na produção, pode-se encapsular estas consultas numa função e chamá-la a cada minuto num trabalho cron.

Próximo passo

Neste tutorial, você aprendeu como criar um cluster elástico. Você se conectou a ele com psql, criou um esquema e distribuiu dados. Você aprendeu a consultar dados na forma bruta, agregar regularmente esses dados, consultar as tabelas agregadas e expirar dados antigos.

Saiba mais sobre clusters elásticos

Feedback

Esta página foi útil?

Last updated on 2025-12-22