Funções de consulta federada

O GoogleSQL para BigQuery oferece suporte às seguintes funções de consulta federada.

Lista de funções

Nome Resumo
EXTERNAL_QUERY Executa uma consulta em um banco de dados externo e retorna os resultados como uma tabela temporária.

EXTERNAL_QUERY

EXTERNAL_QUERY('connection_id', '''external_database_query'''[, 'options'])

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 do GoogleSQL 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. Se você não tiver um projeto padrão configurado, anexe o ID do projeto ao ID da conexão no seguinte formato:

    projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID

    Substitua:

    • PROJECT_ID: o ID do projeto.
    • LOCATION: o local da conexão.
    • CONNECTION_ID: o ID da conexão.

    Por exemplo, projects/example-project/locations/us/connections/sql-bq. Para mais informações, consulte Criar um recurso de conexão.

+ options: uma string opcional de um mapa no formato JSON com pares de chave-valor de opção de nome e valor (ambos diferenciam maiúsculas de minúsculas).

For example::
``` '{"default_type_for_decimal_columns":"numeric"}' ```

Supported options:

|Option Name | Description
|-------- | -------
|"default_type_for_decimal_columns" | Can be "float64", "numeric", "bignumeric" or "string". With this option, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to the provided BigQuery type. When this option is not provided, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to BigQuery NUMERIC type.
|"query_execution_priority" | Can be "low", "medium" or "high". Only supported in Spanner. Specifies priority for execution of the query. Execution priority is "medium" by default.

Outras observações:

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

Ao executar uma consulta federada, os dados do banco de dados externo são convertidos em tipos do GoogleSQL. 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.
  • Os tipos de dados numéricos no MySQL e no PostgreSQL serão mapeados para o valor NUMERIC do BigQuery por padrão. O intervalo de valor NUMERIC do BigQuery é menor que no MySQL e no PostgreSQL. Ele também pode ser mapeado para BIGNUMERIC,FLOAT64 ou STRING com "default_type_for_decimal_columns" nas opções do EXTERNAL_QUERY.

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, BIGNUMERIC, FLOAT64 ou STRING

 DECIMAL (M,D) será mapeado para NUMERIC por padrão ou pode ser mapeado para BIGNUMERIC, FLOAT64 ou STRING com default_type_for_decimal_columns.
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. BYTES
MEDIUM_BLOB Um BLOB com comprimento máximo de 16777215 caracteres. BYTES
LONG_BLOB Um BLOB com comprimento máximo de 4294967295 caracteres. BYTES
TINY_BLOB Um BLOB com comprimento máximo de 255 caracteres. BYTES
Binário Uma string de comprimento fixo entre 1 e 255 caracteres. BYTES
VARBINARY Uma string de comprimento variável entre 1 e 255 caracteres. BYTES
Outro
SET Quando declarar a coluna SET, predefina alguns valores. Em seguida, INSIRA qualquer conjunto de valores predefinidos nesta coluna. STRING
GEOMETRY GEOGRAPHY 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, BIGNUMERIC, FLOAT64 ou STRING numeric [ (p, s) ] será mapeado para NUMERIC por padrão ou pode ser mapeado para BIGNUMERIC, FLOAT64 ou STRING com default_type_for_decimal_columns.
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 do MySQL e do PostgreSQL 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 não compatí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.

Mapeamento de tipo do Spanner para o BigQuery

Ao executar uma consulta federada do Spanner, os dados do Spanner são convertidos em tipos do GoogleSQL.

Tipo de GoogleSQL do Spanner Tipo de PostgreSQL do Spanner Tipo do BigQuery
ARRAY - ARRAY
BOOL bool BOOL
BYTES bytea BYTES
DATE date DATE
FLOAT64 float8 FLOAT64
INT64 bigint INT64
JSON JSONB JSON
NUMERIC numeric* NUMERIC
STRING varchar STRING
STRUCT - Não compatível com consultas federadas do Spanner
TIMESTAMP timestamptz TIMESTAMP com nanossegundos truncados

* Os valores numéricos do PostgreSQL com uma precisão maior que a precisão compatível com o BigQuery são arredondados. Valores maiores que o máximo geram um erro Invalid NUMERIC value.

Se a consulta externa tiver um tipo de dados não compatível com consultas federadas, a consulta vai falhar imediatamente. É possível converter o tipo de dados não aceito em um tipo de dados compatível.