hive3.x入门

前置条件

需要先安装hadoop

hadoop3入门(1)-CSDN博客

hive基础知识

hive是一个基于hadoop的一个数据仓库工具，可以把数据文件映射成一张表，可以sql查询

hive的本质

hive是一个hadoop的客户端，用于将HQL(hive sql)转换成MapReduce程序
1.hive的每张表的数据存储在hdfs
2.hive分析数据底层的实现是MapReduce
3.执行程序运行在yarn上

hive安装部署

下载地址

Index of /dist/hive/hive-3.1.3

 上传到linux中，并解压 重命名

cd /opt/module

tar -zxvf apache-hive-3.1.3-bin.tar.gz

mv apache-hive-3.1.3-bin hive

添加环境变量

vi /etc/profile

export HIVE_HOME=/opt/module/hive
export PATH=$PATH:$HIVE_HOME/bin 

让环境变量生效 

source /etc/profile

配置元数据到mysql

前提先安装好mysql数据库

将mysql-connector-java-5.1.27.jar上传到下面hive的lib目录下

在hive的conf目录下创建hive-site.xml

cd /opt/module/hive/conf

vi hive-site.xml

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
 
<configuration>
    <!-- jdbc连接的URL -->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://192.168.10.104:13306/dmg?useSSL=false</value>
    </property>
    
    <!-- jdbc连接的Driver-->
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
    </property>
    
    <!-- jdbc连接的username-->
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>
 
    <!-- jdbc连接的password -->
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>123456</value>
    </property>
 
    <!-- Hive默认在HDFS的工作目录 -->
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/user/hive/warehouse</value>
    </property>
</configuration>

初始化hive元数据库为mysql

cd /opt/module/hive/bin

schematool -dbType mysql -initSchema -verbose

执行命令之后，就会为数据库创建一些表

启动hive

cd /opt/module/hive/bin

hive

 创建表并添加数据

create table biao(id int,name string);

insert into biao values(1,"zhangsan");

select * from biao;

访问hdfs,我这边端口是9870

http://hadoop102:9870/explorer.html#/user/hive/warehouse/biao

可以看到，hive的表数据就是在hdfs中 

 

查看mysql的中存储的库信息

select * from DBS;

 

 查看mysql中存储的表信息

select * from TBLS;

 查看mysql中存储的列信息

select * from COLUMNS_V2;

hiveserver2部署

hiveserver2的模拟用户功能，依赖hadoop提供的代理用户功能，只有hadoop中的代理用户才能模拟其他用户访问hadoop集群

修改hadoop的core-site.xml，然后分发到其他2台机器上

cd /opt/module/hadoop-3.3.6/etc/hadoop

vi core-site.xml

<!--配置所有节点的root用户都可作为代理用户-->
<property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
</property>
 
<!--配置root用户能够代理的用户组为任意组-->
<property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
</property>
 
<!--配置root用户能够代理的用户为任意用户-->
<property>
    <name>hadoop.proxyuser.root.users</name>
    <value>*</value>
</property>

把文件拷贝到另外2台hadoop集群中 

scp core-site.xml root@hadoop103:/opt/module/hadoop-3.3.6/etc/hadoop/

scp core-site.xml root@hadoop104:/opt/module/hadoop-3.3.6/etc/hadoop/

修改hive的hive-site.xml,配置连接的ip和端口

hadoop102就是我当前的机器的域名

cd /opt/module/hive/conf

vi hive-site.xml

<!-- 指定hiveserver2连接的host -->
<property>
    <name>hive.server2.thrift.bind.host</name>
    <value>hadoop102</value>
</property>
 
<!-- 指定hiveserver2连接的端口号 -->
<property>
    <name>hive.server2.thrift.port</name>
    <value>10000</value>
</property>
 

记得把hadoop集群重新启动

启动hiveserver2

cd /opt/module/hive/bin

hive --service hiveserver2

启动beeline客户端 测试是否能连接hive上

cd /opt/module/hive/bin

beeline -u jdbc:hive2://192.168.10.102:10000 -n root 

 使用idea的数据库客户端，连接hive,不需要密码

metastore服务

hive的metastore服务的作用是为hive cli或者hiveserver2提供元数据访问接口

有2种运行模式，嵌入式模式和独立服务模式

嵌入式模式

默认就是嵌入式模式，生产环境下不推荐
嵌入式模式下，只需要保证hiveserver2和每个hive客户端的配置文件Hive-site.xml包含连接元数据库所需要的参数既可

独立服务模式

就是在嵌入式模式下多了下面的配置

<!-- 指定metastore服务的地址 -->
<property>
    <name>hive.metastore.uris</name>
    <value>thrift://hadoop102:9083</value>
</property>

启动metastore

hive --service metastore

 然后再另一个窗口启动hive,就可以在这里输入sql语句了

hive的常用命令

对sql文件的操作

创建aa.sql文件，输入sql内容

select * from biao;

执行命令，就可以把sql的结果直接打印出来,并退出hive程序

hive -f aa.sql

查看日志

tail -f /tmp/root/hive.log 

创建数据库

 不指定路径，在默认路径下

create database aa;

指定路径,默认在根目录下

create database bb location '/lujing'

创建表 

并以\t 分割数据

create table biao2( 
    id int,
    name string
)
row format delimited fields terminated by '\t';

创建aa.txt,注意要\t分割数据

12    zhangsan
22    lisi
32    wangwu 

然后把aa.txt上传到hdfs对应的表下

hadoop fs -put aa.txt /user/hive/warehouse/biao2 

 再次查询就有数据了

创建json格式的表

create table biao4
(
    name string,
    pwd array<string>,
    age map<string,int>,
    num struct<bianhao:string,jifen:int>
)
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe';

创建bb.txt,格式是json格式，但是内容要压缩，因为hadoop是按照一行一行读取的

{"name":"json格式的表","pwd":["123456","66666"],"age":{"lisi":100,"wangwu":88},"num":{"bianhao":"001","jifen":300}}

上传到hdfs对应的表下面的目录

hadoop fs -put bb.txt /user/hive/warehouse/biao5

再次查看表信息

查看表结构

desc biao5;

添加列

添加一个pwd的字段

alter table biao add columns (pwd string)

修改列 

把pwd缓存pwd3

alter table biao change column pwd pwd3 double;

替换列 

把整个表结构更新为只有id一个字段

alter table biao replace columns (id int);

删除表

drop table biao2;

清空表数据

truncate table biao;

加载本地文件到hive的表中，在hive中执行

load data local inpath '/opt/aa.txt' into table biao2;

加载hdfs的文件到hive的表中

这样是追加到表中

 load data inpath '/user/hive/warehouse/biao2/aa.txt' into table biao2;

导出表数据到hdfs中

export table biao2 to '/user/hive/warehouse/biao';

解析json字符串

第一个参数是json字符串

第二个参数是要显示的内容

$[1]表示 json的第二条数据

select get_json_object('[{"name":"张三","age":"111"},{"name":"李四","age":"222"}]',"$[1].age");

explode

把一个数组展开为多行

select explode(`array`(1,2,3)) as aa;

展开map

select explode(map("a",1,"b",2,"c",3)) as (key,value);

posexplode

展开数组并显示索引 

select posexplode(`array`("a","b","c")) as (pos,item);

lateral view

先插入一些数据

insert into biao2 values(11,"语文 数学 地理");
insert into biao2 values(12,"语文 数学");
insert into biao2 values(13,"地理 政治");

对展开的数据进行在一列临时的列，抽取出单个的数据

select * from
(select id,split(name," ") as aa
 from biao2)t1
lateral view explode(t1.aa)tmp as bb 
;

窗口函数

窗口函数，能为每行数据划分一个窗口，然后对窗口范围内的数据进行计算，最后将计算结果返回给该行数据

窗口函数必须要有排序,over前面必须是运算函数

先创建表

create table order_info
(

    order_id string, --订单id

    user_id string, -- 用户id

    user_name string, -- 用户姓名

    order_date string, -- 下单日期

    order_amount int -- 订单金额

);

插入数据

insert overwrite table order_info

values ('1', '1001', '小元', '2022-01-01', '10'),

       ('2', '1002', '小海', '2022-01-02', '15'),

       ('3', '1001', '小元', '2022-02-03', '23'),

       ('4', '1002', '小海', '2022-01-04', '29'),

       ('5', '1001', '小元', '2022-01-05', '46'),

       ('6', '1001', '小元', '2022-04-06', '42'),

       ('7', '1002', '小海', '2022-01-07', '50'),

       ('8', '1001', '小元', '2022-01-08', '50'),

       ('9', '1003', '小辉', '2022-04-08', '62'),

       ('10', '1003', '小辉', '2022-04-09', '62'),

       ('11', '1004', '小猛', '2022-05-10', '12'),

       ('12', '1003', '小辉', '2022-04-11', '75'),

       ('13', '1004', '小猛', '2022-06-12', '80'),

       ('14', '1003', '小辉', '2022-04-13', '94'),

         ('15', '1001', '小元', '2022-01-05', '20');

统计每个用户截止每次下单的累积下单总额 

over 前面必须是运算的函数

partition by user_id 就是对用户进行分区，也就是把用户一样的分到一块

rows 按照一行一行的处理

between unbounded preceding and current row  就是从上一行到当前行

例如第一行的id为1的order_amount是10，当前行id为15的order_amount为20

那么10+20=30，当前行的总额就是30

select *
     ,sum(order_amount) over(partition by user_id order by order_date rows between unbounded preceding and current row ) as zonge
from order_info

;

rows between unbounded preceding and current row也可以省去，但是如果日期重复，那么计算的就是和下一行一样的数据 

统计每个用户截止每次下单的当月累积下单总额 

substring就是截取字符串，从0开始，截取到7的位置，就是截取年月

这里也把月份给分区了

select *
     ,sum(order_amount) over(partition by user_id,substring(order_date,0,7)

    order by order_date rows between unbounded preceding and current row ) as zonge
from order_info

;

 统计每个用户每次下单距离上次下单相隔的天数(首次下单按0天算)

nvl就是当数据为null的时候，变成0

datediff就是两个日期相减计算天数

lag获取上几行的数据，1就是上一行，默认值为null

select
    *,nvl(datediff(order_date,last_date),0) as aa from
    (
           select *,
                  lag(order_date,1,null) over (partition by user_id order by order_date) as last_date
           from order_info
           )t1


;

 查询所有下单记录以及每个用户的每个下单记录所在月份的首/末次下单日期

first_value 取第一个日期的值

last_value 取最后一个日期的值

rows between unbounded preceding and unbounded following 取排序后最后一个日期

select  *,
first_value(order_date) over (partition by user_id order by order_date) as aa,
last_value(order_date) over (partition by user_id order by order_date rows between unbounded preceding and
    unbounded following)as bb


from order_info

为每个用户的所有下单记录按照订单金额进行排名 

row_number和其他2个函数不一样，如果存在相同的金额，那么会显示继续累加1，不会和上面一样的排名

select  *,
rank() over (partition by user_id order by order_amount desc) as aa,
dense_rank() over (partition by user_id order by order_amount desc) as bb,
row_number() over (partition by user_id order by order_amount desc) as cc

from order_info

 

自定义函数

 pom引入

<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>3.1.3</version>
</dependency>

继承GenericUDF

package com.example.demoscala.controller;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

/**
 * 自定义函数 计算长度
 */
public class MyLeng extends GenericUDF {

    /**
     * 判断传过来的数据类型 和长度
     * 返回定义好的类型
     * @param objectInspectors
     * @return
     * @throws UDFArgumentException
     */
    @Override
    public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
        if(objectInspectors.length!=1){
            throw new UDFArgumentLengthException("长度不是1");
        }
        if(!objectInspectors[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){
            throw new UDFArgumentTypeException(1,"不是基本数据类型");
        }
        //返回Int类型
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }

    /**
        处理具体的业务逻辑
     * @param deferredObjects
     * @return
     * @throws HiveException
     */
    @Override
    public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
        Object o=deferredObjects[0].get();
        if(o==null){
            return 0;
        }
        //返回的是长度
        return o.toString().length();
    }

    /**
     * 用于获取解释的字符串 不做任何处理
     * @param strings
     * @return
     */
    @Override
    public String getDisplayString(String[] strings) {
        return null;
    }
}

然后打包 上传到hdfs

在hive中创建永久函数

create function my_len
 as "com.example.demoscala.controller.MyLeng"
 using jar "hdfs://192.168.10.102:8020/udf/demo-scala-0.0.1-SNAPSHOT.jar"; 

在hive中使用自定义函数

select my_len("aaaaaa");

在hive中删除自定义函数

drop function my_len;

 分区表

hive中的分区就是把一张大表的数据按照业务需要分散的存储到多个目录
每个目录就称为该表的一个分区
在查询的时候通过where 中的表达式 选择查询的分区，速度会快很多

创建分区表，以day字段作为分区

create table dept_partition
(
    deptno int, --部门编号
    dname string, --部门名称
    loc string --部门位置
)
partitioned by (day string)
row format delimited fields terminated by '\t';

 在102的机器上创建dept_20220401.log

vim dept_20220401.log

10    行政部    1700
20    财务部    1800

在hive中使用load的方式加载数据

load data local inpath '/opt/dept_20220401.log' into table dept_partition partition(day='20220401')

使用inser的方式添加数据 ,添加一个新的分区

insert overwrite table dept_partition partition (day = '20220402')
select deptno, dname, loc from dept_partition where day = '20220401';

可以看到，现在有2个区的数据，查询的时候直接通过where条件就可以过滤 

分区表基本命令

查看分区有那些

show partitions dept_partition;

增加分区 

alter table dept_partition add partition (day='20220403');

删除分区

alter table dept_partition drop partition (day='20220403');

分桶表 

首先为每行数据计算一个指定字段的数据的hash值，然后模拟一个指定的分桶数，最后将取模运算结果相同的行，写入同一个文件中，这个文件就称为一个分桶(bucket)

按照id进行分桶，分成4个桶

create table stu_buck(
   id int,
   name string
)
 clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';

在102机器上创建aa.txt,下面是内容

12    zhangsan
22    lisi
32    wangwu 

然后把aa.txt加载到hive中

load data local inpath '/opt/aa.txt' into table stu_buck;

当数据加载到表之后，hdfs对应的表下面的文件就有4个，也就是4个桶

分桶排序表

就是多了sorted by(id)，插入数据的时候，按照id进行排序