备份表数据，将历史数据加载到新表中（hive）

if [ $# -eq 2 ];then
    yesterday=`date -d yesterday +%Y-%m-%d`
    stat_date=${yesterday}
elif [ $# -eq 3 ];then
    stat_date="$3"
else
    echo "wrong arg[] number"
    exit 1
fi


cur_date=`date +%Y%m%d`
#取源数据库和目标表编号前辍


dbname=$1
tab_name=$2


# hive元数据库配置信息
function get_hive_params()
{


while read line;do
    eval "$line"
done < /disk4/azkaban/shell/cfg/.hive_metastore_config


# 判断是否获取到对应数据库用户密码
if [ -z "$username" ] || [ -z "$password" ]; then
       echo "The auth info of has not been configured"
        exit 1;
fi


}
get_hive_params "hiv"


hiv_user=$username
hiv_pwd=$password
hiv_ip=$ip
hiv_port=$port


#取hive元数据
function get_hive_col()
{
clset=`mysql -h$hiv_ip -u$hiv_user -p$hiv_pwd -P$hiv_port  --default-character-set=utf8 -Dhive  -N -e"
select
case when t3.INTEGER_IDX=0 then
upper(t3.column_name)
else
concat(',',upper(t3.column_name))
end mm
from TBLS t1
left join SDS t2
on t1.sd_id=t2.sd_id
left join COLUMNS_V2 t3
on t2.cd_id=t3.cd_id
left join DBS t4
on t1.db_id=t4.db_id
where t1.tbl_name =lower(concat('$cfg_tgtotablecn','$tab_name'))
and t4.name='dc_ods'
order by t3.INTEGER_IDX;
"
`
if [ -z "$clset" ]; then
echo "$table_name does not exist."
else
clset1=" SELECT "$clset",DT FROM dc_ods."$cfg_tgtotablecn$tab_name$cur_date"_bak;"
fi


echo $clset1


#执行修改备份数据表
sqlalt=" use dc_ods; ALTER TABLE "$cfg_tgtotablecn$tab_name" RENAME TO "$cfg_tgtotablecn$tab_name$cur_date"_bak;"
echo $sqlalt>/disk4/ddl/dsql/$cfg_tgtotablecn$tab_name"_alter.sql"


hive -f /disk4/ddl/dsql/$cfg_tgtotablecn$tab_name"_alter.sql"
echo "备份完成"
#执行drop stage表


sqldrp=" use dc_stage; drop table "$cfg_tgtstablecn$tab_name";"


echo $sqldrp >/disk4/ddl/dsql/$cfg_tgtstablecn$tab_name"_drop.sql"


hive -f /disk4/ddl/dsql/$cfg_tgtstablecn$tab_name"_drop.sql"


echo "清除表完成"


#调用建表功能进行建stage,ods层表


sh src_sys_create_one_table_huihuahua.sh $dbname $tab_name


hive -f /disk4/ddl/stgddl/$cfg_tgtstablecn$tab_name".sql"


hive -f /disk4/ddl/odsddl/$cfg_tgtotablecn$tab_name".sql"


echo " 新表建完"


#执行历史数据迁移到新表结构


hive -f /disk4/ddl/dsql/$cfg_tgtotablecn$tab_name"_insert.sql"


echo "数据迁移完成"


}


#读取映射表
function get_map_table()
{
 mapna=`/usr/bin/mysql -u*** -p*** -h*** -P3306 --default-character-set=utf8 -Dmeta  -N -e"
 select concat(tableSchema,'#',tgtotablecn,'#',tgtstablecn)
from (
select tableSchema,tgtotablecn,tgtstablecn from t_map_dbs_cfg where lower(tableSchema) =lower('$dbname')
)tmx;
 "`
cfg_tableSchema=`echo $mapna | awk -F'#' '{print $1}'`
cfg_tgtotablecn=`echo $mapna | awk -F'#' '{print $2}'`
cfg_tgtstablecn=`echo $mapna | awk -F'#' '{print $3}'`




}










get_map_table
get_hive_col




if [ $? -eq 0 ]
then
    echo " prom success!"
else
    echo " prom Error Failed"
fi