用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
性能优化
- 用 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 );
}
}