iT邦幫忙

2022 iThome 鐵人賽
DAY 18
0
Software Development

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

day18-SQL使用與操作方法介紹(九)

14th鐵人賽
1479 瀏覽

  • 分享至 

  • xImage
    •  

前言

本章節中，我們繼續介紹其他的SQL用法與相關的語法範例。

GRANT語句

  1. GRANT可以用來指定某個使用者設定帳號或是角色。
  2. 可以透過GRANT指定多個角色給某個使用者。

相關的語法使用如下：

GRANT [ON CLUSTER cluster_name] privilege[(column_name [,...])] [,...] ON {db.table|db.*|*.*|table|*} TO {user | role | CURRENT_USER} [,...] [WITH GRANT OPTION] [WITH REPLACE OPTION]

從上述的語法可以得知，privilege是使用者權限定義；而role是ClickHouse資料庫角色，user則是ClickHouse資料庫使用者的帳號。

下列的語法使用是指定某個使用者帳號賦予角色的方式：

GRANT [ON CLUSTER cluster_name] role [,...] TO {user | another_role | CURRENT_USER} [,...] [WITH ADMIN OPTION] [WITH REPLACE OPTION]

下列是使用範例：

GRANT SELECT(x,y) ON db.table TO john WITH GRANT OPTION

從上述的執行語法，我們可以知道指定john使用者可以在某個資料庫底下的資料表進行下列的SELECT的x與y欄位權限，相關可以執行的語法如下：

  • SELECT x,y FROM db.table
  • SELECT x FROM db.table
  • SELECT y FROM db.table

若是使用*來表示權限的設定，則是指定使用者可以執行任意有關於SELECT所有的權限，相關的執行語法範例如下：

GRANT SELECT, INSERT ON *.* TO john, robin

從上述語法可以知道，指定john與robin的使用者可以使用有關於任意SELECT與INSERT的權限。

更多的權限存取清單可以參考此列表。

REVOKE語句

我們可以使用此語句將某個使用者所設定的權限進行撤銷，相關的語法使用如下：

REVOKE [ON CLUSTER cluster_name] privilege[(column_name [,...])] [,...] ON {db.table|db.*|*.*|table|*} FROM {user | CURRENT_USER} [,...] | ALL | ALL EXCEPT {user | CURRENT_USER} [,...]

從上述的語法，我們可以將指定的使用者的權限進行取消。

透過下列的語法則可以將指定的使用者角色進行取消：

REVOKE [ON CLUSTER cluster_name] [ADMIN OPTION FOR] role [,...] FROM {user | role | CURRENT_USER} [,...] | ALL | ALL EXCEPT {user_name | role_name | CURRENT_USER} [,...]

相關上述的語法範例如下：

GRANT SELECT ON *.* TO john;
REVOKE SELECT ON accounts.* FROM john;

從上述的語法可以知道，首先先使用GRANT語句將john使用者設定有所有SELECT權限，接著再撤銷accounts資料庫SELECT之權限，意思就是john除了accounts資料庫之外，其他的資料庫都可以進行查詢SELECT權限。

GRANT SELECT ON accounts.staff TO mira;
REVOKE SELECT(wage) ON accounts.staff FROM mira;

從上述的語法可以知道，先將mira使用者設定對accounts資料庫中的staff資料表進行查詢的權限，接著再撤銷staff資料表中的wage欄位的查詢。換句話說就是mira使用者除了accounts資料庫的staff資料表中的wage欄位不可以查詢之外，其他在該資料表中的欄位都可以進行SELECT查詢。

ATTACH語句

我們可以使用ATTACH語句將指定且已經存在的資料表移到另一個資料庫或是另一台資料庫伺服器上，相關的語法如下：

ATTACH TABLE [IF NOT EXISTS] [db.]name [ON CLUSTER cluster]

或者可以使用下列的語法先指定資料檔案來建立新的資料表，相關的語法如下：

ATTACH TABLE name FROM 'path/to/data/' (col1 Type1, ...)

相關的語法範例如下：

DROP TABLE IF EXISTS test;
INSERT INTO TABLE FUNCTION file('01188_attach/test/data.TSV', 'TSV', 's String, n UInt8') VALUES ('test', 42);
ATTACH TABLE test FROM '01188_attach/test' (s String, n UInt8) ENGINE = File(TSV);
SELECT * FROM test;

從上述的SQL範例可以得知，首先若test資料表存在的話，先將test資料表進行移除，接著使用內建的file函式指定某個路徑下的TSV檔進行內容儲存；接著使用ATTACH語句將指定儲存的TSV檔轉成test資料表。

若資料庫引擎使用的是Atomic的話，則可以指定資料表的UUID來執行ATTACH語句，相關的範例如下：

ATTACH TABLE name UUID '<uuid>' (col1 Type1, ...)

我們也可以使用下列的語法將已經存在的Dictinoary進行移動，相關的語法使用如下：

ATTACH DICTIONARY [IF NOT EXISTS] [db.]name [ON CLUSTER cluster]

CHECK TABLE語句

我們可以使用下列的語法來檢查指定的資料表情形與健康狀態，相關的語法範例如下：

CHECK TABLE [db.]name

執行上述的語句範例如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CHECK TABLE simple_table;

CHECK TABLE simple_table

Query id: 8a667a48-bf42-4cf7-a3c7-177ca982ed19

┌─result─┐
│      1 │
└────────┘

1 row in set. Elapsed: 0.001 sec.

檢查資料表只會有一個result欄位，其欄位的型別為布林值，分別代表的意思如下：

  • 若欄位的值為0，則該資料表的資料是損壞的。
  • 若欄位的值為1，則該資料表的資料是良好的。

此語句只支援下列這些的資料表引擎：

  • Log
  • TinyLog
  • StripeLog
  • MergeTree family；即MergeTree系列的資料庫引擎

對於檢查MergeTree家族系列的資料表引擎，若設定check_query_single_value_result值為0的話，則表示將資料表中每個資料部分進行資料的檢查，相關的語句執行如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SET check_query_single_value_result = 0;

SET check_query_single_value_result = 0

Query id: 3192a8c9-7b6a-4773-a65b-5b6ce940ff34

Ok.

0 rows in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CHECK TABLE simple_table;

CHECK TABLE simple_table

Query id: 7c276ff4-6dad-4cff-824c-61cc24ca980f

┌─part_path─┬─is_passed─┬─message─┐
│ all_1_1_0 │         1 │         │
└───────────┴───────────┴─────────┘

1 row in set. Elapsed: 0.001 sec.

若是將``設定為1，則只會檢查一般資料表目前的狀態，相關的語法執行如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SET check_query_single_value_result = 1;

SET check_query_single_value_result = 1

Query id: 83a5b521-6955-42da-9137-303467c0eb7e

Ok.

0 rows in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CHECK TABLE simple_table;

CHECK TABLE simple_table

Query id: 786d62f9-cdb6-4b5f-bad1-17b0771f753b

┌─result─┐
│      1 │
└────────┘

1 row in set. Elapsed: 0.002 sec.

如果檢查出資料表的狀態是損壞的話，可以按照下列的步驟進行操作：

  • 建立一個與損壞的資料表一模一樣的綱要，可以透過CREATE TABLE <new_table_name> AS <damaged_table_name>此SQL語句來完成。
  • 設定max_threads之設定值為1使用單一執行緒處理之後的查詢，我們可以使用SET max_threads = 1的SQL語句來完成此設定。
  • 執行INSERT INTO <new_table_name> SELECT * FROM <damaged_table_name>的SQL語句，這個SQL語句會複製在損壞資料表中尚未損壞的資料寫入到新建的資料表中。
    • 這些未損壞的資料只有在損壞的區塊(part)之前的資料才會被複製。
  • 重新啟動clickhouse-clientClickHouse資料庫客戶端來重置max_threads之設定，相關重啟設定的方式過程如下：
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SET max_threads=1;

SET max_threads = 1

Query id: 1908141f-321c-4f04-b2b2-825e05b61486

Ok.

0 rows in set. Elapsed: 0.001 sec.
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SHOW SETTINGS LIKE '%max_threads%';

SHOW SETTINGS LIKE '%max_threads%'

Query id: a6657d91-cb79-4990-928f-942eeaae7f2e

┌─name─────────────────────────────┬─type───────┬─value─┐
│ max_threads                      │ MaxThreads │ 1     │
│ max_streams_to_max_threads_ratio │ Float      │ 1     │
└──────────────────────────────────┴────────────┴───────┘

2 rows in set. Elapsed: 0.004 sec.
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) exit;
root@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~# clickhouse-client --password
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 :) SHOW SETTINGS LIKE '%max_threads%';

SHOW SETTINGS LIKE '%max_threads%'

Query id: 5ded23d5-d761-422e-b6d1-a7661ea77e73

┌─name─────────────────────────────┬─type───────┬─value─────┐
│ max_threads                      │ MaxThreads │ 'auto(4)' │
│ max_streams_to_max_threads_ratio │ Float      │ 1         │
└──────────────────────────────────┴────────────┴───────────┘

2 rows in set. Elapsed: 0.002 sec.

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

DESCRIBE TABLE語句

我們可以透過此SQL語句來檢查一個資料表的綱要與結構，相關的語法使用如下：

DESC|DESCRIBE TABLE [db.]table [INTO OUTFILE filename] [FORMAT format]

執行完上述的SQL語法之後，產生的結果資料表，相關的欄位說明如下：

  • name — 一個欄位名稱。
  • type — 一個欄位的型別。
  • default_type — 在在此欄位的設定的預設類型，可能是DEFAULT、MATERIALIZED或是 ALIAS
    • 如果沒有預設描述式，則該欄位顯示空字串。
  • default_expression — 接在DEFAULT從句後面的描述式。
  • comment — 一個欄位的註解。
  • codec_expression — 給定欄位的編碼。
  • ttl_expression — TTL的描述式。
  • is_subcolumn — 是否是子欄位，像是欄位使用Tuple則會包含有子欄位，此欄位出現與describe_include_subcolumns之設定有關，若設定為1，則會顯示該欄位，預設為0。

相關的SQL語句執行所輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE TABLE describe_example (
                                      id UInt64, text String DEFAULT 'unknown' CODEC(ZSTD),
                                      user Tuple (name String, age UInt8)
                                  ) ENGINE = MergeTree() ORDER BY id;

CREATE TABLE describe_example
(
    `id` UInt64,
    `text` String DEFAULT 'unknown' CODEC(ZSTD),
    `user` Tuple(name String, age UInt8)
)
ENGINE = MergeTree
ORDER BY id

Query id: 5acb4055-10db-4923-bd4f-ba09f78a8ce6

Ok.

0 rows in set. Elapsed: 0.007 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) DESCRIBE TABLE describe_example;

DESCRIBE TABLE describe_example

Query id: 56f89af4-b87d-4236-88cd-d13876086649

┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ UInt64                        │              │                    │         │                  │                │
│ text │ String                        │ DEFAULT      │ 'unknown'          │         │ ZSTD(1)          │                │
│ user │ Tuple(name String, age UInt8) │              │                    │         │                  │                │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

3 rows in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) DESCRIBE TABLE describe_example SETTINGS describe_include_subcolumns=1;

DESCRIBE TABLE describe_example
SETTINGS describe_include_subcolumns = 1

Query id: e479ef37-bcac-4bb5-8217-95cf39aedbec

┌─name──────┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┬─is_subcolumn─┐
│ id        │ UInt64                        │              │                    │         │                  │                │            0 │
│ text      │ String                        │ DEFAULT      │ 'unknown'          │         │ ZSTD(1)          │                │            0 │
│ user      │ Tuple(name String, age UInt8) │              │                    │         │                  │                │            0 │
│ user.name │ String                        │              │                    │         │                  │                │            1 │
│ user.age  │ UInt8                         │              │                    │         │                  │                │            1 │
└───────────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┴──────────────┘

5 rows in set. Elapsed: 0.001 sec.

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

從上述的最後一個執行的SQL語句可以知道，在執行DESCRIBE TABLE describe_example後面接設定describe_include_subcolumns為1時，則會多輸出is_subcolumn欄位出來。

因為user欄位為Tuple，因此是有子欄位，子欄位有name與age。

DETACH語句

我們可以透過此SQL語句將指定的資料表、物化的視圖(Materalized view)或是字典(dictionary)進行遺忘的動作，當設定資料表為遺忘的時候，需要注意下列的事項：

  • 無法針對遺忘的資料表進行刪除，即執行DROP語句。
  • 無法執行CREATE TABLE的語句建立和遺忘的資料表名稱相同的資料表，遺忘的資料表需要在執行DETACH語句時候設定為PERMANENTLY才會有影響。
  • 無法執行RENAME TABLE語句將遺忘的資料表進行重新命名。

相關的語法如下：

DETACH TABLE|VIEW|DICTIONARY [IF EXISTS] [db.]name [ON CLUSTER cluster] [PERMANENTLY] [SYNC]

若執行DETACH語句時候，沒有設定PERMANENTLY時，則在ClickHouse資料庫伺服器在重啟時，則可以再針對這些設定成遺忘的資料表進行讀取，相關的語法執行與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE TABLE test ENGINE = Log AS SELECT * FROM numbers(10);

CREATE TABLE test
ENGINE = Log AS
SELECT *
FROM numbers(10)

Query id: 33cc2adf-6e9d-4753-93f8-72570bd4789a

Ok.

0 rows in set. Elapsed: 0.007 sec.

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

SELECT *
FROM test

Query id: 87deaef5-744f-4b67-b9c2-bb3ba78388bb

┌─number─┐
│      0 │
│      1 │
│      2 │
│      3 │
│      4 │
│      5 │
│      6 │
│      7 │
│      8 │
│      9 │
└────────┘

10 rows in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) DETACH TABLE test;

DETACH TABLE test

Query id: 28324da9-6a18-4713-911c-57ca9905fddd

Ok.

0 rows in set. Elapsed: 0.001 sec.

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

SELECT *
FROM test

Query id: 003916f6-36a8-4f89-bc9f-3afa9193ce13


0 rows in set. Elapsed: 0.002 sec.

Received exception from server (version 22.8.4):
Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table default.test doesn't exist. (UNKNOWN_TABLE)

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) exit
Bye.
root@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~# systemctl restart clickhouse-server.service
root@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~# clickhouse-client --password
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 :) SELECT * FROM test;

SELECT *
FROM test

Query id: 58036b99-f34e-40db-97d4-d28d360d561d

┌─number─┐
│      0 │
│      1 │
│      2 │
│      3 │
│      4 │
│      5 │
│      6 │
│      7 │
│      8 │
│      9 │
└────────┘

10 rows in set. Elapsed: 0.001 sec.
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) DETACH TABLE test PERMANENTLY;

DETACH TABLE test PERMANENTLY

Query id: 8af7267f-7fd3-4963-a1cd-393b55dbe125

Ok.

0 rows in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) exit
Bye.
root@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~# systemctl restart clickhouse-server.service
root@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~# clickhouse-client --password
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 :) SELECT * FROM test;

SELECT *
FROM test

Query id: 7f656960-e25a-4df8-94c6-a5830748e318


0 rows in set. Elapsed: 0.002 sec.

Received exception from server (version 22.8.4):
Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table default.test doesn't exist. (UNKNOWN_TABLE)

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

從上述的SQL語句執行的輸出訊息可以知道，使用DETACH語句沒有加上PERMANENTLY時，將ClickHouse資料庫伺服器重新啟動之後，則可以繼續讀取該資料表，若設定了PERMANENTLY之後，該資料表就永久的在此ClickHouse資料庫中遺忘。

結論

在本章節中，我們展示了幾個SQL語句的語法範例與用法，下一章節中，將會再介紹其他的SQL語句的用法與範例。


上一篇
day17-SQL使用與操作方法介紹(八)
下一篇
day19-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邦幫忙