使用sqoop进行将Hive 词频统计的结果数据传输到Mysql中。
1、mysql准备接受数据的数据库与表
#查看数据库
show databases;
#创建数据库
create database if not exists sqoop1;
#使用数据库
use sqoop1;
#查看表
show tables;
#创建表
create table if not exists `wc` (`word` varchar(100), `count` int) engine=InnoDB DEFAULT CHARSET =utf8;
#查看表是否创建成功
show tables;
#查看表内容
select * from wc;
2、hive准备待传输的数据
show databases;
create database if not exists sqoop1;
show databases;
use sqoop1;
show tables;
create table if not exists pctext(line string);
show tables;
#映射
load data local inpath '/home/hadoop/1342-0.txt' into table pctext;
#词频统计
select split(line,' ') as word from pctext;
#词频统计
select explode(split(line,' ')) as word from pctext;
#查看统计结果
select word,count(1) as count from (select explode(split(line,'')) as word from pctext) w group by word order by word;
#创建词频表
create table if not exists wc3 row format delimited fields terminated by '\t' as select word,count(1) as count from (select explode(split(line,' ')) as word from pctext) word group by word order by word ;
select * from wc;
3、sqoop进行数据传输
sqoop export --connect jdbc:mysql://127.0.0.1:3306/sqoop1?useSSL=false --username root --password hao991206 --table wc --export-dir /user/hive/warehouse/hive.db/wc --input-fields-terminated-by '\t';
4、mysql查看传输结果
use sqoop1;
select * from wc;
