Hive语法及进阶

目录

1、Hive基本操作——建表

2、Hive——加载数据(上传到hive表)

3、Hive基本语法——分区

4、Hive基本语法——动态分区

5、Hive基本语法——分桶

6、Hive的数据类型

7、Hive高级函数

8、Hive开窗函数

9、Hive自定义函数

10、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
180

select 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    10000

select 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    180

create 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;

 

本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
THE END
分享
二维码
< <上一篇
下一篇>>