Hive从本质到实战

淩亂°似流年 2023-10-04 17:05 5阅读 0赞

## Hive ##

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16]  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 1]  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 2]  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 3]  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 4]

### 本质 ###

#### 将 HQL 转化成 MapReduce 程序 ####

#### 本人理解是使用HQL去写MapReduce ####

#### Hive 处理的数据存储在 HDFS ####

#### Hive 分析数据底层的实现是 MapReduce ####

#### 执行程序运行在 Yarn 上 ####

### 属性配置 ###

#### 配置文件 ####

*  hive-site.xml：用户自定义配置会覆盖默认配置

#### 命令行参数 ####

*  在命令行添加-hiveconf param=value 来设定参数  
    仅对本次有效

#### 参数声明 ####

*  可以在 HQL 中使用 SET 关键字设定参数，仅对本次有效  
    set mapred.reduce.tasks=100;

### Hive的数据类型 ###

#### 基本数据类型 ####

*  TINYINT
 *  SMALLINT
 *  INT
 *  BIGINT
 *  DOUBLE
 *  FLOAT
 *  STRING
 *  BOOLEAN
 *  TIMESTAMP
 *  BIGANY

#### 集合数据类型 ####

*  STRUCT
    
     *  struct<street:string, city:string>
        
         *  “address”: \{  
            “street”: “hui long guan”,  
            “city”: “beijing”  
            \}
 *  MAP
    
     *  map<string, int>
 *  ARRAY
    
     *  array

#### 基于上述数据结构，我们在 Hive 里创建对应的表，并导入数据 ####

*  可以将数据写在txt文件中，MAP，STRUCT 和 ARRAY 里的元素间关系都可以用同一个字符表示，这里用“\_”。
    
     *  songsong,bingbing\_lili,xiao song:18\_xiaoxiao song:19,hui long  
        guan\_beijing  
        yangyang,caicai\_susu,xiao yang:18\_xiaoxiao yang:19,chao yang\_beijing
 *  创建Hive表的时候在语句最后添加  
    row format delimited fields terminated by ‘,’  
    collection items terminated by ‘\_’  
    map keys terminated by ‘:’  
    lines terminated by ‘\\n’;
    
     *  row format delimited fields terminated by ‘,’ ----> 列分隔符
     *  collection items terminated by ‘\_’ ---->MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
     *  map keys terminated by ‘:’ ---->MAP 中的 key 与 value 的分隔符
     *  lines terminated by ‘\\n’; ----> 行分隔符
 *  导入文本数据到测试表
    
     *  load data local inpath ‘/opt/module/hive/datas/test.txt’ into table test;
 *  访问三种集合列里的数据，以下分别是 ARRAY，MAP，STRUCT 的访问方式
    
     *  hive (default)> select friends\[1\],children\[‘xiao song’\],address.city from test

#### 类型转换 ####

*  隐式转换规则
    
     *  （1）任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成INT，INT 可以转换成 BIGINT。
     *  （2）所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。
     *  （3）TINYINT、SMALLINT、INT 都可以转换为 FLOAT。
     *  （4）BOOLEAN 类型不可以转换为任何其它的类型。
 *  可以使用 CAST 操作显示进行数据类型转换
    
     *  CAST(‘1’ AS INT)将把字符串’1’ 转换成整数 1

### DDL 数据定义 ###

#### 创建数据库 ####

*  CREATE DATABASE \[IF NOT EXISTS\] database\_name  
    \[COMMENT database\_comment\]  
    \[LOCATION hdfs\_path\]  
    \[WITH DBPROPERTIES (property\_name=property\_value, …)\];
 *  创建一个数据库，数据库在 HDFS 上的默认存储路径是/user/hive/warehouse/\*.db。

#### 查询数据库 ####

#### 修改数据库 ####

*  使用 ALTER DATABASE 命令为某个数据库的 DBPROPERTIES 设置键-值对属性值
 *  hive (default)> alter database db\_hive set dbproperties(‘createtime’=‘20170830’);

#### 删除数据库 ####

#### 创建表 ####

*  管理表(内部表)
    
     *  默认建立的表都是管理表，删除的时候会将原始数据删除
 *  外部表
    
     *  external，外部表删除之后，只会删除元数据，其原始数据在hdfs中不会被删除
 *  两表的相互转换
    
     *  管理表转换为外部表
        
         *  alter table student2 set tblproperties(‘EXTERNAL’=‘TRUE’);
     *  外部表转换为管理表
        
         *  alter table student2 set tblproperties(‘EXTERNAL’=‘FALSE’);

#### 修改表 ####

### DML 数据操作 ###

#### 向表中装载数据(Load) ####

*  hive> load data \[local\] inpath ‘数据的 path’ \[overwrite\] into table student \[partition (partcol1=val1,…)\];
 *  （1）load data:表示加载数据
 *  （2）local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表
 *  （3）inpath:表示加载数据的路径
 *  （4）overwrite:表示覆盖表中已有数据，否则表示追加
 *  （5）into table:表示加载到哪张表
 *  （6）student:表示具体的表
 *  （7）partition:表示上传到指定分区

#### 通过查询语句向表中插入数据（Insert） ####

*  hive (default)> insert overwrite table student\_par select id, name from student where month=‘201709’;
 *  insert into：以追加数据的方式插入到表或分区，原有数据不会删除
 *  insert overwrite：会覆盖表中已存在的数据
 *  注意：insert 不支持插入部分字段
 *  多表(多分区)插入模式
    
     *  hive (default)> from student  
        insert overwrite table student partition(month=‘201707’)  
        select id, name where month=‘201709’  
        insert overwrite table student partition(month=‘201706’)  
        select id, name where month=‘201709’;

#### 数据导出 ####

*  Insert 导出
 *  Hadoop 命令导出到本地
 *  Hive Shell 命令导出
 *  Export 导出到 HDFS 上
 *  Sqoop 导出
 *  清除表中数据（Truncate）
    
     *  Truncate 只能删除管理表，不能删除外部表中数据

### 查询 ###

#### select ####

#### from ####

#### where ####

#### like ####

#### between\\in\\isnot ####

#### and\\not\\or ####

#### having ####

#### group by ####

#### join on ####

#### order by ####

#### sort by ####

#### distribute by ####

#### cluster by ####

### 分区表和分桶表 ###

#### 分区表 ####

*  一级分区表
    
     *  partitioned by (day string)
 *  二级分区表
    
     *  partitioned by (day string, hour string)
     *  分区表与数据产生关联的三种方式
        
         *  上传数据后修复
         *  上传数据后添加分区
         *  创建文件夹后load数据到分区
 *  动态分区调整
    
     *  自动会根据分区字段的值，将数据插入到相应的分区中
     *  （1）开启动态分区功能（默认 true，开启）
        
         *  hive.exec.dynamic.partition=true
     *  （2）设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）
        
         *  hive.exec.dynamic.partition.mode=nonstrict
     *  （3）在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000
        
         *  hive.exec.max.dynamic.partitions=1000
     *  （4）在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。
        
         *  hive.exec.max.dynamic.partitions.pernode=100
     *  （5）整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000
        
         *  hive.exec.max.created.files=100000
     *  （6）当有空分区生成时，是否抛出异常。一般不需要设置。默认 false
        
            - hive.error.on.empty.partition=false
        
         *  举例：将 dept 表中的数据按照地区（loc 字段），插入到目标表 dept\_partition 的相应分区中
            
             *  hive (default)> insert into table dept\_partition\_dy partition(loc) select deptno, dname, loc from dept;

#### 分桶表 ####

*  分区针对的是数据的存储路径；分桶针对的是数据文件。
 *  create table stu\_buck(id int, name string)  
    clustered by(id)  
    into 4 buckets  
    row format delimited fields terminated by ‘\\t’;

#### 抽样查询 ####

### 函数 ###

#### 内置函数 ####

*  nvl
 *  窗口函数
    
     *  over
        
         *  Current row: 当前行
         *  n preceding：往前n行数据
         *  n following：往后n行数据
         *  unbounded preceding：表示从前面的起点
         *  unbounded following：表示从后面的起点
     *  lag(col,n,default\_value)
     *  lead(col,n,default\_value)
     *  ntile(n)
 *  Rank
    
     *  RANK() 排序相同时会重复，总数不会变
     *  DENSE\_RANK() 排序相同时会重复，总数会减少
     *  ROW\_NUMBER() 会根据顺序计算
 *  行转列
    
     *  CONCAT(string A/col, string B/col…)
        
         *  > SELECT concat(‘abc’, ‘def’) FROM src LIMIT 1;  
            > ‘abcdef’
     *  CONCAT\_WS(separator, str1, str2,…)
        
         *  > SELECT concat\_ws(’.’, ‘www’, array(‘facebook’, ‘com’)) FROM src LIMIT 1;  
            > ‘www.facebook.com’
     *  注意: CONCAT\_WS must be "string or array
     *  hive (default)> SELECT  
        t1.c\_b,  
        CONCAT\_WS("|",collect\_set(t1.name))  
        FROM (  
        SELECT  
        NAME ,  
        CONCAT\_WS(’,’,constellation,blood\_type) c\_b  
        FROM person\_info  
        )t1  
        GROUP BY t1.c\_b
 *  列转行
    
     *  LATERAL VIEW
        
         *  LATERAL VIEW udtf(expression) tableAlias AS columnAlias
     *  SPLIT(string str, string regex):
        
         *  按照regex字符串分割str，会返回分割后的字符串数组
         *  > SELECT split(‘oneAtwoBthreeC’, ‘\[ABC\]’) FROM src LIMIT 1;  
            > \[“one”, “two”, “three”\]
     *  EXPLODE(col)：
        
         *  将hive一列中复杂的array或者map结构拆分成多行

#### 自定义函数 ####

*  UDF（User-Defined-Function）–> 一进一出
    
     *  继承GenericUDF类
        
         *  initialize(ObjectInspector\[\] arguments)
         *  evaluate(DeferredObject\[\] arguments)
         *  getDisplayString(String\[\] children)
     *  打成jar包上传到服务器/opt/module/hive/datas/myudf.jar
     *  将jar包添加到hive的classpath
        
         *  hive (default)> add jar /opt/module/hive/datas/myudf.jar;
     *  创建临时函数与开发好的java class关联
        
         *  hive (default)> create temporary function my\_len as “com.gis.hive. MyStringLength”;
     *  即可在hql中使用自定义的函数
 *  UDAF（User-Defined Aggregation Function） --> 聚合函数，多进一出，类似：count/max/min
 *  UDTF（User-Defined Table-Generating Functions）–> 炸裂函数，一进多出，如：explode()
    
     *  继承GenericUDTF
        
         *  initialize(StructObjectInspector argOIs)
            
             *  初始化
         *  process(Object\[\] args)
            
             *  函数逻辑
         *  close()
     *  打成jar包上传到服务器/opt/module/hive/data/myudtf.jar
     *  将jar包添加到hive的classpath下
        
         *  hive (default)> add jar /opt/module/hive/data/myudtf.jar;
     *  创建临时函数与开发好的java class关联
        
         *  hive (default)> create temporary function myudtf as “com.atguigu.hive.MyUDTF”;
     *  使用自定义的函数

### 压缩与存储 ###

#### 压缩 ####

*  map端压缩
 *  reduce端压缩

#### 文件存储 ####

### 调优 ###

#### 执行计划Explain ####

*  explain select \* from emp;
 *  详细的：explain extended select \* from emp;

#### Fetch抓取 ####

*  Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算
 *  例如SELECT \* FROM employees;，是简单的读取输出，不走MapReduce
 *  在hive-default.xml.template文件中：  
    ·hive.fetch.task.conversion默认是more  
    ·老版本hive默认是minimal  
    该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。

#### 本地模式 ####

*  Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短
 *  用户可以通过设置hive.exec.mode.local.auto= true，来让Hive在适当的时候自动启动这个优化

#### 表的优化 ####

*  Group By
    
     *  是否在Map端进行聚合，默认为True
        
         *  hive(default)> set hive.map.aggr = true
     *  在Map端进行聚合操作的条目数目
        
         *  hive(default)> set hive.groupby.mapaggr.checkinterval = 100000
     *  有数据倾斜的时候进行负载均衡（默认是false）
        
         *  hive(default)> set hive.groupby.skewindata = true
 *  合理设置Map和Reduce
    
     *  小文件合并
 *  jvm重用

### 实战 ###

#### 视频观看量Top10 ####

*  select  
    videoId,  
    `views`  
    from gulivideo\_orc  
    order by `views` desc  
    limit 10;
    
     *  select videoId,sum(views) over(partition by videoId) from gulivideo\_orc

#### 统计视频类别热度Top10 ####

*  因为一个视频可能属于一个或多个类别，所以还要进行列转行
    
     *  hive(default)> select  
        tmp01.category\_col,  
        count(tmp01.videoId) num  
        from (  
        select  
        videoId,  
        category\_col  
        from gulivideo\_orc  
        lateral view  
        explode(category) t as category\_col  
        ) tmp01  
        group by tmp01.category\_col  
        order by num desc  
        limit 10;
 *  列转行
    
     *  LATERAL VIEW
        
         *  LATERAL VIEW udtf(expression) tableAlias AS columnAlias
     *  SPLIT(string str, string regex):
        
         *  按照regex字符串分割str，会返回分割后的字符串数组
     *  EXPLODE(col)：
        
         *  将hive一列中复杂的array或者map结构拆分成多行

#### 统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数 ####

*  hive(default)> select  
    table02.categroy\_name,  
    count(table02.videoId) num  
    from (  
    select  
    videoId,  
    categroy\_name  
    from (  
    select  
    videoId,  
    `views`,  
    category  
    from gulivideo\_orc  
    order by `views` desc  
    limit 20  
    ) table01  
    lateral view  
    explode(category) tmp as categroy\_name  
    ) table02  
    group by table02.categroy\_nam;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 5]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/fab562bc277544dbad1f6d57c52b27c6.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/6e70f7b9995c4e238fb1215dc91795de.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/95bee1795b134165a051fd542f7d00c8.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/472cfc06b01944d7932efc7673ecb916.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/d69b4c90d8f746f7b94930efb5c9fe50.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 5]: https://img-blog.csdnimg.cn/53d38dae28274c89ac79bf9e7e0ee1db.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16