一文学完所有的Hive Sql（两万字最全详解）

Hive Sql 大全

本文基本涵盖了Hive日常使用的所有SQL，因为SQL太多，所以将SQL进行了如下分类： 一、DDL语句（数据定义语句）：
对数据库的操作：包含创建、修改数据库
对数据表的操作：分为内部表及外部表，分区表和分桶表
二、DQL语句（数据查询语句）：
单表查询、关联查询
hive函数：包含聚合函数，条件函数，日期函数，字符串函数等
行转列及列转行：lateral view 与 explode 以及 reflect
窗口函数与分析函数
其他一些窗口函数

文章首发于公众号【】，大数据领域原创技术号，每周更新大数据技术文及面试真题解析，关注后可领取精心制作大数据面试宝典！

hive的DDL语法

对数据库的操作

• 创建数据库:

  create?database?if?not?exists?myhive;
  说明：hive的表存放位置模式是由hive-site.xml当中的一个属性指定的?:hive.metastore.warehouse.dir

  创建数据库并指定hdfs存储位置?:
  create?database?myhive2?location?‘/myhive2’;

• 修改数据库:

  alter??database??myhive2??set??dbproperties(‘createtime’=‘20210329’);

说明：可以使用alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库所在的位置

• 查看数据库详细信息

  查看数据库基本信息
  hive?(myhive)>?desc??database??myhive2;

  查看数据库更多详细信息
  hive?(myhive)>?desc?database?extended??myhive2;

• 删除数据库

  删除一个空数据库，如果数据库下面有数据表，那么就会报错
  drop??database??myhive2;

  强制删除数据库，包含数据库下面的表一起删除
  drop??database??myhive??cascade;?

对数据表的操作

对管理表(内部表)的操作:

• 建内部表:

  hive?(myhive)>?use?myhive;?–?使用myhive数据库
  hive?(myhive)>?create?table?stu(id?int,name?string);
  hive?(myhive)>?insert?into?stu?values?(1,“zhangsan”);
  hive?(myhive)>?insert?into?stu?values?(1,“zhangsan”),(2,“lisi”);??–?一次插入多条数据
  hive?(myhive)>?select?*?from?stu;

• hive建表时候的字段类型:

分类

类型

描述

字面量示例

原始类型

BOOLEAN

true/false

TRUE

TINYINT

1字节的有符号整数 -128~127

1Y

SMALLINT

2个字节的有符号整数，-32768~32767

1S

INT

4个字节的带符号整数

1

BIGINT

8字节带符号整数

1L

FLOAT

4字节单精度浮点数1.0

DOUBLE

8字节双精度浮点数

1.0

DEICIMAL

任意精度的带符号小数

1.0

STRING

字符串，变长

“a”,’b’

VARCHAR

变长字符串

“a”,’b’

CHAR

固定长度字符串

“a”,’b’

BINARY

字节数组

无法表示

TIMESTAMP

时间戳，毫秒值精度

122327493795

DATE

日期

‘2016-03-29’

INTERVAL

时间频率间隔

复杂类型

ARRAY

有序的的同类型的集合

array(1,2)

MAP

key-value,key必须为原始类型，value可以任意类型

map(‘a’,1,’b’,2)

STRUCT

字段集合,类型可以不同

struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)

UNION

在有限取值范围内的一个值

create_union(1,’a’,63)

对decimal类型简单解释下：
用法：decimal(11,2) 代表最多有11位数字，其中后2位是小数，整数部分是9位；如果整数部分超过9位，则这个字段就会变成null；如果小数部分不足2位，则后面用0补齐两位，如果小数部分超过两位，则超出部分四舍五入
也可直接写 decimal，后面不指定位数，默认是 decimal(10,0) 整数10位，没有小数

• 创建表并指定字段之间的分隔符

  create??table?if?not?exists?stu2(id?int?,name?string)?row?format?delimited?fields?terminated?by?’ ‘?stored?as?textfile?location?’/user/stu2’;

row format delimited fields terminated by ’ ’ 指定字段分隔符，默认分隔符为 ‘’
stored as 指定存储格式
location 指定存储位置

• 根据查询结果创建表

  create?table?stu3?as?select?*?from?stu2;

• 根据已经存在的表结构创建表

  create?table?stu4?like?stu2;

• 查询表的结构

  只查询表内字段及属性
  desc?stu2;

  详细查询
  desc?formatted??stu2;

• 查询创建表的语句

  show?create?table?stu2;

对外部表操作

外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉，只会删除表的元数据

• 构建外部表

  create?external?table?student?(s_id?string,s_name?string)?row?format?delimited?fields?terminated?by?’ ';

• 从本地文件系统向表中加载数据

  追加操作
  load?data?local?inpath?‘/export/servers/hivedatas/student.csv’?into?table?student;

  覆盖操作
  load?data?local?inpath?‘/export/servers/hivedatas/student.csv’?overwrite??into?table?student;

• 从hdfs文件系统向表中加载数据

  load?data?inpath?‘/hivedatas/techer.csv’?into?table?techer;

  加载数据到指定分区
  load?data?inpath?‘/hivedatas/techer.csv’?into?table?techer?partition(cur_date=20201210);

• 注意：
  1.使用 load data local 表示从本地文件系统加载，文件会拷贝到hdfs上
  2.使用 load data 表示从hdfs文件系统加载，文件会直接移动到hive相关目录下，注意不是拷贝过去，因为hive认为hdfs文件已经有3副本了，没必要再次拷贝了
  3.如果表是分区表，load 时不指定分区会报错
  4.如果加载相同文件名的文件，会被自动重命名

对分区表的操作

• 创建分区表的语法

  create?table?score(s_id?string,?s_score?int)?partitioned?by?(month?string);

• 创建一个表带多个分区

  create?table?score2?(s_id?string,?s_score?int)?partitioned?by?(year?string,month?string,day?string);

注意：
hive表创建的时候可以用 location 指定一个文件或者文件夹，当指定文件夹时，hive会加载文件夹下的所有文件，当表中无分区时，这个文件夹下不能再有文件夹，否则报错
当表是分区表时，比如 partitioned by (day string)， 则这个文件夹下的每一个文件夹就是一个分区，且文件夹名为 day=20201123 这种格式，然后使用：msck repair table score; 修复表结构，成功之后即可看到数据已经全部加载到表当中去了

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

  load?data?local?inpath?‘/export/servers/hivedatas/score.csv’?into?table?score?partition?(month=‘201806’);

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

  load?data?local?inpath?‘/export/servers/hivedatas/score.csv’?into?table?score2?partition(year=‘2018’,month=‘06’,day=‘01’);

• 查看分区

  show??partitions??score;

• 添加一个分区

  alter?table?score?add?partition(month=‘201805’);

• 同时添加多个分区

  ?alter?table?score?add?partition(month=‘201804’)?partition(month?=?‘201803’);

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

• 删除分区

  ?alter?table?score?drop?partition(month?=?‘201806’);

对分桶表操作

将数据按照指定的字段进行分成多个桶中去，就是按照分桶字段进行哈希划分到多个文件当中去
分区就是分文件夹，分桶就是分文件

分桶优点：
1. 提高join查询效率
2. 提高抽样效率

• 开启hive的捅表功能

  set?hive.enforce.bucketing=true;

• 设置reduce的个数

  set?mapreduce.job.reduces=3;

• 创建桶表

  create?table?course?(c_id?string,c_name?string)?clustered?by(c_id)?into?3?buckets;

桶表的数据加载：由于桶表的数据加载通过hdfs dfs -put文件或者通过load data均不可以，只能通过insert overwrite 进行加载
所以把文件加载到桶表中，需要先创建普通表，并通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去

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

  insert?overwrite?table?course?select?*?from?course_common?cluster?by(c_id);??–?最后指定桶字段

修改表和删除表

• 修改表名称

  alter??table??old_table_name??rename??to??new_table_name;

• 增加/修改列信息

  查询表结构
  desc?score5;

  添加列
  alter?table?score5?add?columns?(mycol?string,?mysco?string);

  更新列
  alter?table?score5?change?column?mysco?mysconew?int;

• 删除表操作

  drop?table?score5;

• 清空表操作

  truncate?table?score6;

  说明：只能清空管理表，也就是内部表；清空外部表，会产生错误

注意：truncate 和 drop：
如果 hdfs 开启了回收站，drop 删除的表数据是可以从回收站恢复的，表结构恢复不了，需要自己重新创建；truncate 清空的表是不进回收站的，所以无法恢复truncate清空的表
所以 truncate 一定慎用，一旦清空将无力回天

向hive表中加载数据

• 直接向分区表中插入数据

  insert?into?table?score?partition(month?=‘201807’)?values?(‘001’,‘002’,‘100’);

• 通过load方式加载数据

  ?load?data?local?inpath?‘/export/servers/hivedatas/score.csv’?overwrite?into?table?score?partition(month=‘201806’);

• 通过查询方式加载数据

  insert?overwrite?table?score2?partition(month?=?‘201806’)?select?s_id,c_id,s_score?from?score1;

• 查询语句中创建表并加载数据

  create?table?score2?as?select?*?from?score1;

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

  create?external?table?score6?(s_id?string,c_id?string,s_score?int)?row?format?delimited?fields?terminated?by?‘,’?location?‘/myscore’;

• export导出与import 导入 hive表数据（内部表操作）

  create?table?techer2?like?techer;?–依据已有表结构创建表

  export?table?techer?to??‘/export/techer’;

  import?table?techer2?from?‘/export/techer’;

hive表中数据导出

• insert导出

  将查询的结果导出到本地
  insert?overwrite?local?directory?‘/export/servers/exporthive’?select?*?from?score;

  将查询的结果格式化导出到本地
  insert?overwrite?local?directory?‘/export/servers/exporthive’?row?format?delimited?fields?terminated?by?’ ‘?collection?items?terminated?by?’#'?select?*?from?student;

  将查询的结果导出到HDFS上(没有local)
  insert?overwrite?directory?‘/export/servers/exporthive’?row?format?delimited?fields?terminated?by?’ ‘?collection?items?terminated?by?’#'?select?*?from?score;

• Hadoop命令导出到本地

  dfs?-get?/export/servers/exporthive/000000_0?/export/servers/exporthive/local.txt;

• hive shell 命令导出

  基本语法：（hive?-f/-e?执行语句或者脚本?>?file）

  hive?-e?“select?*?from?myhive.score;”?>?/export/servers/exporthive/score.txt

  hive?-f?export.sh?>?/export/servers/exporthive/score.txt

• export导出到HDFS上

  export?table?score?to?‘/export/exporthive/score’;

hive的DQL查询语法

单表查询

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

注意：
1、order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
4、Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序。
因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by

• WHERE语句

  select?*?from?score?where?s_score?<?60;

注意：
小于某个值是不包含null的，如上查询结果是把 s_score 为 null 的行剔除的

• GROUP BY 分组

  select?s_id?,avg(s_score)?from?score?group?by?s_id;

  分组后对数据进行筛选，使用having
  ?select?s_id?,avg(s_score)?avgscore?from?score?group?by?s_id?having?avgscore?>?85;

注意：
如果使用 group by 分组，则 select 后面只能写分组的字段或者聚合函数
where和having区别：
1 ha