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

from crate import client
import os, time, datetime

##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.56.10:4200/", username="devtest", password="123456")
cursor = connection.cursor()

def insert_data_cratedb():
    for i in range(1, 10):
        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)

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

CREATE TABLE IF NOT EXISTS "db_test"."metric_local" (
   "app" TEXT NOT NULL,
   "block_qps" BIGINT NOT NULL,
   "count" INTEGER NOT NULL,
   "exception_qps" BIGINT NOT NULL,
   "gmt_create" TIMESTAMP WITH TIME ZONE NOT NULL,
   "gmt_modified" TIMESTAMP WITH TIME ZONE NOT NULL,
   "id" BIGINT,
   "machine_ip" TEXT,
   "pass_qps" BIGINT NOT NULL,
   "resource" TEXT NOT NULL,
   "resource_code" INTEGER NOT NULL,
   "rt" DOUBLE PRECISION NOT NULL,
   "success_qps" BIGINT NOT NULL,
   "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL,
   "month" TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc(‘month‘, "timestamp")
)
CLUSTERED INTO 4 SHARDS
PARTITIONED BY ("month")
WITH (
   "allocation.max_retries" = 5,
   "blocks.metadata" = false,
   "blocks.read" = false,
   "blocks.read_only" = false,
   "blocks.read_only_allow_delete" = false,
   "blocks.write" = false,
   codec = ‘default‘,
   column_policy = ‘strict‘,
   "mapping.total_fields.limit" = 1000,
   max_ngram_diff = 1,
   max_shingle_diff = 3,
   number_of_replicas = ‘0-1‘,
   refresh_interval = 1000,
   "routing.allocation.enable" = ‘all‘,
   "routing.allocation.total_shards_per_node" = -1,
   "store.type" = ‘fs‘,
   "translog.durability" = ‘REQUEST‘,
   "translog.flush_threshold_size" = 536870912,
   "translog.sync_interval" = 5000,
   "unassigned.node_left.delayed_timeout" = 60000,
   "write.wait_for_active_shards" = ‘1‘
)