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 '\t' stored as textfile location '/user/stu2';
row format delimited fields terminated by '\t' 指定字段分隔符，默认分隔符为 '\001'
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 '\t';
- 从本地文件系统向表中加载数据
追加操作 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 一定慎用，一旦清空将无力回天
