列式存储数据库ClickHouse的安装及使用Laravel连接使用

ClickHouse中文文档：https://clickhouse.tech/docs/zh/

1.安装

这里只介绍用docker安装，如需其他安装方式，查看上面中文文档教程

启动ClickHouse服务器实例

docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 yandex/clickhouse-server

root@iZ2ze29pyd9gyxc8isbnqhZ:~# docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 yandex/clickhouse-server
Unable to find image 'yandex/clickhouse-server:latest' locally
latest: Pulling from yandex/clickhouse-server
a31c7b29f4ad: Pull complete 
9f20b88f7a28: Pull complete 
a403cc61cf59: Pull complete 
9f12fda2a7c1: Pull complete 
f3412ff21597: Pull complete 
52e3312c5d6d: Pull complete 
Digest: sha256:337510ae39d39b42fff6ad5120ea1547d27f32b458fa8cd7a4debedabf58fd07
Status: Downloaded newer image for yandex/clickhouse-server:latest
8eda076c890f67825cc33b5b6e8bf7ae37235b30e4ba29cb4e8117d9ab09d2f5
 

如果要使用主机系统卷进行数据存储，请使用以下命令启动服务器：

mkdir $HOME/some_clickhouse_database
docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 --volume=$HOME/some_clickhouse_database:/var/lib/clickhouse yandex/clickhouse-server 

从本机客户端连接到它

docker run -it --rm --link some-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server

在这里可以输入类似mysql命令进行操作查询例如 show databases，select查询等

从外部访问连接到它

docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 -p 8123:8123 yandex/clickhouse-server

 我只开启了8123端口，如需要开启其他端口 使用-p 端口号:内部端口号 开启

使用可视化工具连接，我这里使用的是phpstorm

 输入宿主机的ip地址，端口号为8123 默认用户名为default，密码为空

修改clickhouse用户名和密码参考：https://blog.csdn.net/kongliand/article/details/109219516

2.Laravel连接ClickHouse

composer require the-tinderbox/clickhouse-builder

在 config/app.php 添加:

'providers' => [
        ...
        \Tinderbox\ClickhouseBuilder\Integrations\Laravel\ClickhouseServiceProvider::class,
        ...
    ]

连接配置通过config/database.php。

'connections' => [
    'clickhouse' => [
        'driver' => 'clickhouse',
        'host' => 'ip',
        'port' => '',
        'database' => '',
        'username' => '',
        'password' => '',
        'options' => [
            'timeout' => 10,
            'protocol' => 'https'
        ]
    ]
]

查询基本跟mysql一致

DB::connection('clickhouse')->table(表名)->select(...);

DB::row使用row代替

->select(raw("sum(click_cnt) as click_cnt"), "zone_id");