[ClickHouse] 基本操作 +部分性能优化

用DBeaver 不太好操作 ClickHouse 的时候可以用这个

为某个字段添加注释

ALTER TABLE 表名  MODIFY  COLUMN  字段名 COMMENT '注释';

添加某个字段

ALTER TABLE 表名 ADD COLUMN 字段名 String COMMENT '注释';

---

Spring 连接 clickHouse

POM 中添加

        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.1.53</version>
            <exclusions>
                <exclusion>
                    <groupId>com.google.guava</groupId>
                    <artifactId>guava</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

YML中添加

driver-class-name: ru.yandex.clickhouse.ClickHouseDriver

---

性能优化

1. 用 uniqCombined 代替 count( DISTINCT xxxxx）
   因为 count DISTINCT 非常占用 clickhouse 性能

原:
count( DISTINCT to_char( createtime, 'yyyy-mm-dd' ) )
优:
uniqCombined(toYYYYMMDD(CREATETIME))

---

MySql 转 ClickHouse语句 (基于 ReplacingMergeTree 索引)

public class MysqlToClickHouse {

    public static String changeMysqlTableToClickHouse(String tableName) {
        String tables = tableName;
        String[] rows = tables.split("\n");
        String replaceTables = "";
        int i = 0;
        for (String row : rows) {
            if (row.contains("KEY")) {
                continue;
            }
            if (row.contains(") ENGINE=InnoDB")) {
                row = ") ENGINE = ReplacingMergeTree";
            }
            String changeRow = row.replaceAll(" NOT NULL", "")
                    .replaceAll("AUTO_INCREMENT", "")
                    .replaceAll("CHARACTER SET utf8mb4", "")
                    .replaceAll("CHARACTER SET utf8", "")
                    .replaceAll("ON UPDATE CURRENT_TIMESTAMP", "")
                    .replaceAll("CURRENT_TIMESTAMP", "")
                    .replaceAll("datetime DEFAULT NULL", " DateTime ")
                    .replaceAll(" datetime ", " DateTime ");
            changeRow = changeRow.replaceAll("varchar\\(\\d+\\) DEFAULT NULL", "Nullable(String)");
            changeRow = changeRow.replaceAll("varchar\\(\\d+\\)", "String");
            changeRow = changeRow.replaceAll("text", "String");
            String[] changeColumns = changeRow.split("[ ]");
// System.out.println(changeRow);
            if (changeColumns[3].contains("int") || changeColumns[3].contains("bigint")) {
                int length = Integer.parseInt(changeColumns[3]
                        .replaceAll("bigint", "")

                        .replaceAll("tinyint", "")

                        .replaceAll("int", "")

                        .replaceAll("\\(", "")

                        .replaceAll("\\)", ""));

                String type = changeColumns[3].contains("bigint") ? "bigint" : "int";

                if (length < 3) {

                    changeRow = changeRow

                            .replaceFirst(type + "\\(" + length + "\\)", "Int8");

                } else if (length < 5) {

                    changeRow = changeRow

                            .replaceFirst(type + "\\(" + length + "\\)", "Int16");

                } else if (length <= 9) {

                    changeRow = changeRow

                            .replaceFirst(type + "\\(" + length + "\\)", "Int32");

                } else {

                    changeRow = changeRow

                            .replaceFirst(type + "\\(" + length + "\\)", "Int64");

                }

            }

            replaceTables += changeRow;

            i++;

        }

        if (replaceTables.contains(",) ENGINE = ReplacingMergeTree")) {

            String temp = replaceTables.substring(0, replaceTables.indexOf(",) ENGINE = ReplacingMergeTree"));

            replaceTables = temp + ") ENGINE = ReplacingMergeTree ";

        }

        replaceTables.replaceAll("CREATE TABLE `" + tableName + "`", tableName + "_local");

        return replaceTables;

    }

    public static void main(String[] args) {

//这里是用脚本跑出来后粘上去的。show create table XXX表名;

//用mybatis连接数据库查询出来建表语句比较好

        String createTable = "
		/**
			此处填MYSQL 建表语句
		   MYSQL 语句 从NAVICAT 建表语句中复制
		/**
		
		";

        String res = changeMysqlTableToClickHouse(createTable);
        System.out.println("转换后的建表语句为：" + res );

    }

}