title: hive操作和踩坑指北
categories:
- AiSpeeech
tags: AiSpeech
date: 2020-06-09 11:04:53
hive操作
#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后跟字符串要是变量''
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
Example: IF(1=1, 'working', 'not working') returns 'working'第二种：COALESCE
这个词优点不好记啊。
它返回第一个不为NULL的值。
Example: COALESCE(NULL,NULL,5,NULL,4) returns 5第三种：CASE WHEN THEN END
CASE [ expression ]
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionn THEN resultn
ELSE result
ENDhive transform使用
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"from后应该是另外一个select语句，用于Transform的输入，最好是一一对应的，否则会出错- 插入时会出现较多NULL扰乱目标表，后发现transform只能以’\t’分隔，而print自动末尾+换行符此换行符transform处理为单独的一行插入NULL
用这个job后的数字ID可以查询error信息，$1的地方传入ID号，grep后改为你要查的字段
yarn logs -applicationId application_ > logafteraddfile
cat logafteraddfile | grep dealget_json_object使用
get_json_object(string json_string, string path)
说明：
第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组；如果输入的json字符串无效，那么返回NULL。
每次只能返回一个数据项。
举例：
data 为 test表中的字段，数据结构如下：
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单层值
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"}自己使用
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]，一些坑
hive -e "select uid, from tmp12
lateral view explode(split(,',')) 表别名 AS 列别名;"
这种结果会是列和uid左连接
输入:
uid
1 [true,false]
输出:
uid true
uid false正确写法，查询explode后的列别名保留
hive -e "select uid,c3 from tmp12 lateral view explode(split(, ',')) virtualtbn表别名 as c3"一、explode()
这个函数大多数人都接触过，将一行数据转换成列数据，可以用于array和map类型的数据。
用于array的语法如下：
select explode(arraycol) as newcol from tablename;- explode()：函数中的参数传入的是arrary数据类型的列名。
- newcol：是给转换成的列命名一个新的名字，用于代表转换之后的列名。
- tablename：原表名。
用于map的语法如下：
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字段的目的。
格式一
lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)*- lateral view在UDTF前使用，表示连接UDTF所分裂的字段。
- UDTF(expression)：使用的UDTF函数，例如explode()。
- tableAlias：表示UDTF函数转换的虚拟表的名称。
- columnAlias：表示虚拟表的虚拟字段名称，如果分裂之后有一个列，则写一个即可；如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开。
格式二
from basetable (lateral view)*- 在from子句中使用，一般和格式一搭配使用，这个格式只是说明了lateral view的使用位置。
- from子句后面也可以跟多个lateral view语句，使用空格间隔就可以了。
格式三
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 子句中包括的列
输出
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