01 Python操作ClickHouse大数据库入门教程
Docker部署ClickHouse
将相关资料上传到服务器。
导入镜像:
docker?load?-i?clickhouse22_v1.tar
创建目录:
mkdir?-p?/docker/clickhouse/conf
mkdir?-p?/docker/clickhouse/data
mkdir?-p?/docker/clickhouse/log
将两个配置文件复制到conf目录。
mv?*.xml?/docker/clickhouse/conf/
创建容器:
docker?run?-d?--name=clickhouse?-p?8123:8123?--volume=/docker/clickhouse/conf/config.xml:/etc/clickhouse-server/config.xml?--volume=/docker/clickhouse/conf/users.xml:/etc/clickhouse-server/users.xml?--volume=/docker/clickhouse/data:/var/lib/clickhouse/?clickhouse/clickhouse-server:22.8.14.53
账号1:root zhangdapeng520账号2:zhangdapeng zhangdapeng520
准备Python环境
安装Python3.8
配置国内源:
pip?config?set?global.index-url?https://pypi.tuna.tsinghua.edu.cn/simple
安装依赖:
pip?install?clickhouse-connect==0.6.22
连接CK
核心代码:
ck_conn?=?clickhouse_connect.get_client(
host=host,
port=port,
database=database,
username=username,
password=password,
)
完整实例:
import?clickhouse_connect
#?准备参数
host?=?"127.0.0.1"
port?=?8123
username?=?"zhangdapeng"
password?=?"zhangdapeng520"
database?=?"default"
#?建立连接
ck_conn?=?clickhouse_connect.get_client(
host=host,
port=port,
database=database,
username=username,
password=password,
)
print(ck_conn)
创建数据库表
核心代码:
client.command('CREATE?TABLE?user?(id?UInt32,?name?String,?age?UInt32)?ENGINE?MergeTree?ORDER?BY?id')
完整实例:
import?clickhouse_connect
#?02?创建数据库表
#?准备参数
host?=?"127.0.0.1"
port?=?8123
username?=?"zhangdapeng"
password?=?"zhangdapeng520"
database?=?"default"
#?建立连接
client?=?clickhouse_connect.get_client(
host=host,
port=port,
database=database,
username=username,
password=password,
)
print(client)
#?创建数据库表
client.command('CREATE?TABLE?user?(id?UInt32,?name?String,?age?UInt32)?ENGINE?MergeTree?ORDER?BY?id')
删除数据库表
import?clickhouse_connect
#?04?删除数据库表
#?准备参数
host?=?"127.0.0.1"
port?=?8123
username?=?"zhangdapeng"
password?=?"zhangdapeng520"
database?=?"default"
#?建立连接
client?=?clickhouse_connect.get_client(
host=host,
port=port,
database=database,
username=username,
password=password,
)
print(client)
#?删除数据库表
client.command('drop?table?if?exists?user')
新增数据
import?clickhouse_connect
#?05?新增数据
#?准备参数
host?=?"127.0.0.1"
port?=?8123
username?=?"zhangdapeng"
password?=?"zhangdapeng520"
database?=?"default"
#?建立连接
client?=?clickhouse_connect.get_client(
host=host,
port=port,
database=database,
username=username,
password=password,
)
#?创建数据库表
table?=?"user"
columns?=?["id",?"name",?"age"]
client.command(f'CREATE?TABLE?{table}?(id?UInt32,?name?String,?age?UInt32)?ENGINE?MergeTree?ORDER?BY?id')
#?新增数据
zs?=?[1,?'张三',?23]
ls?=?[2,?'李四',?24]
ww?=?[3,?'王五',?35]
data?=?[zs,?ls,?ww]
client.insert(table,?data,?column_names=columns)
#?删除数据库表
client.command(f'drop?table?if?exists?{table}')
根据ID查询数据
import?clickhouse_connect
#?06?根据ID查询数据
#?准备参数
host?=?"127.0.0.1"
port?=?8123
username?=?"zhangdapeng"
password?=?"zhangdapeng520"
database?=?"default"
#?建立连接
client?=?clickhouse_connect.get_client(
host=host,
port=port,
database=database,
username=username,
password=password,
)
#?创建数据库表
table?=?"user"
columns?=?["id",?"name",?"age"]
client.command(f'CREATE?TABLE?{table}?(id?UInt32,?name?String,?age?UInt32)?ENGINE?MergeTree?ORDER?BY?id')
#?新增数据
zs?=?[1,?'张三',?23]
ls?=?[2,?'李四',?24]
ww?=?[3,?'王五',?35]
data?=?[zs,?ls,?ww]
client.insert(table,?data,?column_names=columns)
#?根据ID查询数据
id?=?1
result?=?client.query(f'SELECT?*?FROM?{table}?where?id={id}')
print(result.result_rows)
#?删除数据库表
client.command(f'drop?table?if?exists?{table}')
查询所有数据
import?clickhouse_connect
#?07?查询所有数据
#?准备参数
host?=?"127.0.0.1"
port?=?8123
username?=?"zhangdapeng"
password?=?"zhangdapeng520"
database?=?"default"
#?建立连接
client?=?clickhouse_connect.get_client(
host=host,
port=port,
database=database,
username=username,
password=password,
)
#?创建数据库表
table?=?"user"
columns?=?["id",?"name",?"age"]
client.command(f'CREATE?TABLE?{table}?(id?UInt32,?name?String,?age?UInt32)?ENGINE?MergeTree?ORDER?BY?id')
#?新增数据
zs?=?[1,?'张三',?23]
ls?=?[2,?'李四',?24]
ww?=?[3,?'王五',?35]
data?=?[zs,?ls,?ww]
client.insert(table,?data,?column_names=columns)
#?查询所有数据
result?=?client.query(f'SELECT?*?FROM?{table}')
print(result.result_rows)
#?删除数据库表
client.command(f'drop?table?if?exists?{table}')
根据ID修改数据
import?time
import?clickhouse_connect
#?08?根据ID修改数据
#?准备参数
host?=?"127.0.0.1"
port?=?8123
username?=?"zhangdapeng"
password?=?"zhangdapeng520"
database?=?"default"
#?建立连接
client?=?clickhouse_connect.get_client(
host=host,
port=port,
database=database,
username=username,
password=password,
)
#?创建数据库表
table?=?"user"
columns?=?["id",?"name",?"age"]
client.command(f'CREATE?TABLE?IF?NOT?EXISTS?{table}?(id?UInt32,?name?String,?age?UInt32)?ENGINE?MergeTree?ORDER?BY?id')
#?新增数据
zs?=?[1,?'张三',?23]
ls?=?[2,?'李四',?24]
ww?=?[3,?'王五',?35]
data?=?[zs,?ls,?ww]
client.insert(table,?data,?column_names=columns)
#?根据ID修改数据
id?=?1
client.command(f"ALTER?TABLE?{table}?UPDATE?name='张三333'?where?id={id}")
time.sleep(1)
#?根据ID查询数据
id?=?1
result?=?client.query(f'SELECT?*?FROM?{table}?where?id={id}')
print(result.result_rows)
#?删除数据库表
client.command(f'drop?table?if?exists?{table}')
根据ID删除数据
import?time
import?clickhouse_connect
#?09?根据ID删除数据
#?准备参数
host?=?"127.0.0.1"
port?=?8123
username?=?"zhangdapeng"
password?=?"zhangdapeng520"
database?=?"default"
#?建立连接
client?=?clickhouse_connect.get_client(
host=host,
port=port,
database=database,
username=username,
password=password,
)
#?创建数据库表
table?=?"user"
columns?=?["id",?"name",?"age"]
client.command(f'CREATE?TABLE?IF?NOT?EXISTS?{table}?(id?UInt32,?name?String,?age?UInt32)?ENGINE?MergeTree?ORDER?BY?id')
#?新增数据
zs?=?[1,?'张三',?23]
ls?=?[2,?'李四',?24]
ww?=?[3,?'王五',?35]
data?=?[zs,?ls,?ww]
client.insert(table,?data,?column_names=columns)
#?根据ID删除数据
id?=?1
client.command(f"ALTER?TABLE?{table}?DELETE?WHERE?id={id}")
time.sleep(1)
#?查询数据
result?=?client.query(f'SELECT?*?FROM?{table}')
print(result.result_rows)
#?删除数据库表
client.command(f'drop?table?if?exists?{table}')
分页查询数据
import?clickhouse_connect
#?10?分页查询数据
#?准备参数
host?=?"127.0.0.1"
port?=?8123
username?=?"zhangdapeng"
password?=?"zhangdapeng520"
database?=?"default"
#?建立连接
client?=?clickhouse_connect.get_client(
host=host,
port=port,
database=database,
username=username,
password=password,
)
#?创建数据库表
table?=?"user"
columns?=?["id",?"name",?"age"]
client.command(f'CREATE?TABLE?{table}?(id?UInt32,?name?String,?age?UInt32)?ENGINE?MergeTree?ORDER?BY?id')
#?新增数据
zs?=?[1,?'张三',?23]
ls?=?[2,?'李四',?24]
ww?=?[3,?'王五',?35]
data?=?[zs,?ls,?ww]
client.insert(table,?data,?column_names=columns)
#?分页查询数据
result?=?client.query(f'SELECT?*?FROM?{table}?limit?2?offset?0')
print(result.result_rows)
#?删除数据库表
client.command(f'drop?table?if?exists?{table}')
排序查询数据
import?clickhouse_connect
#?11?排序查询数据
#?准备参数
host?=?"127.0.0.1"
port?=?8123
username?=?"zhangdapeng"
password?=?"zhangdapeng520"
database?=?"default"
#?建立连接
client?=?clickhouse_connect.get_client(
host=host,
port=port,
database=database,
username=username,
password=password,
)
#?创建数据库表
table?=?"user"
columns?=?["id",?"name",?"age"]
client.command(f'CREATE?TABLE?{table}?(id?UInt32,?name?String,?age?UInt32)?ENGINE?MergeTree?ORDER?BY?id')
#?新增数据
zs?=?[1,?'张三',?23]
ls?=?[2,?'李四',?24]
ww?=?[3,?'王五',?35]
data?=?[zs,?ls,?ww]
client.insert(table,?data,?column_names=columns)
#?排序查询数据
result?=?client.query(f'SELECT?*?FROM?{table}?order?by?age?desc?limit?2?offset?0')
print(result.result_rows)
#?删除数据库表
client.command(f'drop?table?if?exists?{table}')
总结
本教程讲解了Python操作ClickHouse的常用操作,包括:
新增
删除
修改
查询
分页
排序
另外,还讲解了如何基于Docker部署ClickHouse。
领取专属 10元无门槛券
私享最新 技术干货