Consultas federadas do Cloud SQL

Como analista de dados, pode consultar dados no Cloud SQL a partir do BigQuery usando consultas federadas.

A federação do Cloud SQL do BigQuery permite ao BigQuery consultar dados residentes no Cloud SQL em tempo real, sem copiar nem mover dados. A federação de consultas suporta instâncias do MySQL (2.ª geração) e do PostgreSQL no Cloud SQL.

Em alternativa, para replicar dados no BigQuery, também pode usar o Cloud Data Fusion ou o Datastream. Para saber mais sobre a utilização do Cloud Data Fusion, consulte o artigo Replique dados do MySQL para o BigQuery.

Antes de começar

Consultar dados

Para enviar uma consulta federada para o Cloud SQL a partir de uma consulta GoogleSQL, use a função EXTERNAL_QUERY.

Suponhamos que armazena uma tabela de clientes no BigQuery, enquanto armazena uma tabela de vendas no Cloud SQL e quer juntar as duas tabelas numa única consulta. O exemplo seguinte faz uma consulta federada a uma tabela do Cloud SQL denominada orders e junta os resultados a uma tabela do BigQuery denominada 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 3 partes:

  1. Execute a consulta externa SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id na base de dados PostgreSQL operacional para obter a data da primeira encomenda de cada cliente através da função EXTERNAL_QUERY().
  2. Junte a tabela de resultados da consulta externa à tabela de clientes no BigQuery por customer_id.
  3. Selecione as informações do cliente e a data da primeira encomenda.

Veja um esquema de tabela do Cloud SQL

Pode usar a função EXTERNAL_QUERY() para consultar tabelas information_schema para aceder a metadados da base de dados, como listar todas as tabelas na base de dados ou mostrar o esquema da tabela. As seguintes consultas information_schema de exemplo funcionam no MySQL e no PostgreSQL. Pode saber mais nas tabelas information_schema do MySQL e nas 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 ligação

A tabela seguinte mostra as propriedades da ligação do Cloud SQL:

Nome de propriedade Valor Descrição
name de string Nome do recurso de associação no formato: project_id.location_id.connection_id.
location de string Localização da ligação que tem de corresponder à localização 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. Apenas as consultas do BigQuery executadas nesta localização podem usar esta ligação.
friendlyName de string Um nome a apresentar intuitivo para a associação.
description de string Descrição da associação.
cloudSql.type de string Pode ser "POSTGRES" ou "MYSQL".
cloudSql.instanceId de string Nome da instância do Cloud SQL, normalmente no formato:

Project-id:location-id:instance-id

pode encontrar o ID da instância na página de detalhes da instância do Cloud SQL.
cloudSql.database de string A base de dados do Cloud SQL à qual quer estabelecer ligação.
cloudSql.serviceAccountId de string A conta de serviço configurada para aceder à base de dados do Cloud SQL.

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

Nome de propriedade Valor Descrição
username de string Nome de utilizador da base de dados
password de string Palavra-passe da base de dados

Acompanhe as consultas federadas do BigQuery

Quando 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 estiver a monitorizar registos de utilização de consultas numa base de dados MySQL ou PostgreSQL, a seguinte anotação pode ajudar a identificar consultas provenientes do BigQuery.

  1. Aceda à página Explorador de registos.

    Aceda ao Explorador de registos

  2. No separador Consulta, introduza a seguinte consulta:

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

  3. Clique em Executar consulta.

    Se existirem registos disponíveis para consultas federadas do BigQuery, é apresentada uma lista de registos semelhante à seguinte em 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
*/

Resolução de problemas

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

Problema: falha ao estabelecer ligação ao servidor da base de dados. Se estiver a consultar uma base de dados MySQL, pode encontrar 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.

Em alternativa, se estiver a consultar uma base de dados PostgreSQL, pode encontrar 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: certifique-se de que foram usadas credenciais válidas e que foram seguidos todos os pré-requisitos para criar a associação para o Cloud SQL. Verifique se a conta de serviço criada automaticamente quando é criada uma ligação ao Cloud SQL tem a função 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 obter instruções detalhadas, consulte o artigo Conceda acesso à conta de serviço.

O que se segue?