clickhouse

1.配置

安装

以Centos7系统为例，首先添加官方存储库

Ssudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
sudo yum install -y clickhouse-server clickhouse-client

设置用户名和密码

生成密文和明文密码

PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; 
echo -n "$PASSWORD" | sha256sum | tr -d '-'

这样可以得到两行数据，第一行是密码明文，第二行是密码密文

然后编辑users.xml

vim /etc/clickhouse-server/users.xml

将password改成以下内容，将密文填进去

<password_sha256_hex>密码密文</password_sha256_hex>

再找到 config.xml，允许所有人访问

<listen_host>0.0.0.0</listen_host>

启动

这个时候本地连接需要指定端口 127.0.0.1

sudo clickhouse-client --host='127.0.0.1' --port='9000' --user='default' --password='明文密码'

启动服务，日志文件在/var/log/clickhouse-server/目录下

sudo /etc/init.d/clickhouse-server start

客户端连接命令如下,参数如下:

• --host 主机
• --port 端口
• --user 用户名
• -- password 密码

clickhouse-client

3.bitmap解决数据量大计算慢问题

创建测试表

create table label_bit_map
(
    label_code       String comment '标签名称',
    label_name       String comment '标签名称',
    uid               AggregateFunction(groupBitmap, UInt64) comment 'uid'
)
    engine = AggregatingMergeTree PARTITION BY label_code
        ORDER BY label_code
        SETTINGS index_granularity = 8192;

往表中插入 age 一千万条数据

INSERT INTO label_bit_map (label_code, label_name, uid)
SELECT 'age', '年龄', groupBitmapState(toUInt64(rand()%1000000000))
FROM numbers(10000000);

往表中插入sex 一千万条数据

INSERT INTO label_bit_map (label_code, label_name, uid)
SELECT 'sex', '性别', groupBitmapState(toUInt64(rand()%1000000000))
FROM numbers(10000000);

四秒完成

completed in 4 s 720 ms

交并(取交集)

with
(select uid from label_bit_map where label_code='age') as t1,
(select uid from label_bit_map where label_code='sex') as t2
select bitmapAnd(t1,t2)

取并集

with
(select uid from label_bit_map where label_code='age') as t1,
(select uid from label_bit_map where label_code='sex') as t2
select bitmapOr(t1,t2)

取具体数量

with
(select uid from label_bit_map where label_code='age') as t1,
(select uid from label_bit_map where label_code='sex') as t2
select bitmapCardinality(bitmapCardinality())