Hive入门笔记

感谢B站Up主 尚硅谷 提供的视频教程和相关笔记

1. 基本概念

1.1 什么是HIVE

• HIVE简介：HIVE是由Facebook开源的用于处理海量结构化日志的数据统计工具。

  HIVE是基于Hadoop的一个数据仓库工具，可以将结构化数据文件映射成一张表，并提供类SQL的查询功能。

• HIVE本质：将HQL转换为MapReduce程序

  • HIVE数据存储在HDFS上
  • HIVE分析数据的底层实现是MapReduce
  • 执行程序运行在Yarn上

1.2 HIVE的优缺点

• 优点
  • 操作接口提供类SQL的语法，适合快速开发的上手
  • 免去写MapReduce，降低开发人员的学习成本
  • HIVE执行延迟比较高，因此适合于数据分析等对实时性要求不高的场合
  • HIVE优势在处理海量数据，对于小数据量的处理不具有优势
  • HIVE支持用户自定义函数，用户可以根据自己的需求来实现自己的函数
• 缺点
  • HIVE的HQL表达能力有限
    • 迭代式算无法表达
    • 数据挖掘方面不擅长，由于MapReduce的数据处理流程限制，无法实现更高效率的算法
  • HIVE的效率比较低
    • HIVE自动生成MapReduce作业，通常情况不够智能化
    • HIVE调优比较困难，粒度较粗

1.3 HIVE架构原理

• 用户接口：Client

  CLI（Command Line Interface）、JDBC/ODBC(JAVA 访问 HIVE)、WEBUI（浏览器访问HIVE）

• 元数据：Meta Store

  元数据包括：表名，表所属的数据库、表的拥有者、列、分区字段、表的类型（外部表，内部表）、表数据所在的目录等。

• Hadoop

  使用HDFS进行存储，使用MapReduce进行计算

• 驱动器：Driver

  • 解析器：将SQL字符串转换为抽象语法树AST，这一步一般在第三方工具库完成，比如antlr。对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
  • 编译器：将AST编译生成逻辑执行计划
  • 优化器：对执行逻辑进行优化
  • 执行器：将逻辑执行计划转换为可以运行的物理计划，对于HIVE而言，就是MR/SPARK

HIVE通过给用户提供一系列的交互接口，接收到用户的指令SQL，使用自己的Driver，结合元数据，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行结果输出到用户交互接口。

1.4 HIVE与数据库比较

• 查询语言

  由于SQL被广泛应用于数据仓库中，因此专门针对HIVE特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用HIVE进行开发。

• 数据更新

  由于HIVE是针对数据仓库进行设计的，因此数据仓库的内容是读多写少。因此，在HIVE中不建议对数据的改写，所有数据在加载的时候确定好的。而常规的数据库是经常需要进行增删改查的。

• 执行延迟

  HIVE在执行的时候，由于没有索引，需要扫描整个表，因此延迟比较高。另外由于MapReduce框架的原因，HIVE的执行速度也会有较高延迟。数据量在一定范围内时，常规数据库效率更高。

• 数据规模

  HIVE可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。

2. HIVE数据类型

2.1 基本数据类型

2.2 集合数据类型

三种数据类型中，Map和Array对应Java中的Map和Array，Struct和C语言中Struct类似，封装了一个命名字段的集合，复杂数据类型且允许任意层次的嵌套。

数据案例：

{
"name": "songsong",
"friends": ["bingbing" , "lili"] , //列表 Array,
"children": { //map
	"xiao song": 18 ,
	"xiaoxiao song": 19
	}
"address": { //struct
	"street": "hui long guan",
	"city": "beijing"
	}
}

建表案例：

create table test( 
    name string,
	children map<string, int>,
	address struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

字段解释：

row format delimited fields terminated by ‘,’ 列分隔符

collection items terminated by ‘_’ MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)

map keys terminated by ‘:’ MAP 中的 key 与 value 的分隔符

lines terminated by ‘\n’; 行分隔符

2.3 类型转换

HIVE类型转换类似JAVA类型转换，可以从低精度转换到高精度。如TINYINT转INT可以，但是INT转TINYINT就会出现异常。

• 隐式类型转换规则如下

  • 任何整数类型都可以 隐式的转换为一个范围更广的类型。
  • 所有整数类型，Float类型和String类型都可以隐式的转换为DOUBLE
  • TINTINT,SMALLINT、INT都可以转换为Float
  • Boolean类型不允许转换为其他类型

• 可以使用CAST进行强制类型转换，CAST(‘X’ AS INT),如果转换失败，则返回空值NULL。

3. DDL数据定义

3.1 创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name [COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

• 创建一个数据库，数据库在HDFS上默认的存储路径是/user/hive/warehouse/*.db

• 为了避免创建数据库时出现库已经存在的错误，需要用if not exists判断。

  create database	if not exists db_hive;
  

• 创建一个数据库，指定数据在HDFS上存放的位置

  create database if not exists db_hive location '/db_hive2.db';
  

3.2 查询数据库

• 显示数据库

  show databases;
  

• 根据过滤条件查询数据库

  show database like 'db_hi*';
  

• 查看数据库详情

  • 显示数据库信息

    desc database db_hive;
    

  • 显示数据库详细信息

    desc database extended db_hive;
    

• 切换数据库

  use db_hive;
  

3.3 修改数据库

用户可以使用ALTER DATABASE 命令为某个数据库的DBPRIORITIES设置键值对属性值，来描述这个数据库的属性信息。

alter database db_hive set dbproperties('createtime'='20210330');

3.4 删除数据库

• 删除空数据库

  drop database db_hive;
  

• 如果待删除的数据库可能不存在，最好使用if exists进行判断

  drop database if exists db_hive;
  

• 如果数据库不为空，可以使用cascade命令强制删除

  drop database db_hive cascade;
  

3.5 创建表

• 建表语法

  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]
  [TBLPROPERTIES (property_name=property_value, ...)] 
  [AS select_statement]
  

• 字段说明

  字段	说明
  CREATE TABLE	创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常； 用户可以用 IF NOT EXISTS 选项来忽略这个异常
  EXTERNAL	让用户创建一个外部表，在建表的同时可以指定一个指向实 际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
  COMMENT	为表和列添加注释
  PARTITIONED BY	创建分区表
  CLUSTERED BY	创建分桶表
  SORTED BY	不常用，对桶中的一个或多个列另外排序
  ROW FORMAT	DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
  STORED AS	指定存储文件类型 常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
  LOCATION	指定表在 HDFS 上的存储位置
  AS	后跟查询语句，根据查询结果创建表。
  LIKE	允许用户复制现有的表结构，但是不复制数据。

• 管理表(外部表)

  • 定义：默认创建的表都是管理表，也叫内部表。这种表，HIVE会控制数据的生命周期。当HIVE在删除内部表的时候，其中数据也会一并被删除。内部表不适合与其他工具共享。

  • 操作：

    • 创建内部表

      create table if not exists student(
          id int, 
          name string
      )
      row format delimited fields terminated by '\t' stored as textfile
      location '/user/hive/warehouse/student';
      

    • 根据查询结果创建表(查询结果会添加到新创建的表中)

      create table if not exists student2 as select id,name from student;
      

    • 根据已经存在的表创建

      create table if not exists student2 like student;
      

    • 查询表的类型

      desc formatted student2;
      

• 内部表

  • 定义：外部表，HIVE并不拥有对应的数据。删除外部表只是删除表的meta元数据信息，不会删除表中的数据。

  • 内部表和外部表的使用场景：

    每天收集到的网站日志文件定期流入HDFS文件。在外部表的基础上做大量的统计分析，用到的中间表、结果表使用内部表进行存储，数据通过select+insert插入内部表。

  • 建表：

    create external table if not exists emp(
        empno int,
    	ename string, 
        job string, 
        mgr int,
    	hiredate string, 
        sal double,
    	comm double, 
        deptno int
    )
    row format delimited fields terminated by '\t';
    

• 内部表与外部表的转换

  • 修改内部表为外部表

    alter table student2 set tblproperties('EXTERNAL'='TRUE');
    

  • 修改外部表为内部表

    alter table student2 set tblproperties('EXTERNAL'='FALSE');
    

3.6 修改表

• 重命名表

  alter table student2 RENAME TO student;
  

• 增加/修改/替换列的信息

  • 更新列

    ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
    

  • 增加/替换列

    ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
    

    注： ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)，REPLACE 则是表示替换表中所有字段

  • 实操：

    • 添加列

      alter table dept add columns(deptdesc string);
      

    • 更新列

      alter table dept change column deptdesc desc string;
      

    • 替换列

      alter table dept replace columns(deptno string, dname string, loc string);
      

3.7 删除表

drop table student2;

4. DML数据操作

4.1 数据导入

• 向表中装载数据（Load）

  load data [local] inpath '数据的 path' [overwrite] into table student [partition (partcol1=val1,…)];
  

  • load data:表示加载数据
  • local:表示从本地加载数据到HIVE表，否则从HDFS加载数据到HIVE表
  • inpath:表示加载数据的路径
  • overwrite：表示覆盖表中已有的数据，否则表示追加
  • into table: 表示加载数据到那一张表
  • student：具体的表名
  • partition：表示上传到指定的分区

• 通过查询语句向表中导入数据（Insert）

  • 创建一张表

    create table student_par(id int, name string) row format delimited fields terminated by '\t';
    

  • 基本插入

    insert into table student_par values(1,'wangwu'),(2,'zhaoliu');
    

  • 基本模式插入

    insert overwrite table student_par select id, name from student where month='201709';
    

    注意：

    • insert into表示追加插入，原有数据仍然保留
    • insert overwrite表示覆盖插入，原有数据会被覆盖
    • insert不支持插入部分字段

  • 多表（多分区插入模式）

    from student
    insert overwrite table student partition(month='201707') 
    select id, name where month='201709'
    insert overwrite table student partition(month='201706') 
    select id, name where month='201709';
    

• 查询语句中创建表并插入数据

  create table if not exists student3 as select id, name from student;
  

• 创建表时通过location指定加载数据的路径

  create external table if not exists student5(
      id int, 
      name string
  )
  row format delimited fields terminated by '\t' 
  location '/student;
  

  • 查询数据

    select * from student5; 
    

• import数据到指定的HIVE表

  import table student2 from '/user/hive/warehouse/export/student';
  # 注意，import的前提是先执行了export
  

4.2 数据导出

• Insert导出

  • 将查询结果导出到本地

    insert overwrite local directory '/opt/module/hive/data/export/student' select * from student;
    

  • 将查询的结果格式化导出到本地

    insert overwrite local directory '/opt/module/hive/data/export/student1' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    select * from student;
    

  • 将查询结果导出到HDFS(没有local关键字)

    insert overwrite directory '/user/atguigu/student2' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    select * from student;
    

• Hadoop命令导出

  dfs -get /user/hive/warehouse/student/student.txt /opt/module/data/export/student3.txt;
  

• Hive Shell导出

  bin/hive -e 'select * from default.student;' > /opt/module/hive/data/export/student4.txt;
  

• Export

  export table default.student to ...
  

  export和import主要用于两个Hadoop平台集群之间迁移Hive表

4.3数据删除

# 该操作只能删除内部表中的数据
truncate table student

5. 查询

5.1 基本查询

• 全表查询

  select * from emp;
  

• 特定列查询

  select empno,ename from emp;
  

• 列别名：

  重命名一个列，便于计算和使用。重命名方式：别名紧跟列名，也可以在两者之间加入关键字AS

• 算术运算符

• 常用函数

  • 求总行数

    select count(*) from emp;
    

  • 求某列最大值

    select max(sal) from emp;
    

  • 求某列最小值

    select min(sal) from emp;
    

  • 求某列总和

    select sum(sal) from emp;
    

  • 求平均值

    select avg(sal) from emp;
    

• Limit：用于限制返回的行数

  select * from emp limit 5;
  

• Where语句

  select * from emp where sal > 1000;
  # 注意，where子句的字段不能使用别名
  

• 比较运算符：Between，in，is null，is not null，>,<,!=,=

• Like和RLIKE

  %表示匹配0个或者多个字符

  _表示匹配一个字符

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

  案例

  • 查找以A开头的员工信息

  select * from emp where ename like 'A%'
  

  • 查找名字中第二个字母为A的员工

    select * from emp where ename LIKE '_A%';
    

  • 查找名字中带A的员工信息

    select * from emp where ename RLIKE '[A]'
    

• 逻辑运算符 AND，OR，NOT

5.2 分组

• Group By语句

  Group By语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个分组执行聚合操作。

  # 查询每个部门的平均工资
  select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
  

• Having语句

  Having与Where的区别：

  • Where后面不能写分组函数，而having后面可以使用分组函数
  • having只用于group by分组统计语句
  • Where执行顺序在聚合操作之前，Having执行顺序在聚合操作之后。

  # 查出平均薪水大于2000的部门
  select depno,avg(sal) as avg_sal from emp group by depno having avg_sal>2000;
  

• Join语句

  select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;
  

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

    select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
    

  • 左外连接：JOIN操作符左边的表中符合where子句的所有记录都会返回

    select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
    

  • 右外连接：JOIN操作中右边的表中符合where子句的所有记录都被返回

    select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
    

  • 满外连接：会返回两个表中满足where子句的所有记录，无匹配项则的项目则为null

    select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;
    

  • 多表连接 : 连接 n 个表， 至少需要 n-1 个连接条件。 例如： 连接三个表， 至少需要两个连接条件

    SELECT e.ename, d.dname, l.loc_name
    FROM emp e
    JOIN dept d
    ON d.deptno = e.deptno
    JOIN location l
    ON d.loc = l.loc;
    

    大多数情况下， Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。 本例中会首先启动一个 MapReduce job 对表 e 和表 d 进行连接操作，然后会再启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 l;进行连接操作。

    Hive总是从左往右执行，当每个on子句中使用相同的连接键时，只会产生一个MapReduce Job。

• 笛卡尔积

  产生条件：省略连接条件，连接条件无效，所有表中所有行互相连接

  select empno, dname from emp, dept;
  

5.3 排序

• 全局排序（Order By）

  Order By全局排序，只有一个Reducer

  ASC：升序（默认）

  DESC：降序

  注意，Order By语句在select语句的结尾

  # 按照工资降序排序
  select * from emp order by sal desc;
  

• 多排序条件情况

  select ename, deptno, sal from emp order by deptno, sal;
  

• 按照别名排序

  select empno,sal*2 as double_sal from emp order by double_sal desc;
  

• 每个reduce内部排序（Sort By）

  对于大规模集群而言，order by的效率非常低。很多情况下，并不需要全局排序，此时可以使用sort by。

  Sort by为每个reducer产生一个排序文件，每个reducer内部进行排序，对全局结果来说不是排序。

  在使用之前需要指定reduce的数量

  set mapreduce.job.reduces=3;
  

• 分区（Distribute By）

  在有些情况下， 我们需要控制某个特定行应该到哪个 reducer， 通常是为了进行后续的聚集操作。 distribute by 子句可以做这件事。

  distribute by 类似 MR 中 partition（自定义分区），进行分区，结合 sort by 使用。

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

  set mapreduce.job.reduces=3;
  insert overwrite local directory '/opt/module/data/distribute-result' select * from emp distribute by deptno sort by empno desc;
  

  distribute by的分区规则是根据分区字段的Hash码与Reduce的个数进行模除后，余数相同的分配到同一个分区。

  Hive要求distribute by语句写在sort by之前。

• Cluster By

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

  cluster by除了具有distribute by的功能外还具备sort by的功能，但是排序只能是升序排序。

6. 分区表和分桶表

6.1 分区表

• 分区表基本操作

  分区表实际上就是对应的一个HDFS文件系统上独立的文件夹，该文件夹下是分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割为小的数据集，在查询时通过Where子句中表达式选择所需要的指定分区，这样的查询效率会提高很多。

  创建分区表的语法：

  create table dept_partition( 
      deptno int, 
      dname string,
  )
  partitioned by (day string)
  row format delimited fields terminated by '\t';
  

  注意，分区字段不能是表中已经存在的字段。它可以理解成表中的一个隐式字段，在创建语句执行后会创建该列。

  加载分区数据语法：

  load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');
  

  注意，分区表加载数据时，必须指定分区 。

  分区表数据按照分区条件进行查询

  select * from dept_partition where day='20200401';
  

  增加分区

  alter table dept_partition add partition(day='20200405') partition(day='20200406');
  

  删除分区

  # 删除单个分区
  alter table dept_partition drop partition(day='20200406');
  # 删除多个分区
  alter table dept_partition drop partition(day='20200404'), partition(day='20200405');
  

  查看分区

  show partitions dept_partition;
  

• 二级分区

  创建二级分区表

  create tabledept_partition2( 
      deptno int, 
      dname string, 
      loc string
  )
  partitioned by (day string, hour string)
  

  数据加载

  load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition2 partition(day='20200401', hour='12');
  

  把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

  • 上传数据后修复

    # 上传
    dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
    hive (default)> dfs -put /opt/module/datas/dept_20200401.log /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
    

    # 执行修复命令
    msck repair table dept_partition2;
    # 查询可见
    select * from dept_partition2 where day='20200401' and hour='13';
    

  • 上传数据后添加分区

    上传步骤见上

    # 添加分区
    alter table dept_partition2 add partition(day='201709',hour='14');
    # 查询数据
    select * from dept_partition2 where day='20200401' and hour='14';
    

  • 创建文件夹后 load 数据到分区

    load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition2 partition(day='20200401',hour='15');
    

• 分区动态调整

  关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中， Hive 中也提供了类似的机制， 即动态分区(Dynamic Partition)， 只不过，使用 Hive 的动态分区，需要进行相应的配置。

  配置流程

  1. 开启动态分区

     hive.exec.dynamic.partition=true
     

  2. 设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区， nonstrict 模式表示允许所有的分区字段都可以使用动态分区。 ）

     hive.exec.dynamic.partition.mode=nonstrict
     

  3. 在所有执行MR的节点上，最大可以创建多少个动态分区。默认1000

     hive.exec.max.dynamic.partitions=1000
     

  4. 在每个执行 MR 的节点上，最大可以创建多少个动态分区。 该参数需要根据实际的数据来设定。 比如： 源数据中包含了一年的数据， 即 day 字段有 365 个值， 那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。

     hive.exec.max.dynamic.partitions.pernode=100
     

  5. 整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000

     hive.exec.max.created.files=100000
     

  6. 当有空分区生成时，是否抛出异常。 一般不需要设置。默认 false

     hive.error.on.empty.partition=false
     

  案例实践

  将dept表中数据按照地区（loc）字段，插入到目标表dept_partition的相应分区中。

  • 创建目标表

    create table dept_partition_dy(id int, name string) partitioned by (loc int) row format delimited fields terminated by '\t';
    

  • 设置动态分区

    set hive.exec.dynamic.partition.mode = nonstrict;
    hive (default)> insert into table dept_partition_dy partition(loc) select
    deptno, dname, loc from dept;
    

  • 查看目标表的分区情况

    show partitions dept_partition;
    

6.2 分桶表

分区提供一个隔离数据和优化查询的便利方式。 不过， 并非所有的数据集都可形成合理的分区。对于一张表或者分区， Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。 分区针对的是数据的存储路径；分桶针对的是数据文件。

• 创建分桶表

  create table stu_buck(id int, name string)
  clustered by(id)
  into 4 buckets
  row format delimited fields terminated by '\t';
  

• 导入数据到分桶表中

  load data inpath '/student.txt' into table stu_buck;
  

• insert 方式将数据导入分桶表

  insert into table stu_buck select * from student_insert;
  

• 分桶的规则

  根据结果可知： Hive 的分桶采用对分桶字段的值进行哈希， 然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中 。

• 分桶表操作注意事项

  • reduce 的个数设置为-1,让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个 数设置为大于等于分桶表的桶数
  • 从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题
  • 不要使用本地模式

6.3 抽样查询

对于非常大的数据集， 有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。 Hive 可以通过对表进行抽样来满足这个需求。

#  TABLESAMPLE(BUCKET x OUT OF y)，x不能大于y
select * from stu_buck tablesample(bucket 1 out of 4 on id);

7. 内置函数

7.1 常用内置函数

• 空字段赋值

  # 如果value字段是null，则返回default_value
  NVL(value， default_value)
  # 案例：如果员工的comm为null，返回-1
  select comm,nvl(comm, -1) from emp;
  

• CASE WHEN THEN ELSE END

  # 案例：统计各部门男女人数情况
  select
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male_count,
  sum(case sex when '女' then 1 else 0 end) female_count
  from emp_sex
  group by dept_id;
  

• 行转列

  CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

  CONCAT_WS(separator, str1, str2,…)： 它是一个特殊形式的 CONCAT()。 第一个参数是参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。 这个函数会跳过分隔符参数后的任何 NULL 和空字符串。 分隔符将被加到被连接的字符串之间;

  COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 Array 类型字段。

  案例：

  # 将血型和星座相同的人归类到一起，结果如下
  # 射手座,A 大海|凤姐
  # 白羊座,A 孙悟空|猪八戒
  # 白羊座,B 宋宋|苍老师
  
  SELECT
      t1.c_b,
      CONCAT_WS("|",collect_set(t1.name))
  FROM (
      SELECT
      NAME,
      CONCAT_WS(',',constellation,blood_type) c_b
      FROM person_info
  )t1
  GROUP BY t1.c_b
  

• 列转行

  EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。

  LATERAL VIEW
  用法： LATERAL VIEW udtf(expression) tableAlias AS columnAlias
  **解释：**用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

  # 案例：将下面电影分类进行拆解
  # 《疑犯追踪》  悬疑,动作,科幻,剧情
  # 《Lie to me》  悬疑,警匪,动作,心理,剧情
  #-------------------------------------
  # 《疑犯追踪》 悬疑
  # 《疑犯追踪》 动作
  # 《疑犯追踪》 科幻
  # 《疑犯追踪》 剧情
  # 《Lie to me》 悬疑
  # 《Lie to me》 警匪
  # 《Lie to me》 动作
  # 《Lie to me》 心理
  # 《Lie to me》 剧情
  SELECT
      movie,
      category_name
  FROM
      movie_info
  lateral VIEW
      explode(split(category,",")) movie_info_tmp AS category_name;
  

• 窗口函数

  • OVER()： 指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

  • **CURRENT ROW：**当前行

  • **n PRECEDING：**往前 n 行数据

  • **n FOLLOWING：**往后 n 行数据

  • **UNBOUNDED：**起点，
    UNBOUNDED PRECEDING 表示从前面的起点，
    UNBOUNDED FOLLOWING 表示到后面的终点

  • **LAG(col,n,default_val)：**往前第 n 行数据

  • **LEAD(col,n, default_val)：**往后第 n 行数据

  • **NTILE(n)：**把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行， NTILE 返回此行所属的组的编号。 注意： n 必须为 int 类型。

  样本数据：

  jack,2017-01-01,10
  tony,2017-01-02,15
  jack,2017-02-03,23
  tony,2017-01-04,29
  jack,2017-01-05,46
  jack,2017-04-06,42
  tony,2017-01-07,50
  jack,2017-01-08,55
  mart,2017-04-08,62
  mart,2017-04-09,68
  neil,2017-05-10,12
  mart,2017-04-11,75
  neil,2017-06-12,80
  mart,2017-04-13,94
  

  需求：

  1. 查询在 2017 年 4 月份购买过的顾客及总人数

     select name,count(*) over ()
     from business
     where substring(orderdate,1,7) = '2017-04'
     group by name;
     

  2. 查询顾客的购买明细和月购买金额

     select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
     from business;
     

  3. 查看顾客上次的购买时间

     select name,orderdate,cost,
         lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, 
         lag(orderdate,2) over (partition by name order by orderdate) as time2
     from business;
     

  4. 查询前 20%时间的订单信息

     select * from (
         select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
         from business
     ) t
     where sorted = 1;
     

• Rank函数

  RANK() 排序相同时会重复，总数不会变

  DENSE_RANK() 排序相同时会重复，总数会减少

  ROW_NUMBER() 会根据顺序计算

  # 计算每个人在各学科成绩排名情况
  select name,
      subject,
      score,
      rank() over(partition by subject order by score desc) rp,
      dense_rank() over(partition by subject order by score desc) drp,
      row_number() over(partition by subject order by score desc) rmp
  from score;