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

Neste tutorial, você usará clusters elásticos nos clusters elásticos do Banco de Dados do Azure para PostgreSQL para aprender a criar um painel em tempo real e paralelizar consultas.

• Pré-requisitos
• Usar o utilitário psql para criar um esquema
• Fragmentar tabelas entre nós
• Gerar dados de exemplo
• Executar rollups
• Consultar dados brutos e agregados
• Expirar dados

Pré-requisitos

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

• Criar um cluster elástico usando o portal
• Criar um cluster elástico usando Bicep
• Criar um cluster elástico com o modelo do ARM

Usar o utilitário psql para criar um esquema

Depois de conectado ao cluster elástico usando psql, você pode configurar seu cluster elástico. Este tutorial orienta você a ingerir dados de tráfego do Web Analytics e, em seguida, acumular os dados para fornecer painéis em tempo real com base nesses dados.

Agora você criará uma tabela para consumir todos os dados brutos do tráfego da Web. Execute os seguintes comandos no terminal do 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
);

Você também criará uma tabela com os agregados por minuto e outra para manter a posição do último valor acumulado. Execute os seguintes comandos em 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))
);

Agora é possível ver as tabelas recém-criadas na lista de tabelas com este comando do psql:

\dt

Fragmentar tabelas entre 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. Essa "coluna de distribuição" marca como os dados são fragmentados entre nós.

Vamos definir a coluna de distribuição (chave de partição) 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

A distribuição de tabelas ou o uso de fragmentação baseada em esquema é necessário para aproveitar os clusters elásticos dos recursos de desempenho do Banco de Dados do Azure para PostgreSQL. Até distribuir tabelas ou esquemas, os nós de cluster não executarão consultas distribuídas envolvendo seus dados.

Gerar dados de exemplo

Agora, nosso cluster deve estar pronto para ingerir alguns dados. Podemos executar o seguinte localmente da nossa conexão psql 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 por segundo. As linhas são armazenadas em diferentes nós de trabalho com base em suas colunas de distribuição, site_id.

Observação

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

Query

Os clusters elásticos do Banco de Dados do Azure para PostgreSQL permitem que vários nós processem consultas em paralelo para obter velocidade. Por exemplo, o banco de dados calcula agregações como SUM e COUNT nos nós de trabalho e combina os resultados em uma resposta final.

Aqui está uma consulta para contagem de solicitações da Web por minuto, juntamente com algumas estatísticas. Tente executá-la no 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 o desempenho diminuirá conforme os dados são escalados. Até mesmo com o processamento distribuído, é mais rápido pré-computar os dados do que recalculá-los repetidamente.

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

Para facilitar a execução desse roll-up, vamos colocá-lo em uma função plpgsql. Execute estes comandos no psql para criar a função 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;

Com nossa função configurada, execute-a para acumular os dados.

SELECT rollup_http_request();

Além disso, ao ter dados em um formato pré-agregado, é possível consultar a tabela de valor acumulado 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;

Expirar dados antigos

Os rollups realizam consultas mais rapidamente, porém ainda é necessário expirar os dados antigos para evitar custos de armazenamento não associados. Decida por quanto tempo você quer manter os dados para cada granularidade e usar consultas padrão para excluir dados expirados. No exemplo a seguir: optamos por manter os dados brutos para um dia e agregações por minuto para 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';

Em produção, você pode encapsular essas consultas em uma função e chamá-la a cada minuto em um trabalho de Cron.

Próxima etapa

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

Saiba mais sobre clusters elásticos