Ubuntu安装ClickHouse

黑人依旧

已于 2024-02-22 14:30:16 修改

阅读量293

点赞数 3

文章标签： ubuntu clickhouse 服务器

于 2024-02-20 16:24:50 首次发布

本文链接：https://blog.csdn.net/baidu_41881646/article/details/136193146

版权

1）准备工作

更新、安装工具，更新apt源

apt update
apt install -y apt-transport-https ca-certificates dirmngr
apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754
echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee     /etc/apt/sources.list.d/clickhouse.list
apt update

2）部署ClickHouse

apt install -y clickhouse-server clickhouse-client
systemctl status clickhouse-server

3）修改配置

cd /etc/clickhouse-server/
users.xml
config.xml
metrika.xml
chown clickhouse:clickhouse *.xml

4）开放防火墙

ufw allow 8123,9000,9004,9005,9009/tcp

5）启动SQL方式认证

修改users.xml，在default用户下添加以下2行

            <show_named_collections>1</show_named_collections>
            <show_named_collections_secrets>1</show_named_collections_secrets>

6）基于SQL授权

DBA角色创建并授权

create role dba on cluster '{cluster}';
grant all on *.* to dba on cluster '{cluster}';
create user `user1` identified  by 'pass1234' on cluster '{cluster}';
grant dba to user1 on cluster '{cluster}';

只读用户创建并授权

create role dashboard_ro on cluster '{cluster}';
grant select on default.* to dashboard_ro on cluster '{cluster}';
grant dictGet on *.*  to dashboard_ro on cluster '{cluster}';

create settings profile or replace profile_dashboard_ro on cluster '{cluster}'
settings max_concurrent_queries_for_user = 10 READONLY, 
         max_threads = 16 READONLY, 
         max_memory_usage_for_user = '30G' READONLY,
         max_memory_usage = '30G' READONLY,
         max_execution_time = 60 READONLY,
         max_rows_to_read = 1000000000 READONLY,
         max_bytes_to_read = '5000G' READONLY
TO dashboard_ro;

create user `dash1` identified  by 'pass1234' on cluster '{cluster}';
grant dashboard_ro to dash1 on cluster '{cluster}';

这里的多加了一个settings profile的例子，并将这个setting profile赋给了dashboard_ro 角色,这里可以看到对读用户的资源使用进行了最大配额的限制。

读写用户创建并授权

create role ingester_rw on cluster '{cluster}';
grant select,insert on default.* to ingester_rw on cluster '{cluster}';

create settings profile or replace profile_ingester_rw on cluster '{cluster}'
settings max_concurrent_queries_for_user = 40 READONLY,    -- user can run 40 queries select, insert ...) simultaneously  
         max_threads = 10 READONLY,                        -- each query can use up to 10 cpu (READONLY means user cannot override a value)
         max_memory_usage_for_user = '30G' READONLY,       -- all queries of the user can use up to 30G RAM
         max_memory_usage = '25G' READONLY,                -- each query can use up to 25G RAM
         max_execution_time = 200 READONLY,                -- each query can executes no longer 200 seconds
         max_rows_to_read = 1000000000 READONLY,           -- each query can read up to 1 billion rows
         max_bytes_to_read = '5000G' READONLY              -- each query can read up to 5 TB from a MergeTree
TO ingester_rw;

create user `ingester_app1` identified  by 'pass1234'　on cluster '{cluster}';

grant ingester_rw to ingester_app1 on cluster '{cluster}';