Hive语法及进阶
目录
1、Hive基本操作——建表
内部表基础建表语句一:(默认指定文件类型为TextFile,HDFS路径为/user/hive/warehouse/库/下)
格式:create table 表名 ( 字段名1 字段类型1, 字段名2 字段类型2, ... ) row format delimited fields terminated by '分隔符'; // 必选指定列之间的分隔符
内部表基础建表语句二:(HDFS路径为/user/hive/warehouse/库/下)
格式:
create table 表名 ( 字段名1 字段类型1, 字段名2 字段类型2, ... ) row format delimited fields terminated by '分隔符'// 必选指定列之间的分隔符 stored as file_format; 概述: stored as:指定具体的从HDFS获取数据的格式,格式不匹配无法获取(默认为TextFile)
内部表基础建表语句三:(HDFS路径为/user/hive/warehouse/库/下)
格式:create table 表名 ( 字段名1 字段类型1, 字段名2 字段类型2, ... ) row format delimited fields terminated by '分隔符' // 必选指定列之间的分隔符 stored as file_format location 'HDFS路径'; 概述: location:表示指定hive数据在hdfs的路径, 如果路径不存在会自动创建,存在就直接绑定,不能通过 hdfs路径判断是否是hive表 注意: 以上创建的都是内部表, 默认情况下 ,删除表会把数据也删除
外部表external
格式:
create external table 表名 ( 字段名1 字段类型1, 字段名2 字段类型2, ... ) row format delimited fields terminated by '分隔符' // 必选指定列之间的分隔符 stored as file_format location 'HDFS路径'; 注意: 删除外部表不会删除HDFS上的数据
2、Hive——加载数据(上传到hive表)
上传数据方式1:
格式:hadoop dfs -put linux路径 hdfs路径;
上传数据方式2:(直接在hive命令行操作)
格式:dfs -put linux路径 hdfs路径; 优点:比在hadoop操作hdfs快的多
上传数据方式3:(直接在hive命令行操作)
格式:load data inpath '/HDFS路径' into 表名(自动找到hdfs的路径); 注意:对于hive来说是加载,对于HDFS来说是移动
上传数据方式4:(直接在hive命令行操作)
格式:load data local inpath '/HDFS路径' into 表名(自动找到hdfs的路径); 注意:从本地上传数据
上传数据方式5:(直接在hive命令行操作)
格式:create table 表名 as 查询语句 create table student as select * from studentsText; 注意:只能创建内部表
上传数据方式6:(直接在hive命令行操作)
格式1:insert into 表名 查询语句 insert into students select * from studentsText;
格式2:
insert overwrite table 表名 查询语句 insert overwrite table students select * from studentsText; 注意:数据对数据,会有格式的转化
3、Hive基本语法——分区
分区:避免全表扫描
格式:create external table 表名 ( 字段名1 字段类型1, 字段名2 字段类型2, ... )pratition by (分区字段 字段类型) row format delimited fields terminated by '分隔符'// 必选指定列之间的分隔符 stored as file_format location 'HDFS路径'; 注意: 分区字段和普通字段没区别(不能重复)
添加分区:
alter table 表名 add partition(分区字段='值');
删除分区:
alter table 表名 drop partition(分区字段='值');
查看分区:
select distinct 分区字段 from 表名; show partitions 表;
插入数据(需指定分区):
load data local inpath '路径' into table 表名 partiton(分组字段='值'); 注意:分区不存在自动创建
4、Hive基本语法——动态分区
set hive.exec.dynamic.partition=true; //开启hive动态分区,hive默认不支持动态分区
set hive.exec.dynamic.partition.mode=nostrict; //动态分区模式 动静结合
set hive.exec.max.dynamic.partitions.pernode=1000; //hive最大分区数
insert into 分区表 分区字段(字段名称) select * from 表
注意:分区字段不会按照名字匹配,而是按照位置(匹配查询到的最后一个字段)
例如:insert into 分区表 partition(dt_year,dt_month) select id,name,age,gender,clazz,year,month from 表
5、Hive基本语法——分桶
分桶(动态的,hive默认不支持分桶)
set hive.enforce.bucketing=true
格式:
create external table 表名 ( 字段名1 字段类型1, 字段名2 字段类型2, ... )clustered by (分桶字段) into 分桶数量 buckets row format delimited fields terminated by '分隔符'// 必选指定列之间的分隔符 stored as file_format location 'HDFS路径';
注意:
分桶字段来源普通字段,分同数量是明确的
分桶加载数据:
load:不会触发分桶
insert into:自动触发分桶
注意:
分桶:不常用,因为对文件进行切分产生过多的小文件
分区:最多三级分区
6、Hive的数据类型
1)基本数据类型
数值型
TINYINT — 微整型,只占用1个字节,只能存储0-255的整数。
SMALLINT– 小整型,占用2个字节,存储范围–32768 到 32767。
INT– 整型,占用4个字节,存储范围-2147483648到2147483647。
BIGINT– 长整型,占用8个字节,存储范围-2^63到2^63-1。
布尔型
BOOLEAN — TRUE/FALSE
浮点型
FLOAT– 单精度浮点数。
DOUBLE– 双精度浮点数。
字符串型
STRING– 不设定长度。
2)日期类型:
1,Timestamp 格式“YYYY-MM-dd HH:mm:ss.fffffffff”(9位小数位精度)
2,Date DATE值描述特定的年/月/日,格式为"YYYY-MM-dd"。
时间戳——>日期
select from unixtime(时间戳,'YYYY-MM-dd');
日期——>时间戳
select unix_timestamp('2021年9月26日 16时00分21秒','YYYY年MM月dd日 HH时mm分ss秒'); 注意:前后格式要一样,后面格式如果没给,前面只能给默认格式:'2021-9-26 16:00:21'
create table testDate( ts timestamp, dt date )row format delimited fields terminated by ','; //2021-01-14 14:24:57.200, 2021-01-11
3)复杂数据类型: Structs,Maps,Arrays
**array
create table testArray( name string, weight array<string> )row format delimited fields terminated by 't' collection items terminated by ','; //zs 120,130,140 //ls 160,170,180
select * from testArray; zs ["110","120","130"] ls ["170","160","180"]
select weight[0] from testArray; 110 170
*map
create table scoreMap( name string, score map<string,int> )row format delimited fields terminated by 't' collection items terminated by ',' map keys terminated by ':'; //zs score:80,teacher:xm,money:1000 //ls score:66,teacher:xh,money:10000
select * from scoreMap; zs {"score":80,"teacher":null,"money":1000} ls {"score":66,"teacher":null,"money":10000}
select score['money'] from scoreMap; 1000 10000
*struct
create table scoreStruct( name string, score struct<course:string,score:int,course_id:int,teacher:string> )row format delimited fields terminated by 't' collection items terminated by ','; //zs 数学,80,1001,小明 //ls 语文,90,1002,小红
select * from scoreStruct; zs {"course":"数学","score":80,"course_id":1001,"teacher":"小明"} ls {"course":"语文","score":90,"course_id":1002,"teacher":"小红"}
select score.score from scoreStruct; 80 90
hive转json格式:
get_json_object
select get_json_object('{"course":"数学","score":80,"course_id":1001,"teacher":"小明"}','$.course'); 数学 $:表示当前json转化之后的对象
7、Hive高级函数
1)行转列
lateral view用于和split, explode等UDTF一起使用,它能够将一行数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。
对数组展开
zs ["110","120","130"]select explode(weight) from testArray;
110
120
130
170
160
180select name,new_col from testArray lateral view explode(weight) sum as new_col;
zs 110
zs 120
zs 130
ls 170
ls 160
ls 180对map展开
zs {"score":80,"teacher":null,"money":1000}select explode(score) from scoreMap;
score 80
teacher NULL
money 1000
score 66
teacher NULL
money 10000select name,col1,col2 from scoreMap lateral view explode(score) t1 as col1,col2;
zs score 80
zs teacher NULL
zs money 1000
ls score 66
ls teacher NULL
ls money 10000
2)列转行
collect_list
zs 110
zs 120
zs 130
ls 160
ls 170
ls 180create table test( name string, age string )row format delimited fields terminated by ',';
select name,collect_list(age) from test group by name; ls ["160","170","180"] zs ["110","120","130"]
8、Hive开窗函数
1)窗口函数
create external table students( id string, name string, age string, gender string, clazz string ) row format delimited fields terminated by ',';
1)row_number():无并列排名
通过某些操作对数据一一打上行号,over(重写的规则,按性别打行号,按年龄从大到小排序)select * ,row_number() over(partition by gender order by age desc) as num from students;
分别求男女年龄前三(一般用于分组中求 TopN)
select * from (select * ,row_number() over(partition by gender order by age desc) as num from students) as s where s.num<=3;
2)desc_rank:有并列排名,依次递增
3)rank:有并列排名,不依次递增
4)percent_rank:(rank的结果-1)/(分区内数据的个数-1)select * ,row_number() over(partition by gender order by age desc) as num ,desc_rank() over(partition by gender order by age desc) as rank1 ,rank() over(partition by gender order by age desc) as rank2 ,percent_rank() over(partition by gender order by age desc) as rank3 from students;
2)窗口帧:用于从分区中选择指定的多条记录,供窗口函数处理
current row :当前行
unbounded:无界限
1、按行号划分 rows
1)rows格式1:前一行+当前行+后一行
avg(score) over (partition by clazz order by score desc rows between 1 preceding and 1 following) as avg1,
2)rows格式2:前两行+当前行
max(score) over (partition by clazz order by score desc rows between 2 preceding and current row) as max1,
2、按值范围划分,窗口大小不固定 range
range格式:如果当前值在80,取值就会落在范围在80-2=78和80+2=82组件之内的行
avg(score) over (partition by clazz order by score desc range between 2 preceding and 2 following) as avg2,
create table testwos( id string, score string, clazz string )row format delimited fields terminated by ',';
id score clazz
111,69,class1
113,74,class1
216,74,class1
112,80,class1
215,82,class1
212,83,class1
211,93,class1
115,93,class1
213,94,class1
114,94,class1
214,94,class1
124,70,class2
121,74,class2
223,74,class2
222,78,class2
123,78,class2
224,80,class2
225,85,class2
221,99,class2
3)Windows as 的使用
格式:Windows 变量名 as (变量);将经常使用的字段定义
原代码
select *,
row_number() over (partition by clazz order by score desc) as num1,
rank() over (partition by clazz order by score desc) as num2
from testwos;
改进后
select *,
row_number() over w as num1,
rank() over w as num2
from testwos
Window w as (partition by clazz order by score desc) ;
4)with as的使用
格式:with 表名 as (查询语句)
例子:想要求学生总分,且显示学生信息
对students和score两张表做一个关联
create table score(
id string,
cou_id string,
score bigint
)row format delimited fields terminated by ',';
4个job任务
//mysql版本的原始语句
select * from (select id,sum(score)as score from (select students.*,score.score from students left join score on students.id= score.id)as stu group by id) as s1 left join (select students.*,score.score from students left join score on students.id= score.id) as s2 on s1.id=s2.id;
//hive版本的使用with as后(定义一条查询语句)
with stu as (select students.*,score.score from students left join score on students.id=score.id) select * from (select id,sum(score)as score from stu group by id) as s1 left join stu as s2 on s1.id=s2.id;
//hive版本的使用with as后(定义两条查询语句)
with stu as (select students.*,score.score from students left join score on students.id=score.id), s1 as (select id,sum(score) as score from stu group by id) select * from s1 left join stu on s1.id=stu.id;
9、Hive自定义函数
1) UDF 一进一出
* 创建maven项目,并加入依赖
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.1</version>
</dependency>
参数传入字符串
package com.shujia.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
/**
* 一进一出
* "hello"——>"$hello#"
* 1001,zs
* select id,fun01(name) from students;
* 1001,$zs#
*/
public class TestUDF extends UDF {
public String evaluate(String str){
String newstr="$"+str+"#";
return newstr;
}
}
参数传入数组
package com.shujia.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
import java.util.ArrayList;
/**
* [120,130,140]
* 120-130-140
*
* select fun02(weight) from testArray;
* 110-120-130
* 170-160-180
*/
public class UDFArray extends UDF {
public String evaluate(ArrayList<String> arr){
String newStr="";
for (String s : arr) {
newStr=newStr+"-"+s;
}
return newStr.substring(1);
}
}
参数传入可变参数
package com.shujia.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
import java.util.ArrayList;
/**
* UDF可以进多个参数,但只能一出
* select fun04('123','456','789');
* ["123","456","789"]
*/
public class MyUDF04 extends UDF {
//可变参数实际上是一个数组String[]
public ArrayList<String> evaluate(String ...args){
ArrayList<String> list = new ArrayList<String>();
for (String s : args) {
list.add(s);
}
return list;
}
}
代码实现自定义hive函数 UDF函数
1)导包
2)当前类继承hive自带的UDF类
3)重写evaluate函数
4)打包上传,hive需要加载jar资源
* hive shell中,使用 add jar 路径 jar包作为资源添加到hive环境中
add jar /usr/local/soft/jars/HiveUDF2-1.0.jar;
5)hive中注册UDF函数
* 使用jar包资源注册一个临时函数,fun01是你的函数名,'com.shujia.udf.TestUDF'是主类名
create temporary function fun01 as 'com.shujia.udf.TestUDF';
* 使用函数名处理数据
select fun01('123');
$123#
select id,fun01(name) from students;
2)UDTF一进多出
package com.shujia.udtf;
/**
* 一进多出
* [zs,18]
* col1 col2
* String int
*/
public class MyUDTF01 extends GenericUDTF {
//指定进来的数据类型,返回的数据类型,返回的数据列名
@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
//以数组存储多出的字段名
ArrayList<String> filedNames = new ArrayList<String>();
//以数组存储多出的字段类型
ArrayList<ObjectInspector> filedObj = new ArrayList<ObjectInspector>();
filedNames.add("col1");
filedObj.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
filedNames.add("col2");
filedObj.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
return ObjectInspectorFactory.getStandardStructObjectInspector(filedNames, filedObj);
}
@Override
/**
* 写逻辑的
* 接受复杂数据 Java没办法解析 通过GenericUDTF自带的方法去解析
* select fudtf01("key1:value1,key2:value2");
* key1 value1
* key2 value2
*/
public void process(Object[] objects) throws HiveException {
for (int i = 0; i <objects.length ; i++) {
String s = objects[i].toString();
String[] split = s.split(",");//["zs:18","ls:25"]
for (String s1 : split) {
String[] split1 = s1.split(":");
//多出 不再走Java返回值(因为Java返回值只有一个)
forward(split1);//hive 中没有object,需要指定返回的类型
}
}
}
}
10、Hive-数据倾斜优化
数据倾斜解决 :看下key的分布 处理集中的key
原因:
1)key分布不均匀(实际上还是重复) 比如 group by 或者 distinct的时候
2)数据重复,join 笛卡尔积 数据膨胀
表现:
任务进度长时间维持在99%(或100%),查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。 单一reduce的记录数与平均记录数差异过大,通常可能达到3倍甚至更多。 最长时长远大于平均时长。
解决方案:
1)看下业务上,数据源头能否对数据进行过滤,比如 key为 null的,业务层面进行优化。
2)找到key重复的具体值,进行拆分,hash。异步求和。
异步求和
1、数据打散
2、正常求和
3、和相加
遇见了count(1)这种写法,什么意思?
count(*) 统计所有行,不会忽略列值为NULL
count(1) 也是查询所有行总数,与count(*) 结果一样,在统计结果的时候,不会忽略列值为NULL
count(列名) 只包括列名那一列,在统计结果的时候,会忽略列值为null的计数
0~1 rand()
0~9 rand()*10
向下取整 ceil(rand()*10)
原来的数据
select id,count(1) from bigtest group by id;
数据打散
将id为84401和null的字段打散
with bd as (select *,if(id='84401' or id = 'null',ceil(rand()*10),id) as index from bigtest)
select id,index,count(1) as c from bd group by id,index;
正常求和
with bd as (select *,if(id='84401' or id = 'null',ceil(rand()*10),id) as index from bigtest)
,bd1 as (select id,index,count(1) as c from bd group by id,index)
select id,sum(c) from bd1 group by id;