Sqoop的导入导出案例

最新推荐文章于 2020-12-22 14:01:01 发布

转载最新推荐文章于 2020-12-22 14:01:01 发布 · 188 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/ibobby/blog/750836

文章标签：

#数据库 #java #大数据

本文分享了一个用于处理水暖器加热时间数据的Shell脚本，该脚本从Greenplum导出数据到HDFS，接着计算加热时间业务指标，并最终将结果导入回Greenplum数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

为什么80%的码农都做不了架构师？>>>

[root@master2 script]# cat run_waterheater_heattime.sh 
#!/bin/bash

#if [ x$1 = x ]
#then
#    echo '请输入当前日期格式为%Y%m%d';
#    exit 0;
#fi

today=`date -d '1 day ago' +"%Y%m%d"`
echo $today

#export greenplum data to hdfs
hdfs dfs -rm -R /user/root/waterheater-data/source/heat-time/$today
sqoop import  \
--connect jdbc:postgresql://host:port/haieredw \
--username fuck \
--password fuck \
--query "SELECT devicetype,fmacid,fts,ftime from (SELECT '06001001'::varchar as devicetype,fmacid,fts,ftime from waterheater.amb_06001001_status_16 where f60600t='306001' and dayid = $today and \$CONDITIONS group by fmacid,fts,ftime UNION ALL SELECT '06002001'::varchar as devicetype,fmacid,fts,ftime from waterheater.amb_06002001_status_16 where f60f004='30f0S1' and dayid = $today and \$CONDITIONS group by fmacid,fts,ftime UNION ALL SELECT '06004001'::varchar as devicetype,fmacid,fts,ftime from waterheater.amb_06004001_status_16 where f60600t='306001' and dayid = $today and \$CONDITIONS group by fmacid,fts,ftime UNION ALL SELECT '06005001'::varchar as devicetype,fmacid,fts,ftime from waterheater.amb_06005001_status_16 where f60607qq='306001' and dayid = $today and \$CONDITIONS group by fmacid,fts,ftime UNION ALL SELECT '06006001'::varchar as devicetype,fmacid,fts,ftime from waterheater.amb_06006001_status_16 where f60600t='306001' and dayid = $today and \$CONDITIONS group by fmacid,fts,ftime) v order by fts"  \
--fields-terminated-by ','  \
--split-by 'fmacid' \
--target-dir /user/root/waterheater-data/source/heat-time/$today

#calculate heattime biz...
hdfs dfs -rm -R /user/root/waterheater-data/out/heat-time/$today
hadoop jar ../executable/apidocs-0.0.1-SNAPSHOT-jar-with-dependencies.jar com.ilifang.samples.bigdata.apidocs.App -D dayid=$today /user/root/waterheater-data/source/heat-time/$today /user/root/waterheater-data/out/heat-time/$today

#import data to greenplum
sqoop export \
--connect 'jdbc:postgresql://host:port/haieredw' \
--username fuck \
--password fuck \
--table heat_time_tmp \
--fields-terminated-by ',' \
--export-dir /user/root/waterheater-data/out/heat-time/$today \
--columns "devicetype,day_id,mac,dayhour,time" \
-- --schema waterheater

echo 'hahah...'

转载于:https://my.oschina.net/ibobby/blog/750836