Do not have universality, stay as a souvenir.
[[email protected] python]#Cat insert_uv.py#!/usr/bin/env python#-*-coding:utf-8-*- fromDatetimeImport* fromwith_conn_to_dbImportConn_to_mysqlImportUrllib2,jsonImport Time## #define Yestoday 0-24 hours Delta part##########Today =date.today () yestoday= Today-timedelta (Days=1)#today = Today-timedelta (Days=1)#Print Today,yestoday#Import SYS#sys.exit ()A = str (yestoday) +' '+'00:00:00'b= STR (today) +' '+'00:00:00'#print A, bTimeArray1 = Time.strptime (A,"%y-%m-%d%h:%m:%s") TimeArray2= Time.strptime (b,"%y-%m-%d%h:%m:%s") Start_time= Int (Time.mktime (timeArray1)) * 1000End_time= Int (Time.mktime (timeArray2)) * 1000## # # #define ES index and search part########Server ='http://elk.xkops.com:9200/'#stat_index = ' client-visit-* 'index='client-*'#start_time = 1459146210879#stop_time = 1459147110879URL = Server + index +"/_search?pretty=true"query_date={ "Query": { "Filtered": { "Query": { "query_string": { "Analyze_wildcard": True,"Query":"*" } }, "Filter": { "BOOL": { "must": [ { "Range": { "@timestamp": { "GTE": Start_time,"LTE": End_time,"format":"Epoch_millis" } } } ], "Must_not": [] } } } }, "size": 0,"Aggs": { "4": { "Terms": { "Field":"visit_tenant_id", "size": 1000000, "Order": { "1":"desc" } }, "Aggs": { "1": { "sum": { "Field":"Count" } }, "3": { "Terms": { "Field":"Client_type", "size": 2, "Order": { "1":"desc" } }, "Aggs": { "1": { "sum": { "Field":"Count" } }, "2": { "Terms": { "Field":"sessionId", "size": 1000000, "Order": { "1":"desc" } }, "Aggs": { "1": { "sum": { "Field":"Count"} }}}}}}}query_date=Json.dumps (query_date) Req=Urllib2. Request (url,query_date) Response=Urllib2.urlopen (req) page=Response.read ()#Print Pageresult =json.loads (page)## #避免当天多次插入, remove ####### before insertingsql ="Delete from uv_stat where create_time = '%s '"%(Yestoday)#Print SQLWith Conn_to_mysql ('Logstash') as Db:db.execute (SQL) forSinchresult['Aggregations']['4']['Buckets']: tenant_id= s['Key'] Type1= s['3']['Buckets'][0]['Key'] forAinchs['3']['Buckets'][0]['2']['Buckets']: session_id= a['Key'] #Print tenant_id,type1,session_idsql ="INSERT INTO Uv_stat (Tenant_id,create_time,session_id,client_type) VALUES ('%s ', '%s ', '%s ', '%s ')"%(tenant_id,yestoday,session_id,type1)#Print SQLWith Conn_to_mysql ('Logstash') as Db:db.execute (SQL)ifLen (s['3']['Buckets']) > 1: type2= s['3']['Buckets'][1]['Key'] forAinchs['3']['Buckets'][1]['2']['Buckets']: session_id= a['Key'] #Print tenant_id,type2,session_idsql ="INSERT INTO Uv_stat (Tenant_id,create_time,session_id,client_type) VALUES ('%s ', '%s ', '%s ', '%s ')"%(tenant_id,yestoday,session_id,type2)#Print SQLWith Conn_to_mysql ('Logstash') as Db:db.execute (SQL)Else: Continue
Elk-python (iii)