cratedb导出数据到clickhouse

 

python版本:3.6

 

#!/usr/bin/env python
#coding=utf-8

from crate import client
import os, time, datetime
import time
from clickhouse_driver import Client
import logging

##client = Client(host='192.168.56.10',database='db_test',user='dbaadmin' ,password='123456')
##client = Client(host='192.168.56.10',database='db_test')
connection = client.connect("http://192.168.1.136:4200/", username="devtest", password="123456")
cursor = connection.cursor()
outfile='E:/record.txt'

def insert_data_cratedb():
    for i in range(1, 1000001):
        str_i = str(i)
        now_time=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        ##insert_sql = "insert into user_local(id,name) values ('%s','%s')" % (i, "name" + str_i)
        insert_sql = "insert into db_test.metric_local(app,block_qps,count,exception_qps,id,machine_ip,pass_qps,resource,resource_code,rt,success_qps,timestamp,gmt_modified,gmt_create) values ('%s','%s','%s','%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,now_time,now_time,now_time)
        ans = cursor.execute(insert_sql)

def query_cratedb_data(start_datetime,end_datetime):

    sql="select app,block_qps,count,exception_qps,gmt_create,gmt_modified,id,machine_ip,pass_qps,resource,resource_code,rt,success_qps,timestamp,month from sentinel_test.metric where gmt_create>=\'%s\' and gmt_create<\'%s\'" % (start_datetime,end_datetime)
    ##print(sql)
    db_cursor=connection.cursor()
    try:
        cursor.execute(sql)
        result_data = cursor.fetchall()
        record_cnt = len(result_data)
    except Exception as e:
        print(e)
    db_cursor.close()
    return result_data,record_cnt


def insert_ck_data(querydata):
    ##print(querydata)
    client = Client(host='192.168.1.136', database='db_sentinel', user='devtest', password='test123')
    data=[]
    i = 0
    for row in querydata:
        ##print(row)
        try:
            a=['','','','','','','','','','','','','','','',]
            a[0] = row[0]
            a[1] = row[1]
            a[2] = row[2]
            a[3] = row[3]
            a[4] = datetime.datetime.strptime(utc2datetime(row[4]), "%Y-%m-%d %H:%M:%S")
            a[5] = datetime.datetime.strptime(utc2datetime(row[5]), "%Y-%m-%d %H:%M:%S")
            a[6] = row[6]
            a[7] = row[7]
            a[8] = row[8]
            a[9] = row[9]
            a[10] = row[10]
            a[11] = row[11]
            a[12] = row[12]
            a[13] = datetime.datetime.strptime(utc2datetime(row[13]), "%Y-%m-%d %H:%M:%S")
            a[14] = datetime.datetime.strptime(utc2datetime(row[14]), "%Y-%m-%d %H:%M:%S")

            data.append(a)
            i = i+1
            if i==10000:
                i=0
                client.execute("insert into metric_local values", data, types_check=True)
                data = []
        except Exception as e:
            print(e)
    if len(data) > 0:
        ans = client.execute("insert into metric_local values", data, types_check=True)


def utc2datetime(utc_time):
    ##utc_time参数为毫秒
    loc_time = time.localtime(utc_time/1000)
    time1 = time.strftime("%Y-%m-%d %H:%M:%S", loc_time)
    return time1

def sync_by_hour(date_hour):
    sync_date=date_hour
    start_date = datetime.datetime.strptime(sync_date, "%Y-%m-%d %H:%M:%S")
    minute_offset = datetime.timedelta(minutes=1)

    sync_year = start_date.strftime("%Y")
    sync_month = start_date.strftime("%m")
    sync_day = start_date.strftime("%d")
    sync_hour = start_date.strftime("%H")

    i = 0
    while i < 60:
        start_day = sync_year + "-" + sync_month + "-" + sync_day + " " + sync_hour + ":"+ str(i) + ":00"
        run_start_date = datetime.datetime.strptime(start_day, "%Y-%m-%d %H:%M:%S") ##字符串转时间
        start_timedate_str = run_start_date.strftime("%Y-%m-%d %H:%M:%S") ##时间转字符串
        run_end_date = run_start_date + minute_offset
        end_timedate_str = run_end_date.strftime("%Y-%m-%d %H:%M:%S")  ##时间转字符串
        nowtime =  time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
        print(nowtime+",开始查询数据:"+start_timedate_str + "--" + end_timedate_str)
        (querydata, record_cnt) = query_cratedb_data(start_timedate_str,end_timedate_str)
        nowtime =  time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
        print(nowtime+",开始写入:"+start_timedate_str + "--" + end_timedate_str)

        insert_ck_data(querydata)
        i = i+1


if __name__ == '__main__':
    print("开始时间:"+time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())))
    sync_date="2021-09-27 00:00:00"
    start_date = datetime.datetime.strptime(sync_date, "%Y-%m-%d %H:%M:%S")
    sync_year = start_date.strftime("%Y")
    sync_month = start_date.strftime("%m")
    sync_day = start_date.strftime("%d")
    sync_hour = start_date.strftime("%H")
    hour_offset = datetime.timedelta(hours=1)

    hour = 0
    while hour <= 0:
        start_day = sync_year + "-" + sync_month + "-" + sync_day + " " + str(hour) + ":00:00"
        sync_by_hour(start_day)
        hour = hour + 1



    print("结束时间:"+time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))