提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  工具

当前栏目

【大数据系列】Clickhouse学习

学习数据 系列 ClickHouse
2023-09-27 14:29:28 时间

💝💝💝欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。
img

  • 推荐:kuan 的首页,持续学习,不断总结,共同进步,活到老学到老
  • 导航
    • 檀越剑指大厂系列:全面总结 java 核心技术点,如集合,jvm,并发编程 redis,kafka,Spring,微服务,Netty 等
    • 常用开发工具系列:罗列常用的开发工具,如 IDEA,Mac,Alfred,electerm,Git,typora,apifox 等
    • 数据库系列:详细总结了常用数据库 mysql 技术点,以及工作中遇到的 mysql 问题等
    • 懒人运维系列:总结好用的命令,解放双手不香吗?能用一个命令完成绝不用两个操作
    • 数据结构与算法系列:总结数据结构和算法,不同类型针对性训练,提升编程思维,剑指大厂

非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝 ✨✨ 欢迎订阅本专栏 ✨✨

博客目录

一.新增操作

库相关

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 支持的函数

https://www.cnblogs.com/zhangyl-/p/13121544.html

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 注意事项

  1. 严格区分大小写,注意库名和字段的大小写
  2. 子查询的查询结果需要加 as 别名
  3. 不支持 ndv 函数,支持使用 count(disticnt column)
    • 使用方法,如果设置如下参数：
    • set APPX_COUNT_DISTINCT=true;
    • 则所有的 count(distinct col)会在底层计算的时候转成 ndv() 函数，也就是说，在 sql 中可以直接使用 count(distinct col),如果不配置上述参数，则在 sql 中直接写 ndv(col) 也可以
  4. ifnull 函数在 null 时需要有默认值 ifnull(ddopsd.all_sal_act_qty,0)
  5. 查询条件字段最好加上’'单引号,避免类型不匹配
  6. 涉及到计算函数的字段,字段类型必须是 Number 类型,不能是 String
  7. 超过 50g 的表不能直接删除,需要添加一个空文件 sudo touch /data/clickhouse/flags/force_drop_table && sudo chmod 666 /data/clickhouse/flags/force_drop_table
  8. 创建表的 order by 会影响查询效率(相当于 mysql 的索引)
  9. left join 尽量使用 any left join
  10. 存在 null 值的字段不能指定为 order by 索引
  11. clickhouse 的字段是 Int32 时,插入数据不能为 null
  12. null 值不能转化为 Int32 类型,会报错
  13. clickhouse 在 21.3.1 以后的版本支持开窗函数
  14. clickhouse 的字段是 Int32 时,插入数据不能为 null
  15. 空值问题
    1. 空表，Nullable 与非空类型可以互转；
    2. Nullable 字段，如果记录不带有 Null 值，可以从 Nullable 转成非空类型；
    3. 含有 null 值的字段不允许转成非空类型；
    4. 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](https://so.csdn.net/so/search?q=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 道阻且长,行则将至,让我们一起加油吧！🌙🌙🌙

img

相关文章