hive数据文件备份

#-*- coding: utf-8 -*-
import os
import sys
import time
import datetime
import pymysql
from dateutil.parser import parse
#没有传参默认是昨天
if len(sys.argv) < 2 :
    vdate = datetime.datetime.strftime(datetime.datetime.today() + datetime.timedelta(days=-1), '%Y%m%d')
else:
    vdate = sys.argv[1]

print('备份数据日期{vdate}'.format(vdate=vdate))
    
#元数据库
mysql_conn = pymysql.connect(host='127.0.0.1',port=3306,user='hive',passwd='123456',db='hive',charset='utf8')
cursor = mysql_conn.cursor()
#从底层开始备份
db_sql = u'''
        select name from hive.DBS 
        order by 
            case when name = 'ods' then 100
                     when name = 'dwd' then 99
                     when name = 'dws' then 98
                     when name = 'ads' then 97
                     else 1 end desc  
        '''
cursor.execute(db_sql)
db_list = cursor.fetchall()
dir = 'mkdir -p /data10/hive_data_bak/{vdate}'.format(vdate=vdate)
os.system(dir)

for db in db_list:
    if db[0] not in ('tmp','testdb'):
        dir = 'mkdir -p /data10/hive_data_bak/{vdate}/{db}'.format(vdate = vdate,db = db[0])
        os.system(dir)
		
        #每个库每个表的每个分区或者全表,只适合按天为第一级分区的表，根据自己的需求来
        tab_sql = u'''
           select distinct c.name,a.tbl_name,b.part_name,db_location_uri
              ,case when b.part_name is not null then 
                        concat(replace(db_location_uri,'hdfs://nameservice1',''),'/',a.tbl_name,'/',b.part_name)
                    else concat(replace(db_location_uri,'hdfs://nameservice1',''),'/',a.tbl_name) end as db_location
            from hive.TBLS a 
            left join hive.PARTITIONS b on a.tbl_id = b.tbl_id 
            inner join hive.DBS c on a.db_id = c.db_id
            where c.name = '{db_name}'  
                and  (b.part_name like '%{vdate}%' or b.part_name is null) 
                #特殊表
				and a.tbl_name not like ('%_tmp_%') 
                and a.tbl_name not like ('%tmp_%') 
                and a.tbl_name not like ('%_tmp%') 
                and a.tbl_name not like ('%bak')
                #天表
				and (right(a.tbl_name,8) = '{vdate}' or (right(a.tbl_name,8) REGEXP '[^0-9.]')<>0)
                '''.format(vdate = vdate,db_name = db[0])
        #print(tab_sql)
        #列出每个库的每个表
        cursor.execute(tab_sql)
        tab_list = cursor.fetchall()
        for tab in tab_list:
            #4点到9点不跑数,影响业务
            while True:
                if datetime.datetime.today().hour < 4 or datetime.datetime.today().hour > 9:
                    break
                else:
                    print('4点到9点不跑数，正在休眠！！！！')
                    time.sleep(600)
                    
            dir = 'mkdir -p /data10/hive_data_bak/{vdate}/{db}/{tab}'.format(vdate=vdate,db=db[0],tab=tab[1])
            os.system(dir)
            
            source = tab[4]
            if tab[2] == None:
                source = tab[4] + '/*'
                
            target = '/data10/hive_data_bak/{vdate}/{db}/{tab}/'.format(vdate=vdate,db=db[0],tab=tab[1])
            get_dfs = 'hadoop dfs -get {source} {target}'.format(source=source,target=target)
            
            print(datetime.datetime.now())
            print('正在备份{source}'.format(source=tab[4]))
            print(get_dfs)
            os.system(get_dfs)
print(datetime.datetime.now())

# 关闭连接
cursor.close()
mysql_conn.close()

print('完成')