Hive 的表操作和查询

1,575 阅读20分钟

#博学谷IT学习技术支持#

Hive数据库和表操作

  数据库操作

    创建数据库

        ```
        create database if not exists myhive ; use  myhive ; 
        ```

说明:hive的表存放位置模式是由hive-site.xml当中的一个属性指定的

 < name > hive . metastore . warehouse . dir </ name >  <value>/user/ hive / warehouse </value> 

创建数据库并指定hdfs存储位置

create database myhive2 location '/myhive2' ; 

查看数据库详细信息

查看数据库基本信息

desc  database  myhive ; 

删除数据库

删除一个空数据库,如果数据库下面有数据表,那么就会报错

强制删除数据库,包含数据库下面的表一起删除

drop  database  myhive ; 

drop  database  myhive2  cascade;  

数据库表操作

  创建数据库表语法

-   ```
    CREATE  [EXTERNAL]  TABLE  [IF NOT EXISTS]  table_name 
        [(col_name data_type  [COMMENT col_comment ],   ...)]  
        [COMMENT table_comment ]  
        [ PARTITIONED BY  (col_name data_type  [COMMENT col_comment ],   ...)]  
        [ CLUSTERED BY  (col_name,  col_name,   ...)          [ SORTED BY  (col_name  [ASC|DESC],   ...)]  INTO num_buckets BUCKETS ]  
        [ROW FORMAT row_format ]  
        [ STORED AS file_format ]  
        [ LOCATION hdfs_path ] 
    ```

说明:

  1. CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。
  1. EXTERNAL关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION),Hive 创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
  1. LIKE允许用户复制现有的表结构,但是不复制数据。
  1. ROW FORMAT DELIMITED 可用来指定行分隔符
  1. STORED AS SEQUENCEFILE|TEXTFILE|RCFILE 来指定该表数据的存储格式,hive中,表的默认存储格式为TextFile。
  1. CLUSTERED BY

对于每一个表(table)进行分桶(MapReuce中的分区),桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

7、LOCATION: 指定表在HDFS上的存储位置。

Hive建表时候的字段类型

分类类型描述字面量示例
原始类型BOOLEANtrue/falseTRUE
TINYINT1字节的有符号整数 -128~1271Y
SMALLINT2个字节的有符号整数,-32768~327671S
INT4个字节的带符号整数(-2147483648~2147483647)1
BIGINT8字节带符号整数1L
FLOAT4字节单精度浮点数1.0
DOUBLE8字节双精度浮点数1.0
DEICIMAL任意精度的带符号小数1.0
STRING字符串,变长“a”,’b’
VARCHAR变长字符串“a”,’b’
CHAR固定长度字符串“a”,’b’
BINARY字节数组无法表示
TIMESTAMP时间戳,毫秒值精度122327493795
DATE日期‘2016-03-29’
Time时分秒‘12:35:46’
DateTime年月日 时分秒
复杂类型ARRAY有序的的同类型的集合["beijing","shanghai","tianjin","hangzhou"]
MAPkey-value,key必须为原始类型,value可以任意类型{"数学":80,"语文":89,"英语":95}
STRUCT字段集合,类型可以不同struct(‘1’,1,1.0)

内部表操作

  • 未被external修饰的是内部表(managed table),内部表又称管理表,内部表数据存储的位置由hive.metastore.warehouse.dir参数决定(默认:/user/hive/warehouse),删除内部表会直接删除元数据(metadata)及存储数据,因此内部表不适合和其他工具共享数据。
  hive建表初体验
create database myhive ; use myhive ; create table stu ( id int , name string ); insert into stu values  ( 1 , "zhangsan" ); select  *  from stu ; 
  创建表并指定字段之间的分隔符
-   ```
    create  table if not exists stu2 ( id int  , name string )  row format delimited fields terminated by '\t' ; 
    ```
根据查询结果创建表
create table stu3 as select  *  from stu2 ; 
根据已经存在的表结构创建表
create table stu4 like stu2 ; 
查询表的类型
desc formatted  stu2 ; 

  删除表
-   ```
    drop table stu2 ; 
    ```

查看数据库和HDFS,发现删除内部表之后,所有的内容全部删除

外部表操作

在创建表的时候可以指定external关键字创建外部表,外部表对应的文件存储在location指定的hdfs目录下,向该目录添加新文件的同时,该表也会读取到该文件(当然文件格式必须跟表定义的一致)。

  • 外部表因为是指定其他的hdfs路径的数据加载到表当中来,所以hive表会认为自己不完全独占这份数据,所以删除hive外部表的时候,数据仍然存放在hdfs当中,不会删掉。
      1.   数据装载载命令Load

Load命令用于将外部数据加载到Hive表中

语法:

load data  [local]  inpath '/export/data/datas/student.txt' [overwrite]  |  into table student  [partition  ( partcol1=val1 , … )]; 

参数:

  1. load data:表示加载数据
  1. local:表示从本地加载数据到hive表;否则从HDFS加载数据到hive表
  1. inpath:表示加载数据的路径
  1. overwrite:表示覆盖表中已有数据,否则表示追加
  1. into table:表示加载到哪张表
  1. student:表示具体的表
  1. partition:表示上传到指定分区
  操作案例

分别创建老师与学生表外部表,并向表中加载数据

源数据如下:

student.txt

01 赵雷 1990-01-01  
02 钱电 1990-12-21  
03 孙风 1990-05-20  
04 李云 1990-08-06  
05 周梅 1991-12-01  
06 吴兰 1992-03-01  
07 郑竹 1989-07-01  
08 王菊 1990-01-20  

teacher.txt

01 张三 
02 李四 
03 王五 
1、创建老师表:
create external table teacher  ( tid string , tname string )  row format delimited fields terminated by '\t' ; 
2、创建学生表:
create external table student  ( sid string , sname string , sbirth string  ,  ssex string  )  row format delimited fields terminated by '\t' ; 
3、从本地文件系统向表中加载数据
load data local inpath '/export/data/hivedatas/student.txt' into table student ; 
4、加载数据并覆盖已有数据
load data local inpath '/export/data/hivedatas/student.txt' overwrite  into table student ; 
5、从hdfs文件系统向表中加载数据

其实就是一个移动文件的操作

需要提前将数据上传到hdfs文件系统,

hadoop fs -mkdir -p  / hivedatas
cd  / export / data / hivedatas
hadoop fs -put teacher .txt  / hivedatas / load data inpath '/hivedatas/teacher.txt' into table teacher ; 

注意,如果删掉teacher表,hdfs的数据仍然存在,并且重新创建表之后,表中就直接存在数据了,因为我们的student表使用的是外部表,drop table之后,表当中的数据依然保留在hdfs上面了

复杂类型操作

  Array类型

Array是数组类型,Array中存放相同类型的数据

源数据:

说明:name与locations之间制表符分隔,locations中元素之间逗号分隔

zhangsan  beijing,shanghai,tianjin,hangzhou
wangwu   changchun,chengdu,wuhan,beijing

建表语句

create external table hive_array ( name string ,  work_locations array< string >) row format delimited fields terminated by '\t'
collection items terminated by  ',' ; 

导入数据(从本地导入,同样支持从HDFS导入)

load data local inpath '/export/data/hivedatas/work_locations.txt' overwrite into table hive_array ; 

常用查询:

-- 查询所有数据
select  *  from hive_array ; -- 查询work_locations数组中第一个元素
select name ,  work_locations [ 0 ]  location from hive_array ; -- 查询location数组中元素的个数
select name ,  size( work_locations )  location_size from hive_array ; -- 查询location数组中包含tianjin的信息
select  *  from hive_array where array_contains ( work_locations , 'tianjin' );  
  map类型

map就是描述key-value数据

源数据:

说明:字段与字段分隔符: “,”;需要map字段之间的分隔符:"#";map内部k-v分隔符:":"

1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26

建表语句

create table hive_map ( id int ,  name string ,  members map < string , string >,  age int  ) row format delimited
fields terminated by ','
collection items terminated by  '#' 
map keys terminated by  ':' ;  

导入数据

load data local inpath '/export/data/hivedatas/hive_map.txt' overwrite into table hive_map ; 

常用查询

select  *  from hive_map ; #根据键找对应的值
select id ,  name ,  members [ 'father' ]  father ,  members [ 'mother' ]  mother ,  age from hive_map ; #获取所有的键
select id ,  name ,  map_keys ( members )  as relation from hive_map ; #获取所有的值
select id ,  name ,  map_values ( members )  as relation from hive_map ; #获取键值对个数
select id , name ,size( members )  num from hive_map ; #获取有指定key的数据
select  *  from hive_map where array_contains ( map_keys ( members ),  'brother' ); #查找包含brother这个键的数据,并获取brother键对应的值
select id , name ,  members [ 'brother' ]  brother from hive_map where array_contains ( map_keys ( members ),  'brother' ); 
  struct类型

源数据:

说明:字段之间#分割,第二个字段之间冒号分割

192.168.1.1#zhangsan : 40
192.168.1.2#lisi : 50
192.168.1.3#wangwu : 60
192.168.1.4#zhaoliu : 70

建表语句

create table hive_struct ( ip string ,  info struct < name : string ,  age : int >  ) row format delimited
fields terminated by '#'
collection items terminated by ':' ; 

导入数据

load data local inpath '/export/data/hivedatas/hive_struct.txt' into table hive_struct ; 

常用查询

select  *  from hive_struct ; #根据struct来获取指定的成员的值
select ip ,  info . name from hive_struct ; 

分区表:

分区不是独立的表模型,要和内部表或者外部表结合:

内部分区表

  • 外部分区表
  基本操作

在大数据中,最常用的一种思想就是分治,分区表实际就是对应hdfs文件系统上的的独立的文件夹,该文件夹下是该分区所有数据文件。

分区可以理解为分类,通过分类把不同类型的数据放到不同的目录下。

分类的标准就是分区字段,可以一个,也可以多个。

分区表的意义在于优化查询。查询时尽量利用分区字段。如果不使用分区字段,就会全部扫描。

在查询是通过where子句查询来指定所需的分区。

在hive中,分区就是分文件夹

创建分区表语法

create table score ( sid string , cid string ,  sscore int )  partitioned by  (month string )  row format delimited fields terminated by '\t' ; 

创建一个表带多个分区

create table score2  ( sid string , cid string ,  sscore int )  partitioned by  (year string ,month string ,day string )  row format delimited fields terminated by '\t' ; 

加载数据到分区表中

load data local inpath '/export/data/hivedatas/score.txt' into table score partition  (month='202006' ); 

加载数据到一个多分区的表中去

load data local inpath '/export/data/hivedatas/score.txt' into table score2 partition(year='2020' ,month='06' ,day='01' ); 

多分区联合查询使用union all来实现

select  *  from score where month = '202006' union all select  *  from score where month = '202007' ; 

查看分区

show  partitions  score ; 

添加一个分区

alter table score add partition(month='202008' ); 

同时添加多个分区

alter table score add partition(month='202009' )  partition(month = '202010' ); 

注意:添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹

删除分区

alter table score drop partition(month = '202010' ); 

分桶表

  • 分桶就是将数据划分到不同的文件,其实就是MapReduce的分区
  基本操作

将数据按照指定的字段进行分成多个桶中去,说白了就是将数据按照字段进行划分,可以将数据按照字段划分到多个文件当中去

开启hive的桶表功能(如果执行该命令报错,表示这个版本的Hive已经自动开启了分桶功能,则直接进行下一步)

set hive . enforce . bucketing=true ; 

设置reduce的个数

set mapreduce . job . reduces=3 ;    #该参数在Hive2.x版本之后不起作用

创建分桶表

create table course  ( cid string , c_name string , tid string )  clustered by( cid )  into 3 buckets row format delimited fields terminated by '\t' ; 

桶表的数据加载,由于桶表的数据加载通过hdfs dfs -put文件或者通过load data均不好使,只能通过insert overwrite

创建普通表,并通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去

创建普通表:

create table course_common  ( cid string , c_name string , tid string )  row format delimited fields terminated by '\t' ; 

普通表中加载数据

load data local inpath '/export/data/hivedatas/course.txt' into table course_common ; 

通过insert overwrite给桶表中加载数据

insert overwrite table course select  *  from course_common cluster by( cid ); 

修改表

  表重命名

基本语法:

alter table old_table_name rename to new_table_name ;

-- 把表score3修改成score4
alter table score3 rename to score4 ; 
  增加/修改列信息
-   ```
    -- 1:查询表结构
    desc score4 ; -- 2:添加列
    alter table score4 add columns  ( mycolx string ,  myscoy string ); -- 3:查询表结构
    desc score4 ; -- 4:更新列
    alter table score4 change column myscox mysconew int ; -- 5:查询表结构
    desc score4 ; 
    ```
  删除表
-   ```
    drop table score4 ; 
    ```
  清空表数据

只能清空管理表,也就是内部表

truncate table score4 ; 

hive表中加载数据

-   1.  #####   直接向分区表中插入数据

通过insert into方式加载数据

create table score3 like score ; insert into table score3 partition(month ='202007' )  values  ( '001' , '002' , 100 ); 

通过查询方式加载数据

create table score4 like score ; insert overwrite table score4 partition(month = '202006' )  select sid , cid , sscore from score ; 
  通过查询插入数据

通过load方式加载数据

create table score5 like score ; load data local inpath '/export/data/hivedatas/score.txt' overwrite into table score5 partition(month='202006' ); 
多插入模式

常用于实际生产环境当中,将一张表拆开成两部分或者多部分

给score表加载数据

load data local inpath '/export/data/hivedatas/score.txt' overwrite into table score partition(month='202006' ); 

创建第一部分表:

create table score_first (  sid string , cid  string )  partitioned by  (month string )  row format delimited fields terminated by '\t'  ; 

创建第二部分表:

create table score_second ( cid string , sscore int )  partitioned by  (month string )  row format delimited fields terminated by '\t' ; 

分别给第一部分与第二部分表加载数据

from score insert overwrite table score_first partition(month='202006' )  select sid , cid insert overwrite table score_second partition(month = '202006' )   select cid , sscore ; 
查询语句中创建表并加载数据(as select)

将查询的结果保存到一张表当中去

create table score5 as select  *  from score ; 
创建表时通过location指定加载数据路径
  1. 创建表,并指定在hdfs上的位置
create external table score6  ( sid string , cid string , sscore int )  row format delimited fields terminated by '\t' location '/myscore6' ; 
  1. 上传数据到hdfs上
hadoop fs -mkdir -p  / myscore6
hadoop fs -put score .txt/ myscore6 ; 
  1. 查询数据
select  *  from score6 ; 

hive表中的数据导出

  • 将hive表中的数据导出到其他任意目录,例如linux本地磁盘,例如hdfs,例如mysql等等
  insert导出

1)将查询的结果导出到本地

insert overwrite local directory '/export/data/exporthive' select  *  from score ; 
  1. 将查询的结果格式化导出到本地
insert overwrite local directory '/export/data/exporthive' row format delimited fields terminated by '\t' select  *  from student ; 
  1. 将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/exporthive' row format delimited fields terminated by '\t'  select  *  from score ; 
  hive shell 命令导出

基本语法:(hive -f/-e 执行语句或者脚本 > file)

hive -e "select * from myhive.score;"  >   / export / data / exporthive / score . txt
      1.   export导出到HDFS上
    • export table score to '/export/exporthive/score' ; 
      

hive查询语法

  SELECT语句

    语句结构

基本语法:

SELECT  [ALL  |  DISTINCT] select_expr ,  select_expr ,   ... FROM table_reference
 [WHERE where_condition ]  [GROUP BY col_list ]  [HAVING where_condition ]  [ORDER BY col_list ]  [CLUSTER BY col_list
   |   [ DISTRIBUTE BY col_list ]   [ SORT BY col_list ]  ]  [LIMIT number] 

解释:

1、ORDER BY用于全局排序,就是对指定的所有排序键进行全局排序,使用ORDER BY的查询语句,最后会用一个Reduce Task来完成全局排序。

2、sort by用于分区内排序,即每个Reduce任务内排序 ,则sort by只保证每个reducer的输出有序,不保证全局有序。

3、distribute by( 字段)根据指定的字段将数据分到不同的reducer,且分发算法是hash散列。

4、cluster by( 字段) 除了具有Distribute by的功能外,还兼具sort by的排序功能。。

因此,如果distribute bysort by字段是同一个时,此时,cluster by = distribute by + sort by

  1. 全表查询

select  *  from score  ; 
  1. 选择特定列查询

select sid  , cid from score ; 
  1. 列别名

select sid as myid  , cid from score ; 
  1. 常用函数

1)求总行数(count)
 select count( 1 )  from score ; 2)求分数的最大值(max)
 select max( sscore )  from score ; 3)求分数的最小值(min)
 select min( sscore )  from score ; 4)求分数的总和(sum)
 select sum( sscore )  from score ; 5)求分数的平均值(avg)
 select avg( sscore )  from score ; 
  1. LIMIT语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

select  *  from score limit 3 ; select  *  from score limit 3,5 ; #从索引为3的行开始,显示5行
  1. WHERE语句

1)使用WHERE 子句,将不满足条件的行过滤掉。

2)WHERE 子句紧随 FROM 子句。

3)案例实操

查询出分数大于60的数据

select  *  from score where sscore  >  60 ; 

  运算符

比较运算符

1、操作符

操作符支持的数据类型描述
A=B基本数据类型如果A等于B则返回TRUE,反之返回FALSE
A<=>B基本数据类型如果A和B都为NULL,则返回TRUE,其他的和等号(=)操作符的结果一致,如果任一为NULL则结果为NULL
A<>B, A!=B基本数据类型A或者B为NULL则返回NULL;如果A不等于B,则返回TRUE,反之返回FALSE
A<B基本数据类型A或者B为NULL,则返回NULL;如果A小于B,则返回TRUE,反之返回FALSE
A<=B基本数据类型A或者B为NULL,则返回NULL;如果A小于等于B,则返回TRUE,反之返回FALSE
A>B基本数据类型A或者B为NULL,则返回NULL;如果A大于B,则返回TRUE,反之返回FALSE
A>=B基本数据类型A或者B为NULL,则返回NULL;如果A大于等于B,则返回TRUE,反之返回FALSE
A [NOT] BETWEEN B AND C基本数据类型如果A,B或者C任一为NULL,则结果为NULL。如果A的值大于等于B而且小于或等于C,则结果为TRUE,反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL所有数据类型如果A等于NULL,则返回TRUE,反之返回FALSE
A IS NOT NULL所有数据类型如果A不等于NULL,则返回TRUE,反之返回FALSE
IN(数值1, 数值2)所有数据类型使用 IN运算显示列表中的值
A [NOT] LIKE BSTRING 类型B是一个SQL下的简单正则表达式,如果A与其匹配的话,则返回TRUE;反之返回FALSE。B的表达式说明如下:‘x%’表示A必须以字母‘x’开头,‘%x’表示A必须以字母’x’结尾,而‘%x%’表示A包含有字母’x’,可以位于开头,结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP BSTRING 类型B是一个正则表达式,如果A与其匹配,则返回TRUE;反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的,因为正则也依据其中的规则。例如,正则表达式必须和整个字符串A相匹配,而不是只需与其字符串匹配。

2、案例实操

1)查询分数等于80的所有的数据
select  *  from score where sscore = 80 ; (2)查询分数在80100的所有数据
select  *  from score where sscore between 80 and 100 ; (3)查询成绩为空的所有数据
select  *  from score where sscore is null; (4)查询成绩是8090的数据
select  *  from score where sscore in( 80 , 90 ); 

3、LIKE和RLIKE

1)使用LIKE运算选择类似的值

2)选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)。

_ 代表一个字符。

3)RLIKE子句是Hive中这个功能的一个扩展,其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

4)案例实操

1)查找以8开头的所有成绩
select  *  from score where sscore like '8%' ; (2)查找第二个数值为9的所有成绩数据
select  *  from score where sscore like '_9%' ; (3)查找id中含1的所有成绩信息
select  *  from score where sid rlike '[1]' ; 
逻辑运算符
操作符含义
AND逻辑并
OR逻辑或
NOT逻辑否

案例实操

1)查询成绩大于80,并且sid是01的数据
select  *  from score where sscore  > 80 and sid = '01' ; (2)查询成绩大于80,或者sid  是01的数
select  *  from score where sscore  >  80 or sid = '01' ; (3)查询sid  不是 0102的学生
select  *  from score where sid not in  ( '01' , '02' ); 

  分组

  1. GROUP BY语句

GROUP BY语句通常会和聚合函数一起使用,按照一个或者多个列队结果进行分组,然后对每个组执行聚合操作。注意使用group by分组之后,select后面的字段只能是分组字段和聚合函数。

案例实操:

  1. 计算每个学生的平均分数
select sid  ,avg( sscore )  from score group by sid ; 
  1. 计算每个学生最高成绩
select sid  ,max( sscore )  from score group by sid ; 
HAVING语句

1、having与where不同点

(1)where针对表中的列发挥作用,查询数据;having针对查询结果中的列发挥作用,筛选数据。

(2)where后面不能写分组函数,而having后面可以使用分组函数。

(3)having只用于group by分组统计语句。

2、案例实操:

-- 求每个学生的平均分数
select sid  ,avg( sscore )  from score group by sid ; -- 求每个学生平均分数大于85的人
select sid  ,avg( sscore )  avgscore from score group by sid having avgscore  >  85 ; 

  JOIN语句

Hive的join操作只支持等值连接

  1. 内连接(INNER JOIN)

内连接:只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

select * from teacher t, course c where t.tid = c.tid; #隐式内连接 select  *  from teacher t inner join course c on t . tid = c . tid ; #显式内连接 select  *  from teacher t join course c on t . tid = c . tid ; 
  1. 左外连接(LEFT OUTER JOIN)

左外连接:JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

查询老师对应的课程

select  *  from teacher t left join course c on t . tid = c . tid ; 
  1. 右外连接(RIGHT OUTER JOIN)

右外连接:JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

select  *  from teacher t right join course c on t . tid = c . tid ; 
  1. 满外连接(FULL OUTER JOIN)

满外连接:将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话,那么就使用NULL值替代。

SELECT  *  FROM teacher t FULL JOIN course c ON t . tid = c . tid  ; 
  1. 多表连接

注意:连接 n个表,至少需要n-1个连接条件。例如:连接三个表,至少需要两个连接条件。

多表连接查询,查询老师对应的课程,以及对应的分数,对应的学生

select  *  from teacher t 
left join course c 
on t . tid = c . tid
left join score s 
on s . cid = c . cid
left join student stu 
on s . sid = stu . sid ; 
  • 大多数情况下,Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表teacher和表course进行连接操作,然后会再启动一个MapReduce job将第一个MapReduce job的输出和表score;进行连接操作。

  排序

    Order By-全局排序

Order By:全局排序,一个reduce

1、使用 ORDER BY 子句排序

ASC(ascend): 升序(默认)

DESC(descend): 降序

2、ORDER BY 子句在SELECT语句的结尾。

3、案例实操

(1)查询学生的成绩,并按照分数降序排列

SELECT  *  FROM student s LEFT JOIN score sco ON s . sid = sco . sid ORDER BY sco . sscore DESC; 

(2)按照分数的平均值排序

select sid  ,avg( sscore )  avg from score group by sid order by avg; 

(3)按照学生id和平均成绩进行排序

select sid  ,avg( sscore )  avg from score group by sid order by sid ,avg; 
  1. Sort By-每个MapReduce内部局部排序

Sort By:每个MapReduce内部进行排序,对全局结果集来说不是排序。

1 ) 设置reduce个数
 set mapreduce . job . reduces=3 ; 2)查询成绩按照成绩降序排列
 select  *  from score sort by sscore ; 3 ) 将查询结果导入到文件中(按照成绩降序排列)
 insert overwrite local directory '/export/data/exporthive/sort' select  *  from score sort by sscore ; 
  1. Distribute By-分区排序

Distribute By:类似MR中partition,进行分区,结合sort by使用。

注意,Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

对于distribute by进行测试,一定要分配多reduce进行处理,否则无法看到distribute by的效果。

案例实操:

先按照学生id进行分,再按照学生成绩进行排序。

1 ) 设置reduce的个数,将我们对应的sid划分到对应的reduce当中去
set mapreduce . job . reduces=7 ; 2 ) 通过distribute by进行数据的分区 insert overwrite local directory '/export/data/exporthive/distribute' select  *  from score distribute by sid sort by sscore ; 
  1. Cluster By

当distribute by和sort by字段相同时,可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序,不能指定排序规则为ASC或者DESC。

以下两种写法等价:

select  *  from score cluster by sid ;  
select  *  from score distribute by sid sort by sid ;