Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to integrate Google Cloud SQL with Google Big Query

I am designing a solution in which Google Cloud SQL will be used to store all data from the regular functioning of the app(kind of OLTP data). The data is expected to grow over time into pretty large size. The data itself is relational in nature and hence we have chosen Cloud SQL instead of Cloud Datastore.

This data needs to be fed into Big Query for analytics and this needs to be near real-time analytics (as the best case), although realistically some lag can be expected. But I am trying to design a solution which reduces this lag to minimum possible.

My question has 3 parts -

  1. Should I use Cloud SQL for storing data and then move it to BigQuery or change the basic design itself and use BigQuery for storing the data initially as well? Is BigQuery suitable for use for regular, low-latency OLTP workloads?(I don't think so - is my assumption correct?)

  2. What is the recommended/best practice for loading Cloud SQL data into BigQuery and have this integration work near real-time?

  3. Is Cloud Dataflow a good option? If I connect Cloud SQL to Cloud DataFlow and further to BigQuery - will it work? Or is there any other way to achieve this which is better(as asked in question 2)?

like image 492
Dhruv Rai Puri Avatar asked Sep 22 '17 17:09

Dhruv Rai Puri


People also ask

Can Cloud SQL Connect to BigQuery?

BigQuery Cloud SQL federation enables BigQuery to query data residing in Cloud SQL in real time, without copying or moving data. Query federation supports both MySQL (2nd generation) and PostgreSQL instances in Cloud SQL. After the initial one-time set up, you can write a query with the SQL function EXTERNAL_QUERY .

How do I connect Google Cloud to SQL?

In the Google Cloud console, go to the Cloud SQL Instances page. To open the Overview page of an instance, click the instance name. Select Connections from the SQL navigation menu. In the Authorized networks section, click Add network and enter the IP address of the machine where the client is installed.


2 Answers

Take a look at how WePay does this:

  • https://wecode.wepay.com/posts/bigquery-wepay

The MySQL to GCS operator executes a SELECT query against a MySQL table. The SELECT pulls all data greater than (or equal to) the last high watermark. The high watermark is either the primary key of the table (if the table is append-only), or a modification timestamp column (if the table receives updates). Again, the SELECT statement also goes back a bit in time (or rows) to catch potentially dropped rows from the last query (due to the issues mentioned above).

With Airflow they manage to keep BigQuery synchronized to their MySQL database every 15 minutes.

like image 164
Felipe Hoffa Avatar answered Oct 19 '22 01:10

Felipe Hoffa


BigQuery supports Cloud SQL federated queries which lets you directly query Cloud SQL database from BigQuery. To keep Cloud SQL table in sync with BigQuery, you can write a simple script with following query to sync two tables every hour.

INSERT
   demo.customers (column1)
SELECT
   *
FROM
   EXTERNAL_QUERY(
      "project.us.connection",
      "SELECT column1 FROM mysql_table WHERE timestamp > ${timestamp};");

Just remember replace the ${timestamp} with the current timestamp - 1 hour.

like image 27
Jian He Avatar answered Oct 19 '22 01:10

Jian He