print('endtime:' + str(endtime) + '\n') ##增量、全量构建 if buildtype == 'full': print('全量构建cube:') resp, resp_code = kylin_api.http_put_full(v_url,cubename) else: print('增量构建cube:') ##run job seg_list=[] req, ret_code = kylin_api.http_get_cube(v_url,cubename) #req=(str(req.text)) load_dict = json.loads(req) for segment in load_dict['segments']: print (segment['name']) seg_list.append(segment['name']) v_refreshtype = 'BUILD' ##judge wether exists segment if len(seg_list): max_index = seg_list.index((max(seg_list))) print(max_index) max_dt = seg_list[max_index] maxdt = max_dt.split('_') maxdt = (','.join(maxdt[1:])[:8]) print('判断参数:')
return None, None if __name__ == '__main__': cf = configparser.ConfigParser() cf.read( '/Users/wonder/Documents/data_dict/kylin/script/conf/kylin_conf.ini') secs = cf.sections() print(secs) option = cf.options("kylin-pwd") v_pwd = cf.get("kylin-pwd", "pwd") # 获取[Mysql-Database]中host对应的值 v_url = cf.get("kylin-pwd", "url") # 获取[Mysql-Database]中host对应的值 v_project = 'iqj_olap' resp, resp_code = kylin_api.http_get_cube(v_url, 'all') load_dict = json.loads(resp) all_cube_table_list = [] for cube in load_dict: if (cube['project']) == 'iqj_olap' and (cube['status']) == 'READY': print(cube['name']) v_cube_name = cube['name'] resp, resp_code, cook = kylin_api.http_get_cube_sql( v_url, v_cube_name) load_dict = json.loads(resp) sql = (load_dict['sql']) sql_extractor = SqlExtractor(sql) for table in sql_extractor.tables: cube_list = []
def main(): print(sys.getdefaultencoding()) cf = configparser.ConfigParser() cf.read( '/Users/wonder/Documents/data_dict/kylin/script/conf/kylin_conf.ini') secs = cf.sections() print(secs) option = cf.options("kylin-pwd") v_pwd = cf.get("kylin-pwd", "pwd") # 获取[Mysql-Database]中host对应的值 v_url = cf.get("kylin-pwd", "url") # 获取[Mysql-Database]中host对应的值 v_project = 'iqj_olap' option_db = cf.options("mysql") v_model_cube_mapping = cf.get( "mysql", "model_cube_mapping") # 获取[Mysql-Database]中host对应的值 v_hbase_cube_mapping = cf.get( "mysql", "hbase_cube_mapping") # 获取[Mysql-Database]中host对应的值 print(v_url) tm_now = time.localtime(time.time()) tm_now_str = time.strftime("%Y-%m-%d %H:%M:%S", tm_now) # cubename = sys.argv[1] # buildtype = sys.argv[2] # runTime = sys.argv[3] + " 08:00:00" # cubename = sys.argv[1] # buildtype = sys.argv[2] # runTime = sys.argv[3] + " 08:00:00" cubename = 'all' runTime = [10000] def date_get(datetime=datetime): now = datetime.now() data_date = now.strftime('%Y-%m-%d') return data_date ##日期差 def days_diff(str1, str2): date1 = datetime.strptime(str1[0:10], "%Y-%m-%d") date2 = datetime.strptime(str2[0:10], "%Y-%m-%d") num = (date1 - date2).days return num ##月份差 def months_diff(str1, str2): year1 = datetime.strptime(str1[0:10], "%Y-%m-%d").year year2 = datetime.strptime(str2[0:10], "%Y-%m-%d").year month1 = datetime.strptime(str1[0:10], "%Y-%m-%d").month month2 = datetime.strptime(str2[0:10], "%Y-%m-%d").month num = (year1 - year2) * 12 + (month1 - month2) return num # def http_put(v_cube, v_startTime, v_endTime): # # url='http://analysi4olap.finupgroup.com/kylin/api/cubes/'+v_cube+'/rebuild' # url = 'http://192.168.176.67:7070' # url = url + '/kylin/api/cubes/' + v_cube + '/rebuild' # values = {"startTime": v_startTime, "endTime": v_endTime, "buildType": "BUILD"} # jdata = json.dumps(values) # 对数据进行JSON格式化编码 # #req = urllibrequest.Request(url, jdata=urllib.parse.urlencode(jdata).encode(encoding='utf-8')) # #req = urllibrequest.Request(url,jdata=urllib.parse.quote(jdata).encode(encoding='utf-8')) # req = urllibrequest.Request(url,jdata=urllib.parse.quote(jdata,encoding='utf-8')) # #request = urllib2.Request(url, data, headers) # # request = urllib.request.Request(url, data=urllib.parse.urlencode(data).encode(encoding='UTF8'), headers=headers) # req.add_header('Content-Type', 'application/json;charset=utf-8') # req.add_header('Authorization', 'Basic YWRtaW46S1lMSU4=') # req.get_method = lambda: 'PUT' # 设置HTTP的访问方式 # req = urllib.request(req) # return req.read(), req.getcode() # # # # def http_put_inc(v_url,v_cube, v_startTime, v_endTime,v_refreshtype): # #url = 'http://192.168.176.67:7070' # url=v_url+'/kylin/api/cubes/'+v_cube+'/rebuild' # values = {"startTime": v_startTime, "endTime": v_endTime, "buildType": v_refreshtype} # jdata = json.dumps(values) # 对数据进行JSON格式化编码 # header={'Authorization': 'Basic YWRtaW46S1lMSU4=' ,'Content-Type':'application/json'} # req = requests.put(url,data=jdata,headers=header ) # return req.text, req.status_code # # def http_put_full(v_url,v_cube): # #url = 'http://192.168.176.67:7070' # url=v_url+'/kylin/api/cubes/'+v_cube+'/rebuild' # values = {"buildType": "BUILD"} # jdata = json.dumps(values) # 对数据进行JSON格式化编码 # # header={'Authorization': 'Basic YWRtaW46S1lMSU4=' ,'Content-Type':'application/json'} # req = requests.put(url,data=jdata,headers=header ) # return req.text, req.status_code # # def http_get_cube(v_url,v_cube): # if v_cube=='all': # url = v_url + '/kylin/api/cubes' # else: # #url = 'http://192.168.176.67:7070' # url = v_url +'/kylin/api/cubes/'+v_cube # header={'Authorization': 'Basic YWRtaW46S1lMSU4=' ,'Content-Type':'application/json'} # req = requests.get(url, headers=header ) # return req.text, req.status_code # # # # def http_get(v_url,v_job_id): # #url = 'http://192.168.176.67:7070' # url=v_url+'/kylin/api/jobs/'+v_job_id # header={'Authorization': 'Basic YWRtaW46S1lMSU4=' ,'Content-Type':'application/json'} # req = requests.get(url, headers=header ) # return req.text, req.status_code req, ret_code = kylin_api.http_get_all_job(v_url, v_project, 16) load_dict = json.loads(req) for cube in tqdm(load_dict): # if cube['related_cube']=='KYLIN_HIVE_METRICS_QUERY_PROD': if 'PROD' in cube['related_cube']: print(cube['related_cube']) print(cube['uuid']) #req, ret_code= kylin_api.http_oper_job(v_url,cube['uuid'],'resume') req, ret_code = kylin_api.http_oper_job(v_url, cube['uuid'], 'drop') print(req) print(ret_code) # PUT /kylin/api/jobs/{jobId}/cancel # with open('/Users/wonder/Documents/data_dict/kylin/script/data/output/test_job.txt', 'w') as f: # f.write(req) all_model_list = [] req, ret_code = kylin_api.http_get_model(v_url, v_project) load_dict = json.loads(req) for model in tqdm(load_dict): #print(model['name']) tmObject = time.localtime((model['last_modified'] / 1000)) tm_lastbuild_str = time.strftime("%Y-%m-%d %H:%M:%S", tmObject) #print(tm_lastbuild_str) model_str = (model['name'] + ',' + tm_lastbuild_str + ',' + v_project) model_list = [i for i in model_str.split(',')] all_model_list.append(model_list) model_df = pd.DataFrame( all_model_list, columns=['model', 'model_last_build_time', 'project']) # print(model_df) # # exit() req, ret_code = kylin_api.http_get_cube(v_url, cubename) print('~' * 100 + '\n') #print(req) load_dict = json.loads(req) cube_dict = {} segment_dict = {} all_segment_list = [] all_cube_list = [] count = 0 print(type(req)) for cube in tqdm(load_dict): #print('cube:'+str(cube)) #print(cube['name']) if cube['project'] == 'iqj_olap': #if cube['status'] !='DISABLED' and cube['project'] == 'iqj_olap' : # project_list =['iqj_olap' ,'qianzhan_olap'] # if cube['project'] in project_list : # print(cube['project']) count += 1 cube_list = [] # print(cube['project']) # print(cube['model']) # print(cube['name']) # print(cube['status']) # print(cube['last_modified']) # print(cube['segments']) # cube_dict['project'] = cube['project'] # cube_dict['model'] = cube['model'] # cube_dict['cube_name'] = cube['name'] # cube_dict['status'] = cube['status'] # cube_dict['segments'] = cube['segments'] print(cube) cube_list.append(cube['model']) cube_list.append(cube['name']) cube_list.append(cube['status']) cube_list.append(cube['size_kb']) tmObject = time.localtime((cube['last_modified'] / 1000)) tm_lastbuild_str = time.strftime("%Y-%m-%d %H:%M:%S", tmObject) tmObject2 = time.localtime((cube['create_time_utc'] / 1000)) tm_create_str = time.strftime("%Y-%m-%d %H:%M:%S", tmObject2) days_diff_cnt = days_diff(tm_now_str, tm_lastbuild_str) cube_list.append(tmObject) cube_list.append(tmObject2) cube_list.append(days_diff_cnt) all_cube_list.append(cube_list) req, ret_code = kylin_api.http_get_cube(v_url, cube['name']) load_dict = json.loads(req) print('循环次数:' + str(count)) for segment in load_dict['segments']: segment_list = [] #if segment['project'] == 'iqj_olap': #print(segment['project']) # print(segment['name']) # print(segment['storage_location_identifier'].split(':')[0]) # print(segment['storage_location_identifier'].split(':')[1]) # print(segment['size_kb']) # print(segment['input_records']) # print(segment['input_records_size']) # print(segment['last_build_time']) segment_list.append(cube['name']) segment_list.append(segment['name']) segment_list.append( segment['storage_location_identifier'].split(':')[0]) segment_list.append( segment['storage_location_identifier'].split(':')[1]) segment_list.append(segment['size_kb']) segment_list.append(segment['input_records']) segment_list.append(segment['input_records_size']) segment_list.append(segment['last_build_time']) all_segment_list.append(segment_list) #print(segment_dict) #cube_df=pd.DataFrame.from_dict(segment_dict, orient='index').T # print(segment_dict.info()) # print('-' * 2) # print('~~' * 20) # DB_CONNECT = 'mysql+pymysql://data_window:zPcQl%[email protected]:3306/data_window?charset=utf8' # conn = create_engine(DB_CONNECT, echo=True, pool_size=10, max_overflow=20) # # pd.io.sql.to_sql(df,'test_python',con=conn) # cube_df.to_sql(name='cube_test', con=conn, if_exists='replace', index=False) print('segment_list:' + str(segment_list)) segment_df = pd.DataFrame(all_segment_list, columns=[ 'cube_name', 'segmentsegment_name', 'hbase_namespace', 'habse_table', 'size_kb', 'input_records', 'input_records_size', 'last_build_time' ]) print(segment_df) DB_CONNECT = 'mysql+pymysql://root:20131130@localhost:3306/finup_db4test?charset=utf8' #DB_CONNECT = 'mysql+pymysql://data_window:zPcQl%[email protected]:3306/data_window?charset=utf8' conn = create_engine(DB_CONNECT, echo=True, pool_size=10, max_overflow=20) segment_df.to_sql(name=v_hbase_cube_mapping, con=conn, if_exists='replace', index=False) cube_df = pd.DataFrame(all_cube_list, columns=[ 'model', 'cube_name', 'status', 'size_kb', 'lastmodifytime', 'createtime', 'days_diff_cnt' ]) ## model_df left join cube_df model_cube_df = pd.merge(model_df, cube_df, on='model', how='outer') print(model_cube_df) model_cube_df.to_sql(name=v_model_cube_mapping, con=conn, if_exists='replace', index=False) print('结束')