Introdução às consultas federadas

Nesta página, apresentamos como usar consultas federadas e fornecemos orientação sobre como consultar dados do Spanner, do AlloyDB e do Cloud SQL no BigQuery.

As consultas federadas permitem que você envie uma instrução de consulta para bancos de dados do AlloyDB, Spanner ou Cloud SQL e receba o resultado como uma tabela temporária. As consultas federadas usam a API BigQuery Connection para estabelecer uma conexão com o AlloyDB, Spanner ou o Cloud SQL. Na consulta, use a função EXTERNAL_QUERY para enviar uma instrução de consulta ao banco de dados externo usando o dialeto SQL desse banco de dados. Os resultados são convertidos em tipos de dados do GoogleSQL.

Armazenamentos de dados compatíveis

É possível usar consultas federadas com os seguintes armazenamentos de dados:

Fluxo de trabalho

  • Identifique o projeto do Google Cloud que inclui a fonte de dados que você quer consultar.
  • Um usuário bigquery.admin cria um recurso de conexão no BigQuery.
  • O usuário administrador concede permissão para usar o recurso de conexão para o usuário B.
    • Se o administrador e o usuário B forem a mesma pessoa, não será necessário conceder permissão.
  • O usuário B cria uma consulta no BigQuery com a nova função SQL EXTERNAL_QUERY.

Alternativas às consultas federadas: tabelas e conjuntos de dados externos

Outra opção para consultar bancos de dados operacionais, como Bigtable, Spanner, Cloud Storage, Google Drive e Salesforce Data Cloud, é usar tabelas e conjuntos de dados externos. Os conjuntos de dados e tabelas externos permitem que você visualize tabelas e esquemas e os consulte sem usar uma função SQL EXTERNAL_QUERY. Não é necessário trazer os dados de volta ao BigQuery, e você pode usar a sintaxe do BigQuery em vez de escrever no dialeto do banco de dados SQL específico do SQL.

Regiões compatíveis

As consultas federadas são aceitas apenas em regiões compatíveis com a fonte de dados externa e o BigQuery. Para conferir uma lista de locais compatíveis, consulte as seguintes seções:

É possível criar uma conexão e executar uma consulta federada nas regiões de acordo com as regras a seguir.

Regiões únicas

Uma região única do BigQuery só pode consultar um recurso na mesma região.

Por exemplo, se o conjunto de dados estiver em us-east4, é possível consultar instâncias do Cloud SQL e do AlloyDB ou bancos de dados do Spanner que estão localizados em us-east4. O local de processamento da consulta é a única região do BigQuery.

Multirregiões

Uma multirregião do BigQuery pode consultar qualquer região de fonte de dados na mesma área geográfica (EUA e UE). Os locais multirregionais não estão disponíveis para instâncias do Cloud SQL porque são usados apenas para backups. Uma multirregião do BigQuery também pode consultar uma instância do Spanner na mesma multirregião.

  • Uma consulta executada na multirregião EUA do BigQuery pode consultar qualquer região única na região geográfica dos EUA, como us-central1, us-east4 ou us-west2.

  • Uma consulta executada na multirregião da UE do BigQuery pode consultar qualquer região única em estados membro da União Europeia, como europe-north1 ou europe-west3.

  • O local em que a consulta é executada precisa ser o mesmo do recurso de conexão. Por exemplo, as consultas executadas na multirregião dos EUA precisam usar uma conexão localizada nessa multirregião.

O desempenho da consulta varia de acordo com a proximidade entre o conjunto de dados e a fonte de dados externa. Por exemplo, uma consulta federada entre um conjunto de dados na multirregião dos USA e uma instância do Cloud SQL em us-central1 é rápida. No entanto, para executar a mesma consulta entre uma multirregião dos EUA e uma instância do Cloud SQL em us-east4, o desempenho poderá ser mais lento.

O local de processamento da consulta é o local multirregional, US ou EU.

Mapeamentos de tipo de dados

Quando você executa uma consulta federada, os dados da fonte de dados externa são convertidos em tipos do GoogleSQL. Para mais informações, consulte Consultas federadas do Cloud SQL.

Cotas e limites

  • Consulta federada entre regiões. Se o local de processamento da consulta do BigQuery e o local da fonte de dados externa forem diferentes, essa será uma consulta entre regiões. É possível executar até 1 TB em consultas entre regiões por projeto por dia. Veja a seguir um exemplo de consulta entre regiões.
    • A instância do Cloud SQL está em us-west1, enquanto a conexão do BigQuery é baseada na multirregião dos EUA. O local de processamento da consulta do BigQuery é US.
  • Cota. Os usuários precisam controlar a cota de consultas na fonte de dados externa, como o Cloud SQL ou o AlloyDB. Não há configuração de cota extra para consultas federadas. Para conseguir isolamento da carga de trabalho, recomenda-se consultar apenas uma réplica de leitura do banco de dados.
  • Máximo de bytes faturados permitidos. Esse campo não é aceito para consultas federadas. Não é possível calcular os bytes faturados antes de executar de fato as consultas federadas.
  • Número de conexões. Uma consulta federada pode ter no máximo 10 conexões exclusivas.
  • Cloud SQL, MySQL e PostgreSQL. Cotas e limitações se aplicam.

Limitações

As consultas federadas estão sujeitas às seguintes limitações:

  • Desempenho. A consulta federada provavelmente não será tão rápida quanto consultar apenas o armazenamento do BigQuery. O BigQuery precisa esperar que o banco de dados de origem execute a consulta externa e mova temporariamente os dados da fonte de dados externa para o BigQuery. Além disso, o banco de dados de origem pode não estar otimizado para consultas analíticas complexas.

    O desempenho da consulta também varia de acordo com a proximidade entre o conjunto de dados e a fonte de dados externa. Confira mais informações nas regiões com suporte.

  • As consultas federadas são somente leitura. A consulta externa executada no banco de dados de origem precisa ser somente leitura. Portanto, as instruções DML ou DDL não são compatíveis.

  • Tipos de dados não compatíveis Se a consulta externa contiver um tipo de dados não aceito no BigQuery, a consulta falhará imediatamente. É possível converter o tipo de dados não aceito em um tipo diferente.

  • Projeto. Crie o recurso de conexão no mesmo projeto que a instância do Cloud SQL ou do AlloyDB.

Preços

  • Se você estiver usando o modelo de preços sob demanda, será cobrado pelo número de bytes retornados da consulta externa ao executar consultas federadas do BigQuery. Para mais informações, consulte Preços de análise sob demanda.

  • Se você estiver usando edições do BigQuery, a cobrança será feita com base no número de slots usados. Para mais informações, consulte Preços da capacidade de computação.

Pushdowns SQL

As consultas federadas estão sujeitas à técnica de otimização conhecida como push-downs SQL. Isso melhora o desempenho de uma consulta delegando operações, como filtragem, à fonte de dados externa, em vez de executá-las no BigQuery. Reduzir a quantidade de dados transferidos da fonte de dados externa pode reduzir o tempo de execução da consulta e os custos. Os push-downs SQL incluem remoção de colunas (cláusulas SELECT) e push-downs de filtro (cláusulas WHERE).

Quando você usa a função EXTERNAL_QUERY, os push-downs SQL funcionam reescrevendo a consulta original. No exemplo a seguir, a função EXTERNAL_QUERY é usada para se comunicar com um banco de dados do Cloud SQL:

SELECT COUNT(*)
FROM (
  SELECT * FROM EXTERNAL_QUERY("<connection>", "select * from operations_table")
  )
WHERE a = 'Y' AND b NOT IN ('COMPLETE','CANCELLED');

Sem os push-downs SQL, a seguinte consulta é enviada para o Cloud SQL:

SELECT *
FROM operations_table

Quando essa consulta é executada, a tabela inteira é enviada de volta ao BigQuery, mesmo que apenas algumas linhas e colunas sejam necessárias.

Com push-downs SQL, a seguinte consulta é enviada para o Cloud SQL:

SELECT `a`, `b`
FROM (
  SELECT * FROM operations_table) t
WHERE ((`a` = 'Y') AND (NOT `b` IN ('COMPLETE', 'CANCELLED'))

Quando essa consulta é executada, apenas duas colunas e as linhas que correspondem ao predicadode filtragem são enviadas de volta ao BigQuery.

Os pushdowns SQL também são aplicados ao executar consultas federadas com conjuntos de dados externos do Spanner.

Você pode examinar os pushdowns aplicados (se houver) no plano de consulta.

Limitações

Os pushdowns do SQL têm várias limitações que variam de acordo com a origem de dados externa e a forma como você consulta os dados.

Limitações para a federação de consultas ao usar EXTERNAL_QUERY

  • Pushdowns SQL são aplicados apenas a consultas federadas do formulário SELECT * FROM T.
  • Há suporte somente para a remoção de colunas e push-downs de filtro. Pushdowns de computação, mesclagem, limite, ordenação por e agregação não são aceitos.
  • Para push-downs de filtro, os literais precisam ser de um dos seguintes tipos: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP. Não há suporte para literais que são estruturas.
  • Os pushdowns de função SQL são aplicados apenas para funções compatíveis com o BigQuery e um banco de dados de destino.
  • Os push-downs SQL são compatíveis apenas com o AlloyDB, o Cloud SQL e o Spanner.
  • O push-down SQL não é compatível com o SAP Datasphere.

Limitações para a federação de consultas ao usar conjuntos de dados externos do Spanner

  • Há suporte para pushdowns de agregação parcial, remoção de colunas, filtro e computação. Não há suporte para junção, limite e ordem por agregação.
  • Para push-downs de filtro, os literais precisam ser de um dos seguintes tipos: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP, BYTE ou matrizes. Não há suporte para literais que são estruturas.
  • Os push-downs de função SQL são aplicados apenas para funções compatíveis com o BigQuery e o Spanner.

Funções compatíveis por fonte de dados

Confira a seguir as funções SQL compatíveis com a fonte de dados. Nenhuma função é compatível com o SAP Datasphere.

Cloud SQL MySQL

  • Operadores lógicos: AND, OR, NOT.
  • Operadores de comparação: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Operadores aritméticos: +, -, * (apenas para INT64 e FLOAT64).

Cloud SQL PostgreSQL e AlloyDB

  • Operadores lógicos: AND, OR, NOT.
  • Operadores de comparação: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Operadores aritméticos: +, -, *, / (apenas para INT64, FLOAT64 e DATE tipos, exceto a subtração de DATE).

Spanner: dialeto PostgreSQL

  • Operadores lógicos: AND, OR, NOT.
  • Operadores de comparação: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Operadores aritméticos: +, -, *, / (apenas para INT64, FLOAT64, NUMERIC).

Spanner: dialeto GoogleSQL

  • Operadores lógicos: AND, OR, NOT.
  • Operadores de comparação: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Operadores aritméticos: +, -, *, / (apenas para INT64, FLOAT64, NUMERIC).
  • Operadores aritméticos seguros: SAFE_ADD, SAFE_SUBTRACT, SAFE_MULTIPLY, SAFE_DIVIDE (apenas para INT64, FLOAT64, NUMERIC ).
  • Ao usar conjuntos de dados externos, também:
    • Compute pushdown,
    • Pushdown de agregação parcial,
    • Funções String,
    • Funções matemáticas,
    • Funções Cast,
    • Funções de matriz.

A seguir