创建表
CREATE TABLE test ON CLUSTER sharding_simple (clicensetype String,ccarnumber String,dcoollectiondate DateTime,caddresscode String,ccollectionaddress String,ccollectionagencies String...) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(dcollectiondate) ORDER BY (dcollectiondate,cdevicecode,ccarnumber);
CREATE TABLE test_all ON CLUSTER sharding_simple (clicensetype String,ccarnumber String,dcollectiondate DateTime,caddresscode String,ccollectionaddress String,ccollectionagencies String...)ENGINE=Distributed(sharding_simple,default,test,rand());
插入数据
String url="jdbc:clickhouse://node1:8123,node2:8123,node3:8123,node4:8123,node5:8123/default";
String user = "default";
//设置JDBC参数
ClickHouseProperties clickHouseProperties = new ClickHouseProperties();
clickHouseProperties.setUser(user);
//声明数据源
BalancedClickhouseDataSource balanced = new BalancedClickhouseDataSource(url, clickHouseProperties);
//对每个host进行ping操作, 排除不可用的dead连接
balanced.actualize();
//获得JDBC连接
Connection conn = balanced.getConnection();
Statement stmt = conn.createStatement();
String sql = "insert into test_all values(...),(...)";
stmt.execute(sql)
注意:插入数据是插入到distributed表中,实现负载均衡;并且每次可以选择插入数据量大一点,因为clickhouse支持高吞吐,我这边是一次插入5000条数据