Automatically synchronize PolarDB-X metadata - AnalyticDB

If you need to complete one-stop PolarDB-X data synchronization and management, you can enable the columnar storage engine on a PolarDB-X instance and bind it to a destination Enterprise, Basic, or Data Lakehouse Edition cluster. Compared with other data synchronization solutions, the one-stop solution features low cost, ease of use, and real-time data synchronization for true real-time analytics. This topic describes how to synchronize data from a PolarDB-X instance to an AnalyticDB for MySQL Enterprise, Basic, or Data Lakehouse Edition cluster in a one-stop manner.

Prerequisites

The Enterprise, Basic, or Data Lakehouse Edition cluster and the PolarDB-X 2.0 instance are deployed in the same region and belong to the same VPC. For more information, see Create an Enterprise Edition or Basic Edition cluster and Create a PolarDB-X 2.0 instance.
The PolarDB-X 2.0 instance is of the Enterprise Edition and the version is 5.4.19-16989811 or later.
Note
For information about how to view and upgrade the instance version, see View and upgrade the instance version.
Enterprise, Basic, or Data Lakehouse EditionThe kernel version of the cluster must be 3.2.0.0 or later.
Note
Go to the AnalyticDB for MySQL console and navigate to the Cluster Information page. In the Configuration Information section, view and upgrade the kernel version.
A database account is created for the Enterprise, Basic, or Data Lakehouse Edition cluster.

Background information

AnalyticDB for MySQL is a real-time data warehouse built on a lakehouse architecture that delivers high-performance queries on massive data. PolarDB-X is a high-performance cloud-native distributed database that supports millions of concurrent connections and hundreds of petabytes of storage. After you enable the columnar storage engine on a PolarDB-X 2.0 instance and bind it to an Enterprise, Basic, or Data Lakehouse Edition cluster, the Enterprise, Basic, or Data Lakehouse Edition cluster automatically creates a metadata discovery task. The metadata of columnar tables in the PolarDB-X 2.0 instance is automatically synchronized to the Enterprise, Basic, or Data Lakehouse Edition cluster, where you can perform data analysis directly.

The one-stop synchronization of PolarDB-X 2.0 instance data provides the following benefits:

Real-time synchronization: After you modify data in PolarDB-X 2.0, the changes are synchronized to the Enterprise, Basic, or Data Lakehouse Edition cluster within seconds. You can query the updated data in the Enterprise, Basic, or Data Lakehouse Edition cluster within seconds for true real-time analytics.
Ease of use: After data and table schemas in PolarDB-X 2.0 are modified, the changes are automatically synchronized to the Enterprise, Basic, or Data Lakehouse Edition cluster without any manual management.
High cost-effectiveness and low cost：Enabling the columnar storage engine on PolarDB-X 2.0 incurs no additional fees. When you perform data analysis in the Enterprise, Basic, or Data Lakehouse Editioncluster, you can use job-based resource groups for SQL development. Job-based resource groups provide elastic computing and storage resources on demand at low cost.

Scenarios

One-stop synchronization of PolarDB-X 2.0 data is applicable to the following scenarios:

Real-time analysis of PolarDB-X 2.0 data: Perform real-time analysis on data in PolarDB-X 2.0 without affecting online business.
Multi-data source joint analysis: Associate and analyze data from PolarDB-X 2.0 with data from other sources.
Multi-instance PolarDB-X 2.0 data analysis: Perform joint analysis on data across multiple PolarDB-X 2.0 instances.

Precautions

During data synchronization, databases and tables with the same names as those in the PolarDB-X 2.0 instance are created in the Enterprise, Basic, or Data Lakehouse Edition cluster. Therefore, take note of the following items during data synchronization:

If a database or table with the same name already exists in the Enterprise, Basic, or Data Lakehouse Edition cluster, data synchronization fails.
If only a database with the same name exists in the Enterprise, Basic, or Data Lakehouse Edition cluster, no new database is created. Tables are synchronized to the existing database.

Data preparation

In this example, the PolarDB-X 2.0 database is named test_adb, and a table named person is created in this database. Example:

CREATE DATABASE test_adb MODE='AUTO'
CREATE TABLE  person (
`id` bigint NOT NULL AUTO_INCREMENT,
`age` int(11) NOT NULL DEFAULT '0',
`name` char(60) NOT NULL DEFAULT '',
`city` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
INDEX `age_1` (`age`)
) PARTITION BY KEY(`id`) PARTITIONS 4;

Insert data into the person table. Example:

INSERT INTO person VALUES (1,23,'Bob','Hangzhou'),(2,33,'Make','Wuhan');

Create a columnar index on the id column of the person table. The index name is person_col_index.

CREATE clustered columnar INDEX `person_col_index` ON person(`id`) PARTITION BY hash(`id`) partitions 4;

Procedure

Step 1:Enable the columnar storage engine for PolarDB-X 2.0

Log on to the PolarDB-X console.
In the upper-left corner of the page, select the region where the target instance is deployed.
On the Instances page, click the PolarDB-X 2.0 tab. Then, click the ID of the target instance.
In the left-side navigation pane, click Columnar Engine.
Click Create Columnar Engine. In the Create Columnar Engine dialog box, click OK.
Note
You can click the button in the upper-right corner of the page to view the Task Progress of creating the columnar storage engine. When the node Status in the Topology section changes to In Use, the columnar storage engine is enabled.
In the upper-right corner of the page, click Enable ADB Lakehouse.
In the ADB Lakehouse Cluster List dialog box, select the destination Enterprise, Basic, or Data Lakehouse Edition cluster and click OK.
Note
After the binding is successful, the destination Enterprise, Basic, or Data Lakehouse Edition cluster automatically creates a corresponding metadata discovery task.

Step 2: Perform data analysis in the Enterprise, Basic, or Data Lakehouse Edition cluster

Log on to theAnalyticDB for MySQL console.
In the upper-left corner, select the region where the cluster is deployed. In the left-side navigation pane, click Clusters and then click the ID of the target cluster.
In the left-side navigation pane, choose Data Import > Metadata Discovery.
In the Task List section, view the running status of the automatically generated metadata discovery task.

After the task runs successfully (that is, Last Running Status shows Success), you can go to the Job Development > SQL Development page to view the databases and tables synchronized to the Enterprise, Basic, or Data Lakehouse Edition cluster.

SELECT * FORM test_adb.person;

Returned results:

+--------+---------+----------+----------+
|   id   |  name   |   age    |   city   |
+--------+---------+----------+----------+
|   1    |   23    |   Bob    | Hangzhou |
+--------+---------+----------+----------+
|   2    |   33    |   Make   |   Wuhan  |
+--------+---------+----------+----------+