【HBZ分享】Clickhouse常用命令及SQL语法

Clickhouse常用命令及SQL语法

建库语句

CREATE DATABASE [库名]
例：
CREATE DATABASE first_clickhouse

MergeTree建表语句

create table [库名].[表名]([字段1], [字段2]) engine = [引擎]  order by([字段1], [字段2]...)
例：
create table first_clickhouse.order_merge_tree( 
    id UInt32,
    sku_id String,
    out_trade_no String,
    total_amount Decimal(16,2), 
    create_time Datetime,
    is_new Bool
) engine =MergeTree()
  order by (id,sku_id)
  partition by toYYYYMMDD(create_time) 
  primary key (id);

ENGINE：引擎(每个表必须指定引擎，决定数据存储方式和位置)
ORDER BY： 排序
partition by toYYYYMMDD(create_time) ： 分区键，相同的create_time会被分到同一个分区
primary key (id); 主键是id列

ReplacingMergeTree建表语句

create table [库名].[表名]([字段1], [字段2]) engine = [引擎]  order by([字段1], [字段2]...)
例：
create table first_clickhouse.order_relace_merge_tree( 
    id UInt32,
    sku_id String,
    out_trade_no String,
    total_amount Decimal(16,2), 
    create_time Datetime
) engine =ReplacingMergeTree(id)
  order by (sku_id)
  partition by toYYYYMMDD(create_time) 
  primary key (sku_id);`

ENGINE：引擎(每个表必须指定引擎，决定数据存储方式和位置)
ORDER BY： 排序
partition by toYYYYMMDD(create_time) ： 分区键，相同的create_time会被分到同一个分区
primary key (id); 主键是id列


(1).按照sku_id排序，即去重策略也是去重相同分区的相同sku_id的
(2).ReplacingMergeTree(id)：去重保留最大的id，人为造数据可能最新的id反而会小，所以不能说保留最新的id
(3).根据create_time进行分区，同个时间，分到同一个区域，去重策略仅仅作用于同一个分区

SummingMergeTree建表语句

create table [库名].[表名]([字段1], [字段2]) engine = [引擎]  order by([字段1], [字段2]...)
例：
create table first_clickhouse.order_summing_merge_tree( 
    id UInt32,
    sku_id String,
    out_trade_no String,
    total_amount Decimal(16,2), 
    create_time Datetime
) engine =SummingMergeTree(total_amount)
  order by (id,sku_id)
  partition by toYYYYMMDD(create_time) 
  primary key (id);
  
ENGINE：引擎(每个表必须指定引擎，决定数据存储方式和位置)
ORDER BY： 排序
partition by toYYYYMMDD(create_time) ： 分区键，相同的create_time会被分到同一个分区
primary key (id); 主键是id列

Distributed分区表建表语句

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = Distributed(cluster, database, table[, sharding_key[, policy_name]])
[SETTINGS name=value, ...]
例：
CREATE TABLE default.cluster_order_all
(
    `id` Int8,
    `name` String
)
ENGINE = Distributed('cluster_3shards_1replicas', 'default', 'cluster_order', hiveHash(id))


ENGINE中参数含义：
cluster： 集群名称，与集群配置中自定义名称对应，比如cluster_3shards_1replicas
database： 数据库名
table：  本地表名
sharding_key： 可选参数，用于分片的key值，再写入的数据Distributed表引擎会依据分片key的规则，将数据分不到各个节点的本地表
        user_id等业务字段，rand()随机函数等规则

枚举列相关操作

1. 创建枚举列
CREATE TABLE t_enum
(
    page_code Enum8('home' = 1, 'detail' = 2,'pay'=3)
)
ENGINE = TinyLog

2. 插入枚举列 
INSERT INTO t_enum VALUES ('home'), ('detail')

3. 查询枚举列
SELECT * FROM t_enum
SELECT * FROM first_clickhouse.t_enum where page_code = 'home'

插入语句

表结构：
create table first_clickhouse.order_summing_merge_tree( 
    id UInt32,
    sku_id String,
    out_trade_no String,
    total_amount Decimal(16,2), 
    create_time Datetime,
    is_New Bool
) engine =SummingMergeTree(total_amount)
  order by (id,sku_id)
  partition by toYYYYMMDD(create_time) 
  primary key (id);


insert into [库名].[表名] values([列1]. [列2]...)
例：
insert into first_clickhouse.order_summing_merge_tree values 
(1,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00', 1) ,
(2,'sku_2','23241',4.02,'2023-03-01 17:00:00', 1),
(3,'sku_3','542323',55.02,'2023-03-01 18:00:00', 0), 
(4,'sku_5','54222',2000.3,'2023-04-01 19:00:00', 0), 
(5,'sku_6','53423',120.2,'2023-04-01 19:00:00', 1), 
(6,'sku_7','65432',600.01,'2023-04-02 11:00:00', 0);

查询语句

SELECT * FROM first_clickhouse.clickstream
SELECT * FROM first_clickhouse.clickstream WHERE time_stamp >= '2001-11-01'

clickhouse日期函数

求和
sum(pv) 

年格式
select toYear(toDateTime('2024-12-11 11:12:13')) 

日期格式化
select toYYYYMMDD(toDateTime('2024-12-11 11:12:13')) 

日期时间格式化
select toYYYYMMDDhhmmss(toDateTime('2024-12-11 11:12:13')) 

周格式化,1~7，当前时间是本周第几天，下面是周三结果是3，周日结果是7
select toDayOfWeek(toDateTime('2024-12-11 11:12:13')) 

小时格式化，提取时间里面的小时，比如 2023-12-29 10:05:10，格式化后是【10】点
select toHour(toDateTime('2024-12-11 11:12:13')) 

分钟格式化，提取时间里面的分钟，比如 2023-12-29 10:05:10，格式化后是【5】分钟
select toMinute(toDateTime('2024-12-11 11:12:13')) 
 
秒格式化，提取时间里面的秒
select toSecond(toDateTime('2024-12-11 11:12:13')) 
 
获取当前日期时间
select now()

获取当前日期
select today()

clickhouse其他函数

1. 逻辑判断：
SELECT if(cond, then, else)
例子：SELECT if(1, plus(3, 3), plus(6, 8))
plus(); 是两数之和得函数

如果条件 cond 的计算结果为非零值，则返回表达式 then 的结果，并且跳过表达式 else 的结果
如果 cond 为零或 NULL，则将跳过 then 表达式的结果，并返回 else 表达式的结果


2.大小写：
select lower('XDCLASS')
select upper('java')

3. 字符串拼接（不能双引号）：
select concat('我','在小滴课堂','摸鱼')

4. 最大、最小、平均值：
select max(pv), min(pv), avg(pv) from visit_stats

5. URL协议提取：
SELECT  protocol('https://xdclass.net')
结果 https

6. URL域名提取：
SELECT  domain('https://xdclass.net')
结果 xdclass.net

7. URL路径path：
select path('https://xdclass.net/api/v1/pay?video_id=2&accountNo=999')
结果 /api/v1/pay

8. 某个商品，多天内的访问曲线图, 天级别：
select 
   toYYYYMMDD(visit_time) date_time_str, 
   sum(pv) pv_count from visit_stats
where product_id =2 
and toYYYYMMDD(visit_time) BETWEEN '20200101' and '20241212' 
group by date_time_str ORDER BY date_time_str desc

更新与删除数据(尽量不要使用)

更新语法：
ALTER TABLE first_clickhouse.clickstream UPDATE  click_event_type = 'pay' where customer_id = 'customer2'; 
ALTER TABLE 库名.表名 UPDATE  字段 = '更改后的值' where 字段 = '字段值'; 

删除语法
ALTER TABLE first_clickhouse.clickstream delete where customer_id = 'customer2'; 
ALTER TABLE 库名.表名 delete where 字段 = '字段值'; 


更新原理，为什么推荐批量更新？：
    更新数据的时候，实际需要废弃之前数据的分区，并创建一个新分区。如果更新100条，每次更新1条，就会创建100个新分区。
    如果100条批量更新，那么100条可能也就在2个分区中，那么只会创建2个新分区

注意点：

1. 如果未指定数据库，即没写first_clickhouse.，则自动创建到default库中
2. Bool插入要写1/0 或 True/False          1 = True， 0 = false