Executar consultas federadas do MySQL

Como analista de dados, é possível consultar dados relacionais que residem no MySQL diretamente do BigQuery em tempo real, sem copiar ou mover dados.

A federação do BigQuery MySQL usa a API BigQuery Connection para se conectar a mecanismos de banco de dados MySQL. Em seguida, use a função EXTERNAL_QUERY para enviar a consulta SQL diretamente ao MySQL para execução, e os resultados serão transmitidos diretamente de volta.

Limitações

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

  • Só é possível executar consultas somente leitura usando a função EXTERNAL_QUERY. As instruções de linguagem de definição de dados (DDL) e de linguagem de manipulação de dados (DML) não são compatíveis.
  • A federação só pode ser acessada pela função EXTERNAL_QUERY. Não é possível criar tabelas externas do BigQuery ou conjuntos de dados permanentes que sejam mapeados diretamente para tabelas do MySQL.
  • A latência da consulta federada pode ser visivelmente maior do que a execução da consulta diretamente no MySQL.
  • O serviço de conexão subjacente pode hibernar conexões inativas. A primeira consulta que usa uma conexão do MySQL após um período de inatividade pode levar mais de um minuto para ser executada enquanto o conector é inicializado.
  • O peering de DNS não é compatível. É preciso se conectar usando um endereço IP ou um nome de host totalmente resolvível na rede VPC.
  • A consulta SQL do MySQL precisa especificar aliases de coluna explícitos para todas as colunas que contêm resultados ou expressões de função (por exemplo, SELECT COUNT(*) AS total_count). Expressões sem alias produzem nomes de coluna com caracteres especiais que são inválidos nas definições de esquema de tabela do BigQuery.
  • Quando o uso da API Compute Engine no projeto de consulta é restrito pelo VPC Service Controls, a consulta federada falha se a conexão usa um anexo de rede. Verifique se as regras de entrada e saída apropriadas do VPC Service Controls estão configuradas.

  • Não há suporte para pushdowns SQL adicionais no MySQL. Os filtros e as agregações na consulta externa do BigQuery não são enviados para a consulta interna na função EXTERNAL_QUERY. É preciso especificar predicados e projeções de coluna diretamente na instrução SQL interna do MySQL para limitar as linhas e colunas transferidas pela rede.

  • Somente a autenticação de nome de usuário e senha do banco de dados MySQL é compatível. Não há suporte para provedores de identidade de terceiros, Kerberos e autenticação do IAM do provedor de nuvem.

  • As instruções CREATE TABLE ... AS SELECT (CTAS) falham com erros Unsupported type INT32 ou Unsupported type FLOAT se a consulta selecionar colunas de números inteiros de 32 bits (TINYINT, SMALLINT e BOOLEAN / TINYINT(1)) ou colunas flutuantes de precisão única (FLOAT). Para salvar os resultados da consulta em uma nova tabela do BigQuery, converta explicitamente as colunas de números inteiros em INT64 e as colunas flutuantes em FLOAT64 na consulta:

    CREATE TABLE mydataset.mytable AS
SELECT
CAST(tiny_col AS INT64) AS tiny_col,
CAST(small_col AS INT64) AS small_col,
...
FROM EXTERNAL_QUERY(...);

    A inserção em uma tabela com INSERT INTO <table> SELECT ... é bem-sucedida sem transmissões manuais porque o BigQuery amplia automaticamente os valores INT32 e FLOAT para corresponder às colunas INT64 e FLOAT64 da tabela de destino.

  • Embora as colunas DECIMAL e NUMERIC do MySQL sejam mapeadas para BIGNUMERIC(76, 38), o MySQL permite precisão de até 65. Se um valor tiver mais de 38 dígitos inteiros (a precisão menos a escala é maior que 38), ele excederá o intervalo de números inteiros BIGNUMERIC e a consulta falhará no ambiente de execução. Para consultar valores com mais de 38 dígitos inteiros, converta-os em CHAR ou VARCHAR na instrução EXTERNAL_QUERY.

  • O tipo de dados TIME trunca valores fracionários de subsegundos (microssegundos) em segundos inteiros (por exemplo, 12:34:56.789123 se torna 12:34:56). Além disso, o TIME do MySQL é compatível com durações decorridas de -838:59:59 a 838:59:59, enquanto o TIME do BigQuery só é compatível com valores de hora do dia de 00:00:00 a 23:59:59.999999. Valores fora de 00:00:00 a 23:59:59 causam erros de consulta no ambiente de execução. Se forem necessários valores de precisão de microssegundos ou de duração decorrida, converta a coluna em CHAR ou VARCHAR dentro da instrução EXTERNAL_QUERY.

  • As datas e os carimbos de data/hora anteriores a 15 de outubro de 1582 estão sujeitos a uma mudança de 10 dias no calendário devido à transição histórica do calendário gregoriano. Para preservar datas históricas exatas anteriores a 1582, converta-as em CHAR ou VARCHAR na instrução EXTERNAL_QUERY.

Ambientes e versões compatíveis do MySQL

A federação de consultas é compatível com bancos de dados MySQL nos seguintes ambientes de implantação:

  • Bancos de dados MySQL hospedados na nuvem ou no local acessíveis por endereços IP públicos ou anexos de rede (pelo Cloud VPN ou Cloud Interconnect)
  • Instâncias do MySQL auto-hospedadas no Compute Engine ou em outra infraestrutura
  • MySQL 8.4 LTS e 8.0

Antes de começar

Verifique se o administrador do BigQuery criou uma conexão do MySQL e compartilhou com você.

Funções exigidas

Para receber as permissões necessárias para consultar o MySQL, peça ao administrador para conceder a você o papel do IAM de Usuário de conexão do BigQuery (roles/bigquery.connectionUser) no projeto. Para mais informações sobre a concessão de papéis, consulte Gerenciar o acesso a projetos, pastas e organizações.

Também é possível conseguir as permissões necessárias por meio de papéis personalizados ou de outros papéis predefinidos.

Consultar dados

Para enviar uma consulta federada ao MySQL de uma consulta do GoogleSQL, use a função EXTERNAL_QUERY.

O exemplo a seguir é uma consulta federada que mescla registros de clientes no BigQuery chamados mydataset.customers com pedidos de vendas transacionais no MySQL:

SELECT
  c.customer_id,
  c.name,
  fo.first_order_date
FROM
  mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'projects/my-project/locations/us-central1/connections/mysql_prod_conn',
  '''SELECT CUSTOMER_ID, MIN(ORDER_DATE) AS first_order_date
     FROM ORDERS
     GROUP BY CUSTOMER_ID''') AS fo
  ON fo.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.name,
  fo.first_order_date;

Ver um esquema de tabela do MySQL

É possível consultar as visualizações INFORMATION_SCHEMA do MySQL pelo EXTERNAL_QUERY para inspecionar metadados do banco de dados, descobrir tabelas e analisar esquemas de colunas antes de executar análises.

Listar todos os bancos de dados

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
     FROM INFORMATION_SCHEMA.SCHEMATA''');

Listar tabelas em um banco de dados

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = 'DATABASE_NAME'""");

Ver colunas e tipos de dados em uma tabela

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_TYPE
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = 'DATABASE_NAME' AND TABLE_NAME = 'TABLE_NAME'""");

Mapeamento de tipo de dados

Quando você executa uma consulta federada, o BigQuery converte os dados do MySQL em tipos de dados do GoogleSQL de acordo com a seguinte tabela:

Tipo de dados do MySQL Tipo de dados do BigQuery
Tipos numéricos
BOOLEAN, BOOL, TINYINT(1) INT64 (consultas interativas; consulte Limitações para CREATE TABLE ... AS SELECT)
TINYINT, SMALLINT INT64 (consultas interativas; consulte Limitações para CREATE TABLE ... AS SELECT)
MEDIUMINT, INT, INTEGER INT64
BIGINT INT64
BIGINT UNSIGNED, BIGINT ZEROFILL, SERIAL BIGNUMERIC(76, 38)
DECIMAL, DEC, NUMERIC, FIXED BIGNUMERIC(76, 38)
FLOAT FLOAT64 (consultas interativas; consulte Limitações para CREATE TABLE ... AS SELECT)
DOUBLE, DOUBLE PRECISION, REAL FLOAT64
Tipos de data e hora
DATE DATE
DATETIME DATETIME
TIMESTAMP TIMESTAMP
TIME TIME
Tipos de string e texto
CHAR STRING
VARCHAR STRING
BINARY BYTES
VARBINARY BYTES
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB BYTES
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT STRING
ENUM STRING
SET STRING

Preços

O custo de executar uma consulta federada é baseado em três fatores:

  • O custo de computação da execução da consulta no MySQL (cobrado pelo provedor de banco de dados MySQL ou pelo ambiente de hospedagem)
  • O custo da largura de banda da transferência dos resultados da consulta do MySQL para o BigQuery (cobranças de rede de saída do provedor de hospedagem do banco de dados)
  • O custo de computação da execução da consulta no BigQuery.

Todos os custos de computação e saída do MySQL são faturados diretamente pelo seu provedor do MySQL ou host de infraestrutura, não pelo Google. Para limitar o custo da largura de banda, recomendamos que você grave a consulta na instrução EXTERNAL_QUERY para que exclua todas as colunas e linhas que não sejam necessárias para calcular o resultado final.

Não há custo adicional para executar consultas federadas no BigQuery. Para mais informações sobre os preços do BigQuery, consulte Preços.

Se a conexão usar componentes de rede do Google Cloud , os seguintes preços também poderão ser aplicados:

A seguir