Hive基本语句及函数用法

原文链接：https://blog.csdn.net/KEEP_GIONG/article/details/99822103

1. Hive的基本概念

Hive是什么？
Hive是基于Hadoop的数据仓库工具，相当于hadoop之上的一个客户端；
可对存储在 HDFS 上的文件中的数据集进行数据整理、特殊查询和分析处理；
是一种SQL解析引擎，能够将SQL转换成Map/Reduce中的Job在hadoop上执行。
- Hive的数据存储特点
数据存储是基于hadoop的HDFS；
没有专门的数据存储格式；
存储结构主要有：数据库、文件（默认可以直接加载文本文件）、表、视图、索引；
hive中的表实质就是HDFS的目录，按表名将文件夹分开。
- Hive总结
1）Hive 由 Facebook 实现并开源；
2）是基于 Hadoop 的一个数据仓库工具；
3）可以将结构化的数据映射为一张数据库表；
4）并提供 HQL(Hive SQL)查询功能；
5）底层数据是存储在 HDFS 上；
6）Hive的本质是将 SQL 语句转换为 MapReduce 任务运行；
7）使不熟悉 MapReduce 的用户很方便地利用 HQL 处理和计算 HDFS 上的结构化的数据，适用于离线的批量数据计算；
总结为一句话：hive是基于hadoop的数据仓库。

2. Hive基础数据语法

2.1 DDL操作

•建表
•删除表
•修改表结构
•创建／删除视图
•创建数据库
•显示命令

2.1.1建表

•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；
              用户可以用 IF NOT EXIST 选项来忽略这个异常
•EXTERNAL 关键字可以让用户创建一个外部表
•COMMENT可以为表与字段增加描述
•PARTITIONED BY 分区
•CLUSTERED BY 数据汇总
•SORTED BY 按某列排序
•BUCKETS 分桶 #设置词句分桶才有效: set hive.enforce.bucketing=true;
•ROW FORMAT DELIMITED 按分割格式读取文件
	[fields terminated by char]           #每个列字段通过什么分割
	[collection items terminated by char] #array/struct每个键值之间分割符
	[map keys terminated by char]         #每个键值对分隔符
	[lines terminated by char]            #每行之间通过什么分割
	#用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。
	#Hive 通过指定自定义的 SerDe 确定表的具体的列的数据。
•STORED AS 存储为不同文件格式
	[textfile]     #查看：hadoop fs -text 
	[sequencefile] #序列化文件
	[rcfile]       #查看：hive -service rcfilecat path
	[INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname] #自定义的输入\输出流
•LOCATION 在建表的同时指定一个指向实际数据的路径

数据编码格式

hive默认通过^A(\001)、^B(\002)、^C(\003)分别对列、(array和struct)、map进行匹配；
# 行格式分隔的字段终止于
row format delimited fields terminated by '\001'
# 集合项终止于
collection items terminated by '\002'
# 映射键终止于
map keys terminated by '\003'
# 行终止于
lines terminated by '\n'

2.1.2创建简单表

CREATE TABLE pokes (foo INT, bar STRING);

2.1.3创建外部表

CREATE EXTERNAL TABLE page_view (
viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT ‘IP Address of the User’,
country STRING COMMENT ‘country of origination’ )
COMMENT ‘This is the staging page view table’
ROW FORMAT DELIMITED fields terminated by ‘\054’
STORED AS TEXTFILE
LOCATION ‘<hdfs_location>’;

2.1.4创建分区表

CREATE TABLE par_table (
viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT ‘IP Address of the User’)
COMMENT ‘This is the page view table’
PARTITIONED BY(date STRING, pos STRING)
ROW FORMAT DELIMITED ‘\t’
fields terminated by ‘\n’
STORED AS sequencefile;

2.1.5创建分桶表

CREATE TABLE par_table(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT ‘IP Address of the User’)
COMMENT ‘This is the page view table’
PARTITIONED BY(date STRING, pos STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED ‘\t’
fields terminated by ‘\n’
STORED AS sequencefile;

2.1.6创建表并创建索引字段ds

CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

2.1.7 复制一个空表

LIKE 允许用户复制现有的表结构，但是不复制数据
CREATE TABLE empty_key_value_store LIKE key_value_store;#只是复制表结构
CREATE TABLE empty_key_value_store AS SELECT NAME1 FROM key_value_store;#复制表结构及数据

2.1.8 简单示例：

create table user_info(user_id int, cid string, ckid string, username string)
row format delimited
fields terminated by ‘\t’
lines terminated by ‘\n’;

2.1.9 查看所有数据表/分区/数据库：

SHOW table_name;
SHOW partitions ;
SHOW databases;
•按正条件（正则表达式）显示表
SHOW TABLES ‘.*s’;
•显示表的描述信息
DESC table_name；
DESC extented table_name; #查看扩展信息
DESC formatted table_name;#查看格式化信息
•显示建表的语句
SHOW CREATE TABLE tablename;

2.1.10 修改表结构

•增加分区、删除分区
•重命名表
•修改列的名字、类型、位置、注释
•增加/更新列
•增加表的元数据信息

2.1.11 添加/更新列

ALTER TABLE table_name ADD|REPLACE COLUMNS (new_col INT);
ALTER TABLE table_name ADD|REPLACE COLUMNS (new_col INT COMMENT ‘a comment’);
#增加列字段注释，REPLACE会表示替换表中所有字段。

2.1.12 修改列名/类型/注释/位置：

ALTER TABLE table_name CHANGE col_old_name col_new_name;

ALTER TABLE table_name CHANGE col_old_name col_new_name column_type;

ALTER TABLE table_name CHANGE col_old_name col_new_name column_type COMMENT col_comment;

ALTER TABLE table_name CHANGE col_old_name col_new_name column_type AFTER/FIRST column_name;

2.1.13 删除列:

ALTER TABLE table_name DROP col_name;

2.1.14 表重命名：

ALTER TABLE old_table_name RENAME TO new_table_name;

2.1.15 修改表属性/元数据信息:

ALTER TABLE table_name SET TBLPROPERTIES (‘EXTERNAL’=‘TRUE’); //内部表转外部表
ALTER TABLE table_name SET TBLPROPERTIES (‘EXTERNAL’=‘FALSE’); //外部表转内部表
ALTER TABLE table_name SET TBLPROPERTIES property_name = property_value……

2.1.16 修改文件格式与组织

ALTER TABLE table_name SET FILEFORMAT file_format;
ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS;

2.1.17 删除/清空数据表：

DROP TABLE table_name;
TRUNCATE TABLE table_name;

2.1.18 添加/删除/修改分区：

ALTER TABLE table_name ADD IF NOT EXISTS
PARTITION(col_name=‘value1’) location ‘loc1’;//一次添加一个分区

ALTER TABLE page_view ADD IF NOT EXISTS
PARTITION (dt=‘2008-08-08’, country=‘us’) LOCATION ‘/path/to/us/part080808’
PARTITION (dt=‘2008-08-09’, country=‘us’) LOCATION ‘/path/to/us/part080809’; //一次添加多个分区

ALTER TABLE tablename DROP IF EXISTS PARTITION(dt=‘2019-08-08’);

ALTER TABLE table_name PARTITION(dt=‘2019-08-08’) SET LOCATION “new loction”;

ALTER TABLE table_name PARTITION(dt=‘2019-08-08’) RENAME TO PARTITION(‘20190808’);

2.1.19 创建/删除视图

CREATE VIEW [IF NOT EXISTS] view_name
[ (column_name [COMMENT column_comment], …) ]
[COMMENT view_comment]
[TBLPROPERTIES (property_name = property_value, …)] AS SELECT
•增加视图
•如果没有提供表名，视图列的名字将由定义的SELECT表达式自动生成
•如果修改基本表的属性，视图中不会体现，无效查询将会失败
•视图是只读的，不能用LOAD/INSERT/ALTER
•DROP VIEW view_name #删除视图

2.1.20 创建数据库

CREATE DATABASE dataname

2.2 DML操作:元数据存储

•DML包括：INSERT插入、UPDATE更新、DELETE删除
•注：Hive没有行级别的插入、更新和删除操作，往表中插入数据的唯一方法就是使用成批载入操作
        ( hive不支持用insert语句一条一条的进行插入操作，也不支持update操作。
          数据是以load的方式加载到建立好的表中。数据一旦导入就不可以修改。)

2.2.1 向数据表内加载文件

以load的方式加载数据至数据表:
LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE table_name;

LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE table_name
[PARTITION (partcol1=val1, partcol2=val2 …)];

释义：

•Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。
•LOCAL 指定了本地
•filepath
 	 相对路径，例如：project/data1
	 绝对路径，例如：/user/hive/project/data1
 	 包含模式的完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1
例如：hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;
•overwrite重复会覆盖原数据，没有overwrite只会将数据copy到表中
例如：加载本地数据，同时给定分区信息：
hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites 
      PARTITION (ds='2008-08-15');

2.2.2 将查询结果覆盖重写Hive表

基本模式

INSERT OVERWRITE TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]
select_statement1 FROM from_statement

多插入模式

FROM from_statement
INSERT OVERWRITE TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]
select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION …] select_statement2]…

自动分区模式

INSERT OVERWRITE TABLE tablename
PARTITION (partcol1[=val1], partcol2[=val2] …) select_statement FROM from_statement

2.2.3 将查询结果写入HDFS文件系统

基本模式

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT … FROM …

多插入模式

FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]
数据写入文件系统时进行文本序列化，且每列用^A 来区分，\n换行

2.2.4 INSERT INTO

INSERT INTO TABLE tablename1
[PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1 FROM from_statement

2.3 DQL操作:数据查询SQL

2.3.1 基本的Select 操作

SELECT [ALL | DISTINCT] select_expr, select_expr, …
FROM table_reference
WHERE where_condition
GROUP BY col_list HAVING condition
CLUSTER BY col_list | DISTRIBUTE BY col_list
SORT BY | ORDER BY col_list
LIMIT number
释义：
•使用ALL 和 DISTINCT 选项区分对重复记录的处理
•Where 条件类似传统SQL的where条件；
	目前支持 AND ,OR ,between ,IN ,NOT IN；不支持 EXIST ,NOT EXIST
•ORDER BY与SORT BY的不同
	ORDER BY 全局排序，只有一个Reduce任务
	SORT BY 只在本机做排序
•Limit 可以限制查询的记录数；SELECT * FROM t1 LIMIT 5
•实现Topk 查询
	SET mapred.reduce.tasks = 1 
	SELECT * FROM test SORT BY amount DESC LIMIT 5
•REGEX Column Specification
	SELECT 语句可以使用正则表达式做列选择，下面的语句查询除了 ds 和 hr 之外的所有列：
	SELECT `(ds|hr)?+.+` FROM test

示例：

INSERT OVERWRITE [LOCAL] DIRECTORY ‘/tmp/hdfs_out’ SELECT a.* FROM invites a WHERE a.ds=’’;
INSERT OVERWRITE [LOCAL] DIRECTORY ‘/tmp/reg_4’ SELECT a.invites, a.pokes FROM profiles a;
INSERT OVERWRITE [LOCAL] DIRECTORY ‘/tmp/reg_5’ SELECT COUNT(1),SUM(1) FROM invites a WHERE a.ds=’’;

将一个表的统计结果插入另一个表中：

INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key < 100;
FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(1) WHERE a.foo > 0 GROUP BY a.bar;
INSERT OVERWRITE TABLE events SELECT a.bar, count(1) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;
INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100;
INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200;

将多表数据的统计结果插入到同一表中：

FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE events SELECT t1.bar, t1.foo, t2.foo;

将一个表的统计结果插入多表中/[本地]目录：

FROM src
INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
INSERT OVERWRITE TABLE dest3 PARTITION(ds=‘2008-04-08’, hr=‘12’) SELECT src.key WHERE src.key >= 200 and src.key < 300
INSERT OVERWRITE LOCAL DIRECTORY ‘/tmp/dest4.out’ SELECT src.value WHERE src.key >= 300;

将文件流直接插入文件：

FROM invites a
INSERT OVERWRITE TABLE events
SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING ‘/bin/cat’ WHERE a.ds > ‘2008-08-09’;

基于Partition的查询:
一般 SELECT 查询会扫描整个表，使用 PARTITIONED BY 子句建表，
查询就可以利用分区剪枝（input pruning）的特性
Hive 当前的实现是只有分区断言出现在离 FROM 子句最近的那个WHERE 子句中，才会启用分区剪枝

2.3.2 JION

join_table:
table_reference JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
| table_reference LEFT SEMI JOIN table_reference join_condition
释义：
•Hive 只支持等值连接（equality joins）、外连接（outer joins）和（left semi joins）。
 Hive 不支持所有非等值的连接，因为非等值连接非常难转化到 map/reduce 任务
•LEFT，RIGHT和FULL OUTER关键字用于处理join中空记录的情况
•LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现
•join 时，每次 map/reduce 任务的逻辑是这样的：
 reducer会缓存join序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统
•实践中，应该把最大的那个表写在最后

join 查询时，需要注意几个关键点:

只支持等值join

SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)
可以 join 多于2个表，例如:
 SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
如果join中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务

LEFT，RIGHT和FULL OUTER

•例子: SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
•如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件——或是在 join 子句中写
•容易混淆的问题是表分区的情况:
 SELECT c.val, d.val 
 FROM c LEFT OUTER JOIN d ON (c.key=d.key) 
 WHERE d.ds='2010-07-07' AND c.ds='2010-07-07'

•如果 d 表中找不到对应 c 表的记录，d 表的所有列都会列出 NULL，包括 ds 列。
 也就是说，join 会过滤 d 表中不能找到匹配 c 表 join key 的所有记录。
 这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关

•解决办法
 SELECT c.val, d.val 
 FROM c LEFT OUTER JOIN d 
 ON (c.key=d.key AND d.ds='2009-07-07' AND c.ds='2009-07-07')

LEFT SEMI JOIN

•LEFT SEMI JOIN 的限制是， 
 JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行
 SELECT a.key, a.value 
 FROM a 
 WHERE a.key in (SELECT b.key FROM B);
可以被重写为：
 SELECT a.key, a.val 
 FROM a LEFT SEMI JOIN b on (a.key = b.key)

UNION ALL

•用来合并多个select的查询结果，需要保证select中字段须一致
  select_statement UNION ALL select_statement UNION ALL select_statement ...

2.4 从SQL到HiveQL应转变的习惯

•inner join等价于join: 有on取交集，无on取笛卡尔积，不存在 inner outer join
  left  [outer] join：以左表中所有记录，匹配右表纪录
  right [outer] join: 以右表中所有记录，匹配左表纪录
  full  [outer] join: left join和right join的并集

•left semi join: left semi join 的限制是JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，
  在 WHERE 子句、SELECT 子句或其他地方过滤都不行。
  left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。
  右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高

3. Hive基本函数汇总

3.1 常用字段类型

3.1.1 基本字段类型：primitive_data

•数值类型： tinyint、smallint、int、bigint、float、double、decimal
•字符串：string
•布尔类型：boolean (true、false  #生产用1/0代替)
•时间类型：date、TIMESTAMP #生产用字符串代替，如：19010101010101
•字节数组：binary
•注：Oracle 整数类型，可以插入小数，存入时会四舍五入。
     Hive   整数类型，也可以插入小数，存入时不会四舍五入。

基本字段类型说明实例
tinyint 1byte有符号的整数 20
smallint 2byte有符号的整数 20
int 4byte有符号的整数 20
bigint 8byte有符号的整数 20
float 单精度 3.127
double 双精度 3.212
boolean 布尔类型true或false true/false
string 字符串，单双即可 ‘zhang’；“ashakjds”
timestamp 时间戳，精确的纳秒 ‘2015-07-26 11:35:42.0’
binary 字节数组
decimal 数字 123

3.1.2 集合数据类型

•数组类型：array， 存放相同类型的数据集合，     
   如：array[int] 下标访问
•映射集合类型：map， 存放相同类型的k-v键值对集合，
   如：members["father"],map_keys(members),map_values(members)
•结构体类型： struct，可存放不同类型的数据的集合， 
   如：struct{name:STRING,age:INT}.访问

集合数据类型说明用法实例
array 数组类型 work_locations array [“shanghai”, “hangzhou”, “beji”]
map 一组键值对的元组 members map<string, string> {“father”: “xiaoming”, “mother”: “xiaohuang”, “brother”: “xiaoxu”}
struct 对象类型，可以通过字段名.元素名来访问 userinfo struct<name: string, age: int> 192.168.1.1 {“name”: “zhangsan”, “age”: 40}

3.2语法关键字

(1)查看所有数据库/分区/数据表：show databases /PARTITIONS /tables;
(2)创建数据库：create table table_name(字段名 字段类型,……);
(3)修改数据库：alter table table_name rename to new_table_name 
                                     change(字段名 字段类型) 
                                     add columns(字段名 字段类型) 
                                     replace columns(a int,b int);
(4)查看/删除/清空数据库：desc/drop/truncate table table_name ;
(5)load data(local) inpath;
(6)select * from tables;
(7)limit,as,case when then end,union;
(8)group by,having,like;
(9)order by,sort by,cluster by;

3.3 hive常用函数

•简单内置函数: 关系运算符,算术运算符,逻辑运算符,数学函数,
              日期函数,字符函数,类型转换函数,条件函数,聚合函数
•高级内置函数: 由聚合函数等组成的具有一定分析性的函数
•自定义函数:

3.3.1 关系运算符

等值比较:     =
不等值比较:   <>
小于比较:     <
小于等于比较: <=
大于比较:     >
大于等于比较: >=
空值判断:     IS NULL
非空判断:     IS NOT NULL
空值判断：    ISNULL
LIKE比较:     LIKE;  操作类型: strings; ”_”表示任意单个字符; 否定比较时候用NOT A LIKE B</strong>
					eg: hive> select 1 from iteblog where 'football' like 'foot____';
						hive> select 1 from iteblog where NOT 'football' like 'fff%';
JAVA的LIKE操作: RLIKE;  操作类型: strings; 如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；否则为FALSE.
					eg: hive> select 1 from iteblog where 'footbar’ rlike '^f.*r$’;
						hive> select 1 from iteblog where '123456'  rlike '^\\d+$';
REGEXP操作:     REGEXP;   操作类型: strings; 功能与RLIKE相同;
					eg: hive> select 1 from iteblog where 'footbar' regexp '^f.*r$';

3.3.2 算术运算符

加法操作:   +  操作类型：所有数值类型
减法操作:   -
乘法操作:   *
除法操作:   /
取余操作:   %
位与操作:   &
位或操作:   |
位异或操作: ^
位取反操作: ~

3.3.3 逻辑运算符

逻辑与操作: AND;  操作类型：boolean
逻辑或操作: OR;   操作类型：boolean
逻辑非操作: NOT;  操作类型：boolean

3.3.4 数学函数

取整函数:         round;   round(double a);        返回值: BIGINT
指定精度取整函数:  round;   round(double a, int d); 返回值: DOUBLE
向下取整函数:     floor;   返回值: BIGINT
向上取整函数:     ceil;    返回值: BIGINT
向上取整函数: 	 ceiling; 与ceil功能相同;   返回值: BIGINT   
                 银行家舍入法（1~4：舍，6~9：进，5->前位数是偶：舍，5->前位数是奇：进）；
                 bround(2.5) = 2, bround(3.5) = 4.
取随机数函数:     rand;    rand(),rand(int seed);  返回值: double
自然指数函数:     exp;     返回值: double
以10为底对数函数: log10;   返回值: double
以2为底对数函数:  log2
对数函数:         log;     log(double base, double a)
幂运算函数:       pow;     pow(double a, double p)
幂运算函数:       power;   与pow功能相同
立方根函数:       cbrt(2)
开平方函数:       sqrt
数学常数e         e()
数学常数pi        pi()
阶乘函数:         factorial(2)
二进制函数:       bin;   返回值: string  
十六进制函数:     hex;   返回值: string
反转十六进制函数: unhex
进制转换函数:     conv
绝对值函数:       abs;  返回值: double int
正取余函数:       pmod; pmod(int a, int b),pmod(double a, double b); 返回值: int double
正弦函数:         sin
反正弦函数:       asin
余弦函数:         cos
反余弦函数:       acos
正切值函数:       tan
反正切值函数:     atan
弧度值转换角度值: degrees(30)
角度值转换弧度值: radians(30)
符号函数：        sign(2); 如果a是正数则返回1.0，是负数则返回-1.
positive函数:     positive; positive(int a), positive(double a); 返回值: int double,返回a
negative函数:     negative; negative(int a), negative(double a); 返回值: int double,返回-a

3.3.5 日期函数

UNIX时间戳转日期函数:       from_unixtime; 返回值:string;  from_unixtime(1323308943,'yyyyMMdd')
获取当前UNIX时间戳函数: 	   unix_timestamp;返回值: bigint; unix_timestamp()
日期转UNIX时间戳函数:        unix_timestamp;返回值: bigint; unix_timestamp('2011-12-07 13:01:03')
指定格式日期转UNIX时间戳函数: unix_timestamp;返回值: bigint; unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss')
日期时间转日期函数:         to_date;    返回值: string; to_date('2011-12-08 10:03:01')
日期转年函数:               year;       返回值: int;    year('2011-12-08 10:03:01')
日期转月函数:               month    
日期转天函数:               day      
日期转小时函数:             hour     
日期转分钟函数:             minute   
日期转秒函数:               second   
日期转周函数:               weekofyear; 返回值: int;    weekofyear('2019-01-01 10:03:01');   返回日期在当前的周数。
日期转天函数:               dayofmonth; 返回值: int;    weekofyear('2019-01-01 10:03:01');   返回日期在当前月的天数
日期比较函数:               datediff;   返回值: int;    datediff('2012-12-08','2012-05-09'); 返回结束日期减去开始日期的天数。
日期增加函数:               date_add;   返回值: string; date_add('2012-12-08',10);           返回开始日期startdate增加days天后的日期。
日期减少函数:               date_sub
最后一天的日期：            last_day
返回当前时间的下一个星期X所对应的日期: next_day('2015-01-14', 'TU') = 2015-01-20  以2015-01-14为开始时间，其下一个星期二所对应的日期为2015-01-20
返回当前时间属性哪个季度： quarter('2015-04-08') = 2
返回当前时间日期:         current_date
返回当前时间戳:           current_timestamp
添加月数：                add_months('2017-02-10', 2);
             current_date: 返回当前日期,年月日
        current_timestamp: 返回当前日期,年月日时分秒
               year(date)：返回日期date的年,类型为int,如year('2019-01-01')=2019
              month(date)：返回日期date的月,类型为int,如month('2019-01-01')=1
                day(date): 返回日期date的天,类型为int,如day('2019-01-01')=1
        weekofyear(date1)：返回日期date1位于该年第几周,   如weekofyear('2019-03-06')=10
    datediff(date1,date2): 返回日期date1与date2相差的天数,如datediff('2019-03-06','2019-03-05')=1
     date_add(date1,int1): 返回日期date1加上int1的日期,   如date_add('2019-03-06',1)='2019-03-07'
     date_sub(date1,int1): 返回日期date1减去int1的日期,   如date_sub('2019-03-06',1)='2019-03-05' months_between(date1,date2): 返回date1与date2相差月份,如months_between('2019-03-06','2019-01-01')=2
   add_months(date1,int1): 返回date1加上int1个月的日期,int1可为负数,如add_months('2019-02-11',-1)='2019-01-11'
          last_day(date1): 返回date1所在月份最后一天,如last_day('2019-02-01')='2019-02-28'
     next_day(date1,day1): 返回日期date1的下个星期day1的日期,day1为星期X的英文前两字母,如next_day('2019-03-06','MO') 返回'2019-03-11'
     trunc(date1,string1): 返回日期最开始年份或月份。string1可为年(YYYY/YY/YEAR)或月(MONTH/MON/MM),如trunc('2019-03-06','MM')='2019-03-01'，trunc('2019-03-06','YYYY')='2019-01-01'
         unix_timestamp(): 返回当前时间的unix时间戳，可指定日期格式。如unix_timestamp('2019-03-06','yyyy-mm-dd')=1546704180
          from_unixtime(): 返回unix时间戳的日期，可指定格式。如select from_unixtime(unix_timestamp('2019-03-06','yyyy-mm-dd'),'yyyymmdd')='20190306'
to_date(string timestamp): 返回时间字符串中的日期部分,如to_date('1970-01-01 00:00:00')='1970-01-01'

3.3.6 条件函数

If函数:      if; if(1=2,100,200)
非空查找函数: COALESCE;  COALESCE(null,'100','50')
非空查找函数: NVL;       NVL(expr1,expr2)
非空查找函数: NVL2;      NVL2(expr1,expr2, expr3); 第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。
非空查找函数: NULLIF;    NULLIF(exp1,expr2);      如果exp1和exp2相等则返回空(NULL)，否则返回第一个值
条件判断函数：CASE val when num1 then 'str' end; case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end
条件判断函数：CASE when val=num1 then 'str' end; case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end

3.3.7 字符串函数

字符串长度函数：        length
字符串反转函数：        reverse
字符串连接函数：        concat;     concat(‘abc’,'def’,'gh’)->abcdefgh
带分隔符字符串连接函数： concat_ws;  concat_ws(',','abc','def','gh')->abc,def,gh
字符串截取函数：        substr,substring; substr('abcde',3); substring('abcde',3)
字符串截取函数：        substr,substring  substr('abcde',3,2);substring('abcde',3,2)
字符串转大写函数：      upper,ucase
字符串转小写函数：      lower,lcase
去空格函数：            trim; 去除字符串两边的空格; trim(' abc ')
左边去空格函数：        ltrim
右边去空格函数：        rtrim
正则表达式替换函数：    regexp_replace; regexp_replace(string A, string B, string C); 将字符串A中的符合java正则表达式B的部分替换为C。
							eg: regexp_replace('foobar', 'oo|ar', '')->fb
正则表达式解析函数：    regexp_extract; regexp_extract(string subject,string pattern,int index);将字符串subject按照pattern正则规则拆分返回index指定字符。
							eg: regexp_extract('foothebar', 'foo(.*?)(bar)', 0)->foothebar
								regexp_extract('foothebar', 'foo(.*?)(bar)', 1)->bar
								regexp_extract(data_field,'.*?bgStart\\=([^&]+)',1) 
URL解析函数：           parse_url;  parse_url(string urlString, string partToExtract [, string keyToExtract]);
							返回：URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO
							eg: parse_url('https://www.iteblog.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST')->facebook.com
								parse_url('https://www.iteblog.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1')->v1	
json解析函数：          get_json_object;get_json_object(string json_string, string path); 解析json的字符串json_string,返回path指定的内容
空格字符串函数：        space;  length(space(10)) -> 10
重复字符串函数：        repeat; repeat('abc',5)->abcabcabcabcabc
首字符ascii函数：       ascii;  ascii('abcde')->97
左补足函数：            lpad;   lpad('abc',10,'td')->tdtdtdtabc
右补足函数：            rpad
分割字符串函数:         split;       split('abtcdtef','t')->["ab","cd","ef"]; 返回值: array
集合查找函数:           find_in_set; find_in_set(string str, string strList);  返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。
							eg: find_in_set('ab','ef,ab,de')-> 2
								find_in_set('at','ef,ab,de')-> 0

3.3.8 聚合统计函数

个数统计函数:         count
总和统计函数:         sum
平均值统计函数:       avg
最小值统计函数:       min
最大值统计函数:       max
求最大值函数:         greatest
求最小值函数:         least
非空集合总体变量函数: var_pop; var_pop(col);   统计结果集中col非空集合的总体变量（忽略null）
非空集合样本变量函数: var_samp;var_samp (col); 统计结果集中col非空集合的样本变量（忽略null）
总体标准偏离函数: stddev_pop
样本标准偏离函数: stddev_samp
中位数函数:       percentile
中位数函数:       percentile
近似中位数函数:   percentile_approx
近似中位数函数:   percentile_approx
直方图:          histogram_numeric

3.3.9 复合类型构建操作

Map类型构建:    map;    map (key1, value1, key2, value2, …); 根据输入的key和value对构建map类型;  eg: map('100','tom','200','mary')
Struct类型构建: struct; struct(val1, val2, val3, …);         根据输入的参数构建结构体struct类型; eg: struct('tom','mary','tim')
array类型构建:  array;  array(val1, val2, …);                根据输入的参数构建数组array类型;    eg: array("tom","mary","tim")

3.3.10 复杂类型访问操作

array类型访问:  A[n]
map类型访问:    M["key_name"];map_keys(M);map_values(M)
struct类型访问: S.x

3.3.11 复杂类型长度统计函数

Map类型长度函数:   size(Map<k .V>); eg: size(map('100','tom','101','mary'))->2
array类型长度函数: size(Array<T>)
类型转换函数:      cast; cast(expr as <type>); cast(1 as bigint)->1

3.3.12 其他非常有用的函数

• explode函数: (一行转多行)将字段内复杂的数据拆分成多行, 常用来做行拆多行
				eg: explode(array): 返回多行array中对应的元素。如explode(array('A','B','C')),返回:A\B\C三行
					explode(map): 返回多行map键值对对应元素。如explode(map(1,'A',2,'B',3,'C')),返回:

• lateral view函数：(一行转多行)当表中某个字段的取值为列表或数组时，利用该函数和split、explode可以将一行拆分成多行
				eg: select deal_id ,type ,sp 
					from deal_ppt_mark_log t 
					lateral view explode(split(t.sources,','))a as sp
				行转多列可以认为多列是一个字段处理，多个转换之间lateral view不能有 "，" 分割开
				SELECT MR_Longitude,MR_Latitude,b,MAX(a) 
				FROM
				(
					select a,b,
						MR_Longitude * 360 *1.0/ 16777216 MR_Longitude,
						MR_Latitude * 90 / 8388608 MR_Latitude
					FROM etl_4g_mro_zte 
					lateral view explode(split(concat_ws(',',MR_LteScRSRP,MR_LteNcRSRP1,MR_LteNcRSRP2,),',')) r1 AS a             
					-----没有','
					lateral view explode(split(concat_ws(',',MR_LteScEarfcn,MR_LteNcEarfcn1,MR_LteNcEarfcn2),',')) f1 AS b
				)
				WHERE MR_Longitude IS NOT NULL 
				AND MR_Longitude <>''
				AND DAY=20170323;
			
• collect_set()/Collect_list()函数：(多行转一行),同组不同行合并成一行，collect_set/collect_set 去重/不去重，
                                 常常搭配concat_ws()函数使用
				eg: concat_ws(',',collect_list(cast(qty as string)))	

• str_to_map函数：str_to_map(字符串参数, 分隔符1, 分隔符2),分隔符1将文本分成K-V对，分隔符2分割每个K-V对。
				eg: str_to_map(concat_ws(',',collect_set(concat(stat_date,':',label_state))),',',':') 

• distribute by和sort by函数一起使用：ditribute by是控制map的输出在reducer是如何划分的
				eg: select mid, money, name from store distribute by mid sort by mid asc, money asc;
				    所有的mid相同的数据会被送到同一个reducer去处理，这就是因为指定了distribute by mid
					distribute by必须要写在sort by之前

• cluster by函数： cluster by的功能就是distribute by和sort by相结合
				eg: select mid, money, name from store cluster by mid sort by money;
				    注意被cluster by指定的列只能是降序，不能指定asc和desc。
• grouping sets()/cube()/rollup()高级聚合函数:
 • 窗口函数与分析函数: 
	•用于分区排序 
	•动态Group By 
	•Top N 
	•累计计算 
	•层次查询

•排序函数：
row_number() over(partition by .. order by ..): 取partition分组order by排序后，相同值取不同序号，不存在序号跳跃
	  rank() over(partition by .. order by ..): 取partition分组order by排序后，相同值取相同序号，存在序号跳跃，不支持窗口rows从句
dense_rank() over(partition by .. order by ..): 取partition分组order by排序后，相同值取相同序号，不存在序号跳跃，不支持窗口rows从句

•聚合函数：
sum()/count()/max()/max()/min() over(partition by .. order by ..)：取partition分组order by排序后，聚合函数计算

•窗口函数：
lag(col,n,default)    over(partition by .. order by ..)：取partition分组后，查看当前行的上第n行,不指定default为null,指定为指定字段，不支持rows从句
lead(col,n,default)   over(partition by .. order by ..)：取partition分组后，查看当前行的下第n行,不指定default为null,指定为指定字段，不支持rows从句

first_value() over(partition by .. order by ..): 取partition分组order by排序后，截止到当前行|所有行，第一个值 
last_value()  over(partition by .. order by ..): 取partition分组order by排序后，截止到当前行|所有行，最后一个值  

•其他函数
cume_dist()  计算结果相对位置/总行数,不支持窗口rows从句
percent_rank() (相对位置-1)/(总行数-1),不支持窗口rows从句
percentile_disc()
percentile_count()
ratio_to_report() 计算当前行在分组中所有行数值总和所占的比例
ntile(n)      over(partition by .. order by ..): 取partition分组order by排序后，照顺序切分成n片，返回当前切片值，不支持窗口rows从句

•over从句：使用partition by与order by语句，使用一个或者多个数据类型的分区或者排序列 
注:partition内更细的划分，可使用windows子句。常见子句为：
			preceding：  往前
			following：  往后
			current row：当前行
			unbounded：  无界限（起点或终点）
			unbounded preceding：表示从前面的起点
			unbounded following：表示到后面的终点
	eg: sum(col) over(partition by .. order by .. rows between 1 preceding and current row):当前行与前一行做聚合

•窗口规范支持以下格式：窗口必须和order by 一起出现
rows between [unbounded | num] preceding and [unbounded | num] following  --当前行+往前所有行|num行+往后所有行|num行
rows between [unbounded | num] preceding and current row                  --当前行+往前所有行|num行
rows between current row                 and [unbounded | num] following  --当前行+往后所有行|num行
rows between [unbounded | num] preceding and [num] preceding
rows between [num] following and [unbounded | num] following

• order by 后面缺少窗口rows从句条件，
   窗口规范默认是 rows between unbounded preceding and current row
• order by 和窗口rows从句都缺失,    
   窗口规范默认是 rows between unbounded preceding and unbounded following
• over 从句支持排序函数、lead、lag、ntile(n) 函数，但是并不支持和窗口一起使用它们

•注：Hive VS Oracle
(1)Hive时分秒的加减:     from_unixtime(unix_timestamp()+ 3600)
(2)Hive必须大写MM或者YY: trunc(current_date,'MM')
(3)取周一：              next_day(date_sub(current_date,7),'mon')
(4)升序:                 order by case when area_type is null then 1 else 0 end,area_type; #因为Hive 升序 null 默认在最前 
(5)字段联合: oracle: a||b is not null;                        Select a||b from ....
             Hive  ：cast(nvl(a,’’)||nvl(b,’’) as string) != ‘’ ; Select a||b from ....
         #Oracle某个字段为Null不影响，但Hive则全部为null
(6)hive对多个字段去重统计时需要关闭skewindata:
   set hive.groupby.skewindata=false;
   select count(distinct id),count(distinct x) from test;

posted @ 2022-03-17 11:33 牛计费的小迷弟阅读(213) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· Netty--第一章(NIO基础)

· ElasticSearch

· Hive 总结

· Hive SQL基本使用

· hive学习

公告

昵称：牛计费的小迷弟
园龄： 5年6个月
粉丝： 0
关注： 2

+加关注

2025年3月

日

一

二

三

四

五

六

牛计费的小迷弟