[note] ClickHouse

安裝與啟動

$ brew install --cask clickhouse

$ clickhouse server      # 等價於文件中的 "./clickhouse server" 或 "clickhouse-server"，--daemon 可以讓 clickhouse server 在背景執行
$ clickhouse client      # 等價於文件中的 "./clickhouse client" 或 "clickhouse-client"
$ clickhouse             # 等價於文件中的 "./clickhouse"，會啟動 clickhouse-local

# 在 MacOS 還沒找到直接停掉 clickhouse 的方法，所以目前我是找出 clickhouse server 的 PID 後，kill 掉
$ ps aux | grep clickhouse
$ kill <PID>

clickhouse-local 讓你能在不需要設定檔的情況下，使用 ClickHouse SQL 處理 local 和 remote 的檔案。這裡資料只會暫存，一點重新啟動 clickhouse-local，與那邊建立的 table 就會消失。

資訊

ClickHouse server 預設會執行在 TCP Port 9000。Web UI 則會執行在 HTTP 8123。

除了使用 clickhouse client 連進 ClickHouse sever 外，ClickHouse 本身有提供一個 Web UI，在啟動 server 後可以進到 http://localhost:8123/play，就可以在這裡下 ClickHouse 的 SQL。

ClickHouse Client CLI

# 等價於文件中的 "./clickhouse client" 或 "clickhouse-client"
$ clickhouse client --help
$ clickhouse client -m   # --multiline，在 CLI 中使用 ; 當作結尾
$ clickhouse client -q [query]  # --query，在啟動 client 時，直接執行 query

在 ClickHouse Client 中下 Query 的方式：

如果希望換行輸入 Query，需要在換行前使用 \
如果有使用 multiline 模式，則可以隨意換行，直到使用 ; 後，程式才會執行
直接在啟動 CLI 時輸入 Query 可以使用 -q [query]，例如 clickhouse client -q "SHOW DATABASES"。

ClickHouse SQL

官方文件

ClickHouse SQL Statements

DDL (Data Definition Language)

keywords: `CREATE`, `RENAME`, `TRUNCATE`, `DROP`, `ADD`, `DROP`, `CLEAR`

針對 Table 的操作

# 顯示所有的 databases
SHOW DATABASES;

# 顯示目前 database 裡有哪些 tables（如果沒有寫 FROM xxx 則會查名為 "default" 的 database）
SHOW TABLES FROM SQL_EXAMPLES;

# 建立一個名為 "SQL_EXAMPLES" 的 database
CREATE DATABASE SQL_EXAMPLES;

# 在 SQL_EXAMPLES 這個 database 中，建立一個名為 "table1" 的 table
# 預設如果沒有指定 database，則會用名為 "default" 的 database
CREATE TABLE SQL_EXAMPLES.table1
(Column1 String)
ENGINE=Log;

# 在 SQL_EXAMPLES 這個 database 中，建立一個名為 "salary" 的 table
CREATE TABLE SQL_EXAMPLES.salary
(
    salary Decimal(18, 2),
    month Nullable(String),
    name String
)
ENGINE=MergeTree
ORDER BY name;

# Rename a table
RENAME TABLE SQL_EXAMPLES.table1 TO SQL_EXAMPLES.table2

# 移除 table 中的所有 rows
TRUNCATE TABLE SQL_EXAMPLES.table2;

# 移除 table
DROP TABLE SQL_EXAMPLES.table2;

針對 Column 的操作

# ALTER TABLE [DB.][Table] ADD/RENAME/CLEAR/DROP COLUMN [Column] ...;

# 添加名為 "Column3" 的 Column
ALTER TABLE SQL_EXAMPLES.table2 ADD COLUMN Column3 Nullable(String);

# 將 Column3 改名成 Column4
ALTER TABLE SQL_EXAMPLES.table2 RENAME COLUMN Column3 TO Column4;

# 把 Column4 裡的資料清空
ALTER TABLE SQL_EXAMPLES.table2 CLEAR COLUMN Column4;

# 把 Column4 這個欄位砍掉
ALTER TABLE SQL_EXAMPLES.table2 DROP COLUMN Column4;

DQL (Data Query Language)

keywords: `SELECT`

# 如果沒有指定是那個 database 裡的 table，預設會去 query 名為 "default" 的 database
SELECT COUNT(name), is_aggregate FROM system.functions WHERE origin='System' GROUP BY is_aggregate ORDER BY is_aggregate DESC;

DML (Data Manipulation Language)

keywords: `INSERT`, `ALTER`, `UPDATE`, `DELETE`

# 新增兩個 row 的資料
INSERT INTO SQL_EXAMPLES.table1 VALUES ('a'), ('b');

# 新增一個 row 中三個 column 的資料
INSERT INTO SQL_EXAMPLES.table2 VALUES ('c', 'c', 'cat');

# 新增兩個 row 中兩個 column 的資料
INSERT INTO SQL_EXAMPLES.table2 (Column1, Column2) VALUES ('a', 'a'), ('b', 'b');
INSERT INTO SQL_EXAMPLES.salary VALUES (1200, 'Nov', 'a'), (1000, 'Jan', 'b'), (800, Null, 'd');


# UPDATE：把 Column2 為 'a' 的 value 改成 'apple'
ALTER TABLE SQL_EXAMPLES.table2 UPDATE Column2 = 'apple' WHERE Column2 = 'a';

# DELETE：把 Column 為 'apple' 的這個 row 刪除
ALTER TABLE SQL_EXAMPLES.table2 DELETE WHERE Column2 = 'apple';

# Lightweight Delete（只能用在 Engine 是 MergeTree family）
# https://clickhouse.com/docs/en/sql-reference/statements/delete
DELETE FROM SQL_EXAMPLES.table2 WHERE Column2 = 'b';

在 ClickHouse 中 Update 和 Delete 都被視為是 mutation operation，而 mutation operation 都會需要消耗許多的資源。

Views

keywords: `CREATE VIEW`, `CREATE MATERIALIZED VIEW`

View 可以讓我們根據其他 table，做一些計算後，建立出一個 View Table。

Normal View
- 資料不會被保存在 disk 中，而是從原本的 table 中直接拿出來
Parameterized View、
- 資料保存方式和 Normal View 一樣，但可以自己定義參數來呈現出不同的資料內容
Materialized View
- 資料是保存在磁碟
- View 中的內容會在原本的 table 被 Insert 時，才同時寫入這個 View 中（UPDATE 或 DELETE 都不會觸發）。也就是說，如果你的 Table 已經有資料，但沒有 Insert 新的資料時，Materialized View 並不會有內容。

# 建立 Normal View

# 在 SQL_EXAMPLES 這個 database 中，根據 salary 這個 table 建立出 salary_view 這個 View
# 這裡 View 裡面會有 salary 和 name 兩個欄位
CREATE VIEW SQL_EXAMPLES.salary_view
AS SELECT
    salary * 2,
    name
FROM SQL_EXAMPLES.salary;

# 檢視 View
SELECT * FROM SQL_EXAMPLES.salary_view;

#     ┌─multiply(salary, 2)─┬─name─┐
#  1. │                2400 │ a    │
#  2. │                2000 │ b    │
#  3. │                1600 │ d    │
#     └─────────────────────┴──────┘

# 建立 Parameterized View

# 以這裡為例，在 WHERE 後面放入 parameters，所以會用傳入的參數來 filter Column1 的內容
CREATE VIEW parameterized_view
AS SELECT *
FROM SQL_EXAMPLES.table2
WHERE Column1 = {Column1:String};

# 檢視 View，並把參數帶入其中
SELECT * FROM parameterized_view(Column1 = 'a');

#     ┌─Column1─┬─Column2─┐
#  1. │ a       │         │
#     └─────────┴─────────┘

# 建立 Materialized View

# 根據 SQL_EXAMPLES.salary 這個 table 建立出 salary_materialized_view 這個 View
CREATE MATERIALIZED VIEW SQL_EXAMPLES.salary_materialized_view
ENGINE = MergeTree
ORDER BY name
AS SELECT
    salary * 2,
    name
FROM SQL_EXAMPLES.salary;

# 需要 INSERT 新的資料後，Materialized View 才會有資料
INSERT INTO SQL_EXAMPLES.salary VALUES(777, 'Nov', 'z');

# 檢視 View
SELECT * FROM SQL_EXAMPLES.salary_materialized_view;

#     ┌─multiply(salary, 2)─┬─name─┐
#  1. │                1554 │ z    │
#     └─────────────────────┴──────┘

JOIN

原本的兩張 table 長這樣：

SELECT * FROM SQL_EXAMPLES.employee;

#     ┌─name─┬─city──┐
#  1. │ a    │ city1 │
#  2. │ b    │ ᴺᵁᴸᴸ  │
#  3. │ c    │ city3 │
#     └──────┴───────┘

SELECT * FROM SQL_EXAMPLES.salary;


#     ┌─salary─┬─month─┬─name─┐
#  1. │    222 │ Nov   │ a    │
#  2. │   1000 │ Jan   │ b    │
#  3. │    800 │ ᴺᵁᴸᴸ  │ d    │
#     └────────┴───────┴──────┘
#     ┌─salary─┬─month─┬─name─┐
#  4. │    777 │ Nov   │ z    │
#     └────────┴───────┴──────┘

基本的語法長這樣：

# SELECT [columns]
# FROM [LEFT TABLE]
# [JOIN TYPE] [RIGHT TABLE]
# ON [EXPRESS LIST]

Inner Join（都 mapping 到的才有）

# INNER JOIN（都 mapping 到的才有）
SELECT
    employee.name,
    employee.city,
    salary.salary,
    salary.month,
    salary.name
FROM SQL_EXAMPLES.employee AS employee
INNER JOIN SQL_EXAMPLES.salary AS salary ON employee.name = salary.name;

#    ┌─name─┬─city──┬─salary─┬─month─┬─salary.name─┐
# 1. │ a    │ city1 │    222 │ Nov   │ a           │
# 2. │ b    │ ᴺᵁᴸᴸ  │   1000 │ Jan   │ b           │
#    └──────┴───────┴────────┴───────┴─────────────┘

Left Join

以左邊（前面）的 table 為主：

SELECT
    employee.name,
    employee.city,
    salary.salary,
    salary.month,
    salary.name
FROM SQL_EXAMPLES.employee AS employee
LEFT JOIN SQL_EXAMPLES.salary AS salary ON employee.name = salary.name;

#    ┌─name─┬─city──┬─salary─┬─month─┬─salary.name─┐
# 1. │ a    │ city1 │    222 │ Nov   │ a           │
# 2. │ b    │ ᴺᵁᴸᴸ  │   1000 │ Jan   │ b           │
# 3. │ c    │ city3 │      0 │ ᴺᵁᴸᴸ  │             │
#    └──────┴───────┴────────┴───────┴─────────────┘

Right Join

以右邊（後面）的 table 為主：

SELECT
    employee.name,
    employee.city,
    salary.salary,
    salary.month,
    salary.name
FROM SQL_EXAMPLES.employee AS employee
RIGHT JOIN SQL_EXAMPLES.salary AS salary ON employee.name = salary.name;

#     ┌─name─┬─city──┬─salary─┬─month─┬─salary.name─┐
#  1. │ a    │ city1 │    222 │ Nov   │ a           │
#  2. │ b    │ ᴺᵁᴸᴸ  │   1000 │ Jan   │ b           │
#     └──────┴───────┴────────┴───────┴─────────────┘
#     ┌─name─┬─city─┬─salary─┬─month─┬─salary.name─┐
#  3. │      │ ᴺᵁᴸᴸ │    800 │ ᴺᵁᴸᴸ  │ d           │
#  4. │      │ ᴺᵁᴸᴸ │    777 │ Nov   │ z           │
#     └──────┴──────┴────────┴───────┴─────────────┘

Outer Join（FULL OUTER JOIN）

取兩個 Table 的

SELECT
    employee.name,
    employee.city,
    salary.salary,
    salary.month,
    salary.name
FROM SQL_EXAMPLES.employee AS employee
FULL OUTER JOIN SQL_EXAMPLES.salary AS salary ON employee.name = salary.name;

#     ┌─name─┬─city──┬─salary─┬─month─┬─salary.name─┐
#  1. │ a    │ city1 │    222 │ Nov   │ a           │
#  2. │ b    │ ᴺᵁᴸᴸ  │   1000 │ Jan   │ b           │
#  3. │ c    │ city3 │      0 │ ᴺᵁᴸᴸ  │             │
#     └──────┴───────┴────────┴───────┴─────────────┘
#     ┌─name─┬─city─┬─salary─┬─month─┬─salary.name─┐
#  4. │      │ ᴺᵁᴸᴸ │    800 │ ᴺᵁᴸᴸ  │ d           │
#  5. │      │ ᴺᵁᴸᴸ │    777 │ Nov   │ z           │
#     └──────┴──────┴────────┴───────┴─────────────┘

UNION

SELECT name
FROM SQL_EXAMPLES.employee
UNION ALL
SELECT name
FROM SQL_EXAMPLES.salary;

#     ┌─name─┐
#  1. │ a    │
#  2. │ b    │
#  3. │ d    │
#     └──────┘
#     ┌─name─┐
#  4. │ z    │
#     └──────┘
#     ┌─name─┐
#  5. │ a    │
#  6. │ b    │
#  7. │ c    │
#     └──────┘

如果只要顯示 DISTINCT 的 name，需要使用 SELECT DISTINCT [COLUMN]：

SELECT DISTINCT name
FROM
(
    SELECT name
    FROM SQL_EXAMPLES.employee
    UNION ALL
    SELECT name
    FROM SQL_EXAMPLES.salary
)

#     ┌─name─┐
#  1. │ z    │
#     └──────┘
#     ┌─name─┐
#  2. │ a    │
#  3. │ b    │
#  4. │ c    │
#     └──────┘
#     ┌─name─┐
#  5. │ d    │
#     └──────┘

安裝與啟動​

ClickHouse Client CLI​

ClickHouse SQL​

DDL (Data Definition Language)​

keywords: CREATE, RENAME, TRUNCATE, DROP, ADD, DROP, CLEAR​

針對 Table 的操作​

針對 Column 的操作​

DQL (Data Query Language)​

keywords: SELECT​

DML (Data Manipulation Language)​

keywords: INSERT, ALTER, UPDATE, DELETE​

Views​

keywords: CREATE VIEW, CREATE MATERIALIZED VIEW​

JOIN​

Inner Join（都 mapping 到的才有）​

Left Join​

Right Join​

Outer Join（FULL OUTER JOIN）​

UNION​

留言​