• clickHouse基础语法

    clichouse数据类型

    整形

    • lnt8 8bit,1字节 （-128-127）
    • lnt16 16bit
    • lnt32 32bit
    • lnt64 64bit

    无符号整型

    相比于上面，就是把负数部分挪到正数部分

    • Ulnt8 （0-255）
    • Ulnt16
    • Ulnt32
    • Ulnt64

    浮点型

    • Float32 也就是float
    • Float64 也就是double

    布尔型

    • 没有单独的布尔值，可以使用Ulnt8，取值限制为0或1 ，达到相同效果

    Decimal

    精确存储小数，超过最低有效数字会被丢弃（不是四舍五入）

    • Decimal32(s)，相当于Decimal(9-s,s)，有效位数为：1-9
    • Decimal64(s)，相当于Decimal(18-s,s)，有效位数为：1-18
    • Decimal128(s)，相当于Decimal(38-s,s)，有效位数为：1-38

    decimal(10,2)中的“2”表示小数部分的位数，如果插入的值未指定小数部分或者小数部分不足两位则会自动补到2位小数，若插入的值小数部分超过了2为则会发生截断，截取前2位小数。

    “10”指的是整数部分加小数部分的总长度，也即插入的数字整数部分不能超过“10-2”位，否则不能成功插入，会报超出范围的错误。

    例如Decimal32(5)，就是整数加小数一共9位，小数部分为5位

    • 123.12311111结果为：123.12311

    字符串

    • String 任意长度的字符串
    • FixedString(N) 固定长度字符串，N必须是正自然数，字符串小于N的时候会追加 空字节 ，大于N会报错

    枚举类型

    • Enum8 相当于String - lnt8
    • Enum16 相当于String - lnt16

    时间类型

    • Date 年月日
    • Datetime 年月日时分秒
    • Datetime64 年月日时分秒毫秒

    数组

    • Array(T) 由T类型元素构成的数组

    空

    一般不要使用空，可以给一些默认值，空值会影响性能

    --Nullable 表示可以为null
CREATE TABLE 
t_null(
    x Int8,
    y Nullable(Int8)
) ENGINE TinyLog

INSERT INTO t_null VALUES (1, NULL), (2, 3)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    还有很多数据类型，详情见官网

    https://clickhouse.com/docs/zh/sql-reference/data-types/int-uint

    表引擎

    引擎大小写敏感

    日志家族

    • 需要写入许多小数据量（少于一百万行）的表的场景而开发的：

      • TingLog
      • Log
      • StripeLog

    • 以列文件的形式保存在磁盘上，不支持索引，没有并发控制，一般保存少量数据，生产基本不会用

    Memory

    • 内存引擎，数据以未压缩的原始形式保存在内存中，服务器重启数据就会消失，不支持索引，读写操作不会相互阻塞
    • 简单查询效率非常高，超过10G/s，但一般不用，除了测试以外，就是在非常需要高性能且数据量不大的情况下才会考虑（上限大概为1亿行）

    集成引擎

    • 例如，数据在mysql，想要通过clickhouse查询mysql的数据，可以把mysql的数据导入clickhouse来查，但是这样会很麻烦，所以提供了这种集成引擎，直接通过clickhouse去mysql中查询数据

    有很多种集成，并不真正拥有数据

    MergeTree家族（合并树家族）

    MergeTree
    • MergeTree以及该系列（*MergeTree）的引擎是clickhouse最强大的引擎，支持索引和分区，相当于innodb于mysql的地位
    cd /var/lib/clickhouse/

该目录下，有以下子目录：data是数据，metadata是表信息

    • 1
    • 2
    • 3

    image-20221112145128139
    clickhouse默认的user表

    CREATE TABLE `system`.users (
	name String,
	id UUID,
	storage String,
	auth_type Enum8('no_password' = 0, 'plaintext_password' = 1, 'sha256_password' = 2, 'double_sha1_password' = 3, 'ldap' = 4, 'kerberos' = 5),
	auth_params String,
	host_ip Array(String),
	host_names Array(String),
	host_names_regexp Array(String),
	host_names_like Array(String),
	default_roles_all UInt8,
	default_roles_list Array(String),
	default_roles_except Array(String),
	grantees_any UInt8,
	grantees_list Array(String),
	grantees_except Array(String)
) ENGINE = MergeTree()
ORDER BY name;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    clickhouse默认记录了数据的条数

    分区

    ​ 分区的目的就是降低扫描的范围，优化了查询速度，例如按照日志分区，就可以快速按照日期去查询，一个分区一个目录，

    ​ 如果不分区，就会默认一个all分区，也就是把所有的数据放在一个分区

    ​ 而且分区可以提供并行访问，一个分区一个线程，面对跨分区的查询，clickhouse会以分区为单位并行处理

    ​
    image-20221112151341030

    分区文件命名格式如上图，含义是：

    • 分区值-最小区块编号-最大区块编号-合并层级
    • 分区值：由partiton by 分区主键决定
      • 没有partiton by ，默认生成一个all目录，所有数据存于all目录下
      • 分区键为整型，直接用整型值的字符串作为分区id
      • 分区键为日期类，或者可以转换为日期类型
      • 其他类型的分区键，通过hash算法取hash值作为分区id
    • 最小区块编号
    • 最大区块编号
    • 合并层级

    数据的写入于分区合并

    • 任何一次数据写入都会产生新的临时分区，不会纳入任何一个已有的分区，
    • 写入后的某个时刻，大概10-15分钟，会执行合并操作，如果不想等待，也可以手动执行合并操作，把临时分区中的数据合并到已有分区
    -- 合并操作：
optimize table 表名 final;
-- 只针对某个分区进行合并
optimize table 表名 partition '分区名'  final;

    • 1
    • 2
    • 3
    • 4

    主键

    • clickhouse主键提供了一级索引，但并不唯一，而且并不是必须的

    • 主键提供的索引为稀疏索引，默认间隔为8192，除非有大量的重复值，一般不需要改这个值，可以减少索引文件的大小，以及快速定位，但是没办法避免在一个区间内的扫描

    排序

    • order by（） 排序是的必须的，多个字段“，”分开
    • 因为稀疏索引只能确定一个范围，所以数据必须有序，不然就无法通过索引来定位数据
    • 而且操作有序的数据，效率会更高

    主键必须是order by的前缀字段，也就是 order by (id,name) ，那么主键就必须是id或者id,name

    二级索引

    在20.1.2.4之前，二级索引是实验阶段，需要手动开启，后续版本该设置已被删除，默认开启

    建表语句如下：

    CREATE TABLE test (
	name String,
	id UUID,
	ctime Datetime,
	INDEX a id TYPE minmax GRANULARITY 5
) ENGINE = MergeTree()
ORDER BY (name,id);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    INDEX 索引名 字段名 TYPE minmax（索引的一种类型，这里是记录最小和最大值） GRANULARITY（粒度） 5

    ​ 粒度，指的是在一级索引的前提下，在记录数据的粒度，比如说，有很多重复的数据，一级索引记录的区间为1-10，10-20，20-50等，想要判断35数据在哪一个区间，就需要判断3次，这里的粒度是5，也就是把五个区间合并为一个二级索引，使得区间的范围更大，查找更高效，也就是对于一级索引粒度的粒度

    数据TTL

    也就是数据存活时间，MergeTree可以单独对表或者列设置过期时间，过期了数据合并后就会变成默认值

    CREATE TABLE test (
	name String,
	age UInt8 TTL ctime+interval 10 SECOND,
	ctime Date
) ENGINE = MergeTree()
ORDER BY (name,ctime);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    TTL 表中现有的时间列+（加或减固定时间值）interval 10 SECOND（时间单位：秒分时天月等）, 这里表中现有的时间列 不能是主键

    对于已经建好的表，可以修改列的定义加上ttl

    ALTER TABLE 表名 MODIFY COLUMN 列名 列的数据类型 TTL 表中现有的时间列+interval 10 SECOND

    • 1

    表及别的TTL，相等于给所有的列加上过期时间，也可以在建表的时候，在order by 后加上TTL

    ALTER TABLE 表名 MODIFY TTL 表中现有的时间列+interval 10 SECOND

    • 1
    ReplacingMergeTree
    • 是MergeTree的一个变种，增加了去重的作用，会根据order by 的字段来去重，
    • 只有在同一批插入（新版本）或者合并的时候去重，所以在合并前是有可能出现重复数据的，只能保证最终的数据不重复
    • 而且如果表经过了分区，去重也只会在分区内部进行，不能跨分区去重
    • 如果出现重复值，会保留ReplacingMergeTree（）填的字段，保留该字段最大的值的数据，如果该字段的值也一样或者ReplacingMergeTree（）不填，就默认按照插入序，保留最后一条
    CREATE TABLE test (
	name String,
	age UInt8 TTL ctime+interval 10 SECOND,
	ctime Date
) ENGINE = ReplacingMergeTree(ctime)
ORDER BY (name,ctime);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    ​

    SummingMergeTree

    • 是MergeTree的一个变种，因为临时查询时聚合的开销很大，增加了求和功能

    • 提供一种分区内的预聚合，不能跨分区，也不是实时聚合，在同一批次插入（新版本）或者合并的时候会聚合，所以有可能出现没有聚合的情况

    • SummingMergeTree（）里填写的是想要聚合的列，可以填多个，但必须是数值，如果不填就会以所有非维度列的字段来汇总（也就是除了order by 的字段），按插入顺序保留第一行

    • 根据order by 的维度（类似于 group by ）来聚合

      CREATE TABLE test1 (
	name String,
	age UInt8 TTL ctime+interval 10 SECOND,
	ctime Date
) ENGINE = SummingMergeTree(age)
ORDER BY (name,ctime);

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

      sql语句

    insert

    和标准sql基本一样

    update 和 delete

    • 这类操作被称为Mutation查询，可以看成是alter的一种
    • 虽然可以实现修改和删除，但并不是传统意义上的update 和 delete，不支持事务
    • 而且每次update 和 delete都会放弃目标原有的分区，重新建立分区，所以尽量进行批量操作，不要频繁的小数据操作

    语法例如：

    alter table 表名  delete where 过滤条件

alter table 表名  update 字段名 = “”  where 过滤条件

    • 1
    • 2
    • 3

    ​ 具体操作是先新增分区，并把旧的分区变为逻辑失效，直到触发分区合并，才会删除旧的分区

    ​ 如果真的想要删除更新数据，也可以增加一个版本号，每次取数据的时候，只取版本号最大的数据，这样可以避免删除和更新操作，然后定期清理过期数据。也是一种思路

    查询

    基本和标准sql也差别不大

    • 支持子查询
    • 支持CTE（with）
    • 支持各种join，但是尽量避免join的使用，而且join语句不支持缓存
    • 窗口函数
    • 不支持自定义函数，官方的函数还是很充足的
    • group by 增加了一些操作（with rollup/cube/total），以group by (a,b)为例
      • rollup上卷，会得到group by (a,b)和group by (a)，group by ()
      • cube 维度分析，会得到group by (a,b)，group by (a)，group by (b)，group by ()
      • total，会得到group by (a,b)，group by ()

    alter

    基本和mysql一致

    -- 新增字段
alter table 表名 add  column  字段名 字段类型 after column1  ; (这里是指在那一列之后)
-- 修改字段类型
alter table 表名 modify column 列名 字段类型 ;
-- 删除字段
alter table 表名 drop column 列名;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    导出

    clickhouse-client --password xxxx --query '查询语句' --format CSVWithNames>  导出路径

clickhouse-client --password 123456 --query 'SELECT  * from `system`.aggregate_function_combinators afc' --format CSVWithNames>  /usr/data.csv

    • 1
    • 2
    • 3

    其他格式参考官网：

    https://clickhouse.com/docs/zh/interfaces/formats

  • 相关阅读:
    使用arcpy遇到的那些坑（四）
    python技术面试题（其三）
    六零导航页SQL注入漏洞复现（CVE-2023-45951）
    终于把相册集成到摄像头APP
    mysql的高阶语句
    Mybatis-Plus 条件构造器
    浏览器黑暗模式插件
    基于Springboot的代驾管理系统（有报告）。Javaee项目，springboot项目。
    Flutter pod install 时提示Error installing GoogleUtilitiesComponents
    做个清醒的程序员之拥抱AI
  • 原文地址：https://blog.csdn.net/persistence_PSH/article/details/128069582