hive

hive官网：
https://hive.apache.org/

hive操作指南：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual

易百教程：
https://www.yiibai.com/hive/

hive中文手册：
https://www.docs4dev.com/docs/zh/apache-hive/3.1.1/reference/LanguageManual_Commands.html

一、字符串拼接函数CONCAT()

语法: CONCAT(string A, string B…)

返回值: string

说明：返回输入字符串连接后的结果，支持任意个输入字符串

举例：

Hive> select concat(‘abc’,'def’,'gh’) from lxw_dual;

abcdefgh

延伸：指定分割字符的拼接函数CONCAT_WS()

语法 : CONCAT_WS(separator, str1, str2,...)

说明：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间

举例：

Hive> SELECT CONCAT_WS(",","First name","Second name","Last Name");

'First name,Second name,Last Name'

二、时间函数 - (hive支持今天：current_date)
1.unix_timestamp()
返回当前时区的unix时间戳
返回类型：bigint
hive (tmp)> select unix_timestamp() from hive_sum limit 1;
1465875016

2.from_unixtime(bigint unixtime[,string format])
时间戳转日期函数
返回类型：string
hive (tmp)> select from_unixtime(unix_timestamp(),’yyyyMMdd’) from hive_sum limit 1;
20160614

3.unix_timestamp(string date)
返回指定日期格式的的时间戳
返回类型：bigint
注意：如果后面只有date参数，date的形式必须为’yyyy-MM-dd HH:mm:ss’的形式。
hive (tmp)> select unix_timestamp(‘2016-06-01’) from hive_sum limit 1;
NULL
hive (tmp)> select unix_timestamp(‘2016-06-01 00:00:00’) from hive_sum limit 1;
1464710400

4.unix_timestamp(string date,string pattern)
返回指定日期格式的时间戳
返回类型:bigint
hive (tmp)> select unix_timestamp(‘2016-06-01’,’yyyyMMdd’) from hive_sum limit 1;
1449331200

5.to_date(string date)
返回时间字段中的日期部分
返回类型:string
hive (tmp)> select to_date(‘2016-06-01 00:00:00’) from hive_sum limit 1;
2016-06-01

6.year(string date)
返回时间字段中的年
返回类型:int
hive (tmp)> select year(‘2016-06-01 00:00:00’) from hive_sum limit 1;
2016

7.month(string date)
返回时间字段中的月
返回类型:int
hive (tmp)> select month(‘2016-06-01’) from hive_sum limit 1;
6

8.day(string date)
返回时间字段中的天
返回类型:int
hive (tmp)> select day(‘2016-06-01’) from hive_sum limit 1;
1

9.weekofyear(string date)
返回时间字段是本年的第多少周
返回类型:int
hive (tmp)> select weekofyear(‘2016-06-01’) from hive_sum limit 1;
22

10.datediff(string enddate,string begindate)
返回enddate与begindate之间的时间差的天数
返回类型:int
hive (tmp)> select datediff(‘2016-06-01’,’2016-05-01’) from hive_sum limit 1;
31

11.date_add(string date,int days)
返回date增加days天后的日期
返回类型:string
hive (tmp)> select date_add(‘2016-06-01’,15) from hive_sum limit 1;
2016-06-16

12.date_sub(string date,int days)
返回date减少days天后的日期
返回类型:string
hive (tmp)> select date_sub(‘2016-06-01’,15) from hive_sum limit 1;
2016-05-17

三、case when then
when then
else
==
if .. do
elif .. do
else ..

The SQL CASE Statement
The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

CASE Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

case when 可用作group by分组筛选

eg:
SELECT 'Number of Titles', Count(*)
FROM titles
GROUP BY
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress relatives'
END
GO

四、substr() >= 1, substr(bigint型，zz，xx) ——int型可以直接substr，substr的结果（string型）如果是数字型字符串也可以直接进行大小比较
string类型数字可以直接使用 "<" " >" " ="号比较, 不用转换成int类型

五、hive没办法只插入指定字段
hive insert into values,update ..
https://docs.cloudera.com/HDPDocuments/HDP2/HDP-2.3.4/bk_dataintegration/content/new-feature-insert-values-update-delete.html

insert..values,update,delete需要的配置：
hive.enforce.bucketing=true
hive.exec.dynamic.partition.mode=nonstrict

insert into 必须给全字段，但可以像sql一样给null
如：
INSERT INTO TABLE pageviews PARTITION (datestamp = '2014-09-23') VALUES ('jsmith', 'mail.com', 'sports.com'), ('jdoe', 'mail.com', null);

六、空值处理
1.NVL函数
NVL函数的格式如下：NVL(expr1,expr2)
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

2 NVL2函数
NVL2函数的格式如下：NVL2(expr1,expr2, expr3)
含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

3. NULLIF函数
   NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

4.Coalesce函数
Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。
格式如下：
Coalesce(expr1, expr2, expr3….. exprn)
表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。
返回表达式中第一个非空表达式，如有以下语句： 　　SELECT COALESCE(NULL,NULL,3,4,5) FROM dual 　　其返回结果为：3
如果所有自变量均为 NULL，则 COALESCE 返回 NULL 值。 　　
COALESCE(expression1,...n) 与此 CASE 函数等价：
这个函数实际上是NVL的循环使用，在此就不举例子了。

七、join但没有reduce的情况

日志.png
代码.png

（可能）原因：① set hive.optimize.skewjoin=true; 大表join小表，map端直接join了，不用reduce -- on的过程处理
② join的表属于同一分区，join后的结果，也写入了相同的分区字段；所以不存在reduce？-- join后写数据的过程
③ 也和这项设置有关系：set hive.auto.convert.join=true;

convert.join -- map join.png optimize.skewjoin - map join.png

参考链接：https://www.jianshu.com/p/378c0cbe554e
https://www.cnblogs.com/jnba/p/10673747.html

八、Hive下查看数据表信息的方法
方法1：查看表的字段信息
desc table_name;

方法2：查看表的字段信息及元数据存储路径
desc extended table_name;

方法3：查看表的字段信息及元数据存储路径
desc formatted table_name;

方法4：查看建表语句及其他详细信息的方法
show create table table_name;

九、查询效率 -建临时表+简单的select（不走MapReduce） > 各种嵌套子查询
示例：
· 在hive表前1000行里，过滤出不重复的refid,imsi。
错误的写法：
select distinct refid,imsi from HIVE_D_MT_UU_H_SPARK limit 1000;
会去读取全表，把0~1000行的不重复refid,imsi显示出来。

正确的写法：
select distinct refid,imsi from (select * from HIVE_D_MT_UU_H_SPARK limit 1000);

调优的写法：
CREATE TABLE TEMP_HIVE_D_MT_UU_H_SPARK AS
select * from HIVE_D_MT_UU_H_SPARK limit 1000;
select distinct refid,imsi from TEMP_HIVE_D_MT_UU_H_SPARK;

hive最快的执行就是不走MapReduce。简单的select的是最快的，嵌套啥的都比较忙。与关系型数据库不同。
调优的写法执行更快。

十、查询时几乎取全部，排除少数列（或者排除包含某字符子串的列）
排除excol列：
select `(excol)?+.+` from XXX ……

排除少量列的列名.png