Cruzando dados entre Cloud SQL e BigQuery com as Queries Federadas

No Google Cloud Platform possuem várias formas de armazenar dados, mas pode ter situações em que é preciso pegar dados de uma base relacional e cruzar com o que está salvo no BQ para suas análises nos dados. Uma forma de fazer é o uso de Queries Federadas, em que é feito uma consulta em um banco de dados no Cloud SQL e um dataset no BigQuery, sem a necessidade de copiar ou mover dados utilizando algum armazenamento intermediário como o Goocle Cloud Storage no processo, por exemplo. Esse procedimento é suportado em instâncias que utilizam MySQL ou PostgreSQL.

Mas antes vamos precisar seguir algumas configurações necessárias para fazer esse procedimento como:

• Habilitar o uso do BigQuery
• Habilitar o IP público da sua instância Cloud SQL
• Criar uma conexão externa do Cloud SQL no BigQuery
• Garantir que o seu usuário ou service account tenha as roles adequadas

Exemplos de uso

Com as queries federadas você utilizar para dois tipos de necessidades que vamos exemplificar: cruzar dados de bases diferentes e efetuar ingestão de dados.

Exemplo 1: Cruzando dados de bases diferentes

Imagine que você tem dois serviços independentes, focados em dados de clientes e vendas respectivamente. Cada um deles armazena em uma base diferente. Precisamos fazer uma busca em que queremos saber qual foi a data da primeira compra feita por cada cliente.

Essa query pode ser feita no BigQuery e utiliza a função EXTERNAL_QUERY. A consulta abaixo mostra como isso é feito:

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

Nesse trecho temos o seguinte:

• O primeiro argumento da função é o ID da conexão externa que foi criada no BQ, e isso é importante para ter acesso a base
• O segundo argumento é a query SQL que será executada no banco externo, respeitando toda a sintaxe do fornecedor (como PostgreSQL por exemplo). Os dados retornados serão incluídos em uma tabela temporária.
• Com base na tabela temporária é feito um JOIN, utilizando o campo customer_id
• E por fim a consulta principal faz a seleção dos dados e os agrupa.

Dessa forma você consegue extrair informações que precisa, sem ter que ficar copiando um grande volume.

Exemplo 2: Efetuando ingestão de dados

Seguindo o mesmo cenário do primeiro exemplo, vamos supor que agora queremos fazer a ingestão de todos os dados da tabela orders, como fazer nesse caso? A query abaixo mostra como fazer:

INSERT INTO `foobar-project.ecommerce.orders`
SELECT orders.* FROM EXTERNAL_QUERY(
  "us.orders_db",
  '''SELECT * FROM orders;'''

) orders;

Aqui é mais simples de entender, mas basicamente o resultado da query feita na tabela orders da base externa, vai para a tabela temporária e com isso as informações vão servir como insumo para a ingestão dos dados na tabela orders do dataset ecommerce no BQ.

Aqui temos algumas considerações a colocar no uso desse cenário:

• Ao utilizar o asterisco (*) na consulta, o nome das colunas da tabela BQ precisam ser idênticas a tabela externa. Caso contrário defina os nomes de forma explícita (nas duas entidades de preferência)
• A ingestão é feita se os dados externos respeitarem os tipos de dados que as colunas da tabela BQ foram declaradas. Na documentação existe uma tabela de mapeamento dos dados, que explica melhor.

Em ambos os casos, o uso das Queries Federadas podem ser de grande utilidade para quem lida com dados no dia a dia.