hive操作总结手册

1.写udf函数

UDF(User-Defined-Function)，用户自定义函数对数据进行处理。
STEP1：创建UDF函数
                ①自定义UDF需要继承org.apache.hadoop.hive.ql.UDF 
                ②需要实现evaluate函数
STEP2：将udf打jar包，并上传到hive server上
STEP3：进入hive客户端，添加jar包：hive>add jar ${HIVE_HOME}/jar/udf_test.jar;
STEP4：创建临时函数:
hive>CREATE TEMPORARY FUNCTION add_example AS 'hive.udf.Add';
STEP5：使用UDF函数
hive>SELECT add_example(score) FROM scores;
STEP6：销毁临时函数
hive> DROP TEMPORARY FUNCTION add_example;

2.写udaf函数

UDAF（User- Defined Aggregation Funcation）用来实现HQL没有的聚类函数。
STEP1：创建UDAF函数
                ①自定义UDAF需要继承UDAF类，内部类Evaluator实现UDAFEvaluator接口
                ②需要实现 init、iterate、terminatePartial、merge、terminate这几个函数
STEP2：将udaf打jar包，并上传到hive server上
STEP3：进入hive客户端，添加jar包：hive>add jar ${HIVE_HOME}/jar/udaf_test.jar;
STEP4：创建临时函数:
hive>CREATE TEMPORARY FUNCTION avg_example AS 'hive.udaf.avg;
STEP5：使用UDAF函数
hive>SELECT avg_example(col1) FROM scores;
STEP6：销毁临时函数
hive> DROP TEMPORARY FUNCTION avg_example;

3.写udtf函数

UDTF(User-Defined Table-Generating Functions) 用来解决 输入一行输出多行(On-to-many maping) 的需求。

STEP1：创建UDTF函数
                ①自定义UDTF需要继org.apache.hadoop.hive.ql.udf.generic.GenericUDTF 
                ②需要实现initialize, process, close三个方法
STEP2：将udtf打jar包，并上传到hive server上
STEP3：进入hive客户端，添加jar包：hive>add jar ${HIVE_HOME}/jar/udtf_test.jar;
STEP4：创建临时函数:
hive>CREATE TEMPORARY FUNCTION serial AS 'hive.udtf.add';
STEP5：使用UDTF函数
有两种使用方法，一种直接放到select后面，一种和lateral view一起使用
STEP6：销毁临时函数
hive> DROP TEMPORARY FUNCTION serial;

4.join优化

效果最差：

SELECT * FROM t1 JOIN t2 ON t1.id = t2.id
where t1.date = "2016-06-01"

效果一般：
SELECT *
FROM ( SELECT * FROM a WHERE date = "2016-06-01") t1
JOIN b t2
ON t1.id = t2.id;

效果最好：
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id and t1.date = "2016-06-01";

5.查看建表语句

show create table t1;

6.分区表增加字段

需要将修改的表之前的分区删除掉再加工数据。

否则新加工的数据增加的字段无法添加到分区表中。

ps:下面的18点可以解决这个问题，顺表修改之前的分区的数据。

7.locate函数

select locate('a','abcd'), locate('b', 'abcd'), locate('f', 'abcd')

结果: 1 2 0

8.修改变

drop table if exists k1.t1;
create table if not exists k1.t1(
  name string   comment '名字',
)
partitioned by (inc_day string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat';

9.分区表相关操作

#添加一个分区
alter table my_partition_test_table if not exists add partition (p_hour='2017113003', p_city='573', p_loctype='MHA');
#删除单个分区
show partitions dm_analysis.transfer_field_daily_analysis;
alter table  dm_analysis.transfer_field_daily_analysis drop if exists partition(inc_day='20180712');

# 删除多个分区
ALTER TABLE dm_analysis.sdj_jlyc_day_zonecode_sub DROP if exists  PARTITION (inc_day >='20181123',task_type='shou');
ALTER TABLE dm_analysis.sdj_jlyc_day_zonecode_sub DROP if exists PARTITION (inc_day >='20181123',task_type='delivered');

----结论是，根据id分组，就算time_test排序相同，只会有一个第一名
select *,row_number() over(partition by id order by time_test desc) as rn
from dm_analysis.waybill_info_d_test_row_number;

10.导入数据到hive表中

(1)上传csv文件

(2)创建hive表

desc t1;
drop table t1;
create table t1
(
a string comment 'xx词'
,b string comment 'xx'
,c string comment 'xx'
)COMMENT 'xxxx' 
ROW FORMAT delimited 
fields terminated  BY ','
LINES TERMINATED BY '\n'
STORED AS textfile ;

ps:因为.csv文件时以，进行分割，所以建表语句最后一行就是根据,号识别数据，否则数据无法导入。

(3) 将数据存到hive表中

desc formatted t1;

hadoop fs -put xxx.csv hdfs://t1

11.查找hive表中一个字段相同，但是另外一个字段不同的

select * from 
(
select t1.app_id as app_id_1,t2.app_id as app_id_2,t1.source as source1,t2.source as source2
from a t1
left join b t2
on t1.app_id=t2.app_id 
) t
where app_id_1=app_id_2 and source1!=source2
limit 5;

11.记录一下重大的bug错误

发现数据加工老是卡在reduce 98%，一方面是要检查是否发生数据倾斜，另一方面要查看表关联是否正确。哎，因为关联字段错误，导致永远无法加工到第三张表，所以会报错。一定切记，自己犯下的这个超级大的bug

12.hive中  /的优先级高于*

select 1/2*3/6   结果为0.25    操作等级为   (1/2)*(3/6)

13.hive 正则匹配

sql("""
select appid,app_name,pack_name,regexp_extract(app_name, '^[^（]*', 0) as app_name_norm
from xxx
where dayno=20190731 and app_name rlike '天天斗地主（真人版）'
limit 10
""").show(false);
天天斗地主（真人版）    天天斗地主


sql("""
select appid,app_name,pack_name,regexp_extract(app_name, '^[^(]*', 0) as app_name_norm
from xxxx
where dayno=20190709  and app_name rlike '门店宝\\(全国\\)'
limit 10
""").show(false);
门店宝\\(全国\\)    门店宝

14.使用透视表功能 lateral view explode

scala> sql("""
     | select a,b
     | from xxx
     | where dayno = 20190731 limit 10
     | """).show()
+---------------+-----------+
|           a   |      b|
+---------------+-----------+
|z1             |              53,265|
|z2             |        300|
+---------------+-----------+


sql("""
select a,b
from(
     select a,split(b,',') as c
     from x
     where dayno = 20190731
   )a1
lateral view explode(c) t2 as b
limit 10
""").show()
+---------------+------+
|           a   |b    |
+---------------+------+
|z1             |    53|
|z1             |   265|
|z2             |   300|
+---------------+------+

15.hive也支持in  子查询了

select 
a,b,c
from t1 t
where day=xx
    and t.b in (select x from x group by x)

这里要注意的一点是 t.b子查询一定要给表起个别名，否则会报错。

 

16.分位数相关

比如现在有数据格式为：

people1，tag_id，score

现在根据score计算各个分为数，比如p1属于5%分位点，可以用下面的代码

select t1.*,
   (case when score_p_daily<= approxQuantile05 then 05
         when score_p_daily<= approxQuantile10 then 10
         when score_p_daily<= approxQuantile15 then 15
         when score_p_daily<= approxQuantile20 then 20
         else 0 end) as a
from xx t1
join
(
    select tag_id,percentile_approx(score_p_daily,0.05) as approxQuantile05,
    percentile_approx(score_p_daily,0.9) as approxQuantile90,percentile_approx(score_p_daily,0.95) as approxQuantile95
    from xxx
    group by tag_id
) t2
on t1.tag_id=t2.tag_id;

# tested
select tag,percentile_approx(sco,array(0.05,0.5,0.95),9999)
from t
group by tag

17. hive提取中文字符串

select length(regexp_replace('$%^中国人ab的谁啊。。。&&*!(12JJJ暗示，。.,','([^\\u4e00-\\u9fa5])','')); 

18.修改或增加hive表字段属性

修改：

alter table t1 change column a b string comment "注释" cascade;

注意：这里要注意的是cascade，会对所有的分区起作用。否则对之前的分区无效。

增加：
alter table t1 add columns(app_type string comment "app是否游戏，game代表游戏") cascade;

19.hive随机采样

# 抽样方法1：
select a
from t TABLESAMPLE(BUCKET 1 OUT OF 5 ON rand()) s
where xx;

# 抽样方法2：
select a
from t
where xx
    distribute by rand() sort by rand() limit 200000;

两种抽样方法对比：

加入t表中有100W的数据，我们需要抽取20%的数据。

上面是两种不同的抽取方法。经过实验分析，抽样方法1的速度比方法2快一倍。

建议：使用抽样方法1

 

20.hive进行md5编码

select md5(concat(1,',',2)) as url_id from dual;

21.异常代码处理

比如：含有百分号文字如：
欧洲7月电动车销量跌4% 高尔夫GTE最惨

类似这种异常字符编码解码会报错。

.replace("%(?![0-9a-fA-F]{2})", "%25")}

22.插入多个分区数据

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table t1 partition(dayno)
select *
from t2
where dayno>=20191201;

23.不等值判断陷阱

有时候使用<> 或者！进行where判断的时候会"失效"，比如app_type<>'game'，我们

想判断app_type字段不等于game游戏的。但是如果实际这个字段是null的话，不等值判断会

失效，返回null。

所以这里正确的方式是 (app_type<>'game' or  app_type is null)