2.3.8 hadoop体系之离线计算-Hive数据仓库-Hive常见问题总结

「爱情、让人受尽委屈。」 2022-12-27 07:22 82阅读 0赞

**目录**

1. Hive架构原理

command-line shell & thrift/jdbc

Metastore

HQL的执行流程

2. 数据仓库与传统数据库比较

3. Hive与HBase的区别

Apache Hive™是数仓管理工具

Apache HBase™是Hadoop数据库，提供分布式、可扩展的大数据存储

4. Hive的数据模型

5. Hive支持的文件格式和压缩格式及各自特点

Hive支持行式存储和列式存储

优缺点

压缩格式

6. Hive当中四个By

7. Hive内外表的区分方法及差异

8. Hive视图如何创建、特点及应用场景

Hive支持两种类型视图

创建视图

应用场景

9. Hive常用命令及作用

10. Hive常用的系统函数及作用

聚合函数

排名函数

日期函数

字符串连接函数

其它

11. 简述UDF/UDAF/UDTF是什么，各自解决问题及应用场景

自定义UDF案例：

12. Hive设置SQL参数的方式

（1）shell脚本方式

（2）hivevar形式(以文件方式执行SQL)

（3）hiveconf形式

13. Hive动态分区以及分桶区别

--------------------

# 1. Hive架构原理 #

![8fecefe2f699b2b5710733139295f387.png][]

## command-line shell & thrift/jdbc ##

可以用 command-line shell 和 thrift／jdbc 两种方式来操作数据：

*  **command-line shell**：通过 hive 命令行的的方式来操作数据；
 *  **thrift／jdbc**：通过 thrift 协议按照标准的 JDBC 的方式操作数据。

## Metastore ##

在 Hive 中，表名、表结构、字段名、字段类型、表的分隔符等统一被称为元数据。所有的元数据默认存储在 Hive 内置的 derby 数据库中，但由于 derby 只能有一个实例，也就是说不能有多个命令行客户端同时访问，所以在实际生产环境中，通常使用 MySQL 代替 derby。

Hive 进行的是统一的元数据管理，就是说你在 Hive 上创建了一张表，然后在 presto／impala／sparksql 中都是可以直接使用的，它们会从 Metastore 中获取统一的元数据信息，同样的你在 presto／impala／sparksql 中创建一张表，在 Hive 中也可以直接使用。

## HQL的执行流程 ##

Hive 在执行一条 HQL 的时候，会经过以下步骤：

1.  语法解析：Antlr 定义 SQL 的语法规则，完成 SQL 词法，语法解析，将 SQL 转化为抽象 语法树 AST Tree；
2.  语义解析：遍历 AST Tree，抽象出查询的基本组成单元 QueryBlock；
3.  生成逻辑执行计划：遍历 QueryBlock，翻译为执行操作树 OperatorTree；
4.  优化逻辑执行计划：逻辑层优化器进行 OperatorTree 变换，合并不必要的 ReduceSinkOperator，减少 shuffle 数据量；
5.  生成物理执行计划：遍历 OperatorTree，翻译为 MapReduce 任务；
6.  优化物理执行计划：物理层优化器进行 MapReduce 任务的变换，生成最终的执行计划。

# 2. 数据仓库与传统数据库比较 #

![1b3b39b664dbbb48d9818ca0a4c0d517.png][]

# 3. Hive与HBase的区别 #

## **Apache Hive™是数仓管理工具** ##

*  通过构建元数据（MySQL）关联到HDFS上的数据
 *  通过数据库、表(分区表、分桶表、倾斜表)管理数据
 *  查询统计都是基于MapReduce引擎（默认）的
 *  支持行式、列式存储文件

## **Apache HBase™是Hadoop数据库，提供分布式、可扩展的大数据存储** ##

*  主要负责数据的实时存储和查询
 *  通过命名空间、表、行键、列族、列限定符、时间戳来组织数据
 *  支持数据的更新操作
 *  存储key/value形式的数据，并且以二进制字节数组方式将数据存储到HDFS
 *  通过Get和Scan(生产环境下一定记得带上过滤条件)命令查询数据，实现毫秒级响应
 *  依赖于Zookeeper进行构建，用于保存HBase元数据信息、选举Master

# 4. Hive的数据模型 #

Hive的数据模型主要有：Database、Table(Partition Table、Bucket Table)

**Database**：组织管理一系列的表，其表现形式为HDFS的目录  
**Table**：Hive中表的表现形式也为HDFS的目录，表信息包含列、HDFS存储位置等，表类型：内部表、外部表、临时表、视图表。

# 5. Hive支持的文件格式和压缩格式及各自特点 #

## **Hive支持行式存储和列式存储** ##

*  行式存储：在一块存储区域内，数据是以行为单位组织数据的。
 *  列式存储：在一块存储区域内，数据是以列为单位组织数据的。

## **优缺点** ##

*  行式存储：全列查询，尤其是针对应用型系统，不支持压缩、并且不支持列裁剪，不利于数据分析。文件格式：TextFile、SequenceFile。
 *  列式存储：支持列裁剪、减少数据查询范围，支持数据压缩，节省空间，一般应用于分析型系统。文件格式：ORC、PARQUET、RCFILE。

## **压缩格式** ##

![aae787c62955cec0340bf4f14f16adad.png][]

orc：支持NONE、ZLIB、 SNAPPY压缩，默认为ZLIB压缩方式  
parquet：支持LZO、SNAPPY压缩，默认为SNAPPY压缩方式

# 设置文件压缩方式
    tblproperties("文件类型.compress" ="压缩方式")
    # 案例
    create table orc_table_snappy(`loginid` string, `userid` string, `username` string, `rolename` string,`loginip` string,`loginsrc` string,`logintime` string) stored as orc tblproperties("orc.compress" ="SNAPPY");

# 6. Hive当中四个By #

*  Order By：全局排序，只有一个Reducer。
 *  Sort By：分区内有序，在多个MapReduce情况下，仅保证每个MapReduce输出数据有序，不保证全局有序。
 *  Distribute By：根据指定列进行分区(类似 MR 中Partition)，结合 Sort By 进行排序，必须写在Sort By 之前。
 *  Cluster By：集群排序，当Distribute by 和 Sorts by 字段相同时，可以使用 Cluster by 方式，但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

> 在生产环境中Order By 基本不用，容易导致OOM。  
> 在生产环境中Distribute By + Sort By用的多。

# 7. Hive内外表的区分方法及差异 #

内部表又叫做管理表 (Managed/Internal Table)，创建表时不做任何指定，默认创建的就是内部表。想要创建外部表 (External Table)，则需要使用 External 进行修饰。内部表和外部表主要区别如下：

# 内部表创建
    create (managed) table ods_user(name string, age int) row format delimited fields terminated by '\t';
    # 外部表创建
    create external table ods_user(name string, age int) row format delimited fields terminated by '\t' location '/user/xiaokang/user';

# 8. Hive视图如何创建、特点及应用场景 #

## Hive支持两种类型视图 ##

*  普通视图：是一个虚拟表，其本身不存储数据，数据是由查询语句定义的。
 *  物化视图：是一个特殊物理表，视图本身保存数据，其数据来源是根据原始表或者是远程表  
    查询而来，并且会定时更新数据。（Hive目前仅3.0及以上版本支持）

## 创建视图 ##

create view IF NOT EXISTS 视图名称 AS 查询语句
    # 案例
    create view v_login as select userid,count(1) from user_login group by userid;

特点：视图的本质就是通过一个名称索引一条查询语句，视图保存在元数据库中，保存的类型为 VIRTUAL\_VIEW。

## 应用场景 ##

*  保护数据：通过关联查询SQL语句有效的隔离原始数据。
 *  降低查询复杂度：将复杂查询分割成一个个小的查询
 *  屏蔽无用的列：可以通过SQL查询语句，只查询针和业务相关的列
 *  有效保证数据统一：可以通过视图将不同表中数据类型进行统一
 *  在离线多维分析Kylin中，最好将视图作为其数据源

# 9. Hive常用命令及作用 #

# 显示数据库列表
    show databases
    # 创建数据库
    create database if not exists xiaokang comment 'this is database for xiaokang' location 'hdfs://hadoop:9000/xiaokang' with dbproperties('creator'='xiaokang','create-time'='2020.06.30')
    # 查看数据库信息
    desc database [extended] 名称
    # 切换数据库
    use 数据库名称
    # 修改数据库信息
    alter database 数据库名称 set 属性 values
    alter database xiaokang set dbproperties ('create-time'='2019')
    # 删除数据库
    drop database if exists 名称 [RESTRICT|CASCADE]
    RESTRICT：删除空数据库，默认删除方式，不为空时报
    CASCADE：级联删除，删除数据库包括数据库中的所有内容
    # 创建表
    # 内部分区表创建
    create (managed) table ods_user(name string, age int) partitioned by(dt string) row format delimited fields terminated by '\t';
    # 外部分区表创建
    create external table ods_user(name string, age int) partitioned by(dt string) row format delimited fields terminated by '\t' location '/user/xiaokang/user';
    # 分区分桶表创建
    create table user_info_bucket(userId bigint,firstName string,lastName string) partitioned by(ds string) clustered by(userId) into 10 buckets row format delimited fields terminated by '\t';
    # 导入数据
    load data (local) inpath ‘数据存储位置’ into table ${tableName} partition(分区字段=值);
    load data local inpath ‘/home/xiaokang/user.txt’ into table ods_user partition(dt='20200630');
    load data inpath ‘/user/xiaokang/user’ into table ods_user partition(dt='20200630');
    # 清除分区数据
    truncate table ods_user partition(dt='20200630');
    # 查看分区表的分区
    show partitions ods_user;
    # 导出Hive表数据
    insert overwrite (local) directory 'hdfs://hadoop:9000/user/hive/warehouse/xiaokang.db/user' row format delimited fields terminated by ',' select * from ods_user;

# 10. Hive常用的系统函数及作用 #

## **聚合函数** ##

*  sum()—求和
 *  count()—求数据量
 *  avg()—求平均直
 *  distinct—求去重后数据量
 *  min—求最小值
 *  max—求最大值

## **排名函数** ##

*  ROW\_NUMBER()—对所有数值输出不同的序号，序号唯一连续（1，2，3）
 *  RANK()—对于相同数值，输出相同的序号，排序不会连续（1，1，3）
 *  DENSE\_RANK()—对于相同数值，输出相同的序号，排序连续（1，1，2）

## **日期函数** ##

*  year(date)—获取date字段的年，如2020
 *  quarter(date)—获取date字段的所属季度，如2
 *  date\_format(date,'y-MM')—将date字段的时间格式化成xx-xx格式，如2020-06
 *  date\_add(date,2)—当前日期基础上加2天
 *  date\_sub(date,2)—当前日期基础上减去2天
 *  next\_day(date,'MON')—获取当前日期的下一个星期一的日期
 *  last\_day(date)—获取当月最后一天日期

## **字符串连接函数** ##

*  concat()—连接字符串的时候，只要其中一个是NULL，那么将返回NULL
 *  concat\_ws()—连接字符串的时候，只要有一个字符串不是NULL，就不会返回NULL，此函数需要指定分隔符
 *  collect\_set(字段)—根据某个字段分组后，把分在一组的数据合并到一个数组，默认分隔符','

## **其它** ##

*  get\_json\_object(param1,"$.param2")—解析json函数（param1：需要解析的json字段
    
    param2：遇到数组就用 \[0,1,2…\] 0,1,2是数组对应的元素,遇到jsonObject直接用 ".key"取出想要获取的value）
 *  nvl(表达式 1，表达式 2)—如果表达式 1 为空值， NVL 返回值为表达式 2 的值，否则返回表达式 1 的值。

# 11. 简述UDF/UDAF/UDTF是什么，各自解决问题及应用场景 #

UDF(User-Defined Function) 操作作用于单个数据行，并且产生一个数据行作为输出。大多数函数都属于这一类（比如数学函数和字符串函数）。**返回对应值，一对一**

UDAF(User-Defined Aggregate Function) 接受多个输入数据行，并产生一个输出数据行。像COUNT和MAX这样的函数就是聚集函数。**返回聚类值，多对一**

UDTF(User-Defined Table-generating Function) 操作作用于单个数据行，并且产生多个数据行，一个表作为输出。**返回拆分值，一对多**

![4dcf6c873393d8c5eaeda1effbcdd9c2.png][]

## 自定义UDF案例： ##

package cool.xiaokang;
    
    import org.apache.hadoop.hive.ql.exec.UDF;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    /**
     * 根据用户总充值金额进行打标签
     * 1.继承UDF类
     * 2.重写evaluate
     * <p>
     * UDF: 一进一出函数
     */
    public class UserTag extends UDF {
        /**
         * @param moeny 总充值
         * @param tags  用户标签 充值金额:标签|充值金额:标签
         * @return
         *
         *  UserTag(chargeMoney,'100:大客户|200:超级用户')
         */
        public String evaluate(double moeny, String tags) {
    
            if (tags.length() > 0) {
                String[] split = tags.split("\\|");
                List<Tag> tagList = new ArrayList<>();
                for (String tagstr : split) {
                    tagList.add(getTag(tagstr));
                }
                String tagName = null;
                for (Tag tag : tagList) {
                    if (tag.moeny > moeny) {
                        tagName = tag.tagName;
                        break;
                    }
                }
                return tagName;
            } else {
                return "普通用户";
            }
        }
    
        public Tag getTag(String tagstr) {
            String[] split = tagstr.split(":");
            return new Tag(Double.valueOf(split[0]), split[1]);
        }
    }
    
    class Tag {
        double moeny;
        String tagName;
    
        public Tag(double moeny, String tagName) {
            this.moeny = moeny;
            this.tagName = tagName;
        }
    }

# 12. Hive设置SQL参数的方式 #

## （1）shell脚本方式 ##

date='2020-06-30'
    name='xiaokang'
    hive -e "select * from 表 where date=${date} and name=${name}"

## （2）hivevar形式(以文件方式执行SQL) ##

hive -f process.hql --hivevar userName=xiaokang

process.hql

select * from 表 where name=${hivevar:userName}

## （3）hiveconf形式 ##

hive -f process.hql --hiveconf userName=xiaokang

process.hql

select * from 表 where name=${hiveconf:userName}

# 13. Hive动态分区以及分桶区别 #

*  分区表现形式为HDFS的目录，分桶表现形式为HDFS的文件
 *  分区是按照列的值来进行分割的，容易造成数据倾斜，而分桶是按照列的哈希函数进行分割的，相对比较平均
 *  分区是对应不同的文件夹（粗粒度），分桶是对应不同的文件（细粒度），分桶比分区获得更高的查询处理效率，使取样更高效。

[8fecefe2f699b2b5710733139295f387.png]: /images/20221120/6c235bc1a1b946c582667e4cfe9efd5c.png
[1b3b39b664dbbb48d9818ca0a4c0d517.png]: /images/20221120/3c69377ddb574710aca8b14ab9c6165c.png
[aae787c62955cec0340bf4f14f16adad.png]: /images/20221120/23c5d0ddb03141d7b54abbaab5ec676b.png
[4dcf6c873393d8c5eaeda1effbcdd9c2.png]: /images/20221120/acc9e1f34751440287fb662f88dfdc31.png