hive集群迁移

一、主要任务

1、建立hive表
2、建立分区
3、验证数据
4、配置调度任务
5、校验任务

二、hive建表语句

• 获取所有hive库所有表的建表语句

#!/bin/bash
rm -rf databases.txt
hive -e " show databases; exit ;"  | grep -v "WARN"> databases
#sleep(2)
rm -rf ./tables/*
for database in `cat databases`
do
  {
  hive -S -e " use $database ;  show tables ; exit ;"  | grep -v "WARN"> ./tables/$database
  rm -rf ./desc_table/$database
  for table in `cat ./tables/$database`
  do
     hive -e "use $database ; show create table $table ;"  | grep -v "WARN">> ./desc_table/$database
  done
  }
done

• 获取指定库表建表语句

  database=$1
  echo ${database}
  hive -S -e " use $database ;  show tables ; exit ;"  | grep -v "WARN"> ./tables/$database
  rm -rf ./desc_table/$database
  for table in `cat ./tables/$database`
  do
     hive -e "use $database ; show create table $table ;"  | grep -v "WARN">> ./desc_table/$database
  done

三、自动建立分区

MSCK REPAIR TABLE table_name;

使用oozie调度建立分区

  hive -e "ALTER TABLE ods_tidb.${hive_table_name} DROP IF EXISTS partition(dt='${partitions1}');ALTER TABLE ods_tidb.${hive_table_name} ADD partition(dt='${partitions1}') LOCATION '/user/hive/ods_tidb/${hive_table_name}/${partitions1}'"

使用oozie调度建立两个分区

  db_start=1
  db_end=14
  while [ ${db_start} != ${db_end} ]
  do
  mysql_database_name=${db_pre}${db_start} 
  file_name=${mysql_table_name}'.sh'

  #库3是测试库
  if [ ${db_start} != 3 ];then
  hive -e "ALTER TABLE ods_tidb.${hive_table_name} DROP IF EXISTS partition(database_name='${partitions1}',dt='${partitions2}');ALTER TABLE ods_tidb.${hive_table_name} ADD partition(database_name='${partitions1}',dt='${partitions2}') LOCATION '/user/hive/ods_tidb/${hive_table_name}/${partitions1}/${partitions2}'"
  fi

  let "db_start = ${db_start} + 1"

  done