{"id":3164,"date":"2020-06-30T20:49:35","date_gmt":"2020-06-30T20:49:35","guid":{"rendered":"https:\/\/contentlab.com\/tutorial-migrating-from-mysql-to-bigquery-for-real-time-data-analytics\/"},"modified":"2022-01-31T21:06:29","modified_gmt":"2022-01-31T21:06:29","slug":"tutorial-migrating-from-mysql-to-bigquery-for-real-time-data-analytics","status":"publish","type":"post","link":"https:\/\/contentlab.com\/tutorial-migrating-from-mysql-to-bigquery-for-real-time-data-analytics\/","title":{"rendered":"Tutorial: Migrating from MySQL to BigQuery for Real-Time Data Analytics"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-post\" data-elementor-id=\"3164\" class=\"elementor elementor-3164\" data-elementor-post-type=\"post\">\n\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-69f54f30 elementor-section-boxed elementor-section-height-default elementor-section-height-default qodef-elementor-content-no\" data-id=\"69f54f30\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-4144429a\" data-id=\"4144429a\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-a4b4164 elementor-widget elementor-widget-text-editor\" data-id=\"a4b4164\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<style>\/*! elementor - v3.21.0 - 25-04-2024 *\/\n.elementor-widget-text-editor.elementor-drop-cap-view-stacked .elementor-drop-cap{background-color:#69727d;color:#fff}.elementor-widget-text-editor.elementor-drop-cap-view-framed .elementor-drop-cap{color:#69727d;border:3px solid;background-color:transparent}.elementor-widget-text-editor:not(.elementor-drop-cap-view-default) .elementor-drop-cap{margin-top:8px}.elementor-widget-text-editor:not(.elementor-drop-cap-view-default) .elementor-drop-cap-letter{width:1em;height:1em}.elementor-widget-text-editor .elementor-drop-cap{float:left;text-align:center;line-height:1;font-size:50px}.elementor-widget-text-editor .elementor-drop-cap-letter{display:inline-block}<\/style>\t\t\t\t<p>In this post, we will walk through an example of how to replicate and synchronize your data from on-premises MySQL to BigQuery using change data capture (CDC).<\/p><p>Data warehouses have traditionally been on-premises services that required data to be transferred using batch load methods. Ingesting, storing, and manipulating data with cloud data services like Google BigQuery makes the whole process easier and more cost effective, provided that you can get your data in efficiently.<\/p><p>Striim real-time data integration platform allows you to move data in real-time as changes are being recorded using a technology called\u00a0<a href=\"https:\/\/www.striim.com\/change-data-capture\/\">change data capture<\/a>. This allows you to build real-time analytics and machine learning capabilities from your on-premises datasets with minimal impact.<\/p><h2>Source MySQL Database<\/h2><p>Before you set up the Striim platform to synchronize your data from\u00a0<a href=\"https:\/\/contentlab.com\/monitoring-mysql-database-performance-with-new-relic\/\">MySQL<\/a>\u00a0to BigQuery, let\u2019s take a look at the source database and prepare the corresponding database structure in BigQuery. For this example, I am using a local MySQL database with a simple purchases table to simulate a financial datastore that we want to ingest from MySQL to BigQuery for analytics and reporting.<\/p><p><img fetchpriority=\"high\" decoding=\"async\" class=\"size-full wp-image-26761 alignnone\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2021\/11\/MySQLtoGoogleBigQuery_graphic1.png\" alt=\"\" width=\"718\" height=\"287\" \/><\/p><p>I\u2019ve loaded a number of initial records into this table and have a script to apply additional records once Striim has been configured to show how it picks up the changes automatically in real time.<\/p><h2>Targeting Google BigQuery<\/h2><p>You also need to make sure your instance of BigQuery has been set up to mirror the source or the on-premises data structure. There are a few ways to do this, but because you are using a small table structure, you are going to set this up using the Google Cloud Console interface. Open the Google Cloud Console, and select a project, or create a new one. You can now select BigQuery from the available cloud services. Create a new dataset to hold the incoming data from the MySQL database.<\/p><p><img decoding=\"async\" class=\"size-full wp-image-26762 alignnone\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2021\/11\/MySQLtoGoogleBigQuery_graphic2-1.png\" alt=\"\" width=\"504\" height=\"391\" \/><\/p><p>Once the dataset has been created, you also need to create a table structure. Striim can perform the transformations while the data flies through the synchronization process. However, to make things a little easier here, I have replicated the same structure as the on-premises data source.<\/p><p><img decoding=\"async\" class=\"alignnone size-full wp-image-4359\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic3-1.png\" alt=\"\" width=\"690\" height=\"741\" srcset=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic3-1.png 690w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic3-1-279x300.png 279w\" sizes=\"(max-width: 690px) 100vw, 690px\" \/><\/p><p>You will also need a service account to allow your Striim application to access BigQuery. Open the service account option through the IAM window in the Google Cloud Console and create a new service account. Give the necessary permissions for the service account by assigning BigQuery Owner and Admin roles and download the service account key to a JSON file.<\/p><p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4360\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic4-1.png\" alt=\"\" width=\"580\" height=\"484\" srcset=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic4-1.png 580w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic4-1-300x250.png 300w\" sizes=\"(max-width: 580px) 100vw, 580px\" \/><\/p><h2>Set Up the Striim Application<\/h2><p>Now you have your data in a table in the on-premises MySQL database and have a corresponding empty table with the same fields in BigQuery. Let\u2019s now set up a Striim application on Google Cloud Platform for the migration service.<\/p><p>Open your Google Cloud Console and open or start a new project. Go to the marketplace and search for Striim. A number of options should return, but the option you are after is the first item that allows integration of real-time data to Google Cloud services.<\/p><p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4361\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic5-1.png\" alt=\"\" width=\"875\" height=\"360\" srcset=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic5-1.png 875w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic5-1-300x123.png 300w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic5-1-768x316.png 768w\" sizes=\"(max-width: 875px) 100vw, 875px\" \/><\/p><p>Select this option and start the deployment process. For this tutorial, you are just using the defaults for the Striim server. In production, you would need to size appropriately depending on your load.<\/p><p>Click the deploy button at the bottom of this screen and start the deployment process.<\/p><p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4362\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic7.png\" alt=\"\" width=\"952\" height=\"375\" srcset=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic7.png 952w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic7-300x118.png 300w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic7-768x303.png 768w\" sizes=\"(max-width: 952px) 100vw, 952px\" \/><\/p><p>Once this deployment has finished, the details of the server and the Striim application will be generated.<\/p><p>Before you open the admin site, you will need to add a few files to the Striim Virtual Machine. Open the SSH console to the machine and copy the JSON file with the service account key to a location Striim can access. I used \/opt\/striim\/conf\/servicekey.json.<\/p><p>You also need to restart the Striim services for these setting and changes to take effect. The easiest way to do this is to restart the VM.<\/p><p>Give these files the right permissions by running the following commands:<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-617683d elementor-widget elementor-widget-code-highlight\" data-id=\"617683d\" data-element_type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<div class=\"prismjs-default copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-javascript line-numbers\">\n\t\t\t\t<code readonly=\"true\" class=\"language-javascript\">\n\t\t\t\t\t<xmp>chown striim:striim <filename>\r\n\r\nchmod 770 <filename><\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-f51e905 elementor-widget elementor-widget-text-editor\" data-id=\"f51e905\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>You also need to restart the Striim services for this to take effect. The easiest way to do this is to restart the VM.<\/p><p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4362\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic7.png\" alt=\"\" width=\"952\" height=\"375\" srcset=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic7.png 952w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic7-300x118.png 300w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic7-768x303.png 768w\" sizes=\"(max-width: 952px) 100vw, 952px\" \/><\/p><p>Once this is done, close the shell and click on the Visit The Site button to open the Striim admin portal.<\/p><p>Before you can use Striim, you will need to configure some basic details. Register your details and enter in the Cluster name (I used \u201cDemoCluster\u201d) and password, as well as an admin password. Leave the license field blank to get a trial license if you don\u2019t have a license, then wait for the installation to finish.<\/p><p>When you get to the home screen for Striim, you will see three options. Let\u2019s start by creating an app to connect your on-premises database with BigQuery to perform the initial load of data. To create this application, you will need to start from scratch from the applications area. Give your application a name and you will be presented with a blank canvas.<\/p><p>The first step is to read data from\u00a0<a href=\"https:\/\/contentlab.com\/monitoring-mysql-database-performance-with-new-relic\/\">MySQL<\/a>, so drag a database reader from the sources tab on the left. Double-click on the database reader to set the connection string with a JDBC-style URL using the template:<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-f6ed0d0 elementor-widget elementor-widget-code-highlight\" data-id=\"f6ed0d0\" data-element_type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<div class=\"prismjs-default copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-javascript line-numbers\">\n\t\t\t\t<code readonly=\"true\" class=\"language-javascript\">\n\t\t\t\t\t<xmp>jdbc:mysql:\/\/<server_ip>:<port>\/<database><\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-54d587e elementor-widget elementor-widget-text-editor\" data-id=\"54d587e\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>You must also specify the tables to synchronize \u2014 for this example, purchases \u2014 as this allows you to restrict what is synchronized.<\/p><p>Finally, create a new output. I called mine PurchasesDataStream.<\/p><p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4393\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/Striim_BigQuery-e1636141097698-1.png\" alt=\"\" width=\"734\" height=\"955\" srcset=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/Striim_BigQuery-e1636141097698-1.png 734w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/Striim_BigQuery-e1636141097698-1-231x300.png 231w\" sizes=\"(max-width: 734px) 100vw, 734px\" \/><\/p><p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4394\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/Striim_BigQuery2.png\" alt=\"\" width=\"734\" height=\"998\" srcset=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/Striim_BigQuery2.png 734w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/Striim_BigQuery2-221x300.png 221w\" sizes=\"(max-width: 734px) 100vw, 734px\" \/><\/p><p>You also need to connect your BigQuery instance to your source. Drag a BigQuery writer from the targets tab on the left. Double-click on the writer and select the input stream from the previous step and specify the location of the service account key. Finally, map the source and target tables together using the form:<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-58175fb elementor-widget elementor-widget-code-highlight\" data-id=\"58175fb\" data-element_type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<div class=\"prismjs-default copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-javascript line-numbers\">\n\t\t\t\t<code readonly=\"true\" class=\"language-javascript\">\n\t\t\t\t\t<xmp><source-database>.<source-table>,<target-database>.<target-table><\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-472b4907 elementor-widget elementor-widget-text-editor\" data-id=\"472b4907\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p><\/p>\n<pre><span style=\"color: var( --e-global-color-text ); font-family: var( --e-global-typography-text-font-family ), Sans-serif; font-weight: var( --e-global-typography-text-font-weight ); font-size: 1rem;\">For this use case this is just a single table on each side.<\/span><br><\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4368\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/Striim_BigQuery3-e1636140879858.png\" alt=\"\" width=\"758\" height=\"1618\" srcset=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/Striim_BigQuery3-e1636140879858.png 758w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/Striim_BigQuery3-e1636140879858-141x300.png 141w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/Striim_BigQuery3-e1636140879858-480x1024.png 480w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/Striim_BigQuery3-e1636140879858-720x1536.png 720w\" sizes=\"(max-width: 758px) 100vw, 758px\" \/><\/p>\n<p>Once both the source and target connectors have been configured, deploy and start the application to begin the initial load process. Once the application is deployed and running, you can use the monitor menu option on the top left of the screen to watch the progress.<a href=\"https:\/\/contentlab.com\/wp-content\/uploads\/2021\/11\/MySQLtoGoogleBigQuery_graphic12.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4363\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic12.png\" alt=\"\" width=\"997\" height=\"574\" srcset=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic12.png 997w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic12-300x173.png 300w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic12-768x442.png 768w\" sizes=\"(max-width: 997px) 100vw, 997px\" \/><noscript>&lt;img class=&#8221;size-full wp-image-26772 alignnone&#8221; src=&#8221;https:\/\/contentlab.com\/wp-content\/uploads\/2021\/11\/MySQLtoGoogleBigQuery_graphic12.png&#8221; alt=&#8221;&#8221; width=&#8221;997&#8243; height=&#8221;574&#8243; \/&gt;<\/noscript><\/a><\/p>\n<p>Because this example contains a small data load, the initial load application finishes pretty quickly. You can now stop this initial load application and move on to the synchronization.<\/p>\n<h2>Updating BigQuery with Change Data Capture<\/h2>\n<p>Striim has pushed your current database up into BigQuery, but ideally you want to update this every time the on-premises database changes. This is where the change data capture application comes into play.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4364\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic13.png\" alt=\"\" width=\"223\" height=\"166\"><\/p>\n<p>Go back to the applications screen in Striim and create a new application from a template. Find and select the MySQL CDC to BigQuery option.<\/p>\n<p>Like the first application, you need to configure the details for your on-premises MySQL source. Use the same basic settings as before. However, this time the wizard adds the JDBC component to the connection URL.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4370\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/StriimWizard_MySqlCDC-e1636140953674.png\" alt=\"\" width=\"1385\" height=\"1301\" srcset=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/StriimWizard_MySqlCDC-e1636140953674.png 1385w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/StriimWizard_MySqlCDC-e1636140953674-300x282.png 300w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/StriimWizard_MySqlCDC-e1636140953674-1024x962.png 1024w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/StriimWizard_MySqlCDC-e1636140953674-768x721.png 768w\" sizes=\"(max-width: 1385px) 100vw, 1385px\" \/><\/p>\n<p>When you click <strong>Next<\/strong>, Striim will ensure that it can connect to the local source. Striim will retrieve all the tables from the source. Select the tables you want to sync. For this example, it\u2019s just the purchases table.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4365\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic15.png\" alt=\"\" width=\"893\" height=\"398\" srcset=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic15.png 893w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic15-300x134.png 300w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic15-768x342.png 768w\" sizes=\"(max-width: 893px) 100vw, 893px\" \/><\/p>\n<p>Once the local tables are mapped, you need to connect to the BigQuery target. Again, you can use the same settings as before by specifying the same service key JSON file, table mapping, and GCP Project ID.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4366\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic16.png\" alt=\"\" width=\"720\" height=\"497\" srcset=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic16.png 720w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic16-300x207.png 300w\" sizes=\"(max-width: 720px) 100vw, 720px\" \/><\/p>\n<p>Once the setup of the application is complete, you can deploy and turn on the synchronization application. This will monitor the on-premises database for any changes, then synchronize them into BigQuery.<\/p>\n<p>Let\u2019s see this in action by clicking on the monitor button again and loading some data into your on-premises database. As the data loads, you will see the transactions being processed by Striim.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4367\" src=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic17-1.png\" alt=\"\" width=\"1010\" height=\"545\" srcset=\"https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic17-1.png 1010w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic17-1-300x162.png 300w, https:\/\/contentlab.com\/wp-content\/uploads\/2020\/06\/MySQLtoGoogleBigQuery_graphic17-1-768x414.png 768w\" sizes=\"(max-width: 1010px) 100vw, 1010px\" \/><\/p>\n<h2>Next Steps<\/h2>\n<p>As you can see, Striim makes it easy for you to synchronize your on-premises data from existing databases, such as MySQL, to BigQuery. By constantly moving your data into BigQuery, you could now start building analytics or machine learning models on top, all with minimal impact to your current systems. You could also start ingesting and normalizing more datasets with Striim to fully take advantage of your data when combined with the power of BigQuery.<\/p>\n<p>To learn more about Striim for Google BigQuery, check out the <a href=\"https:\/\/www.striim.com\/striim-for-google-bigquery\/\">related product page<\/a>. Striim is not limited to MySQL to BigQuery integration, and supports many different <a href=\"https:\/\/www.striim.com\/sources-and-targets\/\">sources and targets<\/a>. To see how Striim can help with your move to cloud-based services, <a href=\"https:\/\/go2.striim.com\/demo-region-select\">schedule a demo<\/a> with a Striim technologist or <a href=\"https:\/\/go2.striim.com\/download-free-trial\">download a free trial<\/a> of the platform.<\/p>\n<p><em>This article was originally posted on <a href=\"https:\/\/www.striim.com\/blog\/2020\/04\/tutorial-migrating-from-mysql-to-bigquery\/\">striim&#8217;s blog<\/a>.<\/em><\/p>\n<p><em><span class=\"TextRun SCXW156001673 BCX2\" lang=\"EN-US\" xml:lang=\"EN-US\" data-contrast=\"none\"><span class=\"NormalTextRun SCXW156001673 BCX2\">If you\u2019re interested in developing expert technical content that performs, <\/span><\/span><a class=\"Hyperlink SCXW156001673 BCX2\" href=\"https:\/\/contentlab.com\/contact\/\" target=\"_blank\" rel=\"noreferrer noopener\"><span class=\"TextRun Underlined SCXW156001673 BCX2\" lang=\"EN-US\" xml:lang=\"EN-US\" data-contrast=\"none\"><span class=\"NormalTextRun SCXW156001673 BCX2\" data-ccp-charstyle=\"Hyperlink\">let\u2019s have a conversation<\/span><\/span><\/a><span class=\"TextRun SCXW156001673 BCX2\" lang=\"EN-US\" xml:lang=\"EN-US\" data-contrast=\"none\"><span class=\"NormalTextRun SCXW156001673 BCX2\"> today.<\/span><\/span><br><\/em><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>In this post, we will walk through an example of how to replicate and synchronize your data from on-premises MySQL to BigQuery using change data capture (CDC).<\/p>\n","protected":false},"author":3,"featured_media":5331,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[276,278,18],"tags":[286],"hashtags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Tutorial: Migrating from MySQL to BigQuery for Real-Time Data Analytics &#183; Technical Content Marketing | ContentLab<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Tutorial: Migrating from MySQL to BigQuery for Real-Time Data Analytics &#183; Technical Content Marketing | ContentLab\" \/>\n<meta property=\"og:description\" content=\"In this post, we will walk through an example of how to replicate and synchronize your data from on-premises MySQL to BigQuery using change data capture (CDC).\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/\" \/>\n<meta property=\"og:site_name\" content=\"Technical Content Marketing | ContentLab\" \/>\n<meta property=\"article:published_time\" content=\"2020-06-30T20:49:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-01-31T21:06:29+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/contentlab.com\/wp-content\/uploads\/2019\/03\/4.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1746\" \/>\n\t<meta property=\"og:image:height\" content=\"530\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"ContentLab\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@ContentLabIO\" \/>\n<meta name=\"twitter:site\" content=\"@ContentLabIO\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ContentLab\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/contentlab.com\/tutorial-migrating-from-mysql-to-bigquery-for-real-time-data-analytics\/\"},\"author\":{\"name\":\"ContentLab\",\"@id\":\"https:\/\/contentlab.com\/#\/schema\/person\/e1b8cacc1019e83e8d6cd9ab1a049532\"},\"headline\":\"Tutorial: Migrating from MySQL to BigQuery for Real-Time Data Analytics\",\"datePublished\":\"2020-06-30T20:49:35+00:00\",\"dateModified\":\"2022-01-31T21:06:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/contentlab.com\/tutorial-migrating-from-mysql-to-bigquery-for-real-time-data-analytics\/\"},\"wordCount\":1457,\"publisher\":{\"@id\":\"https:\/\/contentlab.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/contentlab.com\/wp-content\/uploads\/2019\/03\/4.png\",\"keywords\":[\"MySQL\"],\"articleSection\":[\"Databases\",\"Hands-on Tutorial\",\"Portfolio\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/contentlab.com\/tutorial-migrating-from-mysql-to-bigquery-for-real-time-data-analytics\/\",\"url\":\"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/\",\"name\":\"Tutorial: Migrating from MySQL to BigQuery for Real-Time Data Analytics &#183; Technical Content Marketing | ContentLab\",\"isPartOf\":{\"@id\":\"https:\/\/contentlab.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/contentlab.com\/wp-content\/uploads\/2019\/03\/4.png\",\"datePublished\":\"2020-06-30T20:49:35+00:00\",\"dateModified\":\"2022-01-31T21:06:29+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#primaryimage\",\"url\":\"https:\/\/contentlab.com\/wp-content\/uploads\/2019\/03\/4.png\",\"contentUrl\":\"https:\/\/contentlab.com\/wp-content\/uploads\/2019\/03\/4.png\",\"width\":1746,\"height\":530},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/contentlab.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Tutorial: Migrating from MySQL to BigQuery for Real-Time Data Analytics\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/contentlab.com\/#website\",\"url\":\"https:\/\/contentlab.com\/\",\"name\":\"Technical Content Marketing | ContentLab\",\"description\":\"Technical Content Marketing Done Right.\",\"publisher\":{\"@id\":\"https:\/\/contentlab.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/contentlab.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/contentlab.com\/#organization\",\"name\":\"ContentLab.io\",\"url\":\"https:\/\/contentlab.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/contentlab.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/contentlab.com\/wp-content\/uploads\/2021\/11\/favicon-1.png\",\"contentUrl\":\"https:\/\/contentlab.com\/wp-content\/uploads\/2021\/11\/favicon-1.png\",\"width\":768,\"height\":768,\"caption\":\"ContentLab.io\"},\"image\":{\"@id\":\"https:\/\/contentlab.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/x.com\/ContentLabIO\",\"https:\/\/www.linkedin.com\/company\/17893315\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/contentlab.com\/#\/schema\/person\/e1b8cacc1019e83e8d6cd9ab1a049532\",\"name\":\"ContentLab\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/contentlab.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/3929e740ba8fe876f001a63220500e8e?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/3929e740ba8fe876f001a63220500e8e?s=96&d=mm&r=g\",\"caption\":\"ContentLab\"},\"description\":\"ContentLab provides high-quality written articles, tutorials, courses, and other technical marketing materials to industry leaders. We create no-nonsense tech content that\u2019s purpose-built to attract, educate, and engage your technical audience.\",\"sameAs\":[\"https:\/\/contentlab.op\"],\"url\":\"https:\/\/contentlab.com\/author\/contentlab\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Tutorial: Migrating from MySQL to BigQuery for Real-Time Data Analytics &#183; Technical Content Marketing | ContentLab","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/","og_locale":"en_US","og_type":"article","og_title":"Tutorial: Migrating from MySQL to BigQuery for Real-Time Data Analytics &#183; Technical Content Marketing | ContentLab","og_description":"In this post, we will walk through an example of how to replicate and synchronize your data from on-premises MySQL to BigQuery using change data capture (CDC).","og_url":"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/","og_site_name":"Technical Content Marketing | ContentLab","article_published_time":"2020-06-30T20:49:35+00:00","article_modified_time":"2022-01-31T21:06:29+00:00","og_image":[{"width":1746,"height":530,"url":"https:\/\/contentlab.com\/wp-content\/uploads\/2019\/03\/4.png","type":"image\/png"}],"author":"ContentLab","twitter_card":"summary_large_image","twitter_creator":"@ContentLabIO","twitter_site":"@ContentLabIO","twitter_misc":{"Written by":"ContentLab","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#article","isPartOf":{"@id":"https:\/\/contentlab.com\/tutorial-migrating-from-mysql-to-bigquery-for-real-time-data-analytics\/"},"author":{"name":"ContentLab","@id":"https:\/\/contentlab.com\/#\/schema\/person\/e1b8cacc1019e83e8d6cd9ab1a049532"},"headline":"Tutorial: Migrating from MySQL to BigQuery for Real-Time Data Analytics","datePublished":"2020-06-30T20:49:35+00:00","dateModified":"2022-01-31T21:06:29+00:00","mainEntityOfPage":{"@id":"https:\/\/contentlab.com\/tutorial-migrating-from-mysql-to-bigquery-for-real-time-data-analytics\/"},"wordCount":1457,"publisher":{"@id":"https:\/\/contentlab.com\/#organization"},"image":{"@id":"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#primaryimage"},"thumbnailUrl":"https:\/\/contentlab.com\/wp-content\/uploads\/2019\/03\/4.png","keywords":["MySQL"],"articleSection":["Databases","Hands-on Tutorial","Portfolio"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/contentlab.com\/tutorial-migrating-from-mysql-to-bigquery-for-real-time-data-analytics\/","url":"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/","name":"Tutorial: Migrating from MySQL to BigQuery for Real-Time Data Analytics &#183; Technical Content Marketing | ContentLab","isPartOf":{"@id":"https:\/\/contentlab.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#primaryimage"},"image":{"@id":"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#primaryimage"},"thumbnailUrl":"https:\/\/contentlab.com\/wp-content\/uploads\/2019\/03\/4.png","datePublished":"2020-06-30T20:49:35+00:00","dateModified":"2022-01-31T21:06:29+00:00","breadcrumb":{"@id":"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#primaryimage","url":"https:\/\/contentlab.com\/wp-content\/uploads\/2019\/03\/4.png","contentUrl":"https:\/\/contentlab.com\/wp-content\/uploads\/2019\/03\/4.png","width":1746,"height":530},{"@type":"BreadcrumbList","@id":"https:\/\/www.striim.com\/recipes\/migrating-from-mysql-to-bigquery\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/contentlab.com\/"},{"@type":"ListItem","position":2,"name":"Tutorial: Migrating from MySQL to BigQuery for Real-Time Data Analytics"}]},{"@type":"WebSite","@id":"https:\/\/contentlab.com\/#website","url":"https:\/\/contentlab.com\/","name":"Technical Content Marketing | ContentLab","description":"Technical Content Marketing Done Right.","publisher":{"@id":"https:\/\/contentlab.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/contentlab.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/contentlab.com\/#organization","name":"ContentLab.io","url":"https:\/\/contentlab.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/contentlab.com\/#\/schema\/logo\/image\/","url":"https:\/\/contentlab.com\/wp-content\/uploads\/2021\/11\/favicon-1.png","contentUrl":"https:\/\/contentlab.com\/wp-content\/uploads\/2021\/11\/favicon-1.png","width":768,"height":768,"caption":"ContentLab.io"},"image":{"@id":"https:\/\/contentlab.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/ContentLabIO","https:\/\/www.linkedin.com\/company\/17893315\/"]},{"@type":"Person","@id":"https:\/\/contentlab.com\/#\/schema\/person\/e1b8cacc1019e83e8d6cd9ab1a049532","name":"ContentLab","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/contentlab.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/3929e740ba8fe876f001a63220500e8e?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3929e740ba8fe876f001a63220500e8e?s=96&d=mm&r=g","caption":"ContentLab"},"description":"ContentLab provides high-quality written articles, tutorials, courses, and other technical marketing materials to industry leaders. We create no-nonsense tech content that\u2019s purpose-built to attract, educate, and engage your technical audience.","sameAs":["https:\/\/contentlab.op"],"url":"https:\/\/contentlab.com\/author\/contentlab\/"}]}},"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/contentlab.com\/wp-content\/uploads\/2019\/03\/4.png","_links":{"self":[{"href":"https:\/\/contentlab.com\/wp-json\/wp\/v2\/posts\/3164"}],"collection":[{"href":"https:\/\/contentlab.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/contentlab.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/contentlab.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/contentlab.com\/wp-json\/wp\/v2\/comments?post=3164"}],"version-history":[{"count":6,"href":"https:\/\/contentlab.com\/wp-json\/wp\/v2\/posts\/3164\/revisions"}],"predecessor-version":[{"id":6527,"href":"https:\/\/contentlab.com\/wp-json\/wp\/v2\/posts\/3164\/revisions\/6527"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/contentlab.com\/wp-json\/wp\/v2\/media\/5331"}],"wp:attachment":[{"href":"https:\/\/contentlab.com\/wp-json\/wp\/v2\/media?parent=3164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/contentlab.com\/wp-json\/wp\/v2\/categories?post=3164"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/contentlab.com\/wp-json\/wp\/v2\/tags?post=3164"},{"taxonomy":"hashtags","embeddable":true,"href":"https:\/\/contentlab.com\/wp-json\/wp\/v2\/hashtags?post=3164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}