With Google Cloud Platform (GCP) MySQL, businesses can manage relational databases with more stability and scalability. GCP MySQL provides dependable data storage and effective query processing. However, enterprises can run into constraints with GCP MySQL, such as agility and scalability issues, performance constraints, and manual resource management requirements.
You can overcome these constraints and open up new data management and analytics options by switching from GCP MySQL to Snowflake. Snowflake provides unmatched concurrency, scalability, and performance, allowing enterprises to easily manage various workloads. In this article, you can examine the advantages and how to read GCP MySQL in Snowflake.
Why Migrate from GCP MySQL to Snowflake?
There are multiple perks of integration between GCP MySQL and Snowflakes, so let’s look at some of them:
- Unlike standard MySQL deployments, Snowflake’s architecture allows for simple scaling, handling variable workloads effortlessly without the need for manual intervention.
- Snowflake’s division of computing and storage resources guarantees superior performance, facilitating quicker data processing and query execution while retaining high-performance analytical capabilities.
- Snowflake offers a fully managed pay-as-you-go SaaS cloud data warehouse.
Overview of Google Cloud Platform (GCP) MySQL
One of the fully managed, highly available, and scalable database options offered by Google is GCP MySQL. The Google Cloud SQL offers numerous database infrastructure configuration choices, read replicas, and scaling up with the replication feature. Cloud SQL has a backup feature that lets you restore datasets in case of data loss. The platform also makes it easier to convert from a backup by providing an extensive toolkit. Google Cloud SQL is an appealing option because of its flexibility, dependability, and user-friendliness.
Overview of Snowflake
Snowflake is a fully managed SaaS that was developed in 2012. It offers a single platform for data warehousing, data lakes, data engineering, data science, and data application development. The scalable architecture that underlines Snowflake supports large workloads and huge data. Any SQL client can establish a connection with its robust management panel. With only a few easy API calls, developers can work in real-time using the Snowflake service’s support for REST APIs.
Methods to Migrate GCP MySQL to Snowflake
There are two methods to integrate GCP MySQL to Snowflake. Let’s look at them in detail.
Method 1: Migrating GCP MySQL to Snowflake using Hevo
Method 2: Migrating GCP MySQL to Snowflake using CSV files
Method 1: Migrating GCP MySQL to Snowflake Using Hevo
Hevo Data is an automated ELT no-code data pipeline platform that is cost-effective and allows you to build flexible data pipelines for real-time data integration. It gives you access to more than 150+ data sources (40+ free connectors) through which you can extract data. Without the need for technical knowledge, integrating data from several sources is possible with Hevo Data’s extremely engaging user interface.
Here are some key features provided by Hevo
- Incremental Data Load: Hevo enables real-time data transfers for modifications made in datasets, guaranteeing effective bandwidth use on both ends.
- Data Transformation: The platform’s well-integrated, analyst-friendly data transformation tools help simplify analytical duties further. The drag-and-drop and Python-based transformations let you clean up and prepare the data to be loaded to your destination.
- Auto-Schema: Hevo replicates source data’s schema to the destination automatically, removing the tedious task of schema administration. It also automatically recognizes the format of incoming data. In addition, you have the option of selecting Full or Incremental mappings based on your data replication needs.
Let’s examine how to connect and load data from GCP MySQL to Snowflake using Hevo Data.
Step 1: Configure GCP MySQL as Source
In this section, you will learn how to set up GCP MySQL as the source of your data pipeline. However, you must ensure that the prerequisites are satisfied.
Prerequisites:
- You must ensure the Google Cloud MySQL instance is running, not localhost. Check this by logging onto your Google Cloud MySQL server and seeing if the instance name has an Active indicator next to it.
GCP MySQL to Snowflake: Google Cloud MySQL instances page
Once the prerequisites are met, you can follow the steps below to configure GCP MySQL as a source:
- Choose PIPELINES using the Navigation Bar.
- Click + CREATE in the Pipeline List View.
- On the Select Source Type page, choose GCP MySQL.
- In the Configure your GCP MySQL Source page, fill out the required details.
GCP MySQL to Snowflake: Source specify details page
- Click TEST CONNECTION and select TEST & CONTINUE. Once you have completed the steps, you need to set Object and Query Mode Settings to match your ingestion mode.
Read the GCP MySQL Hevo Documentation to learn more about the procedures.
Step 2: Configure Snowflake as Destination
Before configuring Snowflake as a destination for your data pipeline, make sure all the prerequisites are met.
Prerequisites:
- A Snowflake account that’s active.
- The user can role for Hevo in Snowflake if they have the roles ACCUNTADMIN or SECURITYADMIN.
- In Snowflake, the user must possess the role of SYSADMIN in order to build a warehouse.
- Hevo has access rights to data warehouses under the USAGE category.
- Hevo has been granted rights to CREATE SCHEMA and USAGE databases.
- On the present and future schemas, Hevo has been granted access to CREATE TABLE, CREATE EXTERNAL TABLE, MONITOR, USAGE, and MODIFY rights.
- To create the destination, you havee Team Collaborator role in Hevo or any other administrator role except the Billing Administrator position.
To set Snowflake as a destination in Hevo, follow the below steps:
- In the Navigation Bar, click DESTINATIONS.
- In the Destinations List View, click + CREATE.
- Choose Snowflake as the destination type on the Add Destination page.
- In Configure your Snowflake Destination page, specify the following details.
GCP MySQL to Snowflake: Destinations specify details page
- After clicking TEST CONNECTION, click SAVE & CONTINUE.
You can consult the Hevo Documentation on Snowflake to learn more about the procedures involved.
Method 2: Migrating GCP MySQL to Snowflake using CSV files
You can use the CSV file transfer method to import GCP MySQL to Snowflake.
Step 1: Export Data from GCP MySQL to CSV
Prerequisites:
To initiate the export from Cloud SQL into Cloud Storage, you must have one of the following roles:
- The Cloud SQL Editor role.
- A custom role, which includes the following permissions.
cloudsql.instances.get
cloudsql.instances.export
Furthermore, one of the following roles has to be present in the service account for the Cloud SQL instance:
- The storage.objectionAdmin Identify and Access Management (IAM) role
- A custom role, which includes the following permissions:
storage.objects.create
storage.objects.list
storage.objects.delete
For assistance with IAM roles, visit Identity and Access Management.
Once the prerequisites are met, follow the steps below:
- Navigate to the Cloud SQL instances page in the Google Cloud console.
- Click the instance name to bring up the instance’s Overview page.
- Click on Export.
- To allow other tasks to take place while the export is in progress, select Offload export.
- Click on Show Advanced Options.
- Select the database from the drop-down menu located in the Database section.
- To specify the table from which to export data, execute an SQL query.
- To start the export, click on Export.
- When the Export database? box opens, a message stating that the “export procedure for a big database may take up to an hour” will appear. You can only examine the instance’s contents while the export is in progress. You also have the option to stop the export after it has begun. Click Export if you think it is an appropriate moment to begin an export. If not, select Cancel.
Step 2: Export Data from CSV file to Snowflake
The process of loading CSV files into your Snowflake database is explained in the section below.
Generate an Object in the File Format for CSV Data
To create the mycsvformat file format, use the CREATE FILE FORMAT command.
CREATE OR REPLACE FILE FORMAT mycsvformat
TYPE = 'CSV'
FIELD_DELIMITER = '|'
SKIP_HEADER = 1;
Set up a Stage for CSV Files
The my_CSV_stage can be created by executing CREATE STAGE function:
CREATE OR REPLACE STAGE my_csv_stage
FILE_FORMAT = mycsvformat;
Set up the CSV Sample Data Files
To upload the CSV files from your local file system, run the PUT command.
Linux or macOS
PUT file:///tmp/load/contacts*.csv @my_csv_stage AUTO_COMPRESS=TRUE;
Windows
PUT file://C:\temp\load\contacts*.csv @my_csv_stage AUTO_COMPRESS=TRUE;
Copy Data to the Intended Tables
To load your staged data into the target tables, execute COPY INTO <table>.
COPY INTO mycsvtable
FROM @my_csv_stage/contacts1.csv.gz
FILE_FORMAT = (FORMAT_NAME = mycsvformat)
ON_ERROR = 'skip_file';
Once data has been moved to the target tables, you need to use the Remove command to delete the copied data files from the intimidating staging table.
Limitations of Migrating GCP MySQL to Snowflake Using CSV files
- No Real-time Data Access: Neither automated data refreshes nor real-time data access is supported by the manual CSV export/import method.
- Technical Knowledge: To ensure correct transfer, a thorough grasp of cloud platforms and SQL scripts is required.
- Manual errors: The quantity of code and actions needed to debug and rectify data might result in errors and additional time spent.
Use Cases
- Data Replication: Snowflake supports advanced features such as data replication for multi-vendor engagement or downstream application integration, enhancing data access traceability, and a zero-copy clone that prevents data export (I/O).
- SQL Support: Snowflake complies with ANSI SQL to provide you with full support for analytical functions and automatic query optimization. The platform also supports SQL capabilities, including the ability to do JOINS utilizing the WHERE and AND clauses and the “(+)” operator.
- High Data Availability: Snowflake’s Database Storage layer ensures high data availability, which is synchronously duplicated over many disc devices and three distinct availability zones within the same area.
- Storage: The cloud data platform from Snowflake separates computation from storage, enabling more computing and storage capacity.
Conclusion
Migrating from GCP MySQL to Snowflake can greatly improve your data management and analytics capabilities. Although manual CSV file techniques can work well for short-term or small-scale data transfers, they are less effective for larger or more complicated migrations. Consider exploring alternative methods like Hevo. It can manage schema conversion, streamline the process, and ensure data integrity.
FAQs
Q. Is there any way to migrate the MySQL data to Snowflake without using the third-party connector?
If you don’t have a third-party connector, you can utilize Snowflake’s internal stage option. Create an internal stage by logging in with the required Snowflake credentials at the command prompt. Then, import your MySQL data into Snowflake using the PUT command.
Anaswara is an engineer-turned writer having experience writing about ML, AI, and Data Science. She is also an active Guest Author in various communities of Analytics and Data Science professionals including Analytics Vidhya.