iT邦幫忙

2022 iThome 鐵人賽
DAY 15
0
Software Development

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

day15-SQL使用與操作方法介紹(六)

14th鐵人賽
1252 瀏覽

  • 分享至 

  • xImage
    •  

前言

從上個章節可以知道在SELECT語句中後面可以接的從句(clause)，在本章節中將會繼續介紹其他的從句。

LIMIT clause

此從句指的語法有兩種：

  • LIMIT n，指的是將查詢出來的結果印出前n筆的資料，也可以等價於LIMIT 0,n。
  • LIMIT n, m，指的是將查詢的結果先忽略n筆資料後並輸出前m筆資料，也可以等價於LIMIT m OFFSET n之從句。

參考下面的SQL範例：

SELECT * FROM (
    SELECT number%50 AS n FROM numbers(100)
) ORDER BY n LIMIT 0,5

上述的SQL語句的運作方式如下：

  • 首先，會產生一個數字資料表，裡面只有number的欄位，數值為從0到99。
  • 接著將此資料表中的number欄位各別除以50並取餘數並將此欄位當作別名n，之後得到此結果的資料表。
  • 將上述的資料表以n欄位的值由小到大排列之後，忽略0行並輸出前5筆的資料。

將上述的SQL語句執行之後，會得到的下列的結果：

┌─n─┐
│ 0 │
│ 0 │
│ 1 │
│ 1 │
│ 2 │
└───┘

OFFSET clause

OFFSET和FETCH從可以允許我們按照將查詢結果中，忽略某幾筆資料後，擷取前幾筆的資料，相關的語法如下：

OFFSET offset_row_count {ROW | ROWS}] [FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} {ONLY | WITH TIES}]

以下為一個SQL語句範例：

SELECT * FROM test_fetch ORDER BY a OFFSET 1 ROW FETCH FIRST 3 ROWS ONLY;

上述的SQL語句可以等價成下列的SQL語句：

SELECT * FROM test_fetch ORDER BY a LIMIT 3 OFFSET 1;

考慮下列的資料表：

┌─a─┬─b─┐
│ 1 │ 1 │
│ 2 │ 1 │
│ 3 │ 4 │
│ 1 │ 3 │
│ 5 │ 4 │
│ 0 │ 6 │
│ 5 │ 7 │
└───┴───┘

接著使用下列的SQL語句對上述的資料表進行查詢：

SELECT * FROM test_fetch ORDER BY a OFFSET 3 ROW FETCH FIRST 3 ROWS ONLY;

執行上述的SQL語句之後，則會得到下列的結果資料表：

┌─a─┬─b─┐
│ 2 │ 1 │
│ 3 │ 4 │
│ 5 │ 4 │
└───┴───┘

接著考慮下列的SQL語句：

SELECT * FROM test_fetch ORDER BY a OFFSET 3 ROW FETCH FIRST 3 ROWS WITH TIES;

執行上述的SQL語句之後，會得到下列的結果資料表：

┌─a─┬─b─┐
│ 2 │ 1 │
│ 3 │ 4 │
│ 5 │ 4 │
│ 5 │ 7 │
└───┴───┘

從上述的結果資料與SQL語句可以知道，WITH ONLY指的是只會印出忽略掉3行資料之後，指印出前3行的資料出來；而WITH TIES則是忽略掉3行資料之後，若印出3行資料出來，後面還有資料的話，則一併將後面的資料也輸出出來。

UNION clause

此從句指的聯集的意思，可以是UNION ALL與UNION DISTINCT從句，若沒有指定的話，則會取決於資料庫設定之union_default_mode設定值。上述這兩者的差別是，UNION DISTINCT會將資料進行獨特的篩選，有重複的資料行數會被刪除，而UNION DISTINCT也可以等價成將每個資料表的SQL查詢語句使用SELECT DISTINCT並使用UNION ALL將各個資料表聯集起來的結果。

以下是一個用UNION ALL聯集的SQL查詢範例：

SELECT CounterID, 1 AS table, toInt64(count()) AS c
    FROM test.hits
    GROUP BY CounterID

UNION ALL

SELECT CounterID, 2 AS table, sum(Sign) AS c
    FROM test.visits
    GROUP BY CounterID
    HAVING c > 0

考慮以下的SQL語句範例：

SET union_default_mode = 'DISTINCT';
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 2;

從上述的SQL範例，首先先用SET語句動態的設定union_default_mode為DISTINCT，因此只使用UNION從句時會把重複的資料進行刪除，因此完成此設定之後，執行SQL語句得到的結果如下：

┌─1─┐
│ 1 │
└───┘
┌─1─┐
│ 2 │
└───┘
┌─1─┐
│ 3 │
└───┘

若設定預設為ALL時，則不會去掉重複的資料，相關執行的SQL範例如下：

SET union_default_mode = 'ALL';
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 2;

得到的結果如下列表所示：

┌─1─┐
│ 3 │
└───┘
┌─1─┐
│ 2 │
└───┘
┌─1─┐
│ 1 │
└───┘
┌─1─┐
│ 2 │
└───┘

因為沒有使用ORDER BY從句進行排序，因此每次得到的聯集結果的資料表中的資料順序會不一樣。

INTERSECT clause

INTERSECT從句指的是交集的意思，則是兩個資料表的結果中的資料兩個資料表都要有才會將此筆資料留下，並輸出交集之後的資料表結果，交集的從句會比聯集UNION與EXCEPT從句的優先權高。

相關的使用語法如下：

SELECT column1 [, column2 ]
FROM table1
[WHERE condition]

INTERSECT

SELECT column1 [, column2 ]
FROM table2
[WHERE condition]

從上述的語法來看，WHERE從句中的條件式可以按照自己的需求加入在SELECT查詢語句中。

SQL交集應用範例如下：

SELECT number FROM numbers(1,10) INTERSECT SELECT number FROM numbers(3,6);

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

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

SELECT number
FROM numbers(1, 10)
INTERSECT
SELECT number
FROM numbers(3, 6)

Query id: 04b70d0b-14a1-4d9b-88f8-7e020f7f0884

┌─number─┐
│      3 │
│      4 │
│      5 │
│      6 │
│      7 │
│      8 │
└────────┘

6 rows in set. Elapsed: 0.002 sec.

從上述的輸出訊息可以知道，在前面章節中有講述過numbers的用法，因此第一個資料表會產生從1到10含有number欄位的資料表；第二個資料表則是產生從3到8含有number欄位的資料表，這兩個資料表進行交集之後，可以得到共同的數值為3、4、5、6、7與8並輸出交集過後的結果資料表。

EXCEPT clause

即差集的從句，指的是第一個SQL查詢結果的資料不在第二個查詢資料之中的資料，並將這些資料輸出成結果的資料表，相關的語句用法如下：

SELECT column1 [, column2 ]
FROM table1
[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]
FROM table2
[WHERE condition]

從上述的語法來看，WHERE從句中的條件式可以按照自己的需求加入在SELECT查詢語句中。

下列的SQL範例如下：

SELECT number FROM numbers(1,10) EXCEPT SELECT number FROM numbers(3,6);

透過ClickHouse客戶端執行完上述的SQL語句之後，輸出的訊息如下：

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

SELECT number
FROM numbers(1, 10)
EXCEPT
SELECT number
FROM numbers(3, 6)

Query id: a4210a15-dc46-4015-8c3a-82e7bb2e0b0b

┌─number─┐
│      1 │
│      2 │
│      9 │
│     10 │
└────────┘

4 rows in set. Elapsed: 0.002 sec.

從上述的輸出結果可以知道，1、2、9與10的數字在第一張資料表但是不在第二張資料表。

接著我們再看下列的SQL範例：

CREATE TABLE t1(one String, two String, three String) ENGINE=Memory();
CREATE TABLE t2(four String, five String, six String) ENGINE=Memory();

INSERT INTO t1 VALUES ('q', 'm', 'b'), ('s', 'd', 'f'), ('l', 'p', 'o'), ('s', 'd', 'f'), ('s', 'd', 'f'), ('k', 't', 'd'), ('l', 'p', 'o');
INSERT INTO t2 VALUES ('q', 'm', 'b'), ('b', 'd', 'k'), ('s', 'y', 't'), ('s', 'd', 'f'), ('m', 'f', 'o'), ('k', 'k', 'd');

SELECT * FROM t1 EXCEPT SELECT * FROM t2;

接著將上述的SQL執行完成之後，輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE TABLE t1(one String, two String, three String) ENGINE=Memory();

CREATE TABLE t1
(
    `one` String,
    `two` String,
    `three` String
)
ENGINE = Memory

Query id: 5e7a2ac9-f9af-4b6e-b434-5bec265922a5

Ok.

0 rows in set. Elapsed: 0.006 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE TABLE t2(four String, five String, six String) ENGINE=Memory();

CREATE TABLE t2
(
    `four` String,
    `five` String,
    `six` String
)
ENGINE = Memory

Query id: ec7b94f7-db30-45e7-9ad2-f1ad9e3f01f6

Ok.

0 rows in set. Elapsed: 0.007 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) INSERT INTO t1 VALUES ('q', 'm', 'b'), ('s', 'd', 'f'), ('l', 'p', 'o'), ('s', 'd', 'f'), ('s', 'd', 'f'), ('k', 't', 'd'), ('l', 'p', 'o');

INSERT INTO t1 FORMAT Values

Query id: 3973cee3-4f4d-4c16-85f4-d6909d01b2c2

Ok.

7 rows in set. Elapsed: 0.004 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) INSERT INTO t2 VALUES ('q', 'm', 'b'), ('b', 'd', 'k'), ('s', 'y', 't'), ('s', 'd', 'f'), ('m', 'f', 'o'), ('k', 'k', 'd');

INSERT INTO t2 FORMAT Values

Query id: 24d237e3-fd22-43a6-a9f3-e9b4e80ced87

Ok.

6 rows in set. Elapsed: 0.002 sec.

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

SELECT *
FROM t1
EXCEPT
SELECT *
FROM t2

Query id: cd83dee2-542a-4a6b-9c7f-9544702b407e

┌─one─┬─two─┬─three─┐
│ l   │ p   │ o     │
│ k   │ t   │ d     │
│ l   │ p   │ o     │
└─────┴─────┴───────┘

3 rows in set. Elapsed: 0.002 sec.

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

從上述的輸出訊息可以知道，第一張資料表中先遇到('l', 'p', 'o')此筆資料不在第二張資料表，接著('k', 't', 'd')此筆資料也是不在第二張資料表，最後('l', 'p', 'o')此筆資料不在第二張資料表，最後就將這三筆資料組合結果輸出資料表出來。

INTO OUTFILE clause

這個從句是將SELECT查詢語句的結果導向成檔案並將此檔案儲存在客戶端上面，也可以把檔案進行壓縮，因此可以加入COMPRESSION的從句做使用。

相關的語法使用方式如下：

SELECT <expr_list> INTO OUTFILE file_name [AND STDOUT] [COMPRESSION type [LEVEL level]]

從上面的語法來看，file_name是檔案名稱，可以是字串；而type為壓縮的類型，可以是none、gzip、deflate、br、xz、zstd、lz4與bz2，level則是壓縮的層級數字為正整數，lz4的壓縮類型層級可以是1到12之間的正整數，zstd的壓縮類型層級可以是1到22之間的正整數，而其他的壓縮類型層級為1到9之間的正整數。

若沒有設定FORMAT從句的話，則預設的檔案格式為TSV的格式，此檔案格式是以TAB的字元作為分隔，相關的SQL範例如下：

SELECT 1,'ABC' INTO OUTFILE 'select.gz' FORMAT CSV

將上述的SQL透過ClickHouse客戶端執行之後輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT 1,'ABC' INTO OUTFILE 'select.gz' FORMAT CSV

SELECT
    1,
    'ABC'
INTO OUTFILE 'select.gz'
FORMAT CSV

Query id: 4ea3d62e-c0b8-4dcb-adb8-05c95b7a4583


1 row in set. Elapsed: 0.006 sec.

從上述的輸出訊息來看，將1,'ABC'的資料輸出成CSV檔案並將此檔案壓縮成select.gz的壓縮檔案，我們可以在終端機中使用file指令確認壓縮檔案是Gzip檔；使用zcat指令來查看壓縮檔案裡面的資料，相關的操作如下所示：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ file select.gz
select.gz: gzip compressed data, from Unix
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ zcat select.gz
1,"ABC"

若指定的檔案已經存在，則ClickHouse客戶端不會覆蓋檔案而是會跳出錯誤，相關的執行輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT 1,'ABC' INTO OUTFILE 'select.gz' FORMAT CSV

SELECT
    1,
    'ABC'
INTO OUTFILE 'select.gz'
FORMAT CSV

Query id: a53ef319-b6c0-4f8b-8532-3859721ac11d

Ok.
Exception on client:
Code: 76. DB::Exception: Code: 76. DB::ErrnoException: Cannot open file select.gz, errno: 17, strerror: File exists. (CANNOT_OPEN_FILE) (version 22.8.4.7 (official build)). (CANNOT_OPEN_FILE)

若指定的不是壓縮檔的檔名，則檔案所輸出的內容會依照FORMAT所指定的檔案格式並存入到指定的file_name中，相關的執行SQL與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT 1,'ABC' INTO OUTFILE 'select.csv' FORMAT CSV

SELECT
    1,
    'ABC'
INTO OUTFILE 'select.csv'
FORMAT CSV

Query id: 29d435b4-995d-48a8-b09b-5893dd45cd01


1 row in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) exit
Bye.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ cat select.csv
1,"ABC"

若要指定壓縮類型的等級的話，則可以參考下列的SQL範例：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT 1,'ABC' INTO OUTFILE 'select.gz' COMPRESSION 'gzip' LEVEL 1 FORMAT CSV

SELECT
    1,
    'ABC'
INTO OUTFILE 'select.gz'
FORMAT CSV

Query id: 477c3322-bc03-4ae0-a518-a5c9195c8807


1 row in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) exit
Bye.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ zcat select.gz
1,"ABC"
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ file select.gz
select.gz: gzip compressed data, from Unix

FORMAT clause

此從句在INTO OUTFILE從句就有使用到，若沒有使用此從句的話，則預設是使用TSV檔案格式進行檔案的輸出，相關可以輸出的檔案格式可以參考下列網站：

https://clickhouse.com/docs/en/interfaces/formats

從上述的網站中，可以看到一個表格，裡面有Format、Input與Output的欄位，分別代表是檔案格式名稱、是否可以當作輸入的檔案格式以及是否可以將資料以此檔案格式進行輸出。

結論

在本章節中，我們將剩下的SELECT查詢語句後面可以接的從句的用法進行介紹，在下一章節中，我們將會介紹寫入資料(INSERT INTO)與建立(CREATE)等SQL語句的使用方式。

參考資料


上一篇
day14-SQL使用與操作方法介紹(五)
下一篇
day16-SQL使用與操作方法介紹(七)
系列文
ClickHouse:時序資料庫建置與運行 共 30 篇
  1. 26
    day26-ClickHouse 客戶端連線介面方式(五)
  2. 27
    day27-ClickHouse 第三方客戶端連線介面方式
  3. 28
    day28-設定資料庫SSL憑證安全連線
  4. 29
    day29-設定資料庫叢集分片與複製節點
  5. 30
    day30-ClickHouse資料庫的故障排除整理
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22212 篇
完賽人數
602 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙