天天看點
傳回

clickhouse基礎clickhouse基礎

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
ClickHouse 資料庫
上一篇: C++之explicit構造函數
下一篇: More Effective C++ 05：謹慎定義類型轉換函數

繼續閱讀