Projetar um banco de dados multilocatário usando o Azure Cosmos DB para PostgreSQL

APLICA-SE A: Azure Cosmos DB para PostgreSQL (alimentado pela extensão de banco de dados Citus para PostgreSQL)

Neste tutorial, você usa o Azure Cosmos DB para PostgreSQL para aprender a:

Pré-requisitos

Se não tiver uma subscrição 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 para PostgreSQL:

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

1. Preencha as informações no separador Básicas.

   

   A maioria das opções são autoexplicativas, mas tenha em atenção:

   • O nome do cluster determina o nome DNS que seus aplicativos usam para se conectar, 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 para PostgreSQL sempre suporta a versão mais recente do Citus para a versão principal selecionada do Postgres.
   • O nome de utilizador do administrador deve ser o valor citus.
   • Você pode deixar o nome do banco de dados em seu valor padrão 'citus' ou definir seu único nome de banco de dados. Não é possível renomear o banco de dados após o provisionamento do cluster.

2. Selecione Next : Networking na parte inferior da tela.

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

   

4. Selecione Analisar + criar e, quando a validação passar, selecione Criar para criar o cluster.

5. O aprovisionamento demora alguns minutos. A página redireciona para a monitorização da implementação. Quando o estado é alterado de Implementação a decorrer para A implementação está concluída, selecione Aceder ao recurso.

Use o utilitário psql para criar um esquema

Uma vez conectado ao Azure Cosmos DB para PostgreSQL usando psql, você pode concluir algumas tarefas básicas. Este tutorial orienta você na criação de um aplicativo Web que permite que os anunciantes acompanhem suas campanhas.

Várias empresas podem usar o aplicativo, então vamos criar uma tabela para segurar empresas e outra para suas campanhas. No console psql, execute estes comandos:

CREATE TABLE companies (
  id bigserial PRIMARY KEY,
  name text NOT NULL,
  image_url text,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL
);

CREATE TABLE campaigns (
  id bigserial,
  company_id bigint REFERENCES companies (id),
  name text NOT NULL,
  cost_model text NOT NULL,
  state text NOT NULL,
  monthly_budget bigint,
  blocked_site_urls text[],
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id)
);

Cada campanha pagará para veicular anúncios. Adicione uma tabela para anúncios também, executando o seguinte código em psql após o código acima:

CREATE TABLE ads (
  id bigserial,
  company_id bigint,
  campaign_id bigint,
  name text NOT NULL,
  image_url text,
  target_url text,
  impressions_count bigint DEFAULT 0,
  clicks_count bigint DEFAULT 0,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, campaign_id)
    REFERENCES campaigns (company_id, id)
);

Por fim, acompanharemos as estatísticas sobre cliques e impressões de cada anúncio:

CREATE TABLE clicks (
  id bigserial,
  company_id bigint,
  ad_id bigint,
  clicked_at timestamp without time zone NOT NULL,
  site_url text NOT NULL,
  cost_per_click_usd numeric(20,10),
  user_ip inet NOT NULL,
  user_data jsonb NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, ad_id)
    REFERENCES ads (company_id, id)
);

CREATE TABLE impressions (
  id bigserial,
  company_id bigint,
  ad_id bigint,
  seen_at timestamp without time zone NOT NULL,
  site_url text NOT NULL,
  cost_per_impression_usd numeric(20,10),
  user_ip inet NOT NULL,
  user_data jsonb NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, ad_id)
    REFERENCES ads (company_id, id)
);

Você pode ver as tabelas recém-criadas na lista de tabelas agora no psql executando:

\dt

Os aplicativos multilocatários podem impor exclusividade apenas por locatário, e é por isso que todas as chaves primária e estrangeira incluem o ID da empresa.

Tabelas de estilhaços entre nós

Uma implantação do Azure Cosmos DB para PostgreSQL armazena linhas de tabela em nós diferentes com base no valor de uma coluna designada pelo usuário. Esta "coluna de distribuição" marca qual locatário possui quais linhas.

Vamos definir a coluna de distribuição a ser company_id, o identificador do locatário. No psql, execute estas funções:

SELECT create_distributed_table('companies',   'id');
SELECT create_distributed_table('campaigns',   'company_id');
SELECT create_distributed_table('ads',         'company_id');
SELECT create_distributed_table('clicks',      'company_id');
SELECT create_distributed_table('impressions', 'company_id');

Ingerir dados de exemplo

Fora do psql agora, na linha de comando normal, baixe conjuntos de dados de exemplo:

for dataset in companies campaigns ads clicks impressions geo_ips; do
  curl -O https://examples.citusdata.com/mt_ref_arch/${dataset}.csv
done

De volta ao psql, carregue os dados em massa. Certifique-se de executar psql no mesmo diretório onde você baixou os arquivos de dados.

SET client_encoding TO 'UTF8';

\copy companies from 'companies.csv' with csv
\copy campaigns from 'campaigns.csv' with csv
\copy ads from 'ads.csv' with csv
\copy clicks from 'clicks.csv' with csv
\copy impressions from 'impressions.csv' with csv

Esses dados agora serão distribuídos entre os nós de trabalho.

Consultar dados do locatário

Quando o aplicativo solicita dados para um único locatário, o banco de dados pode executar a consulta em um único nó de trabalho. As consultas de inquilino único são filtradas por um único ID de inquilino. Por exemplo, os filtros de consulta a seguir para company_id = 5 anúncios e impressões. Tente executá-lo em psql para ver os resultados.

SELECT a.campaign_id,
       RANK() OVER (
         PARTITION BY a.campaign_id
         ORDER BY a.campaign_id, count(*) desc
       ), count(*) as n_impressions, a.id
  FROM ads as a
  JOIN impressions as i
    ON i.company_id = a.company_id
   AND i.ad_id      = a.id
 WHERE a.company_id = 5
GROUP BY a.campaign_id, a.id
ORDER BY a.campaign_id, n_impressions desc;

Partilhar dados entre inquilinos

Até agora, todas as tabelas foram distribuídas por company_id. No entanto, alguns dados não "pertencem" naturalmente a nenhum inquilino em particular, e podem ser compartilhados. Por exemplo, todas as empresas na plataforma de anúncios de exemplo podem querer obter informações geográficas para o seu público com base em endereços IP.

Crie uma tabela para armazenar informações geográficas compartilhadas. Execute os seguintes comandos no psql:

CREATE TABLE geo_ips (
  addrs cidr NOT NULL PRIMARY KEY,
  latlon point NOT NULL
    CHECK (-90  <= latlon[0] AND latlon[0] <= 90 AND
           -180 <= latlon[1] AND latlon[1] <= 180)
);
CREATE INDEX ON geo_ips USING gist (addrs inet_ops);

Em seguida, faça geo_ips uma "tabela de referência" para armazenar uma cópia da tabela em cada nó de trabalho.

SELECT create_reference_table('geo_ips');

Carregue-o com dados de exemplo. Lembre-se de executar este comando em psql de dentro do diretório onde você baixou o conjunto de dados.

\copy geo_ips from 'geo_ips.csv' with csv

Unir a tabela de cliques com geo_ips é eficiente em todos os nós. Aqui está uma junção para encontrar os locais de todos que clicaram no anúncio 290. Tente executar a consulta em psql.

SELECT c.id, clicked_at, latlon
  FROM geo_ips, clicks c
 WHERE addrs >> c.user_ip
   AND c.company_id = 5
   AND c.ad_id = 290;

Personalizar o esquema por locatário

Cada inquilino pode precisar armazenar informações especiais que não são necessárias para outros. No entanto, todos os locatários compartilham uma infraestrutura comum com um esquema de banco de dados idêntico. Para onde podem ir os dados extras?

Um truque é usar um tipo de coluna aberta como o JSONB do PostgreSQL. Nosso esquema tem um campo JSONB chamado clicks user_data. Uma empresa (digamos empresa cinco), pode usar a coluna para rastrear se o usuário está em um dispositivo móvel.

Aqui está uma consulta para descobrir quem clica mais: visitantes móveis ou tradicionais.

SELECT
  user_data->>'is_mobile' AS is_mobile,
  count(*) AS count
FROM clicks
WHERE company_id = 5
GROUP BY user_data->>'is_mobile'
ORDER BY count DESC;

Podemos otimizar esta consulta para uma única empresa criando um índice parcial.

CREATE INDEX click_user_data_is_mobile
ON clicks ((user_data->>'is_mobile'))
WHERE company_id = 5;

Mais geralmente, podemos criar índices GIN em cada chave e valor dentro da coluna.

CREATE INDEX click_user_data
ON clicks USING gin (user_data);

-- this speeds up queries like, "which clicks have
-- the is_mobile key present in user_data?"

SELECT id
  FROM clicks
 WHERE user_data ? 'is_mobile'
   AND company_id = 5;

Clean up resources (Limpar recursos)

Nas etapas anteriores, você criou recursos do Azure em um cluster. Se você não espera precisar desses recursos no futuro, exclua o cluster. Selecione o botão Excluir na página Visão geral do cluster. Quando solicitado em uma página pop-up, confirme o nome do cluster e selecione o botão Excluir final.

Próximos passos

Neste tutorial, você aprendeu como provisionar um cluster. Você se conectou a ele com psql, criou um esquema e distribuiu dados. Você aprendeu a consultar dados dentro e entre locatários e a personalizar o esquema por locatário.

• Saiba mais sobre os tipos de nó de cluster
• Determinar o melhor tamanho inicial para o cluster