导出hive数据库建表语句到git库

因为当前prod环境和本地开发环境有网络隔离，不能直接访问，故导出的hive数据库建表语句打包压缩后上传到HDFS上，再手动下载到本地，上传到Git；

STEP 1：到处建表语句，压缩打包上传到HDFS；

STEP 2：手动下载建表语句包，解压移动到git代码库；

 

文件名： export_create.sh 

#!/bin/bash

# SETP 1:
# export table create script and put it into HDFS directory for downloading.
# create download uri text file, used by STEP 2.(unzip_move.sh)

# run on hdfs prod environment, eg 101.

currentDb=$1
workDir=/tmp/export
scriptDir=$workDir/$currentDb
tableFile=$scriptDir/table.txt
hdfsDir=/user/adore.chen/hive_stats/create_script
logFile=$workDir/export.log


# check input parameter
if [ ! -n "$currentDb" ];then
    echo "parameter database is empty."
    exit 1
else
    echo "to export create script of database: $currentDb"
fi

# check script dir
if [ ! -d $scriptDir  ];then
  mkdir -p $scriptDir
fi

# get tables of input database
echo "use $currentDb; show tables" > ${logFile}
if [ $currentDb != "" ];then
      hive -e "use $currentDb;show tables" > ${tableFile}
      cat $tableFile >> $logFile

      # get create script
      cat $tableFile | while read tab_name
      do
              echo "show create table $currentDb.$tab_name"
              hive -e "show create table $currentDb.$tab_name;" | grep -v createtab_stmt > ${scriptDir}/${tab_name}.sql
      done
fi

# compress sql and upload to HDFS
cd $workDir
tar -zcvf ${currentDb}.tar.gz $currentDb/*.sql
$HADOOP_HOME/bin/hadoop fs -mkdir -p $hdfsDir
$HADOOP_HOME/bin/hadoop fs -put -f ${currentDb}.tar.gz $hdfsDir

 

使用方式：

sh export_create.sh your_db_name 

 

文件名： unzip_move.sh

#!/bin/bash

# SETP 2:
# download the db.gz file to local,
# and then unzip and move to git repository.

# run on your local machine.

currentDb=$1

# check input parameter
if [ ! -n "$currentDb" ];then
    echo "parameter database is empty."
    exit 1
else
    echo "to unzip script of database: $currentDb"
fi

# unzip the db.gz file
tar -xvf ~/Downloads/$currentDb.tar

# move to git location
mv -f ~/Downloads/$currentDb ~/IdeaProjects/yourProject/ops/src/main/resources/hive/

 

 

使用方式：

sh unzip_move.sh your_db_name

先收到从HDFS下载 yourDb.tar 然后运行unzip_move.sh 就可以把建表语句导出到git库了。