hivesql入门总结

HQL:
DDL数据定义:
创建数据库
#数据库已经存在错误，增加if not exists判断
create database [If not exists] database_name
#数据库的描述
[comment database_comment]
#指定数据库在HDFS上存放的位置
[location hdfs_path]
[with dbproperties (property_name=property_value, ...)];
数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db。

过滤显示查询的数据库：show databases like 'db_hive*';
显示数据库信息：desc database db_hive;
显示数据库详细信息：desc database extended db_hive;
切换当前数据库：use db_hive;

数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。
使用alter database命令为某个数据库的dbproperties设置键-值对属性值
alter database db_hive set dbproperties('createtime'='20170830');

删除数据库：
#采用 if exists判断数据库是否存在
drop database if exists db_hive2;

#数据库不为空，采用cascade命令，强制删除
drop database db_hive cascade;

------------------>表：<--------------
创建表
#用 IF NOT EXISTS 选项
#EXTERNAL关键字可以让用户创建一个外部表
#在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
create [external] table [if not exists] table_name 
#COMMENT：为表和列添加注释
[(col_name data_type [comment col_comment], ...)] 
[comment table_comment] 
#PARTITIONED BY创建分区表
[partitioned by (col_name data_type [comment col_comment], ...)] 
#CLUSTERED BY创建分桶表
[clustered by (col_name, col_name, ...) 
#SORTED BY不常用，对桶中的一个或多个列另外排序
[sorted by (col_name [asc|desc], ...)] into num_buckets buckets] 
#hive使用Serde进行行对象的序列与反序列化
#指定ROW FORMAT 或者ROW FORMAT DELIMITED,不指定使用自带的SerDe
[row format row_format] 
#STORED AS指定存储文件类型
[stored as file_format] 
#指定一个指向实际数据的路径（LOCATION）
#指定表在HDFS上的存储位置
[location hdfs_path]
[tblproperties (property_name=property_value, ...)]
#AS：后跟查询语句，根据查询结果创建表
[as select_statement]
#LIKE复制现有的表结构，不复制数据
[like table_name]

*ROW FORMAT 
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] 
   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
*常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）

查询表的类型：desc formatted student2;
删除表：drop table student2;
注：在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

上传数据到HDFS：通过dfs 命令来操作一些hdfs操作
外部表：
创建外部表
create external table if not exists 表名(
字段名1 字段类型,
字段名2 字段类型,
字段名3 字段类型
)
#指定字段分割符为  \t
row format delimited fields terminated by '\t'
#指定hdfs路径，路径上有数据，则读取数据创建表
location '/company/dept';

查看表格式：desc formatted dept;
删除外部表：drop table dept;
修改内部表为外部表：alter table student set tblproperties('EXTERNAL'='TRUE');
修改外部表student为内部表：alter table student set tblproperties('EXTERNAL'='FALSE');

修改表
重命名表：ALTER TABLE 旧表名 RENAME TO 新表明
更新列，列名可以随意修改，列的类型只能小改大，不能大改小
alter table 表名 change [column] 旧列名 新列名 类型 [comment col_comment] [first|after column_name]
增加列：ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)
替换列：ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
注：REPLACE使用的时候，字段的类型要跟之前的类型对应上，数量可以减少或者增加，其实就是包含了更新列，增加列，删除列的功能。
    REPLACE是表示替换表中所有字段
清空表：truncate table student;
注：Truncate只能清空管理表，不能清空外部表中数据

------------------->_<-----------------------
DML数据操作
向表中装载数据:
#local:本地加载到hive；否则从HDFS加载,overwrite:覆盖,否则追加
load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];
基本模式插入数据:insert into table  student2 values(1,'wangwu'),(2,'zhaoliu');
查询语句向表中插入数据
insert overwrite table student2 select id, name from student where id < 1006;
注：insert into：以追加数据的方式插入到表或分区，原有数据不会删除
    insert overwrite：会覆盖表中已存在的数据
    insert不支持插入部分字段，后边跟select语句时，select之前不能加as，加了会报错，一定要跟下面的as select区分开。
查询语句中创建表并加载数据
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';
              
Import数据
注意：先用export导出后，再导入。因为export导出的数据里面包含了元数据，因此import要导入的表不可以存在，否则报错
import table student2  from '/user/hive/warehouse/export/student';

 Insert导出
查询的结果导出到本地
insert overwrite local directory '/opt/module/hive/datas/export/student'
            select * from student;
查询的结果格式化导出到本地
insert overwrite local directory '/opt/module/hive/datas/export/student1'
           ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
           select * from student;
结果导出到HDFS上(没有local)

Hadoop命令导出到本地
dfs -get /user/hive/warehouse/student/student.txt /opt/module/hive/datas/export/student3.txt;
Hive Shell 命令导出：
bin/hive -e 'select * from default.student;' > /opt/module/hive/datas/export/student4.txt;
Export导出到HDFS上
export table default.student to '/user/hive/warehouse/export/student';
注：export和import主要用于两个Hadoop平台集群之间Hive表迁移，不能直接导出的本地
Sqoop导出，详见sqoop

------------------->_<-----------------------
------------------->_<-----------------------
查询
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ]
 [LIMIT number]

常用函数
求总行数（count）：count(*)和count(1)所有行都加  count(字段)   字段为null，则不加
最大值（max）：
最小值（min）：
总和（sum）
平均值（avg）
LIMIT子句用于限制返回的行数:limit 5;  limit 2,3;
WHERE子句，将不满足条件的行过滤掉
比较运算符
A=B-->如果A等于B则返回TRUE，反之返回FALSE
A<=>B-->如果A和B都为NULL，则返回TRUE，如果一边为NULL，返回False
A<>B, A!=B-->A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
A<B-->A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
A<=B-->A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
A>B-->A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE
A>=B-->A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE
A [NOT] BETWEEN B AND C 
-->如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL-->如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL-->如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(数值1, 数值2)-->使用 IN运算显示列表中的值
A [NOT] LIKE B
-->B是一个SQL下的简单正则表达式，也叫通配符模式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
注：% 代表零个或多个字符(任意个字符)、_ 代表一个字符。
A RLIKE B, A REGEXP B
-->B是基于java的正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

分组
Group By语句
GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
 Having语句
 having与where不同点
 1）where后面不能写分组聚合函数，而having后面可以使用分组聚合函数。
 2）having只用于group by分组统计语句
 
Join语句
Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
join-->内连接,即inner join
left join-->左外连接
right join-->右外连接
full join-->满外连接
多表连接：连接 n个表，至少需要n-1个连接条件
注：Hive会对每对JOIN连接对象启动一个MapReduce任务，Hive总是按照从左到右的顺序执行的
    当对3个或者更多表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。
    
笛卡尔积产生条件：
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接

表的别名
1）使用别名可以简化查询。
2）使用表名前缀可以提高执行效率。

排序
Order By：全局排序，只有一个Reducer
ASC（ascend）: 升序（默认）    DESC（descend）: 降序
ORDER BY 子句在SELECT语句的结尾
ORDER BY最后执行，可以按照select中取的别名排序

Sort By：每个Reducer内部排序
order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。
对每个Reducer内部进行排序，对全局结果集来说不是排序

Distribute By：控制某个特定行应该到哪个reducer，自定义分区，结合sort by使用。
DISTRIBUTE BY语句要写在SORT BY语句之前
distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。

Cluster By：分区排序
distribute by和sort by字段相同时，可以使用cluster by方式，排序只能是升序

分区表
分区表创建:
create table dept_partition(
deptno int, dname string, loc 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');
分区表加载数据时，必须指定分区
查看分区表有多少分区
show partitions dept_partition;
单分区查询：根据分区字段过滤
select * from dept_partition where day='20200401';
分区联合查询：union
select * from dept_partition where day='20200401'
union
select * from dept_partition where day='20200402'
union
select * from dept_partition where day='20200403';
等价于
select * from dept_partition where day='20200401' or day='20200402' or day='20200403' ;
增加分区
创建单个分区：alter table dept_partition add partition(day='20200404') ;
创建多个分区：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');
注：分区之间必须有逗号
查看分区表结构：desc formatted dept_partition;

分区表二级分区
create table dept_partition2(
deptno int, dname string, loc string
)
#分区字段指定两个
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';
加载数据
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200401', hour='12');
查询分区
select * from dept_partition2 where day='20200401' and hour='12';
数据直接上传到分区目录上，让分区表和数据产生关联的三种方式
方式一：上传数据后修复
上传数据
dfs -mkdir -p /user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=13;
dfs -put /opt/module/datas/dept_20200401.log  /user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=13;
msck repair table dept_partition2;
方式二：上传数据后添加分区
上传数据：(day='20200401',hour='14')
添加分区
alter table dept_partition2 add partition(day='20200401',hour='14');
方式三：创建文件夹后load数据到分区
创建目录
dfs -mkdir -p /user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=15;
上传数据
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table
 dept_partition2 partition(day='20200401',hour='15');

------------------->_动态分区_<-----------------------
动态分区:数据库自动会根据分区字段的值，将数据插入到相应的分区中
参数设置
开启动态分区功能（默认true，开启）
hive.exec.dynamic.partition=true
设置为非严格模式（默认strict，必须指定至少一个分区为静态分区，nonstrict模式允许所有的分区字段都可以使用动态分区。）
hive.exec.dynamic.partition.mode=nonstrict
在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000
hive.exec.max.dynamic.partitions=1000
在每个执行MR的节点上，最大可以创建多少个动态分区
hive.exec.max.dynamic.partitions.pernode=100
整个MR Job中，最大可以创建多少个HDFS文件。默认100000
hive.exec.max.created.files=100000
当有空分区生成时，是否抛出异常。一般不需要设置。默认false
hive.error.on.empty.partition=false
#建表
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;
#插入数据
insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept;

分桶表
分桶是将数据集分解成更容易管理的若干部分的另一个技术。
分区针对的是数据的存储路径；分桶针对的是数据文件。
Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中
创建分桶表
create table stu_buck(id int, name string)
#分四桶
clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';
导入数据
load data local inpath   '/opt/module/hive/datas/student.txt' into table stu_buck;
hive新版本load数据跑mr，因此要改用hdfs路径导数据

------------------->_函数_<-----------------------
函数
系统内置函数
空字段赋值
NVL( value，default_value)-->如果value为NULL，则NVL函数返回default_value的值，否则返回value的值
行转列
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数是剩余参数间的分隔符。
注：分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
    CONCAT_WS must be "string or array<string>"
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
COLLECT_LIST(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行不去重汇总，产生array类型字段。
列转行
Split(str, separator)：将字符串按照后面的分隔符切割，转换成字符array。
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
LATERAL VIEW udtf(expression) tableAlias AS columnAlias
lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

窗口函数（开窗函数）
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行数据
FIRST_VALUE (col,true/false)：当前窗口下的第一个值，第二个参数为true，跳过空值
LAST_VALUE (col,true/false)：当前窗口下的最后一个值，第二个参数为true，跳过空值
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。
例：查询前20%时间的订单信息
select * from (
    select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
    from business
) t
where sorted = 1;

Rank
RANK() 排序相同时会重复(考虑并列,会跳号)，总数不会变    1,2,2,4
DENSE_RANK() 排序相同时会重复(考虑并列,不跳号)，总数会减少  1,2,2,3
ROW_NUMBER() 会根据顺序计算(不考虑并列,不跳号,行号)   1,2,3,4

自定义函数
UDF（User-Defined-Function）:一进一出
UDAF（User-Defined Aggregation Function）：聚合函数，多进一出
UDTF（User-Defined Table-Generating Functions）：表生成函数，一进多出，如lateral view explode()

编程步骤：
（1）继承Hive提供的类
         org.apache.hadoop.hive.ql.udf.generic.GenericUDF  
        org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
（2）实现类中的抽象方法
（3）在hive的命令行窗口创建函数

将jar包添加到hive的classpath，临时生效:add jar /opt/module/hive/datas/myudf.jar;
创建临时函数与开发好的java class关联
create temporary function 函数名 as "jar全类名";
删除临时函数
drop  temporary function 函数名;
创建永久函数
jar包上传到$HIVE_HOME/auxlib下，然后重启hive
创建永久函数
create function my_len2 as " com.atguigu.hive.udf.MyLength";
删除永久函数
drop function my_len2;
注：在集群中，应该将jar包上传到hdfs，因为保存到本地hive中，其他节点就无法访问，不能所有节点使用
    永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。

UDF
extends GenericUDF
初始化方法,里面要做三件事:
1.约束函数传入参数的个数
2.约束函数传入参数的类型
3.约束函数返回值的类型
@Override
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {}
函数逻辑处理方法

@Override
    public Object evaluate(DeferredObject[] arguments) throws HiveException {}
@Override
    public String getDisplayString(String[] children) {}

hive的命令行窗口删除函数:drop [temporary] function [if exists] [dbname.]function_name;

month(orderdate)：取月份

设置reduce个数：set mapreduce.job.reduces=3;