hive 使用udf函数实现数据导入到mysql

80 阅读 0 评论 53 点赞

我是靠谱客的博主彪壮苗条，最近开发中收集的这篇文章主要介绍hive 使用udf函数实现数据导入到mysql，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

利用hive内置的hive-contrib 来实现udf导入mysql，同时还需要mysql驱动包

例子：

add jar /usr/local/hive-0.13.1b/hive-contrib-0.13.1.jar;
add jar /usr/local/hive-0.13.1b/mysql-connector-java-5.1.32-bin.jar;
create temporary function dboutput as 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput';
select dboutput('jdbc:mysql://192.168.1.3:3306/ minp_db','mysqlusername','password','INSERT INTO rs_click(date,site,click_vol,gradient) VALUES (?,?,?,?)',r.d,r.nv,r.ns,r.g) from

(select from_unixtime(unix_timestamp() ,'yyyy-MM-dd') d,x.x1 nv,z.z2 ns,round(abs(x.x2-x.x3)/z.z2,2) g from (select a.p x1,a.nv x2,a.ov x3 from ( select n.np p , n.ncv nv, o.ocv ov from (select param op ,sum(1) ocv from odm_partitioned where dt='2015-05-27' and unitid='pc_ucenter' and visittype='click' and link='pc' group by param) o join (select param np ,sum(1) ncv from odm_partitioned where dt='2015-05-28' and unitid='pc_ucenter' and visittype='click' and link='pc' group by param) n on o.op = n.np) a ) x full outer join ( select a.p z1,sum(a.nv) z2 from ( select n.np p , n.ncv nv, o.ocv ov from (select param op ,sum(1) ocv from odm_partitioned where dt='2015-05-27' and unitid='pc_ucenter' and visittype='click' and link='pc' group by param) o join (select param np ,sum(1) ncv from odm_partitioned where dt='2015-05-28' and unitid='pc_ucenter' and visittype='click' and link='pc' group by param) n on o.op = n.np) a group by a.p ) z on x.x1=z.z1) r;

注：蓝色字体为需要导入的数据集