ClickHouse 属于 OLAP 数据库
一般使用 OLTP 数据库做业务数据存储, 用 OLAP 数据库做查询分析.
在需要复杂查询的分布式场景, 可以考虑 Apache Doris.
sudo apt-get install -y apt-transport-https ca-certificates dirmngr # 在 /tmp 下创建临时目录 GNUPGHOME=$(mktemp -d) echo $GNUPGHOME # 生成 clickhouse-keyring.gpg sudo GNUPGHOME="$GNUPGHOME" gpg --no-default-keyring --keyring /usr/share/keyrings/clickhouse-keyring.gpg --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys 8919F6BD2B48D754 sudo rm -r "$GNUPGHOME" sudo chmod +r /usr/share/keyrings/clickhouse-keyring.gpg # 创建 ck 的 apt list echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list # 更新软件包 sudo apt-get update # 安装 sudo apt install -y clickhouse-server clickhouse-client
sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo sudo yum install -y clickhouse-server clickhouse-client
<path>/var/lib/clickhouse/</path>
, <tmp_path>/var/lib/clickhouse/tmp/</tmp_path>
, <user_files_path>/var/lib/clickhouse/user_files/</user_files_path>
启动服务
sudo systemctl start clickhouse-server sudo systemctl status clickhouse-server
默认只侦听本地请求, 打开服务端口, 编辑 /etc/clickhouse-server/config.xml
sudo chmod 600 /etc/clickhouse-server/config.xml sudo vi /etc/clickhouse-server/config.xml
取消注释, 同时服务IPv6和IPv4
<listen_host>::</listen_host>
如果只需要提供IPv4, 可以取消这一行注释
<listen_host>0.0.0.0</listen_host>
这两行不能同时取消注释, 启动会报错
ClickHouse 的用户分两种
这两种用户的登录方式是一样的
打开 /etc/clickhouse-server/user.xml 能看到设置用户口令相关的说明, 默认用户 default 的口令为空
简单的说就是
<password>qwerty</password>
进行设置<password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex>
<password_double_sha1_hex>e395796d6546b1b65db9d665cd43f0e858dd4303</password_double_sha1_hex>
<ldap><server>my_ldap_server</server></ldap>
<kerberos><realm>EXAMPLE.COM</realm></kerberos>
相应的口令生成命令
# SHA256 PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-' cY19OvVH <-- 口令 e17cd697e0845d75d2068ae1e1479d3fd10d76e5afa89724fbc6fe27554526e4 <-- SHA256结果 # Double SHA1 PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-' 1gQO8XpM <-- 口令 e9fdf3480016dfae8ad0170e846edd031180a3f4 <-- Double SHA1结果
如果 Centos7 下没有 xxd 命令, 需要通过以下命令安装
sudo yum install vim-common
如果需要增加位数
PASSWORD=$(base64 < /dev/urandom | head -c16); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-' vlIlWHFqY0BbSy2f e08ebd515246f1b5f3bfdb24b967a797b7218289b263ed0fbb3ff47fcc121f1b
如果需要自定义
PASSWORD=asdf1234; echo "$PASSWORD"; echo -n "$PASSWORD" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-' asdf1234 dda7b4594264195da8bb303516d7ec5509b7b942
例如新增一个带管理权限的用户 dbowner, 可以在 /etc/clickhouse-server/user.d 下创建文件 dbowner.xml, 内容为
<clickhouse> <users> <dbowner> <password>abcd1234</password> <networks> <ip>::/0</ip> </networks> <profile>default</profile> <quota>default</quota> <access_management>1</access_management> </dbowner> </users> </clickhouse>
上面这个配置
access_management = 1
)clickhouse启动时, 会将 user.d 下的配置文件与 user.xml 合并, 并覆盖 user.xml 中重复的部分. 注意这个文件的权限, 需要将这个文件的owner设为 clickhouse, 否则 clickhouse 启动后读取会失败.
sudo chown clickhouse:clickhouse dbowner.xml
具体的用户权限配置, 参考 ClickHouse 官方说明
https://clickhouse.com/docs/en/operations/access-rights
上面添加的 dbowner 用户只开启了 access_management, 默认情况下 SQL-driven access control 和 account management都是关闭的, 如果要启用完整的 SQL user mode, 要在<dbowner>
下增加的配置为
<access_management>1</access_management> <named_collection_control>1</named_collection_control> <show_named_collections>1</show_named_collections> <show_named_collections_secrets>1</show_named_collections_secrets>
在安装 ClickHouse 的机器上, 使用上面创建的 dbowner 登入 client, 并创建一个新用户 dbroot, 授予全部权限
$ clickhouse-client --user dbowner --password abcd1234 :) CREATE USER dbroot IDENTIFIED BY 'root1234'; CREATE USER dbroot IDENTIFIED WITH sha256_hash BY '21AC41BC256B35A32EC2021D359AE5F297AD7ED2F8ED8F7A2A1A7B9F1F94F898' SALT '56DC39142C0AEB19BC2C61AACBD9F27DD040E25489CC29B76D07D65F6D2A3AA1' Query id: d0099db9-b292-4905-84bd-a91da19f8edc Ok. 0 rows in set. Elapsed: 0.005 sec.
创建用户成功, 但是授予权限时报错了
:) GRANT ALL ON *.* TO dbroot WITH GRANT OPTION; GRANT ALL ON *.* TO dbroot WITH GRANT OPTION Query id: 92a50da8-d847-4f4f-a74c-95b9f1207a67 0 rows in set. Elapsed: 0.007 sec. Received exception from server (version 23.4.2): Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: dbowner: Not enough privileges. To execute this query it's necessary to have grant SHOW, SELECT, INSERT, ALTER, CREATE, DROP, UNDROP TABLE, TRUNCATE, OPTIMIZE, BACKUP, KILL QUERY, KILL TRANSACTION, MOVE PARTITION BETWEEN SHARDS, ACCESS MANAGEMENT, NAMED COLLECTION CONTROL, SYSTEM, dictGet, INTROSPECTION, SOURCES ON *.* WITH GRANT OPTION. (ACCESS_DENIED)
给dbowner增加以下权限
<named_collection_control>1</named_collection_control> <show_named_collections>1</show_named_collections> <show_named_collections_secrets>1</show_named_collections_secrets>
重启 ClickHouse 后再执行, 就能授权成功
:) GRANT ALL ON *.* TO dbroot WITH GRANT OPTION; GRANT ALL ON *.* TO dbroot WITH GRANT OPTION Query id: f4eaa3ce-8182-4717-9270-ce2e95eb2b88 Ok. 0 rows in set. Elapsed: 0.004 sec.
这时候, 就可以用 dbroot / root1234 登录 ClickHouse 了
clickhouse-client --user [user] --password [password]
用Firefox访问 http://dash.tabix.io, Chrome貌似不行, 会报 CORS 错误
填写服务器地址, 默认为 http://[server_ip]:8123, 用户 default, 口令为空
服务器地址, 默认为 http://[server_ip]:8123, 用户 default, 口令为空
CREATE DATABASE my_db;
CREATE TABLE my_db.my_table (id UInt64, column1 String) ENGINE = MergeTree() ORDER BY id;
CREATE USER my_user IDENTIFIED BY 'password';
# my_db下所有表的 ALTER 权限 GRANT ALTER ON my_db.* WITH GRANT OPTION; # my_db下my_table表的 ALTER 权限 GRANT ALTER ON my_db.my_table TO my_user; # 多个权限 GRANT SELECT, ALTER COLUMN ON my_db.my_table TO my_user WITH GRANT OPTION;
权限树
├── ALTER (only for table and view)/ │ ├── ALTER TABLE/ │ │ ├── ALTER UPDATE │ │ ├── ALTER DELETE │ │ ├── ALTER COLUMN/ │ │ │ ├── ALTER ADD COLUMN │ │ │ ├── ALTER DROP COLUMN │ │ │ ├── ALTER MODIFY COLUMN │ │ │ ├── ALTER COMMENT COLUMN │ │ │ ├── ALTER CLEAR COLUMN │ │ │ └── ALTER RENAME COLUMN │ │ ├── ALTER INDEX/ │ │ │ ├── ALTER ORDER BY │ │ │ ├── ALTER SAMPLE BY │ │ │ ├── ALTER ADD INDEX │ │ │ ├── ALTER DROP INDEX │ │ │ ├── ALTER MATERIALIZE INDEX │ │ │ └── ALTER CLEAR INDEX │ │ ├── ALTER CONSTRAINT/ │ │ │ ├── ALTER ADD CONSTRAINT │ │ │ └── ALTER DROP CONSTRAINT │ │ ├── ALTER TTL/ │ │ │ └── ALTER MATERIALIZE TTL │ │ ├── ALTER SETTINGS │ │ ├── ALTER MOVE PARTITION │ │ ├── ALTER FETCH PARTITION │ │ └── ALTER FREEZE PARTITION │ └── ALTER LIVE VIEW/ │ ├── ALTER LIVE VIEW REFRESH │ └── ALTER LIVE VIEW MODIFY QUERY ├── ALTER DATABASE ├── ALTER USER ├── ALTER ROLE ├── ALTER QUOTA ├── ALTER [ROW] POLICY └── ALTER [SETTINGS] PROFILE
SHOW GRANTS FOR my_user;
可以看下只开了access_management的 default 和完整权限的 dbroot 的权限差别
:) show grants for dbroot; ┌─GRANTS FOR dbroot────────────────────────────┐ │ GRANT ALL ON *.* TO dbroot WITH GRANT OPTION │ └──────────────────────────────────────────────┘ :) show grants for default; ┌─GRANTS FOR default─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ GRANT SHOW, SELECT, INSERT, ALTER, CREATE, DROP, UNDROP TABLE, TRUNCATE, OPTIMIZE, BACKUP, KILL QUERY, KILL TRANSACTION, MOVE PARTITION BETWEEN SHARDS, SYSTEM, dictGet, INTROSPECTION, SOURCES, CLUSTER ON *.* TO default │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
REVOKE ALTER COLUMN ON my_db.my_table FROM my_user;