Hive-SQL

目录

DDL(data definition language 数据定义语言)

DML（data manipulation language 数据操纵语言）

Hive函数

DDL(data definition language 数据定义语言)

对数据库和表进行创建删除和修改，不涉及表内部数据的操作，在Hive中，默认的数据库名为default，存储位置在HDFS的/user/hive/warehouse下，用户创建的数据库在/user/hive/warehouse/database_name.db下

创建数据库

create (database|schema) [if not exists] database_name
comment database_comment  //数据库的注释说明语句
location hdfs_path //数据库的存储路径
with dbproerties (property_name=property_value...)  //数据库的一些配置属性

 选择数据库

use database_name;

删除数据库

drop database_name[restrict|cascade]
 
默认是restrict行为，只能删除空数据库

创建表

为该文件创建对应的表

Step1：根据数据的结构来创建表

 Step2：将该文件上传到HDFS的t_archer文件下

 默认分隔符是\001,键盘敲不出来，在vi编辑器中现实为^A,在文本编辑器中显示为soh，如果数据中的分隔符是\001，那么在创建表时就不需要指定分隔符

show databases ;//查看所有数据库
show tables ;//查看当前数据库下的所有表
show tables in default;//查看某个数据库中的所有表
 
drop table t_archer;
 
desc formatted t_archer; //查看表的详细信息

解决查看表元数据信息时的中文乱码问题

原因是mysql的编码不支持中文，支持Latin1编码，兼容ASCII，在Linux中打开mysql

输入

use hive3;
show tables;
 
alter table hive3.COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table hive3.TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
alter table hive3.PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
alter table hive3.PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
alter table hive3.INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

将datagrip中出问题的表删除后重新创建即可。

DML（data manipulation language 数据操纵语言）

添加数据 

Load给表加载数据

以前加载数据时，先通过客户端创建表，这时在HDFS上就生成一个与表名完全相同的文件夹，通过hadoop fs -put命令或者HDFS网页将文件上传到该文件夹，就完成了表和数据文件的映射，但对于Hive来说，HDFS是它的底层，如果直接跳过Hive操作底层的HDFS是不安全的，也可以看出Hive在映射表和文件时，是根据表名和HDFS上的文件名相同来映射。

现在可以通过Load将数据文件移动到与Hive表对应的位置。

 LOCAL指的是服务所在机器，比如hiveserver2是在node1上开启，那么本地就是指node1，而不是指客户端所在的机器，所以本地文件系统是指node1上的Linux文件系统

 

 当指定local时，从Linux系统本地加载数据文件，将文件复制到HDFS上面

没有指定local关键字时，将HDFS系统上面的文件移动到HDFS系统的另一个文件下面

override关键字表示覆盖之前表中的数据

 

Insert插入数据

可以直接使用insert语法将数据插入到指定的表中。

如insert into student value(1,"zz");

通常是将查询的结果插入到另一张表，将查询的结果放到另一个表中，供其他方向使用，要注意查询出来的内容必须符合表的结构，列的数目一致，数据类型保持一致，也会进行自动转换，但不能保证转换成功，如果转换失败数据就会变为null。

底层要考mapreduce来执行，运行速度很慢

查询数据

select current_database(); //查询当前所处的数据库
select distinct country ,state from t_usa_covid19; //将country和state看作一个整体来去重
select * from t_usa_covid19 where length(state) > 10; //字符长度超过10
select count(distinct country) from t_usa_covid19;
select count(country) from t_usa_covid19;

聚合函数（如count,max,min,sum,avg）使用的前提是结果集已经确定，而where子句还处于去确定结果集的过程中，因而不能使用聚合函数.

group by

select state,count(country),count(deaths) from t_usa_covid19 where count_date="2021-01-28" group by state;

在group by中的被select的字段，要么该字段出现在group by后面，要么该字段是聚合函数作用，否则会报错

先进行where操作，再进行group by操作，因为在进行group by时，先按照group by后面的字段将数据划分好，再将每一组转化为一行数据，下图中，categories化成一行，因为每组数据相同，count（day）也可以，但value就不行。

having

where只能判断表中现有的数据，表中没有count（*）等数据，所以having用于在where过滤之后再进行过滤。

where是确定结果的过程，group by是确定好结果后，再进行分组过滤，having是在分组过滤后进行的，where是在分组前进行过滤

select state,sum(deaths) from t_usa_covid19 where count_date="2021-01-28" group by state having count(deaths) > 10000;
select state,sum(deaths) as death_coutnt from t_usa_covid19 where count_date="2021-01-28" group by state having death_coutnt > 10000;
 
第二个语句比第一个语句更快，因为省去了一次计算，通过起别名的方式

总结各关键字的执行顺序

 

关联查询 

inner join = join

left outer join = left join

Hive函数

show functions; //展示所以的函数
describe function extended count;//展示count()函数的功能和使用方式

函数的分类

用户定义函数的分类

 但现在不管是内置函数还是用户定义的函数，都可以按照输入输出的行数来进行分类，所以UDAF可以代表Hive的所有函数中（包括内置和用户自定义）具有多进一出的特点的函数

常用的内置函数

字符串函数

 返回数组

日期函数

 

 数学函数

 

 条件函数