Alibaba cloud experience Award: how to connect polardb-x with big data and other systems

Time:2022-8-2

Experience introduction

The scenario will provide an ECS instance (cloud server) configured with CentOS 8.5 operating system. Through the operation of this tutorial, you can experience how to interweave polardb-x with Clickhouse through canal and build a real-time analysis system.Click to go

Experimental preparation

1. Create experimental resources

Before starting the experiment, you need to create ECS instance resources.

  1. On the lab page, clickCreate resources

  2. (optional) in the left navigation bar of the lab page, clickCloud product resourcesList, you can view the relevant information of this experiment resources (such as IP address, user information, etc.).

explain:The resource creation process takes 1-3 minutes.

2. Install polardb-x

This step will guide you on how to install polardb-x.

  1. Install and start docekr.

(1) Execute the following command to install docker.

curl -fsSL https://get.docker.com | bash -s docker --mirror Aliyun

(2) Execute the following command to start docker.

systemctl start docker
  1. Execute the following command to install polardb-x.
docker run -d --name some-polardb-x -p 8527:8527 polardbx/polardb-x:2.1.0

3. Log in to polardb-x database

This step will guide you to log in to the polaridb-x database.

Polardb-x supports connection through MySQL client command line, third-party client and third-party program code conforming to MySQL interactive protocol. This experimental scenario mainly introduces how to connect to polardb-x database through MySQL client command line.

  1. Execute the following command to install mysql.
yum install mysql -y
  1. Execute the following command to check the MySQL version number.
mysql -V

The returned results are as follows, indicating that you have successfully installed mysql.

  1. Execute the following command to log in to the polaridb-x database.

explain:

  • The polardb-x database user name and password in this experimental scenario have been preset. Please log in with the following command.

  • If you encounter mysql: [warning] using a password on the command line interface can be secure Error 2013 (HY000): lost connection to MySQL server at’reading initial communication packet’, system error: 0 an error is reported. Please wait a minute and re execute the login command.

mysql -h127.0.0.1 -P8527 -upolardbx_root -p123456

The returned results are as follows, indicating that you have successfully logged into the polardb-x database.

  1. Enter exit to exit the database.

4. Build a real-time analysis system

This step will guide you how to use polardb-x + Canal + Clickhouse to build a real-time analysis system.

  1. Deploy canal.

Canal is a popular MySQL binlog incremental subscription tool. For details, seeCanal documentation。 Canal provides docker images. For details, seeCanal 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 with destination name as test.

be careful:You need to set none_ loopback_ host_ The IP is modified to the elastic IP of ECs in the cloud product resource list. Do not use localhost or 127.0.0.1.

sh run.sh -e canal.auto.scan=false \
-e canal.destinations=test \
-e canal.instance.master.address=none_loopback_host_ip:8527 \
-e canal.instance.dbUsername=polardbx_root \
-e canal.instance.dbPassword=123456 \
-e canal.instance.connectionCharset=UTF-8 \
-e canal.instance.tsdb.enable=true \
-e canal.instance.gtidon=false
  1. Deploy Clickhouse.

Clickhouse is an analysis system. For details, seeClickhouse official document。 Clickhouse provides docker image. For details, seeClickhousedocker image document

Execute the following command to deploy Clickhouse.

docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 -p 8123:8123 yandex/clickhouse-server
  1. Create test libraries and tables in polardb-x and Clickhouse.

(1) Execute the following command to log in to the polaridb-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.

  1. Run the canal client to consume and post incremental changes.

After the above steps, you have prepared three containers, polardb-x, canal server and Clickhouse, and built the databases and tables for testing on the source side (polardb-x) and the target side (Clickhouse). Next, we consume the incremental data obtained by the canal server through the canal client, and post the insert event in the source DML to Clickhouse.

(1) Execute the following command and 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

be careful:Do not interrupt the delivery of code files, otherwise the delivery will fail.

(4) The delivery link has been successfully opened. Next, you can execute the insert statement on the source side (polardb-x) and observe the data changes in the target side (Clickhouse).

On the experiment page, click theIcon to create a new terminal window.

(5) In the new terminal window, execute the following command to log in to the polaridb-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. You can see that the Clickhouse receives the delivered data.

5. Learn more

Congratulations on completion