小小千想和您聊一聊

当前位置: 首页> 技术分享> hive表的查询

hive表的查询

  1.join 查询

  1、永远是小结果集驱动大结果集(小表驱动大表,小表放在左表)。 2、尽量不要使用join,但是join是难以避免的。

  left join 、 left outer join 、 left semi join(左半开连接,只显示左表信息)

  hive在0.8版本以后开始支持left join

  left join 和 left outer join 效果差不多

  hive的join中的on只能跟等值连接 "=",不能跟< >= <= !=

  join:不加where过滤,叫笛卡尔积

  inner join : 内连接

  outer join :外链接

  full outer join : 全外连接,寻找表中所有满足连接(包括where过滤)。

  ##创建学生测试表

  CREATE TABLE student (

  id INT,

  name string,

  sex string,

  birth string,

  department string,

  address string

  )

  row format delimited fields terminated by ','

  ;

  ##从本地加载数据

  load data local inpath '/root/Desktop/student.txt' into table student;

  ##创建学生成绩测试表

  CREATE TABLE score (

  stu_id INT,

  c_name string,

  grade string

  )

  row format delimited fields terminated by ','

  ;

  ##从hdfs加载数据

  load data inpath '/hive.data/score.txt' into table score;

  ##创建学生基本信息测试表

  create table stuinfo(

  id int,

  height double,

  weight double

  )

  row format delimited fields terminated by ','

  ;

  ##从本地加载数据

  load data local inpath '/root/Desktop/stuinfo.txt' into table stuinfo;

  1.1 左连接

  如果左边有数据,右边没有数据,则左边有数据的记录的对应列返回为空。

  ##使用左连接查询:student表为驱动表,通过id连接

  select

  student.name,

  score.c_name,

  score.grade

  from student

  left join score

  on student.id = score.stu_id

  ;

  1.2 左外连接

  如果左边有数据,右边没有数据,则左边有数据的记录的对应列返回为空。

  ##使用左外连接查询:student表为驱动表,通过id连接

  select

  student.name,

  score.c_name,

  score.grade

  from student

  left outer join score

  on student.id = score.stu_id

  ;

  1.3 左半连接

  left semi join是left join 的一种优化,并且通常用于解决exists in,写left semi join 查询时必须遵循一个限制:右表(sales) 只能在 on子句中出现,且不能在select 表达式中引用右表。

  ##左半连接

  select

  student.*

  from student

  left semi join score on

  student.id=score.id

  ;

  ##查询结果等价于

  select * from student where student.id not in (select stu_id from score);

  1.4 右外连接

  right outer join和right join差不多,不常用

  如果左边没有数据,右边有数据,则右边有数据的记录对应列返回为空。

  ##使用右外连接查询:score表为驱动表,通过id连接

  select

  student.name,

  score.c_name,

  score.grade

  from student

  right outer join score

  on student.id = score.stu_id

  ;

  1.5 全外连接

  ##全外连接

  select

  student.name,

  score.c_name,

  score.grade

  from student

  full outer join score

  on student.id = score.stu_id

  ;

  ##不使用join,from 后面跟多个表名使用","分割 、 inner join 、join :三种效果一样

  select

  student.name,

  score.c_name,

  score.grade

  from student,score

  where

  student.id = score.stu_id

  ;

  1.6 内连接

  ##使用内连接查询所有有考试成绩的学生的学生姓名,学科名,学科成绩,及身高

  select

  student.name,

  score.c_name,

  score.grade,

  stuinfo.height

  from student

  inner join score

  on student.id = score.stu_id

  join stuinfo

  on student.id = stuinfo.id

  ;

  1.7 hive提供一个小表标识,是hive提供的一种优化机制

  ##小表标识:/*+STREAMTABLE(表名)*/

  select

  /*+STREAMTABLE(score)*/

  student.name,

  score.c_name,

  score.grade,

  stuinfo.height

  from student

  inner join score

  on student.id = score.stu_id

  join stuinfo

  on student.id = stuinfo.id

  ;

  1.8 map-side join:

  如果有一个连接表小到足以放入内存, Hive就可以把较小的表放入每个mapper的内存来执行连接操作。这就叫做map连接。当有一大一小表的时候,适合用map-join。会将小表文件缓存,放到内存中,在map端和内存中的数据一一进行匹配,连接查找关系。hive-1.2.1 默认已经开启map-side join:hive.auto.convert.join=true

  select

  student.name,

  score.c_name,

  score.grade,

  stuinfo.height

  from student

  inner join score

  on student.id = score.stu_id

  join stuinfo

  on student.id = stuinfo.id

  ;

  hive 0.7版本以前,需要hive提供的mapjoin()标识。来标识该join为map-side join。标识已经过时,但是写上仍然识别

  select

  /*+MAPJOIN(student)*/

  student.name,

  score.c_name,

  score.grade,

  stuinfo.height

  from student

  inner join score

  on student.id = score.stu_id

  join stuinfo

  on student.id = stuinfo.id

  ;

  hive怎么知道将多大文件缓存,配置文件中配置,下面为默认配置

<property>
    <name>hive.mapjoin.smalltable.filesize</name>
    <value>25000000</value>
  </property>

  2.group by:

  GROUP BY 语句通常会和聚合函数一起使用,按照一个或者多个列对结果进行分组,然后对每个组执行聚合操作。使用group by后,查询的字段要么出现在聚合函数中,要么出现在group by 后面。

  ##查询学生的考试门数,及平均成绩

  select

  count(*),

  avg(grade)

  from student join score

  on student.id=score.stu_id

  group by student.id;

  3.where

  SELECT语句用于选取字段,WHERE语句用于过滤条件,两者结合使用可以查找到符合过滤条件的记录。后面不能跟聚合函数或者聚合函数的结果,能跟普通的查询值或者是方法

  ##查询学生的考试平均成绩大于90分

  select

  count(*),

  avg(grade) avg_score

  from student join score

  on student.id=score.stu_id

  where student.id<106

  group by student.id

  having avg_score>90;

  4.having:

  对查询出来的结果进行过滤,通常和group by搭配使用。

  ##查询学生的考试平均成绩大于90分的学生id及平均成绩

  select

  count(*),

  avg(grade) avg_score

  from student join score

  on student.id=score.stu_id

  group by student.id

  having avg_score>90;

  5.排序

  sort by :排序,局部排序,只能保证单个reducer的结果排序。 order by: 排序,全局排序。保证整个job的结果排序。 当reducer只有1个的时候,sort by 和 order by 效果一样。建议使用sort by 通常和: desc asc .(默认升序)

  ##查询学生平均成绩按照降序排序

  select

  avg(grade) avg_score

  from

  student join score

  on student.id=score.stu_id

  group by student.id

  order by avg_score desc;

  设置reducer个数(等于1 或者 2):

  set mapreduce.job.reduces=2

  ##使用order by

  select

  avg(grade) avg_score

  from

  student join score

  on student.id=score.stu_id

  group by student.id

  order by avg_score desc;

  ##使用sort by

  select

  avg(grade) avg_score

  from

  student join score

  on student.id=score.stu_id

  group by student.id

  sort by avg_score desc;

  6.distribute by:

  控制map中如何输出到reduce。整个hive语句转换成job默认都有该过程,如果不写,默认使用第一列的hash值来分。当只有一个reducer的时候不能体现出来。如果distribute by和sort by 一起出现的时候注意顺序问题??distribute by在前面

  clusterd by : 它等价于distribute by和sort by(升序)。后面跟的字段名需要一样 clusterd by它既兼有distribute by,还兼有sort by (只能是升序)

  select

  id

  from

  student

  distribute by id

  sort by id;

  select

  id

  from

  student

  clusterd by id;

  7.limit : 限制结果集的。

  select

  id,name

  from student

  limit 3;

  8.union all:

  将两个或者多个查询的结果集合并到一起,不去重每一个结果集排序。字段数必须一致,字段类型尽量相同

  ##将id<108的和id>103的使用union all合并

  select

  id sid,

  name snames

  from student

  where id<108

  union all

  select

  id sid,

  name sname

  from student

  where id>103;

  9.union:

  将两个或者多个查询结果集合并到一起,去重,合并后的数据排序

  ##将id<108的和id>103的使用union合并

  select

  id sid,

  name sname

  from student

  where id<108

  union

  select

  id sid,

  name sname

  from student

  where id>103

  order by sname;

  10.子查询

  子查询是内嵌在另一个SQL 语句中的SELECT 语句。Hive 对子查询的支持很有限。它只允许子查询出现在SELECT 语句的FROM 子句中。Hive支持非相关子查询,这个子查询通过IN或EXISTS语法在WHERE子句中进行查询。Hive目前暂不支持相关子查询,相关子查询的执行依赖于外部查询的数据。

  ##非相关子查询

  select

  id,name

  from

  student

  where id

  in

  (select stu_id

  from

  score);

  ##相关子查询的执行依赖于外部查询的数据

  select sid,uname

  from

  (select

  id sid,

  name uname

  from student

  ) s

  order by sid

  ##外层查询像访问表那样访问子查询的结果,这是为什么必须为子查询赋予一个别名(s)的原因。子查询中的列必须有唯一的名称,以便外层查询可以引用这些列。

上一篇:HTML5工具初识之网页编辑器

下一篇:Cookie和Session的区别与使用

QQ技术交流群

千锋大数据官方①群
857910996

加入群聊