How to Interoperate PolarDB-X with Big Data and Other Systems

Practical tutorial: How to communicate PolarDB-X with big data and other systems

Introduction: PolarDB-X provides a free experimental environment for user experience, where you can experience the installation and deployment of PolarDB-X and various kernel features. In addition to free experiments, PolarDB-X also provides free video courses to teach you how to use the PolarDB-X distributed database.
This experiment will guide you to use PolarDB-X+Canal+ClickHouse to build a real-time analysis system.

Preparation
Assuming that the PolarDB-X has been built and deployed according to the content of the previous lecture, the PolarDB-X database can be successfully linked.

Practical Tutorial: How to Quickly Install and Deploy PolarDB-X

Deploy Canal
Canal is a popular MySQL Binlog incremental subscription tool. For details, please refer to the Canal documentation. Canal provides a Docker image. For details, see the Canal Docker image document.

(1) Execute the following command to download the script.

wget https://raw.githubusercontent.com/alibaba/canal/master/docker/run.sh
(2) Execute the following command to build a queue whose destination name is test.

Note: You need to modify none_loopback_host_ip to the elastic IP of ECS in the cloud product resource list, do not use localhost or 127.0.0.1.


-e canal.instance.gtidon=false
Deploy ClickHouse
ClickHouse is an analysis system. For details, please refer to the official documentation of ClickHouse. ClickHouse provides a Docker image. For details, see the ClickHouseDocker image documentation.

Execute the following command to deploy ClickHouse.

docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 -p 8123:8123 yandex/clickhouse-server
Create test repository and tables in PolarDB-X and ClickHouse
(1) Execute the following command to log in to the PolarDB-X database.

mysql -h127.0.0.1 -P8527 -upolardbx_root -p123456
(2) Execute the following SQL statement to create the database testdb.

CREATE DATABASE testdb;
(3) Execute the following SQL statement and use the database testdb.

USE testdb;
(4) Execute the following SQL statement to create the test table.

CREATE TABLE test( id INT(11) AUTO_INCREMENT PRIMARY KEY, name CHAR(20) not null );
(5) Enter exit to exit the database.

(6) Execute the following command to log in to the ClickHouse database.

docker run -it --rm --link some-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server
(7) Execute the following SQL statement to create the database testdb.

CREATE DATABASE testdb;
(8) Execute the following SQL statement and use the database testdb.

USE testdb;
(9) Execute the following SQL statement to create the test table.

Create Table test(id INT(32),name CHAR(20)) Engine = MergeTree() Order By id;
(10) Enter exit to exit the database.

Run Canal Client to consume and deliver incremental changes
After the above steps, you have prepared the three containers of PolarDB-X, Canal Server and ClickHouse, and built the test database and tables on the source (PolarDB-X) and target (ClickHouse). Next, we consume the incremental data obtained by Canal Server through Canal Client, and deliver the Insert event in the source DML to ClickHouse.

(1) Execute the following command to install JDK 1.8 using yum.

yum -y install java-1.8.0-openjdk*
(2) Execute the following command to download the polardb-x-to-clickhouse-canal-client.jar delivery code file.

wget https://labfileapp.oss-cn-hangzhou.aliyuncs.com/polardb-x-to-clickhouse-canal-client.jar
(3) Execute the following command to run the polardb-x-to-clickhouse-canal-client.jar code file.

java -jar polardb-x-to-clickhouse-canal-client.jar
Note: Do not interrupt the delivery of the code file, otherwise the delivery will fail.

(4) The delivery link has been successfully established, and then you can execute the INSERT statement on the source end (PolarDB-X) and observe the data changes in the target end (ClickHouse).
On the experiment page, click the

icon to create a new terminal window.
a8.png

(5) In a new terminal window, execute the following command to log in to the PolarDB-X database.

mysql -h127.0.0.1 -P8527 -upolardbx_root -p123456
(6) Execute the following SQL statement and use the database testdb.

USE testdb;
(7) Execute the following SQL statement to insert a piece of data.

INSERT INTO test(name) values("polardb-x"), ("is"), ("awsome");
(8) Enter exit to exit the database.

(9) Execute the following command to log in to the ClickHouse database.

docker run -it --rm --link some-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server
(10) Execute the following SQL statement and use the database testdb.

USE testdb;
(11) Execute the following SQL statement to query the test table

SELECT * FROM test;
The returned results are as follows, and you can see that the destination (ClickHouse) has received the delivered data.