python連線clickhouse資料庫
在python中獲取系統資訊的一個好辦法是使用psutil這個第三方模組。
顧名思義，psutil = process and system utilities，它不僅可以透過一兩行程式碼實現系統監控，還可以跨平臺使用。
主要針對clickhouse_driver的使用進行簡要介紹
第一步：
透過pip install clickhouse_driver 安裝 clickhouse_driver
第二步：
方法一：使用clickhouse_driver 包中的client類,透過例項化一個客戶端進行對資料庫的增刪改查操作
from clickhouse_driver import client
from datetime import datetime
import psutil
host_name = '192.168.50.94'
client = client(host=host_name,database='default',user='default',password='自己設的密碼',send_receive_timeout=20,port=55666)
now = datetime.now()
time_stamp = now.strftime('%a %b %d %h:%m:%s cst %y')# tue apr 06 15:32:55 cst 2021 <class 'str'>
create_at = datetime.now().strftime('%y-%m-%d %h:%m:%s')
disk_io = psutil.disk_io_counters()
net_io = psutil.net_io_counters()
chart_name = ["磁碟io","網路io"]
metric_name1 = ["讀(數量)","寫(數量)", "讀(位元組)", "寫(位元組)", "讀(時間)", "寫(時間)"]
metric_name2 = ["傳送位元組數","接收位元組數","傳送包數","接收包"]
metric_value1 = [disk_io.read_count,disk_io.write_count,disk_io.read_bytes,disk_io.write_bytes,disk_io.read_time,disk_io.write_time]
metric_value2 = [net_io.bytes_sent,net_io.bytes_recv,net_io.packets_sent,net_io.packets_recv]
try:
for i in chart_name:
if i is "磁碟io":
for j in metric_name1:
sql = "insert into clickhouse_host_metrics777(time_stamp,host_name, chart_name, metric_name,metric_value,create_at) " \
"values('%s','%s','%s','%s','%s','%s')" % \
(time_stamp, host_name, i, j, metric_value1[metric_name1.index(j)], create_at)
res = client.execute(sql)
elif i is "網路io":
for j in metric_name2:
sql = "insert into clickhouse_host_metrics777(time_stamp,host_name, chart_name, metric_name,metric_value,create_at) " \
"values('%s','%s','%s','%s','%s','%s')" % \
(time_stamp, host_name, i, j, metric_value2[metric_name2.index(j)], create_at)
res = client.execute(sql)
print("成功寫入資料")
except exception as e:
print(str(e))
方法二：使用clickhouse_driver 包中的connect函式,透過例項化一個客戶端進行對資料庫的增刪改查操作
from datetime import datetime
import psutil
from clickhouse_driver import connect
host_name = '192.168.50.94'
#賬號:密碼@主機名:埠號/資料庫
conn = connect('clickhouse://default:自己設的密碼@'+host_name+':55666/default')
cursor = conn.cursor()
now = datetime.now()
time_stamp = now.strftime('%a %b %d %h:%m:%s cst %y')# tue apr 06 15:32:55 cst 2021 <class 'str'>
create_at = datetime.now().strftime('%y-%m-%d %h:%m:%s')
disk_io = psutil.disk_io_counters()
net_io = psutil.net_io_counters()
chart_name = ["磁碟io","網路io"]
metric_name1 = ["讀(數量)","寫(數量)", "讀(位元組)", "寫(位元組)", "讀(時間)", "寫(時間)"]
metric_name2 = ["傳送位元組數","接收位元組數","傳送包數","接收包"]
metric_value1 = [disk_io.read_count,disk_io.write_count,disk_io.read_bytes,disk_io.write_bytes,disk_io.read_time,disk_io.write_time]
metric_value2 = [net_io.bytes_sent,net_io.bytes_recv,net_io.packets_sent,net_io.packets_recv]
try:
for i in chart_name:
if i is "磁碟io":
for j in metric_name1:
sql = "insert into clickhouse_host_metrics777(time_stamp,host_name, chart_name, metric_name,metric_value,create_at) values('%s','%s','%s','%s','%s','%s')" % \
(time_stamp, host_name, i, j, metric_value1[metric_name1.index(j)], create_at)
# res = client.execute(sql)
res = cursor.execute(sql)
elif i is "網路io":
for j in metric_name2:
sql = "insert into clickhouse_host_metrics777(time_stamp,host_name, chart_name, metric_name,metric_value,create_at) values('%s','%s','%s','%s','%s','%s')" % \
(time_stamp, host_name, i, j, metric_value2[metric_name2.index(j)], create_at)
res = cursor.execute(sql)
cursor.close()
print("成功寫入資料")
except exception as e:
print(str(e))
python將資料寫入clickhouse
from clickhouse_driver import client
# connect clickhouse
client = client(host= ,port= ,user= ,database= , password=)
# 得到table1中查詢的資料匯入table2中（database2中應該事先建立對應的table2表）
query_ck_sql = """ select *
from database1.table1
where date = today() """
# 匯入資料到臨時表
try:
# 匯入資料
client.execute("insert into {official_table_db}.{official_all_table_name} \
{query_ck_sql}".format(
official_table_db = database2,
official_table_name = table2,
query_ck_sql = query_ck_sql)
,types_check = true)
except exception as e:
print str(e)
以上為個人經驗，希望能給大家一個參考，也希望大家多多支援