Consultas federadas do Spanner

Como analista de dados, é possível consultar dados no Spanner pelo BigQuery usando consultas federadas.

A federação BigQuery Spanner permite que o BigQuery consulte dados que residem no Spanner em tempo real, sem copiar ou mover dados.

É possível consultar dados do Spanner de duas maneiras:

  • Crie um conjunto de dados externo do Spanner.
  • Use uma função EXTERNAL_QUERY.

Usar conjuntos de dados externos

A maneira mais simples de consultar tabelas do Spanner é criar um conjunto de dados externo. Depois de criar o conjunto de dados externo, as tabelas do banco de dados do Spanner correspondente ficam visíveis no BigQuery e podem ser usadas nas consultas, por exemplo, em mesclagens, uniões ou subconsultas. No entanto, nenhum dado é movido do Spanner para o armazenamento do BigQuery.

Se você criar um conjunto de dados externo, não vai precisar criar uma conexão para consultar os dados do Spanner.

Usar a função EXTERNAL_QUERY

Assim como em outros bancos de dados federados, também é possível consultar dados do Spanner com uma função EXTERNAL_QUERY. Isso pode ser útil se você quiser consultar o banco de dados do Spanner que usa o dialeto PostgreSQL ou ter mais controle sobre os parâmetros de conexão.

Antes de começar

  • Verifique se o administrador do BigQuery criou uma conexão do Spanner e a compartilhou com você. Consulte Escolher a conexão certa.
  • Para receber as permissões necessárias para consultar uma instância do Spanner, peça ao administrador para conceder a você o papel de Identity and Access Management (IAM) do usuário de conexão do BigQuery (roles/bigquery.connectionUser). Também é necessário pedir ao administrador que conceda uma das seguintes opções:
    • Se você for um usuário de controle de acesso minucioso, precisará de acesso a um papel de banco de dados que tenha o privilégio SELECT em todos os objetos de esquema do Spanner nas suas consultas.
    • Se você não for um usuário de controle de acesso granular, precisará do papel de IAM de Leitor de banco de dados do Cloud Spanner (roles/spanner.databaseReader).

    Para saber mais sobre a concessão de papéis, consulte Gerenciar o acesso a projetos, pastas e organizações. Consulte informações em Sobre controle de acesso detalhado.

Escolher a conexão certa

Se você é um usuário de controle de acesso granular do Spanner, ao executar uma consulta federada com uma função EXTERNAL_QUERY, é necessário usar uma conexão do Spanner que especifique um papel de banco de dados. Todas as consultas executadas com essa conexão usam esse papel de banco de dados.

Se você usar uma conexão que não especifica um papel de banco de dados, os papéis do IAM precisam estar indicados em Antes de começar.

Consultar dados

Para enviar uma consulta federada ao Spanner por uma consulta do GoogleSQL, use a função EXTERNAL_QUERY.

Formule a consulta do Spanner no GoogleSQL ou no PostgreSQL, dependendo do dialeto especificado do banco de dados.

O exemplo a seguir faz uma consulta federada a um banco de dados do Spanner chamado orders e mescla os resultados com uma tabela do BigQuery denominada mydataset.customers

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq
  ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

Data Boost do Spanner

O Data Boost é um recurso sem servidor totalmente gerenciado que fornece recursos de computação independentes para as cargas de trabalho do Spanner compatíveis. O Data Boost permite executar consultas de análise e exportações de dados com impacto quase zero nas cargas de trabalho atuais na instância provisionada do Spanner. O Data Boost permite que você execute consultas federadas com capacidade de computação independente, separada das instâncias provisionadas para evitar afetar as cargas de trabalho atuais no Spanner. O Data Boost tem mais impacto quando você executa consultas ad-hoc complexas ou quando quer processar grandes quantidades de dados sem afetar a carga de trabalho do Spanner. A execução de consultas federadas com o Data Boost pode levar a um consumo de CPU significativamente menor e, em alguns casos, menor latência da consulta.

Antes de começar

Para receber a permissão necessária para ativar o acesso ao Data Boost, peça ao administrador para conceder a você o papel do IAM de Leitor de banco de dados do Cloud Spanner com DataBoost (roles/spanner.databaseReaderWithDataBoost) no banco de dados do Spanner. Para mais informações sobre a concessão de papéis, consulte Gerenciar o acesso a projetos, pastas e organizações.

Esse papel predefinido contém a permissão spanner.databases.useDataBoost, que é necessária para ativar o acesso ao Data Boost.

Também é possível conseguir essa permissão com papéis personalizados ou outros papéis predefinidos.

Ativar o Data Boost

Ao usar conjuntos de dados externos, o Data Boost é sempre usado, e você não precisa ativá-lo manualmente.

Se você quiser usar o Data Boost nas consultas EXTERNAL_QUERY, ative-o ao criar uma conexão que será usada pela consulta.

Ler dados em paralelo

O Spanner pode dividir algumas consultas em partes menores, ou partições, e buscar as partições em paralelo. Para mais informações, consulte Ler dados em paralelo na documentação do Spanner.

No entanto, essa opção está restrita a consultas que atendem a uma das seguintes condições:

Outras consultas retornam um erro. Para conferir o plano de execução de uma consulta do Spanner, consulte Noções básicas sobre como o Spanner executa consultas.

Ao executar consultas federadas com conjuntos de dados externos, a opção "Ler dados em paralelo" é sempre usada.

Para ativar leituras paralelas ao usar o EXTERNAL_QUERY, ative-o ao criar a conexão.

Gerenciar a prioridade de execução da consulta

Ao executar consultas federadas com uma função EXTERNAL_QUERY, é possível atribuir prioridade (high, medium ou low) a consultas individuais especificando a opção query_execution_priority:

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''',
  '{"query_execution_priority":"high"}');

A prioridade padrão é medium.

As consultas com prioridade high competirão com o tráfego transacional. As consultas com prioridade low são mais eficientes e podem ser interrompidas por carga em segundo plano, como backups programados.

Ao executar consultas federadas com conjuntos de dados externos, todas as consultas sempre têm prioridade medium.

Ver um esquema de tabela do Spanner

Se você usar conjuntos de dados externos, as tabelas do Spanner vão aparecer diretamente no BigQuery Studio, e você poderá conferir os esquemas delas.

No entanto, você também pode conferir os esquemas sem definir conjuntos de dados externos. Você também pode usar a função EXTERNAL_QUERY para consultar visualizações information_schema e acessar os metadados do banco de dados. O exemplo a seguir retorna informações sobre as colunas na tabela MyTable:

Banco de dados do Google SQL 

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT t.column_name, t.spanner_type, t.is_nullable
    FROM information_schema.columns AS t
    WHERE
      t.table_catalog = ''
      AND t.table_schema = ''
     AND t.table_name = 'MyTable'
    ORDER BY t.ordinal_position
  ''');

Banco de dados PostgreSQL 

SELECT * from EXTERNAL_QUERY(
 'my-project.us.postgresql.example-db',
  '''SELECT t.column_name, t.data_type, t.is_nullable
    FROM information_schema.columns AS t
    WHERE
      t.table_schema = 'public' and t.table_name='MyTable'
    ORDER BY t.ordinal_position
  ''');

Para mais informações, consulte as seguintes referências de esquema na documentação do Spanner:

Preços

Solução de problemas

Esta seção resolve problemas que podem ocorrer durante o envio de uma consulta federada ao Spanner.

Problema: a consulta não é particionável pela raiz.
Resolução: se você configurar a conexão para ler dados em paralelo, o primeiro operador no plano de execução da consulta precisará ser uma união distribuída ou seu plano de execução precisará não têm uniões distribuídas. Para resolver esse erro, veja o plano de execução da consulta e reescreva a consulta. Para mais informações, consulte Noções básicas sobre como o Cloud Spanner executa consultas.
Problema: prazo excedido.
Solução: selecione a opção para ler dados em paralelo e reescrever a consulta para ser particionada por raiz. Para mais informações, consulte Noções básicas sobre como o Cloud Spanner executa consultas.

A seguir