万字长文——最详Hive入门指南

万字长文——最详Hive入门指南

Hive基础

Hive简介

产生原因

1. 对存在HDFS上的文件或Hbase中的表进行查询时，是要手写一堆MapReducec代码；
2. 对于统计任务，只能由懂MapReduce的程序员才能完成；
3. 耗时耗力，更多精力没有有效的释放出来；

基于以上原因，Hive就此诞生了，Hive基于一个统一的查询分析层通过sql语句的方式对HDFS上的数据进行查询、统计和分析。

Hive的本质就是一个SQL解析引擎，将SQL语句转换成MR job。

Hive的定位是Hadoop大数据仓库，而SQL是其最常用的分析工具。

Hive的表都是纯逻辑表，就只是表的定义等，即表的元数据。其本质就是hdfs路径/文件，以达到元数据与数据存储分离的目的。其元数据(表、表结构等信息)一般存放在mysql，不使用自带的derby，主要是其不支持并发操作，是单线程操作，整体执行效率性能偏低。

Hive可以直接将结构化的数据文件映射成为一张数据库表。

Hive的内容是读多写少，不支持对数据的改写和删除。

Hive SQL VS SQL

补充

读时模式

优点：只有hive读的时候才会检查、解析字段和schema，所以load data非常迅速，因为在写的过程是不需要解析数据的；

缺点：读的时候慢；

写时模式

缺点：写的慢，写的过程需要建立一些索引压缩、数据一致性、字段检查等等。

优点：读的时候会得到优化

Hive对比传统关系型数据库

• hive和关系数据库存储文件的系统不同，hive使用的是 hadoop的HDFS( hadoop的分布式文件
  系统），关系数据库则是服务器本地的文件系统；
• hive使用的计算模型是 mapreduce，而关系数据库则是自己设计的计算模型；
• 关系数据库都是为实时查询的业务进行设计的，而hive则是为海量数据做数据挖掘设计的，实时性很差；
• hive很容易扩展自己的存储能力和计算能力，这个是继承 hadoop的，而关系数据库在这个方面要比Hive差很多。

Hive体系架构

Hive Client

进行交互式执行SQL，直接与 Driver进行交互；

JDBC/ODBC

Hive提供JDBC驱动，作为JAVA的API；

ODBC是通过 Thrift Server来接入，然后发送给 DriverHivef提供的Cli工具；

Metastore（元数据）

是一个独立的关系型数据库中保存表模式和其他系统元数据；

Driver（驱动模块）

通过该模块对需求的计算进行优化，然后按照指定的步骤执行（通常启动多个MR任执行）；

对Driver内组件进行介绍：

SQL Parser

根据SQL代码，生成抽象的语法树，通过语法分析，验证SQL语法是否正确。

Query Optimize(逻辑计划执行器)

写select、group by时，都会对应生成一个操作符的树，相当于定义了一个规则。它也可以帮助我们进行优化，降低作业难度。

Physical plan（物理执行计划）

通过物理执行计划，转变成MR任务；

Hive执行流程

1.首先UI(或Cli)界面和Driver建立互通，进行访问；

2.访问之后有一个编译过程，编译的过程会去获取和查询元数据，如果有则返回，如果没有，则查询的时候会报错，比如select某不存在的列；

3.返回的数据通过Driver交给EXECUTION ENGINE，给具体的执行引擎进行执行，只有它才知道我们具体要操作哪一列；

4.任务最终发送给JobTracker，其对任务进行调度和分发，分发到不同的起点，进行reduce操作。

5.在进行对应map或reudce操作时会生成对应的一个操作的树;

6.计算的数据最终存在HDFS里，需要和NameNode连接，获取元数据，找到数据所在的DataNode，然后对应数据进行返回，进行Map或Reduce任务的运行；

HiveQL常用内部命令

1.在hive客户端执行linux shell命令：

不能使用需要用户输入的交互式命令；
不支持shell命令的“管道”功能和文件名自动补全；

• ! (shell命令) ;

2.在hive客户端执行hadoop的dfs命令

在hive client执行hadoop命令实际比在bash shell客户端执行hadoop dfs命令更高效，因为bash shell每次都会启动一个新的jvm进程实例来运行hadoop dfs命令而hive client会共享hive client 已经开辟的自身jvm进程来执行hadoop dfs命令；

• 在hive client执行hadoop dfs -xxx 命令时，需要把命令的"hadoop"关键字去掉并以分号";"结尾；

3.Hive数据类型

• TINYINT

  1BYTE 有符号整数

• SMALLINT

  2BYTE 有符号整数

• INT

  4BYTE 有符号整数

• BIGINT

  8BYTE 有符号整数

• BOOLEAN

  布尔类型true or false

  (boolean 常见使用0 1 替代true和false)

• FLOAT

  单精度浮点数
  3.14159

• DOUBLE

  双精度浮点数
  3.14159

• STRING

  字符序列，可以指定字符集，可以使用单引号或双引号

• TIMESTAMP

  整数、浮点数或者字符串

• BINARY

  字节数组

4.Hive建表

• 1.建立school数据库

  • create database school;

• 2.在school数据库下建student表

  • create table school.student(id bigint, name string) row format delimited fields terminated by ‘,’;

    • create table school.student(id bigint, name string)

      创建表明和表字段名，与关系型数据库建表含义同

    • row format delimited

      行格式分隔，Hive是通过行格式来管理每条数据的分隔

    • fields terminated by ‘,’

      Hive中每行数据中各个字段的分隔符为逗号","

• 3.导入数据到student表

  • load data local inpath ‘数据路径’ into table student;

补充：表类型

维度表

是抽象提取出的、和业务无关、很少变化的表。维度表数据量较小，结构较为简单，不会有太复杂的查询操作；比如部门id和部门名称的关系对应表；

事实表

在数据仓库中，保存度量值的详细值或事实的表称为“事实表”。比如用户行为表等；

5.hive集合数据类型

使用场景：一般用于一般用于前端埋点，流量埋点

• STRUCT
• MAP
• ARRAY

Hive数据定义与操作

HiveQL数据定义语言

数据库相关

• 1.创建数据库

  • create database (if not exists) 数据库名;

• 2.查询数据库名以"s"开头的数据库列表

  • show database (like ‘s.*’);

• 3,为数据库增加描述以说明数据库业务含义

  • create database (bank) (comment ‘Internet Banking’);

    创建数据库bank作为电子网银系统后台数据库

• 4.查看数据库详细信息

  • desc/describe database (bank);

• 5.删除数据库

  • drop database (if exists) 数据库名;

  • 删除含有多张表的数据库

    • drop database (if exists) 数据库名 (cascade);

数据库表相关

创建表

直接建表

1.create table (if not exists) enterprise.account(acc_name string, acc_balance double) row format delimited fields terminated by ‘\t’ location ‘user/hive/warehouse/enterprise.db/account’;

• create table (if not exists) enterprise.account(acc_name string, acc_balance double)

  定义表和表字段

• user/hive/warehouse

  是默认的数据仓库地址

• /enterprise.db/account

  是enterprise数据库目录，下面存着该数据库里的表，如account

2.将建表语句写到sql文件中，在linux中用hive -f xx.sql创建表；

适用场景

1.用户可以根据数据源的格式进行自定义的建表，包含指定分隔符，列的分隔符、数据的存储格式、等等；

2.根据数据量，指定的时候选择合适的存储格式textfile、orc；

抽取as建表

create table table1 as select * from table2;

适用场景

工作中常用，涉及到逻辑的整合，比如和临时表结合使用，表的结构和数据都要；

like建表

create table table1 like table2;

创建表table1的表结构和table2一样，但是table1没有数据。

适用场景

只关心表的结构，数据不复制

建表时一般不建议以关键字为表字段，如果有需要加上``符号修饰

内外部表

管理表（内部表）

1.管理表能有效的管理表的数据但是不利于数据的分享；

2.管理表删除后，对应的数据也会被删除

适用场景

在做etl逻辑处理时，往往会选择内部表作为中间表，因为这些中间表逻辑处理完后数据会进行删除，同样HDFS上数据得到删除；

外部表

1.删除外部表后，数据仍然存在hdfs中，重新建立相同名称表即可重新恢复删除的外部表；

2.外部表可以和其他外部表进行数据的共享；

• create （external）table product(pro_name string, price double) row format delimited fields terminated by ‘\t’ location ‘/data/stocks’

• 9.修改表

  • 1.修改表名

    • alter table (tableName) rename to (otherTableName)

  • 2.增加表字段

    • alter table (tableName) add columns(column1 type, column2, type)

适用场景

如果怕数据被误删除，可以直接选用外部表，因为不会删除源数据，方便恢复；

外部表删除后不会删除表数据，这时重新创建外部表，相同表名，也可以获取到数据。同样，建立内部表也可以获取到数据。因为，在创建外部表时，" load local data xxxx into table 表名 " 这个记录存在，所以相同表名都能查询到数据

内外部表使用案例

每天将日志数据传入HDFS，一天一个目录；Hive基于流入的数据建立外部表，将每天HDFS上的原始日志映射到外部表的天分区中；在外部表基础上做统计分析，使用内部表存储中间表、结果表，数据通过SELECT+ INSERT进入内部表；

分区表

目的

在Hive中执行select查询一般会扫描整张表，当表的数据量很大时，会消耗过多的时间，影响执行的效率。有时候只需要扫描表中关心的一部分数据，因此引入了分区表的概念。

1.分区表能够把一张大表的数据根据业务需求分配到多张小表中，以提高表的并发量；

2.分区表使得所有数据都集中在一张表中，但是底层物理存储数据根据一定的规则划分到不同的文件中，这些文件还可以存储到不同的磁盘上，分散了存储的压力；

如何分区

业界常使用“dt”或“d”作为分区字段(分区字段命名)，选取id、年月日、男女性、年龄段或者是能够平均将数据分导不同的文件中最好。分区不好将直接导致查询结果延迟。

分区细节

1. 一个表可以拥有一个或多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下；
2. 表和列名不区分大小写；
3. 分区是以字段的形式在表结构中存在，通过describe table命令可以查看到字段的存在(可以看作是一个伪列)，即该字段不存放实际的数据内容，仅仅以分区表示；
4. 分区有一级、二级设置（看分区字段有多少个），一般设置是一级分区；
5. 分区分为动态分区和静态分区；

静态与动态分区

• 静态分区

  使用静态分区在插入数据之前需要首先知道有哪些分区类型，针对每一个分区要单独采用load data 命令载入数据。

  • create table customer_partition(name string, age int) partitioned by(sex string) row format delimited fields terminated by ‘\t’;

    • partitioned by(sex string)

      Hive分区是在创建表时用partitioned by关键字定义，以sex作为分区字段

  • 导入数据

    • load data local inpath ‘/xxx/customer.txt’ into table customer_partition partition(sex=‘man’);

      • partition(sex=‘man’);

        静态分区在导入数据时，要额外指定分区字段值，此时分区表会自动生成一列分区字段man(该"man"分区表的sex字段全为man
        )

  • 应用场景

    • 数据量不大，同时知道分区数据类型可以这样操作；
    • 缺点：每一次load data都要操作，繁琐

• 动态分区

  动态分区不用手动指定指定分区，由系统自己选择分配

  • 开启动态分区功能(只在当前cli有效，要全局有效，需要设置配置文件)

    • 1.set hive.exec.dynamic.partition=true;

    • 2. set hive.exec.dynamic.partition.mode=nonstrict;
      • hive.exec.dynamic.partition.mode属性为“nonstrict”，意味着所有分区列都是动态的，为strict意味着不允许分区列全部是动态的

    • 3.set hive.exec.max.dynamic.partitions.pernode=1000;

• 设置最大动态分区个数

• 创建动态分区表

  • create table customer_partition(name string, age int) partitioned by(sex string) row format delimited fields terminated by ‘\t’;

    设置好分区字段后，直接导入数据即可自动分区，不需要指定分区字段

  • 应用场景

    • load data 非常方便，但是要打开动态分区并设置非严格模式
    • 
    • 
    • 
    • 
    • 利用时间戳分区

Hive分区常见操作整理

• 增加单个分区（增加同级分区）

alter table (table_name) add partition(分区字段名称=实际分区值)
alter table part_table add partition(country='CN')

• 增加多个分区

alter table (table_name) add partition(分区字段名称=实际分区值), partition(分区字段名称=实际分区值)
alter table part_table add partition(country='CN'), partition(country='UK')

• 增加分区的同时添加数据

alter table (table_name) add partition(分区字段名称=实际分区的值) location 'HDFS path'
# location后面需要的是一个存储数据的路径文件夹而不是到文件
alter table part_table add partition(country='MS') location '/user/hive/warehouse/db1/test'

• 删除单个分区