hive-sql入门-实践摘记-持续更新

Hive SQL记录

DQL：Data QueryLanguage - 只读权限 select

DML：Data manipulation language - 数据操作语句 insert/ delete/update

DDL：Data Definition language - 数据定义语句 create/alter/drop etc

DCL：Data Control Language - 数据库控制语句 grant,deny,revoke

---

to_date():

to_date(expr) - Extracts the date part of the date or datetime expression expr

将date类型从date后者timestamp的表达式中抽取出来；

比如 to_date(‘2019-03-31 00:00:00’)

to_char():

to_number():

nvl(num1,str2)/coalesce(expr1,expr2...):

区别1：NVL只适合两个参数，当第一个参数为空时返回第二个参数，否则返回第一个参数；Coalesce可包含多个参数，进行多层从左到右判断获取；

区别2：nvl中的参数类型可以不一致，而coalesce必须保持一致；

ROW_NUMBER(): 该函数将针对SELECT语句返回的每一行，从1开始编号，赋予其连续的编号。在查询时应用了一个排序标准后，只有通过编号才能够保证其顺序是一致的，当使用ROW_NUMBER函数时，也需要专门一列用于预先排序以便于进行编号。

hive最好的去重方式：row_number() over(partition by id order by date desc) as rank_num … where rank_num=1

lead over/lag over

lead()over(): lead(列名,n,m): 当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null。

lag() over():lag(列名,n,m): 当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null。

其中LAG的意思就是取当前顺序的上一行记录，结合over就是分组统计数据。

Over()中执行排序
LAG()OVER(ORDER BY 1) - 对第一列进行排序之后进行上一行的获取；

异常处理
LAG()OVER(ORDER BY EFFECTIVE_FROM) - 异常原因很简单，就是因为字段EFFECTIVE_FROM取不到

FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.

[40000] Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.

Underlying error: org.apache.hadoop.hive.ql.parse.SemanticException: Line 243:92 Invalid table alias or column reference ‘EFFECTIVE_FROM’: (possible column names are: customerid, sk_agency, agencyno, custtype, v_onopenflag_new, v_oncloetflag_new, v_oneffectflag_new, v_onlostflag_new, v_onsleepflag_new, v_onnullflag_new, v_newgroupflag)

group by: select 列表项中不存在的列可以出现在group by的列表项中，但反过来就不行了，在select列表项中出现的列必须全部出现在group by后面(聚合函数除外)。

遇到需要分组的聚合函数（常见的有：sum(),max(),min(),count(),avg()）需要将字段 group by ，否则会报别名指向异常的错误；

- 2019-05-16再次遇到该低级错误

- FAILED: SemanticException [Error 10002]: Line 199:37 Invalid column reference ‘SK_AGENCY’ 很难排查错误原因，开发过程需要重视分组字段是否全

- For Exists/Not Exists operator SubQuery must be Correlated； – 注意字段的对应；

order by:hive中的order by如果字段有别名的话，自动识别成别名，需要按照别名排序；

遇到聚合函数比如count()就不能使用order by：

- FAILED: SemanticException [Error 10004]: Line 3:9 Invalid table alias or column reference

union all/union:合并两个或多个select语句的结果集;

union内部的select语句必须有相同数量的列，并且列也需要是相似的数据类型。同时，每条select语句中的列的顺序必须相同；

union 只会显示一次重复数据，而union all会显示所有数据；

union 结果集的列名总是等于第一个select语句中的列名；

Hive 1.2.0之后才支持 union = union[distinct],之前的版本只能使用Union all

解决方式只有在union all 外面加distinct 再select了~

PS : 大数据量的情况下：distinct + union all 性能大于 union 的性能；

with as: with一般和as连用，定义某个sql片段，会被整段sql使用到，可以提升sql的可读性；并且可以可以将重复的sql代码提取出来，在当前sql执行期间起到临时表的作用，避免重复查询，比如可以和union all搭配使用，因为union all的每个select语句部分可能是相同的，但是每一部分都需要执行一遍的话时间成本太高。

两个with as（唯二式）进行union all起来在进行left join它的性能和分别对两个with as进行left join的性能对比，因为更新必须要有目标表所有字段，因此该测试不再进行，直接选用union all。

collect:实现列转行

collect_set：去除重复元素

collect_list：不去重

concat_ws：多行合并

gender	concat_ws(,collect_set(children))	concat_ws(,collect_list(children)
female	no,yes	no,yes,no.no.yes
male	no,yes	no,yes,no,no,yes

instr():instr(string1 , string2 , 1/-1 , time )获取角标

返回string1中第一次出现字符串片段string2，从右边(>0)/左边(<0)(=0时返回0) 出现第time次(大于0)的位置角标，参数列表第三，四个参数可省略

INSTR(str, substr) - Returns the index of the first occurance of substr in str 返回str中第一次出现substr的角标，以1开始

substr():截取字符串片段

substr(str, pos[, len]) - returns the substring of str that starts at pos and is of length len or

返回一个从str截取的字符串,以pos为开始为开始的标记,截取指定len长度的字符串

substr(bin, pos[, len]) - returns the slice of byte array that starts at pos and is of length len

返回一个bin字节数组以pos开始未标记，截取len长度的数组片段

join-sparksql:

Broadcast hash join:适合一张大表和一张极小表join

spark.sql.autoBroadcastJoinThreshold默认10m，可以调大到适当大小

或者直接hint /*+ BROADCAST(table_name) */ * from 。。。

Shuffle hash join:适合一张大表和一张小表join

Sort Merge join：适合两张大表join

hivesql中join之后的on条件不支持不等值判断:both left and right aliases encountered in JOIN ‘column name’ ;

LEFT JOIN az_dcdw.dim_agencyarea_rela t3
ON t4.sk_agency = t3.sk_agency
AND t.d_cdate BETWEEN str_to_timestamp(t3.effective_from) AND str_to_timestamp(t3.effective_to)
WHERE t.d_cdate BETWEEN str_to_timestamp('${startdate}') AND str_to_timestamp('${enddate}')

Hive supports only equi-joins.Should put the or in WHERE: hivesql中不支持不等值关联，or语句也不支持，需要放到where语句中，但是需要注意NULL值带来的记录数的不一致。

hive中不支持子查询，需要替换成关联来实现

关于hive中的子查询，There are a few limitations:

• These subqueries are only supported on the right-hand side of an expression.-子查询只支持在表达式右边
• IN/NOT IN subqueries may only select a single column.-在子查询中，in/not in只能有一个字段
• EXISTS/NOT EXISTS must have one or more correlated predicates.-exists/not exists必须包含一个或多个关联的谓词
• References to the parent query are only supported in the WHERE clause of the subquery-父查询的对象只能在子查询中的where子句中使用。

hive中的中文别名需要用反引号代替单引号才能被识别

distinct和group by的区别:

一般都是用group by去优化distinct，因为distinct会将所有mapper的数据shuffle到一个reducer上，导致数据倾斜,效率低下，但是底层原因尚未了解；

decode()-Oracle转换成hive函数:DECODE(value, if1, then1, if2,then2, if3,then3, . . . else ) 理解成if语句

if value=if1 ,decode结果=then1, if value =if2 then decode结果=then2 …,else decode结果=else )

–转换成hivesql用case when or if 语句嵌套即可

PS: hive中的decode函数是用来转码的,注意是不支持转成gbk的

- decode(binary bin,string charset):decodes the first argument into a string using provided character set(one of ‘US-ASCII’,‘ISO-8859-1’,‘UTF-8’,‘UTF-16BE’,‘UTF-16LE’,‘UTF-16’) if argument isnull,the result will be null;(Hive 0.12.0)

round(1232143.213 , num):截取函数，根据数值num四舍五入取数

round(12345.6789,-1) = 12350 //num=-1:精确到十位
round(1234.566,-1) = 1230//num<0,精确到小数点前num位
round(12345.34,1) = 12345.3//num=1:精确到小数点后一位
round(12.3455,3) = 12.346//num>0,精确到小数点后num位
round(123.5,0) = 124//num=1,精确到个位

sign():比较大小函数，根据参数列表的结果返回 -1(<0),0(=0),1(>0)

where exists()在hive中的转换：Exists()函数判断括号中的子查询是否为null，不是null就返回True；那么在hive中就可以通过inner join来实现该函数的效果，执行计划从hash-join（哈希关联）转换成nested loops（嵌套循环），IO次数明显减少。

WHERE NOT EXISTS 底层走的join，所以相关join下面的语法规则也有效；

将and not exists中的子查询拖到唯二式-with中，然后关联该with,之后在where条件中添加 'where 字段 is null' 实现对存在值得过滤，但是and条件中需要对表中所有字段进行判断,代码冗余;

TRUNC()-trunc(date,fmt)：将日期转换成指定精度fmt的形式

Returns date with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, then date is truncated to the nearest day. It now only supports ‘MONTH’/‘MON’/‘MM’ and ‘YEAR’/‘YYYY’/‘YY’ as format.

将一个日期格式 2019-04-29的日期指定fmt格式(只支持截取到月或年的方式)输出，比如TRUNC(2019-04-29,‘MON’) = 2019-04-01

last_day(date) - Returns the last day of the month which the date belongs to.

返回一个string类型的date-月末

DATE_FORMAT(date/timestamp/string,fmt) :converts a date/timestamp/string to a value of string in the format specified by the date format fmt:将一个日期转换成指定格式的日期

eg：date_format(date,‘YYYY’)

在sql中 from几张表使用逗号隔开 + where条件 等价于 inner join + on关联条件:select * from a,b where a.num=c.num;等价于 select * from a inner join b on a.num=c.num;

- 原因：sql定义了两种不同的语法方法标识连接，首先是显示 join，其次就是隐式-逗号，on后面的关联条件就放到where后面作为过滤条件；sql89标准只支持内部连接和交叉连接，因此只有隐式连接，sql92增加了对外部连接的支持，这才有了join；

- 两者的效率是一回事，只是书写方式不同而已；

hive表中新增字段 :ALTER TABLE default.test1 ADD COLUMNS (JASON STRING)

hive 给字段添加注释:ALTER TABLE default.test1 CHANGE COLUMN jason jason string comment ‘姓名测试’;

NULLPOINTER EXCEPTION - 一般是字段数据类型没有对上的问题，看字段对应找异常来源

regexp_substr(str,'regexp_format'):判断字符串中是否有正则表达式中表示的值，如果有就返回该正则表达式对应的符号；

建表脚本中的SELECT regexp_substr(‘FACT_CUSTCOUNT1_DETAIL’,’[0-9]+’) FROM DUAL;

regexp_replace():regexp_replace(String,’’,’’) - 被替换的字符串，需要被替换的字符，被替换成目标字符

insert overwrite table select 和 drop table; create table as select

–insert overwrite 会自动将旧的文件放到trash，当文件很大的时候，时间会很长，这个时候可以用第二种方式；

–hive2.3.0之后TBLPROPERTIES(“auto.purge”=“true”)设置表属性，再次insert overwrite的时候就不会将旧数据放到trash，但是该作用只对managed table有效果；

–总结就是遇到需要覆盖一个全量表的数据的需求时，需要先将表中的数据truncate掉再insert overwrite或者drop掉表 再进行create table as ，这样会提高运行效率；

lateral view: 结合udtf使用，将udtf一行对多行输出的结果集形成一张虚拟表并命名一个表别名；

语法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

select a.content,b.name 
from test_table a 
lateral view explode(a.content) b as name

explode: 典型的udtf，将一行内容（一般是map结构或者数组结构）的元素提取出来形成新的一行（将一列转成多行）
get_json_object(需要解析的json元素,'$.[数组角标]or元素的key')：获取json格式目的key的值

--hive赋权给组和用户select权限
grant select on table dmgr_view.pt18nauto_clm_acceptance_ext to GROUP autoquote;
grant select on table dmgr_view.pt18nauto_clm_acceptance_ext to user autoquote;
revoke select from user ysgl;
revoke select from group ysgl;
--hive外表彻底删除方式
ALTER TABLE xxx SET TBLPROPERTIES('EXTERNAL'='False'); 
drop table xxx;
--创建hive外表关联Hbase表sql语句
	CREATE EXTERNAL TABLE rlt_dmgr.hbase_sys0030_kafka( 
	key string COMMENT '', 
	tablename string COMMENT '', 
	partitionid string COMMENT '', 
	offset string COMMENT '', 
	opts string COMMENT '', 
	loadertime string COMMENT '', 
	send_status string COMMENT '', 
	dmgrprocessstarttime string COMMENT '') 
	STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
	WITH SERDEPROPERTIES ('hbase.columns.mapping'=':key, pi:tableName, pi:partitionId, pi:offset, pi:opTs, pi:loaderTime, pi:send_status, pi:dmgrProcessStartTime') 
TBLPROPERTIES ('hbase.table.name'='dmgr_meta:hbase_sys0030_kafka');

--查看month 相关的函数
show functions like '*month*'
--查看add_months 函数的用法
desc function add_months;
--查看 add_months 函数的详细说明并举例
desc function extended add_months;
--explain/desc：想要获取更详细的内容，需要再跟上extended，比如
Explain extended select * from table_name;
Desc extended table_name ;

--设置hive不进行mapjoin
set hive.auto.convert.join = false;
set hive.ignore.mapjoin.hint = false;
set hive.exec.parallel = true;

--drop all partitions in hive:
alter table schedule_events drop if exists partition (year<>'');
alter table az_dcdw.fact_custmanagerfee_fund drop partition (sk_accruedate !='')

hive不支持直接alter table table_name drop column(column_name);想删减表中的字段只能使用alter table table_name replace columns(column_name …)