Exportar dados para o AlloyDB (ETL reverso)
Um fluxo de trabalho de extração, transformação e carregamento reverso (ETL reverso) do
BigQuery para o AlloyDB para PostgreSQL permite mover dados do
BigQuery para um banco de dados do AlloyDB. É possível usar
a instrução
EXPORT DATA para processar
ou filtrar os dados com uma consulta antes de exportá-los, ou sincronizar a
tabela do BigQuery com o
AlloyDB para copiar ou espelhar a tabela
completa.
Antes de começar
Crie um cluster e uma instância do AlloyDB, que incluem um banco de dados, um esquema e uma tabela, para receber os dados exportados. É necessário ter o esquema e a tabela de destino antes de executar o job de exportação. Para o modo
UPSERT, a tabela de destino precisa ter uma restriçãoPRIMARY KEYou pelo menos uma restriçãoUNIQUE.A instância de destino do AlloyDB precisa ser uma instância
PRIMARYno estadoREADY.Crie uma conexão do BigQuery para se conectar à instância do AlloyDB. A conexão precisa usar a autenticação de nome de usuário e senha. O usuário do banco de dados precisa dos seguintes privilégios:
Tipo de tabela Privilégios da tabela de destino Privilégios do esquema de destino Privilégios de banco de dados Observações Tabelas com uma restrição PRIMARY KEYouUNIQUEINSERT,UPDATE,SELECTUSAGECONNECT,TEMPORARYObrigatório para que os modos UPSERTeAPPENDofereçam suporte ao substituto de nova tentativa automática.Tabelas sem restrições INSERTUSAGECONNECTPara usar esse recurso, é necessário criar uma reserva do nível Enterprise ou Enterprise Plus do BigQuery. As exportações para o AlloyDB são compatíveis apenas com reservas que tenham uma atribuição
QUERY.Atribua papéis do Identity and Access Management (IAM) que concedam aos usuários as permissões necessárias para realizar cada tarefa deste documento.
Funções exigidas
Para conseguir as permissões necessárias para exportar dados do BigQuery para o AlloyDB, peça ao administrador para conceder a você os seguintes papéis do IAM no seu projeto:
-
Exporte dados de uma tabela do BigQuery:
Visualizador de dados do BigQuery (
roles/bigquery.dataViewer) -
Executar um job de extração:
Usuário do BigQuery (
roles/bigquery.user) -
Usar uma conexão do BigQuery:
Usuário de conexão do BigQuery (
roles/bigquery.connectionUser) -
Conectar-se a uma instância do AlloyDB:
Cliente do AlloyDB (
roles/alloydb.client): a conta de serviço de conexão
Para mais informações sobre a concessão de papéis, consulte Gerenciar o acesso a projetos, pastas e organizações.
Também é possível conseguir as permissões necessárias usando papéis personalizados ou outros papéis predefinidos.
Limitações
As exportações do AlloyDB só são compatíveis com exportações em lote. Não é possível usar consultas contínuas para exportar para o AlloyDB.
Para usar o modo
UPSERT, a tabela de destino do AlloyDB precisa ter uma restriçãoPRIMARY KEYou pelo menos uma restriçãoUNIQUE, e a consultaSELECTprecisa projetar todas as colunas que pertencem a essa restrição. Só é possível exportar tabelas sem uma chave primária ou restrição exclusiva usando o modoAPPEND.A exportação de dados no modo
UPSERTtem menor capacidade de gravação e maior overhead de CPU e E/S na instância do AlloyDB do que o modoAPPEND. Enquanto o modoAPPENDtransmite linhas diretamente usando o comando em massaCOPYdo PostgreSQL, o modoUPSERTexecuta uma operação de inserção ou atualização (INSERT ... ON CONFLICT DO UPDATE), que exige que o AlloyDB verifique índices exclusivos para chaves existentes e atualize as linhas correspondentes. Ao exportar apenas novos registros, use o modoAPPENDpara otimizar o desempenho.Se a tabela de destino não tiver restrições definidas, a execução de várias exportações do
APPENDcom os mesmos dados resultará em linhas duplicadas.Não recomendamos executar vários jobs
EXPORT DATAsimultâneos na mesma tabela do AlloyDB. Isso pode resultar em um comportamento imprevisível, como perda de dados ou falhas de jobs. Recomendamos verificar se apenas um job de exportação grava em uma tabela específica por vez.Somente a autenticação por nome de usuário e senha por uma conexão do BigQuery é aceita.
Os tipos de dados
ARRAY,BYTES,GEOGRAPHY,INTERVALeSTRUCTdo BigQuery não são compatíveis.Se a instrução
SELECTdo BigQuery omitir colunas que existem na tabela de destino do AlloyDB, essas colunas precisam permitir valoresNULLou ter valores padrão definidos no AlloyDB. Se elas tiverem uma restriçãoNOT NULLe nenhum valor padrão, a exportação vai falhar.As exportações para o AlloyDB só são compatíveis com as edições BigQuery Enterprise ou Enterprise Plus. A edição Standard do BigQuery e a computação sob demanda não são compatíveis. Para mais informações, consulte Recursos de administração.
Um job do BigQuery, como um job de extração para o AlloyDB, tem uma duração máxima de 6 horas. Para exportações muito grandes, recomendamos dividir a exportação em vários jobs menores.
Considerações sobre o local
A exportação de dados para o AlloyDB tem requisitos específicos quanto ao local do conjunto de dados do BigQuery e da instância do AlloyDB:
Exportações na mesma região: a instância de destino do AlloyDB precisa estar na mesma região do Google Cloud do conjunto de dados do BigQuery. Por exemplo, um conjunto de dados em
us-east1só pode ser exportado para uma instância do AlloyDB emus-east1.Exportações multirregionais:
- Os conjuntos de dados na multirregião
USsó podem ser exportados para uma instância do AlloyDB localizada na regiãous-central1. - Os conjuntos de dados na multirregião
EUsó podem ser exportados para uma instância do AlloyDB localizada na regiãoeurope-west4.
- Os conjuntos de dados na multirregião
Não há suporte para exportações entre regiões que não sejam as combinações mencionadas anteriormente.
Configurar exportações com alloydb_options
Use a opção alloydb_options na
instrução EXPORT DATA
para especificar o esquema, a tabela, o modo de gravação
e o número máximo de conexões do AlloyDB de destino. A configuração é expressa como uma string JSON. Somente
o parâmetro table é obrigatório. Todos os outros parâmetros são opcionais.
Ao configurar a exportação, as colunas na instrução SELECT precisam ter
aliases que correspondam aos nomes das colunas na tabela de destino
do AlloyDB.
EXPORT DATA WITH CONNECTION `PROJECT_ID.LOCATION.CONNECTION_ID` OPTIONS( format='ALLOYDB', uri="https://alloydb.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/clusters/CLUSTER_ID/instances/INSTANCE_ID", alloydb_options="""{ "schema": "SCHEMA_NAME", "table": "TABLE_NAME", "write_mode": "WRITE_MODE", "max_parallel_connections":MAX_CONNECTIONS}""" ) AS SELECT * FROM `mydataset.table1`;
Substitua:
PROJECT_ID: o nome do projeto do Google Cloud .LOCATION: o local da conexão e da instância de destino.CONNECTION_ID: o nome da conexão do BigQuery.CLUSTER_ID: o nome do cluster do AlloyDB.INSTANCE_ID: o nome da instância de destino do AlloyDB.SCHEMA_NAME(opcional): o nome do esquema de destino no AlloyDB. Se não for fornecido, o esquema padrão configurado para o usuário do banco de dados será usado (normalmentepublic).TABLE_NAME: o nome de uma tabela de destino atual no AlloyDB, sem o prefixo do esquema.WRITE_MODE(opcional): o modo de gravação a ser usado ao exportar dados. Valores compatíveis:APPEND: por padrão, esse valor insere diretamente as linhas exportadas para uma capacidade de processamento mais rápida ao exportar novos registros. Se a tabela de destino tiver uma restriçãoPRIMARY KEYouUNIQUEe ocorrer um conflito de chave duplicada, por exemplo, porque já existem chaves correspondentes na tabela ou uma tentativa de job com falha é repetida após a gravação de dados parciais, o BigQuery repete automaticamente a exportação no modoUPSERT. Se você espera que a exportação atualize as linhas atuais, especifiqueUPSERTdiretamente para evitar a sobrecarga de uma nova tentativa.UPSERT: atualiza os registros atuais se houver uma chave correspondente ou insere novos registros se nenhuma chave correspondente for encontrada. Requer uma restriçãoPRIMARY KEYouUNIQUEna tabela de destino, com todas as colunas de chave selecionadas na consulta. O BigQuery faz a correspondência de linhas usando aPRIMARY KEYda tabela ou uma restriçãoUNIQUEse a tabela não tiver uma chave primária. Se a tabela tiver outras restriçõesUNIQUEe uma linha exportada tiver um valor duplicado em uma dessas colunas, a exportação vai falhar.
MAX_CONNECTIONS(opcional): o número máximo de conexões paralelas simultâneas de workers do BigQuery com a instância do AlloyDB. Se especificado, o valor precisa ser1ou maior. Limitar as conexões pode evitar a sobrecarga da instância de destino durante grandes exportações.
Exemplos de instruções de exportação
Nesta seção, mostramos exemplos de instruções EXPORT DATA para diferentes casos de uso.
Exportar no modo APPEND
O exemplo a seguir anexa campos selecionados da tabela mydataset.table1 a uma tabela do AlloyDB chamada my_target_table:
EXPORT DATA WITH CONNECTION `myproject.us-central1.my-alloydb-conn` OPTIONS ( format='ALLOYDB', uri="https://alloydb.googleapis.com/v1/projects/myproject/locations/us-central1/clusters/my-cluster/instances/my-instance", alloydb_options="""{ "schema": "public", "table": "my_target_table", "write_mode": "APPEND" }""" ) AS SELECT col1 AS id, col2 AS name, col3 AS value FROM `mydataset.table1`;
Exportar no modo UPSERT
O exemplo a seguir faz upsert de registros de mydataset.customers na tabela customer_profiles do AlloyDB, o que inclui atualizar os registros que correspondem à chave primária customer_id e inserir novos registros:
EXPORT DATA WITH CONNECTION `myproject.us-central1.my-alloydb-conn` OPTIONS ( format='ALLOYDB', uri="https://alloydb.googleapis.com/v1/projects/myproject/locations/us-central1/clusters/my-cluster/instances/my-instance", alloydb_options="""{ "schema": "public", "table": "customer_profiles", "write_mode": "UPSERT", "max_parallel_connections": 8 }""" ) AS SELECT customer_id, email, status, updated_at FROM `mydataset.customers`;
Sincronizar uma tabela para exportação
Para cópias de tabelas completas ou espelhamento automatizado sem a necessidade de transformações baseadas em SQL, sincronize sua tabela do BigQuery diretamente com o AlloyDB. Enquanto a instrução EXPORT DATA permite usar uma consulta SELECT
para processar, criar um alias ou filtrar dados antes que eles sejam movidos, é possível usar sincronizações
de tabela para uma migração individual de uma tabela do BigQuery.
As sincronizações de tabelas estão disponíveis nos seguintes modos:
- Importação única: cria uma cópia gravável e independente de uma tabela do BigQuery no AlloyDB.
- Sincronização periódica (espelhamento): cria uma tabela local somente leitura no AlloyDB que é atualizada automaticamente do BigQuery em uma programação definida.
Para mais informações, consulte Sincronizar dados do BigQuery com o AlloyDB.
Preços
Quando você exporta dados para o AlloyDB usando a instrução EXPORT DATA,
a cobrança é feita usando os
preços de computação da capacidade do BigQuery.
Depois que os dados são exportados, você é cobrado pelo armazenamento dos dados no AlloyDB. Para mais informações, consulte Preços do AlloyDB para PostgreSQL.