AWS RDS MSSQL to BigQuery Integration: Maximizing Analytical Potential

Data is one of the most valuable assets, allowing corporations to make insightful decisions to boost their business performance. By efficiently utilizing their on-premise data, companies are transitioning towards an advanced analytical environment to extract more profound insights.

AWS Relational Database Service (RDS) is an Amazon data management web service that can help you manage your data efficiently. Eventually, you can transfer this data into a data warehousing service such as BigQuery for improved analytics and scalability.

In this article, you will learn about the AWS RDS MSSQL to BigQuery data integration in two ways. These methods will help you transfer data from your SQL Server instance to an analytically optimized Google BigQuery environment.

An Overview of Amazon RDS

Amazon Relational Database Service (Amazon RDS) is a fully managed component of Amazon Web Services (AWS) that allows you to create, operate, and scale a relational database on the cloud. It offers a cost-effective, resizable capacity for an industry-standard relational database while handling everyday database management tasks.

Amazon RDS supports some of the most popular relational databases, including SQL Server, MySQL, and PostgreSQL, among others. With RDS Custom, you can utilize Amazon RDS’s managed features while managing the host and customizing the OS as in Amazon EC2. To know more, refer to Working with Amazon RDS Custom.

An Overview of Google BigQuery

Google BigQuery is a data warehousing service that helps you manage your data with its functionalities. Its serverless architecture allows you to perform complex SQL queries that can answer your business questions without infrastructure management.

With BigQuery’s distributed analysis engine, you can perform operations on terabytes of data in seconds and petabytes in minutes. It also contains built-in AI that provides features like code assist and intelligent recommendations to help you manage costs while enhancing productivity. Check out the Best Practices for BigQuery.

Methods to Integrate AWS RDS MSSQL to BigQuery

Here, you will explore two popular methods on how to read AWS RDS MSSQL in BigQuery.

Method 1: Using Hevo Data to Load AWS RDS MSSQL to BigQuery

Hevo Data is a no-code, automated ELT data pipeline platform that provides a cost-effective solution to real-time data integration. It enables you to transfer data through 150+ data sources that are flexible to your requirements.

Here are some critical offerings of Hevo Data.

• Data Transformation: Hevo Data provides a powerful drag-and-drop and Python-based data transformation feature to help clean and prepare your data.

• Incremental Data Load: Hevo allows you to transfer your modified data in real time, ensuring efficient bandwidth utilization on both the source and the destination ends.

• Automated Schema Mapping: Hevo automatically detects the incoming data format and manages the destination schema to become compatible with the data. It offers the choice between Full and Incremental Mappings according to your data replication requirements.

Let’s look into the steps of using Hevo Data for an AWS RDS MSSQL to BigQuery integration.

Step 1: Configuring Amazon RDS SQL Server as a Source

This involves configuring your Amazon RDS SQL Server as the source end of your data integration pipeline. But before getting started, you must ensure specific prerequisites.

Prerequisites

• MS SQL Server 2008 version or higher.
• For Pipeline Mode of Change Tracking or Table and Change Tracking Query mode,
  • Enable Change Tracking
  • Grant the database user ALTER DATABASE and CHANGE TRACKING privileges.
• Whitelist Hevo’s IP Addresses.
• Grant SELECT and VIEW CHANGE TRACKING privileges to the database user.
• You must have the designation of Team Administrator, Pipeline Administrator, or Team Collaborator role in Hevo.

After satisfying the prerequisites, you can perform the following steps to set AWS RDS MSSQL as your source:

• Select PIPELINES in the Navigation Bar.
• From the Pipelines List View, click + CREATE.
• Select Amazon RDS SQL Server on the Select Source Type page.
• Specify the necessary fields in the Configure your Amazon RDS SQL Server Source page.

• Finally, click TEST CONNECTION and then TEST & CONTINUE to set up the destination. But before that, manage the Object and Query Mode Settings according to your ingestion mode.

Following the steps mentioned, you can easily set Amazon RDS SQL Server as your data pipeline source. You can follow the official Hevo Documentation on Amazon RDS SQL Server to learn more about the steps.

Step 2: Setting up Google BigQuery as a Destination

In this section, you will learn about configuring Google BigQuery as the destination end of your data pipeline. Before proceeding, you must ensure the prerequisites are satisfied.

Prerequisites

• An available GCP project. If you don’t have one, refer to Create a Google Cloud project.
• You must assign the essential roles to the connecting Google account for the GCP project.
• Your GCP project must have an active billing account.
• To create a Hevo Destination, you’re assigned a Team Collaborator or any administrative role apart from the Billing Administrator.

After meeting all the prerequisites, you can easily configure BigQuery as your destination by following the steps below:

• On the Navigation Bar, select DESTINATIONS.

• In the Destinations List View, click on + CREATE.

• Select Google BigQuery as the Destination type on the Add Destination page.

• Specify the mandatory fields on the Configure your Google BigQuery Warehouse page.

• Finally, click TEST CONNECTION and then SAVE & CONTINUE. To learn more about loading data to a data warehouse, read Loading Data to a Data Warehouse.

With just two simple steps, you can move your data from AWS RDS MSSQL to BigQuery. You can also refer to the Hevo BigQuery Destination Documentation for in-depth information about the steps involved.

Method 2: Integrating AWS RDS MSSQL to BigQuery Using Amazon S3 and Google Cloud Storage

In this method, you must transfer your AWS RDS SQL Server data to BigQuery using Amazon S3 and Google Cloud Storage as intermediate staging.

Step 1: Migrating Data from Amazon RDS MSSQL to Amazon S3

This step helps migrate data from Amazon RDS MSSQL to an Amazon S3 bucket using the AWS Management Console. But before proceeding, you must ensure that you meet the prerequisite conditions.

Prerequisites

Follow the steps below to generate an IAM policy:

• Choose Policies in the navigation pane from the IAM Management Console.
• You must create a new policy and use the Visual editor tab for the following steps.
• For Service, enter S3 and select the S3 service.
• For Actions, choose the following: ListAllMyBuckets, ListBucket, GetBucketACL, GetBucketLocation, GetObject, PutObject, ListMultipartUploadParts, and AbortMultipartUpload.
• The available options for Resources will depend on the actions you previously selected. For the bucket option, mention the desired bucket’s Amazon Resource Name (ARN). For objects, specify the bucket ARN, and then choose one of the following options:
  • Select Any for both Bucket name and Object name to grant permission to all the files in the bucket.
  • Specify the ARN for specific files or folders you want to grant SQL Server access to.
• Finally, you must follow the console instructions until you finish creating the policy.

After performing these steps, you can follow the steps given below to create an IAM role for the IAM policy from the previous process.

• Select Roles in the navigation pane on the IAM Management Console.
• Create a new IAM role, choose the AWS service, RDS, and RDS – Add Role to Database options. After completing this step, select Next:Permissions at the bottom.
• For the Attach permission policies, enter the name of the IAM policy previously created.
• Follow the instructions in the console to complete the role creation.

Transferring Data from RDS MSSQL to Amazon S3

After satisfying the prerequisites, you can integrate Amazon RDS SQL Server to S3 by following the steps below:

• Sign in to the AWS Management Console and open the RDS console.
• Select the RDS for SQL Server DB instance name.
• In the Manage IAM roles section, choose the IAM role on the Connectivity & security tab to Add IAM roles to this instance.
• Select S3_INTEGRATION for Feature.

• Select Add role.

To learn more, refer to AWS Documentation on Integrating AWS RDS MSSQL to S3.

Step 2: Moving Data from Amazon S3 to Google Cloud Storage

You can follow the steps in this section to move your data from Amazon S3 to GCS, where BigQuery can easily access the data.

• You can export all the data present in Amazon S3 into CSV format. To download the data to your system, follow the instructions for Downloading an Object Using an S3 Console.
• After downloading data from S3, you can follow the steps to Upload an object to a GCS bucket to move data to GCS.

Following these steps, you can quickly move data from Amazon S3 to GCS.

Step 3: Moving Data from Google Cloud Storage to Google BigQuery

In this step, you will move the data stored in GCS to Google BigQuery. You must utilize the BigQuery Console to upload the CSV data. You can refer to the Loading CSV data from Cloud Storage to learn more about this process, the permissions required, and the associated limitations.

Follow the steps given below to move data from a Google Cloud Storage to BigQuery:

• Visit the BigQuery page on the Google Cloud Console.
• In the Explorer pane, expand your project to select a dataset.
• Select + Create table in the Dataset info section.
• Specify the necessary details in the Create table panel to create a table.

Following these steps, you can move your data from GCS to Google BigQuery.

Limitations of Using Method 2

The method to connect and load data from AWS RDS MSSQL to BigQuery using Amazon S3 and Google Cloud Storage is an effective way. However, there are certain limitations that you must be aware of while using this method of integration between AWS RDS MSSQL and BigQuerys table.

• Time consumption: Many steps associated with this method require manual effort, increasing the time needed for the integration process. It may not be the best choice for real-time integration requirements.

• Complexity: This method requires reviewing all the necessary documentation to achieve the integration, which can complicate the process. It also requires prior technical knowledge.

• Risk of Error Production: Following this method requires manual efforts. This might even introduce errors and generate inconsistencies in the data transfer process.

Use Cases of Moving Data from AWS RDS MSSQL to BigQuery

• Perform Advanced Analysis: AWS RDS MSSQL to BigQuery integration allows you to perform ad hoc analysis, business intelligence, geospatial analysis, and machine learning on your data to produce actionable insights.

• Warehouse Storage: Moving your data from AWS RDS MSSQL to BigQuery allows you to store it in an analytics-ready format. BigQuery provides full support for database transaction semantics (ACID).

• Manage Data Types: Moving data from AWS RDS MSSQL to BigQuery allows you to easily manage all data types with fine-grained access controls across Google Cloud. To know more, refer to BigQuery Omni.

Conclusion

This article describes two effective methods to convert AWS RDS MSSQL to BigQuery. Although both methods successfully load data from AWS RDS SQL Server to BigQuery, the custom method using S3 and GCS is associated with some drawbacks.

You can use Hevo Data to load data from AWS RDS MSSQL to BigQuery efficiently. Hevo provides 150+ source connector options to help you connect your data without interruptions.

Frequently Asked Questions (FAQs)

Q. What are the different ways to load data from AWS RDS to Google BigQuery?

1. There are two ways to move your data from AWS RDS to BigQuery. Here are both of them:
   1. You can use BigQuery Data Transfer Service to move your data from Amazon S3 to BigQuery. However, you must first move your data from AWS RDS to S3 for this method.
   2. Another technique involves using a SaaS-based platform like Hevo Data that will automate your integration of AWS RDS to BigQuery.

Interested in reading about more integrations from different databases to BigQuery? Here are some interesting reads:

Aurora to BigQuery

DynamoDB to BigQuery ETL

MySQL to BigQuery

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable Hevo pricing that will help you choose the right plan for your business needs.

Visit our Website to Explore Hevo