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

APLICA-SE A: 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
  • Ingerir dados de exemplo
  • Consultar dados do locatário
  • Consultar dados entre locatários
  • Personalizar o esquema por locatário

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.

    Captura de tela mostrando a guia “Básico” da tela “Criar”.

    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 <clustername>.postgres.database.azure.com.
    • Você pode escolher uma versão de banco de dados. O Azure Cosmos DB for PostgreSQL sempre dá suporte à versão mais recente do PostgreSQL, um dia após o lançamento.
    • O nome de usuário do administrador precisa ser o valor citus.
  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.

    Captura de tela mostrando a guia “Rede” da tela “Criar”.

  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ê durante a criação de um aplicativo Web que permite aos anunciantes acompanharem suas campanhas.

Várias empresas podem usar o aplicativo, então, vamos criar uma tabela para armazenar as empresas e outra para as campanhas delas. No console do 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 executar anúncios. Também adicione uma tabela para anúncios, executando o seguinte código no 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, vamos rastrear as estatísticas sobre cliques e impressões para 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)
);

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

\dt

Aplicativos multilocatários podem impor exclusividade apenas por locatário, por isso, todas as chaves primárias e estrangeiras incluem a ID da empresa.

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 qual locatário possui quais linhas.

Vamos definir a coluna de distribuição como empresa_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');

Importante

A distribuição de tabelas é necessária para aproveitar os recursos de desempenho do Azure Cosmos DB for PostgreSQL. Se você não distribuir tabelas, os nós de trabalho não poderão ajudar a executar consultas que envolvam essas tabelas.

Ingerir dados de exemplo

Agora, fora do psql, 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

Novamente dentro do psql, carregue em massa os dados. Certifique-se de executar o psql no mesmo diretório em que 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 espalhados entre nós de trabalho.

Consultar dados do locatário

Quando o aplicativo solicita dados para um locatário único, o banco de dados pode executar a consulta em um nó de trabalho único. Consultas de locatário único filtram por uma ID de locatário único. Por exemplo, a consulta a seguir filtra company_id = 5 para anúncios e impressões. Tente executá-la no 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;

Consultar dados entre locatários

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

Crie uma tabela para armazenar informações geográficas compartilhadas. Executar os seguintes comandos na 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 com 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-a com dados de exemplo. Lembre-se de executar esse comando em psql de dentro do diretório em que o conjunto de dados foi baixado.

\copy geo_ips from 'geo_ips.csv' with csv

Unir a tabela de cliques com geo_ips é eficiente em todos os nós. Essa é uma junção para encontrar as localizações de todos que clicaram no anúncio 290. Tente executar a consulta no 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 locatário pode precisar armazenar informações especiais não necessárias para outras pessoas. No entanto, todos os locatários compartilham uma infraestrutura comum com um esquema de banco de dados idêntico. Para onde os dados extras podem ir?

Um truque é usar um tipo de coluna aberta como JSONB do PostgreSQL. Nosso esquema tem um campo JSONB clicks chamado 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 essa 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;

De modo mais geral, é possível 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;

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. 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 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 dados de dentro e entre locatários, além de personalizar o esquema por locatário.