2022 iThome 鐵人賽

DAY 16

Software Development

ClickHouse:時序資料庫建置與運行系列第 16 篇

day16-SQL使用與操作方法介紹(七)

14th鐵人賽

peter279k

2022-09-30 10:39:57

1026 瀏覽

分享至

前言

在前一章節中，我們已經完整的講述SELECT查詢使用的方法以及可以接的從句，在本章節中，我們將會展示INSERT INTO與CREATE等SQL語句用法。

INSERT INTO語句

這個描述的語句的作用就是將資料寫入到指定的資料表中，相關的語法如下：

INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...

從上述的語法來看，我們可以同時將資料寫入到資料表中指定的欄位，指定欄位的部分可以使用*表示全部的欄位，欄位順序就是取決於當初建立資料表綱要時建立的欄位順訊，也可以同時寫入多筆的資料。

舉例來說，下面有一個資料表如下：

CREATE TABLE insert_select_testtable
(
    `a` Int8,
    `b` String,
    `c` Int8
)
ENGINE = MergeTree()
ORDER BY a

執行上述的SQL語句將資料表的綱要進行新增，相關的SQL執行與輸出的訊息如下：

ClickHouse client version 22.8.4.7 (official build).
Password for user (default):
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.8.4 revision 54460.

Warnings:
 * Linux is not using a fast TSC clock source. Performance can be degraded. Check /sys/devices/system/clocksource/clocksource0/current_clocksource

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE TABLE insert_select_testtable
                                  (
                                      `a` Int8,
                                      `b` String,
                                      `c` Int8
                                  )
                                  ENGINE = MergeTree()
                                  ORDER BY a

CREATE TABLE insert_select_testtable
(
    `a` Int8,
    `b` String,
    `c` Int8
)
ENGINE = MergeTree
ORDER BY a

Query id: 78a1902f-fec0-4802-8299-a5ea5b62198a

Ok.

0 rows in set. Elapsed: 0.010 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SHOW CREATE insert_select_testtable;

SHOW CREATE TABLE insert_select_testtable

Query id: 595b2861-1590-417e-9db9-e5f89b0f5761

┌─statement───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.insert_select_testtable
(
    `a` Int8,
    `b` String,
    `c` Int8
)
ENGINE = MergeTree
ORDER BY a
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

從上述的SQL執行過程中可以知道，預設會將insert_select_testtable資料表綱要建立在default資料庫中，我們在建立好資料表之後，可以使用SHOW CREATE TABLE來顯示指定的資料表的綱要。

接著使用下列的SQL語句將一些資料寫入到上述建立好的資料表中，相關的SQL語句執行與輸出的訊息如下：

INSERT INTO insert_select_testtable (*) VALUES (1, 'a', 1) ;

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) INSERT INTO insert_select_testtable (*) VALUES (1, 'a', 1) ;

INSERT INTO insert_select_testtable (*) FORMAT Values

Query id: 32e10665-cfac-4ded-9952-08dc6a8b59d2

Ok.

1 row in set. Elapsed: 0.003 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

同時，我們也可以使用下列的SQL語句將資料寫入到資料表：

INSERT INTO insert_select_testtable (* EXCEPT(b)) Values (2, 2);

從上述的SQL語句可以知道，我們使用了*表示所有的欄位但是用了EXCEPT(b)，表示所有欄位但是除了b欄位，相關的執行SQL語句與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) INSERT INTO insert_select_testtable (* EXCEPT(b)) Values (2, 2);

INSERT INTO insert_select_testtable (* EXCEPT b) FORMAT Values

Query id: 9a26ab85-7898-4fee-8270-57de289d8034

Ok.

1 row in set. Elapsed: 0.003 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

接著執行下列的SQL語句將此資料表所有的資料查詢出來：

SELECT * FROM insert_select_testtable;

執行上述的SQL查詢語句之後，得到的輸出訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * FROM insert_select_testtable;

SELECT *
FROM insert_select_testtable

Query id: 817368c2-4730-4623-b697-2eb6e9f93c05

┌─a─┬─b─┬─c─┐
│ 2 │   │ 2 │
└───┴───┴───┘
┌─a─┬─b─┬─c─┐
│ 1 │ a │ 1 │
└───┴───┴───┘

2 rows in set. Elapsed: 0.003 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

從上述查詢出來的資料可以知道，第一筆資料所有的欄位都有寫入資料，而第二筆資料因為跳過b欄位，因此b欄位值是空的字串，不是空值NULL，可以利用SELECT查詢語句搭配WHERE來驗證這個情形：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * FROM insert_select_testtable WHERE b = '';

SELECT *
FROM insert_select_testtable
WHERE b = ''

Query id: bdc062e1-b37d-431e-9ad3-682462b49680

┌─a─┬─b─┬─c─┐
│ 2 │   │ 2 │
└───┴───┴───┘

1 row in set. Elapsed: 0.003 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

因為b欄位的資料型別是字串，預設的值是空字串，因此在寫入資料時，忽略該欄位會以預設的值寫入進去，我們也可以使用下列的SQL語句將資料寫入：

INSERT INTO insert_select_testtable VALUES (1, DEFAULT, 1) ;

透過上述的SQL寫入一筆資料，並指定b欄位是寫入預設值，若沒有指定欄位則預設也是指定全部的欄位，因此上述的SQL寫入資料語句可以等價於下列的語句：

INSERT INTO insert_select_testtable(*) VALUES (1, DEFAULT, 1) ;

除了可以將資料在INSERT INTO的語句中指定之外，也可以指定寫入資料的格式並進行資料的寫入，相關的SQL語法如下所示：

INSERT INTO [db.]table [(c1, c2, c3)] FORMAT format_name data_set

下列是使用上述的SQL來當作範例將資料寫入insert_select_testtable資料表，相關的執行SQL與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) INSERT INTO insert_select_testtable FORMAT CSV 1,'1',1

INSERT INTO insert_select_testtable FORMAT CSV

Query id: 83a54aac-e956-400f-8d6d-4a484e01a420

Ok.

1 row in set. Elapsed: 0.004 sec.
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * FROM insert_select_testtable

SELECT *
FROM insert_select_testtable

Query id: d407aaed-27a1-4390-a927-cf5504c0c5be

┌─a─┬─b─┬─c─┐
│ 1 │ a │ 1 │
└───┴───┴───┘
┌─a─┬─b─┬─c─┐
│ 2 │   │ 2 │
└───┴───┴───┘
┌─a─┬─b─┬─c─┐
│ 1 │   │ 1 │
└───┴───┴───┘
┌─a─┬─b───┬─c─┐
│ 1 │ '1' │ 1 │
└───┴─────┴───┘
┌─a─┬─b─┬─c─┐
│ 1 │   │ 1 │
└───┴───┴───┘

5 rows in set. Elapsed: 0.003 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

從上述的執行SQL訊息可以知道，在執行INSERT INTO的時候，使用了CSV當作要寫入資料的格式並將1,'1',1寫入到資料表中，下面是寫入的SQL並使用Values這個基本的格式來定義要寫入的資料：

INSERT INTO [db.]table [(c1, c2, c3)] FORMAT Values (v11, v12, v13), (v21, v22, v23), ...

我們也可以將使用SELECT查詢出來的結果當作要寫入的資料集來寫入到指定的資料表中，相關的SQL語法如下：

INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...

我們也可以將要寫入的資料用既有檔案來寫入到指定的資料表，相關的SQL語法使用如下：

INSERT INTO [db.]table [(c1, c2, c3)] FROM INFILE file_name [COMPRESSION type] FORMAT format_name

從上述的SQL語句執行並輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ echo 1,A > input.csv ; echo 2,B >> input.csv
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ cat input.csv
1,A
2,B
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ clickhouse-client --password --query="CREATE TABLE table_from_file (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;"
Password for user (default):
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ clickhouse-client --password --query="INSERT INTO table_from_file FROM INFILE 'input.csv' FORMAT CSV;"
Password for user (default):
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ clickhouse-client --password --query="SELECT * FROM table_from_file FORMAT PrettyCompact;"
Password for user (default):
┌─id─┬─text─┐
│  1 │ A    │
│  2 │ B    │
└────┴──────┘

從上述的輸出訊息可以知道，首先先建立一個要入的檔案，叫做input.csv並將兩筆CSV資料寫入到該檔案中，接著利用clickhouse-client指令連到本地端的ClickHouse資料庫，做下列事情：

建立一個資料表叫做table_from_file並建立兩欄位，分別是id與text，型別是UInt32與string。
執行INSERT INTO指令將先前建立好的input.csv檔案進行寫入資料表的動作。
最後使用SELECT之SQL語句進行驗證上述寫入資料的動作有確實的執行，而FORMAT PrettyCompact從句則是指定查詢出來的結果以PrettyCompact格式輸出到終端機上。

我們也可以利用資料表函數來寫入資料，相關的SQL語法如下：

INSERT INTO [TABLE] FUNCTION table_func ...

下面有個範例，執行該範例之後的輸出訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE TABLE simple_table (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;

CREATE TABLE simple_table
(
    `id` UInt32,
    `text` String
)
ENGINE = MergeTree
ORDER BY id

Query id: 841522c2-b2e4-4462-8bf5-b162d161485b

Ok.

0 rows in set. Elapsed: 0.008 sec.
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) INSERT INTO TABLE FUNCTION remote('localhost', default.simple_table)
                                      VALUES (100, 'inserted via remote()');

INSERT INTO FUNCTION remote('localhost', default.simple_table) FORMAT Values

Query id: 3edd183e-e3c1-46c3-8fbd-291793d3ccce

Ok.

1 row in set. Elapsed: 0.005 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * FROM simple_table;

SELECT *
FROM simple_table

Query id: d77c3c1d-ccd2-4cfb-b4fd-774036cf40e9

┌──id─┬─text──────────────────┐
│ 100 │ inserted via remote() │
└─────┴───────────────────────┘

1 row in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

從上述的輸出訊息可以得知，首先先建立資料表叫做simple_table以及相關的綱要，接著使用remote的資料表函數，這個函數可以讓我們連到外部的資料庫並寫入外部的資料表，接著使用SELECT語句來驗證有沒有正確的寫入資料。

當我們在用INSERT INTO語句時，需要避免下列的情形：

設定批次寫入的大小，不要一次寫入100,000筆資料。
在寫入資料到資料庫之前，避免按分區鍵對資料進行分組的動作。

效能並不會因為下面的情形而降低：

及時的寫入資料。
寫入的資料是按照時間序列的資料。

寫入資料也可以設定非同步的方式寫入，相關的非同步寫入方式只能支援以HTTP通訊協定進行非同步寫入，相關的做法我們在後續的章節會提到。

CREATE語句

此語句是用來建立新的項目用的，項目可以指的是DATABASE、TABLE、FUNCTION以及VIEW等。

我們可以使用下列的語法來建立資料庫：

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)] [COMMENT 'Comment']

加入IF NOT EXISTS從句時，當指定的資料庫存在時，執行此建立資料庫的SQL語句不會執行，ON CLUSTER從句是當使用分散式資料庫會用到，我們在後續的章節會提到。
ENGINE從句是指定資料庫要用的引擎，有非常多種可以選擇，也可以使用ClickHouse預設的資料庫引擎–Atomic即可。

我們也可以在建立資料庫時候對此資料庫加入註解，即在CREATE語句最後面加入COMMENT之從句，相關的語法如下：

CREATE DATABASE db_name ENGINE = engine(...) COMMENT 'Comment'

相關的SQL範例如下：

CREATE DATABASE db_comment ENGINE = Memory COMMENT 'The temporary database';
SELECT name, comment FROM system.databases WHERE name = 'db_comment';

執行上述的SQL語句與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE DATABASE db_comment ENGINE = Memory COMMENT 'The temporary database';

CREATE DATABASE db_comment
ENGINE = Memory
COMMENT 'The temporary database'

Query id: a69d0bd6-c649-4c7d-b7cd-339ac85aef14

Ok.

0 rows in set. Elapsed: 0.005 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT name, comment FROM system.databases WHERE name = 'db_comment';

SELECT
    name,
    comment
FROM system.databases
WHERE name = 'db_comment'

Query id: c080a91b-90f0-44d8-a95b-f3d59779bc20

┌─name───────┬─comment────────────────┐
│ db_comment │ The temporary database │
└────────────┴────────────────────────┘

1 row in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

從上述的輸出訊息可以得知，建立以Memory為資料庫引擎，接著使用SELECT查詢資料庫清單，並設定資料庫名稱當作查詢條件，來找到該資料庫並驗證建立資料庫的SQL語句有確切的執行。

建立資料表我們可以使用下列的SQL語法來達成：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine

我們也可以使用另一個資料表的綱要來建立新的資料表，相關的SQL語法使用範例如下：

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]

若上述的SQL範例沒有指定ENGINE的話，則會使用db2.name2的資料表相同的引擎。

也可以透過資料表函數來建立資料表，相關的SQL語法範例使用如下：

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS table_function()

也可以從SELECT查詢語句的結果來建立資料表，並且會把查詢的輸出資料寫入到新建的資料表中，相關的SQL範例語法使用如下：

CREATE TABLE [IF NOT EXISTS] [db.]table_name[(name1 [type1], name2 [type2], ...)] ENGINE = engine AS SELECT ...

相關的SQL如下：

CREATE TABLE t1 (x String) ENGINE = Memory AS SELECT 1;
SELECT x, toTypeName(x) FROM t1;

執行上述的SQL之後，輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE TABLE t1 (x String) ENGINE = Memory AS SELECT 1;

CREATE TABLE t1
(
    `x` String
)
ENGINE = Memory AS
SELECT 1

Query id: 0a434a9a-4a52-4622-9871-8f1253a90b68

Ok.

0 rows in set. Elapsed: 0.007 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT x, toTypeName(x) FROM t1;

SELECT
    x,
    toTypeName(x)
FROM t1

Query id: c459a5a2-3dd2-4113-a793-d348e2fe5785

┌─x─┬─toTypeName(x)─┐
│ 1 │ String        │
└───┴───────────────┘

1 row in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

我們也可以在建立資料表的時候，針對某個欄位設定預設值，相關的SQL語句執行與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) drop table if exists t1;

DROP TABLE t1

Query id: e416aec2-c9e6-4f09-bef5-bfdece2b8ceb

Ok.

0 rows in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE TABLE IF NOT EXISTS t1 (x String DEFAULT 'default', y String) ENGINE = Memory;

CREATE TABLE IF NOT EXISTS t1
(
    `x` String DEFAULT 'default',
    `y` String
)
ENGINE = Memory

Query id: 9a7dda59-2bb9-4340-a063-85a356760d70

Ok.

0 rows in set. Elapsed: 0.006 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) INSERT INTO t1(* EXCEPT(x)) VALUES('1')

INSERT INTO t1 (* EXCEPT x) FORMAT Values

Query id: 451f4670-828a-43ae-88e0-4c8a2833044e

Ok.

1 row in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * FROM t1;

SELECT *
FROM t1

Query id: 56ea4da9-3a30-4690-93b2-853d9684eaaa

┌─x───────┬─y─┐
│ default │ 1 │
└─────────┴───┘

1 row in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

在建立資料表的時候，可以定義並建立主鍵，建立可以在定義欄位的時候一併定義，也可以定義在定義欄位以外的地方，相關的SQL範例如下：

CREATE TABLE db.table_name
(
    name1 type1, name2 type2, ...,
    PRIMARY KEY(expr1[, expr2,...])]
)
ENGINE = engine;

CREATE TABLE db.table_name
(
    name1 type1, name2 type2, ...
)
ENGINE = engine
PRIMARY KEY(expr1[, expr2,...]);

上述兩種定義主鍵方法只能擇一，不能在同一個SQL語句中同時使用。

預設來說，ClickHouse資料庫會使用lz4的壓縮方式對指定的欄位中的資料進行壓縮，若要更換壓縮的方法，則可以在建立資料表的時候進行修改，相關的SQL範例如下：

CREATE TABLE codec_example
(
    dt Date CODEC(ZSTD),
    ts DateTime CODEC(LZ4HC),
    float_value Float32 CODEC(NONE),
    double_value Float64 CODEC(LZ4HC(9)),
    value Float32 CODEC(Delta, ZSTD)
)
ENGINE = <Engine>
...

同時也可以使用下列的方式將既有的資料表修改某個指定欄位的壓縮方式，相關的SQL語法如下：

ALTER TABLE codec_example MODIFY COLUMN float_value CODEC(Default);

原則上使用預設的壓縮方法即可，其他可以替換的壓縮方法可以參考如下的連結：

https://clickhouse.com/docs/en/sql-reference/statements/create/table#general-purpose-codecs

我們可以使用下列的方法來建立暫時的資料表，相關的SQL語法範例如下：

CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
)

暫時資料表有幾點特性需要注意的：

當資料庫連線結束時候，暫時資料表便會消失。
暫時資料表引擎只能使用Memory。
在建立暫時資料表時，無法指定資料庫，因為暫時資料表建立在資料庫以外的地方。
無法使用DDL(分散式的SQL語句)來建立暫時資料表，因為暫時資料表只建立在當前的資料庫連線中。
當暫時的資料表名稱與某個一般的資料表名稱同個名稱，若不指定資料庫的名稱的話，優先會使用暫時資料表。
當使用分散式的查詢資料時，暫時的資料表會被傳送到遠端的資料庫伺服器進行查詢處理。

在建立資料表的時候，可以搭配REPLACE將既有的資料刪除資料並重新建立，相關的SQL範例語法的使用如下：

{CREATE [OR REPLACE] | REPLACE} TABLE [db.]table_name

下列的SQL範例如下：

CREATE DATABASE base ENGINE = Atomic;
CREATE OR REPLACE TABLE base.t1 (n UInt64, s String) ENGINE = MergeTree ORDER BY n;
INSERT INTO base.t1 VALUES (1, 'test');
SELECT * FROM base.t1;

執行上述的SQL語句與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE DATABASE base ENGINE = Atomic;

CREATE DATABASE base
ENGINE = Atomic

Query id: 38b0a87e-fbb1-4f84-aaa2-6f17add7df39

Ok.

0 rows in set. Elapsed: 0.007 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE OR REPLACE TABLE base.t1 (n UInt64, s String) ENGINE = MergeTree ORDER BY n;

CREATE OR REPLACE TABLE base.t1
(
    `n` UInt64,
    `s` String
)
ENGINE = MergeTree
ORDER BY n

Query id: 30487f6e-e0bd-4b9f-9200-eb65eef67910

Ok.

0 rows in set. Elapsed: 0.012 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) INSERT INTO base.t1 VALUES (1, 'test');

INSERT INTO base.t1 FORMAT Values

Query id: ce678317-f770-4769-9a51-9175e3ca7625

Ok.

1 row in set. Elapsed: 0.003 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * FROM base.t1;

SELECT *
FROM base.t1

Query id: 317490b0-1c9c-4239-bf17-b42b1311a171

┌─n─┬─s────┐
│ 1 │ test │
└───┴──────┘

1 row in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

接著我們可以用REPLACE來清空上述的資料表資料，相關的執行SQL語句與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE OR REPLACE TABLE base.t1 (n UInt64, s Nullable(String)) ENGINE = MergeTree ORDER BY n;

CREATE OR REPLACE TABLE base.t1
(
    `n` UInt64,
    `s` Nullable(String)
)
ENGINE = MergeTree
ORDER BY n

Query id: 4e001306-6e7b-4cd8-bcdf-fd6002a5e562

Ok.

0 rows in set. Elapsed: 0.008 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) INSERT INTO base.t1 VALUES (2, null);

INSERT INTO base.t1 FORMAT Values

Query id: 08b1bcb4-68cb-4a18-86fd-31fc39ab68a6

Ok.

1 row in set. Elapsed: 0.003 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * FROM base.t1;

SELECT *
FROM base.t1

Query id: 475b8cda-6697-4638-b7c5-be37e94016f0

┌─n─┬─s────┐
│ 2 │ ᴺᵁᴸᴸ │
└───┴──────┘

1 row in set. Elapsed: 0.003 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

我們也可以用REPLACE語句來更改資料表的綱要，相關的執行SQL語句與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) REPLACE TABLE base.t1 (n UInt64) ENGINE = MergeTree ORDER BY n;

REPLACE TABLE base.t1
(
    `n` UInt64
)
ENGINE = MergeTree
ORDER BY n

Query id: ec2cf3ae-1eff-437f-95f9-eb65f54d98b8

Ok.

0 rows in set. Elapsed: 0.008 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) INSERT INTO base.t1 VALUES (3);

INSERT INTO base.t1 FORMAT Values

Query id: 2afaea02-a8f5-41ff-9625-229f6f644c09

Ok.

1 row in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * FROM base.t1;

SELECT *
FROM base.t1

Query id: 867d2462-781a-4241-989e-3f328e9b651e

┌─n─┐
│ 3 │
└───┘

1 row in set. Elapsed: 0.003 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

我們也可以建立資料表的時候，加入COMMENT從句來對要建立的資料表進行註解，相關的SQL語法範例如下：

CREATE TABLE db.table_name
(
    name1 type1, name2 type2, ...
)
ENGINE = engine
COMMENT 'Comment'

考慮下列的查詢：

CREATE TABLE t1 (x String) ENGINE = Memory COMMENT 'The temporary table';
SELECT name, comment FROM system.tables WHERE name = 't1';

執行上述的SQL語句與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE TABLE t1 (x String) ENGINE = Memory COMMENT 'The temporary table';

CREATE TABLE t1
(
    `x` String
)
ENGINE = Memory
COMMENT 'The temporary table'

Query id: d295fce6-8558-467e-bc79-341a65ee25a7

Ok.

0 rows in set. Elapsed: 0.005 sec.
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT name, comment FROM system.tables WHERE name = 't1';

SELECT
    name,
    comment
FROM system.tables
WHERE name = 't1'

Query id: c93a437b-cacc-48fb-a2f9-5c21fa9aeab8

┌─name─┬─comment─────────────┐
│ t1   │ The temporary table │
└──────┴─────────────────────┘

2 rows in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

我們也可以建立視圖(VIEW)，以下範例是建立VIEW的SQL範例語法：

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] AS SELECT ...

上述的建立的普通視圖不會儲存任何的資料，只會去執行指定的SELECT語句去執行查詢結果後，讀取指定的資料表中的資料結果出來。

以下面範例來說，假設已經透過CREATE VIEW view AS SELECT ...的語句建立VIEW之後，接著可以執行下列的SQL查詢：

SELECT a, b, c FROM view

上述的SQL查詢語句可以完全的等價於下列的SQL查詢語句：

SELECT a, b, c FROM (SELECT ...)

可以透過下列的SQL語法來建立Materialized View，相關的SQL語法如下：

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

我們也可以透過下列的SQL範例來建立DICTIONARY，這是從另一個資料表來建立的，該資料表可能是在遠端的資料庫中，也有可能是本地端的資料庫中，相關的SQL語法範例如下：

CREATE [OR REPLACE] DICTIONARY [IF NOT EXISTS] [db.]dictionary_name [ON CLUSTER cluster]
(
    key1 type1  [DEFAULT|EXPRESSION expr1] [IS_OBJECT_ID],
    key2 type2  [DEFAULT|EXPRESSION expr2],
    attr1 type2 [DEFAULT|EXPRESSION expr3] [HIERARCHICAL|INJECTIVE],
    attr2 type2 [DEFAULT|EXPRESSION expr4] [HIERARCHICAL|INJECTIVE]
)
PRIMARY KEY key1, key2
SOURCE(SOURCE_NAME([param1 value1 ... paramN valueN]))
LAYOUT(LAYOUT_NAME([param_name param_value]))
LIFETIME({MIN min_val MAX max_val | max_val})
SETTINGS(setting_name = setting_value, setting_name = setting_value, ...)
COMMENT 'Comment'

假設有個資料表如下：

┌─id─┬─value──┐
│  1 │ First  │
│  2 │ Second │
└────┴────────┘

建立DICTIONARY的SQL語句方式如下：

CREATE DICTIONARY dictionary_with_comment
(
    id UInt64,
    value String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(HOST 'localhost' PORT tcpPort() TABLE 'source_table'))
LAYOUT(FLAT())
LIFETIME(MIN 0 MAX 1000)
COMMENT 'The temporary dictionary';

建立好DICTIONARY之後，可以使用下列的SQL語句來查看上述建立的DICTIONARY的綱要：

SHOW CREATE DICTIONARY dictionary_with_comment;

輸出的結果會是如下：

┌─statement───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE DICTIONARY default.dictionary_with_comment
(
    `id` UInt64,
    `value` String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(HOST 'localhost' PORT tcpPort() TABLE 'source_table'))
LIFETIME(MIN 0 MAX 1000)
LAYOUT(FLAT())
COMMENT 'The temporary dictionary' │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

我們也可以透過CREATE FUNCTION建立使用者定義的函數，相關的SQL範例語法使用如下：

CREATE FUNCTION name [ON CLUSTER cluster] AS (parameter0, ...) -> expression

相關的SQL範例如下：

CREATE FUNCTION linear_equation AS (x, k, b) -> k*x + b;
SELECT number, linear_equation(number, 2, 1) FROM numbers(3);

執行上述的SQL語句之後，輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE FUNCTION linear_equation AS (x, k, b) -> k*x + b;

CREATE FUNCTION linear_equation AS (x, k, b) -> ((k * x) + b)

Query id: 045b622a-db0e-468c-8f86-785e9888c078

Ok.

0 rows in set. Elapsed: 0.006 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT number, linear_equation(number, 2, 1) FROM numbers(3);

SELECT
    number,
    linear_equation(number, 2, 1)
FROM numbers(3)

Query id: 5ca29853-9003-4c1c-a66d-4e998d4971c6

┌─number─┬─plus(multiply(2, number), 1)─┐
│      0 │                            1 │
│      1 │                            3 │
│      2 │                            5 │
└────────┴──────────────────────────────┘

3 rows in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

從上述的輸出訊息可以知道，我們先建立一個函數叫做linear_equation並裡面有一個公式，未知數分別有x、k與b等參數，接著會進行k * x + b的公式運算後回傳結果。

接著使用SELECT從numbers(3)撈出序列數字的資料表，裡面有number的欄位，其資料有0、1與2，透過linear_equation(number, 2, 1)函數計算後分別是1、3與5，並將計算結果與number欄位的資料輸出出來。

我們也可以建立含有條件的函數，相關的SQL範例如下：

CREATE FUNCTION parity_str AS (n) -> if(n % 2, 'odd', 'even');
SELECT number, parity_str(number) FROM numbers(3);

執行上述的SQL語句之後，輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE FUNCTION parity_str AS (n) -> if(n % 2, 'odd', 'even');

CREATE FUNCTION parity_str AS n -> if(n % 2, 'odd', 'even')

Query id: ffd26843-d3c8-421e-b21a-880fd81a0d25

Ok.

0 rows in set. Elapsed: 0.008 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT number, parity_str(number) FROM numbers(3);

SELECT
    number,
    parity_str(number)
FROM numbers(3)

Query id: 118b4315-ce48-4ef9-b0e0-650af51a7dc4

┌─number─┬─if(modulo(number, 2), 'odd', 'even')─┐
│      0 │ even                                 │
│      1 │ odd                                  │
│      2 │ even                                 │
└────────┴──────────────────────────────────────┘

3 rows in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

從上述的輸出訊息可以知道，首先我們先建立了一個函數叫做parity_str，函數裡面有一個if函式，用來判斷當輸入的未知數n是否可以被2整除，可以的話則回傳even，不可以則回傳odd。

建立函數的SQL也可以等價於CREATE FUNCTION parity_str AS n -> if((n % 2) = 0, 'odd', 'even')。

建立好上述的函數後，接著執行SELECT查詢語句，從numbers(3)撈出資料表，欄位有number，資料有0、1與2，並分別利用parity_str函數進行運算並輸出結果該欄位上。