clickhouse基础
clickhouse 服务管理
启动 clickhouse 服务
systemctl start ckickhouse-server
停止 clickhouse 服务
systemctl stop ckickhouse-server
客户端连接
clickhouse-client -m
# -m 表示可以多行输入
数据类型
整型
有符号整型
类型 | 范围 |
---|---|
Int8 | [ -128 : 127 ] |
Int16 | [ -32768 : 32767 ] |
Int32 | [ -2147483648 : 2147483647 ] |
Int64 | [-9223372036854775808 : 9223372036854775807] |
无符号整型
类型 | 范围 |
---|---|
UInt8 | [0 : 255] |
UInt16 | [0 : 65535] |
UInt32 | [0 : 4294967295] |
UInt64 | - [0 : 18446744073709551615] |
浮点型
Float32
Float64
布尔型
可以使用 UInt8 类型,取值限制为 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
s 表示小数位
字符串
string
可以任意长度的。它可以包含任意的字节集,包含空字节。
FixedString(N)
固定长度 N 的字符串,N 必须是严格的正自然数。当服务端读取长度小于 N 的字符 串时候,通过在字符串末尾添加空字节来达到 N 字节长度。 当服务端读取长度大于 N 的 字符串时候,将返回错误消息。
枚举类型
Enum8 用 ‘String’= Int8 对描述。
Enum16 用 ‘String’= Int16 对描述。
eg:
# 创建测试表
CREATE TABLE t_enum
(
`x` Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog
# 插入测试数据
hadoop102 :) insert into t_enum values ('hello'), ('world'), ('hello');
# 查询测试数据
hadoop102 :) select * from t_enum;
SELECT *
FROM t_enum
Query id: 00af3e94-0c7e-4b90-8d54-c2c1bd9b9bb5
┌─x─────┐
│ hello │
│ world │
│ hello │
└───────┘
↗ Progress: 3.00 rows, 3.00 B (2.38 thousand rows/s., 2.38 K
3 rows in set. Elapsed: 0.002 sec.
# 不能插入其它值
hadoop102 :) insert into t_enum values ('test');
INSERT INTO t_enum VALUES
Query id: 97f682bc-7e21-40ef-be0d-88da6adc15c7
Exception on client:
Code: 36. DB::Exception: Unknown element 'test' for enum: data for INSERT was parsed from query
# 查询对应数值
hadoop102 :) select cast(x, 'Int8')from t_enum;
SELECT cast(x, 'Int8')
FROM t_enum
Query id: cb20844c-8e9a-4dae-9c17-57813f0511dd
┌─CAST(x, 'Int8')─┐
│ 1 │
│ 2 │
│ 1 │
└─────────────────┘
3 rows in set. Elapsed: 0.004 sec.
时间类型
Date 接受年-月-日的字符串 比如 ‘2019-12-16’
Datetime 接受年-月-日 时:分:秒的字符串 比如 ‘2019-12-16 20:50:10’
Datetime64 接受年-月-日 时:分:秒.亚秒的字符串 比如‘2019-12-16 20:50:10.66’
数组
# 使用 array 函数创建数组
hadoop102 :) select array(1, 2) as x, toTypeName(x);
SELECT
[1, 2] AS x,
toTypeName(x)
Query id: 4bc0b2d3-9d47-4f49-984c-a3a36ae9b924
┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8) │
└───────┴─────────────────────────┘
1 rows in set. Elapsed: 0.005 sec.
# 使用方括号 [] 创建数组
hadoop102 :) select [1, 2] as x, toTypeName(x);
SELECT
[1, 2] AS x,
toTypeName(x)
Query id: 9228ece8-8394-4c9a-9079-10e71a8611c8
┌─x─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8) │
└───────┴────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
表引擎
引擎的名称大小写敏感
TinyLog
以列文件的形式保存在磁盘上,不支持索引,没有并发控制。用来保存少量数据的小表,用于测试。
CREATE TABLE t_tinylog
(
`id` String,
`name` String
)
ENGINE = TinyLog
Memory
内存引擎,以未压缩的原始形式保存在内存中,服务器重启,数据消失。不支持索引。简单查询性能非常高(>10G/s),用于数据量不大(上线大概1亿行),测试。
MergeTree
ClickHouse中最强大,支持索引。
CREATE TABLE t_order_mt
(
`id` UInt32,
`sku_id` String,
`total_amount` Decimal(16, 2),
`create_time` Datetime
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id, sku_id)
insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');
partition by( 可选 )
primary key ( 可选 )
order by ( 必选 )
数据 TTL( Time To Live )数据生命周期
列级别TTL
# 设置 create_time 字段10s后过期
CREATE TABLE t_order_mt3
(
`id` UInt32,
`sku_id` String,
`total_amount` Decimal(16, 2) TTL create_time + toIntervalSecond(10),
`create_time` Datetime
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id, sku_id)
insert into t_order_mt3 values
(106,'sku_001',1000.00,'2020-08-02 22:52:30'),
(107,'sku_002',2000.00,'2020-08-02 22:52:30'),
(110,'sku_003',600.00,'2020-08-02 12:00:00');
表级TTL
# 数据会在 create_time 10s之后丢失
ALTER TABLE t_order_mt3
MODIFY TTL create_time + toIntervalSecond(10)
ReplacingMergeTree
相比MergeTree多了一个去重
-
实际上是使用 order by 字段作为唯一键
-
去重不能跨分区
-
只有同一批插入(新版本)或合并分区时才会进行去重
-
认定重复的数据保留,版本字段值最大的
-
如果版本字段相同则按插入顺序保留最后一笔
# ReplacingMergeTree() 填入的参数为版本字段,重复数据保留版本字段值最大的。如果不填版本字段,默认按照插入顺序保留最后一条。
CREATE TABLE t_order_rmt
(
`id` UInt32,
`sku_id` String,
`total_amount` Decimal(16, 2),
`create_time` Datetime
)
ENGINE = ReplacingMergeTree(create_time)
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id, sku_id)
# 插入数据
insert into t_order_rmt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');
# 合并分区
hadoop102 :) OPTIMIZE TABLE t_order_rmt FINAL;
OPTIMIZE TABLE t_order_rmt FINAL
Query id: 0bc80579-937a-48b9-bb79-e685c39f1219
Ok.
0 rows in set. Elapsed: 0.002 sec.
# 查询
hadoop102 :) select * from t_order_rmt;
SELECT *
FROM t_order_rmt
Query id: 7a53ba26-ac8a-464a-8980-e6239c460269
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000.00 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │ 12000.00 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │ 2500.00 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
4 rows in set. Elapsed: 0.002 sec.
SummingMergeTree
预聚合引擎,适用于不查询明细,只关心维度进行轻度聚合结果。
-
以 SummingMergeTree()中指定的列作为汇总数据列
-
可以填写多列必须数字列,如果不填,以所有非维度列且为数字列的字段为汇总数 据列
-
以 order by 的列为准,作为维度列
-
其他的列按插入顺序保留第一行
-
不在一个分区的数据不会被聚合
-
只有在同一批次插入(新版本)或分片合并时才会进行聚合
CREATE TABLE t_order_smt
(
`id` UInt32,
`sku_id` String,
`total_amount` Decimal(16, 2),
`create_time` Datetime
)
ENGINE = SummingMergeTree(total_amount)
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id, sku_id)
insert into t_order_smt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');
hadoop102 :) select * from t_order_smt;
SELECT *
FROM t_order_smt
Query id: 8b62f75c-34d6-47ad-bbdb-2793a1e48b00
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000.00 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │ 16000.00 │ 2020-06-01 11:00:00 │
│ 102 │ sku_004 │ 2500.00 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
4 rows in set. Elapsed: 0.003 sec.
SQL 操作
insert
insert into [table_name] values(…),(….)
insert into [table_name] select a,b,c from [table_name_2]
update 和 delete
# 删除
alter table t_order_smt delete where sku_id ='sku_001';
# 修改
alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id=102;
查询
# 清空表中数据
alter table t_order_mt delete where 1=1;
# 插入数据
insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(101,'sku_002',2000.00,'2020-06-01 12:00:00'),
(103,'sku_004',2500.00,'2020-06-01 12:00:00'),
(104,'sku_002',2000.00,'2020-06-01 12:00:00'),
(105,'sku_003',600.00,'2020-06-02 12:00:00'),
(106,'sku_001',1000.00,'2020-06-04 12:00:00'),
(107,'sku_002',2000.00,'2020-06-04 12:00:00'),
(108,'sku_004',2500.00,'2020-06-04 12:00:00'),
(109,'sku_002',2000.00,'2020-06-04 12:00:00'),
(110,'sku_003',600.00,'2020-06-01 12:00:00');
# with rollup 从右至左去掉维度进行小计
hadoop102 :) select id, sku_id, sum(total_amount) from t_order_mt group by id, sku_id with rollup;
SELECT
id,
sku_id,
sum(total_amount)
FROM t_order_mt
GROUP BY
id,
sku_id
WITH ROLLUP
Query id: a7f956b8-db80-4adb-b49e-a8c35ae95c75
┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │ 600.00 │
│ 109 │ sku_002 │ 2000.00 │
│ 107 │ sku_002 │ 2000.00 │
│ 106 │ sku_001 │ 1000.00 │
│ 104 │ sku_002 │ 2000.00 │
│ 101 │ sku_002 │ 2000.00 │
│ 103 │ sku_004 │ 2500.00 │
│ 108 │ sku_004 │ 2500.00 │
│ 105 │ sku_003 │ 600.00 │
│ 101 │ sku_001 │ 1000.00 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 110 │ │ 600.00 │
│ 106 │ │ 1000.00 │
│ 105 │ │ 600.00 │
│ 109 │ │ 2000.00 │
│ 107 │ │ 2000.00 │
│ 104 │ │ 2000.00 │
│ 103 │ │ 2500.00 │
│ 108 │ │ 2500.00 │
│ 101 │ │ 3000.00 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│ 0 │ │ 16200.00 │
└────┴────────┴───────────────────┘
20 rows in set. Elapsed: 0.008 sec.
# with cube 从右至左去掉维度进行小计,再从左至右去掉维度进行小计
hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with cube;
SELECT
id,
sku_id,
sum(total_amount)
FROM t_order_mt
GROUP BY
id,
sku_id
WITH CUBE
Query id: 8f86ac84-3e48-4ae4-82c5-aa8a871ac0d5
┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │ 600.00 │
│ 109 │ sku_002 │ 2000.00 │
│ 107 │ sku_002 │ 2000.00 │
│ 106 │ sku_001 │ 1000.00 │
│ 104 │ sku_002 │ 2000.00 │
│ 101 │ sku_002 │ 2000.00 │
│ 103 │ sku_004 │ 2500.00 │
│ 108 │ sku_004 │ 2500.00 │
│ 105 │ sku_003 │ 600.00 │
│ 101 │ sku_001 │ 1000.00 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 110 │ │ 600.00 │
│ 106 │ │ 1000.00 │
│ 105 │ │ 600.00 │
│ 109 │ │ 2000.00 │
│ 107 │ │ 2000.00 │
│ 104 │ │ 2000.00 │
│ 103 │ │ 2500.00 │
│ 108 │ │ 2500.00 │
│ 101 │ │ 3000.00 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id──┬─sum(total_amount)─┐
│ 0 │ sku_003 │ 1200.00 │
│ 0 │ sku_004 │ 5000.00 │
│ 0 │ sku_001 │ 2000.00 │
│ 0 │ sku_002 │ 8000.00 │
└────┴─────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│ 0 │ │ 16200.00 │
└────┴────────┴───────────────────┘
24 rows in set. Elapsed: 0.006 sec.
# with total 只计算合计
hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with totals;
SELECT
id,
sku_id,
sum(total_amount)
FROM t_order_mt
GROUP BY
id,
sku_id
WITH TOTALS
Query id: 602b90de-6e5d-4af1-aa19-070d43ef7b88
┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │ 600.00 │
│ 109 │ sku_002 │ 2000.00 │
│ 107 │ sku_002 │ 2000.00 │
│ 106 │ sku_001 │ 1000.00 │
│ 104 │ sku_002 │ 2000.00 │
│ 101 │ sku_002 │ 2000.00 │
│ 103 │ sku_004 │ 2500.00 │
│ 108 │ sku_004 │ 2500.00 │
│ 105 │ sku_003 │ 600.00 │
│ 101 │ sku_001 │ 1000.00 │
└─────┴─────────┴───────────────────┘
Totals:
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│ 0 │ │ 16200.00 │
└────┴────────┴───────────────────┘
10 rows in set. Elapsed: 0.007 sec.
alter
# 新增字段
alter table tableName add column newcolname String after col1;
# 修改字段类型
alter table tableName modify column newcolname String;
# 删除字段
alter table tableName drop column newcolname;
导出数据
clickhouse-client
--query
"select * from t_order_mt where create_time='2020-06-01 12:00:00'"
--format CSVWithNames>
/opt/module/data/rs1.csv