💝💝💝欢迎来到我的博客,很高兴能够在这里和您见面!希望您在这里可以感受到一份轻松愉快的氛围,不仅可以获得有趣的内容和知识,也可以畅所欲言、分享您的想法和见解。
- 推荐:kuan 的首页,持续学习,不断总结,共同进步,活到老学到老
- 导航
- 檀越剑指大厂系列:全面总结 java 核心技术点,如集合,jvm,并发编程 redis,kafka,Spring,微服务,Netty 等
- 常用开发工具系列:罗列常用的开发工具,如 IDEA,Mac,Alfred,electerm,Git,typora,apifox 等
- 数据库系列:详细总结了常用数据库 mysql 技术点,以及工作中遇到的 mysql 问题等
- 懒人运维系列:总结好用的命令,解放双手不香吗?能用一个命令完成绝不用两个操作
- 数据结构与算法系列:总结数据结构和算法,不同类型针对性训练,提升编程思维,剑指大厂
非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝 ✨✨ 欢迎订阅本专栏 ✨✨
博客目录
- 一.新增操作
- 库相关
- 1.创建库的 sql
- 表相关
- 1.创建表 sql
- 2.通过 select 建表
- 3.clickhouse 多个 order
- 4.创建物化视图
- 字段相关
- 1.插入语句
- 2.新增列
- 二.查询 sql
- 1.查询年月
- 2.计数 sql
- 3.排序函数
- 4.日期处理
- 三.编辑操作
- 1.修改字段 sql
- 2.clickhouse 修改字段名
- 3.clickhosue 复制数据
- 4.修改数据类型
- 5.修改备注
- 6.修改表名
- 四.删除相关
- 1.删除库
- 2.删除表
- 3.删除数据
- 4.清空数据
- 1.通过筛选条件
- 2.通过 TRUNCATE
- 五.系统 sql
- 1.查询表变更
- 2.查询执行计划
- 3.clickhouse 支持的函数
- 4.查询是否开启开窗函数
- 5.查看版本号
- 6.大于 50g 不能删除
- 7.查询表行数和数据量
- 8.使用 clickhouse 注意事项
- 9.查询 ck 下的所有数据库
- 10.查看异步删除是否完成
- 六.java 代码
- 1.clickhouse 分页
- 2.clickhouse 建表
- 七.create table
- 1、普通建表:
- 2、分布表:
- 3、复制已有的一个表创建表。如果不指定 engine,默认会复制源表 engine。
- 4、复制已有的一个表创建表。在集群上执行,要把 on cluster 写在 as 前面。
- 5、使用 select 查询结果来创建一个表,需要指定 engine。字段列表会使用查询结果的字段列表。
- 6、最后,在分布表之上再创建分布表可以吗?
- 八.修改默认数据目录和 log 目录
- 创建数据文件夹
- 安装时生成的默认文件迁移
- 进入 / var/lib 下建立链接
- 九.建表与插入数据
- 1.建表
- 2.插入数据
- 3.clickhouse 使用代码操作增删改查
一.新增操作
库相关
1.创建库的 sql
CREATE DATABASE IF NOT EXISTS chtest; --使用默认库引擎创建库
表相关
1.创建表 sql
CREATE TABLE default.boss
(
row_id String,
user_id Int32
) ENGINE = MergeTree() ORDER BY
(row_id) SETTINGS index_granularity = 16384
2.通过 select 建表
create table t_name_8888
ENGINE = MergeTree
ORDER BY
tuple()
SETTINGS index_granularity = 8192
as
select
*
from
dw_1_sad limit 0,1
3.clickhouse 多个 order
CREATE TABLE bi.boss_info2
(
row_id String,
user_id Int32,
offline_props_time String,
offline_vip_distribute String,
offline_vip_time String,
pay_now String,
data_dt Date
) ENGINE = MergeTree() PARTITION BY data_dt ORDER BY
(industry, l1_name, l2_name, l3_name, job_city, job_area)
SETTINGS index_granularity = 16384
4.创建物化视图
create materialized view views.o6
engine = MergeTree
order by period_sdate
POPULATE
as
select xxxx
字段相关
1.插入语句
INSERT INTO
default.sales_w (`suppkey`, `brand`, `AA`, `AB`, `AC`, `AD`)
VALUES
(1, 'nike', 99, 98, 97, 96);
INSERT INTO
default.sales_w (`suppkey`, `brand`, `AA`, `AB`, `AC`, `AD`)
VALUES
(2, 'nike', 99, 98, 97, 96);
INSERT INTO
default.sales_w (`suppkey`, `brand`, `AA`, `AB`, `AC`, `AD`)
VALUES
(3, 'nike', 99, 98, 97, 96);
2.新增列
ALTER TABLE `default`.belle_out ADD COLUMN product_year_name Nullable(String);
ALTER TABLE `default`.belle_out ADD COLUMN season_name Nullable(String);
二.查询 sql
1.查询年月
--查询年月
SELECT
DISTINCT year(period_sdate) as years,
month(period_sdate) as months
from
tmp_dws_day_org_pro_size_inv_ds_r1
order by
years,
months
2.计数 sql
SELECT
COUNT(1)
from
default.tmp_dws_day_org_pro_size_inv_ds_r1
WHERE
period_sdate >= '2019-03-01'
and period_sdate <= '2019-03-31';
3.排序函数
SELECT
rowNumberInAllBlocks()+ 1 AS total_SAL_rank
FROM
(
SELECT
o2.PERIOD_SDATE,
o2.total_SAL_QTY
FROM
o2
ORDER BY o2.total_SAL_QTY DESC
LIMIT 1 BY o2.PERIOD_SDATE
)
4.日期处理
将 int 类型转为 date 类型
parseDateTimeBestEffort(toString(20191201000407)) as wet
三.编辑操作
1.修改字段 sql
ALTER TABLE qac RENAME COLUMN provId TO `爽`
2.clickhouse 修改字段名
ALTER TABLE test_8 RENAME COLUMN teacher_name TO class_teacher_name;
3.clickhosue 复制数据
create table newtest as test;#先创建表
insert into newtest select * from test;#再插入数据
4.修改数据类型
ALTER table default.dws_day_mgmt_pro_sal_ds MODIFY COLUMN period_sdate Date COMMENT '日期(yyyy-mm-dd)'
5.修改备注
ALTER table default.dim_pro_allinfo modify COMMENT 'dim_商品信息表【商品】'
6.修改表名
RENAME TABLE back.dsd_back TO back.dsd_back_01;
四.删除相关
1.删除库
drop database IF EXISTS base_db
2.删除表
DROP table if EXISTS `default`.`test`
3.删除数据
alter table
default.tmp_dws_day_org_pro_size_inv_ds_r1
delete
where
period_sdate >= '2020-03-01'
and period_sdate <= '2020-12-31'
4.清空数据
1.通过筛选条件
-- ck清空表
ALTER table tmp.dim_pro_allinfo_bak delete where 1=1
2.通过 TRUNCATE
TRUNCATE TABLE dbname.table
五.系统 sql
1.查询表变更
SELECT database,table,command,create_time,is_done FROM system.mutations
WHERE table = 'tmp_dws_day_org_pro_inv_ds2_r4' LIMIT 10
2.查询执行计划
select * from system.query_log
WHERE query_kind ='Alter'
order by query_start_time desc
3.clickhouse 支持的函数
SETTINGS join_algorithm ='prefer_partial_merge';
4.查询是否开启开窗函数
SELECT * from `system`.settings s where name = 'allow_experimental_window_functions'
- allow_experimental_window_functions=1 代表开启
- allow_experimental_window_functions=0 未开启
5.查看版本号
SELECT version()
6.大于 50g 不能删除
sudo touch /data/clickhouse/flags/force_drop_table && sudo chmod 666 /data/clickhouse/flags/force_drop_table
7.查询表行数和数据量
SELECT database, name, total_rows, round(total_bytes / 1024 / 1024 / 1024, 4) as total_memory
from system.tables t
where t.database != 'system'
order by t.database, t.name
8.使用 clickhouse 注意事项
- 严格区分大小写,注意库名和字段的大小写
- 子查询的查询结果需要加 as 别名
- 不支持 ndv 函数,支持使用 count(disticnt column)
- 使用方法,如果设置如下参数:
- set APPX_COUNT_DISTINCT=true;
- 则所有的 count(distinct col)会在底层计算的时候转成 ndv() 函数,也就是说,在 sql 中可以直接使用 count(distinct col),如果不配置上述参数,则在 sql 中直接写 ndv(col) 也可以
- ifnull 函数在 null 时需要有默认值 ifnull(ddopsd.all_sal_act_qty,0)
- 查询条件字段最好加上’'单引号,避免类型不匹配
- 涉及到计算函数的字段,字段类型必须是 Number 类型,不能是 String
- 超过 50g 的表不能直接删除,需要添加一个空文件 sudo touch /data/clickhouse/flags/force_drop_table && sudo chmod 666 /data/clickhouse/flags/force_drop_table
- 创建表的 order by 会影响查询效率(相当于 mysql 的索引)
- left join 尽量使用 any left join
- 存在 null 值的字段不能指定为 order by 索引
- clickhouse 的字段是 Int32 时,插入数据不能为 null
- null 值不能转化为 Int32 类型,会报错
- clickhouse 在 21.3.1 以后的版本支持开窗函数
- clickhouse 的字段是 Int32 时,插入数据不能为 null
- 空值问题
- 空表,Nullable 与非空类型可以互转;
- Nullable 字段,如果记录不带有 Null 值,可以从 Nullable 转成非空类型;
- 含有 null 值的字段不允许转成非空类型;
- Nullable 字段不允许用于 order by;
9.查询 ck 下的所有数据库
SELECT * from system.databases d ;
10.查看异步删除是否完成
SELECT
database,
table,
command,
create_time,
is_done
FROM system.mutations
order by create_time DESC
LIMIT 10
六.java 代码
1.clickhouse 分页
@Override
public PreviewData preview(Integer pageSize, Integer pageNo) {
DdlNode ddlNode = currentNode();
//输出节点运行直接写数据到目标表||非输出节点运行就是预览数据
if (NodeTypeEnum.OUT.name().equalsIgnoreCase(ddlNode.getType())) {
log("空,输出节点没有预览");
return PreviewData.builder().build();
} else {
this.checkNode();
PlainSelect plainSelect = this.sql();
//原始sql
String originalSql = plainSelect.toString();
String countSqlFormat = "select count(1) as count from (%s) ";
//计算总行数sql
String countSql = String.format(countSqlFormat, originalSql);
Integer readNum = mutableGraph().getReadNum();
readNum = readNum == null ? 1000 : readNum;
//计算起始行
int start = (pageNo - 1) * pageSize;
String preSql = originalSql;
//start 是起始行
//pageSize 是偏移量
if (!NodeTypeEnum.AGG.name().equalsIgnoreCase(ddlNode.getType())) {
preSql = preSql + " limit " + start + "," + pageSize;
} else {
preSql = "select * from (" + preSql + ") limit " + start + "," + pageSize;
}
List<DdlColumn> ddlColumns = this.columns();
List<Map<String, Object>> maps = getDdlClickHouseJdbcService().queryForList(preSql);
PreviewData previewData = convert(ddlColumns, maps);
List<Map<String, Object>> maps1 = getDdlClickHouseJdbcService().queryForList(countSql);
if (!CollectionUtils.isEmpty(maps1)) {
BigInteger count = (BigInteger) (maps1.get(0).get("count"));
if (count == null) {
previewData.setTotal(0L);
} else {
previewData.setTotal(count.longValue() <= readNum ? count.longValue() : readNum);
}
}
previewData.setSql(originalSql);
return previewData;
}
}
2.clickhouse 建表
public Boolean createTable(CreateTableRequestDTO dto) {
StringBuilder createTableSql = new StringBuilder("create table ");
createTableSql.append("`")
.append(dto.getTableName())
.append("`")
.append("(");
List<CreateTableRequestDTO.Field> fields = dto.getFields();
String sql = fields.stream().map(t -> warpName(t.getFieldName()) + " " + "Nullable(" + t.getFieldType() + ")")
.collect(Collectors.joining(","));
createTableSql.append(sql).append(") engine = MergeTree() ORDER BY tuple()");
try {
jdbcTemplate.execute(createTableSql.toString());
} catch (DataAccessException e) {
throw new DdlException("创建表失败:" + e);
}
return true;
}
七.create table
1、普通建表:
CREATE TABLE dis_j.D_F1_shard on cluster cluster_demo (
`product_code` String,
`package_name` String
) ENGINE = MergeTree ORDER BY package_name SETTINGS index_granularity = 8192
2、分布表:
CREATE TABLE dis_j.D_F1_all on cluster cluster_demo as dis_j.D_F1_shard
ENGINE = Distributed('cluster_demo', 'dis_j', D_F1_shard, rand())
3、复制已有的一个表创建表。如果不指定 engine,默认会复制源表 engine。
CREATE TABLE dis_j.tmp1 as dis_j.D_F1_shard
4、复制已有的一个表创建表。在集群上执行,要把 on cluster 写在 as 前面。
CREATE TABLE dis_j.tmp1 on cluster cluster_demo as dis_j.D_F1_shard
5、使用 select 查询结果来创建一个表,需要指定 engine。字段列表会使用查询结果的字段列表。
CREATE TABLE dis_j.tmp1 ENGINE = MergeTree ORDER BY package_name AS select * from dis_j.D_F1_shard
6、最后,在分布表之上再创建分布表可以吗?
–在 ck 中创建表:
create table dis_j.t_area_shard on cluster cluster_demo
(
area_id String,
area_name String
)ENGINE = MergeTree ORDER BY area_id SETTINGS index_granularity = 8192
–分布表
CREATE TABLE dis_j.t_area_all on cluster cluster_demo as dis_jiakai.t_area_shard
ENGINE = Distributed('cluster_demo', 'dis_j', t_area_shard, rand())
CREATE TABLE dis_jiakai.t_area_all2 on cluster cluster_demo as dis_jiakai.t_area_all
ENGINE = Distributed('cluster_demo', 'dis_jiakai', t_area_all, rand())
执行成功!
试着查询一下:
select * from dis_jiakai.t_area_all2
SQL 错误 [48]: [ClickHouse]() exception, code: 48, host: 10.9.20.231, port:
8123; Code: 48, e.displayText() = DB::Exception: Distributed on
Distributed is not supported (version 19.9.2.4 (official build))
表可建,但不可用!
八.修改默认数据目录和 log 目录
修改数据目录和 log 目录
创建数据文件夹
/opt/aspire/product/isp_cert
/opt/aspire/product/isp_cert/log
安装时生成的默认文件迁移
mv /var/lib/clickhouse /opt/aspire/product/isp_cert/
mv /var/log/clickhouse-server /opt/aspire/product/isp_cert/log/
进入 / var/lib 下建立链接
cd /var/lib
ln -s /opt/aspire/product/isp_cert/clickhouse .
cd /var/log
ln -s /opt/aspire/product/isp_cert/log/clickhouse-server .
注:如果是自己创建的目录,不是从 / var/lib 下拷过去,有时会遇到权限问题,这里需要把 /opt/aspire/product/isp_cert/
下的文件授权给 clickhouse 用户:
chown -Rc clickhouse:clickhouse /opt/aspire/product/isp_cert/clickhouse
chown -Rc clickhouse:clickhouse /opt/aspire/product/isp_cert/log/clickhouse-server
注:目录搬迁不能搬迁到 / home/xx 用户下,就算 chown 也不行,猜测原因是 clickhouse 用户没权限进入 / home/xx 用户这个目录。
九.建表与插入数据
1.建表
CREATE TABLE default.filter_test_qyj
(
`product_key` String,
`city` String,
`send_date_a` Date,
`send_date_b` Date,
`contribution_rate` Nullable(Float64)
)
ENGINE = MergeTree
ORDER BY (product_key)
SETTINGS index_granularity = 8192;
2.插入数据
INSERT INTO default.filter_test_qyj (`product_key`, `city`, `send_date_a`,`send_date_b`
, `contribution_rate`)
VALUES ('1', '215', 2020-01-01,2020-02-01, 50);
3.clickhouse 使用代码操作增删改查
//生成备份表
String tempName = "temp_" + tableName + "_temp";
String sql1 = "create table " + warpName(dbName) + "." + warpName(tempName) + " as " + warpName(dbName) + "." + warpName(tableName);
super.getDdlClickHouseJdbcService().execute(sql1);
//处理for循环变量
this.handleCyclicVariate(connection, newSql, outputFields, tempName);
//修改原表的表名
String tempNameRemove = "temp_" + tableName + "_temp_remove";
String sql2 = "RENAME TABLE " + warpName(dbName) + "." + warpName(tableName) + " TO " + warpName(dbName) + "." + warpName(tempNameRemove);
super.getDdlClickHouseJdbcService().execute(sql2);
//将备份表表名改为原表表名
String sql3 = "RENAME TABLE " + warpName(dbName) + "." + warpName(tempName) + " TO " + warpName(dbName) + "." + warpName(tableName);
super.getDdlClickHouseJdbcService().execute(sql3);
//删除修改的原表
String sql4 = "DROP table "+ warpName(dbName) + "." + warpName(tempNameRemove);
super.getDdlClickHouseJdbcService().execute(sql4);
觉得有用的话点个赞 👍🏻
呗。
❤️❤️❤️本人水平有限,如有纰漏,欢迎各位大佬评论批评指正!😄😄😄
💘💘💘如果觉得这篇文对你有帮助的话,也请给个点赞、收藏下吧,非常感谢!👍 👍 👍
🔥🔥🔥Stay Hungry Stay Foolish 道阻且长,行则将至,让我们一起加油吧!🌙🌙🌙