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ção PRIMARY KEY ou pelo menos uma restrição UNIQUE.

  • A instância de destino do AlloyDB precisa ser uma instância PRIMARY no estado READY.

  • 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 KEY ou UNIQUE INSERT, UPDATE, SELECT USAGE CONNECT, TEMPORARY Obrigatório para que os modos UPSERT e APPEND ofereçam suporte ao substituto de nova tentativa automática.
    Tabelas sem restrições INSERT USAGE CONNECT
  • Para 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:

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ção PRIMARY KEY ou pelo menos uma restrição UNIQUE, e a consulta SELECT precisa 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 modo APPEND.

  • A exportação de dados no modo UPSERT tem menor capacidade de gravação e maior overhead de CPU e E/S na instância do AlloyDB do que o modo APPEND. Enquanto o modo APPEND transmite linhas diretamente usando o comando em massa COPY do PostgreSQL, o modo UPSERT executa 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 modo APPEND para otimizar o desempenho.

  • Se a tabela de destino não tiver restrições definidas, a execução de várias exportações do APPEND com os mesmos dados resultará em linhas duplicadas.

  • Não recomendamos executar vários jobs EXPORT DATA simultâ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, INTERVAL e STRUCT do BigQuery não são compatíveis.

  • Se a instrução SELECT do BigQuery omitir colunas que existem na tabela de destino do AlloyDB, essas colunas precisam permitir valores NULL ou ter valores padrão definidos no AlloyDB. Se elas tiverem uma restrição NOT NULL e 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-east1 só pode ser exportado para uma instância do AlloyDB em us-east1.

  • Exportações multirregionais:

    • Os conjuntos de dados na multirregião US só podem ser exportados para uma instância do AlloyDB localizada na região us-central1.
    • Os conjuntos de dados na multirregião EU só podem ser exportados para uma instância do AlloyDB localizada na região europe-west4.

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 (normalmente public).
  • 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ção PRIMARY KEY ou UNIQUE e 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 modo UPSERT. Se você espera que a exportação atualize as linhas atuais, especifique UPSERT diretamente 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ção PRIMARY KEY ou UNIQUE na tabela de destino, com todas as colunas de chave selecionadas na consulta. O BigQuery faz a correspondência de linhas usando a PRIMARY KEY da tabela ou uma restrição UNIQUE se a tabela não tiver uma chave primária. Se a tabela tiver outras restrições UNIQUE e 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 ser 1 ou 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.