指定要导出的cloudera数据库,执行脚本:
#! /bin/bash
echo "hive test..."
# 读取表create table
funCreateTable(){
echo "-- --------------$2" >> create_table_first.txt
echo "use $1;" >> create_table_first.txt
echo "drop table if exists $2;" >> create_table_first.txt
hive -e "use $1; show create table $2;" >> create_table_first.txt
sed -e '/WARN/d' create_table_first.txt > create_table.txt
echo ";" >> create_table.txt
echo "msck repair table $2;" >> create_table.txt
cat create_table.txt >> $3
rm create_table.txt
rm create_table_first.txt
}
# 读取 db下的所有表
funReadTable(){
hive -e "use $1; show tables" >> hivetablesfirst.txt
sed -e '/WARN/d' hivetablesfirst.txt > hivetables.txt
rm hivetablesfirst.txt
}
# 读取db下的所有 table strunct
funReadDBCreateTable(){
file="$1.sql"
funReadTable $1
if [ -f "hivetables.txt" ]; then
cat hivetables.txt | while read eachline
do
funCreateTable $1 $eachline $file
done
fi
rm hivetables.txt
}
# 主程序
funReadDBCreateTable cloudera
# 上传到HDFS
while true
do
if [ ! -f "asmp.sql" ]; then
echo "sorry,waiting for file is created..."
sleep 10
else
hdfs dfs -put *.sql /user/lhx/test
rm -rf asmp.sql
echo "end hive test!"
break;
fi
done
执行结果:
--------------test_to_hive
use cloudera;
drop table if exists test_to_hive;
CREATE TABLE `test_to_hive`(
`s1` string COMMENT '',
`s2` string COMMENT '')
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
LOCATION
'hdfs://bigdata01.lhx.com:8020/user/hive/warehouse/cloudera.db/test_to_hive'
TBLPROPERTIES (
'transient_lastDdlTime'='1538116496')
;
msck repair table test_to_hive;
-- --------------test_to_hive2
use cloudera;
drop table if exists test_to_hive2;
CREATE TABLE `test_to_hive2`(
`name` string COMMENT '',
`age` int COMMENT '',
`id` int COMMENT '',
`type` string COMMENT '',
`database` string COMMENT '',
`table` string COMMENT '')
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
LOCATION
'hdfs://bigdata01.lhx.com:8020/user/hive/warehouse/cloudera.db/test_to_hive2'
TBLPROPERTIES (
'transient_lastDdlTime'='1539583665')
;
msck repair table test_to_hive2;