Anterior

Seguinte

Exercício - Alterar andaimes no Azure Cosmos DB para PostgreSQL

Concluído

Trabalhando com os desenvolvedores do Woodgrove Bank para obter o scaffold do banco de dados de seu aplicativo, este diagrama ilustra o que eles carregaram no banco de dados:

No diagrama, o campo event_type de payment_events agora está event_type_id, com uma relação de chave estrangeira com uma nova tabela chamada event_types. A tabela event_types contém o nome e event_type_id, com o event_type_id como chave primária. A tabela payment_events também tem uma relação de chave estrangeira com uma nova tabela chamada payment_merchants. A tabela payment_merchants tem merchant_id, nome e url. O merchant_id é a chave primária para payment_merchants.

Neste exercício, você reestruturará seu banco de dados para chegar à seguinte saída:

No diagrama, payment_events tem uma coluna event_type_id, com uma relação de chave estrangeira com a tabela event_types. A tabela event_types contém o nome e event_type_id, com o event_type_id como chave primária. A tabela event_types é uma tabela de referência. A tabela payment_merchants tem merchant_id, nome e url. O merchant_id é a coluna de distribuição para payment_merchants. A tabela distribuída payment_users usa user_id como sua coluna de distribuição e tem uma relação de chave estrangeira com a tabela payment_events.

Redefinir o banco de dados de exercícios

1. Limpe as tabelas do exercício anterior, removendo as dependências primeiro:

       DROP TABLE payment_events;
       DROP TABLE payment_merchants;    
       DROP TABLE payment_users;
       DROP TABLE event_types;
   

2. Crie as tabelas como os desenvolvedores do Woodgrove Bank criaram.

   CREATE TABLE event_types 
   (
       event_type_id bigint CONSTRAINT event_types_pk PRIMARY KEY,
       event_type text
   );
   
   CREATE TABLE payment_users
   (
       user_id bigint CONSTRAINT payment_users_pk PRIMARY KEY,
       url text,
       login text,
       avatar_url text
   );
   
   CREATE TABLE payment_merchants
   (
       merchant_id bigint CONSTRAINT payment_merchants_pk PRIMARY KEY,
       name text,
       url text
   );
   
   CREATE TABLE payment_events
   (
       event_id bigint,
       user_id bigint CONSTRAINT payment_events_payment_users_fk REFERENCES payment_users (user_id),
       event_type_id bigint CONSTRAINT payment_events_event_types_fk REFERENCES event_types (event_type_id),
       merchant_id bigint CONSTRAINT payment_events_payment_merchants_fk REFERENCES payment_merchants (merchant_id),
       event_details jsonb,
       created_at timestamp
   ) PARTITION BY RANGE (created_at);    
   

Ajustar as dependências

Para preparar o banco de dados relacional para distribuição, você precisa remover algumas restrições.

ALTER TABLE payment_events         
    DROP CONSTRAINT payment_events_payment_merchants_fk;
ALTER TABLE payment_events         
    DROP CONSTRAINT payment_events_payment_users_fk;

Distribuir as tabelas que não são de eventos

Você pode distribuir as tabelas que não são de eventos ou transformá-las em tabelas de referência. À medida que os desenvolvedores do Woodgrove Bank se perguntam o que acontece se payment_merchants for distribuído, essas etapas levarão você por esse caminho.

1. Altere event_types para uma tabela de referência com esta consulta:

   SELECT create_reference_table('event_types');
   

2. Distribua payment_users com user_id esta consulta:

   SELECT create_distributed_table('payment_users','user_id');
   

3. Distribua payment_merchants com merchant_id esta consulta:

   SELECT create_distributed_table('payment_merchants','merchant_id');
   

4. Verifique se as tabelas estão distribuídas conforme o esperado com a seguinte consulta:

   SELECT table_name, citus_table_type, distribution_column, colocation_id FROM citus_tables;
   

   Observe que as tabelas distribuídas estão implicitamente colocalizadas juntas. Eis um exemplo do resultado:

       table_name     | citus_table_type | distribution_column | colocation_id 
   -------------------+------------------+---------------------+---------------
   event_types       | reference        | <none>              |             2
   payment_merchants | distributed      | merchant_id         |             1
   payment_users     | distributed      | user_id             |             1
   

Atualizar configurações de colocation

Os desenvolvedores perguntaram sobre a atualização do colocation depois que uma tabela é distribuída, porque payment_merchants não deve ser colocationada.

1. Execute a seguinte consulta:

   SELECT update_distributed_table_colocation('payment_merchants',colocate_with=>'none');
   

2. Certifique-se de que as tabelas de usuários e comerciantes não estejam mais colocalizadas com a seguinte consulta:

   SELECT table_name, citus_table_type, distribution_column, colocation_id FROM citus_tables;  
   

   As tabelas de usuários e comerciantes devem ter valores diferentes colocation_id . O resultado deverá ter um aspeto semelhante ao seguinte:

       table_name     | citus_table_type | distribution_column | colocation_id 
   -------------------+------------------+---------------------+---------------
   event_types       | reference        | <none>              |             2
   payment_merchants | distributed      | merchant_id         |             4
   payment_users     | distributed      | user_id             |             1
   

Distribuir a tabela de eventos

Enquanto você trabalha no projeto com os desenvolvedores do Woodgrove Bank, eles perguntam por que não devem distribuir os eventos no event_id campo.

1. Distribua a payment_events tabela em event_id.

   SELECT create_distributed_table('payment_events','event_id');
   

2. Consulta citus_tables para mostrar a colocalização.

   SELECT table_name, citus_table_type, distribution_column, colocation_id FROM citus_tables;  
   

   A saída terá esta aparência:

       table_name     | citus_table_type | distribution_column | colocation_id 
   -------------------+------------------+---------------------+---------------
   event_types       | reference        | <none>              |             2
   payment_events    | distributed      | event_id            |             1
   payment_merchants | distributed      | merchant_id         |             4
   payment_users     | distributed      | user_id             |             1
   

   Observe que o Azure Cosmos DB para PostgreSQL colocou payment_events implicitamente payment_users , com base em tipos de dados de suas colunas de distribuição. Os desenvolvedores estão começando a ver que os campos não correspondem e que não parece uma boa coluna de distribuição, porque eles querem juntar as payment_events tabelas e payment_users no user_id.

Atualizar a tabela de eventos para distribuir com base em user_id

Atualize a tabela a ser distribuída payment_events em user_id.

1. Execute a seguinte consulta para alterar a coluna de distribuição para payment_events:

   SELECT alter_distributed_table('payment_events',distribution_column:='user_id');
   

   Quando essa consulta for executada com êxito, você verá uma saída como esta:

   NOTICE:  creating a new table for public.payment_events
   NOTICE:  moving the data of public.payment_events
   NOTICE:  dropping the old public.payment_events
   NOTICE:  renaming the new table to public.payment_events
   

   Esta consulta será aplicada a todas as partições desta tabela.

2. Confirme se a alteração entrou em vigor executando:

   SELECT table_name, citus_table_type, distribution_column, colocation_id FROM citus_tables;  
   

   A saída é:

       table_name     | citus_table_type | distribution_column | colocation_id 
   -------------------+------------------+---------------------+---------------
   event_types       | reference        | <none>              |             2
   payment_events    | distributed      | user_id             |             1
   payment_merchants | distributed      | merchant_id         |             4
   payment_users     | distributed      | user_id             |             1
   

   O distribution_column para payment_events e suas partições devem mostrar o user_id.

Adicionar uma chave estrangeira a uma tabela distribuída após a criação

Em um caso em que há duas tabelas distribuídas colocalizadas com uma relação de chave estrangeira, você precisará adicionar a chave estrangeira depois que ambas as tabelas forem distribuídas. Uma chave estrangeira, neste caso, deve incluir a coluna de distribuição.

1. payment_eventsdeve ter uma relação de chave estrangeira com .payment_users Adicione esta relação com o seguinte comando:

   ALTER TABLE payment_events ADD CONSTRAINT events_users_fk
    FOREIGN KEY (user_id) REFERENCES payment_users(user_id);
   

2. Confirme se a chave estrangeira foi adicionada executando o seguinte comando:

   \d payment_events
   

   A saída terá esta aparência:

                     Partitioned table "public.payment_events"
   Column     |            Type             | Collation | Nullable | Default 
   ---------------+-----------------------------+-----------+----------+---------
   event_id      | bigint                      |           |          | 
   user_id       | bigint                      |           |          | 
   event_type_id | bigint                      |           |          | 
   merchant_id   | bigint                      |           |          | 
   event_details | jsonb                       |           |          | 
   created_at    | timestamp without time zone |           |          | 
   Partition key: RANGE (created_at)
   Foreign-key constraints:
       "events_users_fk" FOREIGN KEY (user_id) REFERENCES payment_users(user_id)
       "payment_events_event_types_fk" FOREIGN KEY (event_type_id) REFERENCES event_types(event_type_id)
   

   Deve haver duas chaves estrangeiras na payment_events mesa: uma para os eventos e outra para os usuários.

Adicionar uma chave estrangeira sem a coluna de distribuição

payment_events também tinha inicialmente uma relação chave estrangeira com payment_merchantsa . Tente adicionar essa relação com o seguinte comando:

ALTER TABLE payment_events ADD CONSTRAINT events_merchants_fk
 FOREIGN KEY (merchant_id) REFERENCES payment_merchants(merchant_id);

Ao executar este comando, você verá a saída de acordo com estas linhas:

ERROR:  cannot create foreign key constraint
DETAIL:  Foreign keys are supported in two cases, either in between two colocated tables including partition column in the same ordinal in the both tables or from distributed to reference tables

Essa chave estrangeira é algo que teremos que sacrificar desde o design ao passar de um único servidor para um banco de dados distribuído.

Converter de uma tabela distribuída em uma tabela de referência

A tabela de comerciantes pode não ser tão grande quanto os desenvolvedores sugeriram. Em vez de descartar a relação de chave estrangeira, converta a tabela do comerciante em uma tabela de referência. Em seguida, traga de volta a relação de chave estrangeira da tabela distribuída para a tabela de referência.

1. Converta a tabela do comerciante em uma tabela de referência com os seguintes comandos:

   SELECT undistribute_table('payment_merchants');
   SELECT create_reference_table('payment_merchants');
   

2. Adicione a relação de chave estrangeira de payment_events a payment_merchants com o seguinte comando:

   ALTER TABLE payment_events ADD CONSTRAINT events_merchants_fk
    FOREIGN KEY (merchant_id) REFERENCES payment_merchants(merchant_id);
   

Ver tabelas distribuídas

Confirme se as tabelas são distribuídas consultando a visualização do citus_tables sistema. A consulta a seguir inclui o nome da tabela, o tipo de tabela e a coluna de distribuição. Execute o seguinte comando:

SELECT table_name, citus_table_type, distribution_column FROM citus_tables;

Nos resultados da consulta, a citus_table_type coluna indicará se uma tabela é uma tabela distribuída ou uma tabela de referência. Os resultados serão os seguintes:

   table_name     | citus_table_type | distribution_column 
-------------------+------------------+---------------------
 event_types       | reference        | <none>
 payment_events    | distributed      | user_id
 payment_merchants | reference        | <none>
 payment_users     | distributed      | user_id

Observe que as tabelas de referência não têm colunas de distribuição.

Limpeza

Uma vez terminado este módulo, limpe os recursos criados para minimizar os custos.

1. Inicie sessão no portal do Azure.
2. Navegue até o grupo de recursos criado para este módulo.
3. Exclua o grupo de recursos.

Continuar