【Hive学习笔记】

以你之姓@ 2022-10-06 15:52 199阅读 0赞

### 文章目录 ###

*  1.基础知识
 *   *  1.1概念
     *  1.2架构原理
     *  1.3Hive和数据库比较
 *  2.基本操作
 *   *  2.1常用交互命令
     *  2.2其他命令操作
 *  3.数据类型
 *   *  3.1基本数据类型
     *  3.2集合数据类型
     *  3.3类型转化
 *  4.DDL数据定义
 *   *  4.1CRUD操作
     *  4.2表
     *   *  4.2.1建表
         *  4.2.2表的分类
 *  5.DML数据操作
 *  6.查询
 *   *  6.1基本查询
     *  6.2函数查询
     *   *  6.2.1空字段赋值
         *  6.2.2if & case when
         *  6.2.3行转列
         *  6.2.4列转行
 *  7.常用
 *   *  7.1with as
     *  7.2union
     *  7.3merge into
     *  7.4json数据获取
 *  8.优化
 *   *  8.1空key过滤或转换
     *  8.2去重
     *  8.3行列过滤
     *  8.4并行执行
     *  8.5严格模式
 *  9.其他

由于工作需求，学习了 [尚硅谷Hive教程][Hive]1-44集，并根据课程内容整理以下笔记

# 1.基础知识 #

## 1.1概念 ##

Hive：由Facebook开源用于解决海量结构化日志的数据统计。
    Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。
    本质是：将HQL转化成MapReduce程序

## 1.2架构原理 ##

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

## 1.3Hive和数据库比较 ##

由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language)，因此很容易将 Hive 理解为数据库。
    其实从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处。

# 2.基本操作 #

## 2.1常用交互命令 ##

hive -e "sql语句"	# 不进入hive交互窗口执行sql语句
    hive -f xxx.sql		# 不进入hive交互窗口执行脚本中的sql语句

## 2.2其他命令操作 ##

hive>exit;	# 退出hive窗口
    hive>quit;	# 退出hive窗口
    hive>dfs -ls /;	# 查看hdfs文件系统
    hive>! ls /opt/module/datas;	# 查看本地文件系统
    cat .hivehistory	# 查看在hive中输入的所有历史命令

# 3.数据类型 #

## 3.1基本数据类型 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2tlaXZlbl8_size_16_color_FFFFFF_t_70 1]

## 3.2集合数据类型 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2tlaXZlbl8_size_16_color_FFFFFF_t_70 2]

## 3.3类型转化 ##

Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作。

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

# 4.DDL数据定义 #

## 4.1CRUD操作 ##

create database db1 if not exists;
    show databases;
    desc database db1;	# 显示数据库信息
    use db1;
    alter database db1 set dbproperties('createtime'='20170830');	# 修改数据库
    drop database db1 if exists db1;	# 删除空数据库
    drop database db1 cascade;	# 强制删除非空数据库

## 4.2表 ##

### 4.2.1建表 ###

# 语法
    CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
    [(col_name data_type [COMMENT col_comment], ...)] 
    [COMMENT table_comment] 
    [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
    [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
    [ROW FORMAT row_format] 
    [STORED AS file_format] 
    [LOCATION hdfs_path]
    
    # 实例
    create table if not exists student2(
    id int, name string
    )
    row format delimited fields terminated by '\t'
    stored as textfile
    location '/user/hive/warehouse/student2';

*  CREATE TABLE 创建一个指定名字的表
 *  COMMENT：为表和列添加注释
 *  PARTITIONED BY创建分区表
 *  CLUSTERED BY创建分桶表
 *  STORED AS指定存储文件类型
 *  LOCATION ：指定表在HDFS上的存储位置
 *  LIKE允许用户复制现有的表结构，但是不复制数据

表增加一列

alter table tablename add columns(columnname string);

### 4.2.2表的分类 ###

1.  管理表  
    默认创建的表都是管理表，有时也被称为内部表。Hive默认情况下会将这些表的数据存储在由配置项所定义的目录的子目录下。 当删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。
2.  外部表 EXTERNAL  
    删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。
3.  分区表  
    分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

# 创建分区表
    create table dept_partition(
    deptno int, dname string, loc string
    )
    partitioned by (month string)
    row format delimited fields terminated by '\t';
    
    # 单分区查询
    select * from dept_partition where month='201709';
    # 多分区联合查询
    select * from dept_partition where month='201709'
      union
    select * from dept_partition where month='201707';
    
    # 创建单个分区
    alter table dept_partition add partition(month='201706') ;
    # 同时创建多个分区
    alter table dept_partition add partition(month='201705') partition(month='201704');
    
    # 删除单个分区
    alter table dept_partition drop partition (month='201704');
    # 同时删除多个分区
    alter table dept_partition drop partition (month='201705'), partition (month='201706');
    
    # 查看分区表有多少分区
    show partitions dept_partition;
    
    # 查看分区表结构
    desc formatted dept_partition;
    
    # 查询表结构
    desc dept_partition;
    
    # 添加列
    alter table dept_partition add columns(deptdesc string);
    
    # 替换列
    alter table dept_partition replace columns(deptno string, dname string, loc string);
    
    # 删除表
    drop table dept_partition;

**注**：alter只修改元数据，不修改实际数据

# 5.DML数据操作 #

load data [local] inpath '/opt/module/datas/student.txt' overwrite | into table student [partition (partcol1=val1,…)];

*  load data:表示加载数据
 *  local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
 *  inpath:表示加载数据的路径
 *  overwrite:表示覆盖表中已有数据，否则表示追加
 *  into table:表示加载到哪张表
 *  student:表示具体的表
 *  partition:表示上传到指定分区

# 6.查询 #

## 6.1基本查询 ##

方法同Mysql语法

*  全局排序（Order By）
 *  每个MapReduce内部排序（Sort By）
 *  分区排序（Distribute By）
 *  Cluster By（当distribute by和sorts by字段相同时可以使用cluster by）

## 6.2函数查询 ##

### 6.2.1空字段赋值 ###

*  NVL：给值为NULL的数据赋值 `NVL( str1, replace_with)`
 *  说明：若str1为NULL，则NVL函数返回replace\_with的值，否则返回str1的值，若两个参数都为NULL ，则返回NULL
 *  eg: `select nvl(comm,-1) from emp;`

### 6.2.2if & case when ###

处理单个列的查询结果

1.  if  
    语法: `if(boolean testCondition, T valueTrue, T valueFalseOrNull)`  
    返回值: T  
    说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull  
    举例：`select IF(order_id IS NULL, 0, 1) AS is_transform from user`
2.  case when

case when 条件 then 值
    when 条件 then 值
    .........
    when 条件 then 值 else 0 end 字段名

### 6.2.3行转列 ###

*  `CONCAT(string A/col, string B/col…)`：返回输入字符串连接后的结果，支持任意个输入字符串;
 *  `CONCAT_WS(separator, str1, str2,...)`：特殊形式的 `CONCAT()`。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间
 *  `COLLECT_SET(col)`：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段

### 6.2.4列转行 ###

*  `EXPLODE(col)`：将hive一列中复杂的array或者map结构拆分成多行
 *  `LATERAL VIEW udtf(expression) tableAlias AS columnAlias`：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合

# 7.常用 #

## 7.1with as ##

*  说明：相当于把重复用到的sql语句放在with as里，取一个别名，后面的查询就可以用它，对于大批量sql语句起到优化作用，清楚明了。

-- 相当于建了e、d临时表
    with
         e as (select * from scott.emp),
         d as (select * from scott.dept)
    select * from e, d where e.deptno = d.deptno;

*  优点:

1.  SQL可读性增强。比如对于特定with子查询取个有意义的名字等
2.  with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能

## 7.2union ##

1.  union all ：将多个select语句结果合并为一个，且结果不去重不排序
2.  union distinct（通常省略distinct写为union）：将多个select语句结果合并为一个，且结果去重且排序
3.  eg：`select id,score from union_test1 union select id,score from union_test2;`

## 7.3merge into ##

MERGE INTO table_name alias1 
    USING (table|view|sub_query) alias2
    ON (join condition) 
    WHEN MATCHED THEN 
        UPDATE  
        SET col1 = col1_val1, 
            col2 = col2_val2 
    WHEN NOT MATCHED THEN 
        INSERT (column_list) VALUES (column_values);

## 7.4json数据获取 ##

使用`get_json_object`获取json中的数据，例如表test中有一列jsonData数据如下：

jsonData = '{ "ID":1, "message":{ "name":"Asher", "location":[{"county":"浦东","city":"上海"}, {"county":"西直门","city":"北京"}] } }'
    --jsonData列数据为字符串格式，外面是单引号，里面是双引号

select get_json_object(jsonData,'$.ID') from test
    --输出：1
    
    select get_json_object(jsonData,'$.message.location[0].city') from test
    --输出：上海

# 8.优化 #

*  设置5个reduce个数 `set mapreduce.job.reduces = 5;`
 *  尽量避免笛卡尔积，join的时候不加on条件，或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积

## 8.1空key过滤或转换 ##

空key过滤

select n.* from (select * from nullidtable where id is not null ) n  left join ori o on n.id = o.id;

空key转换

select n.* from nullidtable n full join ori o on 
    case when n.id is null then concat('hive', rand()) else n.id end = o.id;

## 8.2去重 ##

*  distinct去重 `select count(distinct id) from bigtable;`
 *  group by去重 `select count(id) from (select id from bigtable group by id) a;`

## 8.3行列过滤 ##

*  列处理：在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT \*。
 *  行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤
 *  如通过子查询后，再关联表 `select b.id from bigtable b join (select id from ori where id <= 10 ) o on b.id = o.id;`

## 8.4并行执行 ##

> Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段。或者Hive执行过程中可能需要的其他阶段。默认情况下，Hive一次只会执行一个阶段。某个特定的job可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个job的执行时间缩短。如果有更多的阶段可以并行执行，那么job可能就越快完成。  
> 在共享集群中，如果job中并行阶段增多，那么集群利用率就会增加。  
> 当然，得是在系统资源比较空闲的时候才有优势，否则没资源，并行也起不来

set hive.exec.parallel=true;              //打开任务并行执行
    set hive.exec.parallel.thread.number=16;  //同一个sql允许最大并行度，默认为8。

## 8.5严格模式 ##

Hive提供了一个严格模式，可以防止用户执行那些可能意想不到的不好的影响的查询。

1.  **对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行**。就是用户不允许扫描所有分区。原因是通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。
2.  对于**使用了order by语句的查询，要求必须使用limit语句**。因为order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。
3.  **限制笛卡尔积的查询**。对关系型数据库非常了解的用户可能期望在执行JOIN查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是，Hive并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况。

# 9.其他 #

**可参考博客：**

> [轻松玩转hive中各种join之间的关系以及使用][hive_join]  
> [SQL中merge into用法][SQL_merge into]  
> [Hive入门及常用指令][Hive 1]  
> [数据仓库中的拉链表（hive实现)][hive]  
> [谓词下推][Link 1]

[Hive]: https://www.bilibili.com/video/BV1W4411B7cN
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2tlaXZlbl8_size_16_color_FFFFFF_t_70]: /images/20221005/5f2df7868502433093048d2b653151ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2tlaXZlbl8_size_16_color_FFFFFF_t_70 1]: /images/20221005/d8e5103b95cd4864882d61ad5c75bd26.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2tlaXZlbl8_size_16_color_FFFFFF_t_70 2]: /images/20221005/559a258269514525911b27c474c3244b.png
[hive_join]: https://blog.csdn.net/qq_26442553/article/details/79465608?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162670202516780366533027%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=162670202516780366533027&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-2-79465608.pc_search_result_control_group&utm_term=hive%E7%9A%84join&spm=1018.2226.3001.4187
[SQL_merge into]: https://blog.csdn.net/qq_40992849/article/details/80985417
[Hive 1]: https://blog.csdn.net/ddydavie/article/details/80667727
[hive]: https://blog.csdn.net/weixin_40444678/article/details/81083614
[Link 1]: https://blog.csdn.net/baichoufei90/article/details/85264100