Using Airbyte to migrate data from Google BigQuery to Alibaba Cloud Database.

Airbyte is an open-source tool that offers flexibility for data migration (Extract) from various systems or databases through Connectors that support more than 200+.

After we learned about how to migrate data between databases using Alibaba Cloud Data Transmission Service or DTS in the previous article, this article will discuss another method of data migration, which this time we will use an open source tool called Airbyte.

First, let's get to know Airbyte. Airbyte is an open-source platform for data migration (Data Extraction) that allows users to easily transfer data from various sources into a destination database or storage, whether it's databases, APIs, or different SaaS systems. For the installation steps of Airbyte, you can view more from the link.

How Airbyte works, Airbyte operates by creating a connection between the Source (the data source from which data is to be extracted) and the Destination (where the data will be stored). The data is extracted from the Source and processed in Airbyte before being sent to the Destination as specified by the user.

In this article, we simulate a scenario by extracting data from Google BigQuery to migrate it to Alibaba Cloud AnalyticDB for PostgreSQL.

Pre-requisite

Google BigQuery Service Account and data to be extracted: · Project on Google Cloud that has BigQuery enabled. · Google Cloud Service Account assigned the role "BigQuery User" and "BigQuery Data Editor" in the GCP Project. · Service Account key to verify permissions to access Google Cloud. For more information, click on the Airbyte BigQuery connector.
Alibaba Cloud AnalyticDB for PostgreSQL instance, you can create a 60-day free trial for AnalyticDB for PostgreSQL here. · Whitelist IP Address for the Airbyte server to allow Airbyte to connect to AnalyticDB for PostgreSQL. You can see more steps here.

Steps for using Airbyte to extract data from Google BigQuery are as follows:

1.Setting up the Source in Airbyte:
1.1 Select 'Google BigQuery' from the 'Sources' page in Airbyte UI. Fill in the necessary information such as Project ID, Dataset ID, and information from the JSON key file for various settings:

1.2 In the Source section, select Google BigQuery.

1.3 Fill in the details for various settings and Test connection.

2.Setting up the Destination:
2.1 Select 'PostgreSQL' from the Destination page in Airbyte UI. Fill in the necessary information such as Host, DB Name, and various settings:

3.Create a Connection and start Syncing:
3.1 After creating the Source and Destination and verifying the settings, you can create a Connection to Sync or Migrate the data.

3.2 Select Google BigQuery for the Source.

3.3 Select PostgreSQL for the Destination.

3.4 Select settings such as Frequency, Namespace, and importantly, the Object to be migrated, and "Sync mode" such as "Full refresh | Overwrite" or "Full refresh | Append".

Picture9_1

For Normalization & Transformation of data, we will choose "Raw data (JSON)" this time. In future articles, we will look at how to "Add transformation".

3.5 Start the data migration or Sync process.

3.6 Check the data at the Destination database or Alibaba Cloud AnalyticDB for PostgreSQL.

Here convert from JSONB to tabular

With this method, you can change the Source and Destination according to the different Connectors that Airbyte supports, which are more than 200+ types.

Community

Using Airbyte to migrate data from Google BigQuery to Alibaba Cloud Database.

Read previous post:

Read next post:

Pum

You may also like

Comments

Pum

Related Products

AnalyticDB for MySQL

AnalyticDB for PostgreSQL

Oracle Database Migration Solution

Database Migration Solution