Consultas federadas do Cloud SQL

As consultas federadas no BigQuery permitem consultar dados que residem no Cloud SQL em tempo real sem copiar ou mover dados. A federação de consultas é compatível com instâncias do Cloud SQL para MySQL e do Cloud SQL para PostgreSQL no Cloud SQL.

Como alternativa, para replicar dados no BigQuery, use o Cloud Data Fusion ou o Datastream. Para mais informações sobre como usar o Cloud Data Fusion, consulte Como replicar dados do MySQL para o BigQuery.

Antes de começar

Para executar consultas federadas nos seus dados no Cloud SQL, faça o seguinte:

1. Verifique se o administrador do BigQuery criou uma conexão do Cloud SQL e compartilhou a conexão com você.
2. Verifique se o agente de serviço de conexão do BigQuery recebeu o papel de cliente do Cloud SQL (roles/cloudsql.client). Para mais informações, consulte Conceder acesso ao agente de serviço.
3. Se a instância do Cloud SQL usar um endereço IP particular, verifique se um caminho particular foi ativado quando a instância foi criada. Para mais informações, consulte Conectar-se ao Cloud SQL e Configurar IP particular.

Funções exigidas

Para receber as permissões necessárias para consultar uma instância do Cloud SQL, peça ao administrador para conceder a você o papel do IAM de Usuário de conexão do BigQuery (roles/bigquery.connectionUser) no seu 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 para o Cloud SQL de um consulta do GoogleSQL, use a Função EXTERNAL_QUERY.

Formato do identificador de conexão

O primeiro argumento para a função EXTERNAL_QUERY é o identificador de conexão. É possível especificar o identificador de conexão usando um dos seguintes formatos:

• Formato curto: [LOCATION.]CONNECTION_ID (por exemplo, 'us.my-connection' ou 'my-connection')
• Formato totalmente qualificado: projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID
• Formato de caminho simples: PROJECT_ID.LOCATION.CONNECTION_ID (por exemplo, 'my-project.us.my-connection')

Se você executar uma consulta em uma visualização compartilhada em vários projetos, use sempre o identificador de conexão totalmente qualificado para evitar a conexão com o projeto errado.

Executar uma consulta federada

Suponha que você armazene uma tabela de clientes no BigQuery e armazene uma tabela de pedidos no Cloud SQL, e queira mesclar as duas tabelas em uma única consulta. O exemplo a seguir faz uma consulta federada a uma tabela do Cloud SQL chamada orders e mescla os resultados com uma tabela do BigQuery chamada mydataset.customers.

Selecione uma das seguintes opções para executar a consulta federada:

Console

1. No console do Google Cloud , acesse a página BigQuery.

   Acessar o BigQuery

2. No editor de consultas, insira sua consulta:

   SELECT c.customer_id, c.name, rq.first_order_date
   FROM mydataset.customers AS c
   LEFT OUTER JOIN EXTERNAL_QUERY(
     'us.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;
   

   Substitua CONNECTION_ID pelo ID da conexão do BigQuery.

3. Clique em Executar.

   Os resultados da consulta aparecem no painel Resultados da consulta.

bq

2. No console do Google Cloud , ative o Cloud Shell.

   Ativar o Cloud Shell

   Na parte inferior do console do Google Cloud , uma sessão do Cloud Shell é iniciada e exibe um prompt de linha de comando. O Cloud Shell é um ambiente shell com a CLI do Google Cloud já instalada e com valores já definidos para o projeto atual. A inicialização da sessão pode levar alguns segundos.

3. Execute o comando bq query com a flag --use_legacy_sql=false:

   bq query \
       --use_legacy_sql=false \
       'SELECT c.customer_id, c.name, rq.first_order_date
       FROM mydataset.customers AS c
       LEFT OUTER JOIN EXTERNAL_QUERY(
         "us.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;'
   

   Substitua CONNECTION_ID pelo ID da conexão do BigQuery.

A consulta de exemplo 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 operacional do Cloud SQL 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 à tabela de clientes no BigQuery por customer_id.
3. Selecionar as informações do cliente e a data do primeiro pedido.

Visualizar um esquema de tabela do Cloud SQL

É possível usar a função EXTERNAL_QUERY() para consultar visualizações INFORMATION_SCHEMA e acessar metadados do banco de dados, como listar todas as tabelas no banco de dados ou mostrar esquemas de tabela. As consultas de exemplo INFORMATION_SCHEMA a seguir funcionam tanto no MySQL quanto no PostgreSQL. Saiba mais em Tabelas information_schema do MySQL e Tabelas information_schema do 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';");

Substitua CONNECTION_ID pelo ID da conexão do BigQuery.

Rastrear consultas federadas do BigQuery

Quando você executa uma consulta federada no Cloud SQL, o BigQuery anota a consulta com um comentário semelhante ao seguinte:

/* Federated query from BigQuery. Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID. */

Se você monitorar registros para uso de consultas em um banco de dados MySQL ou PostgreSQL, a anotação vai ajudar a identificar consultas originadas do BigQuery.

1. No console do Google Cloud , acesse a página Análise de registros.

   Acessar a Análise de registros

2. Na guia Consulta, insira a seguinte consulta:

   resource.type="cloudsql_database"
   textPayload=~"Federated query from BigQuery"
   

3. Clique em Executar consulta.

   Se houver registros disponíveis para consultas federadas do BigQuery, uma lista de registros semelhante à seguinte aparecerá nos Resultados da consulta:

   YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]
   db=DATABASE, user=USER_ACCOUNT
   STATEMENT: SELECT 1 FROM (SELECT FROM company_name_table) t;
   /* Federated query from BigQuery.
   Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
   */
   
   YYYY-MM-DD hh:mm:ss.millis UTC [3210532]: [2-1]
   db=DATABASE, user=USER_ACCOUNT
   STATEMENT: SELECT "company_id", "company type_id" FROM
   (SELECT FROM company_name_table) t;
   /* Federated query from BigQuery.
   Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
   */

Detalhes da conexão

A tabela a seguir mostra as propriedades de conexão do Cloud SQL:

Nome da propriedade	Valor	Descrição
name	string	Nome do recurso de conexão no formato: projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID.
location	string	Local da conexão, que precisa corresponder ao local da instância do Cloud SQL ou ser uma multirregião da jurisdição correspondente. Por exemplo, uma instância do Cloud SQL em us-east4 pode usar US, enquanto uma instância do Cloud SQL em europe-north1 pode usar EU. Somente as consultas do BigQuery executadas nesse local podem usar essa conexão.
friendlyName	string	Um nome de exibição fácil de usar para a conexão.
description	string	Descrição da conexão.
cloudSql.type	string	Pode ser POSTGRES ou MYSQL.
cloudSql.instanceId	string	Nome da instância do Cloud SQL, geralmente no formato: PROJECT_ID:LOCATION:INSTANCE_ID. Encontre o ID da instância na página Instâncias do Cloud SQL.
cloudSql.database	string	O banco de dados do Cloud SQL ao qual você quer se conectar.
cloudSql.serviceAccountId	string	A conta de serviço configurada para acessar o banco de dados do Cloud SQL.

A tabela a seguir mostra as propriedades da credencial da instância do Cloud SQL:

Nome da propriedade	Valor	Descrição
username	string	Nome de usuário do banco de dados.
password	string	Senha do banco de dados.

Desempenho de consultas e práticas recomendadas

Para otimizar o desempenho das consultas federadas e evitar uma carga desnecessária na instância do Cloud SQL, considere as seguintes práticas recomendadas:

• Filtre no início da consulta externa: aplique filtros WHERE e agregações GROUP BY dentro da instrução SQL EXTERNAL_QUERY em vez de filtrar na consulta externa do BigQuery. A execução de filtros no Cloud SQL reduz a quantidade de dados transferidos pela rede para o BigQuery.
• Remover colunas: selecione apenas as colunas específicas necessárias na instrução EXTERNAL_QUERY em vez de usar SELECT *. Isso reduz o volume de transferência de rede e o tempo de execução da consulta.
• Use réplicas de leitura: para isolar as cargas de trabalho de consulta analítica do banco de dados de produção principal, crie uma réplica de leitura do Cloud SQL e configure a conexão do BigQuery para consultar a réplica.
• Colocar conjuntos de dados e instâncias no mesmo local: para ter a menor latência, coloque o conjunto de dados e a conexão do BigQuery na mesma região da instância do Cloud SQL ou na multirregião correspondente (como US ou EU). Para mais informações, consulte Regiões com disponibilidade.

Solução de problemas

Esta seção ajuda a resolver problemas que você pode encontrar ao enviar uma consulta federada para o Cloud SQL.

Falha ao se conectar ao servidor do banco de dados

Ao consultar um banco de dados MySQL, talvez você encontre o seguinte erro:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query.

Ao consultar um banco de dados PostgreSQL, talvez você encontre o seguinte erro:

Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

Resolução

Para resolver esse problema, verifique os seguintes pré-requisitos:

1. Verifique se foram usadas credenciais válidas e se todas as etapas de configuração foram seguidas ao criar a conexão para o Cloud SQL.

2. Verifique se o agente de serviço de conexão do BigQuery recebeu o papel de cliente do Cloud SQL (roles/cloudsql.client). O e-mail do agente de serviço é formatado como:

   [e-mail]

   Para instruções detalhadas, consulte Conceder acesso ao agente de serviço.

3. Se a instância do Cloud SQL usar um endereço IP particular, verifique se você ativou um caminho particular ao criar a instância do Cloud SQL. Ao ativar um caminho particular, o BigQuery pode acessar dados no Cloud SQL e executar consultas em uma conexão particular.

Falha ao se conectar ao banco de dados MySQL devido ao plug-in de autenticação

Ao executar uma consulta federada em uma instância do MySQL, talvez você encontre o seguinte erro:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2059): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at [1:15]

Resolução

Esse erro ocorre em contas de usuário do banco de dados configuradas com a autenticação caching_sha2_password, que não é compatível com consultas federadas.

Se a instância do Cloud SQL executar o MySQL versão 8.0 ou anterior, será possível mudar o plug-in de autenticação da conta de usuário usada pelo BigQuery. Execute o seguinte comando na instância do Cloud SQL:

ALTER USER 'USERNAME'@'%' IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

Substitua:

• USERNAME: a conta de usuário do banco de dados que o BigQuery usa para se conectar à instância do Cloud SQL para MySQL.
• PASSWORD: a senha da conta de usuário do banco de dados.

Se a instância do Cloud SQL executar o MySQL versão 8.4, o plug-in mysql_native_password será descontinuado. Não recomendamos fazer o downgrade de um banco de dados atual da versão 8.4 para a 8.0 em cargas de trabalho de produção.

O upgrade de uma instância de uma versão anterior para o MySQL 8.4 não afeta as conexões de banco de dados atuais, desde que você não mude o plug-in de autenticação do usuário antes do upgrade. Como apenas os usuários criados com mysql_native_password podem ser usados para consultas federadas, as consultas enviadas ao MySQL 8.4 precisam usar uma conta de usuário criada antes do upgrade.

Limitações

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

• Consultas somente leitura: as consultas federadas são somente leitura. As consultas externas precisam ser instruções SELECT. Não há suporte para instruções de linguagem de definição de dados (DDL) e linguagem de manipulação de dados (DML).
• Desempenho da consulta: as consultas federadas não são tão rápidas quanto as consultas ao armazenamento do BigQuery. O BigQuery precisa esperar que o Cloud SQL execute a consulta externa e transfira o conjunto de resultados pela rede.
• Restrições de região: o local de processamento de consultas do BigQuery precisa corresponder ou ser compatível com o local da instância do Cloud SQL. Confira mais informações nas regiões com disponibilidade.
• Tipos de dados não compatíveis: se uma consulta externa retornar um tipo de dados não compatível com o BigQuery, ela vai falhar. É possível converter o tipo de dados não aceito em um tipo de dados compatível na consulta externa. Para mais informações, consulte Mapeamentos de tipo de dados.
• Máximo de conexões: uma consulta federada pode referenciar no máximo 10 conexões exclusivas.
• Limitação do plug-in de autenticação: o plug-in caching_sha2_password do MySQL não é compatível com consultas federadas. Para mais informações, consulte Solução de problemas.

A seguir

• Saiba mais sobre consultas federadas.
• Saiba mais sobre o mapeamento de tipos de dados do MySQL para o BigQuery.
• Saiba mais sobre o mapeamento de tipos de dados do PostgreSQL para o BigQuery.
• Saiba mais sobre os tipos de dados não compatíveis.