本文正在参加「技术专题19期 漫谈数据库技术」活动
写本地表还是分布式表
在使用ClickHouse的过程中，我们都知道针对数据的写入需要写本地表，对数据的查询则是分布式表。这里简单介绍一下为什么对数据的写入选择本地表。
首先来看一下本地表和分布式表的创建：
本地表创建:
CREATE TABLE IF NOT EXISTS test on cluster default
(
id Int64 ,
user_id Int64 ,
name String DEFAULT '',
pt String
)ENGINE = ReplicatedMergeTree('/clickhouse/tables/db/test/{shard}', '{replica}')
PARTITION BY (pt)
ORDER BY id
分布式表
CREATE TABLE IF NOT EXISTS test_all on cluster default
AS db.test
ENGINE = Distributed(default, db,test, rand())
分布式表是分片上本地表的逻辑视图，其本身不进行数据的存储，但是提供了查询和写入功能。通过建表可以看到分布式表指定了ENGINE = Distributed引擎，并且可以指定一个sharding参数：分片的key，可以设置字段名或者rand()。通过这个参数可以做到写入时控制数据均衡存储。这种方式虽然简单但是不推荐使用，因为基于分布式表的数据写入需要先在分布式表所在节点落盘，压力会集中在分布式表所在节点。如果分布式表节点宕机，数据可能会丢失，同时对ZK也会有一定的压力。
本地表代表了分布式表引擎指定的每个分片上的表名称。它可以将写入压力分散到每个本地表所在的分片节点，并且支持数据原子写入和去重，保证数据一致性。但是需要应用保证写入的负载均衡。
多种策略保证数据均衡地写入本地表
选择了基于本地表的写入，就可以在应用中定制多种数据写入的策略。
常见的负载均衡策略如轮训，随机，指定分片Key(比如根据用户ID,将相同用户的数据写入到一个分片中)。下面我们来看看具体如何在应用代码中实现
1-获取分片对应的节点信息
获取分片对应的节点信息用于JDBC连接.
我们可以通过查询ClickHouse的系统表来获取这些信息,可以参考系统表的介绍。
select shard_num, host_name,host_address from system.clusters
where cluster = 'cluster'
replicated │ 1 │ chi-repl-05-replicated-0-0 │ 127.0.0.1 │
│ replicated │ 1 │ chi-repl-05-replicated-0-1 │ 10.54.91.30 │
shard_num从1开始，因为可能有副本，副本的shard_num是一样的，可以修改SQL获取每个分片所有副本的host_address
select shard_num, groupArray(host_address) as hosts from system.clusters
where cluster = 'cluster' group by shard_num
2-代码实现
//获取每个分片对应的集群和连接信息
Map<Integer, Shard> shardMap = new HashMap<>();
ResultSet rs = //TODO 省略读取数据库信息;
while (rs.next()) {
int shardNum = rs.getInt(1);
String[] hostAddress = (String[]) rs.getArray(2).getArray();
shardMap.put(shardNum, new Shard("cluster",shardNum,hostAddress));
}
3-基于Spark写入
我们是使用Spark来进行的数据写入的，这里介绍一下如何基于Spark写入。一般来说基于Spark做数据写入的时候我们都会基于如下方式来按分区写入，每个分区获取一次连接，然后写入数据。
data.foreachPartition(iter => {
val partitionId = TaskContext.getPartitionId()//获取当前分区ID
//获取数据库连接
val conn = getConn();
val statement=conn.prepareStatement(this.insertSql)
//数据写入
for (item <- iter) {
statement.addBatch()
if (length >= bulkSize) {
statement.executeBatch()
}
}
statement.executeBatch()
})
如果想要实现随机或者轮训的策略，只需要在获取连接的时候从shardMap中轮训或者随机获取对应的shard信息。shardMap中的Key代表了分片，value则是当前分片的集群信息以及所有副本的连接信息。
1-随机
int index = new Random().nextInt(shardMap.size())+1;
Shard shard = shardMap.get(index);
//TODO 获取连接
2-轮训
需要先获取到foreachPartition中的分区ID,基于当前分区ID来进行轮训
val partitionId = TaskContext.getPartitionId()//获取当前分区ID
partitionId % shardMap.size + 1
Shard shard = shardMap.get(index);
//TODO 获取连接
上面的方式是按分区来获取对应分片的连接信息，如果分区数据不均衡，就需要考虑使用repartition算子来重分区。
3-如何可以将相同key的数据写入到一个分区
spark中提供了相关算子,如repartition,repartitionByRange都可以指定列
val repartitionNum = config.getInt("repartition")
data.repartition(repartitionNum, col("shardkey")).rdd.foreachPartition(iter => {
val partitionId = TaskContext.get.partitionId
//TODO 获取当前分片对应的连接信息
iter .foreach(info => {
//处理数据
})
})
4-按key进行hash写入
这里我们脱离Spark来看看Seatunnel中v2插件是如何来实现的.
首先也是维护分片连接信息的map
this.statementMap = initStatementMap();
每条数据写入的方法
public void write(SeaTunnelRow element) throws IOException {
Object shardKey = null;
//这里获取shardKey的值
if (StringUtils.isNotEmpty(this.option.getShardMetadata().getShardKey())) {
int i = this.option.getSeaTunnelRowType().indexOf(this.option.getShardMetadata().getShardKey());
shardKey = element.getField(i);
}
//shardRouter.getShard 来路由当前数据属于哪个分片连接信息
ClickhouseBatchStatement statement = statementMap.get(shardRouter.getShard(shardKey));
PreparedStatement clickHouseStatement = statement.getPreparedStatement();
IntHolder sizeHolder = statement.getIntHolder();
// add into batch
addIntoBatch(element, clickHouseStatement);
sizeHolder.setValue(sizeHolder.getValue() + 1);
// flush batch
if (sizeHolder.getValue() >= option.getBulkSize()) {
flush(clickHouseStatement);
sizeHolder.setValue(0);
}
}
路由方法
public Shard getShard(Object shardValue) {
if (!splitMode) {
return shards.firstEntry().getValue();
}
if (StringUtils.isEmpty(shardKey) || shardValue == null) {
return shards.lowerEntry(threadLocalRandom.nextInt(shardWeightCount + 1)).getValue();
}
int offset = (int) (HASH_INSTANCE.hash(ByteBuffer.wrap(shardValue.toString().getBytes(StandardCharsets.UTF_8)),
0) & Long.MAX_VALUE % shardWeightCount);
return shards.lowerEntry(offset + 1).getValue();
}
基于clickhouse-local导入数据到本地表
1-介绍
在阅读seatunnel对clickhouse写入的代码中发现,其提供了一个ClickHouseFile的插件，发现如果想进一步提升导入的性能，减少对服务器的压力我们可以尝试使用clickhouse-local来帮我们进行数据的导入。
对于clickhouse-local的介绍可以直接去官网进行学习。
clickhouse.com/docs/zh/ope…
2-基础命令使用
在clickhouse-local的帮助命令提示了使用方法:
ps aux | tail -n +2 | awk '{ printf("%s\t%s\n", $1, $4) }' | clickhouse-local -S "user String, mem Float64" -q "SELECT user, round(sum(mem), 2) as mem_total FROM table GROUP BY user ORDER BY mem_total DESC FORMAT PrettyCompact"
┌─user─────┬─mem_total─┐
│ clickho+ │ 5.3 │
│ root │ 0.6 │
-S指定了结构，-N指定了表名
默认是\t分隔符:
echo -e "1\t小李\n2\t小明\n3\t小路"|clickhouse-local -S "id Int64,name String" -N "tmp_table" -q "SELECT * FROM tmp_table;"
1 小李
2 小明
3 小路
在上面都是基于echo命令来生成数据，在实际使用中可以替换成文件，clickhouse-local 可以指定--file指定文件。这样就我们只需要将数据写入到本地文件，就可以实现数据的导入了。
3-完整功能测试
表和基础数据准备
//测试表准备
CREATE TABLE test_local ( id Int64, pt Date) ENGINE = MergeTree()PARTITION BY toYYYYMM(pt)ORDER BY id
//数据模拟写入
INSERT INTO TABLE test_local
SELECT number,'2022-11-04' FROM `system`.numbers LIMIT 10000
//查询
select count(1) from test_local
SELECT count(1)
FROM test_local
Query id: f94777ad-0c53-4bc4-ba39-0b48a61336cb
┌─count()─┐
│ 10000 │
└─────────┘
//查询分区情况
SELECT
partition,
name,
rows
FROM system.parts
WHERE table = 'test_local'
Query id: 064be019-e0fe-4a7f-a8c1-5b2cac993a60
┌─partition─┬─name─────────┬──rows─┐
│ 202211 │ 202211_1_1_0 │ 10000 │
└───────────┴──────────────┴───────┘
数据写入本地文件
首先需要准备一个文本文件路径，用于存储ClickHouse可以使用的本地数据
mkdir -p /clickhouse/local-data
验证一下数据写入的格式
echo -e "1\t2022-11-05\n2\t2022-11-05\n3\t2022-11-05"|clickhouse-local -S "id Int64,pt String" -N "tmp_table" -q "CREATE TABLE test_local (id Int64,pt Date) ENGINE = MergeTree() PARTITION BY toYYYYMM(pt) ORDER BY id;SELECT id,pt FROM tmp_table;"1 2022-11-052 2022-11-053 2022-11-05
数据写入到上面创建的本地路径，注意SQL修改为了insert，并且增加了-- --path参数
--path参数
echo -e "1\t2022-11-05\n2\t2022-11-05\n3\t2022-11-05"|clickhouse-local -S "id Int64,pt String" -N "tmp_table" -q "CREATE TABLE test_local (id Int64,pt Date) ENGINE = MergeTree() PARTITION BY toYYYYMM(pt) ORDER BY id;INSERT INTO TABLE test_local SELECT id,pt FROM tmp_table;" -- --path /clickhouse/local-data
查看数据的生成
pwd
/clickhouse/local-data/data/_local/test_local
root@chi-repl-05-replicated-0-0-0:/clickhouse/local-data/data/_local/test_local# ls -lrt
total 12
drwxr-xr-x 2 root root 4096 Nov 4 09:27 202211_1_1_0
-rw-r--r-- 1 root root 1 Nov 4 09:27 format_version.txt
drwxr-xr-x 2 root root 4096 Nov 4 09:27 detached
如何将数据真正导入到ClickHouse中
找到ClickHouse真实的存储路径,我的表是default库下的test_local: /var/lib/clickhouse/data/default/test_local。
mv操作
mv /clickhouse/local-data/data/_local/test_local/202211_1_1_0/ /var/lib/clickhouse/data/default/test_local/detached
ALTER TABLE test_local ATTACH PART '202211_1_1_0'
ALTER TABLE test_local
ATTACH PART '202211_1_1_0'
Query id: 34845ff3-0bcd-4a8a-a0a3-3f79e38be362
Ok.
验证数据
SELECT
partition,
name,
rows
FROM system.parts
WHERE table = 'test_local'
Query id: 34b2a169-21ec-4084-a22f-26ef4ce4cf5d
┌─partition─┬─name─────────┬──rows─┐
│ 202211 │ 202211_1_1_0 │ 10000 │
│ 202211 │ 202211_3_3_0 │ 3 │
└───────────┴──────────────┴───────┘
select count(1) from test_local
SELECT count(1)
FROM test_local
Query id: 73ae7617-b323-47bf-bb39-332db1b7e278
┌─count()─┐
│ 10003 │
└─────────┘
最终可以发现数据已经成功导入到ClickHouse中了。整个过程可以发现都是对文件的操作，对ClickHouse服务本身压力都会很小。
4-代码实现参考
对于代码如何实现大家可以参考Seatunnel的源码。贴出部分代码(不同版本可能会不同)
private def generateClickhouseFile(rows: Iterator[(Shard, Row)]): List[String] = {
def getValue(kv: util.Map.Entry[String, String]): String = {
if (this.fields.contains(kv.getKey)) {
kv.getKey
} else {
val v = getDefaultValue(kv.getValue)
if (v == null) {
"NULL"
} else if (v.isInstanceOf[Integer]) {
"0"
} else {
s"'${v.toString}'"
}
}
}
val uuid = UUID.randomUUID().toString.substring(0, UUID_LENGTH).replaceAll("-", "_")
val targetPath = java.lang.String.format("%s/%s", CLICKHOUSE_FILE_PREFIX, uuid)
val target = new File(targetPath)
target.mkdirs()
val tmpDataPath = targetPath + "/local_data.log"
mmapSaveDataSafely(tmpDataPath, rows.map(r => r._2))
val exec = mutable.ListBuffer[String]()
exec.appendAll(clickhouseLocalPath.trim.split(" "))
exec.append("-S")
exec.append(fields.map(f => s"$f ${this.table.tableSchema.get(f)}").mkString(","))
exec.append("-N")
exec.append("temp_table" + uuid)
exec.append("-q")
exec.append(java.lang.String.format("%s; INSERT INTO TABLE %s SELECT %s FROM temp_table%s;", this.table.getCreateDDLNoDatabase
.replaceAll("`", ""), this.table.getLocalTableName,
this.table.tableSchema.entrySet.map(getValue).mkString(","), uuid))
exec.append("--path")
exec.append(targetPath)
val command = Process(Seq("less", tmpDataPath)) #| exec
LOGGER.info(command.lineStream.mkString("\n"))
new File(targetPath + "/data/_local/" + this.table.getLocalTableName).listFiles().filter(f => f.isDirectory).
filterNot(f => f.getName.equals("detached")).map(f => f.getAbsolutePath).toList
}
总结
本文主要总结了关于ClickHouse的数据导入方式，我们在使用过程中也借鉴了开源框架的实现，也是做了一些简单的总结分享给大家。
本文正在参加「技术专题19期 漫谈数据库技术」活动
