Hive知识点总结

程序员文章站 2022-05-28 20:25:15

...

一、Hive概述

1.1 为什么学习Hive

Hadoop中的MapReduce有缺点

Mapper：输出，就是把键相同的合并起来；sql语句；group by；

Reduce：针对一个键相同的多个值，进行处理；聚合函数；order by；

需要编程。
渴望

针对开发人员，只需要写sql语句，递给一个xxx，xxx会把sql语句转换成MapReduce(xxx就是Hive)；

存储过程，就是把多条sql语句放到一个文件中，一块执行；顺序、选择、循环；要么使用底层的MR来写，通过jdbc一次发送多条sql语句(流程控制交给Java)

1.2 Hive的优点

hive是基于hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。

优点是学习成本低，可以通过sql语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。

特点：可扩展，延展性，容错性。

二、架构图（数据库，表，记录）

记录就是数据，为了描述这个记录，引入了数据库和表；数据库和表是为了描述记录的(记录：数据；而数据库和表：元数据)；

元数据是存储在关系型数据库(mysql，derby)，而记录是存储到hdfs上(因为表里面的记录可以存储的很多；数量无上限；而这个hadoop中的hdfs满足)。
MySQL(数据库，表，记录)

一张表就是一个ibd文件；记录也存储到ibd文件中；

缺点：如果随着mysql记录的增长，ibd文件会一直往上涨，硬盘的空间会使用完；

为什么不把多块硬盘连接起来：hdfs会把磁盘连接起来；

所有的程序都需要把数据存储到硬盘上(硬盘的空间总有一天会用完)，hdfs专门解决硬盘空间的问题。
Hive

Hive的驱动器：解释器，编译器，优化器

hive的客户端连接服务器走的是thrift协议，传输内容比较小。

三、类型

名字	MySQL	Hive
整数	tinyint，int，bigint，mediumint，numeric	numeric，tinyint
浮点	double，decimal	decimal
字符串	varchar，text，char	string
布尔	无
时间	date，time，datetime，timestamp	date/time
Json	Json
Geom	几何图形(周边搜索)(地理系统，地图)
二进制	图片	boolean，binary
复杂类型	Json	arrays，maps，structs(类)，union

四、Hive的三种模式

4.1 本地模式（在node7-4上）

先启动Hadoop，zookeeper
解压，重命名
修改配置文件（n conf/hive-env.sh.template复制为conf/hive-env.sh）
初始化之前删除掉之前的数据库

rm -rf derby.log metastore_db/

bin/schematool -dbType derby -initSchema

4.2 单用户模式

记录放到hdfs中，hive表和数据库放到MySQL中

修改配置文件（复制conf/hive-default.xml.template到hive-site.xml）

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
 <configuration>
     <!-- hive数据仓库存储的位置；hadoop中hdfs的位置 -->
     <property>
         <name>hive.metastore.warehouse.dir</name>
         <value>/data/hive/data/</value>
     </property>
     <!-- 本地单用户启动 -->
     <property>
         <name>hive.metastore.local</name>
         <value>true</value>
     </property>
     <!-- 
     hive中数据库相关的逻辑信息存储到mysql中
     hive:文件(hive相关的文件);存储到hdfs中
     hive:逻辑的概念:存储到了数据库中

     jdbc相关的信息
    -->
     <property>
         <name>javax.jdo.option.ConnectionURL</name>
         <value>jdbc:mysql://192.168.56.1/hive-demo?useSSL=false&amp;serverTimezone=GMT%2B8&amp;allowMultiQueries=true&amp;allowPublicKeyRetrieval=true</value>
     </property>
     <property>
         <name>javax.jdo.option.ConnectionDriverName</name>
         <value>com.mysql.cj.jdbc.Driver</value>
     </property>
     <property>
         <name>javax.jdo.option.ConnectionUserName</name>
         <value>root</value>
     </property>
     <property>
         <name>javax.jdo.option.ConnectionPassword</name>
         <value>123456</value>
     </property>
 </configuration>

初始化

bin/schematool -dbType mysql -initSchema

注：将mysql-connector-java…jar包上传到hive/lib中；将hadoop中guava-27.0-jre.jar替换到hive/lib中
启动服务器

bin/hive --service metastore

后台启动

nohup bin/hive --service metastore &
客户端启动

bin/hive
基础操作（HQL：和SQL语法相似）
- 查看数据库：show databases;
- 创建数据库：create database mydata;
- 打开数据库：use default(默认的);
- 显示tables；show tables;
- 创建表：create table student(id int, name string);
- 插入记录：insert into student(id, name) values (1, 'aa');
- 查看表结构：desc student;
- 描述表：describe formatted student;
- 退出：quit;

4.3 多用户模式

	node7-2	node7-3	node7-4
服务器端			√
客户端	√	√

服务器端（配置文件：conf/hive-site.xml）

 <?xml version="1.0"?>
 <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
 <configuration>
     <!-- hive数据仓库存储的位置;
     hadoop中hdfs的位置
     -->
     <property>
         <name>hive.metastore.warehouse.dir</name>
         <value>/data/hive/data/</value>
     </property>
     <!-- 本地单用户启动 -->
     <property>
         <name>hive.metastore.local</name>
         <value>true</value>
     </property>
     <!-- 
     hive中数据库相关的逻辑信息存储到mysql中
     hive:文件(hive相关的文件);存储到hdfs中
     hive:逻辑的概念:存储到了数据库中

     jdbc相关的信息
     -->
     <property>
         <name>javax.jdo.option.ConnectionURL</name>
         <value>jdbc:mysql://192.168.56.1/hive-demo?useSSL=false&amp;serverTimezone=GMT%2B8&amp;allowMultiQueries=true&amp;allowPublicKeyRetrieval=true</value>
     </property>
     <property>
         <name>javax.jdo.option.ConnectionDriverName</name>
         <value>com.mysql.cj.jdbc.Driver</value>
     </property>
     <property>
         <name>javax.jdo.option.ConnectionUserName</name>
         <value>root</value>
     </property>
     <property>
         <name>javax.jdo.option.ConnectionPassword</name>
         <value>123456</value>
     </property>
     <!-- HiveServer2 Web UI
     0.0.0.0任何客户端都可以链接
     -->
     <property>
         <name>hive.server2.webui.host</name>
         <value>0.0.0.0</value>
     </property>
     <!-- 监听的端口号 -->
     <property>
         <name>hive.server2.webui.port</name>
         <value>10001</value>
     </property>
 </configuration>

客户端（配置文件：conf/hive-site.xml）

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <!-- 数据仓库的存储目录:hdfs上的 -->
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/data/hive/data/warehouse</value>
    </property>
    <!-- metastore:不是;false  -->
    <property>
        <name>hive.metastore.local</name>
        <value>false</value>
    </property>
    <!-- 服务器的IP
    thrift:---http---https;
    客户端与服务器交互的协议;
    -->
    <property>
        <name>hive.metastore.uris</name>
        <value>thrift://node7-4:9083</value>
    </property>
</configuration>

启动服务端(node7-4上)

nohup bin/hive --service metastore &
启动客户端(node7-2，node7-3上)

bin/hive

五、常见命令

5.1 创建表（带格式，内部表和外部表）

create external table psn_1
(
id int, 
name string,
age smallint,
scroe double,
createTime timestamp
)
-- 记录行的分隔符
row format delimited
-- 列的分隔符
fields terminated by ',' 
-- 存储文件的格式；textfile是默认的，写与不写都是一样的
stored as textfile ;

创建一个文件（文件名随便起），如data.txt

1,aa,20,80,2019-01-17 15:45:00
2,bb,21,90,2019-01-17 15:45:00
3,cc,24,70,2019-01-18 15:45:00
4,dd,20,82,2019-01-10 15:45:00
5,ee,22,81,2019-01-20 15:45:00
6,gg,29,80,2019-01-11 15:45:00
-- 要注意记录的顺序要与列的顺序一一对应
-- 指定的列的分隔符是','，所以就要用','分开

将data.txt上传到hdfs上

load data inpath '/data/hive/data/data.txt' into table psn_1;

注：load data只适用于表的文件格式是textFile；local不指定就是hdfs，指定就是本地电脑上的路径

内部表和外部表的区别？

创建外部表时要加关键字external；

内部表：删除hive表时，hive对应的hdfs的目录被删除，元数据中的数据也会被删除；

外部表：删除hive表时，hive对应的hdfs的目录不会被删除，元数据中的数据会删除。

5.2 配置事务

事务的四个特性：原子性，一致性，隔离性，持久性

修改服务端配置文件（hive-site.xml）

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <!-- hive数据仓库存储的位置;
    hadoop中hdfs的位置
    -->
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/data/hive/data/</value>
    </property>
    <!-- 本地单用户启动 -->
    <property>
        <name>hive.metastore.local</name>
        <value>true</value>
    </property>
    <!-- 
    hive中数据库相关的逻辑信息存储到mysql中
    hive:文件(hive相关的文件);存储到hdfs中
    hive:逻辑的概念:存储到了数据库中

    jdbc相关的信息
    -->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://192.168.56.1/hive-demo?useSSL=false&amp;serverTimezone=GMT%2B8&amp;allowMultiQueries=true&amp;allowPublicKeyRetrieval=true</value>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.cj.jdbc.Driver</value>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>123456</value>
    </property>
    <!-- HiveServer2 Web UI
    0.0.0.0任何客户端都可以链接
    -->
    <property>
        <name>hive.server2.webui.host</name>
        <value>0.0.0.0</value>
    </property>
    <!-- 监听的端口号 -->
    <property>
        <name>hive.server2.webui.port</name>
        <value>10001</value>
    </property>

    <!-- 配置事务 -->
    <property>
        <name>hive.compactor.initiator.on</name>
        <value>true</value>
    </property>
    <property>
        <name>hive.compactor.worker.threads</name>
        <value>2</value>
    </property>
</configuration>

修改客户端配置文件（hive-site.xml）

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <!--  数据仓库的存储目录:hdfs上的 -->
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/data/hive/data/warehouse</value>
    </property>
    <!-- metastore:不是;false  -->
    <property>
        <name>hive.metastore.local</name>
        <value>false</value>
    </property>
    <!-- 服务器的IP
    thrift:---http---https;
    客户端与服务器交互的协议;
    -->
    <property>
        <name>hive.metastore.uris</name>
        <value>thrift://node7-4:9083</value>
    </property>

    <!-- 事务配置 -->
    <property>
        <name>hive.support.concurrency</name>
        <value>true</value>
    </property>
    <property>
        <name>hive.enforce.bucketing</name>
        <value>true</value>
    </property>
    <property>
        <name>hive.exec.dynamic.partition.mode</name>
        <value>nonstrict</value>
    </property>
    <property>
        <name>hive.txn.manager</name>
        <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
    </property>
</configuration>

创建表（带事务）

create table psn_2 
(
id int, 
name string,
age smallint,
scroe double,
createTime string
)
-- 事务必须要求的,负载均衡;记录比较多,要分开;分成不同的表存储,指定分表的规则就是(id)
clustered by (id)
-- 桶
into 2 buckets 
-- 记录行的分隔符
row format delimited
-- 列的分隔符
fields terminated by ',' 
-- 存储文件的格式；textfile是默认的，写与不写都是一样的
stored as orc
-- 表的属性
tblproperties ("transactional"="true");

5.3 创建表 – as，like

创建表的类型默认是内部表，格式默认是textFile；

使用like创建表时，目标表和原表的结构一样，没有数据。

create table psn_3 as select id,name from psn_1;

create table psn_3 like psn_1;

5.4 Truncate

截断表：团灭；表里面的记录全部干掉，直接删除文件；

truncate table psn_3;

delete：相当于删除文件中的某个内容；

delete from psn_3

5.5 创建类型复杂表

类型	解释
data_type	时间类型
primitive_type	原始类型，整数，小数，字符串
array_type	数组类型
map_type	集合类型，键值对
struct_type	结构类型，Java中的类，这里面没有方法
union_type	联合复杂，复杂类型

create table psn_3 
(
id int, 
name string,
age smallint,
scroe double,
-- 地址;容器,泛型
address array<string>,
-- map爱好;容器,泛型
hobby map<string,string>,
createTime string
)
-- 记录行的分隔符
row format delimited
-- 列的分隔符
fields terminated by ',' 
-- 数组的拆分
collection items terminated by '-'
-- map
map keys terminated by ':'
-- 存储文件的格式;textfile是默认的,写与不写都是一样的
stored as textfile ;

数组的拆分和map的拆分，并没有指定是哪一列，是所有的数组都是用‘-’拆分，所有的map都是用‘:’拆分；

map：键值对，使用‘:’隔开，多个键值对使用集合‘-’。

5.6 分区（partition by）

create table psn_4
(
id int , 
name string,
age smallint,
scroe double,
-- 地址;容器,泛型
address array<string>,
-- map爱好;容器,泛型
hobby map<string, string>,
createTime string
)
-- 分区,分区的规则,指定列进行分区,分区的列不允许出现在(小括号)中;(分区可以有多个)
partitioned by (sex string,adress string)
-- 记录行的分隔符
row format delimited
-- 列的分隔符
fields terminated by ',' 
-- 数组的拆分
collection items terminated by '-'
-- map
map keys terminated by ':'
-- 存储文件的格式;textfile是默认的,写与不写都是一样的
stored as textfile;

导入数据（分区不支持中文）

load data inpath '文件路径' into table psn_4 partition (sex='boy',adress='zhengzhou');

5.7 导入和导出

hive需要往hdfs中写数据，要先配置hadoop中的配置文件（core-site.xml）(追加内容)

<property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
</property>
<property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
</property>

导出：export table psn_4 to '/data/psn_4';

导入：import table psn_4 from '/data/psn_4';

5.8 查询

union 联合查询

将两个sql语句执行的结果联合起来；

union all：联合查询（有重复）

union distinct：联合查询（去重）

子查询

先查询一条查询语句（父查询）；

再执行一条查询语句（需要依赖于夫查询先执行的查询语句）；

select * from a_king where dynastyId in (1,4,5)
-- 皇上表中的朝代id和朝代表中的id一样;
select * from a_king where dynastyId in (select id from a_dynasty where age > 200); 
-- 临时表的表名叫t;用小括号将任意一个sql语句给括起来;
select * from (select ad.id adid ,ad.name adname,ak.id akid,ak.name akname from a_king ak, a_dynasty ad where ad.id = ak.dynastyId) t 
where adid > 3;

窗口函数

将第一条sql语句执行的结果，统一用一个函数操作；

查询的结果，要分组（partition）

lead：往下(后)走；当前行的值，请填写下一行；

lag：往上(前)走；默认往前走一个，数字可以指定。

select *,lag(name, 2) over (partition by dynastyId) from a_king;

over：条件；拿着当前执行的sql语句执行结果拍一张照片，再这些结果上进行处理。
分析函数（row_number() over(partition by)）

每一个朝代，只列出前两个皇上(topn)

select * from (select *, row_number() over (partition by dynastyId) as bh from a_king) t where bh < 3

select *, row_number() over (partition by dynastyId order by id desc) as bh from a_king

六、特殊查询

Grouping sets

Cubes（立方体）

列出所有的组合情况

GROUP BY a, b, c WITH CUBE

GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( ))

Group by (a,b,c)
Union
Group by (a,b)
Union 
Group by (b,c)
Union 
Group by (a,c)
Union 
Group by (a)
Union 
Group by (b)
Union 
Group by (c)
Union 
Group by ()

Rollups（筒）

数据依次少一个

GROUP BY a, b, c with ROLLUP

GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( ))

Group by (a, b, c)
Union
Group by (a, b)
Union
Group by (a)
Union
Group by ()

Grouping_ID：其实就是所统计各列二进制和

select dynastyId,status,count(*),grouping_id from a_king group by dynastyId,status grouping sets((dynastyId,status));

对于每一列，如果该列已在该行中聚合，则为结果集中的一行生成“1”值，否则值为“0”；

当没有统计某一列时，它的值显示为null，这可能与列本身就有null值冲突，这就需要一种方法区分是没有统计还是值本来就是null；

假设：表里有a，b，c三列，在写sql语句统计时，只统计了a，b；group by a,b；c这一列没有管，在hive中c这列的值为null

问题：c本身就是null值（hive统计时变成null值，不亏，是对的）；c原来有值，直接就变成null值，相当于把原来的数据给覆盖掉。
Grouping

分组函数指示Group by子句中的表达式是否聚合到给定行；

“0”：属于分组集的列；

“1”：不属于分组集的列。

七、Beeline

让开发人员可以用Java程序连接hive(jdbc)

启动客户端：nohup bin/hiveserver2 &

启动客户端：bin/beeline

连接客户端：!connect jdbc:hive2://node7-4:10000/mydata root 123456

注：导入jdbc的jar包

import java.sql.*;

/**
 * jdbc连接hive
 */
public class JdbcMain {
    public static void main(String[] args) {
        /**
         * jdbc四步走：
         * 加载驱动
         * 获取连接
         * 操作数据库
         * 关闭连接
         */
        String driver = "org.apache.hive.jdbc.HiveDriver";
        String url = "jdbc:hive2://node7-4:10000/mydata";
        String user = "root";
        String password = "123456";
        // 查询所有记录
        String sql = "select * from a_king";
    
        Connection connection = null;
        // 预处理的sql语句，占位符?
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // 加载驱动
            Class.forName(driver);
            // 获取连接
            connection = DriverManager.getConnection(url, user, password);
            // 操作数据库
            ps = connection.prepareStatement(sql);
            // 执行查询
            rs = ps.executeQuery();
            // 查询多条记录使用while，单条记录if
            while (rs.next()) {
                int id = rs.getInt("id");
                int dynastayId = rs.getInt("dynastayId");
                String name = rs.getString("name");
                Timestamp createTime = rs.getTimestamp("createTime");
                System.out.println(id + "\t" + dynastayId + "\t" + name + "\t" + createTime.toLocaleString());
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

八、UDF、UDTF、UDAF

UDF(User Defined Function)：用户自定义函数

UDTF(User Defined Aggregate Functions)：聚合函数；输入的数据是多条，输出的数据是一条

UDAF(User Defined Table-Generating Functions)：表格生成；输入的数据是一条，输出的数据是多条

九、其他

运算符

字符串相加：+，concat，stringBuffer(stringBuilder)

||：相当于java中字符串相加使用的“+”；在mysql中不能使用，在hive中可以使用
时间戳

select current_timestamp();
explode：爆炸

select explode (array('A','B','C'));
命令

-e：先启动hive客户端，执行sql语句，之后退出hive客户端；

bin/hive -e 'select * from mydata.a_king' > /log.txt

-f：执行一个sql文件（.sql文件中有多个sql语句）；

bin/hive -f '/root/demo.sql'

-S：不把sql语句执行的结果输出出来；

bin/hive -S 'select * from mydata.a_king' > /log.txt

Hive知识点总结

一、Hive概述

1.1 为什么学习Hive

1.2 Hive的优点

二、架构图（数据库，表，记录）

三、类型

四、Hive的三种模式

4.1 本地模式（在node7-4上）

4.2 单用户模式

4.3 多用户模式

五、常见命令

5.1 创建表（带格式，内部表和外部表）

5.2 配置事务

5.3 创建表 – as，like

5.4 Truncate

5.5 创建类型复杂表

5.6 分区（partition by）

5.7 导入和导出

5.8 查询

六、特殊查询

七、Beeline

八、UDF、UDTF、UDAF

九、其他

PHP 中文乱码解决办法总结分析

2019支付宝年度账单哪里看查询2019年支付宝账单总结图文教程

oracle触发器使用总结

详解Javascript几种跨域方式总结

Android 软键盘出现不适应的解决办法总结

python自测知识点和书籍补充（C4 文本和字节序列）

Android WebView或手机浏览器打开连接问题解决办法总结

vue-manage-system 后台管理系统开发总结

【Java基础】String 相关知识点总结

javascript异步编程的六种方式总结

Hive知识点总结

一、Hive概述

1.1 为什么学习Hive

1.2 Hive的优点

二、架构图（数据库，表，记录）

三、类型

四、Hive的三种模式

4.1 本地模式（在node7-4上）

4.2 单用户模式

4.3 多用户模式

五、常见命令

5.1 创建表（带格式，内部表和外部表）

5.2 配置事务

5.3 创建表 – as，like

5.4 Truncate

5.5 创建类型复杂表

5.6 分区（partition by）

5.7 导入和导出

5.8 查询

六、特殊查询

七、Beeline

八、UDF、UDTF、UDAF

九、其他

PHP 中文乱码解决办法总结分析

2019支付宝年度账单哪里看 查询2019年支付宝账单总结图文教程

oracle触发器使用总结

详解Javascript几种跨域方式总结

Android 软键盘出现不适应的解决办法总结

python自测知识点和书籍补充（C4 文本和字节序列）

Android WebView或手机浏览器打开连接问题解决办法总结

vue-manage-system 后台管理系统开发总结

【Java基础】String 相关知识点总结

javascript异步编程的六种方式总结

2019支付宝年度账单哪里看查询2019年支付宝账单总结图文教程