This lab was developed with our partner, Striim. Your personal information may be shared with Striim, the lab sponsor, if you have opted-in to receive product updates, announcements, and offers in your Account Profile.

GSP828

Overview

This lab demonstrates how to migrate a Cloud SQL for MySQL database to BigQuery using Google Cloud's data migration partner, Striim.

Striim is a comprehensive streaming ETL platform that enables online data migrations and continuous streaming replication from on-premises and cloud data sources to Google Cloud data services through a graphical drag and drop interface. The following figure shows Striim's high level architecture.

This lab will focus on the implementation of a continuous migration from MySQL to BigQuery, and is not an explanation of database migration or database replication, or why you might want to migrate your underlying database. If you'd like to learn more about database migrations and replications, refer to Simplify your database migration journey.

The figure shows the various services that you will create as well as the data flow from Cloud SQL for MySQL to BigQuery via Striim that you will implement in this lab.

What you'll' learn

In this lab you will learn to:

• Create and use a Cloud SQL for MySQL database
• Deploy Striim through the Google Cloud Marketplace
• Use Striim to read from a source Cloud SQL for MySQL database
• Create and use a BigQuery data set
• Use Striim to continuously replicate from Cloud SQL for MySQL to BigQuery.

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources are made available to you.

This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).

• Time to complete the lab—remember, once you start, you cannot pause a lab.

How to start your lab and sign in to the Google Cloud console

1. Click the Start Lab button. If you need to pay for the lab, a dialog opens for you to select your payment method. On the left is the Lab Details pane with the following:

   • The Open Google Cloud console button
   • Time remaining
   • The temporary credentials that you must use for this lab
   • Other information, if needed, to step through this lab

2. Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).

   The lab spins up resources, and then opens another tab that shows the Sign in page.

   Tip: Arrange the tabs in separate windows, side-by-side.

   Note: If you see the Choose an account dialog, click Use Another Account.

3. If necessary, copy the Username below and paste it into the Sign in dialog.

   {{{user_0.username | "Username"}}}

   You can also find the Username in the Lab Details pane.

4. Click Next.

5. Copy the Password below and paste it into the Welcome dialog.

   {{{user_0.password | "Password"}}}

   You can also find the Password in the Lab Details pane.

6. Click Next.

   Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.

7. Click through the subsequent pages:

   • Accept the terms and conditions.
   • Do not add recovery options or two-factor authentication (because this is a temporary account).
   • Do not sign up for free trials.

After a few moments, the Google Cloud console opens in this tab.

Note: To access Google Cloud products and services, click the Navigation menu or type the service or product name in the Search field.

Activate Cloud Shell

Cloud Shell is a virtual machine that is loaded with development tools. It offers a persistent 5GB home directory and runs on the Google Cloud. Cloud Shell provides command-line access to your Google Cloud resources.

1. Click Activate Cloud Shell at the top of the Google Cloud console.

2. Click through the following windows:

   • Continue through the Cloud Shell information window.
   • Authorize Cloud Shell to use your credentials to make Google Cloud API calls.

When you are connected, you are already authenticated, and the project is set to your Project_ID, . The output contains a line that declares the Project_ID for this session:

gcloud is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.

3. (Optional) You can list the active account name with this command:

4. Click Authorize.

Output:

5. (Optional) You can list the project ID with this command:

Output:

Before you begin

In order to work on the database migration, some initial configuration is required:

• In Cloud Shell, set the default compute zone for your database and compute resources. For more information, please see Geography and Regions. This lab uses , but change here if you want to use a different zone:

Task 1. ​​Create a Cloud SQL for MySQL instance

Next, create a Cloud SQL for MySQL instance that you will use to connect Striim to. In this case, it will act as the source transactional system to be replicated. In a real world scenario this could be one of many transactional database systems.

1. In Cloud Shell, create environment variables to create the instance:

2. If you close your window or the Cloud Shell session, you will lose the environment variables with the passwords, so use the echo command to show them and make a note of them:

3. Run the following to create the Cloud SQL instance in Cloud Shell. This command might take a few minutes to complete:

4. Create a Cloud SQL user that Striim will connect to:

You have completed setting up the Cloud SQL for MySQL database for Striim to ingest data from.

Click Check my progress to verify the objective. Create a Cloud SQL for MySQL instance

Task 2. ​Setting up the Striim software

Next, set up an instance of the Striim server through the Google Cloud Marketplace.

1. Navigate to the Striim offering on the Google Cloud Marketplace. If a list of Google Cloud projects is displayed then select the Qwiklabs project.

OPEN STRIIM IN THE MARKETPLACE banner

Alternatively you can search in the Marketplace, the direct link is: https://console.cloud.google.com/marketplace/details/striim/striim.

2. Click the button marked Launch. You will be presented with configuration options for the Striim server.

• Change the zone to (or to the zone that you launched Cloud SQL in, if different than <ql-variable key="project_0.default_zone" placeHolder="ZONE"></ql-variable>).
• Change the machine type to E2
• Check the box to accept the terms of service,
• Choose whether to authorize information sharing with Striim,
• Leave all other settings as is, and click the Deploy button.

Click Check my progress to verify the objective. Setting up the Striim software

3. On the Deployment Manager page, once the Striim server is provisioned, the deployment details will show. Take a note of the name of the VM ("Instance" in the example screenshot below) and the zone it is deployed in ("Instance zone").

In order to allow Striim to communicate with Cloud SQL, you need to add the Striim server's IP address to allow it to connect.

4. In Cloud Shell, execute the following commands replacing [STRIIM VM NAME] and[STRIIM VM ZONE] that you just noted previously in step 3, acknowledge that you want to continue by pressing "Y" when prompted.:

5. Click the Visit the site button on the right half of the screen. This will open up the Striim configuration wizard in a new browser tab. Click I Agree and accept the EULA by clicking Accept Striim EULA and Continue.

6. Configure the server and be sure to note the cluster name, the cluster and administrator passwords, and the sys user and keystore passwords. Click the button Save and Continue.

7. The next screen will ask for license details. Leave the field empty and click the button Save and Continue.

8. Click the green Launch button and then the green Log In button. Log in using the admin user and the administrator password you set in the previous step. This will take you to the Striim administrator console, and you are now ready to connect to the MySQL database.

Task 3. Setting up Connector/J

You will now follow the Striim online documentation and use the MySQL Connector/J to connect Striim to your Cloud SQL instance.

1. In the Deployment Manager page for your Striim instance, click the SSH button located next to the blue Visit the site button. This will open up a new window and will automatically SSH into the virtual machine.

2. Run the following commands to create the user directory and give the user permissions to access it:

3. Close the SSH window and click the SSH button again to reconnect to the VM.

4. Download Connector/J to the VM and unpack it:

5. Copy the file to the Striim library path, allow it to be executable, and change ownership of it:

Click Check my progress to verify the objective. Setting up Connector/J

6. You now need to restart the Striim server to recognize the new library. Do this in the same SSH session by running the following:

7. Go back to the browser window for the Striim administration console, refresh the window, and log in using the admin user and password. The administration console will take a few minutes to refresh after the restart. If the administration console does not reload on the first try, keep refreshing until the administration console comes up.

If the administration console does not come up, use the site address, but with port 9080 instead of 9070, i.e. http://[IP_ADDRESS]:9080/

Task 4. ​​Loading sample transactions to Cloud SQL

Before you build your first Striim application, you need to load some existing transactions into the MySQL instance.

1. In Cloud Shell, connect to the instance using the CSQL_USER_PWD set from before:

2. Create a sample database and load some transactions into it at the mysql> prompt:

3. Do a quick check by counting the records and ensuring that 10 records have been inserted. Also note the average and total size of the orders; they should be 43148.95649414063 and 431489.56494140625, respectively:

Results in:

4. Exit MySQL with exit command and get back to the Cloud Shell command line:

5. Log back into the SQL instance as the root user using the CSQL_ROOT_PWD set from before:

6. Run the following commands to give Striim MySQL privileges:

7. Exit MySQL with exit command and get back to the Cloud Shell command line:

Task 5. Creating a BigQuery data set

You will now create a BigQuery dataset and load the Service Account credentials from the Cloud Console so that Striim can write to the BigQuery table.

For this lab, deploy BigQuery in the same region as Cloud SQL. If you chose a different region than for Cloud SQL, change the region for BigQuery accordingly. More information about BigQuery regions is available.

1. Create a BigQuery dataset named striimdemo with the following command:

The dataset is set to expire in 7200 seconds (2 hours) for the purposes of this lab.

2. Create a target table named striimdemo.orders in the new BigQuery dataset:

The table is also set to expire in 7200 seconds (2 hours) for the purposes of this lab.

3. Create a Service Account for Striim to use for connecting to BigQuery, then add policy binding and export some environment variables:

This will create a Service Account Key named striim-bq-key.json in your home path.

4. From the Cloud Shell toolbar, Click on the three dots (⋮) next to the file in the file browser. Then click on Downloads then click on Toggle file browser icon and expand the user name and select the striim-bq-key.json file and click on DOWNLOAD to save the file on your local machine to use further in the lab.

5. Now you need to move the key you just created to the Striim VM. Identify the name of the virtual machine in which Striim is deployed on by either going to the deployment details page in the Deployment Manager, or by using gcloud compute instances list command in Cloud Shell. Once you obtain the name of the Striim VM, continue with the instructions below.

6. Copy the JSON file to the Striim server with the scp command. This operation might cause a new SSH key to be generated and in this case you will be asked to provide a passphrase:

7. Now, move the JSON file on the Striim VM to the /opt/striim directory using the following command:

You are now ready to create a Striim application!

Click Check my progress to verify the objective. Creating a BigQuery data set

Task 6. Creating an online database migration

An online database migration moves data from a source database (either on-premises or hosted on a cloud provider) to a target database in Google Cloud. The source database remains fully accessible to the business applications throughout the migration with minimal performance impact.

In an online migration, an initial bulk load is performed, and a continuous capture of changes is also being run. You then synchronize the source and target databases to ensure that no data is lost during the migration. Typically both databases will be retained for extended periods of time to test and verify that the applications and users are not impacted negatively by switching to a new cloud database.

If you'd like to focus on creating a Change Data Capture (CDC) pipeline, please jump ahead to section 9. Creating a continuous Cloud SQL for MySQL to BigQuery data pipeline. Here, you will start by creating an initial bulk load from your source to target database.

1. Get back to the browser tab with the Striim Web UI, and click on the Apps pane in the middle.

2. Click on the Create App button on the upper right side of the page.

3. There are multiple ways to create applications in Striim. Click on Start From Scratch.

4. Name the application something that is easily remembered. The lab will use MySQLToBigQuery_initLoad. Below the application name, you can choose a namespace. These are logical groupings that help you organize applications. Use the default admin namespace. Click Save.

5. You'll be greeted by the Flow Designer page. All of the out-of-the-box connectors that you will need to create streaming data applications are in the left menu bar.

To perform a one time bulk initial load of data from MySQL database table, you'll use the Database Reader.

6. Drag and drop the Database tile from the Sources pane to the application canvas in the middle. Enter all of your connection properties on the right New Source.

• Use mysql_source as the name.
• The IP address can be found through Cloud Shell with the command gcloud sql instances list in the PRIMARY_ADDRESS field. Use this to construct your Connection URL. It should look like jdbc:mysql://[PRIMARY_ADDRESS]:3306/striimdemo.
• Use the username that you set the CSQL_USERNAME environment variable as (this lab uses striim-user)
• Use the CSQL_USER_PWD value for the password.

7. Click the Show Advanced Settings link to see more configuration properties.

• Enter a table name into the field Tables that you'd like to migrate with the syntax DatabaseName.SchemaName. Per your earlier MySQL setup, this should be striimdemo.ORDERS.
• Under the OUTPUT TO section, name the new stream in the New Output box as stream_CloudSQLMySQLInitLoad.

8. Make a note of the settings in this and the previous step as you will need them again in the next section. Click Save.

Now you will do a quick test to see if the configuration settings are correct, and that Striim can successfully connect to MySQL.

9. In the middle of the page, there will be a button marked Created with a drop-down arrow next to it. Click the button, and select Deploy App.

In the Deployment window, this is where you can specify that you want to run certain pieces of your application on certain parts of your deployment topology. You'll typically use this if you're running a lightweight forwarding Striim agent deployed on premises next to your data source, pushing data over specific ports to the Striim server running in the cloud. Since we're using a simple deployment topology, go ahead and select the default option and click Deploy.

10. Click the wave icon beneath your Database Reader component, and then click the eye icon next to it to preview your data as it flows through the Striim data pipeline.

11. Go back up to the top menu bar, click Deployed, then click Start App. You should now see the Striim application running, and data flowing through the pipeline. If there are any errors, it means that there is an issue connecting to the source database since there is only a source component in the pipeline. If you see your application successfully run but no data flows through, typically that means that you don't have any existing data in your database.

12. So far you've successfully connected to your source database and tested that it can read data. Click where it says Running on the top menu bar, and then select Stop App. Then click again where it now says Stopped, and then select Undeploy App. We are now ready to connect to BigQuery.

13. Click the wave button under the DatabaseReader. This time, a + button will appear. Click it, and then select Connect next Target component. Name this target object BigQueryInitialLoadTarget. Then search for "BigQuery" under the Adapter field in the left panel. Select BigQueryWriter.

14. The tables property is a list of source/target pairs separated by semi-columns. Each pair denotes the full names of the source and target tables separated by commas. The list will look like srcSchema1.srcTable1,tgtSchema1.tgtTable1; srcSchema2.srcTable2,tgtSchema2.tgtTable2. For this lab, use only one pair striimdemo.ORDERS,striimdemo.orders.

15. Enter the PROJECT_ID into the Project Id input field. The project name (PROJECT_ID) can easily be found within the Cloud Shell.

16. In SERVICE ACCOUNT KEY click on Choose option then under the Or Upload a file section click Select File and select the file that you downloaded in Task5 and then Click Upload and Select to browse and upload the file from local.

17. Click Save. Repeat the directions in steps 8 - 10 to deploy and run the app.

18. In the Cloud Console, navigate to BigQuery, and then to your striimdemo dataset. Since you're working with BigQuery, you'll run an analytical query to verify that your data arrived properly, such as to find the average and total size of the orders. They should be 43148.952 and 431489.52 respectively:

Output:

You have successfully set up your Striim environment and pipeline to perform a batch load!

Click Check my progress to verify the objective. Creating an online database migration

Task 7. Creating a continuous Cloud SQL for MySQL to BigQuery data pipeline

With the initial one time bulk load in place, you can now set up a continuous replication pipeline. It will look very similar to the bulk pipeline that you just created, but with a different source object.

1. Click the Striim logo in the upper-left corner of the screen to bring up the main menu. Click on Home to bring you back to the main page, click Apps, and then click the Create App button in the upper-right corner to create a new app.

2. Click Start from Scratch, and then name the app MySQLToBigQuery_cdc. Create it in the same admin namespace as before. This time, instead of choosing a DatabaseReader source, choose a MySQL CDC reader.

3. Give your new source a name. Copy in the connection URL, username, password, and table information that you used in steps 6 and 7 from the previous section. Create a new target. We will name it tgt_MySQLCDCBigQuery. Repeat steps 12, 13, and 14 from the previous section to add the BigQuery target to the data pipeline. Deploy and start the app, click the wave icon, and then the eye icon next to it to view the data as it is replicated. Since only the changes in the source database table from the time of the creation of the app are replicated to the target in real-time, you initially won't see any data flowing through the pipeline.

4. Go back to the Cloud Shell and connect to your MySQL instance. In the striimdemo database, execute the following commands:

5. Go back to the Striim console, and you should start to see the transactions flow through!

6. In the Cloud Console, navigate to BigQuery, and then to your striimdemo dataset. We'll run one final query to verify that your data arrived properly, such as SELECT COUNT(*) AS ORDERS, AVG(ORDER_TOTAL) AS ORDERS_AVE, SUM(ORDER_TOTAL) AS ORDERS_SUM FROM striimdemo.orders; to find the average order size as well as the total order sum. It should be 41959.899333333335 and 629398.49 after the CDC pipeline, respectively.

Click Check my progress to verify the objective. Creating a continuous Cloud SQL for MySQL to BigQuery data pipeline

Congratulations!

In this lab, you successfully set up a streaming pipeline from Cloud SQL for MySQL to BigQuery.

Next steps/ Learn more

• Striim on the Google Cloud Marketplace!
• Try out other Google Cloud features for yourself. Have a look at our tutorials, and also be sure to take a look at the Google Cloud Data Migration content.
• If you'd like to learn more about Striim, please visit the website, schedule a demo with a Striim technologist, and subscribe to the Striim blog.
• Resources related to the lab are https://www.striim.com/blog/2018/09/oracle-to-google-bigquery/ and https://www.youtube.com/watch?v=DLurrZnBU0s

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Last Tested Date: February 22, 2024

Last Updated Date: February 22, 2024

Copyright 2025 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.