关于hive核心

2024-01-23 15:09:47

一、DDL数据定义

1、创建数据库

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

hive (default)> create database db_hive;

2）避免要创建的数据库已经存在错误，增加 if not exists 判断。（标准写法）

hive (default)> create database db_hive;

FAILED:  Execution   Error,   return   code  1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database db_hive already exists

hive (default)> create database if not exists db_hive;

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

hive (default)> create database db_hive2 location '/db_hive2.db';

2、查询数据库

显示数据库

1．显示数据库

hive> show databases;

2．过滤显示查询的数据库

hive> show databases like 'db_hive*'; 

OK 
db_hive 
db_hive_1
查看数据库详情

1．显示数据库信息

hive> desc database db_hive; 
OK 
db_hive		hdfs://master:8020/usr/hive/warehouse/db_hive.db	root	USER

2．显示数据库详细信息，extended

hive> desc database extended db_hive; 
OK
db_hive		hdfs://master:8020/usr/hive/warehouse/db_hive.db	root	USER
切换当前数据库
hive (default)> use db_hive;

3、修改数据库

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

数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

hive (default)>alter hive set database dbproperties('createtime'='20200830');

在 hive 中查看修改结果

hive> desc database extended db_hive;

db_name comment location owner_name owner_type parameters 
db_hive hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db chaosUSER {createtime=20200830}

4、删除数据库

1．删除空数据库

hive>drop database db_hive2;

2．如果删除的数据库不存在，最好采用 if exists 判断数据库是否存在

hive> drop database db_hive;

FAILED: SemanticException [Error 10072]: Database does not exist: db_hive

hive> drop database if exists db_hive2;

3．如果数据库不为空，可以采用 cascade 命令，强制删除

hive> drop database db_hive;

FAILED: Execution   Error,   return   code  1 from org.apache.hadoop.hive.ql.exec.DDLTask.

InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.) hive> drop database db_hive cascade;

5、创建表

1）建表语句
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]
2）字段解释
•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常
•EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）,Hive 创建内部表时，会将数据移动到hadfs指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
•LIKE 允许用户复制现有的表结构，但是不复制数据
•COMMENT可以为表与字段增加注释描述
•PARTITIONED BY  创建分区表,指定分区
•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, ...)] 
　　用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，
用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。 
•STORED AS 
　　SEQUENCEFILE //序列化文件
　　| TEXTFILE //普通的文本文件格式
　　| RCFILE　　//行列存储相结合的文件
　　| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname //自定义文件格式
　　如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。
•LOCATION指定表在HDFS的存储路径

6、修改表

重命名表
ALTER TABLE table_name RENAME TO new_table_name
增加/修改/替换列信息

更新列

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 则是表示替换表中所有字段。

简单示例

（1）查询表结构

hive> desc dept_partition;

（2）添加列

hive (default)> alter table dept_partition add columns(deptdesc string);

（3）更新列

hive (default)> alter table dept_partition change column deptdesc desc int;

（4）替换列

hive (default)> alter table dept_partition replace columns(deptno string, dname string, loc string);

注：hive不支持删除字段

7、删除表

注意：内部表和外部表删除的区别

 drop table dept_name;

8、实例

1）关于内部表与外部表

建表语句

创建部门表

create external table if not exists default.dept( deptno int, dname string, loc int )

row format delimited fields terminated by '\t';

创建员工表

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

查看创建的表

hive (default)> show tables; 
OK 
tab_name 
dept 
emp

向外部表中导入数据导入数据

hive (default)>load data inpath local '/opt/module/data/dept.txt' into table default.dept;
hive (default)>load data local inpath'/opt/module/data/emp.txt' into table default.emp;

查询结果

hive (default)> select * from emp;
hive (default)> select * from dept;

查看表格式化数据

hive (default)> desc formatted dept;

Table Type: EXTERNAL_TABLE
管理表与外部表的互相转换

（1）查询表的类型

hive (default)> desc formatted student2;

 Table Type: MANAGED_TABLE

（2）修改内部表 student2 为外部表

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

（3）查询表的类型

hive (default)> desc formatted student2;

 Table Type: EXTERNAL_TABLE

（4）修改外部表 student2 为内部表

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

（5）查询表的类型

hive (default)> desc formatted student2;

 Table Type: MANAGED_TABLE

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

2）关于基础分区表

1．引入分区表（需要根据日期对日志进行管理）

/user/hive/warehouse/log_partition/20200702/20200702.log

/user/hive/warehouse/log_partition/20200703/20200703.log

/user/hive/warehouse/log_partition/20200704/20200704.log

2．创建分区表语法

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

3．加载数据到分区表中

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='202009');

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='202008');

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='202007’);

4．查询分区表中数据

单分区查询

hive (default)> select * from dept_partition where month='202009';

多分区联合查询

hive (default)> select * from dept_partition where month='202009' union all select * from dept_partition where month='202008' union select * from dept_partition where month='202007';、
简单写法
hive (default)> select * from dept_partition where month>='202007'and month<='202009'

5．增加分区

创建单个分区

hive (default)> alter table dept_partition add partition(month='202006') ;

同时创建多个分区

hive (default)> alter table dept_partition add partition(month='202005') partition(month='202004');

6．删除分区

删除单个分区

hive (default)> alter table dept_partition drop partition (month='202004');

同时删除多个分区

hive (default)> alter table dept_partition drop partition (month='202005'), partition (month='202006');
简单写法
hive (default)> alter table dept_partition drop partition (month>='202005',month<='202006');

7．查看分区表有多少分区

hive> show partitions dept_partition;

8．查看分区表结构

hive> desc formatted dept_partition;
3）关于多级分区表

hive中的多级分区表，可以理解为多级目录，按照分区字段顺序将原本数据划分为多级目录

1．创建二级分区表

hive (default)> create table dept_partition2(deptno int, dname string, loc string)
partitioned by (month string, day string) 
row format delimited fields terminated by '\t';

2．正常的加载数据

（1）加载数据到二级分区表中

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition2 partition(month='202009', day='13');

（2）查询分区数据

hive (default)> select * from dept_partition2 where month='202009' and day='13';

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

（1）方式一：上传数据后修复

​ 上传数据

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202009/day=12; 

hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=202009/day=12;

查询数据（查询不到刚上传的数据）

hive (default)> select * from dept_partition2 where month='202009' and day='12';

执行修复命令

hive> msck repair table dept_partition2;

再次查询数据

hive (default)> select * from dept_partition2 where month='202009' and day='12';

（2）方式二：上传数据后添加分区

​ 上传数据

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202009/day=11; 

hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=202009/day=11;

执行添加分区

 hive (default)> alter table dept_partition2 add partition(month='202009',day='11');

查询数据

hive (default)> select * from dept_partition2 where month='202009' and day='11';

（3）方式三：创建文件夹后 load 数据到分区

创建目录

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202009/day=10;

上传数据

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='202009',day='10');

查询数据

hive (default)> select * from dept_partition2 where month='202009' and day='10';
4）关于动态分区表

前面的分区创建方式为静态分区，如果需要创建非常多的分区，或者根据指定字段值分区，就需要使用动态分区，hive的动态分区可以基于查询参数推断出需要创建的分区名称。

eg：

insert overwrite table employees partition(country,state)

select ...,se.cnty,se.st

from staged_employees se;

如上面例子，hive可以根据select语句中最后2列来确定分区字段country和state的值。这也是为什么在上面例子使用了不同的命名，就是为了强调源表字段值和输出分区值之间的关系是根据位置而不是根据命名来匹配的。

关于混合使用动态和静态分区。

eg：

insert overwrite table employees partition(country='China',state)

select ...,se.cnty,se.st

from staged_employees se

where se.cnty='China';

如上面例子，指定了country字段的值为静态的中国，而分区字段state是动态值

注意：静态分区必须出现在动态分区键之前。

动态分区功能默认是关闭的，开启后，也会默认是严格模式执行，在这种模式下要求至少有一列分区字段是静态的，这有助于因设计错误导致查询产生大量的分区。

动态分区属性

属性名称 缺省值（默认值） 描述
hive.exec.dynamic.partition false 设置为true，表示开启动态分区功能
hive.exec.dynamic.parititon.mode strict 设置为nonstrict，表示允许所有分区都是动态的
hive.exec.dynamic.partitions.pernode 100 每个mapper或reducer可以创建的最大动态分区个数，如果某个mapper或reducer尝试创建超过这个值的分区的话，就会报错
hive.exec.dynamic.parititons 1000 一个动态分区创建语句可以创建的最大动态分区个数。如果超过也会报错
hive.exec.max.created.files 100000 全局可以创建的最大文件个数。有一个Hadoop计数器，会跟踪记录创建了多少个文件，如果超过也会报错。
5）关于倾斜表

通过指定一个或者多个列经常出现的值（严重偏斜），Hive 会自动将涉及到这些值的数据拆分为单独的文件。在查询时，如果涉及到倾斜值，它就直接从独立文件中获取数据，而不是扫描所有文件，这使得性能得到提升。

create [exertnal] table 倾斜表名(字段名 类型,字段名 类型) skewed by (倾斜字段) ON (对应常见倾斜值,对应常见倾斜值)

row format delimited fields terminated by 字段分隔符;

9、视图

当查询变得长或复杂的时候，通过使用视图将这个查询语句分割成多个小的、更可控的片段可以降低这种复杂度。这点和在编程语言中使用函数或者软件设计中的分层设计的概念是一致的。封装复杂的部分可以是最终用户通过重用重复的部分来构建复杂的查询。

对于视图来说一个常见的使用场景就是基于一个或多个列的值来限制输出结果。有些数据库允许将视图作为一个安全机制，也就是不给用户直接访问具有敏感数据的原始表，而是提供给用户一个通过WHERE子句限制了的视图，以供访问。Hive 目前并不支持这个功能，因为用户必须具有能够访问整个底层原始表的权限，这时视图才能工作。然而，通过创建视图来限制数据访问可以用来保护信息不被随意查询。

Hive 会先解析视图，然后使用解析结果再来解析整个查询语句。然而，作为Hive查询优化器的一部分，查询语句和视图语句可能会合并成-一个单-一的实际查询语句。这个概念视图仍然适用于视图和使用这个视图的查询语句都包含了一个ORDER BY子句或-一个LIMIT子句的情况。这时会在使用这个视图的查询语句之前对该视图进行解析。例如，如果视图语句含有一个LIMIT 100 子句，而同时使用到这个视图的查询含有一个LIMIT 200子句，那么用户最终最多只能获取100条结果记录。因为定义一个视图实际上并不会“具体化”操作任何实际数据，所以视图实际上是对其所使用到的表和列的一个查询语句固化过程。因此，如果视图所涉及的表或者列不再存在时，会导致视图查询失败。

一个视图的名称要和这个视图所在的数据库下的其他所有表和视图的名称不同。用户还可以为所有的新列或部分新列增加一个COMMNET子句，进行写注释。这些注释并非“ 继承”原始表中的定义。同样地，如果AS SELECT子句中包含没有命名别名的表达式的话，例如size(cols)(计算cols中元素的个数)，那么Hive将会使用_ _CN 作为新的列名，其中N表示从0开始的一个整数。如果AS SELECT语句不合法的话，那么创建视图过程将失败。

eg:

CREATE VIEW IF NOT EXISTS shipments (time, part)

COMMENT ' Time and parts for shipments. '

TBLPROPERTIES ( 'creator' = 'me' )

AS SELECT ...;

在AS SELECT子句之前，用户可以通过定义TBLPROPERTIES来定义表属性信息，这点和表相同。上例中，我们定义的属性为“creator”, 表示这个视图的创建者名称。

CREATE [EXTERNAL] TABLE ... LIKE ..结构同样适用于复制视图，只需要在LIKE表达式里面写视图名就可以了:

eg:

CREATE TABLE shipments2 LIKE shipments;

视图不能够作为INSERT语句或LOAD命令的目标表。视图是只读的。对于视图只允许改变元数据中TBLPROPERTIES(表属性)属性信息:

ALTER VIEW shipments SET TBLPROPERTIES ('created_ at' = ' some_ timestamp') ;

1）Hive 的视图和关系型数据库的视图区别

和关系型数据库一样，Hive 也提供了视图的功能，不过请注意，Hive 的视图和关系型数据库的数据还是有很大的区别：

　　（1）只有逻辑视图，没有物化视图；

　　（2）视图只能查询，不能做加载数据操作，如：Load/Insert/Update/Delete 数据；

　　（3）视图在创建时候，只是保存了一份元数据，当查询视图的时候，才开始执行视图对应的那些子查询

　　（4）view定义中若包含了ORDER BY/LIMIT语句，当查询视图时也进行ORDER BY/LIMIT语句操作，view当中定义的优先级更高

​ • view: order by age asc;

​ • select order by age desc;

​ • select * from view order by age desc;

　　（5）view支持迭代视图

​ • view1: select * from tb_user1;

​ • view2: select * from view1;

​ • view3: select * from view2;

2）Hive视图的创建语句
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name 
  [(column_name [COMMENT column_comment], ...) ]
  [COMMENT view_comment]
  [TBLPROPERTIES (property_name = property_value, ...)]
  AS SELECT ... ;
3）Hive视图的查看语句
show views;
desc view_test;-- 查看某个具体视图的信息
4）Hive视图的使用语句
select colums from view_test;
select * from view_test;
5）Hive视图的删除语句
DROP VIEW [IF EXISTS] [db_name.]view_name;
drop view view_test;

10、索引

Hive只有有限的索引功能。Hive中没有普通关系型数据库中键的概念，但是还是可以对一些字段建立索引来加速某些操作的。一张表的索引数据存储在另外一张表中。

索引处理模块被设计成为可以定制的Java编码的插件，因此，用户可以根据需要对其进行实现，以满足自身的需求。

当逻辑分区实际上太多太细而几乎无法使用时，建立索引也就成为分区的另-一个选择。建立索引可以帮助裁剪掉一张表的一些数据块，这样能够减少MapReduce的输人数据量。并非所有的查询都可以通过建立索引获得好处。通过EXPLAIN命令可以查看某个查询语句是否用到了索引。

Hive中的索引和那些关系型数据库中的一样， 需要进行仔细评估才能使用。维护索引也需要额外的存储空间，同时创建索引也需要消耗计算资源。用户需要在建立索引为查询带来的好处和因此而需要付出的代价之间做出权衡。

1）创建索引
create index t1_index on table tb_user(name) 

as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild 

in table t1_index_table;

as：指定索引器；

in table：指定索引表，若不指定默认生成在default_tb_user_t1_index表中

create index t2_index on table tb_user(name)

as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild;

with deferred rebuild表示在执行alter index xxx_index on xxx rebuild时将调用generateIndexBuildTaskList获取Index的MapReduce，并执行为索引填充数据。

2）显示索引
show [formatted] index on tb_name;

关键字FORMATTED是可选的。增加这个关键字可以使输出中包含有列名称。用户还可以替换INDEX为INDEXES,这样输出中就可以列举出多个索引信息了。

3）重建索引

建立新索引之后必须重建索引才能生效

ALTER INDEX t2_index ON tb_user REBUILD;
ALTER INDEX employees_index ON TABLE employees
PARTITION (country = 'China')
REBUILD;

如果省略掉PARTITION,那么将会对所有分区进行重建索引。

还没有一个内置的机制能够在底层的表或者某个特定的分区发生改变时，自动触发重建索引。但是，如果用户具有一个工作流来更新表分区中的数据的话，那么用户可能已经在其中某处使用到了ALTER TABLE .. TOUCH PARTITION(..)功能，同样地，在这个工作流中也可以对对应的索引执行重建索引语句ALTER INDEX ... REBUILD。

如果重建索引失败，那么在重建开始之前，索引将停留在之前的版本状态。从这种意义上看，重建索引操作是原子性的。

4）删除索引

如果有索引表的话，删除一个索引将会删除这个索引表:

DROP INDEX IF EXISTS t1_index ON tb_user;
DROP INDEX IF EXISTS employees_index ON TABLE employees;

Hive不允许用户直接使用DROP TABLE语句之前删除索引表。而通常情况下，IF EXISTS都是可选的，其用于当索引不存在时避免抛出错误信息。如果被索引的表被删除了，那么其对应的索引和索引表也会被删除。同样地，如果原始表的某个分区被删除了，那么这个分区对应的分区索引也同时会被删除掉。

二、DML数据操作

1、数据导入

1） 向表中装载数据（Load）
load data [local] inpath '/opt/module/datas/table_name.txt' [overwrite] | into table table_name
[partition (partcol1=val1,…)];

（1）load data:表示加载数据

（2）local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表

（3）inpath:表示加载数据的路径

（4）overwrite:表示覆盖表中已有数据，否则表示追加

（5）into table:表示加载到哪张表

（6）student:表示具体的表

（7）partition:表示上传到指定分区

2）Load示例

（1）创建一张表

hive (default)> create table student(id string, name string) 
row format delimited fields terminated by '\t';

（2）加载本地文件到 hive

hive (default)>load data local inpath '/opt/module/datas/student.txt' 
into table default.student;

（3）加载 HDFS 文件到 hive 中

上传文件到 HDFS

hive (default)> dfs -put /opt/module/datas/student.txt /user/chaos/hive;

加载 HDFS 上数据

hive (default)>load data inpath '/user/chaos/hive/student.txt' into table default.student;

（4）加载数据覆盖表中已有的数据

上传文件到 HDFS

hive (default)> dfs -put /opt/module/datas/student.txt /user/chaos/hive;

加载数据覆盖表中已有的数据

hive (default)>load data inpath '/user/chaos/hive/student.txt' overwrite into table default.student;
3） 通过查询语句向表中插入数据（Insert）

1．创建一张分区表

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

2．基本插入数据

hive (default)>insert into table student partition(month='202009') values(1,'wangwu');

3．基本模式插入（根据单张表查询结果）

hive (default)> insert overwrite table student partition(month='202008') 
select id, name from student where month='202009';

4．多插入模式（根据多张表查询结果）

from(select * from student
)t 
insert overwrite table student partition(month='202007') 
select id,name where month='202009' 
insert overwrite table student partition(month='202006')
select id, name where month='202009';
4） 查询语句中创建表并加载数据（As Select）

根据查询结果创建表（查询的结果会添加到新创建的表中）

create table if not exists student3 as select id, name from student;
5）创建表时通过 Location 指定加载数据路径

1．创建表，并指定在 hdfs 上的位置

hive (default)> create table if not exists student5(id int, name string)
row format delimited fields terminated by '\t' 
location '/user/hive/warehouse/student5';

2．上传数据到 hdfs 上

hive (default)> dfs -put /opt/module/datas/student.txt /user/hive/warehouse/student5;

3．查询数据

hive (default)> select * from student5;
6） Import 数据到指定 Hive 表中

注意：先用 export 导出后，再将数据导入。

hive (default)> import table student2 partition(month='202009') from '/user/hive/warehouse/export/student';

2、 数据导出

1）Insert 导出

（1）将查询的结果导出到本地

hive (default)> insert overwrite local directory '/opt/module/datas/export/student' 
select * from student;

（2）将查询的结果格式化导出到本地

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

（3）将查询的结果导出到 HDFS 上(与导出到本地的区别是没有 local)

hive (default)>insert overwrite directory '/user/chaos/student2' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;
2 ）Hadoop命令导出到本地
hive (default)>dfs -get /user/hive/warehouse/student/month=202009/000000_0 /opt/module/datas/export/student3.txt;
3） Hive Shell 命令导出基本语法：

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

[chaos@hadoop102 hive]$ bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;
4）Export 导出到 HDFS 上
hive(default)> export table default.student to '/user/hive/warehouse/export/student';

3、 清除表中数据（Truncate）

注意：Truncate 只能删除管理表，不能删除外部表中数据

hive (default)> truncate table student;

三、查询

查询语句语法：

[WITH CommonTableExpression (, CommonTableExpression)*](Note: Only available starting with Hive 0.13.0)
  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]

1、 基本查询（Select…From）

1） 全表和特定列查询

全表查询

hive (default)> select * from emp;

选择特定列查询

hive (default)> select empno, ename from emp;

注意：

（1）SQL 语言大小写不敏感。

（2）SQL 可以写在一行或者多行

（3）关键字不能被缩写也不能分行

（4）各子句一般要分行写。

（5）使用缩进提高语句的可读性。

2） 列别名

1．重命名一个列

2．便于计算

3．紧跟列名，也可以在列名和别名之间加入关键字‘AS’

4．中文别名需要使用反引号 `

5．案例实操查询名称和部门，创建时间

select 
	ename AS name,
	deptno dn,
	fcd as `创建时间`
from emp;
3） 算术运算符
运算符 描述
A+B A 和 B 相加
A-B A 减去 B
A*B A 和 B 相乘
A/B A 除以 B
A%B A 对 B 取余
A&B A 和 B 按位取与
A|B A 和 B 按位取或
A^B A 和 B 按位取异或
4）常用函数

1．求总数（count）

select count(*) cnt from emp;

2．求工资的最大值（max）

select max(sal) max_sal from emp;

3．求部门工资的最小值（min）

select deptno，min(sal) min_sal from emp group by deptno;

4．求工资的总和（sum）

select sum(sal) sum_sal from emp;

5．求工资的平均值（avg）

 select avg(sal) avg_sal from emp;
5 ）Limit 语句

典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数。

select * from emp limit 5;

2、Where 语句

1．使用 WHERE 子句，将不满足条件的行过滤掉

2．WHERE 子句紧随 FROM 子句

1） 比较运算符（Between/In/ Is Null）

下面表中描述了谓词操作符，这些操作符同样可以用于 JOIN…ON 和 HAVING 语句中。

操作符 支持的数据类型 描述
A=B 基本数据类型 如果 A 等于 B 则返回 TRUE，反之返回 FALSE
A<=>B 基本数据类型 如果 A 和 B 都为 NULL，则返回 TRUE，其他的和等号（=）操作符的结果一致，如果任一为 NULL 则结果为 NULL
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 STRING 类型 B 是一个 SQL 下的简单正则表达式，如果 A 与其匹配的话，则返回 TRUE；反之返回 FALSE。B 的表达式说明如下：‘x%’表示 A 必须以字母‘x’开头，‘%x’表示 A 必须以字母’x’结尾，而 ‘%x%’表示 A 包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用 NOT 关键字则可达到相反的效果。
A RLIKE B, A REGEXP B STRING 类型 B 是一个正则表达式，如果 A 与其匹配，则返回 TRUE；反之返回 FALSE。匹配使用的是 JDK 中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串 A 相匹配，而不是只需与其字符串匹配。
2） Like 和 RLike

1．使用 LIKE 运算选择类似的值

2．选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)。

_ 代表一个字符。

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

eg:

（1）查找以 2 开头薪水的员工信息

select * from emp where sal LIKE '2%';

（2）查找第二个数值为 2 的薪水的员工信息

select * from emp where sal LIKE '_2%';

（3）查找薪水中含有 2 的员工信息

select * from emp where sal RLIKE '[2]';

3）逻辑运算符（And/Or/Not）
操作符 含义
AND 逻辑并
OR 逻辑或
NOT 逻辑否

3、分组查询

1）Group By 语句

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

eg：

（1）计算 emp 表每个部门的平均工资

select t.deptno, avg(t.sal) avg_sal

from emp t

group by t.deptno;

（2）计算 emp 每个部门中每个岗位的最高薪水

select t.deptno, t.job, max(t.sal) max_sal

from emp t

group by t.deptno, t.job;

2） Having 语句

having 与 where 不同点

（1）where 针对表中的列发挥作用，查询数据；having 针对查询结果中的列发挥作用，筛选数据。

（2）where 后面不能写聚合函数，而 having 后面可以使用聚合函数。

（3）having 只用于 group by 分组统计语句。

eg：

求每个部门的平均薪水大于 2000 的部门求每个部门的平均工资

select deptno, avg(sal)

from emp

group by deptno;

求每个部门的平均薪水大于 2000 的部门

select deptno, avg(sal) avg_sal

from emp

group by deptno

having avg_sal > 2000;

4、 Join 语句

1）等值 Join

Hive 支持通常的 SQL JOIN 语句，但是只支持等值连接，不支持非等值连接。

eg:

select

​ e.empno,

​ e.ename,

​ d.deptno,

​ d.dname

from emp e

join dept d on e.deptno != d.deptno;

会报错，'>'和'<' 这种也不支持

2） 表的别名

1．好处

（1）使用别名可以简化查询。

（2）使用表名前缀可以提高执行效率。<提高的不多，不过也算可以优化提高的点，同时也增加sql的可读性>

3） 内连接

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

4） 左外连接左外连接

JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。

5）右外连接右外连接

JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。

6） 满外连接

将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代。

7） 多表连接

注意：连接 n 个表，至少需要 n-1 个连接条件。

例如：连接三个表，至少需要两个连接条件。

eg:

1．创建位置表

create table if not exists default.location( loc int, loc_name string) row format delimited fields terminated by '\t';

2．导入数据

hive (default)>load data local inpath '/opt/module/data/location.txt' into table default.location;

3．多表连接查询

SELECT e.ename, d.deptno, 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;进行连接操作。

注意：为什么不是表 d 和表 l 先进行连接操作呢？这是因为 Hive 总是按照从左到右的顺序执行的。

8） 笛卡尔积

1．笛卡尔集会在下面条件下产生

（1）省略连接条件

（2）连接条件无效

（3）所有表中的所有行互相连接

注意：开启严格模式的话，笛卡尔积这种查询会报错

9） 连接谓词中不支持 or 
select
	e.empno,
	e.ename,
	d.deptno
from emp e 
join dept d on e.deptno=d.deptno or e.ename=d.dname;

FAILED: SemanticException [Error 10019]: Line 10:3 OR not supported in JOIN currently 'dname'

5、排序

1）全局排序（Order By）

Order By：全局排序，一个 Reducer

1．使用 ORDER BY 子句排序

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

2．ORDER BY 子句在 SELECT 语句的结尾

2） 按照别名排序

eg:

按照员工薪水的 2 倍排序

select ename, sal*2 twosal from emp order by twosal;

3） 多个列排序

eg:

按照部门升序和工资降序排序

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

4） 每个 MapReduce 内部排序/区内排序（Sort By）

Sort By：每个 Reducer 内部进行排序，对全局结果集来说不是排序。

eg:

1．设置 reduce 个数

hive (default)> set mapreduce.job.reduces=3;

2．查看设置 reduce 个数

hive (default)> set mapreduce.job.reduces;

3．根据部门编号降序查看员工信息

hive (default)> select * from emp sort by empno desc;

4．将查询结果导入到文件中（按照部门编号降序排序）

hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result' select * from emp sort by deptno desc;

5） 分区排序（Distribute By）

Distribute By：类似 MR 中 partition，进行分区，结合 sort by 使用。

注意，Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。

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

eg：

根据部门编号查看每个部门，再根据员工编号降序查看每个部门中员工

先按照部门编号分区，再按照员工编号降序排序。

hive (default)> set mapreduce.job.reduces=3;

hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

6） Cluster By

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

cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。

以下两种写法等价

hive (default)> select * from emp cluster by deptno; 
hive (default)> select * from emp distribute by deptno sort by deptno;

注意：按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一个分区里面去。

6） 分桶及抽样查询<很少用>

分区针对的是数据的存储路径；分桶针对的是数据文件。

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。

分桶是将数据集分解成更容易管理的若*分的另一个技术。

eg:

先创建分桶表，通过直接导入数据文件的方式

（1）数据准备

student.txt

（2）创建分桶表,和一个普通表

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

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

向普通的 stu 表中导入数据

load data local inpath '/opt/module/datas/student.txt' into table stu;

（3）查看表结构

hive (default)> desc formatted stu_buck;

Num Buckets: 4

（4）设置属性,通过子查询的方式导入数据

hive (default)> set hive.enforce.bucketing=true;

hive (default)> set mapreduce.job.reduces=-1;

hive (default)> insert into table stu_buck select id, name from stu;

分桶抽样查询

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

hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

注：tablesample 是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。

y 必须是 table 总 bucket 数的倍数或者因子。hive 根据 y 的大小，决定抽样的比例。例如，table 总共分了 4 份，当 y=2 时，抽取(4/2=)2 个 bucket 的数据，当 y=8 时，抽取(4/8=)1/2 个 bucket 的数据。

x 表示从哪个 bucket 开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上 y。

例如，table 总 bucket 数为 4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2 个 bucket的数据，抽取第 1(x)个和第 3(x+y)个 bucket 的数据。

注意：x 的值必须小于等于 y 的值，否则

FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

6、常用查询函数

1）条件函数
Return Type Name(Signature) Description
T if(boolean testCondition, T valueTrue, T valueFalseOrNull) Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise.如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull ，（valueTrue，valueFalseOrNull为泛型）
T nvl(T value, T default_value) Returns default value if value is null else returns value (as of HIve 0.11).如果value值为NULL就返回default_value,否则返回value
T COALESCE(T v1, T v2, ...) Returns the first v that is not NULL, or NULL if all v's are NULL.返回第一非null的值，如果全部都为NULL就返回NULL 如：COALESCE (NULL,44,55)=44/strong>
T CASE a WHEN b THEN c [WHEN d THEN e] [ELSE f] END* When a = b, returns c; when a = d, returns e; else returns f.如果a=b就返回c,a=d就返回e，否则返回f 如CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END 将返回4
T CASE WHEN a THEN b [WHEN c THEN d] [ELSE e] END* When a = true, returns b; when c = true, returns d; else returns e.如果a=ture就返回b,c= ture就返回d，否则返回e 如：CASE WHEN 5>0 THEN 5 WHEN 4>0 THEN 4 ELSE 0 END 将返回5；CASE WHEN 5<0 THEN 5 WHEN 4<0 THEN 4 ELSE 0 END 将返回0
boolean isnull( a ) Returns true if a is NULL and false otherwise.如果a为null就返回true，否则返回false
boolean isnotnull ( a ) Returns true if a is not NULL and false otherwise.如果a为非null就返回true，否则返回false
2）数学函数
Return Type Name (Signature) Description
DOUBLE round(DOUBLE a) Returns the rounded BIGINT value of a.返回对a四舍五入的BIGINT值
DOUBLE round(DOUBLE a, INT d) Returns a rounded to d decimal places.返回DOUBLE型d的保留n位小数的DOUBLW型的近似值
DOUBLE bround(DOUBLE a) Returns the rounded BIGINT value of a using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0). Also known as Gaussian rounding or bankers' rounding. Example: bround(2.5) = 2, bround(3.5) = 4. 银行家舍入法（14：舍，69：进，5->前位数是偶：舍，5->前位数是奇：进）
DOUBLE bround(DOUBLE a, INT d) Returns a rounded to d decimal places using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0). Example: bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4. 银行家舍入法,保留d位小数
BIGINT floor(DOUBLE a) Returns the maximum BIGINT value that is equal to or less than a向下取整，最数轴上最接近要求的值的左边的值 如：6.10->6 -3.4->-4
BIGINT ceil(DOUBLE a), ceiling(DOUBLE a) Returns the minimum BIGINT value that is equal to or greater than a.求其不小于小给定实数的最小整数如：ceil(6) = ceil(6.1)= ceil(6.9) = 6
DOUBLE rand(), rand(INT seed) Returns a random number (that changes from row to row) that is distributed uniformly from 0 to 1. Specifying the seed will make sure the generated random number sequence is deterministic.每行返回一个DOUBLE型随机数seed是随机因子
DOUBLE exp(DOUBLE a), exp(DECIMAL a) Returns ea where e is the base of the natural logarithm. Decimal version added in Hive 0.13.0.返回e的a幂次方， a可为小数
DOUBLE ln(DOUBLE a), ln(DECIMAL a) Returns the natural logarithm of the argument a. Decimal version added in Hive 0.13.0.以自然数为底d的对数，a可为小数
DOUBLE log10(DOUBLE a), log10(DECIMAL a) Returns the base-10 logarithm of the argument a. Decimal version added in Hive 0.13.0.以10为底d的对数，a可为小数
DOUBLE log2(DOUBLE a), log2(DECIMAL a) Returns the base-2 logarithm of the argument a. Decimal version added in Hive 0.13.0.以2为底数d的对数，a可为小数
DOUBLE log(DOUBLE base, DOUBLE a)log(DECIMAL base, DECIMAL a) Returns the base-base logarithm of the argument a. Decimal versions added in Hive 0.13.0.以base为底的对数，base 与 a都是DOUBLE类型
DOUBLE pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p) Returns ap.计算a的p次幂
DOUBLE sqrt(DOUBLE a), sqrt(DECIMAL a) Returns the square root of a. Decimal version added in Hive 0.13.0.计算a的平方根
STRING bin(BIGINT a) Returns the number in binary format (see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_bin).计算二进制a的STRING类型，a为BIGINT类型
STRING hex(BIGINT a) hex(STRING a) hex(BINARY a) If the argument is an INT or binary, hex returns the number as a STRING in hexadecimal format. Otherwise if the number is a STRING, it converts each character into its hexadecimal representation and returns the resulting STRING. (Seehttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex, BINARY version as of Hive 0.12.0.)计算十六进制a的STRING类型，如果a为STRING类型就转换成字符相对应的十六进制
BINARY unhex(STRING a) Inverse of hex. Interprets each pair of characters as a hexadecimal number and converts to the byte representation of the number. (BINARY version as of Hive 0.12.0, used to return a string.)hex的逆方法
STRING conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base) Converts a number from a given base to another (see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv).将GIGINT/STRING类型的num从from_base进制转换成to_base进制
DOUBLE abs(DOUBLE a) Returns the absolute value.计算a的绝对值
INT or DOUBLE pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b) Returns the positive value of a mod b.a对b取模
DOUBLE sin(DOUBLE a), sin(DECIMAL a) Returns the sine of a (a is in radians). Decimal version added in Hive 0.13.0.求a的正弦值
DOUBLE asin(DOUBLE a), asin(DECIMAL a) Returns the arc sin of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0.求d的反正弦值
DOUBLE cos(DOUBLE a), cos(DECIMAL a) Returns the cosine of a (a is in radians). Decimal version added in Hive 0.13.0.求余弦值
DOUBLE acos(DOUBLE a), acos(DECIMAL a) Returns the arccosine of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0.求反余弦值
DOUBLE tan(DOUBLE a), tan(DECIMAL a) Returns the tangent of a (a is in radians). Decimal version added in Hive 0.13.0.求正切值
DOUBLE atan(DOUBLE a), atan(DECIMAL a) Returns the arctangent of a. Decimal version added in Hive 0.13.0.求反正切值
DOUBLE degrees(DOUBLE a), degrees(DECIMAL a) Converts value of a from radians to degrees. Decimal version added in Hive 0.13.0.奖弧度值转换角度值
DOUBLE radians(DOUBLE a), radians(DOUBLE a) Converts value of a from degrees to radians. Decimal version added in Hive 0.13.0.将角度值转换成弧度值
INT or DOUBLE positive(INT a), positive(DOUBLE a) Returns a.返回a
INT or DOUBLE negative(INT a), negative(DOUBLE a) Returns -a.返回a的相反数
DOUBLE or INT sign(DOUBLE a), sign(DECIMAL a) Returns the sign of a as '1.0' (if a is positive) or '-1.0' (if a is negative), '0.0' otherwise. The decimal version returns INT instead of DOUBLE. Decimal version added in Hive 0.13.0.如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0
DOUBLE e() Returns the value of e.数学常数e
DOUBLE pi() Returns the value of pi.数学常数pi
BIGINT factorial(INT a) Returns the factorial of a (as of Hive 1.2.0). Valid a is [0..20]. 求a的阶乘
DOUBLE cbrt(DOUBLE a) Returns the cube root of a double value (as of Hive 1.2.0). 求a的立方根
INT BIGINT shiftleft(TINYINT|SMALLINT|INT a, INT b)shiftleft(BIGINT a, INT b) Bitwise left shift (as of Hive 1.2.0). Shifts a b positions to the left.Returns int for tinyint, smallint and int a. Returns bigint for bigint a.按位左移
INTBIGINT shiftright(TINYINT|SMALLINT|INT a, INTb)shiftright(BIGINT a, INT b) Bitwise right shift (as of Hive 1.2.0). Shifts a b positions to the right.Returns int for tinyint, smallint and int a. Returns bigint for bigint a.按拉右移
INTBIGINT shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b),shiftrightunsigned(BIGINT a, INT b) Bitwise unsigned right shift (as of Hive 1.2.0). Shifts a b positions to the right.Returns int for tinyint, smallint and int a. Returns bigint for bigint a.无符号按位右移（<<<）
T greatest(T v1, T v2, ...) Returns the greatest value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with ">" operator (as of Hive 2.0.0). 求最大值
T least(T v1, T v2, ...) Returns the least value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with "<" operator (as of Hive 2.0.0). 求最小值
3）集合函数
Return Type Name(Signature) Description
int size(Map<K.V>) Returns the number of elements in the map type.求map的长度
int size(Array) Returns the number of elements in the array type.求数组的长度
array map_keys(Map<K.V>) Returns an unordered array containing the keys of the input map.返回map中的所有key
array map_values(Map<K.V>) Returns an unordered array containing the values of the input map.返回map中的所有value
boolean array_contains(Array, value) Returns TRUE if the array contains value.如该数组Array包含value返回true。，否则返回false
array sort_array(Array) Sorts the input array in ascending order according to the natural ordering of the array elements and returns it (as of version 0.9.0).按自然顺序对数组进行排序并返回
4）类型转换函数
Return Type **Name(Signature) ** Description
binary binary(string|binary) Casts the parameter into a binary.将输入的值转换成二进制
Expected "=" to follow "type" cast(expr as ) Converts the results of the expression expr to . For example, cast('1' as BIGINT) will convert the string '1' to its integral representation. A null is returned if the conversion does not succeed. If cast(expr as boolean) Hive returns true for a non-empty string.将expr转换成type类型 如：cast("1" as BIGINT) 将字符串1转换成了BIGINT类型，如果转换失败将返回NULL
5）日期函数
Return Type Name(Signature) Description
string from_unixtime(bigint unixtime[, string format]) Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the format of "1970-01-01 00:00:00".将时间的秒值转换成format格式（format可为“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等）如from_unixtime(1250111000,"yyyy-MM-dd") 得到2009-03-12
bigint unix_timestamp() Gets current Unix timestamp in seconds.获取本地时区下的时间戳
bigint unix_timestamp(string date) Converts time string in format yyyy-MM-dd HH:mm:ss to Unix timestamp (in seconds), using the default timezone and the default locale, return 0 if fail: unix_timestamp('2009-03-20 11:30:01') = 1237573801将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳 如unix_timestamp('2009-03-20 11:30:01') = 1237573801
bigint unix_timestamp(string date, string pattern) Convert time string with given pattern (see [http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html]) to Unix time stamp (in seconds), return 0 if fail: unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400.将指定时间字符串格式字符串转换成Unix时间戳，如果格式不对返回0 如：unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400
string to_date(string timestamp) Returns the date part of a timestamp string: to_date("1970-01-01 00:00:00") = "1970-01-01".返回时间字符串的日期部分
int year(string date) Returns the year part of a date or a timestamp string: year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970.返回时间字符串的年份部分
int quarter(date/timestamp/string) Returns the quarter of the year for a date, timestamp, or string in the range 1 to 4 (as of Hive 1.3.0). Example: quarter('2020-04-08') = 2.返回当前时间属性哪个季度 如quarter('2020-04-08') = 2
int month(string date) Returns the month part of a date or a timestamp string: month("1970-11-01 00:00:00") = 11, month("1970-11-01") = 11.返回时间字符串的月份部分
int day(string date) dayofmonth(date) Returns the day part of a date or a timestamp string: day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1.返回时间字符串的天
int hour(string date) Returns the hour of the timestamp: hour('2009-07-30 12:58:59') = 12, hour('12:58:59') = 12.返回时间字符串的小时
int minute(string date) Returns the minute of the timestamp.返回时间字符串的分钟
int second(string date) Returns the second of the timestamp.返回时间字符串的秒
int weekofyear(string date) Returns the week number of a timestamp string: weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44.返回时间字符串位于一年中的第几个周内 如weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44
int datediff(string enddate, string startdate) Returns the number of days from startdate to enddate: datediff('2009-03-01', '2009-02-27') = 2.计算开始时间startdate到结束时间enddate相差的天数
string date_add(string startdate, int days) Adds a number of days to startdate: date_add('2008-12-31', 1) = '2009-01-01'.从开始时间startdate加上days
string date_sub(string startdate, int days) Subtracts a number of days to startdate: date_sub('2008-12-31', 1) = '2008-12-30'.从开始时间startdate减去days
timestamp from_utc_timestamp(timestamp, string timezone) Assumes given timestamp is UTC and converts to given timezone (as of Hive 0.8.0). For example, from_utc_timestamp('1970-01-01 08:00:00','PST') returns 1970-01-01 00:00:00.如果给定的时间戳并非UTC，则将其转化成指定的时区下时间戳
timestamp to_utc_timestamp(timestamp, string timezone) Assumes given timestamp is in given timezone and converts to UTC (as of Hive 0.8.0). For example, to_utc_timestamp('1970-01-01 00:00:00','PST') returns 1970-01-01 08:00:00.如果给定的时间戳指定的时区下时间戳，则将其转化成UTC下的时间戳
date current_date Returns the current date at the start of query evaluation (as of Hive 1.2.0). All calls of current_date within the same query return the same value.返回当前时间日期
timestamp current_timestamp Returns the current timestamp at the start of query evaluation (as of Hive 1.2.0). All calls of current_timestamp within the same query return the same value.返回当前时间戳
string add_months(string start_date, int num_months) Returns the date that is num_months after start_date (as of Hive 1.1.0). start_date is a string, date or timestamp. num_months is an integer. The time part of start_date is ignored. If start_date is the last day of the month or if the resulting month has fewer days than the day component of start_date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as start_date.返回当前时间下再增加num_months个月的日期
string last_day(string date) Returns the last day of the month which the date belongs to (as of Hive 1.1.0). date is a string in the format 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'. The time part of date is ignored.返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss）
string next_day(string start_date, string day_of_week) Returns the first date which is later than start_date and named as day_of_week (as of Hive1.2.0). start_date is a string/date/timestamp. day_of_week is 2 letters, 3 letters or full name of the day of the week (e.g. Mo, tue, FRIDAY). The time part of start_date is ignored. Example: next_day('2020-01-14', 'TU') = 2020-01-20.返回当前时间的下一个星期X所对应的日期 如：next_day('2020-01-14', 'TU') = 2020-01-20 以2020-01-14为开始时间，其下一个星期二所对应的日期为2020-01-20
string trunc(string date, string format) Returns date truncated to the unit specified by the format (as of Hive 1.2.0). Supported formats: MONTH/MON/MM, YEAR/YYYY/YY. Example: trunc('2020-03-17', 'MM') = 2020-03-01.返回时间的最开始年份或月份 如trunc("2016-06-26",“MM”)=2016-06-01 trunc("2016-06-26",“YY”)=2016-01-01 注意所支持的格式为MONTH/MON/MM, YEAR/YYYY/YY
double months_between(date1, date2) Returns number of months between dates date1 and date2 (as of Hive 1.2.0). If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise the UDF calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2. date1 and date2 type can be date, timestamp or string in the format 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss'. The result is rounded to 8 decimal places. Example: months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677返回date1与date2之间相差的月份，如date1>date2，则返回正，如果date1<date2,则返回负，否则返回0.0 如：months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677 1997-02-28 10:30:00与1996-10-30相差3.94959677个月
string date_format(date/timestamp/string ts, string fmt) Converts a date/timestamp/string to a value of string in the format specified by the date format fmt (as of Hive 1.2.0). Supported formats are Java SimpleDateFormat formats –https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html. The second argument fmt should be constant. Example: date_format('2020-04-08', 'y') = '2020'.date_format can be used to implement other UDFs, e.g.:dayname(date) is date_format(date, 'EEEE')dayofyear(date) is date_format(date, 'D')按指定格式返回时间date 如：date_format("2016-06-22","MM-dd")=06-22
6）字符函数
eturn Type Name(Signature) Description
int ascii(string str) Returns the numeric value of the first character of str.返回str中首个ASCII字符串的整数值
string base64(binary bin) Converts the argument from binary to a base 64 string (as of Hive 0.12.0)..将二进制bin转换成64位的字符串
string concat(string|binary A, string|binary B...) Returns the string or bytes resulting from concatenating the strings or bytes passed in as parameters in order. For example, concat('foo', 'bar') results in 'foobar'. Note that this function can take any number of input strings..对二进制字节码或字符串按次序进行拼接
array<struct<string,double>> context_ngrams(array<array>, array, int K, int pf) Returns the top-k contextual N-grams from a set of tokenized sentences, given a string of "context". See StatisticsAndDataMining for more information..与ngram类似，但context_ngram()允许你预算指定上下文(数组)来去查找子序列，具体看StatisticsAndDataMining(这里的解释更易懂)
string concat_ws(string SEP, string A, string B...) Like concat() above, but with custom separator SEP..与concat()类似，但使用指定的分隔符喜进行分隔
string concat_ws(string SEP, array) Like concat_ws() above, but taking an array of strings. (as of Hive 0.9.0).拼接Array中的元素并用指定分隔符进行分隔
string decode(binary bin, string charset) Decodes the first argument into a String using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If either argument is null, the result will also be null. (As of Hive 0.12.0.).使用指定的字符集charset将二进制值bin解码成字符串，支持的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任意输入参数为NULL都将返回NULL
binary encode(string src, string charset) Encodes the first argument into a BINARY using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If either argument is null, the result will also be null. (As of Hive 0.12.0.).使用指定的字符集charset将字符串编码成二进制值，支持的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任一输入参数为NULL都将返回NULL
int find_in_set(string str, string strList) Returns the first occurance of str in strList where strList is a comma-delimited string. Returns null if either argument is null. Returns 0 if the first argument contains any commas. For example, find_in_set('ab', 'abc,b,ab,c,def') returns 3..返回以逗号分隔的字符串中str出现的位置，如果参数str为逗号或查找失败将返回0，如果任一参数为NULL将返回NULL回
string format_number(number x, int d) Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. (As of Hive 0.10.0; bug with float types fixed in Hive 0.14.0, decimal type support added in Hive 0.14.0).将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数
string get_json_object(string json_string, string path) Extracts json object from a json string based on json path specified, and returns json string of the extracted json object. It will return null if the input json string is invalid. NOTE: The json path can only have the characters [0-9a-z_], i.e., no upper-case or special characters. Also, the keys *cannot start with numbers.* This is due to restrictions on Hive column names..从指定路径上的JSON字符串抽取出JSON对象，并返回这个对象的JSON格式，如果输入的JSON是非法的将返回NULL,注意此路径上JSON字符串只能由数字 字母 下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制
boolean in_file(string str, string filename) Returns true if the string str appears as an entire line in filename..如果文件名为filename的文件中有一行数据与字符串str匹配成功就返回true
int instr(string str, string substr) Returns the position of the first occurrence of substr in str. Returns null if either of the arguments are null and returns 0 if substr could not be found in str. Be aware that this is not zero based. The first character in str has index 1..查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的
int length(string A) Returns the length of the string..返回字符串的长度
int locate(string substr, string str[, int pos]) Returns the position of the first occurrence of substr in str after position pos..查找字符串str中的pos位置后字符串substr第一次出现的位置
string lower(string A) lcase(string A) Returns the string resulting from converting all characters of B to lower case. For example, lower('fOoBaR') results in 'foobar'..将字符串A的所有字母转换成小写字母
string lpad(string str, int len, string pad) Returns str, left-padded with pad to a length of len..从左边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分
string ltrim(string A) Returns the string resulting from trimming spaces from the beginning(left hand side) of A. For example, ltrim(' foobar ') results in 'foobar '..去掉字符串A前面的空格
array<struct<string,double>> ngrams(array<array>, int N, int K, int pf) Returns the top-k N-grams from a set of tokenized sentences, such as those returned by the sentences() UDAF. See StatisticsAndDataMining for more information..返回出现次数TOP K的的子序列,n表示子序列的长度，具体看StatisticsAndDataMining (这里的解释更易懂)
string parse_url(string urlString, string partToExtract [, string keyToExtract]) Returns the specified part from the URL. Valid values for partToExtract include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO. For example, parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') returns 'facebook.com'. Also a value of a particular key in QUERY can be extracted by providing the key as the third argument, for example, parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') returns 'v1'..返回从URL中抽取指定部分的内容，参数url是URL字符串，而参数partToExtract是要抽取的部分，这个参数包含(HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO,例如：parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') ='facebook.com'，如果参数partToExtract值为QUERY则必须指定第三个参数key 如：parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') =‘v1’
string printf(String format, Obj... args) Returns the input formatted according do printf-style format strings (as of Hive0.9.0)..按照printf风格格式输出字符串
string regexp_extract(string subject, string pattern, int index) Returns the string extracted using the pattern. For example, regexp_extract('foothebar', 'foo(.*?)(bar)', 2) returns 'bar.' Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\s' is necessary to match whitespace, etc. The 'index' parameter is the Java regex Matcher group() method index. See docs/api/java/util/regex/Matcher.html for more information on the 'index' or Java regex group() method..抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串，注意些预定义字符的使用，如第二个参数如果使用'\s'将被匹配到s,'\s'才是匹配空格
string regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT) Returns the string resulting from replacing all substrings in INITIAL_STRING that match the java regular expression syntax defined in PATTERN with instances of REPLACEMENT. For example, regexp_replace("foobar", "oo|ar", "") returns 'fb.' Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\s' is necessary to match whitespace, etc..按照Java正则表达式PATTERN将字符串INTIAL_STRING中符合条件的部分成REPLACEMENT所指定的字符串，如里REPLACEMENT这空的话，抽符合正则的部分将被去掉 如：regexp_replace("foobar", "oo|ar", "") = 'fb.' 注意些预定义字符的使用，如第二个参数如果使用'\s'将被匹配到s,'\s'才是匹配空格
string repeat(string str, int n) Repeats str n times..重复输出n次字符串str
string reverse(string A) Returns the reversed string..反转字符串
string rpad(string str, int len, string pad) Returns str, right-padded with pad to a length of len..从右边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分
string rtrim(string A) Returns the string resulting from trimming spaces from the end(right hand side) of A. For example, rtrim(' foobar ') results in ' foobar'..去掉字符串后面出现的空格
array<array> sentences(string str, string lang, string locale) Tokenizes a string of natural language text into words and sentences, where each sentence is broken at the appropriate sentence boundary and returned as an array of words. The 'lang' and 'locale' are optional arguments. For example, sentences('Hello there! How are you?') returns ( ("Hello", "there"), ("How", "are", "you") )..字符串str将被转换成单词数组，如：sentences('Hello there! How are you?') =( ("Hello", "there"), ("How", "are", "you") )
string space(int n) Returns a string of n spaces..返回n个空格
array split(string str, string pat) Splits str around pat (pat is a regular expression)..按照正则表达式pat来分割字符串str,并将分割后的数组字符串的形式返回
map<string,string> str_to_map(text[, delimiter1, delimiter2]) Splits text into key-value pairs using two delimiters. Delimiter1 separates text into K-V pairs, and Delimiter2 splits each K-V pair. Default delimiters are ',' for delimiter1 and '=' for delimiter2..将字符串str按照指定分隔符转换成Map，第一个参数是需要转换字符串，第二个参数是键值对之间的分隔符，默认为逗号;第三个参数是键值之间的分隔符，默认为"="
string substr(string|binary A, int start) substring(string|binary A, int start) Returns the substring or slice of the byte array of A starting from start position till the end of string A. For example, substr('foobar', 4) results in 'bar' (see [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr])..对于字符串A,从start位置开始截取字符串并返回
string substr(string|binary A, int start, int len) substring(string|binary A, int start, int len) Returns the substring or slice of the byte array of A starting from start position with length len. For example, substr('foobar', 4, 1) results in 'b' (see [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr])..对于二进制/字符串A,从start位置开始截取长度为length的字符串并返回
string substring_index(string A, string delim, int count) Returns the substring from string A before count occurrences of the delimiter delim (as of Hive 1.3.0). If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. Substring_index performs a case-sensitive match when searching for delim. Example: substring_index('www.apache.org', '.', 2) = 'www.apache'..截取第count分隔符之前的字符串，如count为正则从左边开始截取，如果为负则从右边开始截取
string translate(string|char|varchar input, string|char|varchar from, string|char|varchar to) Translates the input string by replacing the characters present in the from string with the corresponding characters in the to string. This is similar to the translatefunction in PostgreSQL. If any of the parameters to this UDF are NULL, the result is NULL as well. (Available as of Hive 0.10.0, for string types)Char/varchar support added as of Hive 0.14.0..将input出现在from中的字符串替换成to中的字符串 如：translate("MOBIN","BIN","M")="MOM"
string trim(string A) Returns the string resulting from trimming spaces from both ends of A. For example, trim(' foobar ') results in 'foobar'.将字符串A前后出现的空格去掉
binary unbase64(string str) Converts the argument from a base 64 string to BINARY. (As of Hive 0.12.0.).将64位的字符串转换二进制值
string upper(string A) ucase(string A) Returns the string resulting from converting all characters of A to upper case. For example, upper('fOoBaR') results in 'FOOBAR'..将字符串A中的字母转换成大写字母
string initcap(string A) Returns string, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by whitespace. (As of Hive 1.1.0.).将字符串A转换第一个字母大写其余字母的字符串
int levenshtein(string A, string B) Returns the Levenshtein distance between two strings (as of Hive 1.2.0). For example, levenshtein('kitten', 'sitting') results in 3..计算两个字符串之间的差异大小 如：levenshtein('kitten', 'sitting') = 3
string soundex(string A) Returns soundex code of the string (as of Hive 1.2.0). For example, soundex('Miller') results in M460..将普通字符串转换成soundex字符串
7）聚合函数
Return Type Name(Signature) Description
BIGINT count(*), count(expr), count(DISTINCT expr[, expr...]) count(*) - Returns the total number of retrieved rows, including rows containing NULL values.统计总行数，包括含有NULL值的行count(expr) - Returns the number of rows for which the supplied expression is non-NULL.统计提供非NULL的expr表达式值的行数count(DISTINCT expr[, expr]) - Returns the number of rows for which the supplied expression(s) are unique and non-NULL. Execution of this can be optimized with hive.optimize.distinct.rewrite.统计提供非NULL且去重后的expr表达式值的行数
DOUBLE sum(col), sum(DISTINCT col) Returns the sum of the elements in the group or the sum of the distinct values of the column in the group.sum(col),表示求指定列的和，sum(DISTINCT col)表示求去重后的列的和
DOUBLE avg(col), avg(DISTINCT col) Returns the average of the elements in the group or the average of the distinct values of the column in the group.avg(col),表示求指定列的平均值，avg(DISTINCT col)表示求去重后的列的平均值
DOUBLE min(col) Returns the minimum of the column in the group.求指定列的最小值
DOUBLE max(col) Returns the maximum value of the column in the group.求指定列的最大值
DOUBLE variance(col), var_pop(col) Returns the variance of a numeric column in the group.求指定列数值的方差
DOUBLE var_samp(col) Returns the unbiased sample variance of a numeric column in the group.求指定列数值的样本方差
DOUBLE stddev_pop(col) Returns the standard deviation of a numeric column in the group.求指定列数值的标准偏差
DOUBLE stddev_samp(col) Returns the unbiased sample standard deviation of a numeric column in the group.求指定列数值的样本标准偏差
DOUBLE covar_pop(col1, col2) Returns the population covariance of a pair of numeric columns in the group.求指定列数值的协方差
DOUBLE covar_samp(col1, col2) Returns the sample covariance of a pair of a numeric columns in the group.求指定列数值的样本协方差
DOUBLE corr(col1, col2) Returns the Pearson coefficient of correlation of a pair of a numeric columns in the group.返回两列数值的相关系数
DOUBLE percentile(BIGINT col, p) Returns the exact pth percentile of a column in the group (does not work with floating point types). p must be between 0 and 1. NOTE: A true percentile can only be computed for integer values. Use PERCENTILE_APPROX if your input is non-integral.返回col的p%分位数
8）表生成函数
Return Type Name(Signature) Description
Array Type explode(array<TYPE> a) For each element in a, generates a row containing that element.对于a中的每个元素，将生成一行且包含该元素
N rows explode(ARRAY) Returns one row for each element from the array..每行对应数组中的一个元素
N rows explode(MAP) Returns one row for each key-value pair from the input map with two columns in each row: one for the key and another for the value. (As of Hive 0.8.0.).每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值
N rows posexplode(ARRAY) Behaves like explode for arrays, but includes the position of items in the original array by returning a tuple of (pos, value). (As of Hive 0.13.0.).与explode类似，不同的是还返回各元素在数组中的位置
N rows stack(INT n, v_1, v_2, ..., v_k) Breaks up v_1, ..., v_k into n rows. Each row will have k/n columns. n must be constant..把M列转换成N行，每行有M/N个字段，其中n必须是个常数
tuple json_tuple(jsonStr, k1, k2, ...) Takes a set of names (keys) and a JSON string, and returns a tuple of values. This is a more efficient version of the get_json_object UDF because it can get multiple keys with just one call..从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值
tuple parse_url_tuple(url, p1, p2, ...) This is similar to the parse_url() UDF but can extract multiple parts at once out of a URL. Valid part names are: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:..返回从URL中抽取指定N部分的内容，参数url是URL字符串，而参数p1,p2,....是要抽取的部分，这个参数包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:
inline(ARRAY<STRUCT[,STRUCT]>) Explodes an array of structs into a table. (As of Hive 0.10.).将结构体数组提取出来并插入到表中
9）其它函数
collect

Hive中collect相关的函数有collect_list和collect_set。
它们都是将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重。还可以利用collect来突破group by的限制，Hive中在group by查询的时候要求出现在select后面的列都必须是出现在group by后面的，即select列必须是作为分组依据的列，但是有的时候我们想根据A进行分组然后随便取出每个分组中的一个B，比如按照用户进行分组，然后随便拿出一个他看过的视频名称：

select username, collect_list(video_name)[0] 
from user_visit_video 
group by username;

注：与之对应的是explode，行转列

Lateral View

lateral view是Hive中提供给表生成函数的结合，它可以解决表生成函数不能添加额外的select列的问题。
lateral view其实就是用来和类似explode这种表生成函数函数联用的，lateral view会将表生成函数生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行进行join来达到连接表生成函数外的select字段的目的。

  • 格式一
lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)*

lateral view在UDTF前使用，表示连接UDTF所分裂的字段。

UDTF(expression)：使用的表生成函数，例如explode()。

tableAlias：表示表生成函数转换的虚拟表的名称。

columnAlias：表示虚拟表的虚拟字段名称，如果分裂之后有一个列，则写一个即可；如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开。

eg:

统计人员表*有多少种爱好、多少个城市?

select count(distinct(myCol1)), count(distinct(myCol2)) from psn

LATERAL VIEW explode(likes) myTable1 AS myCol1

LATERAL VIEW explode(address) myTable2 AS myCol2, myCol3;

  • 格式二
from basetable (lateral view)*

在from子句中使用，一般和格式一搭配使用，这个格式只是说明了lateral view的使用位置。
from子句后面也可以跟多个lateral view语句，使用空格间隔就可以了。

  • 格式三
from basetable (lateral view outer)*

它比格式二只是多了一个outer，这个outer的作用是在表生成函数转换列的时候将其中的空也给展示出来，UDTF默认是忽略输出空的，加上outer之后，会将空也输出，显示为NULL。这个功能是在Hive0.12是开始支持的。

7、窗口函数

普通的聚合函数聚合的行集是组,窗口函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值，而窗口函数则可为窗口中的每行都返回一个值。简单理解就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。 窗口函数一般分为两类——聚合窗口函数和排序窗口函数。

窗口函数的调用格式为：

函数名(列) OVER(选项)

OVER 关键字表示把函数当成窗口函数而不是聚合函数。SQL标准允许将所有聚合函数用做窗口函数，使用OVER关键字来区分这两种用法。

OVER()

指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；

在over()里面用的：

​ CURRENT ROW：当前行；

​ n PRECEDING：往前 n 行数据；

​ n FOLLOWING：往后 n 行数据；

​ UNBOUNDED：起点，

​ UNBOUNDED PRECEDING 表示从前面的起点，

​ UNBOUNDED FOLLOWING 表示到后面的终点；

在over()前用的:

​ LAG(col,n)：往前第 n 行数据；

​ LEAD(col,n)：往后第 n 行数据；

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

聚合窗口函数
SUM

1．数据准备：

cookie1,2020-04-10,1
cookie1,2020-04-11,5
cookie1,2020-04-12,7
cookie1,2020-04-13,3
cookie1,2020-04-14,2
cookie1,2020-04-15,4
cookie1,2020-04-16,4

2．创建本地 business.txt，导入数据

[chaos@hadoop102 datas]$ vi cookie1.txt

3．创建 hive 表并导入数据
create table cookie1(cookieid string, createtime string, pv int) row format delimited fields terminated by ',';
load data local inpath "/opt/module/datas/cookie1.txt" into table cookie1;

4．查询示例

select
cookieid,
createtime,
pv,
sum(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1,
sum(pv) over (partition by cookieid order by createtime) as pv2,
sum(pv) over (partition by cookieid) as pv3,
sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4,
sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5,
sum(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from cookie1;

说明：

pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
pv2: 同pv1
pv3: 分组内(cookie1)所有的pv累加
pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号， 13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号
pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，14号=14号+15号+16号=2+4+4=10

如果不指定ROWS BETWEEN,默认为从起点到当前行
如果不指定ORDER BY，则将分组内所有值累加
注：其他AVG，MIN，MAX，和SUM用法一样

AVG

select
cookieid,
createtime,
pv,
avg(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1, -- 默认为从起点到当前行
avg(pv) over (partition by cookieid order by createtime) as pv2, --从起点到当前行，结果同pv1
avg(pv) over (partition by cookieid) as pv3, --分组内所有行
avg(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4, --当前行+往前3行
avg(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5, --当前行+往前3行+往后1行
avg(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6 --当前行+往后所有行
from cookie1;

MIN

select
cookieid,
createtime,
pv,
min(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1, -- 默认为从起点到当前行
min(pv) over (partition by cookieid order by createtime) as pv2, --从起点到当前行，结果同pv1
min(pv) over (partition by cookieid) as pv3, --分组内所有行
min(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4, --当前行+往前3行
min(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5, --当前行+往前3行+往后1行
min(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6 --当前行+往后所有行
from cookie1;

MAX

select
cookieid,
createtime,
pv,
max(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1, -- 默认为从起点到当前行
max(pv) over (partition by cookieid order by createtime) as pv2, --从起点到当前行，结果同pv1
max(pv) over (partition by cookieid) as pv3, --分组内所有行
max(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4, --当前行+往前3行
max(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5, --当前行+往前3行+往后1行
max(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6 --当前行+往后所有行
from cookie1;

排序窗口函数（序列函数）

表示根据COL1分组，在分组内部根据COL2排序， 而此函数计算的值就表示每组内部排序后的顺序编号 （该编号在组内是连续并且唯一的)。

注意： 序列函数不支持WINDOW子句。（ROWS BETWEEN）

NTILE

NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均匀，默认增加第一个切片的分布

eg:

select
cookieid,
createtime,
pv,
ntile(2) over (partition by cookieid order by createtime) as rn1, --分组内将数据分成2片
ntile(3) over (partition by cookieid order by createtime) as rn2, --分组内将数据分成2片
ntile(4) over (order by createtime) as rn3 --将所有数据分成4片
from cookie.cookie2
order by cookieid,createtime;

统计一个cookie，pv数最多的前1/3的天

select
cookieid,
createtime,
pv,
ntile(3) over (partition by cookieid order by pv desc ) as rn
from cookie.cookie2;

--rn = 1 的记录，就是我们想要的结果

ROW_NUMBER

ROW_NUMBER() –从1开始，按照顺序，生成分组内记录的序列
ROW_NUMBER() 的应用场景非常多，再比如，获取分组内排序第一的记录;获取一个session中的第一条refer等。

eg:

-- 按照pv降序排列，生成分组内每天的pv名次

select
cookieid,
createtime,
pv,
row_number() over (partition by cookieid order by pv desc) as rn
from cookie.cookie2;

-- 所以如果需要取每一组的前3名，只需要rn<=3即可，适合TopN

RANK/DENSE_RANK

—RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
—DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

eg:

select
cookieid,
createtime,
pv,
rank() over (partition by cookieid order by pv desc) as rn1,
dense_rank() over (partition by cookieid order by pv desc) as rn2,
row_number() over (partition by cookieid order by pv desc) as rn3
from cookie.cookie2
where cookieid='cookie1';

结果：

rn1 rn2 rn3
1 1 1
2 2 2
3 3 3
3 3 4
5 4 5
6 5 6
7 6 7

ROW_NUMBER、RANK和DENSE_RANK的区别

row_number： 按顺序编号，不留空位
rank： 按顺序编号，相同的值编相同号，留空位
dense_rank： 按顺序编号，相同的值编相同的号，不留空位

实例：

1．数据准备：

name，orderdate，cost

jack,2021-01-01,10

tony,2021-01-02,15

jack,2021-02-03,23

tony,2021-01-04,29

jack,2021-01-05,46

jack,2021-04-06,42

tony,2021-01-07,50

jack,2021-01-08,55

mart,2021-04-08,62

mart,2021-04-09,68

neil,2021-05-10,12

mart,2021-04-11,75

neil,2021-06-12,80

mart,2021-04-13,94

2．创建本地 business.txt，导入数据

[chaos@hadoop102 datas]$ vi business.txt

3．创建 hive 表并导入数据

create table business(name string, orderdate string, cost int)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/module/datas/business.txt" into table business;

4．按需求查询数据

（1）查询在 2020年 4 月份购买过的顾客及总人数

（2）查询顾客的购买明细及月购买总额

（3）上述的场景,要将 cost 按照日期进行累加

（4）查看顾客上次的购买时间

（5）查询前 20%时间的订单信息

上一篇：复习一下Kafka


下一篇：LeetCode刷题分类