前言
ClickHouse是一个开源的分布式列式数据库管理系统，它被设计用于存储和分析大规模数据。Python是一种流行的编程语言，凭借其简洁的语法和丰富的生态系统，成为了数据处理和分析的首选语言之一。在Python中，我们可以使用多种方式与ClickHouse进行连接并操作数据。
Python连接ClickHouse有三种常用方式，分别是使用clickhouse-driver、clickhouse-sqlalchemy和pyclickhouse库。每种方式都有其特点和适用场景，可以根据具体需求选择合适的方法。这里我们详细介绍 clickhouse-driver 的连接方式。
ClickHouse 安装
ClickHouse的官方文档地址：
https://clickhouse.com/docs/zh/getting-started/install
下面是在MacOS系统中安装ClickHouse的步骤
参考：
https://blog.csdn.net/qq_51513626/article/details/139601933
1、下载ClickHouse最简单的方法是运行以下curl命令
curl https://clickhouse.com/ | sh操作：
edy@edydeMBP ~ % curl https://clickhouse.com/ | sh
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 2911 0 2911 0 0 2316 0 --:--:-- 0:00:01 --:--:-- 2315
Will download https://builds.clickhouse.com/master/macos-aarch64/clickhouse into clickhouse.0
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 79.9M 100 79.9M 0 0 4886k 0 0:00:16 0:00:16 --:--:-- 5785k
Successfully downloaded the ClickHouse binary, you can run it as:
./clickhouse.0
You can also install it:
sudo ./clickhouse.0 install2、执行本命令安装
edy@edydeMBP ~ % sudo ./clickhouse.0 install
Password:
....
Copying ClickHouse binary to /usr/local/bin/clickhouse.new
Renaming /usr/local/bin/clickhouse.new to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/clickhouse-server to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/clickhouse-client to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/clickhouse-local to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/clickhouse-benchmark to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/clickhouse-obfuscator to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/clickhouse-git-import to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/clickhouse-compressor to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/clickhouse-format to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/clickhouse-extract-from-config to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/clickhouse-keeper to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/clickhouse-keeper-converter to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/clickhouse-disks to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/ch to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/chl to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/chc to /usr/local/bin/clickhouse.
Will not create a dedicated clickhouse group.
Will not create a dedicated clickhouse user.
Creating config directory /etc/clickhouse-server.
Creating config directory /etc/clickhouse-server/config.d that is used for tweaks of main server configuration.
Creating config directory /etc/clickhouse-server/users.d that is used for tweaks of users configuration.
Data path configuration override is saved to file /etc/clickhouse-server/config.d/data-paths.xml.
Log path configuration override is saved to file /etc/clickhouse-server/config.d/logger.xml.
User directory path configuration override is saved to file /etc/clickhouse-server/config.d/user-directories.xml.
OpenSSL path configuration override is saved to file /etc/clickhouse-server/config.d/openssl.xml.
Creating log directory /var/log/clickhouse-server.
Creating data directory /var/lib/clickhouse.
Creating pid directory /var/run/clickhouse-server.
Set up the password for the default user:
Password for the default user is saved in file /etc/clickhouse-server/users.d/default-password.xml.
Allow server to accept connections from the network (default is localhost only), [y/N]: y
The choice is saved in file /etc/clickhouse-server/config.d/listen.xml.
ClickHouse has been successfully installed.
Start clickhouse-server with:
sudo clickhouse start
Start clickhouse-client with:
clickhouse-client --password3、启动服务器
edy@edydeMBP ~ % sudo ./clickhouse.0 install
Password:
....
Copying ClickHouse binary to /usr/local/bin/clickhouse.new
Renaming /usr/local/bin/clickhouse.new to /usr/local/bin/clickhouse.
Creating symlink /usr/local/bin/clickhouse-server to /usr/local/bin/clickhouse.
....4、启动客户端
edy@edydeMBP ~ % ./clickhouse client
ClickHouse client version 25.2.1.1099 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 25.2.1.
Warnings:
* Maximum number of threads is lower than 30000. There could be problems with handling a lot of simultaneous queries.搭建ClickHouse 测试数据
1、创建数据库launch
edydeMBP.lan :) create database if not exists launch
CREATE DATABASE IF NOT EXISTS launch
Query id: 04d45ce0-a963-422d-a50a-34c21f1510a2
Ok.
0 rows in set. Elapsed: 0.003 sec. 2、创建表
edydeMBP.lan :) CREATE TABLE IF NOT EXISTS launch.users
(
`id` String COMMENT 'ID',
`name` Nullable(String) COMMENT '名称',
`age` Nullable(Int64) COMMENT '年龄',
`create_date` Nullable(Date) DEFAULT toDate(now()) COMMENT '创建时间'
)
ENGINE = MergeTree
PRIMARY KEY id
Query id: 582c505a-799f-4473-8e46-05ddf594afcd
Ok.
0 rows in set. Elapsed: 0.019 sec. 3、插入数据
edydeMBP.lan :) INSERT INTO launch.users(id,name,age) VALUES (1, 'yyp', 18)
INSERT INTO launch.users (id, name, age) FORMAT Values
Query id: 9f27b48d-1cf5-43ee-81cc-5c36c7aa0630
Ok.
1 row in set. Elapsed: 0.032 sec. 4、查询数据
edydeMBP.lan :) select * from launch.users;
SELECT *
FROM launch.users
Query id: 4035a08e-fd82-4776-85c6-d80adf45d181
┌─id─┬─name─┬─age─┬─create_date─┐
1. │ 1 │ yyp │ 18 │ 2025-02-05 │
└────┴──────┴─────┴─────────────┘
1 row in set. Elapsed: 0.008 sec. 什么是clickhouse-driver ？
简介
- 官方推荐的高性能 Python 客户端。
- 支持 ClickHouse 原生协议，提供快速、高效的查询和插入。
- 支持同步和异步操作，适用于需要极高性能的场景。
安装
python3 -m pip install clickhouse-driveredy@edydeMBP ~ % python3 -m pip install clickhouse-driver
Collecting clickhouse-driver
Downloading clickhouse-driver-0.2.9.tar.gz (357 kB)
Installing build dependencies ... done
Getting requirements to build wheel ... done
Preparing metadata (pyproject.toml) ... done
Collecting pytz (from clickhouse-driver)
Downloading pytz-2025.1-py2.py3-none-any.whl.metadata (22 kB)
Collecting tzlocal (from clickhouse-driver)
Downloading tzlocal-5.2-py3-none-any.whl.metadata (7.8 kB)
Downloading pytz-2025.1-py2.py3-none-any.whl (507 kB)
Downloading tzlocal-5.2-py3-none-any.whl (17 kB)
Building wheels for collected packages: clickhouse-driver
Building wheel for clickhouse-driver (pyproject.toml) ... done
Created wheel for clickhouse-driver: filename=clickhouse_driver-0.2.9-cp313-cp313-macosx_10_13_universal2.whl size=344651 sha256=afed8eb16cd4c8a08108ce7ae77060d6440bf1affeea11da4c2d96433602af3e
Stored in directory: /Users/edy/Library/Caches/pip/wheels/34/d5/f6/6476c444c2165a366ab8085297c2985e2e2e502ab29baee1dd
Successfully built clickhouse-driver
Installing collected packages: pytz, tzlocal, clickhouse-driver
Successfully installed clickhouse-driver-0.2.9 pytz-2025.1 tzlocal-5.2
[notice] A new release of pip is available: 24.3.1 -> 25.0
[notice] To update, run: pip3 install --upgrade pip
edy@edydeMBP ~ % Python 连接 ClickHouse
编写脚本ck.py
from clickhouse_driver import connect
# 连接到 ClickHouse
conn = connect(host='localhost', database='launch', port=9000, user='default', password='123456')
cursor = conn.cursor()
# 执行查询
cursor.execute("select * from launch.users")
result = cursor.fetchall();
print(type(result))
try:
for row in result:
print(row)
except ValueError:
print("query launch.users error :", ValueError)
finally:
conn.close()输出结果：
('1', 'yyp', 18, datetime.date(2002, 1, 1))