常用的Hive表命令

1. groupby

将表数据通过某一列或者多个列进行聚合。

• example 1
  给定一个表grade_table如下表结构

uid	name	class	grade
0001	张三	学前一班	0
0002	李四	学前二班	3
0003	王五	学前二班	12
0004	赵六	学前一班	13

这种情况下想要计算每个班的平均分，对应的sql如下:

select class, avg(grade) as grade from grade_table group by class

如果需要将某个人，所有的分数计算平均分并且展示学号和姓名的话，sql如下

select uid, name avg(grade) as grade from grade_table group by uid, name

2. collect_set

将表数据中的某一个列进行转换，列转行

• example 2
  依旧是给定的班级分数的例子

uid	name	class	grade
0001	张三	学前一班	0
0002	李四	学前二班	3
0003	王五	学前二班	12
0004	赵六	学前一班	13

如果需要将一个班级里面的所有用户学生聚合到一个行中，如何进行操作?

select collect_set(uid) as uid, collect_set(name) as name from grade_table group by class

3. array_contains

用来判断一个集合内部是否具有某个元素。

• example 3
  还是规定的上述表格中的数据，我们希望得到包含了张三的班级的所有人的姓名和学号。

uid	name	class	grade
0001	张三	学前一班	0
0002	李四	学前二班	3
0003	王五	学前二班	12
0004	赵六	学前一班	13

这种情况下有两种处理逻辑：

1. 先判断张三属于哪个班级，然后按照班级做group by得到数据。
2. 先按照班级进行group by，然后判断姓名里面是否包含张三

为了展示array_contains的使用，我们这里不考虑如何使用第一种逻辑，使用第二种逻辑进行处理。

select uid, name  
    from (select  collect_set(uid) as uid, collect_set(name) as name  
        from grade_table  
        group by  class) a  
    where array_contains(name, "张三")

4. if

判断并且赋值

• example 4

当然，还是给定的上述例子，如果需要给学前一班的同学赋值为富二代，其他的赋值为穷逼，在sql中需要怎么样进行处理呢？

uid	name	class	grade
0001	张三	学前一班	0
0002	李四	学前二班	3
0003	王五	学前二班	12
0004	赵六	学前一班	13

select uid, name, if(class="学前一班","富二代","穷逼") as flag from grade_table

5.substr

对字符串数据取字符子串

• example 5

当然还是上述例子。

uid	name	class	grade
0001	张三	学前一班	0
0002	李四	学前二班	3
0003	王五	学前二班	12
0004	赵六	学前一班	13

我们需要挑选出所有姓李的学生，这个时候对应的sql如下。

select uid, name from grade_table where substr(name, 0,1)="李"

6. unix_timestamp()和from_unixtime()

功能： 实现hive表数据格式的转换

• example 6

例如在，如下给定的hive表grade_table中，hive表按照时间分区，分区字段为dt。我们需要将每天数据所属的分区时间字段以"yyyy-mm-dd"格式作为新的一列，列名为datetimes

uid	name	class	grade	dt
0001	张三	学前一班	0	20201025
0002	李四	学前二班	3	20201026
0003	王五	学前二班	12	20201027
0004	赵六	学前一班	13	20201025

select from_unixtime(unix_timestamp(dt, 'yyyymmdd'), 'yyyy-mm-dd') as datetimes from grade_table

7. join

功能: 两表联合查询

• example 7

例如给出两种表grade_table和student_table，我们需要联合查询学生的成绩信息和学生的基本信息。
两张表的表结构分别对应如下。

• grade_table 表的结构如下

uid	name	class	grade	dt
0001	张三	学前一班	0	20201025
0002	李四	学前二班	3	20201026
0003	王五	学前二班	12	20201027
0004	赵六	学前一班	13	20201025

• student_table 表的结构如下

uid	name	age	father
0001	张三	62	王三麻
0002	李四	34	李四多
0003	王五	25	王五趣
0004	赵六	12	赵六斧

那么对应联合查询的sql如下

select a.uid, a.name, b.age, b.father, a.class, a.grade      
    from (select   uid, name, class, grade          
        from grade_table) a 
    join (
            select uid, name, age, father 
            from student_table
        ) b 
            on a.uid=b.uid 
            and a.name=b.namem 

8. datediff

计算两个时间之间的天数间隔，默认时间格式“yyyy-mm-dd”