Clickhouse x PostgreSQL --从PG中读取数据作为字典进行查询

最新推荐文章于 2024-09-14 18:32:06 发布

想象造物

最新推荐文章于 2024-09-14 18:32:06 发布

阅读量1k

点赞数 38

分类专栏： clickhouse实践文章标签： clickhouse postgresql 数据库

本文链接：https://blog.csdn.net/simulate_create/article/details/141232715

版权

clickhouse实践专栏收录该内容

7 篇文章 0 订阅

订阅专栏

一、Clickhouse x PostgreSQL 简介

clickhouse支持PG表引擎，可以双向读写PG表。这个特性可以弥补clickhouse对数据管理方面的不足，例如数据的删除、事务、整表查询等，但正好PG擅长这些。因此在一个项目里使用这个两种数据库，是一个比较好的选择。利用clickhouse做数据处理（这部分刚好PG比较弱一些），利用PG进行用户信息、基础数据等的管理。

二、同步PG里的可变更数据

在实际业务中，经常会有一些不太大且会变更的表，例如用户信息表、地理信息表、组织架构表等。其变更不太频繁，但会时不时变更一些。这些数据的管理如果用clickhouse会很不方便，尤其是例如删除等，只有在触发merge的时候才会生效，可能会让用户困惑。这些数据还是放在传统的行数据库里较好。

在流量日志里，一个实际的例子，就是有一个表要用来查询不同网站之间的联系，有个对应关系表。而这个表是经常变化的，但数据量也比较小。因此就使用了Clickhouse x PostgreSQL 的方式，让Clickhouse 读取 PostgreSQL 里的数据。

其步骤是：

一个PG表，用来管理这个关联数据，网页API也直接操作这个表；
clickhouse表：使用PG引擎，用来同步数据。
直接读取clickhouse表，来查询PG表。

三、实践步骤

首先有一个PG表，用来管理这个关联数据，网页API也直接操作这个表；

CREATE TABLE public."link" (
	id serial4 NOT NULL,
	"domain" text NULL,
	link_id int4 NULL,
	CONSTRAINT link_pk PRIMARY KEY (id)
);

创建一个clickhouse表，使用PG引擎，用来同步数据。

这里要引入DICTIONARY 类型的表。这种表就是一个字典，在C、go等语言里就是map，在Python里就dict。本质都是一种哈希表。就是可以用key:value的形式来查询。

创建一个PG引擎的clickhouse表很简单：
```
CREATE TABLE link ENGINE = PostgreSQL('127.0.0.1:5432', 'dbname', 'link', 'user', 'password');
```
这样就可以直接创建一个从PG读数据的表。
为了效率考量，使用了DICTIONARY类型的表。可以使用域名作为key，直接查出其关联的link_id。
```
CREATE DICTIONARY domain_link_dict
(
`domain` String,
`link_id` UInt32,
)
PRIMARY KEY domain
SOURCE(POSTGRESQL(PORT 5432 HOST '127.0.0.1' USER 'user' PASSWORD 'password' DB 'dbname' TABLE 'link'))
LIFETIME(MIN 0 MAX 60)
LAYOUT(COMPLEX_KEY_HASHED());
```
解释：
1. PRIMARY KEY domain指定了主键为domain字段。也就是哈希表的key。
2. SOURCE(POSTGRESQL(...))定义了数据源为PostgreSQL数据库，连接详情包括端口、地址、用户名、密码、数据库名及表名。
3. LIFETIME(MIN 0 MAX 60)设置了词典缓存的有效期最大为60秒。也就是最长多久同步一次PG的数据。
4. LAYOUT(COMPLEX_KEY_HASHED())指定了Hash方式。可以根据数据不同选择其他的方式。

由于DICTIONARY缓存在内存里，所以查询速度很快。

直接读取clickhouse表，来查询PG表。

可以直接查询domain_link_dict的数据，和普通表一样：

select * from domain_link_dict

但要利用特性，一般使用这种方式，使用get：

SELECT dictGet('domain_link_dict', 'link_id', 'test.com')

dictGet的格式是：dictGet(表名，要取的字段名，PK值）。PK为domain，所以用的是test.com。

下面是一个综合查询的例子，利用查询的结果再次进行聚合。其作用是获取所有的link_id为1的domain的所有数据计数：

SELECT	count(*) FROM	log
WHERE log.domain IN (
    SELECT
        domain_link_dict.domain
    FROM
        domain_link_dict
    WHERE
        domain_link_dict.link_id = 1
) AND log."time" >= toStartOfDay(now())

可以在这个基础上，做更加复杂的查询。也可以使用join，效率也不错。以下是一个统计各个link_id 的请求计数的SQL：

SELECT
	dict.link_id as link_id,
	sum(log.requests)
FROM
	log
JOIN (
	SELECT
		domain_link_dict.domain AS domain,
		domain_link_dict.link_id AS link_id
	FROM
		domain_link_dict
) AS dict ON log.domain = dict.domain
WHERE
	log.time >= toStartOfDay(now())
group by
	link_id
order by
	link_id;

引用：

1. ClickHouse and PostgreSQL - a Match Made in Data Heaven - part 1

2. ClickHouse and PostgreSQL - a Match Made in Data Heaven - part 2

3. Dictionaries | ClickHouse Docs