hive mysql 效率 对比_hive 连接查询sql对比效率

准备4个表

从mysql 导出excel 转换为txt

创建hive 表的导入文件

create table bdqn_student(

sno int,

sname string,

sbirthdate string,

sgender string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ STORED AS TEXTFILE;

create table bdqn_teacher(

tno int,

tname string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ STORED AS TEXTFILE;

create table bdqn_course(

cno int,

cname string,

tno int)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ STORED AS TEXTFILE;

create table bdqn_score(

sno int,

cno int,

score string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ STORED AS TEXTFILE;

Time taken: 4.246 seconds, Fetched: 1 row(s)

hive> create table bdqn_student(

sno int,

sname string,

sbirthdate string,

sgender string);

OK

Time taken: 0.583 seconds

hive> create table bdqn_teacher(

tno int,

tname string);

OK

Time taken: 0.106 seconds

hive> create table bdqn_course(

cno int,

cname string,

tno int);

OK

Time taken: 0.105 seconds

hive>

create table bdqn_score(

sno int,

cno int,

score string);

OK

Time taken: 0.094 seconds

Time taken: 0.094 seconds

hive> show tables;

OK

bdqn_course

bdqn_score

bdqn_student

bdqn_teacher

ncdc

Time taken: 0.021 seconds, Fetched: 5 row(s)

一共四个表

load data local inpath ‘/opt/hadoop/hadoopDATA/sql_Query_do_not_delete/course.txt’ into table bdqn_course

load data local inpath ‘/opt/hadoop/hadoopDATA/sql_Query_do_not_delete/student.txt’ into table bdqn_student

load data local inpath ‘/opt/hadoop/hadoopDATA/sql_Query_do_not_delete/teacher.txt’ into table bdqn_teacher

load data local inpath ‘/opt/hadoop/hadoopDATA/sql_Query_do_not_delete/score.txt’ into table bdqn_score

中文乱码问题解决：

解决方法：

1、修改远程linux机器的配置

[root@rhel ~]#vi /etc/sysconfig/i18n

把LANG改成支持UTF-8的字符集

如： LANG=”zh_CN.UTF-8″ 或者是 LANG=”en_US.UTF-8″ 本文修改为后者

2、修改Secure CRT的Session Options

Options->Session Options->Appearance->Font->新宋体 字符集：中文GB2312 ->Character encoding 为UTF-8

3、OK.

查询：

查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩(提示：子查询，分组)

select st.sname, ascore from bdqn_student st join

(select sno,avg(score) ascore from bdqn_score group by sno having avg(score)>=60) sc on sc.sno=st.sno

hive> select st.sname, ascore from bdqn_student st join

(select sno,avg(score) ascore from bdqn_score group by sno having avg(score)>=60) sc on sc.sno=st.sno;

Total MapReduce jobs = 2

Launching Job 1 out of 2

Number of reduce tasks not specified. Estimated from input data size: 1

In order to change the average load for a reducer (in bytes):

set hive.exec.reducers.bytes.per.reducer=

In order to limit the maximum number of reducers:

set hive.exec.reducers.max=

In order to set a constant number of reducers:

set mapred.reduce.tasks=

Starting Job = job_201507050950_0007, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201507050950_0007

Kill Command = /opt/hadoop/hadoop-1.2.1/libexec/../bin/hadoop job -kill job_201507050950_0007

Hadoop job information fo