環境概要
三台のCentOS7サーバーにClickHouseがインストールされています。
| ホスト名 | IPアドレス | インストール済みソフトウェア | ポート番号 |
| centf8118.sharding1.db | 192.168.81.18 | clickhouse-server, clickhouse-client | 9000 |
| centf8119.sharding2.db | 192.168.81.19 | clickhouse-server, clickhouse-client | 9000 |
| centf8120.sharding3.db | 192.168.81.20 | clickhouse-server, clickhouse-client | 9000 |
一：データベースへの接続
ローカル接続：clickhouse-client
[root@centf8118 format_schemas]# clickhouse-client
ClickHouse client version 20.6.4.44 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.6.4 revision 54436.
centf8118.sharding1.db :) SHOW DATABASES;
SHOW DATABASES
┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ default │
│ system │
│ testdb │
└────────────────────────────────┘
4 rows in set. Elapsed: 0.003 sec.
centf8118.sharding1.db :) SELECT now();
SELECT now()
┌───────────────now()─┐
│ 2020-08-26 15:11:22 │
└─────────────────────┘
1 rows in set. Elapsed: 0.010 sec.
centf8118.sharding1.db :)
リモート接続：(ローカルのclickhouse-clientでリモートのclickhouse-serverに接続)
clickhouse-client --host 192.168.81.20 --port 9000 --database default --user default --password ""
接続できない場合は、telnetでポートが開いているか確認してください。
[root@centf8118 ~]# telnet 192.168.81.20 9000
Trying 192.168.81.20 ...
telnet: connect to address 192.168.81.20: Connection refused
telnetが接続できない場合は、iptablesでポートが許可されているか確認してください。（全サーバーで設定が必要。ここではIP範囲とポート範囲を許可していますが、実際の要件に合わせて設定してください。）
-A INPUT -p tcp -m iprange --src-range 192.168.81.18-192.168.81.20 -m tcp --dport 9000:9200 -j ACCEPT
iptablesで9000ポートを許可しても接続できない場合は、リスニングポートを確認してください：
[root@centf8118 ~]# netstat -anltp|grep :9000
tcp 0 0 127.0.0.1:9000 0.0.0.0:* LISTEN 22710/clickhouse-se
127.0.0.1:9000となっている場合は、設定ファイルを修正してリモートアクセスを許可する必要があります。
vim /etc/clickhouse-server/config.xml
設定ファイル内のコメントアウトされた<listen_host>0.0.0.0</listen_host>を有効にします。
設定変更後、サービスを再起動： server clickhouse-server restart
再度リスニングポートを確認：
[root@centf8118 ~]# netstat -anltp|grep :9000
tcp 0 0 0.0.0.0:9000 0.0.0.0:* LISTEN 6060/clickhouse-ser
データベースに再接続：（インストール後、システムはデフォルトでログインアカウントdefaultを作成し、パスワードは空文字列です）
[root@centf8118 ~]# clickhouse-client --host 192.168.81.20 --port 9000 --database default --user default --password ""
ClickHouse client version 20.6.4.44 (official build).
Connecting to database default at 192.168.81.20:9000 as user default.
Connected to ClickHouse server version 20.6.4 revision 54436.
centf8120.sharding3.db :) SHOW DATABASES;
SHOW DATABASES
┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ default │
│ system │
│ testdb │
│ tutorial │
└────────────────────────────────┘
5 rows in set. Elapsed: 0.004 sec.
centf8120.sharding3.db :)
クライアント接続時、デフォルトでは1行が1つの完全なSQLステートメントです。複数行のSQLの場合、行ごとに実行されエラーが発生します。
clickhouse-client -m で複数行SQLをサポートできます。
二：ロールとユーザーアカウントの操作
公式ドキュメント：https://clickhouse.tech/docs/en/sql-reference/statements/create/role/
ロールとユーザーの作成
CREATE ROLE [IF NOT EXISTS | OR REPLACE] role_name
[SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
CREATE USER [IF NOT EXISTS | OR REPLACE] user_name [ON CLUSTER cluster_name]
[IDENTIFIED [WITH {NO_PASSWORD|PLAINTEXT_PASSWORD|SHA256_PASSWORD|SHA256_HASH|DOUBLE_SHA1_PASSWORD|DOUBLE_SHA1_HASH}] BY {'password'|'hash'}]
[HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
[DEFAULT ROLE role [,...]]
[SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
例：
-- ロールを作成して権限を付与
CREATE ROLE accountant;
GRANT SELECT ON db.* TO accountant;
-- ユーザーにロールを追加
GRANT accountant TO mira;
-- ロールを設定
SET ROLE accountant;
SELECT * FROM db.*;
-- ユーザー作成例
CREATE USER mira HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'qwerty'
CREATE USER john DEFAULT ROLE role1, role2
ALTER USER user DEFAULT ROLE ALL
ALTER USER john DEFAULT ROLE ALL EXCEPT role1, role2
ロールとユーザーのクエリ
ロールとユーザーの変更
ALTER ROLE [IF EXISTS] role_name [ON CLUSTER cluster_name]
[RENAME TO new_name]
[SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
ALTER USER [IF EXISTS] user_name [ON CLUSTER cluster_name]
[RENAME TO new_name]
[IDENTIFIED [WITH {PLAINTEXT_PASSWORD|SHA256_PASSWORD|DOUBLE_SHA1_PASSWORD}] BY {'password'|'hash'}]
[[ADD|DROP] HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
[DEFAULT ROLE role [,...] | ALL | ALL EXCEPT role [,...] ]
[SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
ロールとユーザーの削除
DROP ROLE [IF EXISTS] role_name [,...] [ON CLUSTER cluster_name]
DROP USER [IF EXISTS] user_name [,...] [ON CLUSTER cluster_name]
三：データベースの操作
公式ドキュメント：https://clickhouse.tech/docs/en/sql-reference/statements/create/database/
データベースの作成
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]
データベースのクエリ
SHOW DATABASES [INTO OUTFILE filename] [FORMAT format]
データベースの削除
DROP DATABASE [IF EXISTS] db [ON CLUSTER cluster]
四：テーブルの操作
テーブルの作成
-- 新規テーブルの作成
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
column1 [type1] [DEFAULT|MATERIALIZED|ALIAS expression1] [compression_codec] [TTL expression1],
column2 [type2] [DEFAULT|MATERIALIZED|ALIAS expression2] [compression_codec] [TTL expression2],
...
) ENGINE = engine
-- 既存テーブルの構造をコピー
CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]source_table [ENGINE = engine]
テーブルのクエリ
SHOW [TEMPORARY] TABLES [{FROM | IN} <database>] [LIKE '<pattern>' | WHERE expression] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]
SELECT name FROM system.tables WHERE database = <database> [AND name LIKE <pattern>] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]
テーブルの削除
DROP [TEMPORARY] TABLE [IF EXISTS] [db.]table_name [ON CLUSTER cluster]