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.

Entender papéis e permissões

Ao consultar o Spanner no BigQuery, você encontra dois tipos distintos de papéis que gerenciam o acesso em diferentes níveis.

  • Papéis do IAM:eles regem o acesso aos recursos do Google Cloud , incluindo instâncias e bancos de dados do Spanner. Eles determinam quais principais podem acessar o serviço do Spanner e realizar ações no nível da instância ou do banco de dados, como conectar, ler dados ou administrar. Você gerencia papéis do Identity and Access Management (IAM) pelo console do IAM ou pela Google Cloud CLI. Por exemplo, roles/bigquery.connectionUser e roles/spanner.databaseReader. Para mais informações, consulte Papéis do IAM do Spanner e Como conceder permissões.

  • Papéis de banco de dados do Spanner:são definidos em um banco de dados do Spanner usando instruções DDL, como CREATE ROLE e GRANT. Eles controlam o acesso granular a objetos de esquema específicos, como tabelas, colunas e visualizações, dentro do banco de dados. Isso faz parte do controle de acesso refinado (FGAC, na sigla em inglês). Você usa uma função de banco de dados se sua organização implementa o FGAC para gerenciar permissões dentro do banco de dados.

Determinar se você é um usuário do FGAC

Para determinar as permissões corretas a serem solicitadas, você precisa saber se é um usuário da FGAC. Para isso, pergunte ao administrador do banco de dados do Spanner se o acesso ao banco de dados do Spanner é gerenciado por controle de acesso refinado.

Você provavelmente é um usuário do FGAC se o administrador conceder permissões à sua conta atribuindo-a a um papel específico do banco de dados do Spanner (por exemplo, concedendo à sua conta o papel do IAM roles/spanner.databaseRoleUser em um recurso de papel do banco de dados). Se for esse o caso, você precisa saber o nome das funções de banco de dados que pode usar. É necessário configurar a conexão do BigQuery para usar uma dessas funções de banco de dados.

É provável que você não seja um usuário do FGAC se o administrador conceder à sua conta papéis do IAM mais amplos no nível do banco de dados, como roles/spanner.databaseReader. Nesse caso, não é necessário usar uma função de banco de dados específica ao se conectar.

Comparar a aplicação de papéis

Embora o IAM controle o acesso ao recurso de banco de dados em si, os papéis de banco de dados do Spanner controlam as permissões dos objetos dentro desse banco de dados.

Para usar uma função de banco de dados do FGAC, geralmente são necessárias as seguintes permissões:

  • A permissão spanner.databases.useRoleBasedAccess do IAM, que geralmente é concedida pelo papel roles/spanner.fineGrainedAccessUser.
  • Permissão para usar o papel específico do banco de dados, que é concedida pelo papel roles/spanner.databaseRoleUser com uma condição do IAM.

Para mais informações sobre como configurar essas permissões, consulte Configurar o FGAC.

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 você pode usá-las nas consultas, por exemplo, em junções, uniões ou subconsultas. No entanto, nenhum dado é movido do Spanner para o armazenamento do BigQuery.

Não é necessário criar uma conexão para consultar dados do Spanner se você criar um conjunto de dados externo.

Usar a função EXTERNAL_QUERY

Assim como 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 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 do IAM de usuário de conexão do BigQuery (roles/bigquery.connectionUser) na conexão. Você também precisa das permissões adequadas no banco de dados do Spanner, que dependem de se você é um usuário do FGAC.
    • Se você usa o controle de acesso granular:
      • Você precisa dos papéis do IAM necessários para usar o FGAC. Esses papéis geralmente são roles/spanner.fineGrainedAccessUser e roles/spanner.databaseRoleUser. Os papéis são usados com uma condição que especifica o papel do banco de dados.
      • O papel de banco de dados do Spanner especificado na conexão precisa ter o privilégio SELECT em todos os objetos de esquema referenciados pelas consultas. O administrador do banco de dados concede privilégios usando a instrução DDL GRANT ou o equivalente do PostgreSQL.
    • Se você não for um usuário de controle de acesso minucioso, precisará do papel de Leitor de banco de dados do Spanner (roles/spanner.databaseReader) no banco de dados.

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

Escolher a conexão certa

Se você é um usuário de controle de acesso minucioso 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. Esse papel do banco de dados faz parte da configuração do FGAC no banco de dados do Spanner, separado dos seus papéis do IAM. Todas as consultas executadas com essa conexão usam as permissões concedidas a 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 efeito mínimo 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 é mais útil 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 do 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 receber essa permissão com papéis personalizados ou outros papéis predefinidos.

O papel do IAM roles/spanner.databaseReaderWithDataBoost concede permissão para usar o Data Boost. Esse papel é necessário além das permissões básicas necessárias para ler dados, como roles/spanner.databaseReader para usuários que não usam o FGAC ou as permissões de controle de acesso refinado adequadas.

O uso de conjuntos de dados externos com o Spanner sempre usa o Data Boost e, portanto, exige a permissão spanner.databases.useDataBoost.

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 suas consultas EXTERNAL_QUERY, ative-o ao criar uma conexão 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, incluindo uma lista de limitações, consulte Ler dados em paralelo na documentação do Spanner.

Para ver o plano de execução de uma consulta do Spanner, consulte Noções básicas sobre como o Cloud 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, faça isso 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 competem 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 ficar visíveis diretamente no BigQuery Studio, e você poderá conferir os esquemas delas.

No entanto, também é possível ver 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 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

Consultas entre regiões

O BigQuery aceita consultas federadas em que as instâncias do Spanner e os conjuntos de dados do BigQuery estão em regiões diferentes. Essas consultas geram uma cobrança adicional de transferência de dados do Spanner. Para mais informações, consulte os preços do Spanner.

A transferência de dados é cobrada com base nas seguintes SKUs:

  • Transferência de dados de rede entre zonas na mesma região
  • Transferência de dados de rede entre regiões para o mesmo continente
  • Transferência de dados de rede entre regiões para um continente diferente

A transferência de dados é cobrada com base na região do BigQuery em que você executa a consulta e na região mais próxima do Spanner que tem réplicas de leitura/gravação ou somente leitura.

Para configurações multirregionais do BigQuery (US ou EU), os custos de transferência de dados do Spanner são determinados da seguinte forma:

  • Multirregião US do BigQuery: região do Spanner us-central1
  • Multirregião EU do BigQuery: região do Spanner europe-west1

Exemplo:

  • BigQuery (US multirregião) e Spanner (us-central1): custos se aplicam à transferência de dados na mesma região.
  • BigQuery (multirregião US) e Spanner (us-west4): custos são aplicados à transferência de dados entre regiões no mesmo continente.

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