AntDB和Clickhouse的安装部署过程忽略，直接从创建数据库和用户开始测试
1、AntDB数据库创建用户
[antdb@localhost ~]$ adb -p 6655 -h 192.168.126.10
Password for user antdb:
adb (13.3)
Type "help" for help.
antdb=# CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';
CREATE ROLE
2、AntDB创建数据库
antdb=# CREATE DATABASE db_in_psg;
CREATE DATABASE
3、AntDB创建数据表
antdb=# \c db_in_psg
db_in_psg=# CREATE TABLE table1 (
db_in_psg(# id integer primary key,
db_in_psg(# column1 varchar(10)
db_in_psg(# );
CREATE TABLE
4、AntDB插入2条数据
db_in_psg=# INSERT INTO table1
db_in_psg-# (id, column1)
db_in_psg-# VALUES
db_in_psg-# (1, 'abc'),
db_in_psg-# (2, 'def');
INSERT 0 2
5、AntDB查询插入数据
db_in_psg=# select * from table1;
id | column1
----+---------
1 | abc
2 | def
(2 rows)
6、ClickHouse创建数据库
[root@localhost data]# clickhouse-client
ClickHouse client version 23.6.2.18 (official build).
Connecting to localhost:9000 as user default.
Password for user (default):
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 23.6.2 revision 54464.
Warnings:
* Linux transparent hugepages are set to "always". Check /sys/kernel/mm/transparent_hugepage/enabled
localhost :) CREATE DATABASE db_in_ch;
CREATE DATABASE db_in_ch
Query id: fd4f73e0-c999-462e-933e-18233a75e86c
Ok.
0 rows in set. Elapsed: 0.004 sec.
7、ClickHouse创建 AntDB（PostgreSQL）引擎数据表
localhost :) CREATE TABLE db_in_ch.table1
(
id UInt64,
column1 String
)
ENGINE = PostgreSQL('192.168.126.10:6655', 'db_in_psg', 'table1', 'clickhouse_user', 'ClickHouse_123');
Query id: 743a8bb5-237c-4ce2-b2a9-b836efffef76
Ok.
8、ClickHouse 查询表数据，可以查询到AntDB中的数据
localhost :) SELECT * FROM db_in_ch.table1
SELECT *
FROM db_in_ch.table1
Query id: e8e513b8-5dd9-4063-bc4e-cf5a9603c507
┌─id─┬─column1─┐
│ 1 │ abc │
│ 2 │ def │
└───────────────
2 rows in set. Elapsed: 0.008 sec.
9、AntDB查询再次插入2条数据
db_in_psg=# INSERT INTO table1
db_in_psg-# (id, column1)
db_in_psg-# VALUES
db_in_psg-# (3, 'ghi'),
db_in_psg-# (4, 'jkl');
INSERT 0 2
10、ClickHouse 再次查询，可以查询到AntDB新增的数据
localhost :) SELECT * FROM db_in_ch.table1
SELECT *
FROM db_in_ch.table1
Query id: aee3b745-9a57-47a3-9d88-5b8afdf395fa
┌─id─┬─column1─┐
│ 1 │ abc │
│ 2 │ def │
│ 3 │ ghi │
│ 4 │ jkl │
└───────────────
4 rows in set. Elapsed: 0.002 sec.
11、clickhouse插入2条数据
localhost :) INSERT INTO db_in_ch.table1
(id, column1)
VALUES
(5, 'mno'),
(6, 'pqr');
INSERT INTO db_in_ch.table1 (id, column1) FORMAT Values
Query id: eb7075e4-9c5a-4deb-8f3d-a982143bed1d
Ok.
2 rows in set. Elapsed: 0.004 sec.
12、clickhouse查询出来6条记录
localhost :)
localhost :) SELECT * FROM db_in_ch.table1
SELECT *
FROM db_in_ch.table1
Query id: 2104a29c-1854-4e5c-becc-643090dd74a7
┌─id─┬─column1─┐
│ 1 │ abc │
│ 2 │ def │
│ 3 │ ghi │
│ 4 │ jkl │
│ 5 │ mno │
│ 6 │ pqr │
└────┴─────────┘
6 rows in set. Elapsed: 0.002 sec.
13、AntDB查询出来6条件记录，可以看到在clickhouse插入的数据在antdb中也能查询到
db_in_psg=# select * from table1;
id | column1
----+---------
1 | abc
2 | def
3 | ghi
4 | jkl
5 | mno
6 | pqr
(6 rows)
上述测试可以看出来AntDB可以和clickhouse实现数据互相访问
