Tutorial: Desenhe um dashboard de análise em tempo real utilizando a Base de Dados Azure para PostgreSQL – Hyperscale (Citus)Tutorial: Design a real-time analytics dashboard by using Azure Database for PostgreSQL – Hyperscale (Citus)

Neste tutorial, utiliza a Base de Dados Azure para PostgreSQL - Hyperscale (Citus) para aprender a:In this tutorial, you use Azure Database for PostgreSQL - Hyperscale (Citus) to learn how to:

  • Criar um grupo do servidor Hyperscale (Citus)Create a Hyperscale (Citus) server group
  • Use a utilidade psql para criar um esquemaUse psql utility to create a schema
  • Mesas de caco em todos os nódososShard tables across nodes
  • Gerar dados de exemploGenerate sample data
  • Realizar rollupsPerform rollups
  • Consulta de dados crus e agregadosQuery raw and aggregated data
  • Dados expiradosExpire data

Pré-requisitosPrerequisites

Se não tiver uma subscrição do Azure, crie uma conta gratuita antes de começar.If you don't have an Azure subscription, create a free account before you begin.

Iniciar sessão no portal do AzureSign in to the Azure portal

Inicie sessão no portal do Azure.Sign in to the Azure portal.

Criar um banco de dados do Azure para PostgreSQL-Citus (hiperescala)Create an Azure Database for PostgreSQL - Hyperscale (Citus)

Siga estes passos para criar uma Base de Dados do Azure para o servidor PostgreSQL:Follow these steps to create an Azure Database for PostgreSQL server:

  1. Clique em Criar um recurso, no canto superior esquerdo do portal do Azure.Click Create a resource in the upper left-hand corner of the Azure portal.

  2. Selecione Bases de Dados, na página Nova, e selecione Base de Dados do Azure para o PostgreSQL, na página Bases de Dados.Select Databases from the New page, and select Azure Database for PostgreSQL from the Databases page.

  3. Para a opção de implantação, clique no botão criar em grupo de servidores de hiperescala (Citus).For the deployment option, click the Create button under Hyperscale (Citus) server group.

  4. Preencha o formulário com os detalhes do novo servidor com as seguintes informações:Fill out the new server details form with the following information:

    • Grupo de recursos: clique no link criar novo abaixo da caixa de texto deste campo.Resource group: click the Create new link below the text box for this field. Insira um nome como myresourceattribute.Enter a name such as myresourcegroup.
    • Nome do grupo de servidores: Insira um nome exclusivo para o novo grupo de servidores, que também será usado para um subdomínio de servidor.Server group name: enter a unique name for the new server group, which will also be used for a server subdomain.
    • Nome de usuário do administrador: atualmente, ele precisa ser o valor cituse não pode ser alterado.Admin username: currently required to be the value citus, and can't be changed.
    • Senha: deve ter pelo menos oito caracteres e conter caracteres de três das categorias a seguir – letras maiúsculas em inglês, letras minúsculas, números (0-9) e caracteres não alfanuméricos (!, $, #,% e assim por diante).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.)
    • Local: Use o local mais próximo de seus usuários para dar a eles o acesso mais rápido aos dados.Location: use the location that is closest to your users to give them the fastest access to the data.

    Importante

    A senha de administrador do servidor que você especificar aqui é necessária para fazer logon no servidor e em seus bancos de dados.The server admin password that you specify here is required to log in to the server and its databases. Lembre-se ou grave estas informações para utilização posterior.Remember or record this information for later use.

  5. Clique em Configurar grupo de servidores.Click Configure server group. Deixe as configurações dessa seção inalteradas e clique em salvar.Leave the settings in that section unchanged and click Save.

  6. Clique em Avançar: rede > na parte inferior da tela.Click Next : Networking > at the bottom of the screen.

  7. Na guia rede , clique no botão de opção ponto de extremidade público .In the Networking tab, click the Public endpoint radio button. ponto de extremidade público selecionadoPublic endpoint selected

  8. Clique no link + Adicionar endereço IP do cliente atual.Click the link + Add current client IP address. IP do cliente adicionadoAdded client IP

    Nota

    O servidor PostgreSQL do Azure comunica através da porta 5432.Azure PostgreSQL server communicates over port 5432. Se estiver a tentar ligar a partir de uma rede empresarial, o tráfego de saída através da porta 5432 poderá não ser permitido pela firewall da rede.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. Nesse caso, você não pode se conectar ao seu cluster de hiperescala (Citus), a menos que o departamento de ti Abra a porta 5432.If so, you cannot connect to your Hyperscale (Citus) cluster unless your IT department opens port 5432.

  9. Clique em revisar + criar e em criar para provisionar o servidor.Click Review + create and then Create to provision the server. O aprovisionamento demora alguns minutos.Provisioning takes a few minutes.

  10. A página será redirecionada para monitorar a implantação.The page will redirect to monitor deployment. Quando o status ao vivo for alterado de sua implantação estiver em andamento para a implantação ser concluída, clique no item de menu saídas à esquerda da 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.

  11. A página de saídas conterá um nome de host de coordenador com um botão ao lado dele para copiar o valor para a área de transferência.The outputs page will contain a coordinator hostname with a button next to it to copy the value to the clipboard. Registre essas informações para uso posterior.Record this information for later use.

Conectar-se ao banco de dados usando psqlConnect to the database using psql

Quando você cria o banco de dados do Azure para o servidor PostgreSQL, um banco de dados padrão chamado citus é criado.When you create your Azure Database for PostgreSQL server, a default database named citus is created. Para se conectar ao servidor de banco de dados, você precisa de uma cadeia de conexão e da senha de administrador.To connect to your database server, you need a connection string and the admin password.

  1. Obtenha a cadeia de conexão.Obtain the connection string. Na página grupo de servidores, clique no item de menu cadeias de conexão .In the server group page click the Connection strings menu item. (Está em configurações.) Localize a cadeia de caracteres marcada como psql.(It's under Settings.) Find the string marked psql. Ele estará no formato:It will be of the form:

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

    Copie a cadeia de caracteres.Copy the string. Será necessário substituir "{Your_password}" pela senha administrativa que você escolheu anteriormente.You will need to replace "{your_password}" with the administrative password you chose earlier. O sistema não armazena sua senha de texto não criptografado e, portanto, não pode exibi-la para você na cadeia de conexão.The system doesn't store your plaintext password and so can't display it for you in the connection string.

  2. Abra uma janela de terminal no computador local.Open a terminal window on your local computer.

  3. No prompt, conecte-se ao banco de dados do Azure para o servidor PostgreSQL com o utilitário psql .At the prompt, connect to your Azure Database for PostgreSQL server with the psql utility. Passe sua cadeia de conexão entre aspas, certificando-se de que ela contém sua senha:Pass your connection string in quotes, being sure it contains your password:

    psql "host=..."
    

    Por exemplo, o comando a seguir se conecta ao nó de coordenador do 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"
    

Use a utilidade psql para criar um esquemaUse psql utility to create a schema

Uma vez ligado à Base de Dados Azure para PostgreSQL - Hiperescala (Citus) utilizando o psql, pode completar algumas tarefas básicas.Once connected to the Azure Database for PostgreSQL - Hyperscale (Citus) using psql, you can complete some basic tasks. Este tutorial acompanha-o através da ingestão de dados de tráfego a partir da análise web, em seguida, rolando os dados para fornecer dashboards em tempo real com base em esses dados.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 criar uma tabela que consumirá todos os nossos dados de tráfego web crus.Let's create a table that will consume all of our raw web traffic data. Executar os seguintes comandos no 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
);

Também vamos criar uma mesa que vai manter os nossos agregados por minuto, e uma mesa que mantém a posição da nossa última rollup.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. Executar os seguintes comandos no PSQL também: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))
);

Pode ver as mesas recém-criadas na lista de tabelas agora com este comando psql:You can see the newly created tables in the list of tables now with this psql command:

\dt

Mesas de caco em todos os nódososShard tables across nodes

Uma implantação de hiperescala armazena linhas de mesa em diferentes nódosos com base no valor de uma coluna designada pelo utilizador.A hyperscale deployment stores table rows on different nodes based on the value of a user-designated column. Esta "coluna de distribuição" marca como os dados são espalhados por nós.This "distribution column" marks how data is sharded across nodes.

Vamos definir a coluna de distribuição para ser o local_id, a chave do fragmento.Let's set the distribution column to be site_id, the shard key. No psql, executar estas funções:In psql, run these functions:

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

Gerar dados de exemploGenerate sample data

Agora o nosso grupo de servidores deve estar pronto para ingerir alguns dados.Now our server group should be ready to ingest some data. Podemos executar o seguinte localmente a partir da nossa ligação psql para inserir continuamente dados.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 $$;

A consulta insere aproximadamente oito linhas a cada segundo.The query inserts approximately eight rows every second. As filas são armazenadas em diferentes nós de trabalhador, tal como dirigido pela coluna de distribuição, site_id.The rows are stored on different worker nodes as directed by the distribution column, site_id.

Nota

Deixe a consulta de geração de dados em execução, e abra uma segunda ligação psql para os restantes comandos neste tutorial.Leave the data generation query running, and open a second psql connection for the remaining commands in this tutorial.

ConsultaQuery

A opção de hospedagem de hiperescala permite que vários nódosos processem consultas paralelas à velocidade.The hyperscale hosting option allows multiple nodes to process queries in parallel for speed. Por exemplo, a base de dados calcula agregados como SUM e COUNT em nós de trabalhadores, e combina os resultados numa resposta final.For instance, the database calculates aggregates like SUM and COUNT on worker nodes, and combines the results into a final answer.

Aqui está uma consulta para contar pedidos web por minuto, juntamente com algumas estatísticas.Here's a query to count web requests per minute along with a few statistics. Tente executá-lo em psql e observe os 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;

Elaboração de dadosRolling up data

A consulta anterior funciona bem nas fases iniciais, mas o seu desempenho degrada-se à medida que os seus dados se degradam.The previous query works fine in the early stages, but its performance degrades as your data scales. Mesmo com o processamento distribuído, é mais rápido pré-calcular os dados do que recalculá-lo repetidamente.Even with distributed processing, it's faster to pre-compute the data than to recalculate it repeatedly.

Podemos garantir que o nosso dashboard se mantenha rápido, rolando regularmente os dados brutos numa tabela agregada.We can ensure our dashboard stays fast by regularly rolling up the raw data into an aggregate table. Pode experimentar a duração da agregação.You can experiment with the aggregation duration. Usamos uma tabela de agregação por minuto, mas pode quebrar os dados em 5, 15 ou 60 minutos.We used a per-minute aggregation table, but you could break data into 5, 15, or 60 minutes instead.

Para executar este roll-up mais facilmente, vamos colocá-lo em uma função plpgsql.To run this roll-up more easily, we're going to put it into a plpgsql function. Executar estes comandos em psql para criar a função 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;

Com a nossa função no lugar, execute-os para arregaçar os dados:With our function in place, execute it to roll up the data:

SELECT rollup_http_request();

E com os nossos dados de forma pré-agregada podemos consultar a mesa de rollup para obter o mesmo relatório de antes.And with our data in a pre-aggregated form we can query the rollup table to get the same report as earlier. Execute a seguinte 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;

Expirando dados antigosExpiring old data

Os rollups fazem consultas mais rápidas, mas ainda precisamos de expirar dados antigos para evitar custos de armazenamento ilimitados.The rollups make queries faster, but we still need to expire old data to avoid unbounded storage costs. Decida quanto tempo pretende manter os dados para cada granularidade e use consultas padrão para eliminar dados expirados.Decide how long you’d like to keep data for each granularity, and use standard queries to delete expired data. No exemplo seguinte, decidimos manter dados brutos por um dia, e agregações por minuto por um mês: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';

Na produção, pode embrulhar estas consultas numa função e chamá-las a cada minuto num trabalho de compadrio.In production, you could wrap these queries in a function and call it every minute in a cron job.

Limpar recursosClean up resources

Nos passos anteriores, criou os recursos do Azure num grupo de servidores.In the preceding steps, you created Azure resources in a server group. Se não espera precisar destes recursos no futuro, elimine o grupo de servidores.If you don't expect to need these resources in the future, delete the server group. Prima o botão Eliminar na página 'Visão Geral' para o seu grupo de servidores.Press the Delete button in the Overview page for your server group. Quando solicitado numa página pop-up, confirme o nome do grupo do servidor e clique no botão Eliminar final.When prompted on a pop-up page, confirm the name of the server group and click the final Delete button.

Passos SeguintesNext steps

Neste tutorial, aprendeu a fornecer um grupo de servidores de Hiperescala (Citus).In this tutorial, you learned how to provision a Hyperscale (Citus) server group. Ligaste-lhe com o PSQL, criaste um esquema e distribuíste dados.You connected to it with psql, created a schema, and distributed data. Aprendeu a consultar dados na forma bruta, agregar regularmente esses dados, consultar as tabelas agregadas e expirar dados antigos.You learned to query data in the raw form, regularly aggregate that data, query the aggregated tables, and expire old data.

Em seguida, aprenda sobre os conceitos de hiperescala.Next, learn about the concepts of hyperscale.