def run_hive(conf: ConfigData, the_date: str): client = Client(conf.hdfs_ip()) # "http://10.2.201.197:50070" conn = connect(host=conf.hive_ip(), port=conf.hive_port(), auth_mechanism=conf.hive_auth(), user=conf.hive_user()) cur = conn.cursor() the_date = StrTool.get_the_date_str(the_date) # "20181101" root_path = conf.get_data("hdfs_dir_zc") # "/data/posflow/allinpay_utf8_zc/" file_ext3 = conf.get_data("file_ext3") # _loginfo_rsp.txt # 20181101_loginfo_rsp.txt file_ext4 = conf.get_data("file_ext4") # _loginfo_rsp_agt.txt # 20181101_loginfo_rsp_agt.txt file_ext5 = conf.get_data("file_ext5") # _rxinfo_rsp.txt # 20181101_rxinfo_rsp.txt file_ext6 = conf.get_data("file_ext6") # _rxinfo_rsp_agt.txt # 20181101_rxinfo_rsp_agt.txt print("Start\n") file3 = str(pathlib.PurePosixPath(root_path).joinpath(the_date + file_ext3)) file4 = str(pathlib.PurePosixPath(root_path).joinpath(the_date + file_ext4)) file5 = str(pathlib.PurePosixPath(root_path).joinpath(the_date + file_ext5)) file6 = str(pathlib.PurePosixPath(root_path).joinpath(the_date + file_ext6)) f_list = [file3,file4,file5,file6] t_list = ["hive_table3", "hive_table4", "hive_table5", "hive_table6"] for n in range(0,4): if MyHdfsFile.isfile(client, f_list[n]): sql = 'LOAD DATA INPATH \'' + f_list[n] + '\' INTO TABLE ' + conf.get_data(t_list[n]) # 'test.t1_trxrecprd_v2_zc' # '\' OVERWRITE INTO TABLE test.t1_trxrecprd_v2_bl2' print("OK" + " " + sql+"\n") cur.execute(sql) # , async=True) cur.close() conn.close()
def run_hive(conf: ConfigData, the_date: str): a_client = Client(conf.hdfs_ip()) # "http://10.2.201.197:50070" conn = connect(host=conf.hive_ip(), port=conf.hive_port(), auth_mechanism=conf.hive_auth(), user=conf.hive_user()) cur = conn.cursor() print("Start\n") the_date = StrTool.get_the_date_str(the_date) # "20181101" # hdfs_dir_bl root_path = str( pathlib.PurePosixPath(conf.get_hdfs_path()).joinpath(the_date)) file_name = str( pathlib.PurePosixPath(root_path).joinpath( conf.get_file_name(the_date))) # "/data/posflow/allinpay_utf8_zc/20181101/" # 20181101_loginfo_rsp_bl_new.csv # 20181101_rsp_agt_bl_new.del # 20181101_rxinfo_rsp_bl.txt table_name = conf.get_table_name() if MyHdfsFile.isfile(a_client, file_name): sql = 'LOAD DATA INPATH \'' + file_name + '\' INTO TABLE ' + table_name # 'test.t1_trxrecprd_v2_zc' # '\' OVERWRITE INTO TABLE test.t1_trxrecprd_v2_bl2' print("OK" + " " + sql + "\n") cur.execute(sql) # , async=True) cur.close() conn.close()
def run_unzip_file(conf: ConfigData, the_date: str): the_date = StrTool.get_the_date_str(the_date) if (type(the_date) is str) and len(the_date) == 8: m_month = the_date[0:6] m_day = the_date[6:8] else: return root_path = conf.get_data("allinpay_data_zc") destdir = conf.get_data("allinpay_data_zc") # ifile = '/home/testFolder/logflow/bl_shouyinbao/20181101/9999100000/t1_trxrecord_20181101_V2.csv' # ofile = '/home/testFolder/logflow/bl_shouyinbao/UTF8/20181101/9999100000/t1_trxrecord_20181101_V2.csv' print("Start\n") # os.path.join(root_path, the_date) # real SYB folder don't have date folder f_name = conf.get_zip_name(the_date, 3) # the_date+".zip" a_file = os.path.join(root_path, f_name) if MyLocalFile.check_file(a_file): MyLocalFile.unzip_the_file(a_file, destdir, p_name=the_date+"*") f_name = conf.get_zip_name(the_date,5) # the_date+"_agt.zip" a_file = os.path.join(root_path, f_name) if MyLocalFile.check_file(a_file): MyLocalFile.unzip_the_file(a_file, destdir, p_name=the_date+"*")
def run_unzip_file(conf: ConfigData, p_date: str): p_date = StrTool.get_the_date_str(p_date) if (type(p_date) is str) and len(p_date) == 8: m_month = p_date[0:6] m_day = p_date[6:8] else: return p_zip_path = os.path.join(conf.get_zip_path(), p_date) # root_path = conf.get_data("allinpay_data_zc") data_path = os.path.join(conf.get_data_path(), p_date) # allinpay_data_zc # ifile = '/home/testFolder/logflow/bl_shouyinbao/20181101/9999100000/t1_trxrecord_20181101_V2.csv' # ofile = '/home/testFolder/logflow/bl_shouyinbao/UTF8/20181101/9999100000/t1_trxrecord_20181101_V2.csv' print("Start\n") # os.path.join(root_path, the_date) # real SYB folder don't have date folder p_f_name = conf.get_zip_name( p_date) # 3= the_date+".zip" # 5 = the_date+"_agt.zip" p_a_file = os.path.join(p_zip_path, p_f_name) p_p_name = conf.get_file_name(p_date) # p_date+"*" if MyLocalFile.check_file(p_a_file): MyLocalFile.unzip_the_file(p_a_file, data_path, p_name=p_p_name)
def run_remove_hive(conf: ConfigData, the_date: str, delta_day=0): f_date_str = StrTool.get_the_date_str(the_date, delta_day) # "20181101" # "/user/hive/warehouse/rds_posflow.db/t1_trxrecprd_v2/t1_trxrecord_20181204_V2*.csv" del_table = conf.get_table_name( ) # hive_table="rds_posflow.t1_trxrecprd_v2" if the_conf.m_project_id == 1: del_file = conf.get_file_name(f_date_str).replace('.', '*.') MyHdfsFile.delete_hive_ssh(conf.get_data("cdh_ip"), table=del_table, p_name=del_file, username=conf.get_data("cdh_user"), password=conf.get_data("cdh_pass")) if the_conf.m_project_id == 2: conn = connect(host=conf.hive_ip(), port=conf.hive_port(), auth_mechanism=conf.hive_auth(), user=conf.hive_user()) cur = conn.cursor() # "ALTER TABLE rds_posflow.t1_trxrecprd_v2_tmp DROP IF EXISTS PARTITION(p_date=20190208) " sql = "ALTER TABLE {} DROP IF EXISTS PARTITION( p_date={} )".format( del_table, the_date) print(sql) cur.execute(sql) cur.close() conn.close()
def run_remove_files(conf: ConfigData, the_date: str, delta_day=0): sdate = StrTool.get_the_date_str(the_date, delta_day) # "20181101" data_path = os.path.join(conf.get_data_path(1), sdate) utf8_path = os.path.join(conf.get_utf8_path(1), sdate) hdfs_path = str(pathlib.PurePosixPath(conf.get_hdfs_path()).joinpath(sdate)) shutil.rmtree(data_path, ignore_errors=True) shutil.rmtree(utf8_path, ignore_errors=True) client = MyClient(conf.hdfs_ip()) # "http://10.2.201.197:50070" client.delete(hdfs_path, recursive=True)
def run_remove_hive(conf: ConfigData, the_date: str, delta_day=0): f_date_str = StrTool.get_the_date_str(the_date, delta_day) # "20181101" del_table = conf.get_table_name( ) # "hive_table" + str(conf.the_id) # "rds_posflow.loginfo_rsp_bl" del_file = conf.get_file_name(f_date_str).replace( '.', '*.') # "file_ext" + str(conf.the_id) MyHdfsFile.delete_hive_ssh(conf.get_data("cdh_ip"), table=del_table, p_name=del_file, username=conf.get_data("cdh_user"), password=conf.get_data("cdh_pass"))
def run_sftp_file(conf: ConfigData, the_date: str): the_date = StrTool.get_the_date_str(the_date) if (type(the_date) is str) and len(the_date) == 8: m_month = the_date[0:6] m_day = the_date[6:8] else: return a = sftp_tool.Sftp_Tool(h=conf.get_data("allinpay_ftp_ip_zc"), p=int(conf.get_data("allinpay_ftp_port_zc")), u=conf.get_data("allinpay_ftp_user_zc"), s=conf.get_data("allinpay_ftp_pass_zc"), r=conf.get_data("allinpay_ftp_folder_zc"), d=conf.get_data("allinpay_data_zc")) a.openSFTP() a.download_files(from_dir=conf.get_data("allinpay_ftp_folder_zc"), to_dir=conf.get_data("allinpay_data_zc"), p_name=the_date+"*.zip")
def run_remove_files(conf: ConfigData, the_date: str, delta_day=0): f_date_str = StrTool.get_the_date_str(the_date, delta_day) # "20181101" data_path = os.path.join(conf.get_data_path(), f_date_str) # allinpay_data_bl utf8_path = os.path.join(conf.get_utf8_path(), f_date_str) # allinpay_utf8_bl hdfs_path = str( pathlib.PurePosixPath( conf.get_hdfs_path()).joinpath(f_date_str)) # hdfs_dir_bl a_client = MyClient(conf.hdfs_ip()) # "http://10.2.201.197:50070" shutil.rmtree(data_path, ignore_errors=True) shutil.rmtree(utf8_path, ignore_errors=True) a_client.delete(hdfs_path, recursive=True)
def run_conv_file_local_to_hdfs(conf: ConfigData, the_date: str): """ :param conf: :param the_date: :return: """ the_date = StrTool.get_the_date_str(the_date) a_client = MyClient(conf.hdfs_ip()) # "http://10.2.201.197:50070" # allinpay_data_bl data_path = os.path.join(conf.get_data_path(), the_date) # allinpay_utf8_bl dest_dir1 = os.path.join(conf.get_utf8_path(), the_date) # hdfs_dir_bl dest_dir2 = str( pathlib.PurePosixPath(conf.get_hdfs_path()).joinpath(the_date)) # file_ext7 = conf.get_data("file_ext7") # _loginfo_rsp_bl_new.csv # 20181101_loginfo_rsp_bl_new.csv # file_ext8 = conf.get_data("file_ext8") # _rsp_agt_bl_new.del # 20181101_rsp_agt_bl_new.del # file_ext9 = conf.get_data("file_ext9") # _rxinfo_rsp_bl.txt # 20181101_rxinfo_rsp_bl.txt # f_list = [file_ext7, file_ext8, file_ext9] print("Start\n") # "file_ext" + str(conf.the_id) file_name = conf.get_file_name(the_date).lower() files = MyLocalFile.get_child_file(data_path) for aFile in files: short_name = os.path.basename(aFile).lower() f_name = pathlib.PurePath(aFile).name if short_name == file_name: to_file1 = str(pathlib.PurePath(dest_dir1).joinpath(f_name)) to_file2 = str(pathlib.PurePosixPath(dest_dir2).joinpath(f_name)) f_add_date = conf.get_hive_add_date(the_date) f_need_head = conf.get_hive_head() MyLocalFile.conv_file_local(aFile, to_file1, need_first_line=f_need_head, p_add_head=f_add_date) MyHdfsFile.safe_make_dir(a_client, to_file2) # a_client.newupload(to_file2, to_file1, encoding='utf-8') the_file = a_client.status(to_file2, strict=False) if the_file is None: a_client.upload(to_file2, to_file1) a_client.set_permission(to_file2, 777) # a_client.set_owner(thePath,owner='hdfs',group='supergroup') elif the_file['type'].lower() == 'file': # 'directory' a_client.set_permission(to_file2, 777)
def run_unzip_file(conf: ConfigData, the_date, folder_type=2): the_date = StrTool.get_the_date_str(the_date) if (type(the_date) is str) and len(the_date) == 8: m_month = the_date[0:6] m_day = the_date[6:8] else: return zip_path = conf.get_zip_path() data_path = conf.get_data_path() f_name = conf.get_zip_name("") # "t1_trxrecord_" the_date # "_V2.csv" print("Start\n") # os.path.join(root_path, the_date) # real SYB folder don't have date folder branches = MyLocalFile.get_child_dir(zip_path) for aBranch in branches: if MyLocalFile.check_branch(aBranch): months = MyLocalFile.get_child_dir(aBranch) for aMonth in months: the_month = MyLocalFile.check_month(aMonth) if the_month > 0 and "{:0>6d}".format(the_month) == m_month: day_list = MyLocalFile.get_child_dir(aMonth) for aDay in day_list: the_day = MyLocalFile.check_day(aDay) if the_day > 0 and "{:0>2d}".format(the_day) == m_day: files = MyLocalFile.get_child_file(aDay) for aFile in files: if MyLocalFile.check_file(aFile, p_name=f_name): short_name = os.path.basename(aBranch) if folder_type == 1: new_path = os.path.join( data_path, m_month, m_day, short_name) # "{:0>6d}".format(month) "{:0>2d}".format(day) else: new_path = os.path.join( data_path, m_month + m_day, short_name) # "{:0>6d}{:0>2d}".format(month, day) p_name = conf.get_file_name(m_month + m_day) MyLocalFile.unzip_the_file( aFile, new_path, p_name)
def run_sftp_file(conf: ConfigData, the_date: str): the_date = StrTool.get_the_date_str(the_date) # allinpay_ftp_folder_bl_1 or allinpay_ftp_folder_bl_2 f_dir = conf.get_remote_path_ftp(the_date) # allinpay_data_bl t_dir = os.path.join(conf.get_local_path_ftp(), the_date) # "file_ext" + str(conf.the_id) file_name = conf.get_ftp_name(the_date) a = sftp_tool.Sftp_Tool(h=conf.get_ftp_ip(), p=int(conf.get_ftp_port()), u=conf.get_ftp_user(), s=conf.get_ftp_pass(), r=f_dir, d=t_dir) a.openSFTP() a.download_files(from_dir=f_dir, to_dir=t_dir, p_name=file_name)
def run_hive(conf: ConfigData, the_date: str, is_baoli=True): p_client = Client(conf.hdfs_ip()) # "http://10.2.201.197:50070" conn = connect(host=conf.hive_ip(), port=conf.hive_port(), auth_mechanism=conf.hive_auth(), user=conf.hive_user()) cur = conn.cursor() the_date = StrTool.get_the_date_str(the_date) # "20181101" root_path = conf.get_hdfs_path() # "/shouyinbao/bl_shouyinbao/UTF8/" f_name = conf.get_file_name( the_date) # "t1_trxrecord_" the_date # "_V2.csv" table_name = conf.get_table_name() print("Start\n") idn = 0 branches = MyHdfsFile.get_child(p_client, root_path + the_date) for aBranch in branches: if MyHdfsFile.check_branch(p_client, aBranch): files = MyHdfsFile.get_child(p_client, aBranch) f_a_branch = MyHdfsFile.get_name(aBranch) for aFile in files: if MyHdfsFile.check_file(p_client, aFile, f_name): # '/shouyinbao/bl_shouyinbao/UTF8/20181101/9999997900/t1_trxrecord_20181101_V2.csv' to_file2 = str( pathlib.PurePosixPath(root_path).joinpath( the_date, f_a_branch, f_name)) if conf.m_project_id == 1: sql = 'LOAD DATA INPATH \'{}\' INTO TABLE {}'.format( to_file2, table_name) # 'test.t1_trxrecprd_v2_zc' # '\' OVERWRITE INTO TABLE test.t1_trxrecprd_v2_bl2' elif conf.m_project_id == 2: sql = 'LOAD DATA INPATH \'{}\' INTO TABLE {} PARTITION ( p_branch=\'{}\', p_date={} )'.format( to_file2, table_name, f_a_branch, the_date) # 'test.t1_trxrecprd_v2_zc' idn += 1 print(str(idn) + " " + sql + "\n") cur.execute(sql) # , async=True) cur.close() conn.close()
def run_conv_file_local(conf: ConfigData, the_date: str, is_baoli=True): the_date = StrTool.get_the_date_str(the_date) root_path = conf.get_data_path() dest_dir = conf.get_utf8_path() f_name = conf.get_file_name( the_date) # "t1_trxrecord_" the_date # "_V2.csv" print("Start\n") branches = MyLocalFile.get_child(os.path.join(root_path, the_date)) for aBranch in branches: if MyLocalFile.check_branch(aBranch): files = MyLocalFile.get_child(aBranch) for aFile in files: if MyLocalFile.check_file(aFile, f_name): MyLocalFile.conv_file_local( aFile, os.path.join(dest_dir, the_date, os.path.basename(aBranch), f_name), True)
def run_conv_file_hdfs(conf: ConfigData, the_date: str, is_baoli=True): the_date = StrTool.get_the_date_str(the_date) client = Client(conf.hdfs_ip()) # "http://10.2.201.197:50070" root_path = conf.get_data_path() # 'D:/DATA/UNZIP/' dest_dir = conf.get_hdfs_path() f_name = conf.get_file_name( the_date) # "t1_trxrecord_" the_date # "_V2.csv" print("Start\n") branches = MyLocalFile.get_child(os.path.join(root_path, the_date)) for aBranch in branches: if MyLocalFile.check_branch(aBranch): files = MyLocalFile.get_child(aBranch) for aFile in files: if MyLocalFile.check_file(aFile, f_name): MyHdfsFile.conv_file_hdfs( aFile, os.path.join(dest_dir, the_date, os.path.basename(aBranch), f_name), client)
def run_conv_file_local_to_hdfs(conf: ConfigData, the_date: str): """ :param conf: :param the_date: :return: """ the_date = StrTool.get_the_date_str(the_date) client = MyClient(conf.hdfs_ip()) # "http://10.2.201.197:50070" root_path = conf.get_data("allinpay_data_zc") dest_dir1 = conf.get_data("allinpay_utf8_zc") dest_dir2 = conf.get_data("hdfs_dir_zc") file_ext3 = conf.get_data("file_ext3") # _loginfo_rsp.txt # 20181101_loginfo_rsp.txt file_ext4 = conf.get_data("file_ext4") # _loginfo_rsp_agt.txt # 20181101_loginfo_rsp_agt.txt file_ext5 = conf.get_data("file_ext5") # _rxinfo_rsp.txt # 20181101_rxinfo_rsp.txt file_ext6 = conf.get_data("file_ext6") # _rxinfo_rsp_agt.txt # 20181101_rxinfo_rsp_agt.txt print("Start\n") files = MyLocalFile.get_child_file(root_path) for aFile in files: short_name = os.path.basename(aFile).lower() if short_name == (the_date + file_ext3).lower() or \ short_name == (the_date + file_ext4).lower() or \ short_name == (the_date + file_ext5).lower() or \ short_name == (the_date + file_ext6).lower(): to_file1 = str(pathlib.PurePath(dest_dir1).joinpath(pathlib.PurePath(aFile).name)) to_file2 = str(pathlib.PurePosixPath(dest_dir2).joinpath(pathlib.PurePath(aFile).name)) MyLocalFile.conv_file_local(aFile, to_file1, need_first_line=True) MyHdfsFile.safe_make_dir(client, to_file2) # client.newupload(to_file2, to_file1, encoding='utf-8') the_file = client.status(to_file2, strict=False) if the_file is None: client.upload(to_file2, to_file1) client.set_permission(to_file2, 777) # client.set_owner(thePath,owner='hdfs',group='supergroup') elif the_file['type'].lower() == 'file': # 'directory' client.set_permission(to_file2, 777)
if MyLocalFile.check_file(a_file): MyLocalFile.unzip_the_file(a_file, data_path, p_name=p_name) if __name__ == "__main__": m_is_test = False # client = Client(the_conf.hdfs_ip()) # "http://10.2.201.197:50070" if m_is_test: m_project_id = 3 start_date_str = "20180901" m_days = 9 m_project_id = StrTool.get_param_int(1, 3) start_date_str = StrTool.get_the_date_str(StrTool.get_param_str(2, "")) m_days = StrTool.get_param_int(3, 1) else: m_project_id = StrTool.get_param_int(1, 3) start_date_str = StrTool.get_the_date_str(StrTool.get_param_str(2, "")) m_days = StrTool.get_param_int(3, 1) start_date = StrTool.get_the_date(start_date_str) the_conf = ConfigData(m_project_id, StrTool.get_the_date_str_by_date(start_date, 0, 10), p_is_test=m_is_test) for i in range(0, m_days): delta = m_days - i - 1 # 不多加1天,20190108处理的是20190108文件夹 # delta = days - i - 1 + 1 # 多加1天,是因为20190108处理的是20190107文件夹 # 收银宝文件没有多 delta 1天 # 1、20190110 191 2019-1-10 2018-7-4
def run_hive(conf: ConfigData, the_date: str): conn = connect(host="10.91.1.20", port=conf.hive_port(), auth_mechanism=conf.hive_auth(), user=conf.hive_user()) cur = conn.cursor() sql = """ --deduct union sql created by Zhaohu on 14-Nov-2018 am 10:49 use loginfo """ print("OK" + " " + sql + "\n") cur.execute(sql) # , async=True) sql = "set mapreduce.job.queuename = deduct_union_gansu_mysql;" print("OK" + " " + sql + "\n") cur.execute(sql) # , async=True) sql = "set hive.cli.print.header=true;" print("OK" + " " + sql + "\n") cur.execute(sql) # , async=True) sql = """ select distinct case when creq.prod_code='8005000001' then '甘肃银行' else '' end as `产品类别`, creq.LMT_SERNO as `授信编号`, creq.CUST_NAME as `客户姓名`, creq.LIVE_ADDR as `居住地`, creq.ADDRESS as `客户联系地址`, creq.TEL_NO as `家庭电话(贷款申请表)`, creq.MOBILE as `手机号码(贷款申请表)`, creq.MOBILE as `客户手机`, creq.SPOUSE_NM as `配偶姓名`, creq.SPOUSE_PHONE as `配偶电话`, creq.EMER_NAME_1 as `第一联系人姓名`, case when creq.EMER_REL_1='1' or creq.EMER_REL_1='01' then '配偶' when creq.EMER_REL_1='2' or creq.EMER_REL_1='02' then '父母' when creq.EMER_REL_1='3' or creq.EMER_REL_1='03' then '子女' when creq.EMER_REL_1='4' or creq.EMER_REL_1='04' then '亲戚' when creq.EMER_REL_1='5' or creq.EMER_REL_1='05' then '朋友' when creq.EMER_REL_1='6' or creq.EMER_REL_1='06' then '其他' when creq.EMER_REL_1='7' or creq.EMER_REL_1='07' then '兄弟姐妹' when creq.EMER_REL_1='8' or creq.EMER_REL_1='08' then '同事' else '' end as `第一联系人关系`, creq.EMER_PHONE_1 as `第一联系人联系电话`, creq.EMER_NAME_2 as `第二联系人姓名`, case when creq.EMER_REL_2='1' or creq.EMER_REL_2='01' then '配偶' when creq.EMER_REL_2='2' or creq.EMER_REL_2='02' then '父母' when creq.EMER_REL_2='3' or creq.EMER_REL_2='03' then '子女' when creq.EMER_REL_2='4' or creq.EMER_REL_2='04' then '亲戚' when creq.EMER_REL_2='5' or creq.EMER_REL_2='05' then '朋友' when creq.EMER_REL_2='6' or creq.EMER_REL_2='06' then '其他' when creq.EMER_REL_2='7' or creq.EMER_REL_2='07' then '兄弟姐妹' when creq.EMER_REL_2='8' or creq.EMER_REL_2='08' then '同事' else '' end as `第二联系人关系`, creq.EMER_PHONE_2 as `第二联系人联系电话`, creq.EMER_NAME_3 as `第三联系人姓名`, case when creq.EMER_REL_3='1' or creq.EMER_REL_3='01' then '配偶' when creq.EMER_REL_3='2' or creq.EMER_REL_3='02' then '父母' when creq.EMER_REL_3='3' or creq.EMER_REL_3='03' then '子女' when creq.EMER_REL_3='4' or creq.EMER_REL_3='04' then '亲戚' when creq.EMER_REL_3='5' or creq.EMER_REL_3='05' then '朋友' when creq.EMER_REL_3='6' or creq.EMER_REL_3='06' then '其他' when creq.EMER_REL_3='7' or creq.EMER_REL_3='07' then '兄弟姐妹' when creq.EMER_REL_3='8' or creq.EMER_REL_3='08' then '同事' else '' end as `第三联系人关系`, creq.EMER_PHONE_3 as `第三联系人联系电话`, creq.CREDIT_CONTRACT as `合同编号`, trim(creq.CERT_NO) as `身份证号`, case when creq.sex='2' then '男' when creq.sex='3' then '女' else '' end as `性别`, loaninfo.limit_statr_date as `贷款开始日期`, ureq.APP_START_DATE as `支用开始SAS日期`, ureq.APP_END_DATE as `支用结束SAS日期`, ureq.USE_DATE as `支用申请期限`, merchant.contact_tel as `联系电话(APMS系统)`, merchant.legal_name as `法定代表人姓名`, merchant.finance_name as `财务联系人`, merchant.finance_hp_no as `财务联系人电话(APMS系统)`, merchant.stlm_acct as `还款银行卡号`, merchant.name_busi as `商户名称`, dict4.dict_name as `所在城市`, merchant.busi_addr as `营业地址`, dict1.dict_name as `分公司`, merchant.stlm_ins_city as `地市业务部`, merchant.contact as `客户姓名`, trim(creq.merchant_no) as `商户编号`, dict2.dict_name as `inst_oid中文`, dict3.dict_name as `mcc中文`, trim(deductall.bill_no) as `借据编号`, deductall.CURR_DATE as `截止日期`, loaninfo.LOAN_AMT as `放款金额`, loaninfo.LIMIT_END_DATE as `货款到期日`, loaninfo.REMAIN_LOAN_AMT as `贷款剩余本金`, loaninfo.REMAIN_LOAN_AMT as `应收未收本金`, loaninfo.OVERDUE_INTE_AMT as `应收未收利息`, case when compensate.BILL_NO is not null then '是' else '否' end as `代偿状态`, deductall.begin_late_date as `逾期开始日期`, date_add(deductall.begin_late_date,1) as `新增日期`, datediff(from_unixtime(unix_timestamp(deductall.CURR_DATE,'yyyymmdd'),'yyyy-mm-dd') ,deductall.begin_late_date) as `逾期天数` from loginfo.union_flag1 deductall left join loginfo.t_loan_use_req ureq on ureq.bill_no=trim(deductall.bill_no) left join loginfo.t_loan_credit_req creq on creq.LMT_SERNO=ureq.LMT_SERNO left join loginfo.merchant_zc merchant on creq.merchant_no=merchant.mcht_cd left JOIN loginfo.T_REOCN_LOANINFO loaninfo on trim(deductall.bill_no)=loaninfo.bill_no and loaninfo.current_data=trim(deductall.curr_date) left join loginfo.t_loan_compernsatory compensate on ureq.BILL_NO=compensate.BILL_NO left join loginfo.t_allinpay_dict dict1 on merchant.aip_bran_id = dict1.dict_id and dict1.dict_type='aip_bran_id' left join loginfo.t_allinpay_dict dict2 on merchant.inst_oid = dict2.dict_id and dict2.dict_type='inst_oid' left join loginfo.t_allinpay_dict dict3 on merchant.up_mcc_cd = dict3.dict_id and dict3.dict_type='up_mcc_cd' left join loginfo.t_allinpay_dict dict4 on merchant.city_cd = dict4.dict_id and dict4.dict_type='city_cd' where creq.prod_code='8005000001' """ sql2 = """ select distinct case when creq.prod_code='8005000001' then '甘肃银行' else '' end as `产品类别`, creq.LMT_SERNO as `授信编号`, creq.CUST_NAME as `客户姓名`, creq.LIVE_ADDR as `居住地`, creq.ADDRESS as `客户联系地址`, creq.TEL_NO as `家庭电话(贷款申请表)`, creq.MOBILE as `手机号码(贷款申请表)`, creq.MOBILE as `客户手机`, creq.SPOUSE_NM as `配偶姓名`, creq.SPOUSE_PHONE as `配偶电话`, creq.EMER_NAME_1 as `第一联系人姓名`, case when creq.EMER_REL_1='1' or creq.EMER_REL_1='01' then '配偶' when creq.EMER_REL_1='2' or creq.EMER_REL_1='02' then '父母' when creq.EMER_REL_1='3' or creq.EMER_REL_1='03' then '子女' when creq.EMER_REL_1='4' or creq.EMER_REL_1='04' then '亲戚' when creq.EMER_REL_1='5' or creq.EMER_REL_1='05' then '朋友' when creq.EMER_REL_1='6' or creq.EMER_REL_1='06' then '其他' when creq.EMER_REL_1='7' or creq.EMER_REL_1='07' then '兄弟姐妹' when creq.EMER_REL_1='8' or creq.EMER_REL_1='08' then '同事' else '' end as `第一联系人关系`, creq.EMER_PHONE_1 as `第一联系人联系电话`, creq.EMER_NAME_2 as `第二联系人姓名`, case when creq.EMER_REL_2='1' or creq.EMER_REL_2='01' then '配偶' when creq.EMER_REL_2='2' or creq.EMER_REL_2='02' then '父母' when creq.EMER_REL_2='3' or creq.EMER_REL_2='03' then '子女' when creq.EMER_REL_2='4' or creq.EMER_REL_2='04' then '亲戚' when creq.EMER_REL_2='5' or creq.EMER_REL_2='05' then '朋友' when creq.EMER_REL_2='6' or creq.EMER_REL_2='06' then '其他' when creq.EMER_REL_2='7' or creq.EMER_REL_2='07' then '兄弟姐妹' when creq.EMER_REL_2='8' or creq.EMER_REL_2='08' then '同事' else '' end as `第二联系人关系`, creq.EMER_PHONE_2 as `第二联系人联系电话`, creq.EMER_NAME_3 as `第三联系人姓名`, case when creq.EMER_REL_3='1' or creq.EMER_REL_3='01' then '配偶' when creq.EMER_REL_3='2' or creq.EMER_REL_3='02' then '父母' when creq.EMER_REL_3='3' or creq.EMER_REL_3='03' then '子女' when creq.EMER_REL_3='4' or creq.EMER_REL_3='04' then '亲戚' when creq.EMER_REL_3='5' or creq.EMER_REL_3='05' then '朋友' when creq.EMER_REL_3='6' or creq.EMER_REL_3='06' then '其他' when creq.EMER_REL_3='7' or creq.EMER_REL_3='07' then '兄弟姐妹' when creq.EMER_REL_3='8' or creq.EMER_REL_3='08' then '同事' else '' end as `第三联系人关系`, creq.EMER_PHONE_3 as `第三联系人联系电话`, creq.CREDIT_CONTRACT as `合同编号`, trim(creq.CERT_NO) as `身份证号`, case when creq.sex='2' then '男' when creq.sex='3' then '女' else '' end as `性别`, loaninfo.limit_statr_date as `贷款开始日期`, ureq.APP_START_DATE as `支用开始SAS日期`, ureq.APP_END_DATE as `支用结束SAS日期`, ureq.USE_DATE as `支用申请期限`, merchant.contact_tel as `联系电话(APMS系统)`, merchant.legal_name as `法定代表人姓名`, merchant.finance_name as `财务联系人`, merchant.finance_hp_no as `财务联系人电话(APMS系统)`, merchant.stlm_acct as `还款银行卡号`, merchant.name_busi as `商户名称`, dict4.dict_name as `所在城市`, merchant.busi_addr as `营业地址`, dict1.dict_name as `分公司`, merchant.stlm_ins_city as `地市业务部`, merchant.contact as `客户姓名`, trim(creq.merchant_no) as `商户编号`, dict2.dict_name as `inst_oid中文`, dict3.dict_name as `mcc中文`, trim(deductall.bill_no) as `借据编号`, deductall.CURR_DATE as `截止日期`, loaninfo.LOAN_AMT as `放款金额`, loaninfo.LIMIT_END_DATE as `货款到期日`, loaninfo.REMAIN_LOAN_AMT as `贷款剩余本金`, loaninfo.REMAIN_LOAN_AMT as `应收未收本金`, loaninfo.OVERDUE_INTE_AMT as `应收未收利息`, case when compensate.BILL_NO is not null then '是' else '否' end as `代偿状态`, deductall.begin_late_date as `逾期开始日期`, date_add(deductall.begin_late_date,1) as `新增日期`, datediff(from_unixtime(unix_timestamp(deductall.CURR_DATE,'yyyymmdd'),'yyyy-mm-dd') ,deductall.begin_late_date) as `逾期天数` from loginfo.union_flag1 deductall left join loginfo.t_loan_use_req ureq on ureq.bill_no=trim(deductall.bill_no) left join loginfo.t_loan_credit_req creq on creq.LMT_SERNO=ureq.LMT_SERNO left join loginfo.merchant_zc merchant on creq.merchant_no=merchant.mcht_cd left JOIN loginfo.T_REOCN_LOANINFO loaninfo on trim(deductall.bill_no)=loaninfo.bill_no and loaninfo.current_data=trim(deductall.curr_date) left join loginfo.t_loan_compernsatory compensate on ureq.BILL_NO=compensate.BILL_NO left join loginfo.t_allinpay_dict dict1 on merchant.aip_bran_id = dict1.dict_id and dict1.dict_type='aip_bran_id' left join loginfo.t_allinpay_dict dict2 on merchant.inst_oid = dict2.dict_id and dict2.dict_type='inst_oid' left join loginfo.t_allinpay_dict dict3 on merchant.up_mcc_cd = dict3.dict_id and dict3.dict_type='up_mcc_cd' left join loginfo.t_allinpay_dict dict4 on merchant.city_cd = dict4.dict_id and dict4.dict_type='city_cd' where creq.prod_code='8005000001' and from_unixtime(unix_timestamp(deductall.CURR_DATE,'yyyymmdd'))>=date_add(current_date(),-5) """ print("OK" + " " + sql2 + "\n") cur.execute(sql2) # , async=True) data = as_pandas(cur) print(len(data)) name = '/home/data/deduct/deduct_gansu_late5_' + StrTool.get_the_date_str( '', -1) + '.xlsx' writer = pd.ExcelWriter(name) data.to_excel(writer, 'Sheet1') writer.save() cur.close() conn.close()
client = Client(the_conf.hdfs_ip()) # "http://10.2.201.197:50070" a = MyHdfsFile.get_child(client, "/data/posflow/allinpay_utf8_zc") b = MyHdfsFile.get_child_file(client,"/data/posflow/allinpay_utf8_zc") c = MyHdfsFile.get_child_dir(client, "/data/posflow/allinpay_utf8_zc") # test # MyHdfsFile.delete(client, "/data/posflow/allinpay_utf8_zc", "*agt_cpy*") # test if the_conf.is_test(): day_str = the_conf.test_date() days = 9 else: day_str = StrTool.get_param_str(1, "") days = StrTool.get_param_int(2, 1) day_str = StrTool.get_the_date_str(day_str) date1 = StrTool.get_the_date(day_str) for i in range(0, days): delta = days - i - 1 date2 = date1 - datetime.timedelta(days=delta) day_str2 = date2.strftime("%Y%m%d") run_sftp_file(the_conf, day_str2) run_unzip_file(the_conf, day_str2) run_conv_file_local_to_hdfs(the_conf, day_str2) run_hive(the_conf, the_date=day_str2) run_remove_files(the_conf, day_str2, 0) print("ok")
if the_conf.is_test(): day_str = the_conf.test_date() days = 190 else: the_conf.m_project_id = StrTool.get_param_int(1, 1) day_str = StrTool.get_param_str(2, "") days = StrTool.get_param_int(3, 1) if the_conf.m_project_id == 1: return_code = subprocess.call("/app/code/posflow_loader/ftpcmd.sh", shell=True) print(return_code) f_delta = the_conf.get_data("file_date_delta" + str(the_conf.m_project_id), "0") day_str = StrTool.get_the_date_str(day_str, -int(f_delta)) del_range = 30 # 删除旧数据的时间范围,天 keep_range = 7 # 保留最近旧数据的时间范围,天 for i in range(0, del_range): run_remove_files(the_conf, day_str, -(days + keep_range + del_range - 1 - i)) date1 = StrTool.get_the_date(day_str) for i in range(0, days): delta = days - i - 1 date2 = date1 - datetime.timedelta(days=delta) day_str2 = date2.strftime("%Y%m%d") run_remove_files(the_conf, day_str2, -keep_range) run_remove_files(the_conf, day_str2, 0)
def run_conv_file_local_to_hdfs(conf: ConfigData, the_date: str, is_baoli=True): """ # client.upload('/shouyinbao/', "/home/testFolder/logflow/bl_shouyinbao/UTF8/20181101/9999100000/t1_trxrecord_20181101_V2.csv", cleanup=True) # dat = client.list('/shouyinbao/', status=False) # print(dat) # root_path = "/home/bd/桌面/201811_flow/zc_shouyinbao/UNZIP/" # dest_dir1 = "/home/bd/桌面/201811_flow/zc_shouyinbao/UTF8/" # dest_dir2 = "/shouyinbao/zc_shouyinbao/UTF8/" # root_path = "/home/testFolder/logflow/bl_shouyinbao/UNZIP/" # dest_dir1 = "/home/testFolder/logflow/bl_shouyinbao/UTF8/" # dest_dir2 = "/shouyinbao/zc_shouyinbao/UTF8/" # i_file = '/home/testFolder/logflow/bl_shouyinbao/20181101/9999100000/t1_trxrecord_20181101_V2.csv' # o_file = '/home/testFolder/logflow/bl_shouyinbao/UTF8/20181101/9999100000/t1_trxrecord_20181101_V2.csv' :param conf: :param the_date: :param is_baoli: :return: """ the_date = StrTool.get_the_date_str(the_date) p_client = MyClient(url=conf.hdfs_ip()) # "http://10.2.201.197:50070" # webhdfs 默认是 dr.who ,不能伪装成其他用户,可以在配置里修改 hadoop.http.staticuser.user=dr.who # https://www.cnblogs.com/peizhe123/p/5540845.html root_path = os.path.join(conf.get_data_path(), the_date) dest_dir1 = os.path.join(conf.get_utf8_path(), the_date) dest_dir2 = str( pathlib.PurePosixPath(conf.get_hdfs_path()).joinpath(the_date)) f_name = conf.get_file_name( the_date) # "t1_trxrecord_" the_date # "_V2.csv" print("Start\n") branches = MyLocalFile.get_child_dir(root_path) for aBranch in branches: if MyLocalFile.check_branch(aBranch): files = MyLocalFile.get_child_file(aBranch) f_a_branch = os.path.basename(aBranch) for aFile in files: if MyLocalFile.check_file(aFile, f_name): to_file1 = os.path.join(dest_dir1, f_a_branch, f_name) to_file2 = str( pathlib.PurePosixPath(dest_dir2).joinpath( f_a_branch, f_name)) f_add_date = conf.get_hive_add_date(the_date) f_need_head = conf.get_hive_head() # False MyLocalFile.conv_file_local(aFile, to_file1, need_first_line=f_need_head, p_add_head=f_add_date) MyHdfsFile.safe_make_dir(p_client, to_file2) # client.newupload(to_file2, to_file1, encoding='utf-8') the_file = p_client.status(to_file2, strict=False) if the_file is None: p_client.upload(to_file2, to_file1) p_client.set_permission(to_file2, 777) # client.set_owner(thePath,owner='hdfs',group='supergroup') elif the_file['type'].lower() == 'file': # 'directory' p_client.set_permission(to_file2, 777)