Funções de consulta federada no SQL padrão

O BigQuery é compatível com as seguintes funções de consulta federada.

EXTERNAL_QUERY

EXTERNAL_QUERY('connection_id', '''external_database_query''')

Descrição

Executa uma consulta em um banco de dados externo e retorna os resultados como uma tabela temporária. O tipo de dados do banco de dados externo é convertido em um tipo de dados SQL padrão na tabela de resultados temporários com esses mapeamentos de tipo de dados.

  • external_database_query: a consulta a ser executada no banco de dados externo.
  • connection_id: o código do recurso de conexão. O recurso de conexão contém configurações para a conexão entre o banco de dados externo e o BigQuery.

Observações adicionais:

  • A função EXTERNAL_QUERY é normalmente usada em uma cláusula FROM.
  • É possível usar a função EXTERNAL_QUERY() para acessar os metadados sobre o banco de dados externo.
  • EXTERNAL_QUERY() não respeitará a ordem do resultado da consulta externa, mesmo que ela inclua ORDER BY.

Tipo de dados retornados

Tabela do BigQuery

Exemplos

Suponha que você precise incluir a data do primeiro pedido de cada um dos seus clientes em um relatório. No momento, esses dados não estão no BigQuery, mas estão disponíveis no banco de dados PostgreSQL operacional no Cloud SQL. O exemplo de consulta federada a seguir faz isso e inclui três partes:

  1. Executar a consulta externa SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id no banco de dados PostgreSQL operacional para conseguir a primeira data de pedido de cada cliente por meio da função EXTERNAL_QUERY().
  2. Mesclar a tabela de resultados de consultas externas com a tabela de clientes no BigQuery por customer_id.
  3. Selecionar as informações do cliente e a data do primeiro pedido.
SELECT
  c.customer_id, c.name, SUM(t.amount) AS total_revenue, rq.first_order_date
FROM customers AS c
INNER JOIN transaction_fact AS t ON c.customer_id = t.customer_id
LEFT OUTER JOIN
  EXTERNAL_QUERY(
    'connection_id',
    '''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;

É possível usar a função EXTERNAL_QUERY() para consultar tabelas information_schema, acessar metadados de bancos de dados, como listar todas as tabelas no banco de dados ou mostrar esquemas de tabelas. As consultas information_schema de exemplo a seguir funcionam tanto em MySQL quanto em PostgreSQL.

-- List all tables in a database.
SELECT *
FROM
  EXTERNAL_QUERY(
    'connection_id',
    '''SELECT * FROM information_schema.tables'''
  );
-- List all columns in a table.
SELECT *
FROM
  EXTERNAL_QUERY(
    'connection_id',
    '''SELECT * FROM information_schema.columns WHERE table_name='x';'''
  );

EXTERNAL_QUERY() não respeitará a ordem do resultado da consulta externa, mesmo que ela inclua ORDER BY. A consulta de exemplo a seguir ordena as linhas pelo ID de cliente no banco de dados externo, mas o BigQuery não exibirá as linhas de resultado nesse pedido.

-- ORDER BY will not order rows.
SELECT *
FROM
  EXTERNAL_QUERY(
    'connection_id',
    '''SELECT * FROM customers AS c ORDER BY c.customer_id'''
  );

Mapeamentos de tipo de dados

Quando você executa uma consulta federada, os dados do banco de dados externo são convertidos em tipos SQL padrão do BigQuery. Veja abaixo os mapeamentos de tipos de dados do MySQL para o BigQuery e do PostgreSQL para o BigQuery.

O que saber sobre mapeamentos:

  • A maioria dos tipos de dados MySQL pode ser correspondida ao mesmo tipo de dados do BigQuery, com algumas exceções, como decimal, timestamp e time.
  • O PostgreSQL aceita muitos tipos de dados não padrão que não são aceitos no BigQuery. Por exemplo, money, path, uuid, boxer e outros.
  • O intervalo de valores numéricos do BigQuery é menor que no MySQL e no PostgreSQL.

Tratamento de erros

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 de dados MySQL/PostgreSQL diferente que seja compatível. Consulte tipos de dados não compatíveis para mais informações sobre como transmitir.

Mapeamento de tipo MySQL para BigQuery

Tipo do MySQL Descrição do MySQL Tipo do BigQuery Diferença entre os tipos
Número inteiro
INT 4 bytes, 2^32 - 1 INT64
TINYINT 1 byte, 2^8 - 1 INT64
SMALLINT 2 bytes, 2^16 - 1 INT64
MEDIUMINT 3 bytes, 2^24 - 1 INT64
BIGINT 8 bytes, 2^64 - 1 INT64
UNSIGNED BIGINT 8 bytes, 2^64 - 1 NUMERIC
Numérico exato
DECIMAL (M,D) Um decimal é representado por (M,D), em que M é o número total de dígitos e D é o número de decimais. M <= 65 NUMERIC

O intervalo NUMERIC do BigQuery é menor, aceita apenas 38 dígitos decimais de precisão e 9 dígitos decimais de escala.
Numérico aproximado
FLOAT (M,D) 4 bytes, M <= 23 FLOAT64
DOUBLE (M,D) 8 bytes, M <= 53 FLOAT64
Data e hora
TIMESTAMP '1970-01-01 00:00:01'UTC a '2038-01-19 03:14:07' UTC. TIMESTAMP O TIMESTAMP do MySQL é recuperado como fuso horário UTC, não importa de onde o usuário chame o BigQuery
DATETIME '1000-01-01 00:00:00' a '9999-12-31 23:59:59' DATETIME
DATE '1000-01-01' a '9999-12-31'. DATE
TIME Hora no formato "HH:MM:SS"
"-838:59:59" a "838:59:59".
TIME
O intervalo TIME do BigQuery é menor, de 00:00:00 a 23:59:59
YEAR INT64
Caractere e strings
ENUM Objeto string com um valor escolhido em uma lista de valores permitidos. STRING
CHAR (M) Uma string de comprimento fixo entre 1 e 255 caracteres. STRING
VARCHAR (M) Uma string de comprimento variável entre 1 e 255 caracteres. STRING
TEXT Um campo com comprimento máximo de 65535 caracteres. STRING
TINYTEXT Coluna TEXT com comprimento máximo de 255 caracteres. STRING
MEDIUMTEXT Coluna TEXT com comprimento máximo de 16777215 caracteres. STRING
LONGTEXT Coluna TEXT com comprimento máximo de 4294967295 caracteres. STRING
Binário
BLOB Um objeto grande binário com comprimento máximo de 65535 caracteres. BYTE
MEDIUM_BLOB Um BLOB com comprimento máximo de 16777215 caracteres. BYTE
LONG_BLOB Um BLOB com comprimento máximo de 4294967295 caracteres. BYTE
TINY_BLOB Um BLOB com comprimento máximo de 255 caracteres. BYTE
Outro
SET Quando declarar a coluna SET, predefina alguns valores. Em seguida, INSIRA qualquer conjunto de valores predefinidos nesta coluna. STRING
GEOMETRY GEOMETRY INCOMPATÍVEL
BIT INT64 INCOMPATÍVEL

Mapeamento de tipo PostgreSQL a BigQuery

Nome Descrição Tipo do BigQuery Diferença entre os tipos
Número inteiro
smallint 2 bytes, -32768 a +32767 INT64
smallserial Consulte smallint INT64
integer 4 bytes, -2147483648 a +2147483647 INT64
serial Consulte integer INT64
bigint 8 bytes, -9223372036854775808 a 9223372036854775807 INT64
bigserial Consulte bigint INT64
Numérico exato
numeric [ (p, s) ] Precisão de até 1.000. NUMERIC O intervalo NUMERIC do BigQuery é menor, aceita apenas 38 dígitos decimais de precisão e 9 dígitos decimais de escala.
Decimal [ (p, s) ] Consulte numeric NUMERIC Consulte numeric
money 8 bytes, escala de 2 dígitos, -92233720368547758,08 a +92233720368547758,07 INCOMPATÍVEL
Numérico aproximado
real 4 bytes, número de ponto flutuante de precisão única FLOAT64
double precision 8 bytes, número de ponto flutuante de precisão dupla FLOAT64
Data e hora
date data do calendário (ano, mês, dia) DATE
time [ (p) ] [ without time zone ] hora do dia (sem fuso horário) TIME
time [ (p) ] with time zone hora do dia, incluindo fuso horário INCOMPATÍVEL
timestamp [ (p) ] [ without time zone ] data e hora (sem fuso horário) DATETIME
timestamp [ (p) ] with time zone data e hora, incluindo fuso horário TIMESTAMP O TIMESTAMP do PostgreSQL é recuperado como fuso horário UTC, não importa de onde o usuário chame o BigQuery
interval Uma duração de tempo INCOMPATÍVEL
Caractere e strings
character [ (n) ] string de caracteres com comprimento fixo STRING
character varying [ (n) ] string de caracteres com comprimento variável STRING
text string de caracteres com comprimento variável STRING
Binário
bytea dados binários ("matriz de bytes") BYTES
bit [ (n) ] string de bits com comprimento fixo BYTES
bit varying [ (n) ] string de bits com comprimento variável BYTES
Outro
boolean booleano lógico (verdadeiro/falso) BOOL
inet Endereço de host IPv4 ou IPv6 INCOMPATÍVEL
path caminho geométrico em um plano INCOMPATÍVEL
pg_lsn número de sequência de registro do PostgreSQL INCOMPATÍVEL
point ponto geométrico em um plano INCOMPATÍVEL
polygon caminho geométrico fechado em um plano INCOMPATÍVEL
tsquery consulta de pesquisa de texto INCOMPATÍVEL
tsvector documento de pesquisa de texto INCOMPATÍVEL
txid_snapshot snapshot do código da transação no nível do usuário INCOMPATÍVEL
uuid identificador universalmente exclusivo INCOMPATÍVEL
xml dados XML STRING
box caixa retangular em um plano INCOMPATÍVEL
cidr endereço de rede IPv4 ou IPv6 INCOMPATÍVEL
circle círculo em um plano INCOMPATÍVEL
interval [ fields ] [ (p) ] período INCOMPATÍVEL
json dados JSON textuais STRING
jsonb dados JSON binários, decompostos INCOMPATÍVEL
line linha infinita em um plano INCOMPATÍVEL
lseg segmento de linha em um plano INCOMPATÍVEL
macaddr endereço MAC (controle de acesso à mídia) INCOMPATÍVEL
macaddr8 endereço MAC (controle de acesso à mídia) (formato EUI-64) INCOMPATÍVEL

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 de dados MySQL/PostgreSQL que seja compatível.

  • Tipo de dados MySQL incompatível

    • Mensagem de erro: Invalid table-valued function external_query Found unsupported MySQL type in BigQuery. at [1:15]
    • Tipo incompatível: GEOMETRY, BIT
    • Resolução: converta o tipo de dados não compatível em STRING.
    • Exemplo: SELECT ST_AsText(ST_GeomFromText('POINT(1 1)')); Este comando converte o tipo de dados incompatível GEOMETRY em STRING.
  • Tipo de dados PostgreSQL não compatível

    • Mensagem de erro: Invalid table-valued function external_query Postgres type (OID = 790) is not supported now at [1:15]
    • Tipo incompatível: money, time with time zone, inet, path, pg_lsn, point, polygon, tsquery, tsvector, txid_snapshot, uuid, box, cidr, circle, interval, jsonb, line, lseg, macaddr, macaddr8
    • Resolução: converta o tipo de dados não compatível em STRING.
    • Exemplo: SELECT CAST('12.34'::float8::numeric::money AS varchar(30)); Este comando converte o tipo de dados incompatível money em string.