Tutorial: projetar um painel de análise em tempo real usando o Azure Cosmos DB for PostgreSQL

  • Artigo

APLICA-SE AO: Azure Cosmos DB for PostgreSQL (da plataforma da extensão de dados Citus para PostgreSQL)

Neste tutorial, você usará o Azure Cosmos DB for PostgreSQL para saber como:

  • Criar um cluster
  • 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

Se você não tiver uma assinatura do Azure, crie uma conta gratuita antes de começar.

Criar um cluster

Entre no portal do Azure e siga estas etapas para criar um cluster do Azure Cosmos DB for PostgreSQL:

Acesse Criar cluster do Azure Cosmos DB for PostgreSQL no portal do Azure.

No formulário Criar cluster do Azure Cosmos DB for PostgreSQL:

  1. Preencha as informações na guia Básico.

    Screenshot showing the Basics tab of the Create screen.

    A maioria das opções é auto-explicativa, mas tenha em mente que:

    • O nome do cluster determina o nome DNS que os aplicativos usarão para estabelecer conexão, no formato <node-qualifier>-<clustername>.<uniqueID>.postgres.cosmos.azure.com.
    • Você pode escolher uma versão principal do PostgreSQL, como a 15. O Azure Cosmos DB for PostgreSQL sempre dá suporte para a versão mais recente do Citus para a versão principal do Postgres selecionada.
    • O nome de usuário do administrador precisa ser o valor citus.
    • Você pode deixar o nome do banco de dados com o valor padrão "citus" ou definir seu próprio nome de banco de dados. O banco de dados não pode ser renomeado após o provisionamento do cluster.

  2. Selecione Avançar: Rede na parte inferior da tela.

  3. Na tela Rede, selecione Permitir acesso público de serviços e recursos no Azure a este cluster.

    Screenshot showing the Networking tab of the Create screen.

  4. Selecione Revisar + criar e, quando a validação for aprovada, selecione Criar para criar o cluster.

  5. O provisionamento demora alguns minutos. A página redirecionará você para monitorar a implantação. Quando o status mudar de A implantação está em andamento para A implantação foi concluída, clique em Acessar recurso.

Usar o utilitário psql para criar um esquema

Depois de conectado ao Azure Cosmos DB for PostgreSQL usando psql, você pode concluir algumas tarefas básicas. 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.

Vamos criar uma tabela que consumirá todos os nossos dados brutos de 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
);

Também vamos criar uma tabela que conterá nossas agregações por minuto e uma tabela que mantém a posição do nosso último rollup. 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 do Azure Cosmos DB for PostgreSQL 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 como site_id, a chave de fragmento. No psql, execute estas funções:

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

Importante

A distribuição de tabelas ou o uso de esquema baseado em fragmentos é necessário para aproveitar os recursos de desempenho do Azure Cosmos DB for PostgreSQL. Se você não distribuir tabelas ou esquemas, os nós de trabalho não poderão ajudar a executar consultas que envolvam suas tabelas.

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, conforme direcionado pela 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 do psql para os comandos restantes neste tutorial.

Consulta

O Azure Cosmos DB for PostgreSQL permite 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 em 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, acumulando regularmente os dados brutos em uma tabela de agregação. 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 executar esse pacote cumulativo mais facilmente, 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 no local, execute-a para acumular os dados:

SELECT rollup_http_request();

E com nossos dados em um formulário pré-agregado, é possível consultar a tabela de rollup 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, decidimos manter os dados brutos de um dia e agregações por minuto de 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, é possível encapsular essas consultas em uma função e chamá-la a cada minuto em um trabalho de Cron.

Limpar os recursos

Nas etapas anteriores, você criou recursos do Azure em um cluster. Caso esses recursos não sejam necessários no futuro, exclua o cluster. Pressione o botão Excluir na página Visão geral de seu cluster. Quando solicitado em uma página pop-up, confirme o nome do cluster e clique no botão final Excluir.

Próximas etapas

Neste tutorial, você aprendeu a provisionar um cluster. 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.