hive操作和踩坑指北

Posted on | In

hive操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
#1.创建数据库
hive>create database myhive1;
#2.使用数据库
hive>use myhive1;
#3.查询现在使用的数据库
hive>select current_database;
#4.创建表结构
hive>create table student(id int, name string comment '33标签名字', sex string, age int, department string) 
    >row format delimited fields terminated by ",";
#copy表但不copy数据    
CREATE TABLE yourtable 
LIKE table2; 
#更常用如下，和其他语句连用记得分号隔开
create table if not exists ${tableName};
    
#5.向表中加载数据，注意inpath后跟字符串要是变量'$1'
hive -e "load data local inpath 'ud2' into table 33tmptb partition(dt=20170919);"#从文件中追加

#insert方式需要插入的列数严格对齐不能用select *，且overwrite不会重写load进去的数据
#从select中追加，若重写把Into改为overwrite
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table 33tmptb partition(dt=25) select name from load_data_local;

#6.查询表
hive> set hive.fetch.task.conversion=more;
这句话不经过mapreduce大大加速了查询速度
select * from student limit 10;


#7.查询表结构
hive> describe student;
#8.查询详细的表结构
hive>describe formatted student;
#9.cast建立子表
hive>create table student_ctas as select * from student where id <95012;
#10. 查询创建表语句
hive > show create table TBname
#*****这里有个坑， `dt` string)中的`符号要删掉
hdfs -du -h
#11. 删除表
hive>DROP TABLE IF EXISTS employee
#12. 删除某个分区
hive -e "alter table tablename drop partition (dt='20200804')"
#13. 更改表名

在实际情况中，表的输出结果可能太多，不适于显示在控制台上，这时候，将Hive的查询输出结果直接存在一个新的表中是非常方便的，我们称这种情况为CTAS（create table 表名 as select 列名 from 查询的表名）

条件语句

第一种：IF
1
Example: IF(1=1, 'working', 'not working') returns 'working'
第二种：COALESCE

这个词优点不好记啊。

它返回第一个不为NULL的值。

1
Example: COALESCE(NULL,NULL,5,NULL,4) returns 5
第三种：CASE WHEN THEN END
1
2
3
4
5
6
7
CASE   [ expression ]
       WHEN condition1 THEN result1
       WHEN condition2 THEN result2
       ...
       WHEN conditionn THEN resultn
       ELSE result
END

hive transform使用

1
2
3
4
5
6
7
hive -e "add file logging.zip;add file gensim.zip;add file deal12.py;add file word2vec_model_20200325;
select transform(a.uid, a.device)
using 'deal12.py'
as (aaa, bbb,p3,p4,p5,p6,p7,p8,p9,p10)
from (
select uid,device from
gh_user_feature_daily where dt='20200606' and usertype='uid' limit 30)as a"
  • 1
    from后应该是另外一个select语句，用于Transform的输入，最好是一一对应的，否则会出错

  • 插入时会出现较多NULL扰乱目标表，后发现transform只能以’\t’分隔，而print自动末尾+换行符此换行符transform处理为单独的一行插入NULL

用这个job后的数字ID可以查询error信息，$1的地方传入ID号，grep后改为你要查的字段

1
2
yarn logs -applicationId application_$1 > logafteraddfile
cat logafteraddfile | grep deal

get_json_object使用

get_json_object(string json_string, string path)

说明：
第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组；如果输入的json字符串无效，那么返回NULL。
每次只能返回一个数据项。

举例：
data 为 test表中的字段，数据结构如下：

1
2
3
4
5
6
7
8
9
10
data =
{
 "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" 
}

1.get单层值

1
2
3
4
5
6
7
8
9
10
hive> select  get_json_object(data, '$.owner') from test;
结果：amy

2.get多层值.
hive> select  get_json_object(data, '$.store.bicycle.price') from test;
结果：19.95

3.get数组值[]
hive> select  get_json_object(data, '$.store.fruit[0]') from test;
结果：{"weight":8,"type":"apple"}

自己使用

1
hive -e "create table tmpud as select  get_json_object(device, '$.push_short_interest_tag') from gh_user_feature_daily where dt='20200606' and usertype='uid' limit 10"

输入一行拆多行的lateral view和explode()

explode函数输入被拆行，需要是array，split（’[1,2,3]’， ‘,’）可以把String’[1,2,3]’分隔为array[1,2,3]，一些坑

1
2
3
4
5
6
7
8
9
10
hive -e "select uid,$1 from tmp12
lateral view explode(split($1,',')) 表别名 AS 列别名;"
这种结果会是$1列和uid左连接

输入:
uid $1
1		[true,false]	
输出:
uid true
uid false

正确写法，查询explode后的列别名保留

1
hive -e "select uid,c3 from tmp12 lateral view explode(split($1, ',')) virtualtbn表别名 as c3"
一、explode()

这个函数大多数人都接触过，将一行数据转换成列数据，可以用于array和map类型的数据。

用于array的语法如下：

1
select explode(arraycol) as newcol from tablename;
  • explode()：函数中的参数传入的是arrary数据类型的列名。
  • newcol：是给转换成的列命名一个新的名字，用于代表转换之后的列名。
  • tablename：原表名。

用于map的语法如下：

1
select explode(mapcol) as (keyname,valuename) from tablename;
  • explode()：函数中的参数传入的是map数据类型的列名。
  • 由于map是kay-value结构的，所以它在转换的时候会转换成两列，一列是kay转换而成的，一列是value转换而成的。
  • keyname：表示key转换成的列名称，用于代表key转换之后的列名。
  • valuename：表示value转换成的列名称，用于代表value转换之后的列名称。

注意：这两个值需要在as之后用括号括起来然后以逗号分隔。

以上为explode()函数的用法，此函数存在局限性：

  • 其一：不能关联原有的表中的其他字段。
  • 其二：不能与group by、cluster by、distribute by、sort by联用。
  • 其三：不能进行UDTF嵌套。
  • 其四：不允许选择其他表达式。
二、lateral view

lateral view是Hive中提供给UDTF的结合，它可以解决UDTF不能添加额外的select列的问题。

lateral view其实就是用来和想类似explode这种UDTF函数联用的，lateral view会将UDTF生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行进行join来达到连接UDTF外的select字段的目的。

格式一
1
lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)*
  • lateral view在UDTF前使用，表示连接UDTF所分裂的字段。
  • UDTF(expression)：使用的UDTF函数，例如explode()。
  • tableAlias：表示UDTF函数转换的虚拟表的名称。
  • columnAlias：表示虚拟表的虚拟字段名称，如果分裂之后有一个列，则写一个即可；如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开。
格式二
1
from basetable (lateral view)*
  • 在from子句中使用，一般和格式一搭配使用，这个格式只是说明了lateral view的使用位置。
  • from子句后面也可以跟多个lateral view语句，使用空格间隔就可以了。
格式三
1
from basetable (lateral view outer)*

它比格式二只是多了一个outer，这个outer的作用是在UDTF转换列的时候将其中的空也给展示出来，UDTF默认是忽略输出空的，加上outer之后，会将空也输出，显示为NULL。这个功能是在Hive0.12是开始支持的。

####

排序

1.Order By和 Sort BY

尽量不用order by。
ORDER BY 是全局排序，最终hadoop会把所有数据通过一个reducer，这样很可能会超过单个节点的磁盘和内存存储能力导致任务失败。
SORT BY 是局部排序，各个reducer各自排序，不保证全局有序
都可以使用升序asc，降序desc，缺省是asc

2.含有SORT BY的DISTRUBUTE BY

DISTRIBUTE BY 可以控制map的输出在reducer中是如何划分的，如果不指定，默认是按哈希值进行均匀划分。可以手工指定键，以保证在这个键上的sort by是有序的。

使用order by会引发全局排序

select * from baidu_click order by click desc;

使用distribute和sort进行分组排序

select * from baidu_click distribute by 列名 sort by click desc;

distribute by + sort by就是该替代方案，被distribute by设定的字段为KEY，数据会被HASH分发到不同的reducer机器上，然后sort by会对同一个reducer机器上的每组数据进行局部排序。

3. Group By

  • group by的作用是用来分组，表示按照某些字段的值进行分组，相同的值放到一起
  • GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录对应其他列数据放入一行，我们找同一学期报同一课程的总(count)人数，筛选条件是且的关系
  • 一些没有聚合函数的字段必须要加到group by 后边，不和聚合函数使用可起到去重作用，即相同字段对应其他列放一行但不展示
  • 通常和聚合函数sum,avg一起使用，一个Key就是一个逻辑上的group来分组操作，having联合使用在组内过滤，where是整个数据过滤
  • Select列表中的所有列必须是聚合列（SUM,MIN/MAX,AVG等）或是GROUP BY 子句中包括的列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
输出
Subject   学期   Attendee
---------------------------------
ITB001    1          John
ITB001    1          Bob
ITB001    1          Mickey
ITB001    2          Jenny
ITB001    2          James
MKB114    1          John
MKB114    1          Erica

SELECT Subject, Count(*)
FROM Subject_Selection
GROUP BY Subject

Subject    Count
------------------------------
ITB001     5
MKB114     2

GROUP BY Subject，学期

Subject    Semester   Count
------------------------------
ITB001     1          3
ITB001     2          2
MKB114     1          2
1
2