ClickHouse学习笔记（使用场景，原理解析，环境搭建，数据迁移和同步，SQL语法）

我们的客户中台项目用到了一个用户大宽表，实时对客户数据进行多维度分析。之前一直用的Mysql，随着数据量越来越大，现在想使用ClickHouse进行优化是最佳的选择

ClickHouse和Mysql很像，也是数据库，常用于数据分析OLAP（On-Line Analytical Processing，联机分析处理）领域。传统数据库是OLTP（On-Line Transaction Processing，联机事务处理）

使用场景

1.读多于写
通常将数据批量导入后，进行任意维度的灵活探索、BI工具洞察、报表制作等。数据一次性写入后，分析师需要尝试从各个角度对数据做挖掘、分析，直到发现其中的商业价值、业务变化趋势等信息。这是一个需要反复试错、不断调整、持续优化的过程，其中数据的读取次数远多于写入次数。

2.大宽表，读大量行但是少量列，结果集较小
在OLAP场景中，通常存在一张或是几张多列的大宽表，列数高达数百甚至数千列。对数据分析处理时，选择其中的少数几列作为维度列、其他少数几列作为指标列，然后对全表或某一个较大范围内的数据做聚合计算。这个过程会扫描大量的行数据，但是只用到了其中的少数列。而聚合计算的结果集相比于动辄数十亿的原始数据，也明显小得多。

3.无需事务，数据一致性要求低
OLAP类业务对于事务需求较少，通常是导入历史日志数据，或搭配一款事务型数据库并实时从事务型数据库中进行数据同步。其实多数OLAP系统都支持最终一致性。

大厂常用：苏宁超6亿会员如何做到秒级用户画像查询？

原理解析

这里列举的很详细：ClickHouse深度揭秘。包括列式存储，索引，数据分区/分片，TTL，主从同步等

这篇更详细地介绍了ClickhHouse的架构：为什么ClickHouse这么快？

行存储和列存储区别，一行取所有列，速度慢

列存储，直接取，忽略无用字段，很快

环境搭建

1.本地搭建

作为例子，通过docker起一个clickhouse容器，先拉镜像

docker pull yandex/clickhouse-server

启动容器，ck的默认端口是8123

通过cli进入，输入# clickhouse-client -h 127.0.0.1进入

idea也支持连接ck，可以看这篇：intellij idea如何连接ClickHouse，详细教程

输入简单的sql，结果返回成功

2.使用JDBC操作ClickHouse

引入maven依赖

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

代码，就是简单的jdbc使用：

@RunWith(SpringRunner.class)
@SpringBootTest
public class ClickhouseWelcomeTest {
    private static final String[] GREETINGS = {
            "Welcome to DevStudio",
            "碧油鸡全部退散, 颈腰椎早日康复! 贼真诚",
            "有对象了么? 别慌, 送你一个! 领取请加钉钉群: 35991139", "404?!! 不要慌，不要急，App Observer 帮助您~ 了解一下", "Cosy 提效补全用过没, 还能搜搜搜",
            "代码平台哪家强, Codeup! Codeup!! Codeup!!!"
    };

    @Test
    public void welcome() throws ClassNotFoundException {
        Random random = new Random();
        int position = random.nextInt(GREETINGS.length);
        String url = "172.27.31.10";
        String username = "";
        String password = "";
        Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
        String connectionStr = "jdbc:clickhouse://" + url + ":8123";

        try {
            Connection connection = DriverManager.getConnection(connectionStr);//, username, password
            Statement stmt = connection.createStatement();
            // 创建local表
            String createLocalTableDDL = "CREATE TABLE IF NOT EXISTS whatever_table ON CLUSTER default " +
                    "(user_id UInt64, " +
                    "city_level SimpleAggregateFunction(anyLast, Nullable(Enum('一线城市' = 0, '二线城市' = 1, '三线城市' = 2, '四线城市' = 3))), " + "gender SimpleAggregateFunction(anyLast, Nullable(Enum('女' = 0, '男' = 1)))," +
                    "interest_sports SimpleAggregateFunction(anyLast, Nullable(E    num('否' = 0, '是' = 1)))," +
                    "reg_date SimpleAggregateFunction(anyLast, Datetime)) " +
                    "comment_like_cnt SimpleAggregateFunction(anyLast, Nullable(UInt32)),\n" +
                    "last30d_share_cnt SimpleAggregateFunction(anyLast, Nullable(UInt32)),\n" +
                    "user_like_consume_trend_type SimpleAggregateFunction(anyLast, Nullable(String)),\n" +
                    "province SimpleAggregateFunction(anyLast, Nullable(String)),\n" +
                    "last_access_version SimpleAggregateFunction(anyLast, Nullable(String)),\n" +
                    "others SimpleAggregateFunction(anyLast, Array(String)),\n" +
                    "ENGINE = AggregatingMergeTree() PARTITION by toYYYYMM(reg_date) ORDER BY user_id;";
            stmt.execute(createLocalTableDDL);
            System.out.println("create local table done.");
            // 创建distributed表
            String createDistributedTableDDL = "CREATE TABLE IF NOT EXISTS whatever_table_dist ON cluster default " + "AS default.whatever_table " +
                    "ENGINE = Distributed(default, default, whatever_table, intHash64(user_id));";
            stmt.execute(createDistributedTableDDL);
            System.out.println("create distributed table done");
            // 插入mock数据
            String insertSQL = "INSERT INTO whatever_table(\n" +
                    "\tuser_id,\n" +
                    "\tcity_level,\n" +
                    "\tgender,\n" +
                    "\tinterest_sports,\n" +
                    "\treg_date,\n" +
                    "\tcomment_like_cnt,\n" +
                    "\tlast30d_share_cnt,\n" +
                    "\tuser_like_consume_trend_type,\n" +
                    "\tprovince,\n" +
                    "\tlast_access_version,\n" +
                    "\tothers\n" +
                    "\t)SELECT\n" +
                    " number as user_id,\n" +
                    " toUInt32(rand(11)%4) as city_level,\n" +
                    " toUInt32(rand(30)%2) as gender,\n" +
                    " toUInt32(rand(28)%2) as interest_sports,\n" +
                    " (toDateTime('2020-01-01 00:00:00') + rand(1)%(3600*24*30*4)) as reg_date,\n" + " toUInt32(rand(15)%10) as comment_like_cnt,\n" +
                    " toUInt32(rand(16)%10) as last30d_share_cnt,\n" +
                    "randomPrintableASCII(64) as user_like_consume_trend_type,\n" + "randomPrintableASCII(64) as province,\n" +
                    "randomPrintableASCII(64) as last_access_version,\n" + "[randomPrintableASCII(64)] as others\n" +
                    " FROM numbers(100000);\n";
            stmt.execute(insertSQL);
            System.out.println("Mock data and insert done.");
            System.out.println("Select count(user_id)...");
            ResultSet rs = stmt.executeQuery("select count(user_id) from whatever_table_dist");
            while (rs.next()) {
                int count = rs.getInt(1);
                System.out.println("user_id count: " + count);
            }
            // 数据合并
            String optimizeSQL = "OPTIMIZE table whatever_table final;";
            // 如数据合并时间过⻓，可在partition级别并行执行
            String optimizeByPartitionSQL = "OPTIMIZE table whatever_table PARTITION 202001 final;";
            try {
                stmt.execute(optimizeByPartitionSQL);
            }catch (SQLTimeoutException e){
            // 查看merge进展
            // String checkMergeSQL = "select * from system.merges where database = 'default' and table = 'whatever_table';"; Thread.sleep(60*1000);
            }
            // 人群圈选(city_level='一线城市'，gender='男性'，interest_sports='是', reg_date<='2020-01-31 23:59:59')
            String selectSQL = "SELECT user_id from whatever_table_dist where city_level=0 and gender=1 and interest_sports=1 and reg_date <= NOW();";
            rs = stmt.executeQuery(selectSQL);
            while (rs.next()) {
                int user_id = rs.getInt(1);
                System.out.println("Got suitable user: " + user_id);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println(GREETINGS[position]);
    }

}

3.阿里云

产品首页：云数据库 ClickHouse
直接丢上SDK：云数据库 ClickHouse 版
调试：阿里云OpenAPI开发门户

数据迁移和同步

因为项目中要将mysql的大宽表数据同步到clickhouse，这一步很重要。我们用的是datax，b站的使用教程：tis-datax-mysql-clickhouse

阿里云直接导：从RDS MySQL导入/同步

SQL语法

直接上官方文档：https://clickhouse.com/docs/zh/sql-reference/syntax/