1

CREATE DATABASE dbtest ON CLUSTER default_cluster;

1

default 数据库只允许运维访问

1

CREATE DATABASE dbtest ON CLUSTER default_cluster ENGINE = Atomic;

1
2
3
4
5

CREATE DATABASE dbtest ON CLUSTER default_cluster ENGINE = Replicated('/clickhouse/databases/dbtest', '{shard}', '{replica}');
-- Replicated 引擎：
-- 在 ClickHouse 中，Replicated 引擎用于在集群中的多个节点上存储相同的数据副本。
-- 这可以确保即使某些节点出现故障，数据仍然可用。每个副本会保持一致性，并能自动进行故障恢复
-- '/clickhouse/databases/<db_name>', '{shard}', '{replica}'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

CREATE TABLE dbtest.test_table
-- 一般由 create database 指定，指定后此处就不再指定，否则会报告错误 
-- ON CLUSTER default_cluster
(
    event_time DateTime,
    user_id UInt64,
    event_type String
)
ENGINE = ReplicatedMergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

-- 推荐设置此项，防止数据堆积
-- TTL event_time + INTERVAL 7 DAY TO DISK 'cold' SETTINGS storage_policy = 'hot_to_cold';
-- 根据 event_time 字段，自动将超过 7 天的数据迁移到 'cold' 存储中，并使用 hot_to_cold 存储策略。
-- 存储策略配置查看 config.d/storage.xml

1
2
3

ENGINE = ReplicatedMergeTree
-- 不再推荐手写 ZooKeeper / Keeper 路径，而是自动生成路径
-- 此项设定会避免不必要的冲突、便于迁移、也更加安全

1

SYSTEM SYNC REPLICA dbtest.test_table;

1
2

CREATE ROLE ops_role ON CLUSTER default_cluster;
GRANT SELECT, CREATE, ALTER, OPTIMIZE, SYSTEM, SHOW ON *.* TO ops_role;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE ROLE admin_role ON CLUSTER default_cluster;

GRANT ALL ON *.* TO admin_role WITH GRANT OPTION;
-- 默认default不具备 ALL 的授权，因此上诉授权可能失败，采用以下方式解决，或者通过配置文件建立一个具备all的管理用户(TODO: 未测试该项)
-- GRANT CURRENT GRANTS ON *.* TO admin_role;

-- 查看 admin_role 的权限
-- SHOW GRANTS FOR admin_role;
-- 以 ops_user 身份登录后执行
-- SELECT * FROM system.grants WHERE user_name = current_user();
-- 管理员账号下查看该角色链条
-- SELECT * FROM system.grants WHERE role_name = 'admin_role';

1
2
3
4
5
6
7

-- 只读角色
CREATE ROLE dbtest_read;
GRANT SELECT ON dbtest.* TO dbtest_read;

-- 可写角色
CREATE ROLE dbtest_write;
GRANT SELECT, INSERT ON dbtest.* TO dbtest_write;

1
2

GRANT SELECT, INSERT, UPDATE, DELETE ON test_dbtest.* TO test_app_role;
-- GRANT ALL ON test_dbtest.* TO test_app_role;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 创建用户
-- 注意: 用户、授权、访问控制都可以通过配置文件 users.xml 进行配置，节点扩展时候，该项是不会进行同步的，需要手动同步
-- 但可以通过 开启权限信息的 ZooKeeper 存储（Replicated Access），实现权限的同步（TODO: 此项未测试）
CREATE USER app_user ON CLUSTER default_cluster IDENTIFIED BY 'StrongPassword';

-- 授权角色给用户, 多个角色逗号隔开 
GRANT dbtest_write TO app_user;

-- 角色授权还有一种为角色继承机制,比如下面的是 ops_role 继承 admin_role 的权限，继承的机制是合并(TODO: 未测试此项)
-- GRANT admin_role TO ops_role ON CLUSTER default_cluster;

1

ALTER USER default SETTINGS readonly = 1;

1

-- TODO: 此项待完善

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 创建一个资源限制模板
CREATE SETTINGS PROFILE high_priority ON CLUSTER default_cluster 
SETTINGS 
    -- 此项计算 (系统总内存 / 最大查询并发数 ) x 每个查询的内存比例因子(通常设定为 0.05 至 0.2（即每个查询使用系统总内存的 5% 至 20%）)
    max_memory_usage = 8G,      
    -- 通常建议与cpu核心数保持一致 
    max_threads = 8,            
    max_execution_time = 30,
    read_overflow_mode = 'throw';

-- 绑定给用户
ALTER USER readonly_user ON CLUSTER default_cluster SETTINGS PROFILE high_priority;

-- 例子分析
-- -- 假设系统总内存为 64GB，并且预计最多同时执行 20 个查询，每个查询使用 4 个线程：
-- 每个查询的内存使用量：
-- -- max_memory_usage_per_query = (64GB / 20) * 0.1 = 0.32GB
-- 每个查询使用 4 个线程时，内存使用量为：
-- -- max_memory_usage_per_query_with_threads = 0.32GB * 4 = 1.28GB
-- 这样，如果你同时执行 20 个查询，每个查询使用最多 1.28GB 内存，那么总内存使用量为：
-- TotalMemoryUsage = 1.28GB * 20 = 25.6GB

-- 如果你的服务器总内存为 64GB，那么该配置是合理的，因为最大查询总内存使用量仅为 25.6GB，远低于系统的总内存。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- 数据库
CREATE DATABASE dbtest ON CLUSTER default_cluster ENGINE = Replicated('/clickhouse/databases/dbtest', '{shard}', '{replica}');

-- 表
-- 若数据库创建使用 ON CLUSTER，表创建不再使用 ON CLUSTER
-- 推荐增加 TTL
CREATE TABLE dbtest.test_table
-- ON CLUSTER default_cluster 
(...)
ENGINE = ReplicatedMergeTree ORDER BY (...);

-- 角色
CREATE ROLE dbtest_read;
CREATE ROLE dbtest_write;
CREATE ROLE ops_role;

GRANT SELECT ON dbtest.* TO dbtest_read;
GRANT SELECT, INSERT ON dbtest.* TO dbtest_write;
GRANT SELECT, CREATE, ALTER, SYSTEM, OPTIMIZE ON *.* TO ops_role;