将hive库的数据同步到PostgreSQL库中
#!/bin/bash
# 参数校验
if [ $# -lt 1 ] ; then
echo "期待1个参数"
exit 1
fi
#时间处理
dt="$(date -d "$1 1 day ago" +'%Y-%m-%d')"
# pg库的配置连接信息
rdb="postgresql"
host="**********************"
port="**********************"
user="**********************"
password="******************"
database="******************"
export LANG="en_US.UTF-8"
export PGPASSWORD=${password}
hive_table="****************"
hive_partition="dt='${dt}'"
pg_table="******************"
#路径
current_path="$(cd $(dirname $0);pwd)"
tmp_file="$current_path/data"
hive_sql="
select
name,
sex,
age
from
${hive_table}
where dt='${dt}';
"
sudo -u root /home/apache-hive-2.1.1-bin/bin/hive -e " ${hive_sql} " > $tmp_file || exit 1
pg_sql="
begin; -- 回滚用
truncate table ${pg_table}; -- 清空表数据
alter sequence ${pg_table}_id_seq restart with 1; -- 表的自增id 置为1
copy ${pg_table}(name,sex,age) from stdin; -- 将数据导入到pg中
commit; -- 回滚用 中间任意一步出错就回滚
"
# id_seq 一般会在前面加上table名 ${pg_table}_id_seq
# 如果 alter sequence ${pg_table}_id_seq restart with 1; 报以下错:
# ERROR: must be owner of sequence ${pg_table}_id_seq
# 就将其改成 select setval('${pg_table}_id_seq'::regclass,1);
# setval不需要owner权限
cat $tmp_file | psql -h${host} -p${port} -d${database} -U${user} -c " ${pg_sql} " || exit 1