将数据从mysql导入到hive表

最新推荐文章于 2022-11-06 21:52:54 发布

原创最新推荐文章于 2022-11-06 21:52:54 发布 · 2.6k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#hadoop #hdfs

javaEE 同时被 2 个专栏收录

55 篇文章

订阅专栏

大数据Hadoop

10 篇文章

订阅专栏

本文介绍了一种使用Bash脚本从MySQL数据库导出数据至文本文件的方法，然后通过Hive加载至指定表分区，实现数据的高效迁移。详细步骤包括设置数据库连接参数、执行SQL查询、结果输出至文件及Hive数据加载。

代码脚本如下：

#!/bin/bash
# 连接mysql数据库，并查询结果，将结果输出到文件中
# 1.定义连接变量
mysql_hostname='172.28.65.133'
mysql_port='3306'
mysql_username='root'
mysql_password='xxxxxx'
#
dbname='recom_video'
time=`date -d "1 day ago" +"%Y-%m-%d"`
save_f="t_st_video_info_${time}.txt"
save_p="/data/recommend/"
#
mysql_sq="select 
        IFNULL(id,''),
        IFNULL(video_id,''),
        IFNULL(video_name,''),
        IFNULL(video_desc,''),
        IFNULL(video_url,''),
        IFNULL(hits_num,''),
        IFNULL(praise_num,''),
        IFNULL(create_time,''),
        IFNULL(update_time,'')
from t_st_video_info 
where create_time <= '${time} 23:59:59'"

echo "开始链接数据库..."

# 2.连接数据库
result=`mysql -h${mysql_hostname} -P${mysql_port} -u${mysql_username} -p${mysql_password} << EOF
use recom_video;
$mysql_sq;
quit
EOF`

# 判断是否连接成功
if [ $? = 0 ] 
then
 echo "连接成功。。。"
else
 echo "连接失败。。。"
 exit
fi
echo "写入查询结果..."

# 将结果写入文本
echo "$result" | tail -n +2 | sed 's/\t/,/g' >> $save_p$save_f
echo "写入完成..."

#
target_table="t_di_video_info_ad"
op_time=`date -d "1 day ago" +"%Y%m%d"`

hive -e "alter table ${target_table} drop partition(dayid='$op_time');"
hive -e "use recommend_video;load data local inpath'$save_p$save_f' overwrite into table ${target_table} partition(dayid='${op_time}');"

hive表：

CREATE TABLE `t_di_video_info_ad`(
  `id` int, 
  `video_id` string, 
  `video_name` string, 
  `video_desc` string, 
  `video_url` string, 
  `hits_num` string, 
  `praise_num` string, 
  `create_time` string, 
  `update_time` string)
COMMENT '视频信息表'
PARTITIONED BY ( `dayid` string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' STORED AS TEXTFILE

SQL：

load data inpath '/recommend/video/t_st_video_info.txt' into table video_info PARTITION (date = '20200608');