Hive—Hbase—Sqoop—Mysql

桃扇骨 2022-08-05 05:23 166阅读 0赞

1:创建Hive 和hBase 关系表  
use order;  
   
CREATE TABLE  tmp\_member\_info\_new\_mysql  
(memberkey string, memberid string,uid string,email string, fullname string,sex string,                  
birthday string,province string,area string, edulevel string,                 
unitname string, logdata string,domain string)  
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'   
WITH SERDEPROPERTIES  
("hbase.columns.mapping" = ":key\#b,info:memberid,info:uid,info:email,info:fullname,info:sex,  
info:birthday,info:province,info:area,info:edulevel,  
info:unitname,info:logdata,info:domain")   
TBLPROPERTIES ("hbase.table.name"="tmp\_member\_info\_new\_mysql");  
  
  
  
  
2：导入数据到hive 和hbase ：  
rowkey=memberid\_uid  
  
  
insert  overwrite  table tmp\_member\_info\_new\_mysql  
select  concat\_ws('\_',lpad(uid,10,'0'),memberid) as memberkey, memberid,uid, email, fullname,sex ,                  
birthday,province,area, edulevel,unitname,logdata,domain   
from  order.tmp\_member\_info\_new  ;  
  
  
3： 导出到对应的hdfs文件系统目录下：：hadoop fs -mkdir  /user/cdel/tmp\_member\_info\_new\_mysql  
  
  
  
  
insert overwrite   directory '/user/cdel/tmp\_member\_info\_new\_mysql\_20150420'   
select  me.memberid,me.uid,me.email,me.fullname,me.sex,me.birthday,me.province,me.area,me.edulevel,me.unitname,me.logdata,me.domain     
from  order.tmp\_member\_info\_new\_mysql me    where me.logdata <='20150420';  
  
  
insert overwrite   directory '/user/cdel/tmp\_member\_info\_new\_mysql\_20150421'   
select  me.memberid,me.uid,me.email,me.fullname,me.sex,me.birthday,me.province,me.area,me.edulevel,me.unitname,me.logdata,me.domain     
from  order.tmp\_member\_info\_new\_mysql me    where me.logdata >='20150421';  
  
  
   
  
  
3:使用Sqoop导出到mysql中：  
  
  
sqoop export --connect jdbc:mysql://10.223.0.61:3306/cdellog --username cdel --password hdcdel20141217 -m 2 --table member\_info\_new --columns "memberid,uid,email,fullname,sex,birthday,province,area,edulevel,unitname,logdata,domain" --export-dir /user/cdel/tmp\_member\_info\_new\_mysql --fields-terminated-by '\\001';  
  
  
sqoop export --connect jdbc:mysql://10.223.0.61:3306/cdellog --username cdel --password hdcdel20141217 -m 2 --table member\_info\_new --columns "memberid,uid,email,fullname,sex,birthday,province,area,edulevel,unitname,logdata,domain" --export-dir /user/cdel/tmp\_member\_info\_new\_mysql\_20150420 --fields-terminated-by '\\001';  
  
  
sqoop export --connect jdbc:mysql://10.223.0.61:3306/cdellog --username cdel --password hdcdel20141217 -m 2 --table member\_info\_new --columns "memberid,uid,email,fullname,sex,birthday,province,area,edulevel,unitname,logdata,domain" --export-dir /user/cdel/tmp\_member\_info\_new\_mysql\_20150421 --fields-terminated-by '\\001';  
  
  
  
  
select  me.memberid,me.uid,me.email,me.fullname,me.sex,me.birthday,me.province,me.area,me.edulevel,me.unitname,max(me.logdata) as logdata,me.domain     
from  order.tmp\_member\_info\_new me   
where   uid is not null and uid !=''

Hive—Hbase—Sqoop—Mysql

发表评论取消回复

还没有评论，来说两句吧...

相关阅读