一、概述

1.1 简介

(1)Hive提供了一个被称为Hive查询语言(简称HiveQL或HQL)的SQL语言，来查询存储在HDFS中的结构化数据文件，它把HQL语句的查询转换为MapReduce任务。

(2)Hive应用场景：

a、数据仓库：数据抽取、数据加载、数据转换

b、数据汇总：每天/每周用户点击数、流量统计

c、非实时分析：日志分析、文本分析

d、数据挖掘：用户行为分析、兴趣分区、区域展示

1.2 架构

hive是典型C/S模式，Client端有JDBC/ODBC Client和Thrift Client两类。Server端则分为如下几个部分：

CLI： CLI是和Hive交互的最简单/最常用方式，你只需要在一个具备完整Hive环境下的Shell终端中键入hive即可启动服务。
Thrift Server： Hive Thrift Server是基于Thrift 软件框架开发的，它提供Hive的RPC通信接口。目前的HiveServer2(HS2)较之前一版HiveServer，增加了多客户端并发支持和认证功能，极大地提升了Hive的工作效率和安全系数。
Metastore： Metastore是Hive元数据的存储地。在功能上Metastore分为两个部分：服务和存储，也就是架构图中提到的Metastore及其Database。通常使用MySQL来存储元数据。
WUI： WUI并不属于Apache Hive，它是Hive生态圈的一项服务，目前熟知的有Karmasphere、Hue、Qubole等项目。WUI是B/S模式的服务进程，Server一端与Hive Thrfit Server交互，Brower一端供用户进行Web访问。
Driver：每一个Hive服务都需要调用Driver来完成HQL语句的翻译和执行。通俗地说，Driver就是HQL编译器，它解析和优化HQL语句，将其转换成一个Hive Job（可以是MapReduce，也可以是Spark等其他任务）并提交给Hadoop集群。

67f40f45248e645abaea9ad1b744b1523606a8f4

1.3 特性

(1)优点：

a、高可靠、高容错：HiveServer采用主备模式、双MetaStore、超时重试

b、类SQL：类似sql语句、内置大量函数

c、可扩展：自定义存储格式、自定义函数

(2)缺点：

a、延迟较高：默认M/R为执行引擎、M/R启动有延迟

b、不支持物化视图：虽然提供了视图的概念，但还不支持物化视图；不能再视图上更新、插入、删除数据

c、不适用OLTP：暂不支持行级别的数据添加、更新、删除操作

d、暂无支持存储过程

二、数据类型

Hive表字段所支持的数据类型分为两类：基本数据类型和集合数据类型。

2.1 基本数据类型

tinyint、smallint、int、bigint、boolean、float、double、string、timestamp、binary

2.2 集合数据类型

struct(结构体或者对象)、map(键-值对)、array(数组)

举个例子，创建一张员工表。

 CREATE TABLE employees (

            name                STRING,

            salary                FLOAT,

            subordinates    ARRAY<STRING>,

            deductions        MAP<STRING, FLOAT>,

            address            STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>);

name表示员工姓名，salary是薪水，subordinates是下属员工的姓名集合数组，deductions是一个由键-值对构成的map，记录了每一次的扣除额，address是家庭住址，使用struct数据类型存储，其中的每个域都被作了命名，并且具有一个特定的类型。

三、读时模式 VS 写时模式

传统型数据库采用写时模式，即在数据写入数据库时对模式进行检查。Hive不会在数据加载时进行验证，而是在查询时进行，也就是读时模式。如果模式和文件内容并不匹配，hive会极力读取这些数据。比如每行记录中的字段个数少于对应的模式中定义的字段个数的话，那么用户将会看到查询结果中多出来的字段值为null。

四、HiveQL：数据定义

4.1 新增数据库

create database [数据库名];

如果没有显示指定数据库，那么将会使用默认的数据库default;

数据库所在的目录位于属性 hive.metastore.warehouse.dir 所指定的顶层目录之后，假设使用的是默认的配置，也就是/user/hive/warehouse，那么当我们创建数据库financials时，Hive将会对应地创建一个目录/user/hive/warehouse/financials.db，financials.db是一个文件夹，在financials数据库下建的表对应的文件都放这个目录下面。

4.2 建表

4.2.1 管理表(内部表)

默认创建的表即为管理表(内部表)，区别于外部表，管理表(内部表)的数据文件是加载到内部，由hive来管理。

create table 表名(列名  列类型，列名 列类型......)   location  '路径';

location指定表对应的目录，默认为对应数据库目录下面，如在financials数据库下面新建的records表对应的目录为/user/hive/warehouse/financials.db/records，后面该表对应的数据文件会存储到该目录下。

4.2.2 外部表

                create external table if not exists [表名] (列名  列类型，列名  列类型.....)

                row format delimited fields terminated by ','  

                location '路径'；

关键字external 来表名创建的是外部表，location声明该表对应的数据文件的位置，该位置处于外部系统，hive不会把该数据文件加载到系统内。在删除表时，hive不会同时此数据文件。

外部表适用于hive和其他的工具共用一份数据，可以进行一些查询的操作，但hive并不拥有此数据的所有权。

4.2.3 分区表

创建一张表partition_log，以dt和country这两个字段进行分区。

 create table partition_log(ts bigint, line string) partitioned by (dt string, country string);

从本地文件给表partition_log加载数据，在加载时要为加载的数据指定分区，这里指定的值dt为2001-01-01，2001-01-01为GB。

 load data local inpath '/root/hive/partitions/file1' into table partition_log partition (dt='2001-01-01', country='GB');

hive此时会在partition_log表对应的路径下面创建 /dt=2001-01-01/country=GB/ 这两级目录。

分区表本质上就是将数据进行归类，同一类的数据放到一个文件夹下，提供查询的效率。

4.2.4 外部分区表

要使用外部分区表，首先要创建一张外部表。

create external table  external_table  (列名 列类型，列名 列类型....) partitioned by (year int, month int ,day int);

用alter table为表增加一个分区，并为该分区指定一个外部文件的路径。

alter table external_table  add partition(year=2012,month=1,day=2) location 'hdfs://master_server/data/log_messages/2012/01/02';

五、HiveQL：数据操作

5.1 向管理表中装载数据

load data local inpath  '${env.HOME}/california-employees' overwrite into table employees partition (country='US', state='CA');

inpath指定数据文件的路径，此路径可以是一个目录，hive会把该目录下所有的文件都加载进来。partition指定分区，如果不是分区可以省略。overwrite关键字表示覆盖，hive会把原先表目录下的数据文件删除再加载新的数据文件。

5.2 通过查询语句向表中插入数据

                insert overwrite table employees partition (country='US'，state='OR')

                select * from staged_employees se where se.cntry='US' and se.st='OR';

如果向多个分区插入数据，可以采用如下方式：

                from staged_employees se

                insert overwrite table employees

                    partition(country='US' , state='OR')

                    select * where se.cnty='US' and se.st='OR'

                insert overwrite table employees

                    partition(country='US',state='CA')

                    select * where se.cnty='US' and se.st='CA';

动态分区插入

基于查询参数推断出需要创建的分区名称。

                    insert overwrite table employees

                    partition (country,state)

                    select ... ，se.cnty，se.st

                    from staged_employees se;

Hive根据select语句中最后2列来确定分区字段country和state的值。

也可以混合使用动态和静态分区，下面例子指定了country字段的值为静态的US，而分区字段state是动态值：

                    insert overwrite table employees partition (country='US'，state)

                    select ...，se.cnty，se.st from staged_employees se where se.cnty='US'；

静态分区键必须出现在动态分区键之前。

5.3 单个查询语句中创建表并加载数据

                    create table ca_employees

                    as select name,salary,address

                    from employees where se.state='CA'；

六、HiveQL：查询

6.1 select ... from...语句

1、查询array数组中的元素,使用索引来查找，比如：

select name,subordinates[0] from employees;

2、查询map中的元素用 [键名]

select name,deductions["State Taxes"] from employees;

3、查询struct中的元素用“点”符号

select name,address.city from employees;

limit语句

限制返回的行数

select * from employees limit 2;

case...when...then 句式

case...when...then语句和if条件语句类似，用于处理单个列的查询结果。例如：

                      select name,salary,

                            case

                                when salary<5000.0 then 'low'

                                when salary>=5000.0 and salary<7000.0 then 'middle'

                                when salary>7000.0 then 'high'

                            end as bracket from employees;

6.2 where语句

1、like和rlike

like和rlike都可以进行模糊匹配，rlike是like的增强版，可以通过正则表达式来指定匹配条件，例如：

select name,address.street from employees where address.street rlike '.*(Chicago|Ontario).*'；

6.3 group by 语句

having语句

having语句用来对group by产生的分组进行条件过滤，例如：

6.4、join语句

1、 内连接：只有进行连接的两个表中都存在与连接标准相匹配的数据才会被保留下来。Hive的join语句只支持等值连接，不支持“<” 和“>”。

                    select a.ymd, a.price_close, b.price_close from stocks a join stocks b on a.ymd=b.ymd where a.symbol='APPL' and b.symbol='IBM'；

2、 join语句优化：hive在执行join语句时，会把其他表都缓存起来，然后扫描最后那个表进行计算。所以在保证join语句中表从左至右要依次递增。比如：

                    select a.name, b.companyname from company a join employees b on a.companycode=b.companycode，要保证表大小company <employees 。

3、 left outer join左外连接：左边表符合where条件的全部保留，对应右边表选择的列如果没有符合连接条件的将会返回null值。

4、 right outer join右外连接：返回右边表所有符合where语句的记录。左表中匹配不上的字段用null代替。

6.5 order by 、sort by、distribute by、cluster by

1、 order by：进行全局排序，会有一个所有的数据都通过一个reducer进行处理的过程。

2、sort by：进行局部排序，只会在每个reducer中对数据进行排序。

distribute by：distribute by和sort by配合使用。distribute by可以让具有相同字段值的记录被分发到一个reducer，实现了对数据进行分组，并根据sort by指定的字段进行组内排序。例如：

                        select s.ymd, s.symbol, s.price_close from stocks s distribute by s.symbol sort by s.symbol ASC, s.ymd ASC；

3、 cluster by：相当于distribute by...sort by...语句中distribute by 和sort by的是同一个字段，表示对数据进行分组显示。

select s.ymd, s.symbol, s.price_close from stocks s cluster by s.symbol；

七、HiveQL：视图

视图可以允许保存一个查询并像对待表一样对这个查询进行操作。这是一个逻辑结构，因为它不像一个表会存储数据。换句话说，Hive目前暂不支持物化视图。

使用视图的好处：

1、降低查询复杂度，例如下面以个嵌套子查询：

        from (

                select * from people join cart

                    on (cart.people_id=people.id) where firstname='john'

        ) a select a.lastname where a.id=3;

将嵌套子查询声明为一个视图:

        create view shorter_join as

        select * from people join cart on (cart.people_id=people.id) where firstname='john';

这样就可以用这个视图来简化第一个查询：

        select lastname from shorter_join where id=3;

2、视图可以只展示一张表的部分内容，而不会让用户看到全部。

八、HiveQL：索引

Hive只有有限的索引功能，因为Hive没有主键和外键。建立索引可以加速查询速度。一张表的索引数据存储在另一张表中。

8.1 创建索引

例如对表employees的country字段建立索引：

        create index employees_index on table employees(country)

        as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'

        with deferred rebuild

        idxproperties('creator'='me', 'created_at'='some_time') in table employees_index_table

        partitioned by (country,name) comment 'Employees indexed by country and name.'；

employees_index表示索引名称，as...语句指定了索引处理器，也就是一个实现了索引接口的类，也可以使用其他的类（Hive内置的其他实现类、第三方实现类或者是自定义实现类），with deferred rebuild表示重建索引，这样新建的索引将呈现空白状态。in table...指定了一张新表，用来存储该索引数据。

8.2 Bitmap索引

bitmap索引应用于排重后值较少的列，如性别、年级等。

九、模式设计

9.1 按天划分的表

每天一张表的方式在数据库领域用来应对数据集增长很快的情况，通常会在表名中加入一个时间戳，比如upply_2011_01_01、upply_2011_01_02等等。对于hive，这种情况应该使用分区表，为每一天创建一个对应的分区。

        create table supply(id int, part string, quantity int) partitioned by (int day)；

        alter table supply add partition (day=20110102);

        alter table supply add partition (day=20110103);

        alter table supply add partition (day=20110104);

9.2 分区创建策略

一个理想的分区方案不应该导致产生太多的分区和文件夹目录，并且每个目录下的文件夹应该足够的大，应该是文件系统HDFS中块大小的若干倍。

9.3 同一份数据多种处理

hive提供了一个独特的语法，它可以从一个数据源产生多个数据聚合，而无需每次聚合都要重新扫描一次。例如，下面2个查询都会从源表history表读取数据，然后导入到2个不同的表中：

        insert overwrite table sales

                select * from history where action='purchased'；

        insert overwrite table credits

                select * from history where action='returned'；

上面的方式效率低下，下面这个查询可以达到同样的目的，却只需要扫描history表一次就可以：

        from history

        insert overwrite sales select * where action='purchased'

        insert overwrite credits select * where action='returned'；

9.4 分桶表存储数据

分桶是根据某一列的值把数据分成指定个数的文件进行存储，列值相同或者列值的hash值相同的记录会被存储到同一个文件。

分桶可以用于数据取样，同时当查询条件是分桶字段的话，也可以提高查询效率。

9.5 使用列存储表

hive通常使用行式存储，但是也提供了混合列式存储。下面两种表适合采用列式存储：

(1)某些列的值会有很多重复的值，比如包含性别、年龄、状态等属性的表。

(2)表含有非常多的字段。

9.6 使用压缩

压缩都可以使磁盘上存储的数据量变小，这样可以通过降低I/O来提高查询执行速度。压缩和解压缩会消耗CPU资源，但是MapReduce任务通常是I/O密集型，所以几乎在所有场景下都要使用压缩。除了CPU密集型的场景，例如一些机器学习算法等。

十、调优

10.1 explain [extended]

使用explain关键字可以显示sql的执行计划，explain extended可以显示出更多的信息。

10.2 limit限制调整

一般情况下，Limit语句还是需要执行整个查询语句，然后再返回部分结果。这种情况通常是浪费的，应该尽可能的避免出现这种情况。

有一个配置属性可以开启，避免这种情况---对数据源进行抽样

         hive.limit.optimize.enable=true --- 开启对数据源进行采样的功能

         hive.limit.row.max.size --- 设置最小的采样容量

         hive.limit.optimize.limit.file --- 设置最大的采样样本数

缺点：有可能部分数据永远不会被处理到。

10.3 本地模式

有时hive的输入数据量是非常小的。在这种情况下，为触发其他机器执行任务的时间消耗可能会比实际job的执行时间要多的多。对于大多数这种情况，hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间会明显被缩短

set hive.exec.mode.local.auto=true;

当一个job满足如下条件才能真正使用本地模式：

　　 1.job的输入数据大小必须小于参数：hive.exec.mode.local.auto.inputbytes.max(默认128MB)

　　 2.job的map数必须小于参数：hive.exec.mode.local.auto.tasks.max(默认4)

　　 3.job的reduce数必须为0或者1

可用参数hive.mapred.local.mem(默认0)控制child jvm使用的最大内存数。

10.4 并行执行

hive会将一个查询转化为一个或多个阶段，包括：MapReduce阶段、抽样阶段、合并阶段、limit阶段等。默认情况下，一次只执行一个阶段。不过，如果某些阶段不是互相依赖，是可以并行执行的。

        set hive.exec.parallel=true,可以开启并发执行。

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

会比较耗系统资源。

10.5 严格模式

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

通过设置hive.mapred.mode为strict来开启。

使用严格模式可以禁止3种类型的查询：

（1）对于分区表，不加分区字段过滤条件，不能执行

（2）对于order by语句，必须使用limit语句。

（3）限制笛卡尔积的查询（join的时候不使用on，而使用where的）。

10.6 调整mapper和reducer数量

10.7 JVM重用

JVM启动过程比较慢，默认情况下，hadoop是通过为每一个map和reduce任务启动一个JVM实例来运行，当小文件比较多或者Task特别多的场景，任务的执行比较短，大量的时间消耗在JVM启动上。通过mapred.job.reuse.jvm.num.tasks配置，可以设置JVM实例在同一个job中重新使用的次数。这个功能的一个缺点是，开启JVM重用将会一直占用使用到的task插槽，直到整个任务执行结束。

10.8 索引

添加适当的索引来加快查询速度。

10.9 动态分区调整

(1)将动态分区模式设置为严格模式，必须保证至少有一个分区是静态的。通过设置hive.exec.dynamic.partition.mode为strict。

(2)限制单表可以创建的最大分区数。hive.exec.max.dynamic.partitions。

十二、函数

12.1 内置函数

12.1.1 标准函数

以一行数据中的一列或多列数据作为参数然后返回结果是一个值的函数，大多数函数都是属于这类的，比如round()、floor()这些数学函数。

12.1.2 聚合函数

聚合函数接受从零行到多行的零个到多个列，然后返回单一值。例如sum()、avg()、min()、max()等。

12.1.3 表生成函数

表生成函数接受零个或多个输入，然后产生多列或多行输出，例如explode()函数以array类型数据作为输入，然后对数组中的数据进行迭代，返回多行结果，一行一个数组元素值。

12.2 自定义函数

分别提供了三种接口对应标准函数、聚合函数、表生成函数，需要实现对应接口，实现接口的方法。

十三、Streaming

自定义函数需要开发人员编写java代码，对于不熟悉java的开发人员，hive提供了streaming的方式来完成同样的事情。streaming就是在hiveQL中允许使用系统的脚本来对数据进行处理。

例如，使用Linux系统中的命令cat来查询表：

select transform(e.name, e.salary) using '/bin/cat' as name, salary from employee e；

同时还允许运行用户自定义的脚本。

十四、文件格式、记录格式和文件压缩

14.1 文件格式

Hive内置了三种文件格式：文本文件、SequenceFile、RCfile。当从表中读取数据时，Hive会使用到InputFormat，向表中写入数据时，会使用OutputFormat。在create table...建表时，通过stored as...来指定文件格式。

14.1.1 文本文件

文本文件格式是默认存储格式，等价于在创建表时通过 stored as textfile语句指定使用文本存储格式。对应的InputFormat是TextInputFormat，OutputFormat是HiveIgnoreKeyTextOutputFormat。

14.1.2 SequenceFile

SequenceFile是hadoop本身就支持的一种标准文件格式，在定义表结构时通过stored as sequencefile语句指定。对应的InputFormat是SequenceFileInputFormat，OutputFormat是HiveSequenceFileOutputFormat。

14.1.3 RCfile

采用列式存储。一张表有成百上千字段，而大多数的查询只需要使用到其中的一小部分字段，这时候该表就适合采用RCfile进行存储。对应的InputFormat是RCFileInputFormat，OutputFormat是RCFileOutputFormat。

14.1.4 自定义文件格式

可以通过实现InputFormat和OutFormat来自定义文件格式。在定义表结构时，可以指定InputFormat和OutFormat，例如：

            create table colunmTable(key int, value int)

            row format serde

            'org.apache.hive.serde2.columnar.ColumnarSerde'

            stored as

            inputformat 'org.apache.hadoop.hive.ql.io.RCFileInputFormat'

            outputformat 'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'

14.2 记录格式

14.2.1 SerDe

SerDe是序列化/反序列化的接口，在内部，Hive引擎使用定义的InputFormat来读取一行数据记录，这行记录之后会被传递给SerDe.deserialize()方法来处理，SerDe决定了一行记录如何被解析。Hive内置了几个SerDe，比如处理正则表达式RegexSerDe，CSV文件CSVSerDe。

14.2.2 JSON SerDe

JsonSerde来自于第三方，可以用于查询json格式的记录。例如：

            create external table messages (

                msg_id          bigint,

                tstamp          string,

                text               string,

                user_id          bigint,

                user_name,   string

            )

            row format serde "org.apache.hadoop.hive.contrib.serde2.JsonSerde"

            with serdeproperties (

                "msg_id"="$.id",

                "tstamp"="$.created_at",

                "text"="$.text",

                "user_id"="$.user.id",

                "user_name"="$.user.name"

            )

            location '/data/messages';

row format serde用于指定使用的SerDe，with serdeproperties用于给SerDe传递属性信息，上面的例子中用这些属性将json文档和表的字段对应起来。定义好之后，就可以像通常一样执行查询：select * from messages；

14.3 文件压缩

压缩可以节省磁盘存储空间，减小磁盘和网络I/O时间。Hive表的文件存储在hadoop上，可以使用hadoop提供的压缩对文件进行压缩。除此以外，hive也提供了两个跟压缩相关的特有设置：

(1)开启中间压缩 hive.exec.compress.intermediate

对中间数据进行压缩可以减少job中map和reduce task间的数据传输量

(2)最终输出结果压缩 hive.exec.compress.output和mapred.output.compression.codec

十五、存储处理程序和NoSQL

存储处理程序可以将外部实体表作为标准的hive表进行处理，它包含了InputFormat、OutputFormat、SerDe和Hive需要使用的特定的代码。例如可以将Hbase、Cassandra、DynamoDB这样的NoSQL数据库中的表作为hive表来处理，下面是创建一个指向Hbase表的Hive表：

    create table habase_stocks(key int, name string, price float)

    stored by 'org.apache.hive.hbase.HBaseStorageHandler'

    with serdeproperties("hbase.columns.mapping"=":key,stock:val")

    tblproperties("hbase.table.name"="stocks")；

十六、权限

默认情况下，授权模块是不开启的，需要将属性hive.security.authorization.enabled设置为true。

16.1 用户、组和角色

hive中的用户和组是外部系统的用户和组，比如当使用HiveCLI方式来访问hive时，此时的用户和组就对应linux系统当前用户和组。角色是hive自己创建的，它的使用比较灵活。可以为一个角色赋予权限，然后给用户设置该角色，这样用户就拥有了该角色所拥有的权限。

16.2 权限种类

下面列举了可以配置的权限：

名称	描述
all	所有的权限
alter	修改表结构的权限
create	创建表的权限
drop	删除表或表中的分区的权限
index	创建表索引的权限
lock	开启并发后，锁定和解锁表的权限
select	查询表或者分区中数据的权限
show_database	查看所有数据库的权限
update	向表或者分区中插入或加载数据的权限

例如为用户edward赋予在数据库edsstuff中create权限：

grant create on database edsstuff to user edward；

16.3 分区级别的授权

默认情况下，是在表级别授予权限的，但是可以在分区级别进行授权，将表属性partition_level_privilege设置为true即可。如下面的例子：

        create table autorized_part (key int, value string) partitioned by (ds string);   //创建一张分区表autorized_part

        alter table autorized_part  set tblproperties("partition_level_privilege"="true")；//为该表开启分区级别的授权

        alter table autorized_part  add partition (ds='3')； //为表新增一个分区，值为3

        revoke select on table autorized_part   partition(ds='3') from user edward；  //给edward赋予分区ds='3'的select 权限

十七、锁

hive不支持行级别的更新和删除，也不支持事务，因此细粒度锁对hive是没有必要的。hive提供了表级别和分区级别的锁，这需要配合zookeeper来使用。

在hive-site.xml配置文件中，增加hive.zookeeper.quorum属性来指定zookeeper集群的地址，同时设置hive.support.concurrency为true，开启支持并发。

      show locks；      //查询当前所有锁

      show locks 表名；   //查询某张表上的锁

      show locks 表名 partition (...)； //查询某个分区上的锁

17.1 锁的种类

hive中的锁分为两种：共享锁和独占锁。共享锁和共享锁之间不互斥，通常在读取的时候使用共享锁，独占锁通常在写入的时候使用，它会使其他用户无法访问锁定表或分区。

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Hive笔记