抓取慢sql

function : collect slowly sql greate than 5 minute
author : liushan

   #! /usr/bin/env python
   # -*- coding:utf-8 -*- import os import re import time import sys
   
   class Base_conf(object):
       def __init__(self):
           self.sqlfile='/tmp/.slowsql.sql'
           self.exec_comm1='offender -s active -t 300 -s in_sql'
           self.exec_comm2= '"explain for qid '
           self.exec_comm3='trafci.sh -h xx.xx.xx.xx -u xx-p xx -j yes -q '
           self.exec_comm4='trafci.sh -h xx.xx..xx.xx -u xx-p xx -j yes -s '
   
       def exec_log(self,log):
           log_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
           logfile = os.path.abspath(os.path.join(os.getcwd(), "slowSql.log"))
           with open(logfile, 'a+') as f:
               if os.path.getsize(logfile) > 1073741824:
                   f.seek(0)
                   f.truncate()
                   f.write("begin time: -- " + log_time + " -- \n" + log + "\nend time:  -- " + log_time + " -- \n")
               else:
                   f.write("begin time: -- " + log_time + " -- \n" + log + "\nend time:  -- " + log_time + " -- \n")
   
       def exec_m_log(self,log):
           log_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
           log_m_file = os.path.abspath(os.path.join(os.getcwd(), ".slow_m_sql.log"))
   
           with open(log_m_file, 'a+') as f:
               if os.path.getsize(log_m_file) > 1073741824:
                   f.seek(0)
                   f.truncate()
                   f.write("begin time: -- " + log_time + " -- \n" + log + "\nend time:  -- " + log_time + " -- \n")
               else:
                   #print("====>{}".format(log), type(log))
                   f.write("begin time: -- " + log_time + " -- \n" + log + "\nend time:  -- " + log_time + " -- \n")

  class Get_sql(Base_conf):
   
       def qid_handle(self,qid):
           commd=Base_conf.exec_comm3+Base_conf.exec_comm2 + str(qid)+'"'
           qid_sql_se=os.popen(commd).read()
           kg="                           "
           Base_conf.exec_m_log(qid_sql_se)
           qid_sql_se_sp=re.findall('STATEMENT \S+\s([\S\s]+)\s--.*[\S\s]+NODE LISTING',qid_sql_se)
           qid_sql_se_sp=''.join(qid_sql_se_sp).replace(kg,' ').replace("\r\n",'').replace("\t",'').replace("\n",'').replace(";","")
           return qid_sql_se_sp
   
       def users(self,qid):
           filename='/tmp/.slow_sql.sql'
           commd='select auth_db_name from "_MD_".auths where trim(auth_type)||auth_ID=(SELECT
   QUERYID_EXTRACT('+"'"+qid+"'"+",'USERNAME') FROM DUAL);"
           with open(filename,'w') as f:
               f.write(commd)
               f.write('\nexit\n')
           commd_script=Base_conf.exec_comm4+filename
           commd_rlt=re.findall('AUTH.*\s+\S+\s+(\S+) ',os.popen(commd_script).read())
           commd_rlt=''.join(commd_rlt)
           return str(commd_rlt)
   
       def clear(self,sqlf):
   
           with open(sqlf,'w') as f1:
               f1.seek(0)
               f1.truncate()
   
       def sql_generate(self,*args):
           sql_ctab="create table TRAFODION.seabase.slow_sql (qid varchar(90 bytes) CHARACTER SET UTF8,sql_users varchar(30 bytes)
   CHARACTER SET UTF8,elaps_time varchar(10 byte) CHARACTER SET UTF8,
   slow_sqls varchar(5000 bytes) CHARACTER SET UTF8,ActRowsAccess
   NUMERIC(15),ActRowsUsed NUMERIC(15),RowsReturned
   NUMERIC(15),SE_IO_KBytes NUMERIC(15),create_time
   timestamp(0),PROVINCE VARCHAR(32 bytes) CHARACTER SET UTF8,client_ip
   VARCHAR(15 bytes) CHARACTER SET UTF8, CONSTRAINT
   TRAFODION.seabase.pk_slow_sql PRIMARY KEY (qid)) ATTRIBUTES ALIGNED
   FORMAT HBASE_OPTIONS (DATA_BLOCK_ENCODING = 'FAST_DIFF',COMPRESSION =
   'SNAPPY',MEMSTORE_FLUSH_SIZE = '1073741824');"
           # sql_ctab="create table TRAFODION.seabase.slow_sql (qid varchar(90 bytes) CHARACTER SET UTF8,sql_users varchar(30 bytes)
   CHARACTER SET UTF8,elaps_time varchar(10 byte) CHARACTER SET UTF8,
   slow_sqls varchar(5000 bytes) CHARACTER SET UTF8,ActRowsAccess
   varchar(15 bytes) CHARACTER SET UTF8,ActRowsUsed varchar(15 bytes)
   CHARACTER SET UTF8,RowsReturned varchar(15 bytes) CHARACTER SET
   UTF8,create_time timestamp(0),PROVINCE VARCHAR(32 bytes) CHARACTER
   SET UTF8, CONSTRAINT TRAFODION.seabase.pk_slow_sql PRIMARY KEY (qid))
   ATTRIBUTES ALIGNED FORMAT HBASE_OPTIONS (DATA_BLOCK_ENCODING =
   'FAST_DIFF',COMPRESSION = 'SNAPPY',MEMSTORE_FLUSH_SIZE =
   '1073741824');"
           sql_args=str(args[3]).replace("'","''")
           if str(args[5][0])=='0' and str(args[5][1])=='0' and str(args[5][2])=='0':
               return
           else:
               args_val="'"+str(args[0])+"','"+str(args[1])+"','"+str(args[2])+"','"+sql_args+"','"+str(args[4])+"',nvl("+str(args[5][0])+",0),nvl("+str(args[5][1])+",0),nvl("+str(args[5][2])+",0),nvl("+str(args[5][3])+",0),'"+str(args[6])+"'"
               sql_itab="upsert into seabase.slow_sql(qid,sql_users,elaps_time,slow_sqls,PROVINCE,ACTROWSACCESS,ACTROWSUSED,ROWSRETURNED,SE_IO_KBytes,client_ip,create_time)
   values("+args_val+",now());"
               sqlfilesize=os.path.getsize(sqlfile)
               with open(sqlfile,'a') as f:
                   if sqlfilesize<=1:
                       f.write(sql_ctab)
                       f.write("\n")
                   f.write(sql_itab)
                   f.write("\n")
   
       def exec_sql(self):
           with open(Base_conf.sqlfile,'a+') as f:
               f.write("\nexit\n")
           sql_commd=Base_conf.exec_comm4+Base_conf.sqlfile
           sql_log=os.popen(sql_commd).read()
           Base_conf.exec_log(sql_log)
           return sql_log
   
       def province(self,u):
           provincefile = '/tmp/.province.sql'
           sql="SELECT distinct province FROM DB_MGR.MGR_USER WHERE uname=upper('"+u+"') or uname=lower('"+u+"');"
           with open(provincefile,'w') as f:
               f.write(sql)
               f.write("\nexit\n")
           sql_commd=Base_conf.exec_comm4+provincefile
           sql_log=re.findall('---.*\s(\S+)\s+--',os.popen(sql_commd).read())
           sql_log_rlt=''.join(sql_log).decode('string_escape')
           return sql_log_rlt
   
       def ipdr(self,qid):
           provincefile = '/tmp/.province.sql'
           conn = "http://xx.xx.xx.xx:xxx"
           sql="select queryid_extract('%s','PIN')from dual;" %qid
           with open(provincefile,'w') as f:
               f.write(sql)
               f.write("\nexit\n")
           sql_commd=Base_conf.exec_comm4+provincefile
   
           sql_rlt = re.findall('--.*\s(\d+)\s+--',os.popen(sql_commd).read())
           sql_rlt=''.join(sql_rlt)
           connect = "curl -X GET -H 'Accept: application/json' %s/servers.jsp -k|grep -iF6 %s|sed -n '$p' |egrep -o [0-9.]+"
   %(conn,sql_rlt)
           if os.system(connect) ==0:
               connect_rlt = os.popen(connect).read()
               connect_rt = connect_rlt.strip('\n')
               return connect_rt
           else:
               return 0
   
       def statics_info(self,id):
           # m_log = base_conf()
           accrows = 0
           userows = 0
           seioks = 0
           commd="'get statistics for qid "+id+"'"
           exec_commd=Base_conf.exec_comm3+commd
           # m_log.exec_m_log(os.popen(exec_commd).read())
           exec_commd_rt=re.findall('Returned\s+(\d+)',os.popen(exec_commd).read())
           if "Returned" in exec_commd_rt:
               exec_commd_rt=''.join(exec_commd_rt).strip()
           else:
               exec_commd_rt=0
           exec_commd_row_SE = re.findall('(SE_IO_MaxTime.*\s+[\s\S]+)--',
   os.popen(exec_commd).read())
           if 'SE_IO_MaxTime' in str(exec_commd_row_SE):
               exec_commd_row1 = re.findall('SE_IO_MaxTime.*\s+([\s\S]+)--',
   os.popen(exec_commd).read())
               exec_commd_row=''.join(exec_commd_row1).replace("\n\n","").split('\n')
               if len(exec_commd_row)>1 and exec_commd_row1:
                   for i in exec_commd_row:
                       if 'TRAF' in i or 'hdfs:' in i or i is None or i=='   ':
                           continue
                       ii=i.replace(',','')
                       seiok=re.findall('^(\s*\S+){7,7}',ii)
                       seiok_str=''.join(seiok).strip()
                       arows=re.findall('^\s*\d+\s+([\d,]+)\s+\S+\s+[\d,]+',ii)
                       urows = re.findall('^\s*\d+\s+[\d,]+\s+\S+\s+([\d,]+)', ii)
                       arows=''.join(arows).strip()
                       urows=''.join(urows).strip()
                       accrows+=int(arows)
                       userows+=int(urows)
                       seioks+=int(seiok_str)
                   rows_hz = int(accrows),int(userows),int(exec_commd_rt),int(seioks)
                   return rows_hz
           else:
               rows_hz = 0, 0, int(exec_commd_rt)
               return rows_hz

  if __name__ == '__main__':
       get_sql=Get_sql()
       Base_conf=Base_conf()
       while True:
           comm_rlts = os.popen(get_sql.exec_comm1).read().split('-----\n\n')
           comm_rlts_str = ''.join(comm_rlts[1]).replace('\r\n', '').replace('\t', '').split('\n')
           sqlfile = Base_conf.sqlfile
           get_sql.clear(sqlfile)
           for i in comm_rlts_str:
               line = re.findall('(\d+)\s+(MXID\S+)\s+', i)
               if line:
                   qids=line[0][1]
                   elaps_time=line[0][0]
                   sql_ipdr=get_sql.ipdr(qids)
                   slow_sqls=get_sql.qid_handle(qids)
                   sql_users=get_sql.users(qids)
                   sql_province=get_sql.province(sql_users)
                   sql_statics = get_sql.statics_info(qids)
                   sql_text=get_sql.sql_generate(qids,sql_users,elaps_time,slow_sqls,sql_province,sql_statics,sql_ipdr)
                   print("QID: %s -- users: %s -- elaps_time: %s -- slow_sql: %s -- province: %s -- sql_statics: %s -- sql_ipdr: %s"
   %(qids,sql_users,elaps_time,slow_sqls,sql_province,sql_statics,sql_ipdr))
           exec_output=get_sql.exec_sql()
           time.sleep(300)