sqoop将hive中的数据导入mysql

hive 数据通过sqoop 将增量数据导入mysql

查看hive表结构

1. hive> desc hbase_visit;
   
2. OK
   
3. key     string     from deserializer
   
4. insert_time     string     from deserializer
   
5. label     string     from deserializer
   
6. sessionid     string     from deserializer
   
7. stay_time     int     from deserializer
   
8. visit_time     bigint     from deserializer
   
9. leave_time     bigint     from deserializer
   
10. page_url     string     from deserializer
    
11. referrer_url     string     from deserializer
    
12. visit_number     int     from deserializer
    
13. site     string     from deserializer
    
14. ip     string     from deserializer

hive新建内部表

“\001” 为分隔符

1. create table hbase_visit_cp(
   
2. key_table varchar(100),
   
3. insert_time varchar(20),
   
4. label varchar(50),
   
5. sessionid varchar(50),
   
6. stay_time int,
   
7. visit_time bigint,
   
8. leave_time bigint,
   
9. page_url varchar(2000),
   
10. referrer_url varchar(2000),
    
11. visit_number int,
    
12. site varchar(20),
    
13. ip varchar(20)
    
14. ) row format delimited fields terminated by '\001'

mysql建表

1. create table hbase_visit(
   
2. key_table varchar(100),
   
3. insert_time varchar(20),
   
4. label varchar(50),
   
5. sessionid varchar(50),
   
6. stay_time int,
   
7. visit_time bigint,
   
8. leave_time bigint,
   
9. page_url varchar(2000),
   
10. referrer_url varchar(2000),
    
11. visit_number int,
    
12. site varchar(20),
    
13. ip varchar(20)
    
14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

hive 将增量数据导入hbase_visit_cp

1. insert overwrite table hbase_visit_cp SELECT * from hbase_visit where insert_time>=20150818000000000 and insert_time<20150819000000000

sqoop将数据导入mysql

1. /usr/bin/sqoop export --connect jdbc:mysql://10.10.10.10:3306/test --username ** --password ******** --table hbase_visit --export-dir /user/hive/warehouse/hbase_visit_cp --input-fields-terminated-by '\001' --input-null-string '\\N' --input-null-non-string '\\N'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30150152/viewspace-1778764/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30150152/viewspace-1778764/