Introdução às consultas federadas

Esta página apresenta como usar consultas federadas e fornece orientações sobre como consultar dados do Spanner, AlloyDB e Cloud SQL a partir do BigQuery.

As consultas federadas permitem-lhe enviar uma declaração de consulta para bases de dados do AlloyDB, Spanner ou Cloud SQL e receber o resultado como uma tabela temporária. As consultas federadas usam a API BigQuery Connection para estabelecer uma ligação com o AlloyDB, o Spanner ou o Cloud SQL. Na sua consulta, usa a função EXTERNAL_QUERY para enviar uma declaração de consulta para a base de dados externa, usando o dialeto SQL dessa base de dados. Os resultados são convertidos em tipos de dados GoogleSQL.

Armazenamentos de dados suportados

Pode usar consultas federadas com os seguintes repositórios de dados:

Fluxo de trabalho

  • Identifique o Google Cloud projeto que inclui a origem de dados que quer consultar.
  • Um bigquery.admin utilizador cria um recurso de ligação no BigQuery.
  • O utilizador administrador concede autorização para usar o recurso de ligação ao utilizador B.
    • Se o administrador e o utilizador B forem a mesma pessoa, não é necessário conceder autorização.
  • O utilizador B escreve 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 bases de dados operacionais, como o Bigtable, o Spanner, o Cloud Storage, o Google Drive e o Salesforce Data Cloud, é usar tabelas e conjuntos de dados externos. Os conjuntos de dados e as tabelas externos permitem-lhe ver tabelas e os respetivos esquemas, bem como consultá-los sem usar uma função SQL EXTERNAL_QUERY. Não tem de trazer os dados de volta para o BigQuery e pode usar a sintaxe do BigQuery em vez de escrever no dialeto de SQL da base de dados SQL específica.

Regiões suportadas

Para ver uma lista das localizações suportadas, consulte as seguintes secções:

AlloyDB e Cloud SQL

As consultas federadas só são suportadas em regiões que suportam a origem de dados externa e o BigQuery.

Pode criar uma ligação e executar uma consulta federada em várias regiões de acordo com as seguintes regras:

Regiões únicas

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

Por exemplo, se o seu conjunto de dados estiver em us-east4, pode consultar instâncias do Cloud SQL ou instâncias do AlloyDB localizadas em us-east4. A localização do processamento de consultas é a região única do BigQuery.

Várias regiões

Uma multirregião do BigQuery pode consultar qualquer região de origem de dados na mesma grande área geográfica (EUA, UE). As localizações multirregionais não estão disponíveis para instâncias do Cloud SQL, porque estas são usadas apenas para cópias de segurança.

  • Uma consulta executada na multirregião dos EUA do BigQuery pode consultar qualquer região única na área 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 nos Estados-Membros da União Europeia, como europe-north1 ou europe-west3.

  • A localização onde a consulta é executada tem de ser a mesma que a localização do recurso de ligação. Por exemplo, as consultas executadas a partir da multirregião dos EUA têm de usar uma ligação localizada na multirregião dos EUA.

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

A localização do tratamento de consultas é a localização multirregional, US ou EU.

Spanner

Para o Spanner, são suportadas asconfigurações regionais e multirregionais. Uma única região/multirregião do BigQuery pode consultar uma instância do Spanner em qualquer região do Spanner suportada. Para mais detalhes, consulte o artigo sobre as consultas entre regiões.

Mapeamentos de tipos de dados

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

Quotas e limites

  • Consultas federadas entre regiões. Se a localização de processamento de consultas do BigQuery e a localização da origem de dados externa forem diferentes, trata-se de uma consulta entre regiões. Pode executar até 1 TB em consultas entre regiões por projeto por dia. Segue-se um exemplo de uma consulta entre regiões.
    • A instância do Cloud SQL está em us-west1, enquanto a ligação do BigQuery se baseia na multirregião dos EUA. A localização do tratamento de consultas do BigQuery é US.
  • Quota. Os utilizadores devem controlar a quota de consultas na origem de dados externa, como o Cloud SQL ou o AlloyDB. Não existe uma definição de quota adicional para consultas federadas. Para alcançar o isolamento da carga de trabalho, recomendamos que consulte apenas uma réplica de leitura da base de dados.
  • Máximo de bytes faturados permitidos. Este campo não é compatível com consultas federadas. Não é possível calcular os bytes faturados antes de executar efetivamente as consultas federadas.
  • Número de associações. Uma consulta federada pode ter, no máximo, 10 ligações únicas.
  • Cloud SQL MySQL e PostgreSQL. Aplicam-se quotas e limitações.

Limitações

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

  • Desempenho. É provável que uma consulta federada não seja tão rápida como consultar apenas o armazenamento do BigQuery. O BigQuery tem de aguardar que a base de dados de origem execute a consulta externa e mova temporariamente os dados da origem de dados externa para o BigQuery. Além disso, a base de dados de origem pode não estar otimizada para consultas analíticas complexas.

    O desempenho da consulta também varia com base na proximidade entre o conjunto de dados e a origem de dados externa. Para mais informações, consulte as regiões compatíveis.

  • As consultas federadas são só de leitura. A consulta externa executada na base de dados de origem tem de ser apenas de leitura. Por conseguinte, as declarações DML ou DDL não são suportadas.

  • Tipos de dados não suportados. Se a sua consulta externa contiver um tipo de dados que não é suportado no BigQuery, a consulta falha imediatamente. Pode converter o tipo de dados não suportado num tipo de dados suportado diferente.

  • Chaves de encriptação geridas pelo cliente (CMEK). A CMEK é configurada separadamente para o BigQuery e para origens de dados externas. Se configurar a base de dados de origem para usar a CMEK, mas não o BigQuery, a tabela temporária que contém os resultados de uma consulta federada é encriptada com uma Google-owned and Google-managed encryption key.

Preços

  • Se estiver a usar o modelo de preços a pedido, é-lhe cobrado o número de bytes devolvidos pela consulta externa quando executa consultas federadas a partir do BigQuery. Para mais informações, consulte a secção Preços de análise a pedido.

  • Se estiver a usar as edições do BigQuery, a cobrança é feita com base no número de slots que usa. Para mais informações, consulte os preços de computação de capacidade.

Transferências de SQL

As consultas federadas estão sujeitas à técnica de otimização conhecida como pushdowns de SQL. Melhoram o desempenho de uma consulta ao delegar operações como a filtragem para a origem de dados externa, em vez de as realizar no BigQuery. Reduzir a quantidade de dados transferidos da origem de dados externa pode reduzir o tempo de execução da consulta e diminuir os custos. Os pushdowns de SQL incluem a eliminação de colunas (cláusulas SELECT) e os pushdowns de filtros (cláusulas WHERE).

Quando usa a funçãoEXTERNAL_QUERY, os pushdowns de SQL funcionam reescrevendo a consulta original. No exemplo seguinte, a função EXTERNAL_QUERY é usada para comunicar com uma base 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 ligação do BigQuery.

Sem os pushdowns de SQL, a seguinte consulta é enviada para o Cloud SQL:

SELECT *
FROM operations_table

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

Com os pushdowns de 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 esta consulta é executada, apenas são enviadas duas colunas e as linhas que correspondem ao predicado de filtragem de volta para o BigQuery.

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

Pode examinar as reduções aplicadas (se existirem) no plano de consulta.

Limitações

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

Limitações para a federação de consultas quando usa o EXTERNAL_QUERY

  • Os pushdowns de SQL só são aplicados a consultas federadas do formulário SELECT * FROM T.
  • Apenas são suportadas a eliminação de colunas e as transferências de filtros. Especificamente, os pushdowns de computação, junção, limite, ordenação por e agregação não são suportados.
  • Para a transferência de filtros, os literais têm de ser de um dos seguintes tipos: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME e TIMESTAMP. Os literais que são estruturas não são suportados.
  • Os pushdowns de funções SQL são aplicados apenas a funções suportadas pelo BigQuery e por uma base de dados de destino.
  • Os pushdowns de SQL só são suportados para o AlloyDB, o Cloud SQL e o Spanner.
  • Os pushdowns de SQL não são suportados para o SAP Datasphere.

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

  • São suportados a eliminação de colunas, a filtragem, o cálculo e a transferência de agregação parcial. Especificamente, a junção, o limite e a ordenação por agregação não são suportados.
  • Para pushdowns de filtros, os literais têm de ser de um dos seguintes tipos: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP, BYTE ou matrizes. Os literais que são estruturas não são suportados.
  • Os pushdowns de funções SQL são aplicados apenas a funções suportadas pelo BigQuery e pelo Spanner.

Funções suportadas por origem de dados

Seguem-se as funções SQL suportadas por origem de dados. Não são suportadas funções para o SAP Datasphere.

Cloud SQL MySQL

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

Spanner – Dialeto do PostgreSQL

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

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

    • Pushdown de computação
    • Partial Aggregate pushdown
    • Funções de string
    • Funções matemáticas
    • Funções de conversão
    • Funções de matriz

  • Quando as consultas são executadas, espere semântica do GoogleSQL e não semântica do PostgreSQL. Por exemplo:

    • Os valores NULL são ordenados primeiro por ordem ascendente por predefinição, ao contrário do PostgreSQL, em que são ordenados por último por predefinição.

    • Os valores do PostgreSQL NUMERIC lidos a partir 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 seguinte consulta devolve 0 linhas: 

      SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "SELECT * from
operations_table where numeric_col = 1.123456789")
       No entanto, a seguinte declaração devolve 1 linha com base na semântica do GoogleSQL: 
      SELECT * from operations_table where numeric_col = 1.123456789

    • A normalização de objetos JSON tem um comportamento diferente. As chaves são ordenadas estritamente lexicograficamente no Spanner JSON, mas no PostgreSQL PG JSONB, são ordenadas primeiro pelo comprimento da chave e, em seguida, lexicograficamente com um comprimento da chave equivalente.

Spanner – Dialeto GoogleSQL

  • Operadores lógicos: AND, OR, NOT.
  • Operadores de comparação: =, >, >=, <, <=, <>, IN, BETWEEN e IS NULL.
  • Operadores aritméticos: +, -, *, / (apenas para INT64, FLOAT64 e NUMERIC).
  • Operadores aritméticos seguros: SAFE_ADD, SAFE_SUBTRACT, SAFE_MULTIPLY, SAFE_DIVIDE (apenas para INT64, FLOAT64 e NUMERIC).
  • Quando usar conjuntos de dados externos, adicionalmente:
    • Transferência de computação,
    • Partial Aggregate pushdown,
    • Funções de string,
    • Funções matemáticas,
    • Funções CAST,
    • Funções de matriz.

Trabalhe com ordenações em origens de dados externas

Uma origem de dados externa pode ter uma ordenação definida numa coluna (por exemplo, sem distinção entre maiúsculas e minúsculas). Quando executa uma consulta federada, a base de dados remota tem em conta a ordenação configurada.

Considere o seguinte exemplo em que tem uma coluna flag com uma ordenação não sensível a maiúsculas e minúsculas na origem de dados externa:

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

Substitua CONNECTION_ID pelo ID da ligação do BigQuery.

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

No entanto, para a federação de consultas com origens de dados do Cloud SQL, SAP Datasphere ou AlloyDB, se adicionar um filtro à consulta principal, a consulta é executada no lado do BigQuery com a ordenação predefinida. Veja a seguinte consulta:

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

Devido à colação predefinida sensível a maiúsculas e minúsculas no BigQuery, a consulta anterior apenas devolve linhas em que a flag é Y e filtra as linhas em que a flag é y. Para tornar a sua cláusula WHERE insensível a maiúsculas e 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'

O que se segue?