python写入数据到clickhouse

环境:

python3.6

 clickhouse-server-20.8.3.18

 

1.加载clickhouse驱动包

pip3 install clickhouse_driver

 

2.表结构

全局表

CREATE TABLE db_test.metric_all
(
    `app` String,
    `block_qps` Int64,
    `count` Int64,
    `exception_qps` Int64,
    `gmt_create` DateTime DEFAULT now(),
    `gmt_modified` DateTime DEFAULT now(),
    `id` Int64,
    `machine_ip` Nullable(String),
    `pass_qps` Int64,
    `resource` String,
    `resource_code` Int64,
    `rt` Float64,
    `success_qps` Int64,
    `timestamp` DateTime DEFAULT now(),
    `day` Date DEFAULT now()
)
ENGINE = Distributed('perftest_3shards_1replicas', 'db_test', 'metric_local', id)

 

本地表

CREATE TABLE db_test.metric_local
(
    `app` String,
    `block_qps` Int64,
    `count` Int64,
    `exception_qps` Int64,
    `gmt_create` DateTime DEFAULT now(),
    `gmt_modified` DateTime DEFAULT now(),
    `id` Int64,
    `machine_ip` Nullable(String),
    `pass_qps` Int64,
    `resource` String,
    `resource_code` Int64,
    `rt` Float64,
    `success_qps` Int64,
    `timestamp` DateTime DEFAULT now(),
    `day` Date DEFAULT now()
)
ENGINE = MergeTree
PARTITION BY day
ORDER BY id
SETTINGS index_granularity = 8192

 

 

3.写入数据

from clickhouse_driver import Client


client = Client(host='192.168.1.118',database='db_test',user='dbaadmin' ,password='123456')

for i in range(201, 100000):
    str_i = str(i)
    ##insert_sql = "insert into user_local(id,name) values ('%s','%s')" % (i, "name" + str_i)
    insert_sql = "insert into metric_all(app,block_qps,count,exception_qps,id,machine_ip,pass_qps,resource,resource_code,rt,success_qps) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
    "app" + str_i, i, i, i, i, "machine_ip" + str_i, i, "resource" + str_i, i, i, i)
    ans = client.execute(insert_sql)

 

批量写入

#!/usr/bin/env python
#coding=utf-8
import os, json, urllib, datetime, shutil,random,uuid
from random import choice
import pymysql
import traceback
import time
from clickhouse_driver import Client
gl_mysql_server = "192.168.1.183"
gl_user_name = "root"
gl_password = "mysql"
gl_db_name = "db_sentinel"
gl_db_name_msg="db_pushmsg"
gl_port=9030



def insert_data_message():
    ##db = pymysql.connect(host=gl_mysql_server,user=gl_user_name,password=gl_password,db=gl_db_name_msg,port=gl_port,use_unicode=True,charset="utf8")
    client = Client(host='192.168.1.136', database='db_pushmsg', user='default', password='123456')
    # 得到一个可以执行SQL语句的光标对象
    ##cursor = db.cursor()
    ##db.autocommit(0) ## 关闭字自动提交
    ip_list = ['192.168.1.1', '192.168.1.2', '192.168.1.3', '192.168.1.4', '192.168.1.5', '192.168.1.6', '192.168.1.7','192.168.1.8', '192.168.1.9', '192.168.1.10', '192.168.1.11', '192.168.1.12', '192.168.1.13', '192.168.1.14', '192.168.1.15']
    message_type=[0,2,3,6,8,9,16,4096,8196,8448,12292,16385,16386,16387,32770,65536,69632,73728,77824]
    massive_type=[0,1000,1001,1002,1003,1004,1005,1006,1007]
    status = [1000,2000]
    insert_sql = "insert into app_message_all(user_id,message_type,massive_type,status,create_time,message_id,sender_seq_no,title,message,extra,send_date,deleted ) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    data = []
    i = 0
    for i in range(1, 5000001):
        try:
            str_i = str(i)
            curr_time = datetime.datetime.now()
            l_user_id=random.randint(10000000, 99999999)
            l_message_type = choice(message_type)
            l_massive_type = choice(massive_type)
            l_status = choice(status)
            l_create_time = curr_time
            l_message_id=random.randint(1000000000000000, 9999999999999999)
            l_sender_seq_no=str(uuid.uuid4())
            l_title='测试测试'+str_i
            l_message='测试'+str_i+'测试测试'
            l_extra='{"hospitalId":1111,"vchildCode":"xxx0xxxxxx1xxx6000xxxx","showMessage":xxxxxx。","messageTypeTitle":"门诊通知","childId":19509955,"templateId":36}'
            l_send_date =curr_time
            l_deleted=0
            a = ['', '', '', '', '', '', '', '', '', '', '', '']
            a[0] = l_user_id
            a[1] = l_message_type
            a[2] = l_massive_type
            a[3] = l_status
            a[4] = l_create_time
            a[5] = l_message_id
            a[6] = l_sender_seq_no
            a[7] = l_title
            a[8] = l_message
            a[9] = l_extra
            a[10] = l_send_date
            a[11] = l_deleted
            data.append(a)
            i = i+1
            if i % 10000== 0:
                i = 0
                client.execute("insert into app_message_all(user_id,message_type,massive_type,status,create_time,message_id,sender_seq_no,title,message,extra,send_date,deleted) values", data, types_check=True)
                data = []
        except Exception as e:
            print(e)
    if len(data) > 0:
          ans = client.execute("insert into app_message_all(user_id,message_type,massive_type,status,create_time,message_id,sender_seq_no,title,message,extra,send_date,deleted) values", data, types_check=True)

    return 0



if __name__ == '__main__':
    print("开始时间:"+time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
    l_flag=insert_data_message()
    print("结束时间:"+time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))