大华的Hive技术文档

原来是大华啊~

已于 2024-03-27 10:43:30 修改

阅读量565

点赞数 6

文章标签： hive

于 2023-12-04 19:30:00 首次发布

本文链接：https://blog.csdn.net/yuanlaishidahuaa/article/details/134784470

版权

写在开头：

本文是博主学习自用，如有错误，请指出，谢谢

Hive中的一些概念

分区修复

 --实际没有，hive显示还有
 MSCK repair tablle t_all_hero_msck drop partitions;
 
 --实际Hdfs上存在，hive上没显示
 MSCK repair table t_all_hero_msck add partitions;

Hive查询

从哪里查询取决于from关键字后面的table_reference，可以是普通物理表，视图，Join结果或子查询结果。
表名和列名不区分大小写。
执行顺序：from > where > group by > having > order > select ;

 --用正则表达式查询字段
 set hive.support.quoted.identifiers = none;--指定反引号不再解释为其他含义，被理解为正则表达式

Union联合查询

union关键字用于将多个select 语句的结果合并为一个结果集。
每个select 返回的列的数量和名称必须相同才能合并

 select num,name from student_local 
 union distinct --会删除重复行
 select num,name from student_hdfs
 order by num desc;
 
 --对子句进行限制
 select num,name from (select num,name from student_local limit 2) subq1
 union 
 select num,name from (select num,name from student_hdfs limit 3) subq2
 order by num desc;

CTE表达式

CTE是一个临时结果集：该结果集是从with子句中指定的简单查询派生而来，紧接在select或insert关键字之前

CTE仅在单个语句的执行范围内定义

with q1 as (select num,name,age from student where num = 234)
select * from q1;

--from风格
with q1 as (select num,name,age from student where num = 123)
from q1 
select *;

--链式CTE
with q1 as (select * from student where num=123)
 		 q2 as (select num,name,age from q1)
select * from (select num from q2) a;

--union 
with q1 as (select * from student where num =1)
		 q2 as (select * from student where num =2)
select * from q1 union all select * from q2;

--插入
with q1 as (select * from student where num=2)
from q1
insert overwrtie table s1 
select * ;

Join...on[and]

支持on子句中不等值连接

inner join

和join一样，两个表中都存在与连接条件相匹配的数据才会被保留

left join

左外连接，或称为左连接。join时以左表的全部数据为准，右边与之关联；返回左表全部数据，右表关联上的返回，关联不上的显示null。

full join

全外连接，相当于对两个数据集合并，再消去重复的行。

left semi join

左半开连接，只返回同时满足右边表on条件的左边表的记录。

结果有点像inner join，但是效率比inner join会更高一点。

cross join

交叉连接，将会返回被连接的两个表的笛卡尔积。返回的结果等于两个表行数的乘积。大表慎用

cross join 后面可以跟where子句进行过滤，或者on条件过滤。

注意事项

1、支持非等值连接

select a.* from a left join b on a.id>b.id;

2、同一查询中可以连接2个以上的表

select a.val,b.val c.val from a join b on a.id=b.id join c on b.id =c.id;

3、如果多表连接on的字段相同，hive只有一个MR作业

4、join时最后一个表会通过reducer流式传输，并在其中缓冲之前的其他表，因此将大表放在最后有助于减少reduce阶段缓存数据需要的内存。

5、join条件在where条件之前进行

6、如果除一个要连接的表之外，其他所有的表都很小，则可以将其作为仅map作业执行（mapjoin）

DQL 查询

查其他

查看执行计划

explain extended select * from tableName;

查看表的所有分区

show partitions 表名;
show partitions default.D_EBU_SMS;

hive里可以模糊查询多个分区，如下：

select * from default.D_EBU_SMS 
where bill_no ='15067357571' 
	and gateway='10681342' 
  and p_city =571 
  and p_day like '%202211%' -- zheli ，但是肯定不建议这么跑
;

查表

模糊查询对应表名

show tables like '*tableN*'; -- 此处的tableN为不完整表名。注意，里面也是*，不是%；hive里模糊查询的关键字就是*
show views;

查看表分区

 show partitions tableName;

查看建表信息

 desc tableName; -- 查看建表字段
 describe formatted tableName; -- 查看除了建表字段外其他信息，所有者，

查看表中XX开头的字段

 select `^c.*` from tableName; --^:牟定行首，找出这个表当中所有c开头的字段

多字段去重查询

distinct

 select 
   distinct county,state 
 from t_usa_covid_p;
 --对county,state两个字段整体去重，而不是county一个

关于a.*的问题

在hive中，两个表关联，其中一个表的字段，不能用a.*来表示，如果只有一个表，则可以

drop table temp_IotAntiFraudWrist3_20230524;
create table temp_IotAntiFraudWrist3_20230524 as
select distinct 
	a.bill_no, -- 这么写可以
	a.cond1,
	a.cond2,
	a.cond3,
	a.warn_degree,
	b.op_time,
	b.city_name,
	b.msisdn
from temp_IotAntiFraudWrist2_20230524 a
left join 
D_1BA_I_02288_DT_D b on (a.bill_no = b.msisdn) -- msisdn：13位居多
;

drop table temp_IotAntiFraudWrist3_20230524;
create table temp_IotAntiFraudWrist3_20230524 as
select distinct 
	a.*, -- 这么写报错，执行sql异常，Error while compiling statement: FAILED: SemanticException TOK_ALLCOLREF is not supported in current context
	b.op_time,
	b.city_name,
	b.msisdn
from temp_IotAntiFraudWrist2_20230524 a
left join 
D_1BA_I_02288_DT_D b on (a.bill_no = b.msisdn) -- msisdn：13位居多
;

null和有值的字段求和

是可以相加的，比如1+null相加，仍然等于1，而不是null

select * from huoyj_test_20200103;
id   name    total_fee       base_fee   dinner_fee
130478483       huiyif  434.0   400.0   NULL
342142948       fhdjfk  438.8   NULL    NULL
130478483       huiyif  134.0   NULL    NULL
342142948       fhdjfk  438.8   134.0   NULL

select 
sum(id),sum(name),sum(total_fee),sum(base_fee),sum(dinner_fee) 
from huoyj_test_20200103;
---->
_c0     			  _c1     _c2     							_c3     _c4
9.45242862E8    0.0     1445.5999755859375      534.0   NULL

子查询

where子查询

不相关子查询

-- 子查询不会引用父查询中的列
select * from t1 
where t2.x in (select y from B);

相关子查询（推荐）

-- 子查询会引用父查询中的列
select *
from t1 
where exists (select 1 from t2 where t1.x=t2.y)

备注：上面两个查询结果一样，但是用相关子查询效率高！

from子查询

select num from(
	select num,name from sutdent_local
) tmp;

--包含union的子查询
select t3.name from(
	select num,name from student_local
  union distinct
  select num,name from student_hdfs
) t3;

查函数

模糊查询hive自带函数

show functions

show functions like '*month*';  -- 注意：里面一定一个* *，而不是% %
>>>
add_months
dayofmonth
floor_month
month
months_between

查看函数基用法

desc function extended

desc function extended month;  -- 查看hive中month函数的具体用法
>>>
month(param) - Returns the month component of the date/timestamp/interval
param can be one of:
1. A string in the format of 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'.
2. A date value
3. A timestamp value
4. A year-month interval valueExample:
 > SELECT month('2009-07-30') FROM src LIMIT 1;
 7
Function class:org.apache.hadoop.hive.ql.udf.UDFMonth
Function type:BUILTIN;
  
-- 查看explode函数的功能
describe function extended explode;  -- describe等价于desc

分组排序查询

order by

对输出的结果全局排序，底层只会有一个reduceTask执行，如果输出的行数太大，比较耗时。
如果一定要使用，最好加上limit关键字。

cluster by

根据指定字段分组，组内根据该字段正序排序（只能正序）。
只能根据同一个字段分且排序

--手动设置reduce task个数
set mapreduce.job.reduces=2;
select * from student 
cluster by num;--如果不设置前面的个数，这里mapreduce会根据任务量大概率安排一个task，就没有起到排序的作用

distribute by + sort by

distribute by 负责指定字段分组
sort by 负责组内排序
分组和排序的字段可以不同

select * from student 
distribute by sex 
sort by age desc;

DML 操作

hive是非结构化数据库，所以它的操作，不能像对数据库那样，像update，delete，都是不能用的，而且就连insert 也最好别用，要给hive加数据，最好就是create 一张新的表

插入数据

insert into table ：在表后直接插入，不去重

insert overwrite table ：将原表清空后，再插入数据
注意，hive里不建议用insert into

-- insert overwrite table 表名 PARTITION (act_mon = '202212')
insert overwrite table r_yk_mdl_beiji_m 
PARTITION (act_mon = '202212')
select distinct
	a.USER_ID, -- 用户ID
	a.BILL_id, -- 手机号
	a.COUNTY_CODE, -- 地市ID
	a.channel_id,
	a.CHANNEL_TYPE
from hzjx_yk_check_04 a,hzjx_yk_check_05 b
where a.org_id=b.org_id;

上传文件数据

第一步：hdfs建文件

hdfs dfs - mkdir -p /hive_data/student;

第二步：上传数据

方式一：

hadoop fs -put teacher.txt '/root/hivedata/teacher'; -- 不推荐
hdfs dfs -put teacher.txt '/root/hivedata/teacher'; -- 推荐

teacher.txt：此处表示本地文件
'/root/hivedata/teacher'：此处hdfs文件路径

fs和dfs的区别

fs是文件系统， dfs是分布式文件系统。fs > dfs
分布式环境情况下，fs与dfs无区别。
本地环境中，fs就是本地文件，dfs就不能用了。
fs涉及到一个通用的文件系统，可以指向任何的文件系统如local，HDFS等。但是dfs仅是针对HDFS的。

方式二：

load data local inpath '/root/hivedata/us-covid19-counties.dat' [overwrite] into table teacher;

-- 将student.txt里面的数据插入到 表studnt学生表里面，overwrite表示覆盖。
load data inpath ‘/student.txt’ overwrite into table student; 


load data： 表示加载数据
local：表示从本地加载，否则从hdfs上加载，local一般是在服务器命令行模式使用，其他地方比如dbeaver不能用
inpath：加载文件的路径
overwrite：表示覆盖原表已有数据，否则表示追加
into table：加载到哪个表
partition：加载到指定分区

备注：load只对数据做单纯的复制移动操作

删除数据

删除分区数据

alter table 表名 drop partition (分区字段 = '${l3m_mtaskid}');

DDL 定义

数据存储格式

orc和parquet区别的讲解视频：

https://www.bilibili.com/video/BV1ZV411J7Vt/?spm_id_from=333.337.search-card.all.click&vd_source=3c0c84befaea55842fa8622aa89e5300

Orc格式

和Parquet不同，ORC原生是不支持嵌套数据格式的。

ORC支持ACID特性（即update，insert，delete）

Parquet格式

Parquet设计动机是为了支持存储嵌套数据，可以将 ProtoBuf、Thrift、Avro 等格式的数据转换为列格式来存储，这是其最大的优势。

Parquet不支持ACID特性。

Parquet文件是以二进制方式存储的，是不可以直接读取和修改的，Parquet文件是自解释（自身的内容包含对自身内容的描述）的，文件中包括该文件的数据和元数据。

存储原理：见上面那个视频。

Orc（Optimized Row Columnar）和Parquet是两种流行的列式存储格式，用于在大数据处理环境中高效地存储和查询数据。它们都是为了解决传统行式存储格式（如CSV和JSON）在大数据场景下的性能和效率问题而设计的。虽然它们有一些相似之处，但也有一些区别。

以下是Orc和Parquet格式之间的一些区别：

压缩算法：Orc使用基于行的压缩算法，而Parquet使用基于列的压缩算法。基于行的压缩意味着Orc将每行的数据一起压缩，这在某些查询场景下可以提供更好的性能。基于列的压缩意味着Parquet将每列的数据一起压缩，这对于只需要访问特定列的查询可以提供更好的性能。
列式存储：Orc和Parquet都采用列式存储，这意味着它们将数据按列存储在磁盘上，而不是按行。这种存储方式可以提供更好的压缩率和查询性能，因为它允许只读取和处理查询所需的列，而不需要读取整行数据。
数据压缩：Orc和Parquet都支持多种压缩算法，如Snappy、LZO和Gzip。它们都可以根据需求选择适合的压缩算法来平衡存储空间和查询性能。
列式编码：Orc和Parquet都使用列式编码来进一步提高存储效率。列式编码技术可以根据数据的分布特点对列进行编码，从而减少存储空间。它们支持多种列式编码方法，如Run Length Encoding（RLE）、Dictionary Encoding和Bit Packing。
数据类型支持：Orc和Parquet都支持各种数据类型，包括整数、浮点数、字符串、日期时间等。它们还支持复杂数据类型，如数组和结构体，以便于存储和查询复杂数据结构。
生态系统支持：Orc和Parquet都有广泛的生态系统支持。它们可以与多个大数据处理框架（如Apache Hadoop、Apache Spark和Apache Hive）无缝集成，并提供高性能的数据读取和写入功能。

总的来说，Orc和Parquet都是为大数据处理场景而设计的高效列式存储格式。选择使用哪种格式通常取决于具体的使用场景、查询需求以及所使用的数据处理框架。

建库

-- 建库
create database if not exist DB_dahua
comment "this is dahua's first db"
with dbproperties ('createdBy'='dahua');

-- 选择数据库
use itheima;

-- 删库
drop database if exists DB_dahua cascade; -- cascade表示强制删库；因为如果库里有表，直接删是删不掉的。

建表

hive2里建表不能有中文名称字段

外部表

只管理元数据，删除外部表，只删除元的元数据，存放在hdfs上的文件还在
一般业务都建外部表，安全考虑
不支持事务
移动dm平台建表一定要指定存储格式，默认是orc格式
移动dm平台，hive上建表一律用string

create external table sutdent_ext( -- 移动dm平台建表不用加external
	num int,
  name string,
  sex string,
  age int,
  dept string,
  lng float,
  lat float,
  created_date TIMESTAMP
)PARTITIONED BY (day_id string)
row format delimited
fields terminated by ',' --字段间的分隔符，一般要加
map keys terminated by ':' --map的key value之间的分隔符，可选
lines terminated by '|' --行与行之间的分隔符，可选
stored AS textfile; -- 指定存储格式，这个在移动dm平台一定要指定，移动dm默认存orc格式
location '/stu'; -- 指定存储位置，可选，不选就默认，移动dm平台不用加

备注：由于Hive建表之后加载映射数据很快，实际中如果建表有问题，可以不用修改，直接删除重建即可。

内部表

hive完全对该表进行管理，删除表后元数据和hdfs文件都没有了
支持事务
去掉external就是内部表了

-- 建分区表：
CREATE TABLE IF NOT EXISTS part_test(
  c1 string,
  c2 string,
  c3 string,
  c4 string
)PARTITIONED BY (day_id string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘|’
STORED AS TEXTFILE;

表备份

全表备份：建和t1完全相同的表t2

create tabel tableName1 like tableName2;

部分字段备份：建t1中部分字段表t2

create table teacher as 
select id, name from student;

分区表

本质：hdsf通过不同的文件夹来管理不同分区文件

分区字段不能是表中已经存在的字段

单分区表

create  table r_kaiji_user_list_d(
	user_id string, -- 用户ID
	bill_no string, -- 手机号
	kj_dates string, -- 开机记录
	kj_dates_sn string, -- 市内开机记录
	kj_dates_sw string, -- 省内开机记录
	kj_dates_gn string, -- 省外开机记录
	kj_dates_gw string -- 国外开机记录
) partitioned by(p_day string); -- 注意分区是partitioned，要加ed
-- row format后面那些可以不写

多分区表

Partition by (partition1 data_type，partition2 data_type,...)

多重分区下，分区之间是递进关系，可以理解为在前一个分区的基础上继续分区。因此要注意分区字段的前后关系

--建双分区表
create table t_user_province_city(
	id int,
  name string,
  age int
)pratitioned by(province string,city string);

--多分区的数据插入
load data local inpath '文件路径' into table t_user_province_city
partition(province='zhejiang',city='hangzhou');

--多分区表的查询
select * from t_user_province_city 
where province='zhejiang' and city='hangzhou';

静态分区表

--建分区表
create external table t_all_hero_part (
	id int,
  name string,
  hp_max int,
  mp_max int,
  attack_max int,
  defense_max int,
  attact_range string,
  role_main string,
  role_assist string
)partitioned by (role string)--这里是分区字段
row format delimited
fields terminated by '\t';

--静态加载分区表中的数据
load data local inpath '/root/hivedata/warrior.txt' into table t_all_hero_part partition(role='zhangshi');
--这个‘zhanshi’与原表中role_main列的数据无关，自己想起什么就起什么，相当于把每个文件的数据按照你自己的role做一个表内分组
--如果文件在本地机器上就要加local，如果在hdfs上就不用加local

动态分区表

关键词：insert into select...
完全依赖位置确定分区，select后面如果字段顺序错了，就会出现分区报错
需要现有一个原始表，从原始表里再创建分区表

 --开启动态分区参数
 set hive.exec.dynamic.partition=true;
 set hive.exec.dynamic.partition=nonstrict;
 
 create table t_all_hero_part_dynamic(
   id int,
   name string,
   hp_max int,
   mp_max int,
   attack_max int,
   defense_max int,
   attack_range string,
   role_main string,
   role_assist string
 )partitioned by(role string)
 row format delimited
 fields terminated by '\t';
 
 --执行动态分区插入
 insert into table t_all_hero_part_dynamic partition(role) 
 select tmp.*,tmp.role_main 
 from t_all_hero tmp;

分桶表

把一个文件分为若干个部分，每个部分就叫一个桶。

分桶的字段一定得是表中的字段

 --注意：通过Hadoop fs -put上传文件是不行的，底层需要MR来实现
 --分桶表的插入只能从普通表中插入获得
 --建表（普通表）
 create table t_usa_covid19....
 --建表（分桶表）
 create table t_usa_covid19_bucket_sort(
   count_date string,
   county string,
   state string,
   fips int,
   cases int,
   death int
 )clustered by(state)--分桶的字段一定是表中已经存在的字段
 sorted by(cases desc) into 5 buckets;
 
 --将普通表的数据插入到分桶表当中
 insert into t_usa_covid19_bucket_sort 
 select * from t_usa_covid19;

分桶表的好处

基于分桶字段查询，减少全表扫描

select * from t_usa_covid19_bucket where state='New York';

Join时可以提高MR的效率，减少笛卡尔积的数量。根据分桶的字段join

高效抽样

事务表

局限

1、不支持BEGIN，COMMIT和ROLLBACK。所有语言自动提交。

2、仅支持ORC文件格式（stored as orc）

3、默认情况事务配置为关闭，需要参数开启。

4、表必须是分桶表才能使用事务。

5、表参数transactional必须为true。

6、外部表不能成为ACID表

--开启参数
set hive.support.concurrenct=true;--开启支持并发
set hive.exec.dynamic.partition.mode=nonstrict;--非严格模式
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;--hive默认事务管理的类
set hive.compactor.initiator.on=true;--线程清理
set hive.compactor.worker.threads=1;--数据压缩

--创建事务表
create table trans_student(
	id int,
  name String,
  age int
)clustered by(id) into 2 buckets 
stored as ORC
tblproperties('transactional'='true');--tblproperties表示属性

--插入
--into追加，overwrite重写
insert into/overwrite trans_student values(1,'allen',18);
--更新
update trans_student set age=20 where id = 1;
--删除
delete from trans_student where id = 1;

很慢，跟MySQL完全不能比

视图

视图是一种虚拟的表，并不存储数据，只保存定义。你在hdfs上是看不到视图的。

通常是从已有的真实的物理表中创建生成视图，也可以从视图上再建新视图。
创建好视图后，会将视图和基础物理表进行绑定，如果此时对原表修改后，视图将失效
视图是用来简化操作和封装的，不缓冲记录，不会提高查询性能。
视图不支持插入数据，因为它是虚拟的。

--创建视图
create view V_usa_covid19 as 
select count_date,count,state deaths 
from t_usa_covid19 
limit 5;

--从已有视图中再创建视图
create view v_usa_covid19_from_view as 
select * 
from V_usa_covid19 
limit 2;

--更改视图定义
alter view v_usa_covid19 as 
select county,deahts 
from t_usa_covid19 limit3;--这个等于把视图重新创建了

视图好处

1、将真实表中特定列的数据提供给查询的人，保护了其他隐私的，较敏感的列数据。

--使用视图查询特定列
create table userinfo(firstname string,lastname string,ssn string,password);
create view v_safer_user_info as 
select firstname,lastname 
from userinfo;

--通过where子句限制数据访问
create table employee(firstname string ,lastname string,ssn string,password string,department string);
create view v_techops_employee as 
select firstname,lastname,ssn 
from userinfo 
where department = 'java';

2、降低了查询的复杂度，优化了查询语句（并没有优化查询效率）

--原有的查询语句，相对查询逻辑比较复杂
from(
	select * from people p join cart c on p.id=c.id where firstname = 'join'
) a select a.lastname where a.id = 3;

--使用view优化上述语句（把from字句封装成一个视图）
create view v_shorter_join as 
select * 
from people p 
join cart c on p.id=c.id 
where firstname = 'join';
select lastname from shorter_join where id = 3;

物化视图

核心思想：将提前计算好的查询结果保存起来，在下次查询时，可以提高查询效率。这也是大数据处理技术的基本思想，预处理。

真实的表，里面保存数据，和视图区别开

--物化视图的创建
create materialized view M_wiki_mv
stored as 'org.apache.hadoop.druid.DruidStoragegHandler'--支持存储在除Hive之外的其他介质
as select a,b,c,d from tableA;

drop materialized view materialized_view_name;
show materialized views;
describe materialized_view_name;

--当数据源变更，物化视图也需要更新保持数据一致性，目前需要用户主动触发重构
alter materialized view materialized_view_name rebuild;

物化视图的查询重写机制

1、用户提交查询语句。

2、若该查询语句经过重写后可以命中已存在的物化视图。

3、则直接通过物化视图返回查询结果，以实现查询加速。（注意这里用户不用查询物化视图也是立马返回结果，系统会自动匹配物化视图）

删表

删除外部表只会删除对应表的原数据，该表对应的data不会被删除。

删除内部表则都会被删除。

删表

--删除表
drop table tableName;

清空表

-- 仅删除数据，保留表结构
truncate table tableName; -- truncate只能删除内部表，不能删除外部表

-- 等价于
delete from tableName where 1=1;

删除hdfs对应文件

--删除hdfs上的文件
hadoop fs -rm -r 对应文件路径

删除分区

-- 删除分区
alter table tableName drop partition (分区字段名='对应分区');

DCL 控制

Hive中参数配置及运算符的使用

Hive客户端和属性配置

第一代客户端

批处理模式，会话模式

启动hive服务

bin/hive --service metastore
bin/hive --service hiveserver2

第二代客户端

--启动beeline服务
hive/bin/beeline

> ! connect jdbc:hive2://single01:10000

hive属性配置

hive配置属性是在hiveConf.java类中管理。

配置文件在hive-default.xml中配置。

方式一：hive-site.xml，整个hive中有效，优先级最低。

方式二：--hiveconf命令行参数，会话级别，会话中有效，会话结束失效，优先级次之。

方式三：set命令，会话级别，使用最多，谁需要，谁配置，谁使用。优先级最高。

Hive内置运算符

关系运算

是二元运算符，执行的是两个操作数之间的比较。

--like的用法
select 1 from dual where 'itcast' like 'it_';
select 1 from dual where 'itcast' like 'it%'
select 1 from dual where 'itcast' not like 'hadoo_'--写法一
select 1 from dual where 'itcast' not 'itcast' like 'hadoo_'--写法二

--rlike：确定字符串是否匹配正则表达式
select 1 from dual where 'itcast' rlike '^i.*t$';.表示任意字符，*表示出现多次
--^确定行首位置，$确定行尾位置

select 1 from dual where '123' rlike '^\\d+$'：\表示转义，\d表示数字

算数运算符

操作数必须是数值类型，分为一元运算符和二元运算符。

一元运算符：只有一个操作数

二元运算符：运算符要写在两个操作数之间

取余也叫取模操作

select 14 div 3;
select 14 % 3;
--&：两个都为真，结果为真
select 4 & 8 from dual;--4转换二进制：0100，8转换二进制：1000。依次按位比较。结果为0000，转换为十进制为0
select 4 | 8 from dual;
--^：比较时依旧先转换为二进制，依次按位比较，每一位两者结果值不同，结果为1，相同为0
select 4 ^ 8 from dual;4:0100,8:1000。转换为1100，按8421,8+4结果为12

逻辑运算

select 1 from dual where !2=1;
select 1 from dual where 11 not in (11,22,33)

--exists：将主查询的数据，放到子查询中做条件验证来决定主查询的结果是否得以保留。
select A.* from A
where exists(select B.id from B where A.id=B.id);

其他运算符

--concat()：字符串拼接
select concat('it','cast');
--map(k1,v1,k2,v2,k3,v3...)
--array(v1,v2,v3...)
--struct(v1,v2,v3...)

Hive函数

Hive函数分为两大类：内置函数，用户定义函数

--查看count函数的使用方式
describe function extended count;

内置函数

系统函数

数值类型函数

1. 取整函数: round
2. 指定精度取整函数: round
3. 向下取整函数: floor
4. 向上取整函数: ceil
5. 向上取整函数: ceiling
6. 取随机数函数: rand
7. 自然指数函数: exp
8.自然对数函数: ln
8. 以10为底对数函数: log10
9. 以2为底对数函数: log2
10. 对数函数: log
11. 幂运算函数: pow
12. 开平方函数: sqrt
13. 二进制函数: bin
14. 十六进制函数: hex
15. 反转十六进制函数: unhex
16. 进制转换函数: conv
17. 绝对值函数: abs
18. 正取余函数: pmod
19. 正弦函数: sin
20. 反正弦函数: asin
21. 余弦函数: cos
22. 反余弦函数: acos
24.正切函数:tan
25.反正切函数:atan
26.弧度值转换角度值:degrees
27.角度值转换成弧度值:radians
23. positive函数: positive
24. negative函数: negative
30.判断正负函数:sign
31.数学e函数:e
32.数学pi函数:pi
33.阶乘函数:factorial
34.立方根函数:cbrt
35.左移函数:shiftleft
36.右移函数:shiftright
37.无符号按位右移函数:shiftrightunsigned
38.求最大值函数:greatest
39.求最小值函数:least
40.银行家舍入法函数:bround
41.银行家精确舍入法函数:bround

日期类型函数

-- to_date(string timestamp)
select to_date(‘2018-02-27 10:03:01’) ;
>>>
2018-02-27
select 
	user_id,bill_id,county_code,
	...
from get_user_attrib
where to_date(create_date) = 2022-11;

 1. UNIX时间戳转日期函数:from_unixtime
 2. 获取当前UNIX时间戳函数:unix_timestamp
 3. 日期转UNIX时间戳函数:unix_timestamp
 4. 指定格式日期转UNIX时间戳函数:unix_timestamp
 5. 日期时间转日期函数:to_date
 6. 日期转年函数: year
 7. 日期转月函数: month
 8. 日期转天函数: day
 9. 日期转小时函数: hour
 10. 日期转分钟函数: minute
 11. 日期转秒函数: second
 12. 日期转周函数: weekofyear
 13. 日期比较函数: datediff
 14. 日期增加函数: date_add
 15. 日期减少函数: date_sub
 16. 转化成指定的时区下时间戳函数: from_utc_timestamp
 17. 转化成UTC下的时间戳函数: to_utc_timestamp
 18. 当前时间日期函数:current_date
 18. 当前时间日期函数:current_timestamp
 19. 月份增加函数:add_months
 20. 最后一天的日期函数:last_day
 21. 下一个星期X所对应的日期函数:next_day
 22. 时间的最开始年份或月份函数:trunc
 23. 相差的月份函数:  months_between
 24. 指定格式返回时间函数: date_format
 25. 当前星期函数: dayofweek
 26. 季节函数: quarter

字符串函数

‘\\s+’：表示按空格进行切分

去重汇总函数 collect_set()

列转行专用函数，其返回值类型是 array 。

该函数会自动帮你去重，如果不想去重的话，可以用collect_list()

注意，这个函数是在hive中用的，SQL中对应这个函数的是group_concat，要实现去重的话可以加distinct。
该函数不会帮你排序，如果还要实现排序，可以在外面套个sort_array()，即：
```
concat_ws(',', sort_array(collect_set(brand_classify), false))
```

下面是具体实现过程：

原数据

select
  brand_name,
  concat_ws(',', collect_set(brand_classify)) as brand_classify
from tb_brand_classify
group by brand_name;

聚合函数

注意：聚合函数不支持嵌套聚合函数

count函数

count(1), count(*), count(colN)三者之间的区别

count(*)：对包括null的所有行求和
count(1)：和count(*)相同
count(col)：对col中的非null行统计求和

count出现数据倾斜的情况

sum函数

sum和if或case when结合使用的情况

sum时出现空行的处理办法

null和任何字段运算，结果都返回null
coalesce函数，返回第一个不为空的值，如果都为空，返回0

sum(coalesce(col1,0) + coalesce(col2,0))

原数据如下：

group_sets，cube，rollup三个函数

group_sets

它的作用是将不同维度的group by结果集写在一个SQL中的写法。

原数据如下：

select 
	month,null as day,conut(distinct cookieid) as nums,1 as grouping__id
from cookie_info 
group by month 
union all
select
	null as month,day,count(distinct cookieid) as nums,2 as grouping__id 
from cookie_info 
group by day;

--上面的语句和下面这个等价
select 
	month,day,count(distinct cookieid) as nums,grouping__id 
from cookieid_info 
group by month,day
grouping sets(month,day) --这里是关键
order by grouping__id;

不用grouping sets的写法：

-- 方法1：分开聚合
select province
	,NULL as city
	,goodsid
	,goodsname
	,sum(sales_qty) as sales_qty
	,sum(sales_amt) as sales_amt
	,'prov_dim' dim_flag
from temp.goods_sale_info
group by province,goodsid,goodsname
union all
select NULL as province
	,city
	,goodsid
	,goodsname
	,sum(sales_qty) as sales_qty
	,sum(sales_amt) as sales_amt
	,'city_dim' dim_flag
from temp.goods_sale_info
group by city,goodsid,goodsname

用的写法：

select province
	,city
	,goodsid
	,goodsname
	,sum(sales_qty) as sales_qty
	,sum(sales_amt) as sales_amt
	,GROUPING__ID
from temp.goods_sale_info
group by province,city,goodsid,goodsname
grouping sets(
	province,city,goodsid,goodsname   -- 各省各市各商品的销量、销售额
)

cube

根据group by的维度所有组合进行聚合，对于cube来说，如果有n个维度，，组合的总个数是2的n次方。（注意0维也是维度）

select 
	null,null count(distinct cookieid) as nums,0 as grouping__id 
from cookie_info 
union all
select 
	month,null count(distinct cookieid) as nums,1 as grouping__id 
from cookie_info 
union all
select
	null,day count(distinct cookieid) as nums,2 as grouping__id 
from cookie_info 
union all
select 
	month,day count(distinct cookieid) as nums,3 as grouping__id 
from cookie_info 
group by month,day;

--等价于
select month,day,count(distinct cookieid) as nums,grouping__id
from cookie_info
group by month,day with cube
order by grouping__id;

rollup

rollup是cube的子集，以最左侧的维度为主，比如rollup有a，b，c三个维度，则所有组合情况是（a,b,c),(a,b),(a),()

集合函数

条件函数

数学函数

脱敏函数

其他杂项函数

用户自定义函数

可分为三类：UDF,UDAF,UDTF

UDF函数：一进一出

round()，但它是内置函数，但是由于UDF分类标准扩大了，也算UDF函数。

UDAF函数：聚合函数,多进一出

sum()，avg()，min()，max()，count()，collect_set()去重，collect_list()不去重

count(*): 对所有行进行统计,包括null行
count(1): 对所有行进行统计,包括null行
count(col): 对col中非空字段进行统计
聚合函数中不支持嵌套聚合函数
聚合操作时对null的处理
- 非空统计函数coalesce：--coalesce函数返回第一个非空字段,如果不为空返回val1本身,如果为空返回后面给的值,此时后面给的值为0
- ```
select sum(coalesce(val1,0)),sum(coalesce(val1,0) + val2) from tmp_1;  
```

需求: 找出student中男女学生年龄最大的及其名字

select sex,
	max(struct(age,name)).col1 as age,
	max(struct(age,name)).col2 as name 
from student group by sex;
--struct是构造函数，可以把多个字段构建为一个整体

UDTF函数:表生成函数,一进多出

explode() 函数:

接受map,array类型的数据作为输入,然后将数据中的每个元素拆成一行数据
可单独使用，但如果要select多个字段必须配合侧视图lateral view一起使用
用lateral view产生的字段可以用于group by，order by，limit等语句，不需要再单独嵌套一层子查询
一般只要用到UDTF函数，一般用需要配合lateral view一起来使用

Java实现UDF函数

案例

：UDF实现手机号中间4位加密

步骤:

0、准备开发环境，创建maven工程，在pom.xml里面添加2个添加hive-exec包和Hadoop-common包的依赖和一个打jar包的build插件：

<dependencies>
	<dependency>
  	<groupId>org.apache.hive</groupId>
  	<artifactId>hive-exec</artifactId>
  	<version>3.1.2</version>
  </dependency>
  <dependency>
  	<groupId>org.apache.hadoop</groupId>
  	<artifactId>hadoop-commono</artifactId>
  	<version>3.1.4</version>
  </dependency>
<dependencies>

还要再pom.xml里面添加一个打jar包的插件，详情请看：【Python+大数据】Apache Hive教学2021最新大数据Hive从入门到精通更新完毕哔哩哔哩bilibili

注意：把依赖打进去会显得这个jar包特别大，之所以这么做的原因是很可能会依赖第三方的jar包实现业务逻辑，但是这些第三方的jar包不一定存在于hive当中，运行可能就会报错。

1、要写个Java代码,继承UDF，重载evaluate方法，实现函数的业务逻辑（可以在函数中实现多个业务逻辑，没有必要一个函数实现一个功能）

重载意味着可以在一个Java类中实现多个函数功能

public class EncryptPhoneNumber extends UDF {
  public String evaluate(String phoNum){
    //先定义一个手机号的变量，后面要用，默认为空
  	String encryptPhoNum = null;
  	//手机号不为空，并且11位
  	if(StringUtils.isNotEmpty(phoNum) && phoNum.trim().length() ==11 ){//phoNum.trim().length()这个表示手机号去空的长度
    	//判断数据是否满足大陆手机号码规范
      String regex = "^(1[3-9]\\d{9}$)";//[3-9]这个表示第二位必须是3-9中的一个，[]表示范围，()表示位数，\表示转义符，\d表示匹配数字，{9}表示重复9次。
    	Pattern p = Pattern.compile(regex);//这行是将上面的正则字符串封装成正则表达式p
    	Matcher m = p.matcher(phoNum)//这行表示用封装好的正则表达式p去匹配用户输入phoNum
      if(m.matcher()){
        //使用正则替换，封装成加密数据
        encryptPhoNum = phoNum.trim().replaceAll("(\\d{3})(\\d{4})(\\d{4})","$1****$3");//$美元符号在正则中是拿出第几个分组的意思
      }else{
        //用户输入不满足匹配规则，输入原封不动返回
        System.out.print("不满足输入规范，退出")
      	encryptPhoNum = phoNum;
      }
  	}else{
      //不满足输入规则，返回
      System.out.print("不满足输入规则，退出")
      break；
    }
    return encryptPhoNum;
  }
}

2、将程序打成jar包，上传至HDFS

先点clean做一个清空

3、客户端命令行中添加jar包到Hive的classPath中

需要先把这个jar包鼠标拖拽上传到Linux服务器
再用add jar命令

 hive> add jar /root/hive-udf-1.0-SNAPSHOT.jar;#因为我们直接把这个jar包放在root路径下，所有就是/root/【jar包名字】

4、注册称为临时函数（给UDF命名）

 # create temporary function 函数名 as 'UDF类全路径';
 # temporary临时的原因是这个jar只在会话中有效
 hive> create temporary function encrypt_phonum as 'cn.itcast.hive.udf.EncryptPhoneNumber';
 # cn.itcast.hive.udf.EncryptPhoneNumber这个是我们从Java里写好的这个类然后点copy reference后的全路径名

5、HQL中使用函数【这个函数就正常像内置函数使用就好了】

 select encrypt_phonum("allen");//报错
 select encrypt_phonum(13261816202);//返回132****6202

侧视图+爆炸函数

explode函数 + lateral view函数

explode函数只接收map，array类型的数据作为输入，然后把输入数据中的每个元素拆开成一行数据，俗称列转行。

直接查要列转行的字段时可以单独使用，但如果要查询多个字段得和lateral view结合使用。
lateral view侧视图的作用类似于把源表和炸开的虚拟的表做join

lateral view使用语法：

select ... 
from tableA 
lateral view UDTF(xxx) 别名 as col1,col2,col3...;

UDTF(xxx) 炸开的虚拟表要起个别名
炸开的字段可以再as起别名如col1...

需求：冠军球队名单分析

--1.建表
create table the_nba_championship(
	team_name string,
  champion_year array<string>
)row fromat delimited  --建表指定好对应的分隔符,
fields terminated by ','  --字段间的分隔符为","
collection items terminated by '|';  --数组(array,struct)间分隔符为'|'

--2.加载数据文件到表
load data local inpath '/root/hivedata/The_NBA_Championship.txt' into table the_nba_championship;

--3.验证
select * from the_nba_championship;

--4.使用explode对champion_year炸开
select explode(champion_year) from the_nba_champion;--这样没问题
select team_name,explode(champion_year) from the_nba_champion;--这样会报错，因为单独使用explode只能select一个字段

--报错原因：
--在select 中只查询源表数据没有问题，只查询explode生成的虚拟表数据也没有问题，但是不能在只查询源表的时候，既想返回源表字段，又想返回explode生成的虚拟表字段；通俗点讲，有两张表，不能只查询一张表但是返回分属于两张表的字段。


--5.查询球队获得冠军的年份并挨个将每年炸开
--lateral view + explode
select a.team_name,b.year
from the_nba_championship a 
lateral view explode(chanpion_year) b as year; --b是生成的虚拟表的名字,as 后面的col是炸开的字段的别名

--5.1根据年份倒叙排序
select a.team_name,b.year
from the_nba_championship a 
lateral view explode(chanpion_year) b as year
order by b.year desc;

--5.2统计每个球队总冠军次数，并且根据倒叙排序
select a.team_name,count(*) as nums --count( * )表示统计所有行
from the_nba_championship a 
lateral view explode(champion_year) b as year
group by a.team_name
order by nums desc;

split函数

用于把字段间分隔开，返回一个array

用法：split（str，''分隔符"）

create table temp_White_list_20230520 as 
SELECT 
	msisdn,
	city_name,
	split(voice_dir_list,',') as voice_dir_list  -- 这里
from default.D_1BA_I_02288_DT_D
where p_day='20230520' 
;

增强聚合函数

窗口函数

特点：输入值是select语句的结果集中一行或多行窗口中获取的，说白了，就是它的取值有个窗户的概念，在这个窗户内进行取值。有点类似group by，但和group by不一样在于，它能分组后依旧看到组里每个人的情况和分组后聚合的情况。

语法：function(arg1,...,argN) over (partition by XXX order by XXX window_expresssion ) as aaa

function 可以是下面分类中任意：
- 聚合函数：sum，max，avg
排序函数：row_number，rank，dense_rank，ntile
- row_number：在每个分组中，为每一行分配从1开始唯一的序列号，递增且不考虑重复
- rank：在每个分组中，为每行分配从1开始的序列号，考虑重复，挤占后续位置
- dense_rank：在每个分组中，为每行分配从1开始的序列号，考虑重复，不挤占后续位置
- ntile：将每个分组的数据分为指定的若干个桶里，并为每个桶分配一个桶编号
- 分析函数：lead，lag，first_value，last_value
  - lag(col,n,default) over (...) as aaa : 窗口内往上取第n行值.(col表示作用于哪个字段,n不说了,default不写也行,不写为null)
  - lead(col,n,default) over (...) as aaa: 往下取第n行的值
  - first_value: 分组内排序后，截止到当前行，第一个值
  - last_value: 分组内排序后，截止到当前行，最后一个值
partition by 作用类似group by，用于将某个字段分组，若没有写Partition by则整张表为一组
order by 用于将每个分组内的数据进行排序，支持asc和desc
window_expresssion 用于指定每个窗口操作行聚合的范围，默认是所有行。关键字：rows between。
- X preceding：向前X行
- Y following：往后Y行
- current row：当前行
- unbounded:：边界
- unbounded preceding：从第一行
- unbounded following：到最后一行

执行流程:

 --1. 建表
 create table emploee(
   id int,
   name string,
   deg string,
   salary int,
   dept string
 )row format delimited
 fields terminated by ',';
 
 --2. 上传加载数据
 load data local inpath '/root/hivedata/employee' into table employee;
 
 --3. 执行普通聚合操作: 
 select dept,sum(salary) as total_salary from employee group by dept;  
 -- 按照不同部门进行分组求各部门总薪水
 -- 普通聚合函数的不足: 只能看到部门的总薪水,但每个人具体的薪水看不懂,数据UDAF操作,多行转一行,隐藏掉中间细节
 
 --4. 执行开窗函数: 
 select id,name,salary,dept,sum(salary) over (partition by dept) as total_salary from employee;  
 -- 按照部门进行开窗,查询部门里每个人的薪水和每个部门的总薪水
 -- 执行完窗口函数后,行数没有减少,能看到每个人的具体情况,又能看到每个部门的聚合的情况

案例：

网站用户页面浏览次数分析

 --1.建表
 --页面浏览次数
 create table website_pv_info(
   cookieid string,
   createtime string, --day
   pv int
 )row format delimited
 fields terminated by ',';
 
 --对应浏览页面
 create table website_url_info(
   cookieid string,
   createtime string, --访问时间
   url string --访问页面
 )row format delimited
 fields terminated by ',';
 
 --2.加载数据
 load data local inpath '/root/hivedata/website_pv_info.txt' into table website_pv_info;
 load data local inpath '/root/hivedata/website_url_info.txt' into table website_url_info;
 
 --3.验证
 select * from website_pv_info;
 select * from website_url_info;
 
 --(1)窗口聚合函数
 --1.求出每个用户的总pv数
 select cookieid,sum(pv) as total_pv from website_pv_info group by cookieid;
 1. sum(col) over(): 对表内所有行求和,没有分组(意义不大,相当于普通聚合)
 2. sum(col) over(order by XXX): 不分组,对整张表连续累计求和
 3. sum(col) over(partition by XXX): 分组后对每个分组统计求和
 4. sum(col) over(partition by XXX order by YYY): 分组后对每个分组内连续累计求和
 
 --2.求出网站总pv数,所有用户所有访问加起来
 select cookieid,createtime,pv,
   sum(pv) over() as total_pv --注意这里的窗口函数是没有分组的,全表所有行求和
 from website_pv_info;
 
 --3.求出每个用户的总pv数
 select cookieid,createtime,pv
   sum(pv) over(partition by cookieid) as total_pv
 from website_pv_info;
 
 --4.求出每个用户截止到当天,累积的总pv数
 select cookieid,createtime,pv,
   sum(pv) over(partition by cookieid order by createtime) as current_total_pv
 from website_pv_info;
 
 --(2)窗口表达式
 --1.从第一行到当前行:
 select cookieid,createtime,pv,
   sum(pv) over(partition by cookieid order by createtime 
                rows between unbounded preceding and current row) as pv1 
 from website_pv_info;
 
 --2.向前3行至当前行
 select cookieid,createtime,pv,
   sum(pv) over(partition by cookieid order by createtime 
                rows between 3 preceding and current row) as pv2
 from website_pv_info;
 
 --3.向前3行，向后1行
 select cookieid,createtime,pv,
   sum(pv) over(partition by cookieid order by createtime 
                rows between 3 preceding and 1 following) as pv3
 from website_pv_info;
 
 --4.当前行至最后一行
 select cookieid,createtime,pv,
   sum(pv) over(partition by cookieid order by createtime 
                rows between current row and unbounded following) as pv4 
 from website_pv_info;
 
 --5.从第一行到最后一行，即分组内所有行
 select cookieid,createtime,pv
   sum(pv) over(partition by cookieid order by createtime 
                rows between unbounded preceding and unbounded following) as pv5
 from website_pv_info;
 
 --(3)窗口排序函数：非常适合做TopN业务分析
 --1.给cookie1的用户的pv浏览量打标号
 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 website_pv_info
 where cookieid = 'cookie1'; --这里做不做过滤都行
 
 --2.找出每个用户访问过pv最多的Top3，重复并列不考虑
 select * from (
   select 
     cookieid,createtime,pv,
     ROW_NUMBER() OVER (partition by cookieid order by pv desc) as seq  --分组,排序,打序号
   from website_pv_info ) as tmp 
 where tmp.seq<4;
 
 --(3.1)窗口排序分桶函数：非常适合做只关心某一部分(比如后1/3)的数据的需求
 --如果不能平均分配，则优先分配编号较小的桶，每个桶编号行数最多差1
 --统计每个用户浏览页面（pv）数最多的前1/3天
 select * from 
   (select 
     cookieid,
     createtime,
     pv,
     ntile(3) over (partition by cookieid order by pv desc) as rn 
   from website_pv_info ) as tmp 
 where rn = 1;     
 
 --(4)窗口分析函数：适合做连续登录/下单用户的查询
 --lag用法
 select cookieid,createtime,url
   row_number() over(partition by cookieid order by createtime) as rn,
   lag(createtime,1,'1970-01-01 00:00:00') over(partition by cookieid order by createtime) as        last_1_time,
   lag(createtime,2) over(partition by cookieid order by createtime) as last_2_time
 from website_url_info;
 
 --lead用法
 select cookieid,createtime,url
   row_number() over(partition by cookieid order by createtime) as rn,
   lead(createtime,1,'1970-01-01 00:00:00') over(partition by cookieid order by createtime) as         last_1_time,
   lead(createtime,2) over(partition by cookieid order by createtime) as last_2_time
 from website_url_info;
 
 --first_value用法
 select cookieid,createtime,url,
   row_number() over(partition by cookieid order by createtime) as rn,
   first_value(url) over(partition by cookieid order by createtime) as first1
 from website_url_info;
 
 --last_value用法
 select cookieid,createtime,url,
   row_number() over(partition by cookieid order by createtime) as rn,
   last_value(url) over(partition by cookieid order by createtime) as last
 from website_url_info;

max开窗

 select 
   studentId,math,departmentId,classId,
   
   -- 以符合条件的所有行作为窗口
   max(math) over() as max1,
   
   -- 以按classId分组的所有行作为窗口
   max(math) over(partition by classId) as max2,
   
   -- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口
   max(math) over(partition by classId order by math) as max3,
   
   -- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口
   max(math) over(partition by classId order by math rows between 1 preceding and 2 following) as max4
 from student_scores where departmentId='department1';

抽样函数

以局部评估整体的函数，HQL中分为随机采样，桶表采样，块采样三种

随机抽样：Random

用到rand()函数， limit限制抽样个数
优点: 非常随机，缺点: 速度慢
推荐使用distribte+sort，不要order by，因为可以确保数据随机分布式mapper和reducer之间

--随机从学生表里查看两个学生的所有信息
select * from student distribute by rand()  sort by rand() limit 2;

数据块抽样：Block

用到tablesample()函数, 允许随机获取n行数据,百分比数据,或指定大小数据
粒度是基于HDFS块的大小
优点：速度快，缺点：不随机

--查看学生表一行的学生所有信息
select * from student tablesample(1 rows);  

--查看学生表50%的学生所有信息
select * from student tablesample(50 percent);  

--查看学生表3k的学生所有信息
select * from student tablesample(3k);

分桶表抽样：Bucket table

语法：

tablesample (bucket x out of y  [on colname])   
--y必须是table总bucket数的倍数或因子，hive会根据y的大小，决定抽样的比例。分桶表中分桶个数的倍数或因子,
	--例如，table总共分了4桶，当y=2时，抽取4/2=2个bucket的数据，当y=8时，抽取4/8=1/2个bucket的数据。
--x表示从哪个bucket开始抽,x必须小于等于y，否则会报错
	--x=1表示从第一个桶开始抽
--on colname表示根据什么抽,
	--on rand()表示随机抽,
	--on 分桶字段 表示根据分桶字段抽样，效率更高，推荐使用

优点: 又快又随机

执行流程:

--1.创建原表
create table itheima.t_usa_covid19(
	count_date string,
  county string,
  state string,
  fips int,
  cases int,
  deaths int
)row format delimited fields terminated by ',';

--2.将数据上传至原表中
hadoop fs -put us_covid19_counties.dat /user/hive/warehouse/itheima.db/t_usa_covid19

--3. create 创建分桶表,指定分桶字段且分为几桶(比如5桶),分桶字段state
create table itheima.t_usa_covid19_bucket(
	count_date string,
  county string,
  state string,
  fips int,
  cases int,
  death int
)clustered by (state) into 5 buckets;--分桶字段一定是表中已存在的字段

--4. set开启分桶功能
set hive.enforce.bucketing = true;

--5. insert into将原表数据插入到分桶表当中
insert into t_usa_covid19_bucket select * from t_usa_covid19;

--6. 验证
select * from t_usa_covid19_bucket;

--7. 进行抽样: 
--在美国新冠疫情分桶表中的第1个桶中随机抽1/5个部分的数据
select * from t_usa_covid19_bucket tablesample(bucket 1 out of 5 on  rand()); 
--在美国新冠疫情分桶表中的第2个桶中按分桶字段state抽1/5个部分的数据   按分桶字段抽效率更高
select * from t_usa_covid19_bucket tablesample(bucket 2 out of 5 on  state);

聚合函数

collect_set

连续问题

统计连续N次登录 / 连续下单 / 连续访问的用户

实现思路：要想找到连续登录用户的核心，必须找到相同用户id与登陆日期的关系。比如统计连续登录两天的，只要用户id相等，登录日期差一天即可。有两种实现思路：

连续两天登录:

思路一：表中数据自连接，构建笛卡尔积自连接。

关键字段：substr：字符串截取

select distinct(a_userid) 
from tb_login_tmp
where a_userid = b_userid  --两边id相等
and cast(substr(a_logintime,9,2)as int)-1= cast(substr(b_logintime,9,2) as int);  --日期相差一天

思路二：使用lead窗口函数。

分析：

连续两天登录：本次登录以后的第二天和用户下次登陆的时间相等
连续三天登录：本次登录以后的第三天和用户下下次登陆的时间相等
根据用户id分区，按登录时间排序

关键字段: userid,loginTime

用到: lead()函数用于从当前数据中基于当前行的数据向后取几行，另外有个lag是向前取几行

语法：lead（colName，N，defaultValue）

colName：取哪一列的值
N：向后偏移N行
defaultValue：娶不到返回默认值

with t1 as(    --with这个叫CTE表示式
	select 
		userid,
		logintime,
		date_add(loginTime,1) as nextday,  --用户第二天登录
		lead(logintime,1,0) over (partition by userid order by loginTime) as nextlogin   --用户下一次登录的时间，这里形成一个错位
	from tb_login)
select distinct userid from t1 where nextday = nextlogin;--下一次登录数据偏移上来如果跟你第二次登录的日期相等，就匹配上了

连续7天登录:

思路：下次登录的时间

with t2 as (
	select 
		userid, 
		logintime, 
		date_add(logintime,7-1) as nextday, --本次登录日期往后加6天
		lead(logintime,7-1,0) over (partition by userid order by logintime) as nextlogin from tb_login ) --向下取6天，如果+6天后登录日期和向下取6天的数据相等，就匹配上了
select distinct userid from t2 where nextday = nextlogin;

统计每个用户，每个月消费总金额，以及当前累计消费金额

方案一：自连接

--按照每个用户分组,求每个用户每个月消费总金额
create table tb_money_mtn as 
select userid,mth,sum(money) as m_money
from tb_money
group by uesrid,mth;

方案二：sum窗口函数

功能：用于实现窗口数据的求和
语法：sum（colName）over（partition by col order by col）
colName：用于对某一列的值求和
基于每个用户每个月的消费金额，可通过窗口函数对用户分区，按照月份排序
基于聚合窗口，从每个分区的第一行累加到当前行可得累计消费金额

 --按照每个用户分组,求每个用户消费总金额,并按消费额大小排序
 select 
   userid, mth, m_money, sum(m_money) 
 over (partition by userid order by mth) as t_money 
 from tb_money_mtn;

留存问题

思路分析

按天留存率计算就是指用户在首日新增后，在接下来的后推第N天活跃情况，用后推第N天活跃的用户 / 首日新增用户。就得到后推第N天的新增用户留存率。留存率计算案例 1月1日，新增用户200人; 次日留存：第2天，1月2日，这200人里面有100人活跃，则次日留存率为： 100 / 200 = 50% 2日留存：第3天，1月3日;这200名新增用户里面有80人活跃，第3日新增留存率为:80/200 = 40%; 7日留存：第8天，1月8日，这200名新增用户里面有25人活跃，第7日新增留存率为:25/200 = 12.5%;

步骤

① 首先查询时间内的 每天新增用户 (t_new)

-- 为了简化问题，假设有一张new_users表记录了每天的新增用户
select 
	user_id,--用户id
	dt as new_dt --新增日期
from new_users
where dt >= '2020-05-01' and dt<= '2020-06-01'; //t_new

② 查询时间范围的 每天活跃用户数 (t_active)

--为了简化问题，假设有一张active_users表记录了每天的活跃用户数
select
	user_id, --用户id
	dt as active_dt, --活跃日期
from active_users
where dt >= '2020-05-01' and dt<= '2020-06-01'; // t_active

③ 统计当天注册且第N日活跃的用户 (t3)：以①为主表 left join ②

select
	t_new.new_dt, --用户注册时间
	count(distinct t_new.user_id) as cnt_01, --当日注册且当日活跃的用户(当日注册肯定活跃)
	count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 1,t_new.user_id,null)) as cnt_02,--当日注册 且 第二日活跃的用户
	count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 2,t_new.user_id,null)) as cnt_03,--当日注册 且 第三日活跃的用户
	count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 4,t_new.user_id,null)) as cnt_05,--当日注册 且 第五日活跃的用户
	count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 6,t_new.user_id,null)) as cnt_07,--当日注册 且 第七日活跃的用户
	count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 14,t_new.user_id,null)) as cnt_15,--当日注册 且 第十五日活跃的用户
	
from  t_new
left join
t_active on t_new.user_id = t_active.user_id
group by t_new.new_dt; // t3

④ 统计留存: 用后推第N天活跃的用户 / 首日新增用户即 cnt_0n / cnt_01

select
	t.new_dt,--注册时间
	t.cnt_01,--当日注册用户
	round(cnt_02/ cnt_01 * 100, 2) as keep_02, -- 次日留存
	round(cnt_03/ cnt_01 * 100, 2) as keep_03, -- 3日留存
	round(cnt_05/ cnt_01 * 100, 2) as keep_05, -- 5日留存
	round(cnt_07/ cnt_01 * 100, 2) as keep_07, --7日留存
	round(cnt_15/ cnt_01 * 100, 2) as keep_15, -- 15日留存
from t3

完整代码

select
t.new_dt,--注册时间
t.cnt_01,--当日注册用户
round(cnt_02/ cnt_01 * 100, 2) as keep_02, -- 次日留存
round(cnt_03/ cnt_01 * 100, 2) as keep_03, -- 3日留存
round(cnt_05/ cnt_01 * 100, 2) as keep_05, -- 5日留存
round(cnt_07/ cnt_01 * 100, 2) as keep_07, --7日留存
round(cnt_15/ cnt_01 * 100, 2) as keep_15, -- 15日留存

from
(
	select
		t_new.new_dt, --用户注册时间
		count(distinct t_new.user_id) as cnt_01, --当日注册且当日活跃的用户
		count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 1,t_new.user_id,null)) as cnt_02,--当日注册 且 第二日活跃的用户
		count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 2,t_new.user_id,null)) as cnt_03,--当日注册 且 第三日活跃的用户
		count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 4,t_new.user_id,null)) as cnt_05,--当日注册 且 第五日活跃的用户
		count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 6,t_new.user_id,null)) as cnt_07,--当日注册 且 第七日活跃的用户
		count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 14,t_new.user_id,null)) as cnt_15,--当日注册 且 第十五日活跃的用户
		
	from
	( -- 新增用户
		select 
			user_id,--用户id
			dt as new_dt --新增日期
		from new_users
		where dt >= '2020-05-01' and dt<= '2020-06-01'
	)t_new
	left join
	( --活跃用户
		select
			user_id, --用户id
			dt as active_dt, --活跃日期
		from active_users
		where dt >= '2020-05-01' and dt<= '2020-06-01'
	)t_active on t_new.user_id = t_active.user_id
	group by t_new.new_dt
)t

拉链表

设计初衷：每日需要保存前一日的数据做保存和更新,对既想保存历史状态，又要保存新增的状态，用拉链表

拉链表核心：有startTime和endTime两个字段来保存数据的状态变化

实现过程: 1.采集最新的数据 2.把新增的数据和拉链表的数据合并成临时表 3.把临时表的数据覆盖到拉链表中

-- 创建拉链表
create table dw_zipper(
	userid string,
	phone string,
	nick string,
	gender int,
	addr string,
	starttime string,
	endtime string
)row format delimited fields terminated by '\t';

-- 加载模拟数据
load data local inpath '/root/hivedata/zipper.txt' into table dw_zipper;

-- 查询
select userid,nick,addr,starttime,endtime from dw_zipper;

-- 创建ods层增量表 加载数据
create table ods_zipper_update(
	userid string,
	phone string,
	nick string,
	gender int,
	addr string,
	starttime string,
	endtime string
)row format delimited fields terminated by '\t';
-- 加载模拟增量数据
load data local inpath '/root/hivedata/update.txt' into table ods_zipper_update;
-- 查询
select * from ods_zipper_update;

-- 合并数据
-- 创建临时表
create table tmp_zipper(
	userid string,
	phone string,
	nick string,
	gender int,
	addr string,
	starttime string,
	endtime string
)row format delimited fields terminated by '\t';

Hive中多字节分隔符的处理

情况一: 每一行数据时多字节分隔符,"||","--"等

情况二: 数据字段中包含了分隔符

字符间分隔符时空格,但是字符串内部又有空格分隔

方案一：替换分隔符

写个Java程序,将数据中的多字节分隔符提前替换为单字节分隔符

方案二：RegexSerDe正则加载

除了LazySimpleSerDe，Hive还内置了很多SerDe类，用于解析和加载不同类型的数据文件。
常用的有ORCSerDe，RegexSerDe，JsonSerde等
RegexSerDe该类的难度在于正则的编写

案例一：

用RegexSerDe类解析多字节分隔符的文档，如下：

--1.建表
create table singer(
	id string,
  name string,
  country string,
  province string,
  gender string,
  works string
) row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' 
with serdeproperites("input.regex" = "([0-9]*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)\\|\\|('男'|'女')\\|\\|(.*)") stored as textfile;

--2.加载数据
load data local inpath '/root/hivedata/test01' into table singer;

--3.验证
select * from singer;

案例二：

--1.建表
create table apachelog(
	id string,
  time string,
  method string,
  url string,
  policy string,
  stat string,
  body string
) row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' 
with serdeproperites("input.regex" = "([^ ]*) ([^}]*) ([^ ]*) ([^ ]*) ([^ ]*) ([0-9]*) ([^ ]*))") stored as textfile;

--2.加载数据
load data local inpath '/root/hivedata/apache_web_access.log' into table apachelog;

--3.验证
select * from apachelog;

方案三：自定义(不推荐)

需要自己写一个Java类，就是在读文件的时候，把多字节直接读成单字节分隔符
add jar命令将打好的jar包上传到Hive classPath中

Hive中 URL格式解析

经常用于网站的用户行为分析,例如对用户访问页面进行统计分析,分析热门受访页面的TOP10,观察用户最喜欢的用户网站

parse_url函数:

特征: 一进一出函数

select parse_url('http://facebook.com/path/p1.php?id=10086','HOST'); --提取域名
select parse_url('http://facebook.com/path/p1.php?id=10086&name=allen',QUERY','name'); --提取参数名

弊端: 如果想解析多个参数,需要使用多次select函数

parse_url_tuple函数:

特征: 多进多出函数,UDTF函数

--一次性提取域名和路径
select parse_url_tuple(url,"HOST","PATH")  as (host,path) from tb_url;  

--如果要写其他字段必须与lateral view放在一起使用
select 
	a.id as id,
	b.host as host,
	b.path as path,
	c.protocol as protocol,
	c.query as query 
  from tb_url a 
  lateral view parse_url_tuple(url,"HOST","PATH","QUERY") b as host,path
  lateral view parse_url_tuple(url,"PROTOCOL","QUERY") c as protocol,query;   --可以多个侧视图进行join

--如果UDTF不产生数据,这时侧视图与原表关联的结果为空; 但如果加上outer以后,就会保留原表数据

select id,url,col1 from tb_url lateral view explode(array()) et as col1;  --结果显示为空
select id,url,col1 from tb_url lateral view outer explode(array()) et as col1;  --结果为原表数据

行列转换

多行转多列:

case when 函数

-- 语法一: 

select id,
case 
when id<2 then 'a'
when id=2 then 'b'
else 'c' 
end as caseName
  from tb_url;

-- 语法二:  (相当于精准匹配,这种没法写<>,只能等于某个值)
select id,
	case **id**
	when 1 then 'a'
	when 2 then 'b'
	else 'c' end as caseName   --转换后的字段起名不起名都可以
from tb_url;

需求:

select col1 as col1,
	case col2 when 'c' then col3 else 0 end as c,
	case col2 when 'd' then col3 else 0 end as d,
	case col2 when 'e' then col3 else 0 end as e
from row2col1
group by col1;

多行转单列:

拼接函数: concat_ws函数

功能: 可以实现字符串拼接,并且可以指定分隔符,如果拼接的元素中有一个元素为空,结果就为null

收集函数:

--collect_list(不去重) ,只接受字符串类型或者array类型
select collect_list(col1) from row2col1;            //["a","a","a","b","b","b"]

--collect_set(去重)  
select collect_set(col1) from row2col1;           //["b","a"]

功能这两个都可以将多行数据收集为一行

需求:

select col1,col2,
	concat_ws(',',collect_list (cast( col3 as string ))) as col3
from row2col2
group by col1,col2;

多列转多行:

结果合并函数
- union ,将多个select的结果合并成一个,结果去重且排序
- union all ,将多个select的结果合并成一个,结果不去重不排序

实现需求:

select col1,'c' as col2,col2 as col3 from col2row1
union all
select col1, 'd' as col2, col3 as col3 from col2row1
union all 
select col1, 'e' as col2, col4 as col3 from col2row1;

单列转多行:

炸开函数:

lateral view explode() as aaa;

实现需求:

select col1,col2,
	lv.col3 as col3 
from col2row2 lateral view explode(split(col3,',')) lv as col3;

Json字符串处理

需求处理:

方案一:

用Json函数:

get_json_object函数

特征: 该函数一次只能解析一个参数

select 
	get_json_object(json,"**$.**device") as device,          
	get_json_object(json,"$.signal") as signal,
	get_json_object(json."$.time") as time
from tb_json_test;

json_tuple函数

特征: UDTF函数,一般搭配lateral view使用

--单独使用: 
select json_tuple(json,"device","signal","time") as (device,signal,time) 
from tb_json_test;

--搭配侧视图使用:
select json,device,signal,time 
from tb_json_test1 lateral view json_tuple(json,"device","signal","time") b as device,signal,time;

方案二:

JsonSerDe函数

用Json类,在建表的时候就把Json读进去

步骤:

create table 表名() row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
load加载数据到表就行了

总结:

如果数据中每行只有个别字段时Json字段,使用Json函数
如果文件整体就是Json文件,用JsonSerDe处理更方便一些

Hive优化

表设计优化

Hive查询基本原理

Hive的设计思想是通过元数据解析将HDFS上的文件映射成表
基本的查询原理是当用户通过HQL对Hive中的表进行复杂数据的处理和计算时，默认将其转换为分布式计算MapReduce程序对HDFS中的数据进行读取的过程。
当执行查询时，hive会使用表的最后一级目录作为底层处理数据的输入

基于分区表的设计

查询先检索元数据，元数据记录该表为分区表并且过滤条件为分区字段，所以找到该分区对应的HDFS目录

通过查看执行计划后发现，如果不做分区表结构：扫描时读取全表，做分区表后只读取对应分区的数据

分桶表的设计

注意：如果分桶字段不是join字段，分桶将没有任何意义

索引设计

3.0不再支持的原因时Hive中构建索引的过程太过繁琐！！！

文件格式优化

TextFile格式

SequenceFile格式

--1.建表，指定Sequencefile格式存储（二进制）
create table tb_sogou_seq(
	time string,
  userid string,
  keyword string,
  clickorder string,
  url string
)row format delimited 
fields terminated by '\t'
stored as sequencefile;

--2.装载数据
insert into tb_sogou_seq select * from tb_sogou_source;
--不能用load装载数据的原因是load只能做单纯的复制移动数据的操作

Parquet格式

--1.建表，指定Sequencefile格式存储（二进制）
create table tb_sogou_seq(
	time string,
  userid string,
  keyword string,
  clickorder string,
  url string
)row format delimited 
fields terminated by '\t'
stored as parquet;

--2.装载数据
insert into tb_sogou_par select * from tb_sogou_source;
--不能用load装载数据的原因是load只能做单纯的复制移动数据的操作
--用insert底层走的是MR程序

ORC格式

--1.建表，指定Sequencefile格式存储（二进制）
create table tb_sogou_orc(
	time string,
  userid string,
  keyword string,
  clickorder string,
  url string
)row format delimited 
fields terminated by '\t'
stored as orc;

--2.装载数据
insert into tb_sogou_orc select * from tb_sogou_source;
--不能用load装载数据的原因是load只能做单纯的复制移动数据的操作
--用insert底层走的是MR程序

压缩优化

压缩本质是通过算法对数据进行重新的编制

压缩配置

--开启hive中间传输数据压缩功能
--1.开启hive中间传输数据压缩功能
set hive.exec.compress.intermediate = true;
--2.开启mapreduce中map输出压缩功能
set mapreduce.map.output.compress = true;
--3.设置mapreduce中map输出数据的压缩方式
set mapreduce.map.output.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

--开启Reduce输出阶段压缩
--1.开启hive最终输出数据压缩功能
set hive.exec.compress.output = true;
--2.开启mapreduce最终输出数据压缩
set mapreduce.output.fileoutputformat.compress = true;
--3.设置mapreduce最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
--4.设置mapreduce最终数据输出压缩为块压缩
set mapreduce.output.fileoutputformat.compress.type = BLOCK;

--上面这些设置开启以后，创建表时不管采用什么文本格式，hive就会自动和压缩做一个配合

存储优化

--避免小文件产生的参数
--如果hive的程序，只有maptask，将MapTask产生的所有小文件进行合并
set hive.merge.mapfiles = true;
--如果hive的程序，有maptask和reduceTask，将ReduceTask产生的所有小文件进行合并
set hive.merge.mapredfiles = true;
--每个合并文件的大小（244M）
set hive.merge.size.per.task = 256000000；
--平均每个文件的大小，如果小于这个值就会进行合并（15M）
set hive.merge.smallfiles.avgsize = 16000000;

--设置hive底层MapReduce读取数据的输入类：将所有文件合并成一个大文件作为输入
set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

--1.开启索引配置
set hive.optimize.index.filter = true;

--2.创建表并指定构建索引
create table tb_sogou_orc_index
	stored as orc tblproperties("orc.create.index"="true")
as select * from tb_sogou_source
distribute by time
sort by time;

--3.当进行范围或等值查询时（<,>,=），就可以基于构建的索引进行查询
select count(*) from tb_sogou_orc_index where time > '12:00:00' and time < '18:00:00'

--创建表指定创建布隆索引
create table tb_sogou_orc_bloom
sotred as orc tblproperties("orc.create.index"="true","orc.bloom.filter.columuns"="time,userid")
as select * from tb_sogou_source
distribute by time
sort by time;

--time的范围过滤可以走row group index，userid的过滤可以走bloom filter index
select count(*) 
from tb_sogou_orc_index
where time>'12:00:00' and time < '18:00:00' and userid = '23423452';

--开启orc矢量化查询
set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;

Job执行优化

explain执行计划

MapReduce属性优化

--开启本地模式
set hive.exec.mode.local.auto = true;--开启之后它会自己判断数据量是否满足本地模式执行条件
--下面是满足本地模式运行的条件，三者同时满足才可以走本地模式（这个不能在idea上操作，因为idea没有本地运行环境，要在beeline黑窗口运行）
1.输入文件不超过128M
2.maptask的个数少于4个
3.reducetask的个数要么是1，要么是0

--hadoop3版本已经不支持该选项了
set mapduce.job.jvm.numtasks = 10;

--开启stage并行化，默认为false
set hive.exec.parallel = true;
--指定并行化线程数，默认为8；
set hive.exec.parallel.thread.number = 16;

Join优化

Map Join

默认情况下，Hive中默认自动开启了Map Join

Reduce Join

Hive会自动判断是否满足Map Join，如果不满足自动执行Reduce Join

Bucket Join

基于桶之间join，而不是整张表之间join，减少笛卡尔积
要求分桶字段为Join字段

set hive.optimize.bucketmapjoin = true;

Bucket Join升级版

关联优化

--hive比较泵，默认会按方案一执行，我们可以通过设置下面的参数
--设置关联优化：将有关联的的操作尽量放在同一个MR中实现
set hive.optimize.correlation = true;

优化器引擎优化

--设置为CBO优化器引擎
set hive.cbo.enable = true;
set hive.compute.query.using.stats = true;
set hive.stats.fetch.column.stats = true;

谓词下推（PDD）

hive.optimize.ppd = true;--这个在hive中是默认开启的

--举例：谓词下推的
select a.id,a.value,b.value from table1 a 
join (select b.* from table2 b where b.ds>='20181201' and b.ds<'20190101') c
on (a.id=c.id)

--无谓词下推
select a.id,a.value,b.value from table1 a 
join table2 b on a.id=b.id
where b.ds>='20181201' and b.ds<'20190101'

数据倾斜

--开启运行过程中的skew join
set hive.optimize.skewjoin = true;
--如果这个key的出现超过这个范围
set hive.skewjoin.key = 100000;
--在编译时判断是否会产生数据倾斜
set hive.optimize.skewjoin.compiletime = true;
--不合并，提升性能
set hive.optimize.union.remove = true;
--如果hive底层走的MapReduce，必须开启这个属性，才能实现不合并
set mapreduce.input.fileinputformat.input.dir.recursive = true;

hive函数大全

三、条件函数

1. If函数: if
2. 空查找函数: nvl
3. 非空查找函数:COALESCE
4. 条件判断函数:CASE
5. 条件判断函数:CASE
6. 空值判断函数:isnull
7. 非空值判断函数:isnotnull

四、字符函数

1. 首字符ascii函数:ascii
2. 转换成64位的字符串:base64
3. 字符串连接函数:concat
4. 字符串连接函数:context_ngrams
5. 带分隔符字符串连接函数:concat_ws
6. 指定的字符集将二进制值bin解码成字符串:decode
7. 指定的字符集charset将字符串编码成二进制值:encode
8. 集合查找函数:find_in_set
9. 将数值X转换成"#,###,###.##"格式字符串:format_number
10. json解析函数:get_json_object
11. 文件数据与字符串str匹配: in_file
12. 查找字符串str中子字符串substr出现的位置:instr
13. 字符串长度函数:length
    14.第一次出现的位置:locate
14. 字符串转小写函数:lower,lcase
15. 左补足函数:lpad
16. 左边去空格函数:ltrim
    18.返回出现次数TOP K的的子序列:ngrams
17. URL解析函数:parse_url
18. printf风格格式输出字符串:printf
19. 正则表达式解析函数:regexp_extract
20. 正则表达式替换函数:regexp_replace
21. 重复字符串函数:repeat
22. 字符串反转函数:reverse
23. 右补足函数:rpad
24. 右边去空格函数:rtrim
25. 字符串str将被转换成单词数组:sentences
26. 空格字符串函数:space
27. 分割字符串函数: split
28. 字符串str按照指定分隔符转换成Map: split
29. 字符串截取函数:substr,substring
30. 字符串截取函数:substr,substring
31. 截取第count分隔符之前的字符串:substring_index
32. 字符串替换成to中的字符串:substring_index
33. 去空格函数:trim
34. 将64位的字符串转换二进制值:trim
35. 字符串转大写函数:upper,ucase
    38.首字母大写函数:initcap
36. 两个字符串之间的差异大小: levenshtein
    40.字符串转换成soundex字符串:soundex

五、聚合函数

1. 个数统计函数: count
2. 总和统计函数: sum
3. 平均值统计函数: avg
4. 最小值统计函数: min
5. 最大值统计函数: max
6. 非空集合总体变量函数:var_pop
7. 非空集合样本变量函数:var_samp
8. 总体标准偏离函数:stddev_pop
9. 样本标准偏离函数:stddev_samp
   10.协方差函数:covar_pop
10. 样本协方差函数:covar_samp
    12.相关系数函数:corr
    13．中位数函数:percentile
11. 中位数函数:percentile
12. 近似中位数函数:percentile_approx
13. 近似中位数函数:percentile_approx
14. 直方图:histogram_numeric

六、表生成函数

1. explode
2. explode
3. explode
4. posexplode
5. posexplode
6. posexplode
7. parse_url_tuple
8. parse_url_tuple
   七、类型转换函数
9. 转换成二进制: binary
10. expr转换成type类型: cast
    八、集合函数
11. Map类型长度函数: size(Map
    一、数学函数
12. 取整函数: round
    语法: round(double a)

返回值: double

说明:返回double类型的整数值部分（遵循四舍五入）

举例：
hive> select round(10.235);
OK
10.0

2. 指定精度取整函数: round
   语法: round(double a, int d)

返回值: DOUBLE

说明:返回指定精度d的double类型

举例：
hive> select round(3.141592,4);
OK
3.1416

3. 向下取整函数: floor
   语法: floor(double a)

返回值: BIGINT

说明:返回等于或者小于该double变量的最大的整数

举例：

hive> select floor(3.1415926);
OK
3

hive> select floor(-10.2);
OK
-11

4. 向上取整函数: ceil
   语法: ceil(double a)

返回值: BIGINT

说明:返回等于或者大于该double变量的最小的整数

举例：
hive> select ceil(10.1);
OK
11

5. 向上取整函数: ceiling
   语法: ceiling(double a)

返回值: BIGINT

说明:与ceil功能相同

举例：
hive> select ceiling(3.1415926);
OK
4

6. 取随机数函数: rand
   语法: rand(),rand(int seed)

返回值: double

说明:返回一个double型0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列

举例：
hive> select rand();
OK
0.5577432776034763

hive> select rand();
OK
0.6638336467363424

hive> select rand(100);
OK
0.7220096548596434

hive> select rand(100);
OK
0.7220096548596434

7. 自然指数函数: exp
   语法: exp(double a)

返回值: double

说明:返回自然对数e的a次方,a可为小数

举例：
hive> select exp(3);
OK
20.085536923187668

8.自然对数函数: ln
语法: ln(double a)

返回值: double

说明:返回a的自然对数，a可为小数

举例：
hive> select ln(7.38905609893065);
OK
2.0

9. 以10为底对数函数: log10
   语法: log10(double a)

返回值: double

说明:返回以10为底的a的对数，a可为小数

举例：
hive> select log10(100);
OK
2.0

10. 以2为底对数函数: log2
    语法: log2(double a)

返回值: double

说明:返回以2为底的a的对数，a可为小数

举例：
hive> select log2(8);
OK
3.0

11. 对数函数: log
    语法: log(double base, double a)

返回值: double

说明:返回以base为底的a的对数，base 与 a都是double类型

举例：
hive> select log(4,256);
OK
4.0

12. 幂运算函数: pow
    语法: pow(double a, double p), power(double a, double p)

返回值: double

说明:返回a的p次幂

举例：
hive> select pow(2,4);
OK
16.0

13. 开平方函数: sqrt
    语法: sqrt(double a)

返回值: double

说明:返回a的平方根

举例：
hive> select sqrt(16);
OK
4.0

14. 二进制函数: bin
    语法: bin(BIGINT a)

返回值: string

说明:返回a的二进制代码表示,，a为BIGINT类型

举例：
hive> select bin(7);
OK
111

15. 十六进制函数: hex
    语法: hex(BIGINT a),hex(string a)

返回值: string

说明:如果变量是int类型，那么返回a的十六进制表示；如果变量是string类型，则返回该字符串的十六进制表示

举例：
hive> select hex(17);
OK
11

hive> select hex(‘abc’);
OK
616263

16. 反转十六进制函数: unhex
    语法: unhex(string a)

返回值: string

说明:返回该十六进制字符串所代码的字符串,hex的逆方法

举例：
hive> select unhex(‘616263’);
OK
abc

17. 进制转换函数: conv
    语法: conv(BIGINT num, int from_base, int to_base),conv(STRING num, int from_base, int to_base)

返回值: string

说明:将bigint/string数值num从from_base进制转化到to_base进制

举例：
hive> select conv(17,10,16);
OK
11

hive> select conv(17,10,2);
OK
10001

18. 绝对值函数: abs
    语法: abs(double a),abs(int a)

返回值: double or int

说明:返回数值a的绝对值

举例：
hive> select abs(-3.9);
OK
3.9

19. 正取余函数: pmod
    语法: pmod(int a, int b),pmod(double a, double b)

返回值: int or double

说明:返回正的a除以b的余数

举例：
hive> select pmod(9,4);
OK
1

hive> select pmod(-9,4);
OK
3

20. 正弦函数: sin
    语法: sin(double a)

返回值: double

说明:返回a的正弦值

举例：
hive> select sin(0.8);
OK
0.7173560908995228

21. ```
    21. 反正弦函数: asin
        语法: asin(double a)
    
    返回值: double
    
    说明:返回a的反正弦值
    
    举例：
    hive> select asin(0.7173560908995228);
    OK
    0.8

22. 余弦函数: cos
    语法: cos(double a)

返回值: double

说明:返回a的余弦值

举例：
hive> select cos(0.9);
OK
0.6216099682706644

23. 反余弦函数: acos
    语法: acos(double a)

返回值: double

说明:返回a的反余弦值

举例：
hive> select acos(0.6216099682706644);
OK
0.9

24.

正切函数:tan
语法: tan(double a)

返回值: double

说明:返回a的正切值

举例:
hive> select tan(0.8);
OK
1.0296385570503641
```

25.

反正切函数:atan
语法: atan(double a)

返回值: double

说明:返回a的反正切值

举例:
hive> select atan(1.0296385570503641);
OK
0.8

26.

 弧度值转换角度值:degrees
 语法: degrees(double a)
 
 返回值: double
 
 说明:返回a的角度值
 
 举例:
 hive> select degrees(1);
 OK
 57.29577951308232
 ```
 
 27.角度值转换成弧度值:radians
 语法: radians(double a)
 
 返回值: double
 
 说明:返回a的弧度值
 
 举例:
 hive> select radians(57.29577951308232);
 OK
 1.0
 
 28. positive函数: positive
     语法: positive(int a), positive(double a)
 
 返回值: int or double
 
 说明:返回a
 
 举例：
 hive> select positive(-10);
 OK
 -10
 
 29. negative函数: negative
     语法: negative(int a), negative(double a)
 
 返回值: int or double
 
 说明:返回-a,a的相反数
 
 举例：
 hive> select negative(-5);
 OK
 5
 
 30.判断正负函数:sign
 语法: sign(double a)
 
 返回值: double
 
 说明:如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0
 
 举例：
 hive> select sign(-4);
 OK
 -1.0
 
 31.数学e函数:e
 语法: e()
 
 返回值: double
 
 说明:数学常数e
 
 举例：
 hive> select e();
 OK
 2.718281828459045
 
 32.数学pi函数:pi
 语法: pi()
 
 返回值: double
 
 说明:数学常数e
 
 举例：
 hive> select pi();
 OK
 3.141592653589793
 
 33.阶乘函数:factorial
 语法: factorial(int a)
 
 返回值: BIGINT
 
 说明:求a的阶乘
 
 举例：Hive 1.2.0,版本原因无法测试
 
 34.立方根函数:cbrt
 语法: cbrt(double a)
 
 返回值: double
 
 说明:求a的立方根
 
 举例：Hive 1.2.0,版本原因无法测试
 
 35.左移函数:shiftleft
 语法:shiftleft(BIGINT a, int b)
 
 返回值: INT BIGINT
 
 说明:按位左移
 
 举例：Hive 1.2.0,版本原因无法测试
 
 36.右移函数:shiftright
 语法:shiftright(BIGINT a, int b)
 
 返回值: INT BIGINT
 
 说明:按位右移
 
 举例：Hive 1.2.0,版本原因无法测试
 
 37.无符号按位右移函数:shiftrightunsigned
 语法:shiftrightunsigned(BIGINT a, int b)
 
 返回值: INT BIGINT
 
 说明:无符号按位右移（<<<）
 
 举例：Hive 1.2.0,版本原因无法测试
 
 38.求最大值函数:greatest
 语法:greatest(T v1, T v2, …)
 
 返回值:T
 
 说明:求最大值
 
 举例：
 hive> select greatest(1,2,3);
 OK
 3
 
 39.求最小值函数:least
 语法:least(T v1, T v2, …)
 
 返回值:T
 
 说明:求最小值
 
 举例：
 hive> select least(1,2,3);
 OK
 1
 
 40.银行家舍入法函数:bround
 语法:bround(double a)
 
 返回值:double
 
 说明:银行家舍入法（1-4：舍，6-9：进，5->前位数是偶：舍，5->前位数是奇：进）
 
 举例： Hive 1.3.0, 2.0.0,版本原因无法测试
 
 41.银行家精确舍入法函数:bround
 语法:bround(double a，int d)
 
 返回值:double
 
 说明:银行家舍入法,保留d位小数
 
 举例： Hive 1.3.0, 2.0.0,版本原因无法测试

二、日期函数

时间戳转日期 from_unixtime()

from_unixtime

说明：将时间戳转换为你想要的格式，可以转成以下任意一种格式：

yyyy-MM-dd hh:mm:ss
yyyy-MM-dd hh
yyyy-MM-dd hh:mm
yyyy-MM
yyyyMM
yyyy
MM
......

语法: from_unixtime(bigint unixtime[, string format])

返回值: string

hive> select from_unixtime(1250111000,“yyyy-MM-dd”);
OK
2009-08-13

日期转时间戳 unix_timestamp()

unix_timestamp()

语法: unix_timestamp(string date)

说明:将 yyyy-MM-dd HH:mm:ss 或 yyyy-MM-dd 格式的时间字符串转换成时间戳。如果转化失败，则返回0。

返回值: bigint

select unix_timestamp('2018-12-05 01:10:00','yyyy-MM-dd HH:mm:ss');
>>>1543943400 # 10位是到秒，13位到毫秒
select unix_timestamp('2018-12-05','yyyy-MM-dd');
>>>1543939200
select unix_timestamp('20181205','yyyyMMdd');
>>>1543939200

日期转(年-月-日) to_date()

to_date

语法：to_date(string timestamp)

说明：返回日期时间字段中的年月日（不含时分秒）部分

注意：只能识别到 “年-月-日” 级别的时间，无法识别 “年-月” 级别的时间，也无法识别时分秒级

返回值：string

hive> select to_date(‘1970-01-01 23:00:00’);
OK
1970-01-01

hive> select to_date('2016-09');
OK
NULL

日期比较 datediff()

datediff

语法：datediff(string enddate, string startdate)

说明：返回结束日期减去开始日期的天数（如果一个时间参数是带时分秒，另一个不带，也可以比较）

返回值：int

hive> select datediff(‘2012-12-08’,‘2012-05-09’);
OK
213

返回指定格式 date_format()

date_format

语法：date_format(date, format)

说明：该函数可以将日期时间值转换为任意目标格式的字符串

注意：

date 参数必须是日期时间类型或可以转换为日期时间类型的字符串类型。否则会返回 NULL

返回值：string

举例：
hive> select date_format(current_date(),‘MM.dd’);
OK
07.13

hive> select date_format('2016-06-22',‘MM.dd’);
OK
06.22

SELECT date_format('2022-09-15 22:30:00', 'MMM d, yyyy'' at'' h:mm a z');
OK
Sep 15, 2022 at 10:30 PM UTC

获取当前时间戳 unix_timestamp()

unix_timestamp()

语法: unix_timestamp()

返回值: bigint

hive> select unix_timestamp();
OK
1594648263

时间提取函数

   
  6. 日期转年函数: year
     语法: year(string date)
   
返回值: int
   
说明:返回日期中的年。
   
举例：
hive> select year(“1970-01-01 00:00:00”);
OK
1970
   
  7. 日期转月函数: month
     语法: month (string date)
   
返回值: int
   
说明:返回日期中的月份。
   
举例：
hive> select month(“1970-11-01”);
OK
11
   
  8. 日期转天函数: day
     语法: day (string date)
   
返回值: int
   
说明:返回日期中的天。
   
举例：
hive> select day(“1970-11-01”);
OK
1
   
  9. 日期转小时函数: hour
     语法: hour (string date)
   
返回值: int
   
说明:返回日期中的小时。
   
举例：
hive> select hour(‘2009-07-30 12:58:59’);
OK
12
   
  10. 日期转分钟函数: minute
      语法: minute (string date)
   
返回值: int
   
说明:返回日期中的分钟。
   
举例：
hive> select minute(‘2011-12-08 10:03:01’);
OK
3
   
  11. 日期转秒函数: second
      语法: second (string date)
   
返回值: int
   
说明:返回日期中的秒。
   
举例：
hive> select second(‘2011-12-08 10:03:01’);
OK
1
   
  12. 日期转周函数:weekofyear
      语法: weekofyear (string date)
   
返回值: int
   
说明:返回时间字符串位于一年中的第几个周内.
   
举例：
   
hive>
hive> select weekofyear(‘2011-12-08 10:03:01’);
OK
49
   
 
   
  14. 日期增加函数: date_add
      语法: date_add(string startdate, int days)
   
返回值: string
   
说明:返回开始日期startdate增加days天后的日期。
   
举例：
hive> select date_add(‘2012-12-08’,10);
OK
2012-12-18
   
  15. 日期减少函数: date_sub
      语法: date_sub (string startdate, int days)
   
返回值: string
   
说明:返回开始日期startdate减少days天后的日期。
   
举例：
hive> select date_sub(‘2012-12-08’,10);
OK
2012-11-28
   
  16. 转化成指定的时区下时间戳函数: from_utc_timestamp
      语法: from_utc_timestamp(timestamp, string timezone)
   
返回值:timestamp
   
说明:如果给定的时间戳并非UTC，则将其转化成指定的时区下时间戳
   
举例：
hive> select from_utc_timestamp(‘1970-01-01 08:00:00’,‘PST’);
OK
1970-01-01 00:00:00
   
  17. 转化成UTC下的时间戳函数: to_utc_timestamp
      语法:to_utc_timestamp(timestamp, string timezone)
   
返回值:timestamp
   
说明:如果给定的时间戳指定的时区下时间戳，则将其转化成UTC下的时间戳
   
举例：
hive> select to_utc_timestamp(‘1970-01-01 00:00:00’,‘PST’);
OK
1970-01-01 08:00:00
   
18.当前时间日期函数:current_date
语法:current_date()
   
返回值:date
   
说明:返回当前时间日期
   
举例：
hive> select current_date;
OK
2020-07-13
   
18.当前时间日期函数:current_timestamp
语法:current_timestamp()
   
返回值:timestamp
   
说明:返回当前时间戳
   
举例：
hive> select current_timestamp();
OK
2020-07-13 22:52:11.309
   
19.月份增加函数:add_months
语法:add_months(string start_date, int num_months)
   
返回值:string
   
说明:返回当前时间下再增加num_months个月的日期
   
举例：
hive> select add_months(‘1996-10-21’,10);
OK
1997-08-21
   
20.最后一天的日期函数:last_day
语法:last_day(string date)
   
返回值:string
   
说明:返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss）
   
举例：
hive> select last_day(current_date());
OK
2020-07-31
   
21.下一个星期X所对应的日期函数:next_day
语法:next_day(string start_date, string day_of_week)
   
返回值:string
   
说明:返回当前时间的下一个星期X所对应的日期 如：next_day(‘2015-01-14’, ‘TU’) = 2015-01-20 以2015-01-14为开始时间，其下一个星期二所对应的日期为2015-01-20
   
举例：
hive> select next_day(current_date(),‘su’);
OK
2020-07-19
   
22.时间的最开始年份或月份函数:trunc
语法:trunc(string date, string format)
   
返回值:string
   
说明:返回时间的最开始年份或月份 如trunc(“2016-06-26”,“MM”)=2016-06-01 trunc(“2016-06-26”,“YY”)=2016-01-01 注意所支持的格式为MONTH/MON/MM, YEAR/YYYY/YY
   
举例：
hive> select trunc(current_date(),‘MM’);
OK
2020-07-01
   
23.相差的月份函数:months_between
语法:months_between(date1, date2)
   
返回值:double
   
说明:返回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个月
   
举例：
hive> select months_between(current_date(),‘2020-5-13’);
OK
2.0
   

   
25.当前星期函数:dayofweek
语法:dayofweek(date)
   
返回值:int
   
说明:返回日期那天的周几
   
举例：
hive> select dayofweek(current_date());
OK
2

26.季节函数:quarter
语法:quarter(date/timestamp/string)
   
返回值:int
   
说明:返回当前时间属性哪个季度 如quarter(‘2015-04-08’) = 2
   
举例：Hive 1.3.0，版本原因不能测试

hive里将日期格式转换为你想要的格式

移动dm不能直接转，得先转成时间戳，再把时间戳转成你要的格式。

from_unixtime(unix_timestamp(create_date),'yyyyMM')
-- 示例：
drop table hz_yk_check2_01;
create table hz_yk_check2_01 as
select 
	user_id,
	bill_no,
	county_id,
	create_chl_id, -- 入网渠道ID
	create_date -- 办卡时间
from dwfu_hive_db.a_usoc_user_attr_d  -- 个人社会属性表
where p_day='${taskid}' 
	and city_id='571' and cust_state=2 and user_state=1
	and length(bill_no)=11 and bill_no like '1%' 
	and from_unixtime(unix_timestamp(create_date),'yyyyMM') = '${l3m_mtaskid}'
;

三、条件函数

条件判断 case()

case()

格式一：

语法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f

hive> select 
					CASE 4 WHEN 5 THEN wrong
								WHEN 4 THEN right
  							ELSE is null
      		END;
OK
right

格式二：

语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e

hive> select CASE WHEN 5>0 THEN 5 WHEN 4>0 THEN 4 ELSE 0 END;
OK
5

空值查找 nvl()

nvl()

语法: nvl(T value, T default_value)

返回值: T

hive> select nvl(null,5);
OK
5

1. If函数: if
语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)

返回值: T

说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull（valueTrue，valueFalseOrNull为泛型）

举例：
hive> select if(1=1,100,200);
OK
100



3. 非空查找函数: COALESCE
语法: COALESCE(T v1, T v2,…)

返回值: T

说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

举例：
hive> select COALESCE (NULL,44,55);
OK
44



6. 空值判断函数：isnull
语法: isnull( a )
返回值:boolean

说明：如果a为null就返回true，否则返回false
举例：
hive> select isnull(5);
OK
false

7. 非空值判断函数：isnotnull
语法: isnotnull ( a )

返回值:boolean

说明：如果a为非null就返回true，否则返回false

举例：
hive> select isnotnull(5);
OK
true

四、字符函数

第N次出现位置 locate()

locate

语法:locate(string substr, string str[, int pos])

说明：查找字符串str中字符串substr第pos次出现的位置

返回值: int

hive> select locate(‘aa’,‘aabbedfaad’,2);
OK
8

 1. 首字符ascii函数：ascii
      语法: ascii(string str)
 
   返回值: int
 
   说明：返回字符串str第一个字符的ascii码
 
   举例：
   hive> select ascii(‘abcde’);
   OK
   97
 
 2. 转换成64位的字符串：base64
        语法: base64(binary bin)
 
   返回值: string
 
   说明：将二进制bin转换成64位的字符串
 
 3. 字符串连接函数：concat
        语法: concat(string A, string B…)
 
   返回值: string
 
   说明：返回输入字符串连接后的结果，支持任意个输入字符串
 
   举例：
   hive> select concat(‘abc’,‘def’);
   OK
   abcdef
 
 4. 字符串连接函数：context_ngrams
        语法: context_ngrams(array<array>, array, int K, int pf)
 
   返回值: array<struct<string,double>>
 
   说明：与ngram类似，但context_ngram()允许你预算指定上下文(数组)来去查找子序列，具体看StatisticsAndDataMining(这里的解释更易懂)
 
 5. 带分隔符字符串连接函数：concat_ws
        语法: concat_ws(string SEP, string A, string B…) | concat_ws(string SEP, array)
 
   返回值: string
 
   说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符 | 拼接Array中的元素并用指定分隔符进行分隔
 
   举例：
   hive> select concat_ws(’,’,‘abc’,‘def’,‘gh’);
   OK
   abc,def,gh
 
 6. 指定的字符集将二进制值bin解码成字符串：decode
        语法: decode(binary bin, string charset)
        返回值: string
 
   说明：使用指定的字符集charset将二进制值bin解码成字符串，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任意输入参数为NULL都将返回NULL
 
 7. 指定的字符集charset将字符串编码成二进制值：encode
        语法:encode(string src, string charset)
        返回值: binary
        说明：使用指定的字符集charset将字符串编码成二进制值，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-  8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任一输入参数为NULL都将返回NULL
 
 8. 集合查找函数:find_in_set
        语法: find_in_set(string str, string strList)
 
   返回值: int
 
   说明:返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0，如果任一参数为NULL将返回NULL回
 
   举例：
   hive> select find_in_set(‘ab’,‘ef,ab,de’);
   OK
   2
 
 9. 将数值X转换成"#,###,###.##"格式字符串：format_number
        语法: format_number(number x, int d)
 
   返回值: string
 
   说明：将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数
 
   举例：
   hive> select format_number(123345.65545,2);
   OK
   123,345.66
 
 10. json解析函数：get_json_object
         语法: get_json_object(string json_string, string path)
 
   返回值: string
 
   说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。注意此路径上JSON字符串只能由数字 字母 下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制
 
   举例：
   hive> select get_json_object(’{“store”:
 
   > {“fruit”:[{“weight”:8,“type”:“apple”},{“weight”:9,“type”:“pear”}],
   > “bicycle”:{“price”:19.95,“color”:“red”}
   > },
   > “email”:“amy@only_for_json_udf_test.net”,
   > “owner”:“amy”
   > }
   > ‘,’$.owner’);
   > OK
   > amy
 
 11. 文件数据与字符串str匹配： in_file
         语法: in_file(string str, string filename)
 
   返回值: boolean
 
   说明：如果文件名为filename的文件中有一行数据与字符串str匹配成功就返回true
 
 12. 查找字符串str中子字符串substr出现的位置：instr
         语法: instr(string str, string substr)
         返回值:int
 
   说明：查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的
 
   举例：
   hive> select instr(‘dvfgefggdgaa’,‘aa’);
   OK
   11
 
 13. 字符串长度函数：length
         语法: length(string A)
 
   返回值: int
 
   说明：返回字符串A的长度
 
   举例：
   hive> select length(‘abcedfg’);
   OK
   7
 
 14.
 
 15. 字符串转小写函数：lower,lcase
         语法: lower(string A) lcase(string A)
 
   返回值: string
 
   说明：返回字符串A的小写格式
 
   举例：
   hive> select lower(‘abSEd’);
   OK
   absed
 
 16. 左补足函数：lpad
         语法: lpad(string str, int len, string pad)
 
   返回值: string
 
   说明：将str进行用pad进行左补足到len位,从左边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分
 
   举例：
   hive> select lpad(‘abc’,10,‘td’);
   OK
   tdtdtdtabc
 
   注意：与GP，ORACLE不同，pad不能默认
 
 17. 左边去空格函数：ltrim
         语法: ltrim(string A)
 
   返回值: string
 
   说明：去掉字符串A前面的空格
   举例：
   hive> select ltrim(’ abc fg ');
   OK
   abc fg
 
 18.返回出现次数TOP K的的子序列：ngrams
   语法: ngrams(array<array>, int N, int K, int pf)
 
   返回值: array<struct<string,double>>
 
   说明：返回出现次数TOP K的的子序列,n表示子序列的长度，具体看StatisticsAndDataMining (这里的解释更易懂)
 
 19. URL解析函数：parse_url
         语法: parse_url(string urlString, string partToExtract [, stringkeyToExtract])
 
   返回值: string
 
   说明：返回从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’
 
   举例：
   hive> select parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’);
   OK
   facebook.com
 
   hive> select parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’,‘k1’);
   OK
   v1
 
 20. printf风格格式输出字符串：printf
         语法:printf(String format, Obj… args)
         返回值:string
 
   说明：按照printf风格格式输出字符串
   举例：
   hive> select printf(‘abfhg’);
   OK
   abfhg
 
 21. 正则表达式解析函数：regexp_extract
         语法: regexp_extract(string subject, string pattern, int index)
 
   返回值: string
 
   说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。注意些预定义字符的使用，如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格
 
   举例：
   hive> select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2);
   OK
   bar
 
   hive> select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 0);
   OK
   foothebar
 
   hive> select regexp_extract(‘hello,world’,’^((\w+),(\w+))$’,2);
   OK
   hello
 
   hive> select regexp_extract(‘hello,world’,’(\w+),(\w+)’,2);
   OK
   world
 
   注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则。
 
 22. 正则表达式替换函数：regexp_replace
         语法: regexp_replace(string A, string B, string C)
 
   返回值: string
 
   说明：按照Java正则表达式PATTERN将字符串INTIAL_STRING中符合条件的部分成REPLACEMENT所指定的字符串，如里REPLACEMENT这空的话，抽符合正则的部分将被去掉 如：regexp_replace(“foobar”, “oo|ar”, “”) = ‘fb.’ 注意些预定义字符的使用，如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格
 
   举例：
   hive> select regexp_replace(‘hello,world’,‘o|l’,‘e’);
   OK
   heeee,wered
 
 23. 重复字符串函数：repeat
         语法: repeat(string str, int n)
 
   返回值: string
 
   说明：返回重复n次后的str字符串
 
   举例：
   hive> select repeat(‘abc’,5);
   OK
   abcabcabcabcabc
 
 24. 字符串反转函数：reverse
         语法: reverse(string A)
 
   返回值: string
 
   说明：返回字符串A的反转结果
 
   举例：
   hive> select reverse(‘abc’);
   OK
   cba
 
 25. 右补足函数：rpad
         语法: rpad(string str, int len, string pad)
 
   返回值: string
 
   说明：从右边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分
 
   举例：
   hive> select rpad(‘abc’,10,‘td’);
   OK
   abctdtdtdt
 
 26. 右边去空格函数：rtrim
         语法: rtrim(string A)
 
   返回值: string
 
   说明：去除字符串右边的空格
 
   举例：
   hive> select rtrim(’ abc ');
   OK
   abc
 
 27. 字符串str将被转换成单词数组：sentences
         语法: sentences(string str, string lang, string locale)
 
   返回值:array<array>
 
   说明：字符串str将被转换成单词数组，如：sentences(‘Hello there! How are you?’) =( (“Hello”, “there”), (“How”, “are”, “you”) )
 
   举例：
   hive> select sentences(‘Hello there! How are you?’);
   OK
   [[“Hello”,“there”],[“How”,“are”,“you”]]
 
 28. 空格字符串函数：space
         语法: space(int n)
 
   返回值: string
 
   说明：返回n个空格
 
   举例：
   hive> select length(space(10));
   OK
   10
 
 29. 分割字符串函数: split
         语法: split(string str, string pat)
 
   返回值: array
 
   说明:按照pat字符串分割str，会返回分割后的字符串数组
 
   举例：
   hive> select split(‘abc|cde,def kyy’,’[\|, ]’);
   OK
   [“abc”,“cde”,“def”,“kyy”]
 
   hive> select split(‘abtcdtef’,‘t’);
   OK
   [“ab”,“cd”,“ef”]
 
 30. 字符串str按照指定分隔符转换成Map: split
         语法: str_to_map(text[, delimiter1, delimiter2])
 
   返回值: map<string,string>
 
   说明:将字符串str按照指定分隔符转换成Map，第一个参数是需要转换字符串，第二个参数是键值对之间的分隔符，默认为逗号;第三个参数是键值之间的分隔符，默认为"="
 
 31. 字符串截取函数：substr,substring
         语法: substr(string A, int start),substring(string A, int start)
 
   返回值: string
 
   说明：返回字符串A从start位置到结尾的字符串
 
   举例：
   hive> select substr(‘abcde’,3);
   OK
   cde
 
   hive> select substr(‘abcde’,-1); //(和ORACLE相同）
   OK
   e
 
 32. 字符串截取函数：substr,substring
         语法: substr(string A, int start, int len),substring(string A, int start, int len)
 
   返回值: string
 
   说明：返回字符串A从start位置开始，长度为len的字符串
 
   举例：
   hive> select substr(‘abcde’,3,2);
   OK
   cd
 
 33. 截取第count分隔符之前的字符串：substring_index
         语法:substring_index(string A, string delim, int count)
 
   返回值: string
 
   说明：截取第count分隔符之前的字符串，如count为正则从左边开始截取，如果为负则从右边开始截取
 
   举例： Hive 1.3.0
 
 34. 字符串替换成to中的字符串：substring_index
         语法:translate(string|char|varchar input, string|char|varchar from, string|char|varchar to)
 
   返回值: string
 
   说明：将input出现在from中的字符串替换成to中的字符串 如：translate(“MOBIN”,“BIN”,“M”)=“MOM”
 
   举例：
   hive> select translate(“MOBIN”,“BIN”,“M”);
   OK
   MOM
 
 35. 去空格函数：trim
         语法: trim(string A)
 
   返回值: string
 
   说明：去除字符串两边的空格
 
   举例：
   hive> select trim(’ abc dce’);
   OK
   abc dce
 
 36. 将64位的字符串转换二进制值：trim
         语法:unbase64(string str)
 
   返回值:binary
 
   说明：将64位的字符串转换二进制值
 
 37. 字符串转大写函数：upper,ucase
         语法: upper(string A) ucase(string A)
 
   返回值: string
 
   说明：将字符串A中的字母转换成大写字母
 
   举例：
   hive> select upper(‘abSEd’);
   OK
   ABSED
 
 38.首字母大写函数：initcap
   语法: initcap(string A)
 
   返回值: string
 
   说明：将字符串A转换第一个字母大写其余字母的字符串
 
   举例：
   hive> select initcap(‘abcd def’);
   OK
   Abcd Def
 
 39. 两个字符串之间的差异大小： levenshtein
         语法: levenshtein(string A, string B)
 
   返回值: int
 
   说明：计算两个字符串之间的差异大小 如：levenshtein(‘kitten’, ‘sitting’) = 3
 
   举例：
   hive> select levenshtein(‘kitten’, ‘sitting’);
   OK
   3
 
 40.字符串转换成soundex字符串：soundex
   语法: soundex(string A)
 
   返回值: string
 
   说明：将普通字符串转换成soundex字符串
 
   举例： Hive 1.2.0

五、聚合函数

1. ```properties

1. 个数统计函数: count
   语法:count(*), count(expr), count(DISTINCT expr[, expr…])

返回值: BIGINT

说明: count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；count(DISTINCTexpr[, expr_.])统计提供非NULL且去重后的expr表达式值的行数

2. 总和统计函数: sum
   语法: sum(col), sum(DISTINCT col)

返回值: double

说明: sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果

3. 平均值统计函数: avg
   语法: avg(col), avg(DISTINCT col)

返回值: double

说明: avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值

4. 最小值统计函数: min
   语法: min(col)

返回值: double

说明:统计结果集中col字段的最小值

5. 最大值统计函数: max
   语法: maxcol)

返回值: double

说明:统计结果集中col字段的最大值

6. 非空集合总体变量函数:var_pop
   语法: variance(col), var_pop(col)

返回值: double

说明:统计结果集中col非空集合的总体变量（忽略null），（求指定列数值的方差）

7. 非空集合样本变量函数:var_samp
   语法: var_samp (col)

返回值: double

说明:统计结果集中col非空集合的样本变量（忽略null）（求指定列数值的样本方差）

8. 总体标准偏离函数:stddev_pop
   语法: stddev_pop(col)

返回值: double

说明:该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同（求指定列数值的标准偏差）

9. 样本标准偏离函数:stddev_samp
   语法: stddev_samp (col)

返回值: double

说明:该函数计算样本标准偏离，（求指定列数值的样本标准偏差）

10.协方差函数:covar_pop 语法: covar_pop(col1, col2)

返回值: double

说明:求指定列数值的协方差

11. 样本协方差函数:covar_samp
    语法: covar_samp(col1, col2)

返回值: double

说明:求指定列数值的样本协方差

12.相关系数函数:corr 语法:corr(col1, col2)

返回值: double

说明:返回两列数值的相关系数

13．中位数函数:percentile 语法: percentile(BIGINT col, p)

返回值: double

说明:求准确的第pth个百分位数，p必须介于0和1之间，但是col字段目前只支持整数，不支持浮点数类型

14. 中位数函数:percentile
    语法: percentile(BIGINT col, array(p1 [, p2]…))

返回值: array

说明:功能和上述类似，之后后面可以输入多个百分位数，返回类型也为array，其中为对应的百分位数。

举例：

select percentile(score,<0.2,0.4>) from lxw_dual；取0.2，0.4位置的数据

15. 近似中位数函数:percentile_approx
    语法: percentile_approx(DOUBLE col, p [, B])

返回值: double

说明:求近似的第pth个百分位数，p必须介于0和1之间，返回类型为double，但是col字段支持浮点类型。参数B控制内存消耗的近似精度，B越大，结果的准确度越高。默认为10,000。当col字段中的distinct值的个数小于B时，结果为准确的百分位数

16. 近似中位数函数:percentile_approx
    语法: percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B])

返回值: array

说明:功能和上述类似，之后后面可以输入多个百分位数，返回类型也为array，其中为对应的百分位数。

17. 直方图:histogram_numeric
    语法: histogram_numeric(col, b)

返回值: array<struct {‘x’,‘y’}>

说明:以b为基准计算col的直方图信息。

举例：

hive> select histogram_numeric(100,5) 、 OK [{“x”:100.0,“y”:1.0}]

  

# 六、表生成函数

1. ```properties

1. explode
   语法: explode(array a)

返回类型: Array Type

说明：对于a中的每个元素，将生成一行且包含该元素

2. explode
   语法: explode(ARRAY)

返回类型:N rows

说明：每行对应数组中的一个元素

3. explode
   语法: explode(MAP)

返回类型:N rows

说明：每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值

4. posexplode
   语法:posexplode(ARRAY)

返回类型:N rows

说明：与explode类似，不同的是还返回各元素在数组中的位置

5. posexplode
   语法:stack(INT n, v_1, v_2, …, v_k)

返回类型:N rows

说明：把M列转换成N行，每行有M/N个字段，其中n必须是个常数

6. posexplode
   语法:json_tuple(jsonStr, k1, k2, …)

返回类型:tuple

说明：从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值

7. parse_url_tuple
   语法:parse_url_tuple(url, p1, p2, …)

返回类型:tuple

说明：返回从URL中抽取指定N部分的内容，参数url是URL字符串，而参数p1,p2,…是要抽取的部分，这个参数包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:

8. parse_url_tuple
   语法:inline(ARRAY<STRUCT[,STRUCT]>)

说明：将结构体数组提取出来并插入到表中

  

# 七、类型转换函数

1. ```properties
  1. 转换成二进制: binary
     语法: binary(string|binary)
  
  返回类型: binary
  
  说明：将输入的值转换成二进制
  
  2. expr转换成type类型: cast
     语法: cast(expr as )
  
  返回类型: Expected “=” to follow “type”
  
  cast(expr as
  
  说明：将expr转换成type类型 如：cast(“1” as BIGINT) 将字符串1转换成了BIGINT类型，如果转换失败将返回NULL

八、集合函数

1. Map类型长度函数: size(Map<K.V>)
     语法: size(Map<K.V>)

  返回值: int

  说明:返回map类型的长度

  举例：
  hive> select size(map(‘100’,‘tom’,‘101’,‘mary’));
  OK
  2

    2. array类型长度函数: size(Array)
       语法: size(Array)

  返回值: int

  说明:求数组的长度

  举例：
  hive> select size(array(‘100’,‘101’,‘102’,‘103’));
  OK
  4

    3. key函数
       语法: map_keys(Map<K.V>)

  返回值: array

  说明:返回map中的所有key

    4. value函数
       语法: map_keys(Map<K.V>)

  返回值:array

  说明:返回map中的所有value

    5. 包含value函数
       语法: array_contains(Array, value)

  返回值:boolean

  说明:如该数组Array包含value返回true。，否则返回false

    6. 排序函数
       语法:sort_array(Array)

  返回值:array

  说明:按自然顺序对数组进行排序并返回

  下面附上对照表:
  round(double d)–返回double型d的近似值（四舍五入），返回bigint型；

  round(double d,int n)–返回保留double型d的n位小数double型近似值（四舍五入）；

  floor(double d)–返回<=d的最大bigint值；

  ceil(double d)–返回>=d的最小bigint 值；

  ceiling(double d)–返回>=d的最小bigint 值；

  rand() --每行返回一个double型随机数；

  rand(int seed) --每行返回一个double型随机数，整数seed是随机因子；

  exp(double d)–返回e的 d幂次方，返回double型；

  ln(double d)–以自然数为底d的对数，返回double型；

  log10(double d)–以10为底d的对数，返回double型；

  log2(double d)–以2为底d的对数，返回double型；

  log(double base,double d)–以base为底d的对数，返回double型；

  pow(double d,double p)–d的p次幂，返回double型；

  power(double d,double p)–d的p次幂，返回double型；

  sqrt(double d)–d的平方根，返回double型；

  bin(bigint i)–二进制i的string类型；

  hex(bigint i)–十六进制i的string类型；

  hex(string str)–计算十六进制表达的str值；

  unhex(string i)–hex(string str)的逆方法；

  conv(bigint num,int from_base,int to_base)–将bigint类型的num从from_base进制转换成to_base进制，返回string类型；

  conv(string num,int from_base,int to_base)–将string类型的num从from_base进制转换成to_base进制，返回string类型；

  abs(double d)–计算double型d 的绝对值，返回double型；

  pmod(int i1,int i2)–int型i1对 int型 i2取模，结果为int型；

  pmod(double d1,double d2)–double型i1对double型 i2取模，结果为double型；

  sin(double d)–返回d的正弦值，结果为double型；

  asin(double d)–返回d的反正弦值，结果为double型；

  cos(double d)–返回d 的余弦值，结果为double型；

  acos(double d)–返回d的反余弦值，结果为double型；

  tan(double d)–返回d的正切值，结果为double型；

  atan(double d)–返回d的反正切值，结果为double型；

  degrees(double d)–将弧度值d转换成角度值，结果为double型；

  radians(double d)–将角度值d转换成弧度值，结果为double型；

  positive(int i)–等价有效表达式是+i,返回i，结果为int型；

  positive(double d)–等价有效表达式是+d，返回d,结果为double型；

  negative(int i)–等价有效表达式是-i，返回i的负数，结果为int型；

  negative(double d)–等价有效表达式是-i，返回d的负数，结果为double型；

  sign(double d)–如果d是正数的话，则返回float型1.0，如果d是负数的话，则返回-1.0，否则返回0.0；

  e()–数学常熟e,超越数；

  PI()–数学常数Pi，圆周率；

  hive函数之聚合函数

  count(*)–计算总行数，包括null值；

  count(expr)–计算expr表达式的值，非null的行数；

  count(distinct expr[,expr_.])–计算expr表达式的值排重后非null的行数；

  sum(col)–指定行的值的和；

  sum(distinct col)–排重后值的和；

  avg(col)–指定行的值的平均值；

  avg(distinct col)–排重后的值的平均值；

  min(col)–指定行的值的最小值；

  max(col)–指定行的值的最大值；

  variance(col)–返回col 中一组数值的方差；

  var_pop(col)—返回col 中一组数值的方差；

  var_samp(col)–返回col 中一组数值的样本方差；

  stddev_pop(col)–返回col 中一组数值的标准偏差；

  stddev_samp(col)–返回col 中一组数值的样本偏差；

  covar_pop(col1,col2)–返回一组数值的协方差；

  covar_samp(col1,col2)–返回一组数值的样本协方差；

  corr(col1,col2)–返回两组数值的相关系数；

  percentile(bigint int_expr,p)–int_expr在p（范围是[0,1]）处对应的百分比，其中p是double型；

  percentile(bigint int_expr,array(p1[,p2…]))–int_expr在p（范围是[0,1]）处对应的百分比，其中p是double型数组；

  percentile(double col,p[,NB])–col在p（范围是[0,1]）处对应的百分比，其中p是double型,NB是用于估计的直方图中的仓库数量（默认10000）；

  percentile_approx(double col,array(p1[,p2…])[,NB])–col在p（范围是[0,1]）处对应的百分比，其中p是double型数组,NB是用于估计的直方图中的仓库数量（默认10000）；

  histogram_numeric(col,NB)–返回NB数量的直方图仓库数组，返回结果array<struct{‘x’,‘y’}>中的值x是中心，y是仓库的高；

  collect_set(col)–返回集合col元素排重后的数组；

  set hive.map.aggr=true; --通过设置属性hive.map.aggr值为true来提高聚合性能；

  hive函数之表生成函数

  当时用表生成函数时，hive要求使用别名；

  explode(ARRAY array)–返回0到多行结果，每行都对应输入的array数组中的一个元素；

  explode(MAP map)–返回0到多行结果，每行对应每个map键-值对，其中一个字段是map键，另一个字段是对应的map值；

  explode(ARRAY a)–对于a的每个元素，explode()会生成一行记录包含这个元素；

  inline(ARRAY<STRUCT[,STRUCT]>)–将结构体数组提取出来并插入到表中；

  json_tuple(STRING jsonStr,p1p2,…,pn)–本函数可以接受多个标签名称，对于输入的json字符串进行处理，这个与get_json_object类似，不过更高效，通过一次调用就可以获得多个键值；

  parse_url_tuple(url,partname1,partname2,…,partnameN)–从url中解析出n部分信息，其输入参数是url，以及多个要抽取部分的名称。所有输入的参数类型都是string，部分名称大小写是敏感的，不应该包含空格：HOST,PATH,QUERY,REF,PROTOCOL,AUTHORITY,FILE,USERINFO,QUERY:<KEY_NAME>；

  stact(int n,col1,col2,…,colM)–把M列换成N行，每行有M/N个字段，n为常数；

  hive函数之其他内置函数

  ascii(string s)–返回字符串s中首个ASCII字符的整数型；

  base64(binary bin)–基于二进制值bin转换成基于64位的字符串；

  binary(srting s)–将输入的值转换成二进制值；

  binary(BINARY b)–将输入的值转换成二进制值；

  cast( as )–将expr转换成type类型的，例如cast(‘1’ as bigint)会将字符串转换成bigint数值类型，如果转换失败，则返回null；

  concat(binary s1,binary s2,…)–将二进制字节码按次序拼接成一个字符串；

  concat(string s1,string s2,…)–将字符串s1,s2等拼接成一个字符串，例如concat(‘ab’,‘cd’)的结果是 ‘abcd’；

  concat_ws(string separator,string s1,string s2,…)–与concat类似，不过是使用指定的分隔符进行拼接的；

  context_ngrams(array<array>,array,int K,int pf)–与ngrams类似，但是从每个外层数组的第二个单词数组来查找前K个字尾；

  decode(binary bin,string charset)–使用指定的字符集charset将二进制bin解码成字符串（支持的字符集有：‘US_ASCII’,‘IOS-8859-1’,‘UTF-8’,‘UTF-16BE’,‘UTF-16FE’,‘UTF-16’），如果任一项输入参数为null，则结果为null；

  encode(string src,string charset)–使用指定的字符集charset将字符串src编码成二进制值（支持的字符集有：‘US_ASCII’,‘IOS-8859-1’,‘UTF-8’,‘UTF-16BE’,‘UTF-16FE’,‘UTF-16’），如果任一项输入参数为null，则结果为null；

  find_in_set(string s,string commaSeparatedString)–返回在以逗号分隔的字符串中s出现的位置，如果没找到则返回null；

  format_number(number x,int d)–将数值x转换成‘#,###,###.##’格式字符串，并保留d位小数。如果d为0，那么输出值就没有小数点后面的值；

  get_json_object(string json_string,string path)–从给定路径上的json字符串中抽取json对象，并返回这个对象的json字符串形式。如果输入的json是非法的，则返回null；

  in–例如test in(val1,val2,…),其表示如果test值等于后面列表中任一值的话，则返回true；

  in_file(string s,string filename)–如果文件名为filename的文件中有完整一行数据和字符串s完全匹配的话，则返回true；

  instr(string str,string substr)–查找字符串str中子字符串substr第一次出现的位置；

  length(string s)–计算字符串s的长度；

  locate(string substr,string str[,int pos])–查找字符串str中pos位置后字符串substr第一次出现的位置；

  lower(string s)–将字符串中所有字母转换成小写字母；

  lcase(string s)–和lower()一样；

  lpad(string s,int len,string pad)–从左边开始对字符串s使用字符串pad进行填充，最终达到len长度为止。如果字符串s本身长度比len大的话，那么多余部分会被去除；

  ltrim(string s)–将字符串s前面出现的空格全部去除掉；

  ngram(array<array>,int N,int K,int pf)–估计文件中前K个字尾。pf是精度系数；

  parse_url(string url,string partname[,string key])–从url中抽取指定部分的内容。参数url表示一个url字符串，参数partname表示要抽取的部分名称，其是大小写敏感的，可选的值有：HOST,PATH,QUERY,REF,PROTOCOL,AUTHORITY,FILE,USERINFO,QUERY:；如果partname是QUERY的话，那么还需要指定第三个参数key；

  printf(string format,Obj…args)–按照printf风格格式化输出输入的字符串；

  regexp_extract(string subject,string regexp_pattern,string index)–抽取字符串subject中符合正则表达式regexp_pattern的第 index个部分的子字符串；

  regexp_relpace(string s,string regex,string replacement)–按照java正则表达式regex将字符串s中符合条件的部分替换成replacement所指定的字符串a，如果replacement部分是空的话，那么符合正则的部分将会被去除掉。如regexp_relpace(‘hive’,’[ie]’,‘z’)的结果是’hzvz’；

  repeat(string s,int n)–重复输入n次字符串s；

  reverse(string s)–反转字符串；

  rpad(string s,int len,string pad)–从右面开始对字符串s使用字符串pad进行填充，最终达到len长度为止，如果字符串s本身长度比len大的话，那么多余部分将会被去除；

  rtrim(string s)–将字符串s右面出现的空格全部去除掉；

  sentences(string s,string lang,string locale)–将输入字符串s转换成句子数组，每个句子又由一个单词数组构成，单词lang和locale是可选的，如果没有使用，则使用默认本地化信息；

  size(MAP<K.V>)–返回map中元素的个数；

  size(ARRAY)–返回数组array的元素个数；

  space(int n)–返回n个空格；

  split(string s,string pattern)–按照正则表达式pattern分割字符串s,并将分割后的部分以字符串数组的方式返回；

  str_to_map(string s,string delim1,string delim2)–将字符串s按照指定分隔符转化成map，第一个参数是输入的字符串，第二个参数是键值对之间的分隔符，第三个参数是键和值之间的分隔符；

  substr(string s,string start_index,string length)–对于字符串s，从start位置开始截取length长度的字符串，作为子字符串，例如substr(‘abcdefgh’,3,5)的结果是’cdefg’；

  substring(string s,string start_index,string length)–对于字符串s，从start位置开始截取length长度的字符串，作为子字符串，例如substr(‘abcdefgh’,3,5)的结果是’cdefg’；

  substr(binary s,string start_index,string length)–对于二进制字节值s，从start位置开始截取length长度的字符串，作为子字符串；

  substring(binary s,string start_index,string length)–对于二进制字节值s，从start位置开始截取length长度的字符串，作为子字符串；

  trim(string a)–将字符串a前后出现的空格全部去掉；

  unbase64(string str)–将基于64位的字符串str转换成二进制值；

  upper(string a)–将字符串中所有的字母转换成大写字母；

  ucase(string a)–将字符串中所有的字母转换成大写字母；

  from_unixtime(bigint unixtime[,string format])–将时间戳秒数转换成UTC时间，并用字符串表示，可以通过format规定的时间格式，指定输出的时间格式；

  unix_timestamp()–获取当前本地时区下的当前时间戳，例如：1534132825；

  unix_timestamp(string date)–输入的时间字符串格式必须是yyyy-MM-dd HH:mm:ss,如果不符合则返回0，如果符合则将此时间字符串转换成Unix时间戳，
  	例如:select unix_timestamp(‘2009-03-20 11:30:01’)=1237519801；

  unix_timestamp(string date,string pattern)–将指定时间字符串格式转换成Unix时间戳，如果格式不对，则返回0，例如unix_timestamp(‘2009-03-20’,‘yyyy-MM-dd’)=1237478400；

  to_date(string timestamp)–返回时间字符串的日期部分，例如：to_date(“1970-01-01 00:00:00”)=“1970-01-01”；

  year(string date)–返回时间字符串中的年份并使用int类型表示。例如：year(“1970-01-01 00:00:00”)=“1970”；

  month(string date)–返回时间字符串中的月份并使用int类型表示。例如：month(“1970-01-01 00:00:00”)=“1”；

  day(string date)–返回时间字符串中的天并使用int类型表示。例如：day(“1970-01-01 00:00:00”)=“1”；

  dayofmonth(string date)–返回时间字符串中的天并使用int类型表示。例如：day(“1970-01-01 00:00:00”)=“1”；

  hour(string date)–返回时间字符串中的小时并使用int类型表示。例如：hour(“1970-01-01 11:58:59”)=“11”；

  minute(string date)–返回时间字符串中的分钟数；

  second(string date)–返回时间字符串中的秒数；

  weekofyear(string date)–返回时间字符串位于一年中第几个周内，例如：weekofyear(“1970-11-01 11:58:59”)=“44”；

  datediff(string enddate,string startdate)–计算开始时间startdate到结束时间enddate相差的天数，例如 datediff(‘2009-03-01’,‘2009-02-27’)=2；

  date_add(string startdate,int days)–为开始时间startdate增加days天。例如：date_add(‘2018-12-31’,1)=‘2009-01-01’；

  date_sub(string startdate,int days)–从开始时间startdate减去days天。例如date_sub(‘2008-12-31’,1)=‘2018-12-30’；

  from_utc_timestamp(TIMESTAMP timestamp,STRING timezone)–如果给定的时间戳并非UTC,则将其转化成指定的时区下的时间戳；

  to_utc_timestamp(TIMESTAMP timestamp,STRING timezone)–如果给定的时间戳是指定的时区下的时间戳，则将其转化成UTC下的时间戳；

九、替换函数

正则替换函数 regexp_replace

语法: regexp_replace(string A, string B, string C)

操作类型: strings

返回值: string

说明：将第一个参数A中，B的部分，替换为C

select regexp_replace("[101,102,103]", "1", "8")
---
[808,802,803]

hive> select regexp_replace('h234ney', '\\d+', 'o');
OK ---
honey


select regexp_replace("[101,102,103]", "\\[|\\]", "") # ‘[’，‘]’为特殊字符，要\\转义
---
101,102,103

hive对时间字段的处理

对‘2023-02-01’字符串日期格式的比较

方式一：

hive中可以先把时间字符串的‘-’替换掉，然后再以字符串的格式进行时间的比较

 select * from dwfu_hive_db.I_USOC_PLAN_M where P_MON = '202303'
 and replace(substr(exp_date,1,7),'-','') = '202302'

方式二

把两个时间字符串都替换成时间戳再比较大小

unix_timestamp函数， current_timestamp()获取当前时间

 select unix_timestamp(current_timestamp())

方式三

 select *
 from dwfu_hive_db.I_USOC_PLAN_M where P_MON = '202303
 where date_format(exp_date,'yyyyMM') = '202303'

结尾

原来是大华啊~

关注

6
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫