How to Integrate MySQL and Google BigQuery: A Comprehensive Guide

Connecting MySQL to Google BigQuery combines structured data with analytical and data processing tools. You offload analytical workloads from MySQL servers, improve their performance, set up real-time data analytics, and build machine learning models based on it. This article describes two methods of Mysql and Bigquery integration: manual and using a third-party service. Let’s start with MySQL to Google BigQuery capabilities and move to integration instructions later. If you are looking for the instructions only, feel free to scroll down a bit.

Table of Contents

1. MySQL Explained: An Overview and Its Key Benefits
2. Google BigQuery: Features and Benefits Explained
3. How to Connect & Sync MySQL and BigQuery: 2 Easy Ways
   • Manual MySQL and BigQuery Integration
   • MySQL and BigQuery Integration Using Skyvia
4. Conclusion

MySQL Explained: An Overview and Its Key Benefits

MySQL is a relational database management system that is currently owned by Oracle. It is designed for transactional and structured data and uses SQL for management.

MySQL Benefits

• High performance and usability
• Transaction support
• Easy data warehouse integration
• Highly scalable and flexible
• Multi-OS compatible

Google BigQuery: Features and Benefits Explained

BigQuery is a data warehouse owned by Google that is mostly used for applying complex analytical queries. It shines when it comes to large-scale data processing and working with semi-structured data. Moreover, it offers integration with other Google services such as Spreadsheets, Google Drive, and more.

BigQuery Benefits

• Serverless nature allows you to save expenses on scaling and managing resources.
• Reasonable pricing
• Real-time on-demand data analysis that makes sure you work with fresh data
• Streaming support
• Secure environment
• Semi-structured data support
• Machine learning support

How to Connect & Sync MySQL and BigQuery: 2 Easy Ways

There are several ways to integrate MySQL and BigQuery. Let’s check two popular ones: — manually dumping MySQL to Google Cloud Platform and using a third-party tool such as Skyvia. Before you start the integration, take a closer look at each method and decide which one fits you better.

Manual MySQL and BigQuery Integration

To integrate MySQL and BigQuery manually, you need to:

1. Set up the environment
2. Create a MySQL dump
3. Upload data to the cloud
4. Create a BigQuery dataset
5. Import data to BigQuery

Setting up the Google Cloud Platform Environment

Create a Google Cloud account and set up the GCP projects to enable the BigQuery service.

Installing the Google Cloud SDK

Download and install the Google Cloud SDK on your local machine to interact with GCP services from the command line.

Exporting MySQL data

Export your MySQL data by using the mysqldump command. For example, to export a table called mytable:

mysqldump -u [username] -p[password] -h [hostname] [database] mytable > mytable.sql 

Uploading Data to Google Cloud Storage

Upload the data export file to Google Cloud Storage to your bucket by using the following command(enter the proper table and bucket names):

gsutil cp mytable.sql gs://your-bucket-name/ 

Creating a BigQuery Dataset

In the BigQuery Console, create a dataset to import your MySQL data to. Do it with the help of GCP UI or by using the following command:

bq mk mydataset 

Importing Data into BigQuery

Use the bq command-line tool to load the data from the GCS file into BigQuery. You will specify the source format, schema, and destination table.

bq load --source_format=CSV mydataset.mytable gs://your-bucket-name/mytable.sql schema.json

You need to provide the schema information in the schema.json file, describing the structure of your MySQL data.

Import and export are resource-intensive and can take a lot of time. Plan carefully before proceeding to import large data sets.

MySQL and BigQuery Integration Using Skyvia

Now that you know how to integrate MySQL and BigQuery manually, let’s move to the second method of integration between MySQL and BigQuery using Skyvia.

To integrate MySQL and BigQuery using Skyvia, you will need a Skyvia account that you can create for free, a MySQL and BigQuery connection, and an integration of your choice.

Creating a Connection to MySQL

To create a connection to MySQL:

1. Click New and choose Connection.
2. Select MySQL.

3. Enter MySQL credentials: server, port, login and password, and a database name.

Note: Agent connections don’t have such requirements, but you need to install the Skyvia Agent application in order to use them.

4. Click Create.

Creating a Connection to BigQuery

To create a connection to BigQuery:

1. Click New and choose Connection.
2. Select BigQuery

3. Enter BigQuery Credentials: Acces token, project Id, data set Id, and bucket name.

4. Click Create.

Importing MySQL data to BigQuery

Once you created a connection with MySQL and BigQuery, you can start importing MySQL data to BigQuery. To run the import, create an Import integration:

1. Click +New, and choose Import.
2. Set the preferred package name at the top of the page.

3. Select MySQL as the Source and BigCommerce as the Target.
4. Add a Task to the integration by clicking Add New. Import task is specific to a table. You can add any number of Import tasks.
5. Select the table and click Next Step.
6. Select the Import operation. You can choose between Insert, Update, Upsert, and Delete. Click Next Step.
7. Map the required fields and save your progress. (You should map the target columns to the source columns. For synchronization, configure mapping in both directions — source columns to target columns and target columns to source columns.)
8. Click Create to create an Import integration.
9. Click Run to execute.

You can set up a schedule for your integration to run automatically based on it. Moreover, Skyvia offers a lot more than just an import. It provides a package of different tools to work with your data and is compatible with more than 170 data sources. Whether you want to perform a simple import, replicate your data, make a backup or automate your workflow — Skyvia got you covered.

Conclusion

The integration of BigQuery and MySQL brings a list of clear benefits when it comes to data analysis. There are several ways to perform the integration, and everyone chooses the most suitable way. If you are more of a coding person and a command line is your way to go – proceed with a manual process, you will most likely find it more convenient. If you just want a clear interface that allows you to manage your data easily – go with Skyvia, especially as it has much more to offer in addition to Importing data from MySQL to BigQuery.