clickhouse的简单教程

文章目录

1. 1. clickhouse部署和查询小结
2. 2. 起初是直接修改 不行
3. 3. 然后是挂载目录还不行
4. 4. 最后在本地rpm包部署成功
5. 5. final 使用dockerfile实现
6. 6. 使用clickhouse入门

clickhouse部署和查询小结

/etc/clickhouse-server/users.xml

docker version ：1.13.1

• 简单修改 config.xml 中0.0.0.0 保证外部能访问

• 同时修改users.xml 的为 123456 如果使用md5 加密 着需要修改标签为

目前保存配置文件的地方有两个 /var/lib/clickhouse/preprocessed_configs ， /etc/clickhouse-server/

后面的目录下的文件会复制到 前者，如果如果直接修改前面目录内的文件是不会成功的。

起初是直接修改 不行

docker run -d --name some-clickhouse-server --privileged=true --ulimit nofile=262144:262144 --volume=/root/clickhouse_data:/var/lib/clickhouse -p 8123:8123  yandex/clickhouse-server

修改 /root/clickhouse_data/preprocessed_configs/users.xml 后无论使用 docker restart 还是进容器里面重启后都是都没有修改。

然后是挂载目录还不行

docker run --rm  --name clickhouse-server  --privileged=true  --net=host --ulimit nofile=262144:262144 --volume=/root/clickhouse_data:/var/lib/clickhouse  --volume=/root/clickhouse_config:/etc/clickhouse-server  yandex/clickhouse-server

报错没有修改的权限，这部分由于涉及到具体的操作就放弃了

Configuration file ‘/etc/clickhouse-server/config.xml’ isn’t readable by user with id ‘101’

最后在本地rpm包部署成功

sudo yum install yum-utils
sudo rpm --import https://repo.clickhouse.com/CLICKHOUSE-KEY.GPG
sudo yum-config-manager --add-repo https://repo.clickhouse.com/rpm/stable/x86_64

sudo yum install clickhouse-server clickhouse-client

如果没有 yum-config-manager 需要安装 yum-utils

final 使用dockerfile实现

Processing configuration file ‘/etc/clickhouse-server/config.xml’.
Merging configuration file ‘/etc/clickhouse-server/config.d/docker_related_config.xml’.
Logging trace to /var/log/clickhouse-server/clickhouse-server.log
Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log
Processing configuration file ‘/etc/clickhouse-server/config.xml’.
Merging configuration file ‘/etc/clickhouse-server/config.d/docker_related_config.xml’.
Saved preprocessed configuration to ‘/var/lib/clickhouse/preprocessed_configs/config.xml’.
Processing configuration file ‘/etc/clickhouse-server/users.xml’.
Saved preprocessed configuration to ‘/var/lib/clickhouse/preprocessed_configs/users.xml’.

由于不能直接挂载

所有使用Dockerfile 来打包 内容为

FROM yandex/clickhouse-server
COPY users.xml  /etc/clickhouse-server/users.xml

然后 **docker build -t clickhouse:lastet . ** ，打包完成后运行

使用clickhouse入门

我们使用的是最简单的clickhouse ，引擎使用 MergeTree

使用

官方提供的数据来实现 https://clickhouse.com/docs/en/getting-started/example-datasets/cell-towers/

-- auto-generated definition
create table kafka_iot_dev_data_state_queue
(
    json String
)
    engine = Kafka SETTINGS kafka_broker_list = '{IP}', kafka_topic_list = '{TOPIC}', kafka_group_name = '{GROUP_NAME}', kafka_format = 'JSONAsString';

CREATE MATERIALIZED VIEW zhyl.TABLE_NAME1 TO zhyl.TABLE_NAME2
(
    `rdid` String,
    `prdid` String,
    `kdcode` String,
    `devid` String,
    `rdstate` String,
    `rdtime` DateTime,
    `content` Nullable (String),
    `dev` Nullable (String)
)
AS
SELECT JSONExtractString(json, 'rdid')    AS rdid,
       JSONExtractString(json, 'prdid')   AS prdid,
       JSONExtractString(json, 'kdcode')  AS kdcode,
       JSONExtractString(json, 'devid')   AS devid,
       JSONExtractString(json, 'rdstate') AS rdstate,
       JSONExtractString(json, 'rdtime')  AS rdtime,
       JSONExtractString(json, 'content') AS content,
       JSONExtractString(json, 'dev')     AS dev
FROM zhyl.kafka_iot_dev_data_rd_queue;

create table TABLE_NAME1
(
    rdid    String,
    prdid   String,
    kdcode  String,
    devid   String,
    rdstate String,
    rdtime  DateTime('Asia/Shanghai'),
    content String,
    dev     String
)
    engine = MergeTree PARTITION BY (toYYYYMM(rdtime))
        ORDER BY rdid
        SETTINGS index_granularity = 8192;


select rdtime  from kafka_data_11 WHERE  rdtime < '2021-11-30 02:53:49' and rdtime > '2021-11-27 02:53:49' AND kdcode = 'hmb'

目前接触的函数包括

• visitParamExtractString 解析json数据后获取key对应的value

• visitParamExtractRaw 解析json数据后防护原始值

补充

使用tabix来查询和管理clickhouse