Consultas federadas do Cloud SQL

Como analista de dados, é possível consultar dados no Cloud SQL pelo BigQuery usando consultas federadas.

A federação BigQuery Cloud SQL permite que o BigQuery consulte dados que residem no Cloud SQL em tempo real, sem copiar ou mover dados. A federação de consulta aceita instâncias do MySQL (segunda geração) e do PostgreSQL no Cloud SQL.

Como alternativa, para replicar dados no BigQuery, também é possível usar 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

Consultar dados

Para enviar uma consulta federada para o Cloud SQL de um consulta do GoogleSQL, use a Função EXTERNAL_QUERY.

Suponha que você armazene uma tabela de clientes no BigQuery e armazene uma tabela de vendas 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.

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;

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 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 à 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 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 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';");

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: project_id.location_id.connection_id.
location string O local da conexão, que é o mesmo do local da instância do Cloud SQL com as seguintes exceções: a região us-central1 do Cloud SQL é mapeada para o BigQuery nos EUA e a europe-west1 do Cloud SQL é mapeada para o BigQuery na UE.
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 O nome da instância do Cloud SQL, geralmente no formato de:

Project-id:location-id:instance-id

É possível encontrar o ID da instância na página de detalhes Instância 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

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ê estiver monitorando registros para uso de consultas em um banco de dados MySQL ou PostgreSQL, a anotação a seguir poderá ajudar a identificar consultas provenientes do BigQuery.

  1. Acesse a página Análise de registros.

    Acesse o Explorador 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
*/

Solução de problemas

Nesta seção, você verá possíveis problemas ao enviar uma consulta federada para o Cloud SQL.

Problema: falha ao se conectar ao servidor do banco de dados. Se você estiver consultando um banco de dados MySQL, talvez 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.

Como alternativa, se você estiver consultando um banco de dados PostgreSQL, talvez 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: verifique se foram usadas credenciais válidas e se todos os pré-requisitos foram seguidos para criar a conexão para o Cloud SQL. Verifique se a conta de serviço criada automaticamente quando uma conexão com o Cloud SQL é criada tem o papel de cliente do Cloud SQL (roles/cloudsql.client). A conta de serviço tem o seguinte formato: service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com. Para ver instruções detalhadas, consulte Conceder acesso à conta de serviço.

A seguir