Introdução às consultas federadas

As consultas federadas permitem que você envie uma instrução de consulta para um banco de dados externo (como AlloyDB, Spanner, Cloud SQL, Oracle, MySQL ou PostgreSQL) e receba o resultado como uma tabela temporária. As consultas federadas usam a API BigQuery Connection para estabelecer uma conexão com o banco de dados externo. 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.

Repositórios 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. Com conjuntos de dados e tabelas externos, é possível visualizar tabelas e os esquemas delas e consultá-las sem usar uma função SQL EXTERNAL_QUERY. Não é necessário trazer os dados de volta para o BigQuery, e é possível usar a sintaxe do BigQuery em vez de escrever no dialeto específico do banco de dados SQL.

Regiões compatíveis

Para conferir uma lista de locais compatíveis, consulte as seguintes seções:

AlloyDB e Cloud SQL

As consultas federadas são aceitas apenas em regiões compatíveis com a fonte de dados externa e o BigQuery.

É 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, será possível consultar instâncias do Cloud SQL ou do AlloyDB que estão localizadas 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 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.

Spanner

Para o Spanner, as configurações regionais e multirregionais são compatíveis. Uma região única/multirregião do BigQuery pode consultar uma instância do Spanner em qualquer região compatível do Spanner. Para mais detalhes, consulte consultas entre regiões.

Oracle, MySQL e PostgreSQL

As consultas federadas para Oracle, MySQL e PostgreSQL são compatíveis com todas as regiões do BigQuery em que é possível criar recursos de conexão. O local em que a consulta é executada precisa corresponder à região do recurso de conexão.

Se você rotear o tráfego por redes particulares usando anexos de rede do Private Service Connect, o anexo de rede e a conexão do BigQuery precisarão estar na mesma região.

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 mapeamentos de tipo de dados específicos de cada fonte de dados, consulte:

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. 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.

  • Chaves de criptografia gerenciadas pelo cliente (CMEK). As CMEKs são configuradas separadamente para o BigQuery e para fontes de dados externas. Se você configurar o banco de dados de origem para usar CMEK, mas não o BigQuery, a tabela temporária que contém os resultados de uma consulta federada será criptografada com uma Google-owned and Google-managed encryption key.

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 transferência de dados 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_ID", "select * from operations_table")
  )
WHERE a = 'Y' AND b NOT IN ('COMPLETE','CANCELLED');

Substitua CONNECTION_ID pelo ID da conexão do BigQuery.

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 de SQL têm várias limitações que variam de acordo com a fonte de dados externa e a maneira como você consulta os dados.

Limitações para 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. Especificamente, 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. Literais que são structs não são aceitos.
  • As funções SQL são aplicadas apenas a 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. Para outros bancos de dados, a consulta é enviada sem modificações.

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

  • Há suporte para remoção de colunas, filtro, computação e pushdowns de agregação parcial. Especificamente, as operações de mesclagem, limite e ordenação por pushdowns não são aceitas.
  • Para push-downs de filtro, os literais precisam ser de um dos seguintes tipos: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP, BYTE ou matrizes. Literais que são structs não são aceitos.
  • Os pushdowns de função SQL são aplicados apenas a 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:

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).

  • Ao usar conjuntos de dados externos, também:

    • Pushdown de computação
    • Pushdown de agregação parcial
    • Funções de string
    • Funções matemáticas
    • Funções de conversão
    • Funções de matriz

  • As consultas são executadas de acordo com a semântica do GoogleSQL, não do PostgreSQL. Exemplo:

    • Os valores NULL são classificados primeiro em ordem crescente por padrão, ao contrário do PostgreSQL, em que são classificados por último por padrão.

    • Os valores NUMERIC do PostgreSQL lidos do Spanner são processados de acordo com o mapeamento de tipos do Spanner para o BigQuery. Por exemplo, se uma coluna numérica tiver o valor 1.1234567891, a consulta a seguir vai retornar 0 linhas: 

      SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "SELECT * from
operations_table where numeric_col = 1.123456789")
       No entanto, a instrução a seguir retorna uma linha com base na semântica do GoogleSQL: 
      SELECT * from operations_table where numeric_col = 1.123456789

    • A normalização de objetos JSON se comporta de maneira diferente. As chaves são classificadas de forma estritamente lexicográfica no Spanner JSON, mas no PostgreSQL PG JSONB, elas são classificadas primeiro pelo comprimento da chave e, em seguida, lexicograficamente com comprimento de chave equivalente.

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:
    • Pushdown de computação,
    • Pushdown de agregação parcial
    • Funções de string,
    • Funções matemáticas
    • Funções de conversão,
    • Funções de matriz.

Trabalhar com agrupamentos em fontes de dados externas

Uma fonte de dados externa pode ter uma ordenação definida em uma coluna (por exemplo, não diferencia maiúsculas de minúsculas). Quando você executa uma consulta federada, o banco de dados remoto considera a ordenação configurada.

Considere o exemplo a seguir, em que você tem uma coluna flag com uma reordenação que não diferencia maiúsculas de minúsculas na fonte de dados externa:

SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table where flag = 'Y'")

Substitua CONNECTION_ID pelo ID da conexão do BigQuery.

A consulta anterior retorna linhas em que flag é y ou Y porque a consulta é executada na fonte de dados externa.

No entanto, para federação de consultas com fontes de dados do Cloud SQL, SAP Datasphere, AlloyDB, Oracle, MySQL ou PostgreSQL, se você adicionar um filtro à consulta principal, ela será executada no BigQuery com a ordenação padrão. Confira a consulta a seguir:

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE flag = 'Y'

Devido à relação padrão que diferencia maiúsculas de minúsculas no BigQuery, a consulta anterior retorna apenas linhas em que a sinalização é Y e filtra as linhas em que a sinalização é y. Para que a cláusula WHERE não diferencie maiúsculas de minúsculas, especifique a ordenação na consulta:

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE COLLATE(flag, 'und:ci') = 'Y'

A seguir