mysql导入hive进行分析

-------------------------mysql---创建表----------------------------------
mysql -uroot -p123456
create database if not exists stucour;
use stucour;
create table if not exists student (
id int,
name varchar(20),
hoby varchar(20),
primary key (id)
);

insert into student (id,name,hoby)
values (001,"wangwu","football");

select * from student;

create table if not exists course(
id int,
name varchar(20),
teacher varchar(20),
primary key (id)
);
insert into course values (0001,"math","teacherwang");
insert into course values (0002,"chinese","teacherli");

create table if not exists stu_course(
stuid varchar(20),
courid varchar(20)
);
insert into stu_course values (001,0002);
insert into stu_course values (001,0001);
insert into stu_course values (002,0001);
insert into stu_course values (003,0002);

student.txt course.txt stu_course.txt

-------------------------hive-----创建表------------------------------
show databases;
create database if not exists stucour;
use stucour;
create table student(
id int,
name string,
hoby string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;
load data local inpath '/opt/datas/student.txt' into table student;
select * from student;

create table if not exists course(
id int,
name string,
teacher string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data local inpath '/opt/datas/course.txt' into table course;
truncate table course;
select * from course;


create table if not exists stu_course(
stuid string,
courid string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data local inpath '/opt/datas/stu_course.txt' into table stu_course;
----------------sqoop-----import-sql-to-hive-------------------
bin/sqoop import \
--connect jdbc:mysql://master:3306/stucour \
--username root \
--password 123456 \
--table student \
--fields-terminated-by '\t' \
--delete-target-dir \
--num-mappers 1 \
--hive-import \
--hive-database stucour \
--hive-table student

bin/sqoop import \
--connect jdbc:mysql://master:3306/stucour \
--username root \
--password 123456 \
--table course \
--fields-terminated-by '\t' \
--delete-target-dir \
--num-mappers 1 \
--hive-import \
--hive-database stucour \
--hive-table course

bin/sqoop import \
--connect jdbc:mysql://master:3306/stucour \
--username root \
--password 123456 \
--table stu_course \
--fields-terminated-by '\t' \
--delete-target-dir \
--num-mappers 1 \
--hive-import \
--hive-database stucour \
--hive-table stu_course
--------------------------------------------查询hive---------------------------------------
select * from student,stu_course where student.id=stu_course.stuid;
select * from student,stu_course,course where student.id=stu_course.stuid and stu_course.stuid=course.id;

//查询多个表
select distinct id,name,hoby from student;//查询去重

insert overwrite table student select distinct id,name,hoby from student; //将各字段去重后覆盖到原表中

//将HQL查询结果写入MYSQL数据库中

bin/hive -e "select * from student ;" > /opt/datas/studenthive.txt  //将HIVE分析的数据导入到本地