Streaming Cloud SQL Data into BigQuery in near real-time

Lab Details:

  1. In this Lab, we will be Connecting Cloud SQL with BigQuery for Data analysis.

  2. Region: us-central1.

  3. Duration: 1 hour.

Note: Do not refresh the page after you click Start Lab, wait for a few seconds to get the credentials.
In case while login into Google, if it asks for verification, please enter your mobile number and verify with OTP, Don't worry this Google Account will be deleted after the lab.

What is Google Cloud SQL?

It is a fully managed Relational Database service offered by Google for your MySQL, PostgreSQL, and SQL Server workloads. For New Users create your preferred instance(instance creation is done in a few minutes) and you are good to go, no overhead/operational time spent for provisioning the infrastructure, maintaining the SLAs(Uptime), or the Storage issues, Google takes care of everything.  For old and heavy workloads running on-premise, you can use  Database Migration Service (DMS) to migrate your production databases to Cloud SQL with very minimal downtime. You can refer to the Introduction to Google Cloud SQL Lab to explore more about Google Cloud SQL.

Advantages offered by Cloud SQL:

  1. High Disaster Recovery and Failover with HA(High Availability feature).

  2. Tight Integration with other Google Cloud Services such as GKE and App Engine.

  3. Very Easy To set up.

  4. Data stored in Cloud SQL is automatically Encrypted.

Disadvantages offered by Cloud SQL:

  1. If you are using a specific IP on-prem and have high dependencies on that IP or have hard-coded the IP, Google Cloud doesn't provide an option to select an IP for your SQL instance.

  2. The cost of using Cloud SQL will release as your Operations will increase ultimately leading to Migrating off the Cloud

What are Federated Queries? 

A federated query is a way to send a query statement to an external database. It returns the result in a temporary table. In a standard SQL query, we use the EXTERNAL_QUERY function to send a query statement to the external database. The EXTERNAL_QUERY function is usually used in a FROM clause.

Federated Queries are Supported by:

  1. Cloud SQL

  2. Cloud Spanner

What is BigQuery?

The top driving factor for customers to Migrate to Google Cloud Platform is Google's High-end, fully managed Data Analytics services. One of the advanced services offered by Google is BigQuery which is a Serverless, Highly Scalable Data warehouse service. With a Huge Volume of Data Generation through various sensors, Applications there is a need for a low-cost Data Warehouse to store the data and analyze it when needed. With BigQuery you can analyze Petabytes of data within minutes. BigQuery stores data in a columnar format, achieving a high compression ratio and scan throughput. BigQuery is fully efficient when used with not changing Dataset for example it can be used at the end of an ETL pipeline to enrich/analyze the data. For Daily OLTP workload or sensor data, you should opt for other services such as Cloud SQL, Cloud Spanner, and Cloud BigTable.You are charged for the data you store and the bytes read to give you the Query output. You can refer to the Introduction to BigQuery Lab to explore more about Google BigQuery.

Advantages of using BigQuery:

  1. 0 Overhead Cost of running the operations on BigQuery.

  2. Very Less TCO(Total Cost of Ownership).

  3. Integrated with BigQuery ML Create ML Models.

  4. Supported by Data Studio to visualize the analyzed data.

  5. Addition with BigQuery Omini which is a Multi-Cloud Data Analysis service.

Disadvantages of using BigQuery:

  1. As it's a fully managed service, you have no control over where and how your data is stored.

Advantages of Streaming Data to BigQuery:

  1. Use the Most Powerful Analytics Platform i.e. BigQuery.

  2. Highly Available and Geo-redundant.

  3. Use SQL to Analyse Data.

Lab Tasks:

  1. Create a Cloud SQL Instance and a Database.

  2. Enter the data into Database Manually.

  3. Create a Connection between BigQuery and Cloud SQL.

  4. Create Scheduled Queries & Run Simple Queries in BigQuery.                                                                                                

Join Whizlabs_Hands-On to Read the Rest of this Lab..and More!

Step 1 : Login to My-Account
Step 2 : Click on "Access Now" to view the course you have purchased
Step 3 : You will be taken to our Learn Management Solution (LMS) to access your Labs,Quiz and Video courses

Open Console