Linux之centos安装clinkhouse以及python如何连接

1.安装

clinkhouse之安装以及python操作

1.Clickhouse 仅支持Linux 且必须支持SSE4.2 指令集
grep -q sse4_2 /proc/cpuinfo && echo "SSE 4.2 supported" || echo "SSE 4.2 not supported"
显示
SSE 4.2 supported


2.首先去根目录创建文件
mkdir /myclinkhouse
cd /myclinkhouse
3.直接复制下载
wget --content-disposition https://packagecloud.io/Altinity/clickhouse/packages/el/7/clickhouse-server-common-20.3.12.112-1.el7.x86_64.rpm/download.rpm

wget --content-disposition https://packagecloud.io/Altinity/clickhouse/packages/el/7/clickhouse-server-20.3.12.112-1.el7.x86_64.rpm/download.rpm

wget --content-disposition https://packagecloud.io/Altinity/clickhouse/packages/el/7/clickhouse-common-static-20.3.12.112-1.el7.x86_64.rpm/download.rpm

wget --content-disposition https://packagecloud.io/Altinity/clickhouse/packages/el/7/clickhouse-client-20.3.12.112-1.el7.x86_64.rpm/download.rpm

4.解压安装
rpm -ivh ./*.rpm

5.卸载（当你装错了）
# 卸载及删除安装文件（需root权限）
yum list installed | grep clickhouse
yum remove -y clickhouse-common-static
yum remove -y clickhouse-server-common
rm -rf /var/lib/clickhouse
rm -rf /etc/clickhouse-*
rm -rf /var/log/clickhouse-server

6.创建用户名密码
password=$(base64 < /dev/urandom | head -c8); echo "$password"; 
echo -n "$password" | sha256sum | tr -d '-'
输出：明文  密文
RaSQ8uTe
1bf9e7c1e8692697f360a40aabfeaa8ae386b304482cdd81e597a36f426d432c


7.修改配置文件
vim /etc/clickhouse-server/config.xml
修改下面这个
<listen_host>0.0.0.0</listen_host>

vim /etc/clickhouse-server/users.xml
#找到 users --> default --> 标签下的password修改成password_sha256_hex，并把密文填进去
<password_sha256_hex>1bf9e7c1e8692697f360a40aabfeaa8ae386b304482cdd81e597a36f426d432c</password_sha256_hex>

8.启动以及重启
service clickhouse-server start
service clickhouse-server restart


9.连接
9.1直接连接
clickhouse-client
9.2用命令连接
clickhouse-client -h wusen0601.xyz -d default -m -u default --password 明文

2.python使用

# pip install clickhouse-driver
from clickhouse_driver import Client
import random

host = "wusen0601.xyz"
port = "9000"
user = "default"
password = "RaSQ8uTe1"
database = "default"
client = Client(host=host, port=port, user=user, password=password, database=database)
# client = Client(host=host, port=port, database=database)
sql = f"""
select enterprise_name,bike_type,count(*) as nums
from t_bike_location_real
GROUP BY (enterprise_name,bike_type)
"""

enterprise_name_list = [
    {"enterprise_name": "哈罗", "enterprise_id": "10001", "bike_type": random.randrange(0, 2),"bike_id":random.randrange(100000,1000000)},
    {"enterprise_name": "美团", "enterprise_id": "10002", "bike_type": random.randrange(0, 2),"bike_id":random.randrange(100000,1000000)},
    {"enterprise_name": "骑电", "enterprise_id": "10003", "bike_type": random.randrange(0, 2),"bike_id":random.randrange(100000,1000000)}
]
index_random = random.randrange(0,len(enterprise_name_list))
insert_data = enterprise_name_list[index_random]
insert_sql = f"""
insert into t_bike_location_real
(enterprise_name,enterprise_id,bike_type,bike_id)

values('{insert_data['enterprise_name']}','{insert_data['enterprise_id']}','{insert_data['bike_type']}','{insert_data['bike_id']}')
"""
print(insert_sql)
for i in range(100):
    ans = client.execute(insert_sql)

    print(ans)