Clickhouse 挑战亿级数据实时查询

概念

标题是噱头。

实际业务中遇到一个场景，需要展示一个（组）设备全年的量测数据的曲线。且用户可以自定义查询时间段。

假设有4000组设备，每隔5分钟采集一个点。那么全年的数据量就为： 4000 * 12 * 24 * 365 = 4亿的数据规模。

实时性要求，查询数据需要在2s内返回。而Mysql单库查询，本人实力有限，优化做的不够好，没达到要求。

所以在对比了几个网上的方案，选择了Clickhouse。

简单介绍下Clickhouse：

ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。由号称“俄罗斯 Google”的Yandex开发而来，在2016年开源，在计算引擎里算是一个后起之秀，在内存数据库领域号称是最快的。由于它有几倍于GreenPlum等引擎的性能优势，所以不少人都选择将其安装云服务器中使用。
ClickHouse是一个列导向数据库，是原生的向量化执行引擎。它在大数据领域没有走Hadoop生态，而是采用Local attached storage作为存储，这样整个IO可能就没有Hadoop那一套的局限。它的系统在生产环境中可以应用到比较大的规模，因为它的线性扩展能力和可靠性保障能够原生支持shard replication这种解决方案。它还提供了一些SQL直接接口，有比较丰富的原生client。

总结：OLAP，列式存储。

行式存储查询过程：

列式存储查询过程：

实践

1. 安装

安装设置等，参照官网，有docker环境，可以直接用docker镜像。

2. 浏览工具

可用的浏览工具有自带的clickhouse-client，或者官方推荐的http://ui.tabix.io/

3. 新建一个数据库

Clickhouse的语法多数与SQL类似，使用clickhouse-client登入后：

代码语言：sql复制

# 显示所有数据库
show databases;

# 新建一个数据库
create database db_dev

# 查询结果
SHOW DATABASES

Query id: a583d5a8-d649-4a17-ab80-651632757c27

┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ db_dev                         │
│ default                        │
│ system                         │
└────────────────────────────────┘

4. 设计表结构

由于设备采集信息带时序，参考ClickHouse的建表语法，设计表如下：

代码语言：sql复制

create table IF NOT EXISTS db_dev.t_dev_lp (
  dev_name String,
  dev_id UInt64,
  ld Float32,
  dt DateTime
) Engine = MergeTree PARTITION BY toYYYYMM(dt) 
primary key intHash32(dev_id) 
SAMPLE BY intHash32(dev_id)
ORDER BY (intHash32(dev_id), dt)
TTL dt   toIntervalYear(3)

# 简单解释下
在db_dev中创建名为 t_dev_lp 的表，且当中包含 
dev_name 设备名称
dev_id   设备Id
ld 		 量测数据
dt 		 量测时间

Engine 类型为 MergeTree
主键是dev_id
使用dev_id 做为采样hash （超大规模数据的时候，使用采样，目前这种用法有问题）
排序是 dev_id   dt 
TTL 为过期时间 即保留三年的数据

PS: 设备信息可单独拎表保存, 这边只是简单设计

5. JDBC连接Clickhouse

Maven 使用官方JDBC驱动

代码语言：javascript复制

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.3.0</version>
</dependency>

配置DataSource （Spring）

代码语言：javascript复制

@Configuration
public class ClickHouseConfig {
    @Bean
    public ClickHouseDataSource clickhouseSource(){
        final String url = "jdbc:clickhouse://serverip/db_dev";
        ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(url);
        return clickHouseDataSource;
    }
}

写入模拟数据（测试数据插入）

代码语言：javascript复制

public void mocData() {
        Calendar instance = Calendar.getInstance();
        instance.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));
        instance.set(2020, 0, 1, 0, 0, 0);

        while (instance.get(Calendar.YEAR) < 2021) {
            instance.add(Calendar.MINUTE, 5);
            try (ClickHouseStatement statement = dataSource.getConnection().createStatement()) {
                statement.write().send("insert into db_dev.t_dev_lp", new ClickHouseStreamCallback() {
                    @Override
                    public void writeTo(ClickHouseRowBinaryStream rowStream) throws IOException {
                        for (int i = 0; i < 4000;   i) {
                            String devName = "dev"   i;
                            double load = new Random().nextDouble() * 10;
                            Date time = instance.getTime();
                            rowStream.writeString(devName);
                            rowStream.writeUInt64(i);
                            rowStream.writeFloat32((float) load);
                            rowStream.writeDateTime(time);
                        }
                        System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(instance.getTime()));
                    }
                }, ClickHouseFormat.RowBinary);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

查看数据总量 4亿条数据

使用JDBC查询

代码语言：javascript复制

    public void querytest(){
        long t1 = System.currentTimeMillis();
        String sql = "select dev_name, dt from db_dev.t_dev_lp where dev_id = 10 and dt BETWEEN '2020-01-01' and '2020-03-01'";
        try (ClickHouseStatement statement = dataSource.getConnection().createStatement()) {
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println(resultSet.getString(1));
                System.out.println(resultSet.getString(2));
                resultSet.next();
            }
            System.out.println(resultSet.getRow());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        System.out.println(System.currentTimeMillis() - t1   "ms");
    }

// 输出
// 17280
// 585ms

查询耗时0.5s

使用Tabix查询（使用的是http协议）