Hive原理详解

(一) Hive基础

1.1 背景

• 引入原因：
    – 对存在HDFS上的文件或HBase中的表进行查询时，是要手工写一堆MapReduce代码
        select word, count(*) from ( select explode(split(sentence, ' ')) as word from article) t group by word即可完成wordcount( UDTF函数explode的说明)

    – 对于统计任务，只能由动MapReduce的程序员才能搞定

    – 耗时耗力，更多精力没有有效的释放出来
• Hive基于一个统一的查询分析层，通过SQL语句的方式对HDFS上的数据进行查询、统计和分析

1.2 Hive是什么

• Hive是一个SQL解析引擎，将SQL语句转译成MR Job，然后再Hadoop平台上运行，达到快速开发的目的。
• Hive中的表是纯逻辑表，就只是表的定义等，即表的元数据。本质就是Hadoop的目录/文件，达到了元数据与数据存储分离的目的
• Hive本身不存储数据，它完全依赖HDFS和MapReduce。
• Hive的内容是读多写少，不支持对数据的改写和删除
• Hive中没有定义专门的数据格式，由用户指定，需要指定三个属性：

    – 列分隔符

    – 行分隔符

    – 读取文件数据的方法

1.3  Hive中的SQL与传统SQL区别

Hive在0.8之后增加的索引为位图索引，而传统SQL有复杂的索引

1.4 Hive与传统关系数据特点比较

• hive和关系数据库存储文件的系统不同，hive使用的是hadoop的HDFS（hadoop的分布式文件系统），关系数据库则是服务器本地的文件系统；
• hive使用的计算模型是mapreduce，而关系数据库则是自己设计的计算模型；
• 关系数据库都是为实时查询的业务进行设计的，而hive则是为海量数据做数据挖掘设计的，实时性很差

• Hive很容易扩展自己的存储能力和计算能力，这个是继承hadoop的，而关系数据库在这个方面要比数据库差很多。

（二）Hive体系架构

2.1 Hive的基本组成

用户接口：包括 CLI、JDBC/ODBC、WebGUI。

元数据存储：通常是存储在关系数据库如 mysql , derby中。

语句转换：解释器、编译器、优化器、执行器。

1.  查询语言。由于 SQL 被广泛的应用在数据仓库中，因此，专门针对 Hive 的特性设计了类 SQL 的查询语言 HQL。熟悉 SQL 开发的开发者可以很方便的使用 Hive 进行开发。

2.  数据存储位置。Hive 是建立在Hadoop 之上的，所有 Hive 的数据都是存储在HDFS 中的。而数据库则可以将数据保存在块设备或者本地文件系统中。

3.  数据格式。Hive 中没有定义专门的数据格式，数据格式可以由用户指定，用户定义数据格式需要指定三个属性：列分隔符（通常为空格、”\t”、”\x001″）、行分隔符（”\n”）以及读取文件数据的方法（Hive 中默认有三个文件格式 TextFile，SequenceFile 以及 RCFile）。由于在加载数据的过程中，不需要从用户数据格式到 Hive 定义的数据格式的转换，因此，Hive 在加载的过程中不会对数据本身进行任何修改，而只是将数据内容复制或者移动到相应的 HDFS 目录中。而在数据库中，不同的数据库有不同的存储引擎，定义了自己的数据格式。所有数据都会按照一定的组织存储，因此，数据库加载数据的过程会比较耗时。

4.  数据更新。由于 Hive 是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive 中不支持对数据的改写和添加，所有的数据都是在加载的时候中确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用 INSERT INTO ...  VALUES 添加数据，使用 UPDATE... SET 修改数据。

5.  索引。之前已经说过，Hive 在加载数据的过程中不会对数据进行任何处理，甚至不会对数据进行扫描，因此也没有对数据中的某些 Key 建立索引。Hive 要访问数据中满足条件的特定值时，需要暴力扫描整个数据，因此访问延迟较高。由于 MapReduce 的引入， Hive 可以并行访问数据，因此即使没有索引，对于大数据量的访问，Hive 仍然可以体现出优势。数据库中，通常会针对一个或者几个列建立索引，因此对于少量的特定条件的数据的访问，数据库可以有很高的效率，较低的延迟。由于数据的访问延迟较高，决定了 Hive 不适合在线数据查询。

6.  执行。Hive 中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的，而数据库通常有自己的执行引擎。

7.  执行延迟。之前提到，Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致 Hive 执行延迟高的因素是 MapReduce 框架。由于 MapReduce 本身具有较高的延迟，因此在利用 MapReduce 执行 Hive 查询时，也会有较高的延迟。相对的，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive 的并行计算显然能体现出优势。

8.  可扩展性。由于 Hive 是建立在 Hadoop 之上的，因此 Hive 的可扩展性是和 Hadoop 的可扩展性是一致的（世界上最大的 Hadoop 集群在 Yahoo!，2009年的规模在4000 台节点左右）。而数据库由于 ACID 语义的严格限制，扩展行非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有 100 台左右。

9.      数据规模。由于 Hive 建立在集群上并可以利用 MapReduce 进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。

2.2 各组件的基本功能

用户接口主要由三个：CLI、JDBC/ODBC和WebGUI。其中，CLI为shell命令行；JDBC/ODBC是Hive的JAVA实现，与传统数据库JDBC类似；WebGUI是通过浏览器访问Hive。

元数据存储：Hive 将元数据存储在数据库中。Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。

解释器、编译器、优化器完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS 中，并在随后有 MapReduce 调用执行。

2.3 HIVE 和 HADOOP的关系

简化版

详细版

2.4 Hive的数据管理

• hive的表本质就是Hadoop的目录/文件
    – hive默认表存放路径一般都是在你工作目录的hive目录里面，按表名做文件夹分开，如果你有分区表的话，分区值是子文件夹，可以直接在其它的M/R job里直接应用这部分数据
1、Hive中所有的数据都存储在 HDFS 中，没有专门的数据存储格式（可支持Text，SequenceFile，ParquetFile，RCFILE等）
2、只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。
3、Hive 中包含以下数据模型：DB、Table，External Table，Partition，Bucket。
      –  db：在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹
     – table：在hdfs中表现所属db目录下一个文件夹
     –  external table：与table类似，不过其数据存放位置可以在任意指定路径
     – partition：在hdfs中表现为table目录下的子目录

     –  bucket：在hdfs中表现为同一个表目录下根据hash散列之后的多个文件

2.4.1 Hive中的内部表和外部表

• Hive的create创建表的时候，选择的创建方式：
    – create table
    – create external table
• 特点：
    – 在导入数据到外部表，数据并没有移动到自己的数据仓库目录下，也就是说外部表中的数据并不是由它自己来管理的！而表则不一样；

    – 在删除表的时候，Hive将会把属于表的元数据和数据全部删掉；而删除外部表的时候，Hive仅仅删除外部表的元数据，数据是不会删除的！

2.4.2 Hive中的Partition

• 在 Hive 中，表中的一个 Partition 对应于表下的一个目录，所有的 Partition 的数据都存储在对应的目录中
    – 例如：pvs 表中包含 ds 和 city 两个 Partition，则
    – 对应于 ds = 20090801, ctry = US 的 HDFS 子目录为：/wh/pvs/ds=20090801/ctry=US；
    – 对应于 ds = 20090801, ctry = CA 的 HDFS 子目录为；/wh/pvs/ds=20090801/ctry=CA

• partition是辅助查询，缩小查询范围，加快数据的检索速度和对数据按照一定的规格和条件进行管理。

2.4.3 Hive中的Bucket

• hive中table可以拆分成partition，table和partition可以通过‘CLUSTERED BY’进一步分bucket，bucket中的数据可以通过‘SORT BY’排序。
• create table bucket_user (id int,name string)clustered by (id) into 4 buckets;
• 'set hive.enforce.bucketing = true' 可以自动控制上一轮reduce的数量从而适配bucket的个数，当然，用户也可以自主设置mapred.reduce.tasks去适配bucket个数
• Bucket主要作用：
    – 数据sampling
    – 提升某些查询操作效率，例如mapside join
• 查看sampling数据：
    – hive> select * from student tablesample(bucket 1 out of 2 on id);
    – tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)

    – y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。

2.4.4 Hive数据类型

- 数据类型
    • TINYINT
    • SMALLINT
    • INT
    • BIGINT
    • BOOLEAN
    • FLOAT
    • DOUBLE
    • STRING
    • BINARY（Hive 0.8.0以上才可用）

    • TIMESTAMP（Hive 0.8.0以上才可用）

– 复合类型
• Arrays：ARRAY<data_type>
• Maps:MAP<primitive_type, data_type>
• Structs:STRUCT<col_name: data_type[COMMENT col_comment],……>

• Union:UNIONTYPE<data_type, data_type,……>

2.4.5 HiveSQL——JoininMR 执行流程图

INSERT OVERWRITE TABLE pv_users 
SELECT pv.pageid, u.age 
FROM page_view pv 
JOIN user u
ON (pv.userid = u.userid);

SELECT pageid, age, count(1)
FROM pv_users
GROUP BY pageid, age;

2.4.6 Hive的优化

2.4.6.1 Map的优化

– 作业会通过input的目录产生一个或者多个map任务。set dfs.block.size(=128)
– Map越多越好吗？是不是保证每个map处理接近文件块的大小？

– 如何合并小文件，减少map数？

– 如何适当的增加map数？

– Map端聚合 hive.map.aggr=true 。 Mr中的Combiners.

2.4.6.2 Reduce的优化：

– hive.exec.reducers.bytes.per.reducer；reduce任务处理的数据量
– 调整reduce的个数：
    • 设置reduce处理的数据量
    • set mapred.reduce.tasks=10
• 一个Reduce：

    – 没有group by

    – order by（可以使用distribute by和sort by）

    – 笛卡尔积

2.4.6.3 分区裁剪（partition）

    – Where中的分区条件，会提前生效，不必特意做子查询，直接Join和GroupBy

2.4.6.4 笛卡尔积

    – join的时候不加on条件或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积

2.4.6.5 Map join

    – /*+ MAPJOIN(tablelist) */，必须是小表，不要超过1G，或者50万条记录

2.4.6.6 Union all

    – 先做union all再做join或group by等操作可以有效减少MR过程，尽管是多个Select，最终只有一个mr

2.4.6.7 Multi-insert & multi-group by

    – 从一份基础表中按照不同的维度，一次组合出不同的数据
    – FROM from_statement
    – INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1)] select_statement1 group by key1
    – INSERT OVERWRITE TABLE tablename2 [PARTITION(partcol2=val2 )] select_statement2 group by key2

2.4.6.8 Automatic merge

    – 当文件大小比阈值小时，hive会启动一个mr进行合并
    – hive.merge.mapfiles = true 是否和并 Map 输出文件，默认为 True
    – hive.merge.mapredfiles = false 是否合并 Reduce 输出文件，默认为 False
    – hive.merge.size.per.task = 256*1000*1000 合并文件的大小

2.4.6.9 Multi-Count Distinct

    – 必须设置参数：set hive.groupby.skewindata=true;
    – select dt, count(distinct uniq_id), count(distinct ip)

    – from ods_log where dt=20170301 group by dt

2.4.6.10 Hive的Join优化

• 一个MR job

• 生成多个MR job

2.4.6.11 Hive的Join优化--表连接顺序

• 按照JOIN顺序中的最后一个表应该尽量是大表，因为JOIN前一阶段生成的数据会存在于Reducer的buffer中，通过stream最后面的表，直接从Reducer的buffer中读取已经缓冲的中间结果数据（这个中间结果数据可能是JOIN顺序中，前面表连接的结果的Key，数据量相对较小，内存开销就小），这样，与后面的大表进行连接时，只需要从buffer中读取缓存的Key，与大表中的指定Key进行连接，速度会更快，也可能避免内存缓冲区溢出。

• 左连接时，左表中出现的JOIN字段都保留，右表没有连接上的都为空。

• 执行顺序是，首先完成2表JOIN，然后再通过WHERE条件进行过滤，这样在JOIN过程中可能会输出大量结果，再对这些结果进行过滤，比较耗时。可以进行优化，将WHERE条件放在ON后，在JOIN的过程中，就对不满足条件的记录进行了预先过滤。

2.4.6.12 Hive的并行执行

同步执行hive的多个阶段，hive在执行过程，将一个查询转化成一个或者多个阶段。某个特定的job可能包含众多的阶段，而这些阶段可能并非完全相互依赖的，也就是说可以并行执行的，这样可能使得整个job的执行时间缩短。hive执行开启：set hive.exec.parallel=true

2.4.6.13 Hive优化---数据倾斜

• 操作
    • Join
    • Group by
    • Count Distinct
• 原因
    • key分布不均导致的
    • 人为的建表疏忽
    • 业务数据特点
• 症状
    • 任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。
    • 查看未完成的子任务，可以看到本地读写数据量积累非常大，通常超过10GB可以认定为发生数据倾斜。
• 倾斜度
    • 平均记录数超过50w且最大记录数是超过平均记录数的4倍。
    • 最长时长比平均时长超过4分钟，且最大时长超过平均时长的2倍。
• 万能方法

    • hive.groupby.skewindata=true

2.4.6.14 Hive优化---数据倾斜--大小表关联

• 原因
• Hive在进行join时，按照join的key进行分发，而在join左边的表的数据会首先读入内存，如果左边表的key相对分散，读入内存的数据会比较小，join任务执行会比较快；而如果左边的表key比较集中，而这张表的数据量很大，那么数据倾斜就会比较严重，而如果这张表是小表，则还是应该把这张表放在join左边。
• 思路
    • 将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率
    • 使用map join让小的维度表先进内存。
• 方法

    • Small_table join big_table

2.4.6.15 Hive优化---数据倾斜--大大表关联

• 原因
    • 日志中有一部分的userid是空或者是0的情况，导致在用user_id进行hash分桶的时候，会将日志中userid为0或者空的数据分到一起，导致了过大的斜率。
• 思路
    • 把空值的key变成一个字符串加上随机数，把倾斜的数据分到不同的reduce上，由于null值关联不上，处理后并不影响最终结果。
• 方法
    • on case when (x.uid = '-' or x.uid = '0‘ or x.uid is null) then concat('dp_hive_search',rand()) else x.uid

       end = f.user_id;

2.4.6.16 Hive优化---数据倾斜--大大表关联（业务削减）

• 案例
    • Select * from dw_log t join dw_user t1 on t.user_id=t1.user_id
    • 现象：两个表都上千万，跑起来很悬
• 思路
    • 当天登陆的用户其实很少
• 方法
    • Select/*+MAPJOIN(t12)*/ *
    • from dw_log t11
    • join (
    •     select/*+MAPJOIN(t)*/ t1.*
    •     from (
    •         select user_id from dw_log group by user_id
    •     ) t
    •     join dw_user t1
    •     on t.user_id=t1.user_id
    • ) t12

    • on t11.user_id=t12.user_id

2.4.6.17 Hive优化---数据倾斜--聚合时存在大量特殊值

• 原因
    • 做count distinct时，该字段存在大量值为NULL或空的记录。
• 思路
    • count distinct时，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1。
    • 如果还有其他计算，需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进行union
• 方法
    • select cast(count(distinct(user_id))+1 as bigint) as user_cnt
    •     from tab_a

    •     where user_id is not null and user_id <> ''

2.4.6.18 Hive优化---数据倾斜-- 空间换时间

• 案例
    • Select day,count(distinct session_id),count(distinct user_id) from log a group by day
• 问题
    • 同一个reduce上进行distinct操作时压力很大
• 方法
    • select day,
    •     count(case when type='session' then 1 else null end) as session_cnt,
    •     count(case when type='user' then 1 else null end) as user_cnt
    • from (
    •     select day,session_id,type
    •     from (
    •         select day,session_id,'session' as type
    •         from log
    •         union all
    •         select day user_id,'user' as type
    •         from log
    •     )
    •     group by day,session_id,type
    • ) t1

    • group by day

（三）Hive环境搭建和部署

Hive环境搭建

（四）Hive的基本操作--DDL操作

4.1 创建表

建表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name

   [(col_name data_type[COMMENT col_comment], ...)]

   [COMMENT table_comment]

   [PARTITIONED BY (col_namedata_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]

说明：

1、CREATETABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

2、EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

3、 LIKE 允许用户复制现有的表结构，但是不复制数据。

4、 ROW FORMAT DELIMITED [FIELDS TERMINATED BY char][COLLECTION ITEMS TERMINATED BY char]

       [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, ...)]

ROW FORMAT DELIMITED 用来设置创建的表在加载数据的时候，支持的列分隔符；用户在建表的时候可以自定义SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过 SerDe 确定表的具体的列的数据。

5、 STORED AS

SEQUENCEFILE|TEXTFILE|RCFILE

如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED ASSEQUENCEFILE。

6、CLUSTERED BY

对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

把表（或者分区）组织成桶（Bucket）有两个理由：

（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

具体实例

1、  创建内部表mytable。

2、  创建外部表pageview。

 

3、  创建分区表invites。

create table student_p(Sno int,Sname string,Sex string,Sage int,Sdept string) partitioned by(part string) row format delimited fields terminated by ','stored as textfile;

4、  创建带桶的表student。

4.2 修改表

4.2.1 增加/删除分区

语法结构
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION'location1' ] partition_spec [ LOCATION 'location2' ] ...
partition_spec:
: PARTITION (partition_col = partition_col_value, partition_col =partiton_col_value, ...)

ALTER TABLE table_name DROP partition_spec, partition_spec,...

 具体实例

alter table student_p add partition(part='a') partition(part='b');

4.2.2 重命名表

ü  语法结构
ALTER TABLE table_name RENAME TO new_table_name

具体实例

4.2.3 增加/更新列

语法结构
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type[COMMENT col_comment], ...)
    注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。  
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_namecolumn_type [COMMENT col_comment] [FIRST|AFTER column_name]

具体实例

4.3 显示命令

show tables
show databases
show partitions
show functions
desc extended t_name;

desc formatted table_name;

（五）Hive的基本操作--DML操作

5.1 Load

语法结构

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO

TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

说明：

1、 Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。

2、 filepath：

相对路径，例如：project/data1

绝对路径，例如：/user/hive/project/data1

包含模式的完整 URI，列如：

hdfs://namenode:9000/user/hive/project/data1

3、 LOCAL关键字

如果指定了 LOCAL，那么：

load 命令会去查找本地文件系统中的filepath。如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。

load 命令会将 filepath中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置。

如果没有指定 LOCAL 关键字，如果 filepath 指向的是一个完整的 URI，hive 会直接使用这个 URI。否则：如果没有指定 schema 或者 authority，Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI。

如果路径不是绝对的，Hive 相对于/user/进行解释。

Hive 会将 filepath 中指定的文件内容移动到 table （或者 partition）所指定的路径中。

4、  OVERWRITE 关键字

如果使用了OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

  具体实例

1、 加载相对路径数据。

2、 加载绝对路径数据。

3、 加载包含模式数据。

4、 OVERWRITE关键字使用。

5.2 insert

将查询结果插入Hive表

语法结构

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1,partcol2=val2 ...)] select_statement1 FROM from_statement

 

Multiple inserts:

FROM from_statement

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1,partcol2=val2 ...)] select_statement1

[INSERT OVERWRITE TABLE tablename2 [PARTITION ...]select_statement2] ...

 

Dynamic partition inserts:

INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1],partcol2[=val2] ...) select_statement FROM from_statement

 具体实例

1、基本模式插入。

2、多插入模式。

3、自动分区模式。

导出表数据

语法结构

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...

multiple inserts:

FROM from_statement

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]...

 具体实例

1、导出文件到本地。

说明：

数据写入到文件系统时进行文本序列化，且每列用^A来区分，\n为换行符。用more命令查看时不容易看出分割符，可以使用: sed-e 's/\x01/|/g' filename[dht1] 来查看。

2、导出数据到HDFS。

5.3 SELECT    

基本的Select操作

语法结构

SELECT [ALL | DISTINCT] select_expr, select_expr, ...

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list [HAVING condition]]

[CLUSTER BY col_list

  |[DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]

]

[LIMIT number]

注：1、order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。

2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。

3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。

4、（Cluster by字段) 除了具有Distribute by的功能外，还会对该字段进行排序。

因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by

 

分桶表的作用：最大的作用是用来提高join操作的效率；

（思考这个问题：

select a.id,a.name,b.addr from a join b ona.id = b.id;

如果a表和b表已经是分桶表，而且分桶的字段是id字段

做这个join操作时，还需要全表做笛卡尔积吗？）

 

具体实例

1、获取年龄大的3个学生。

2、查询学生信息按年龄，降序排序。

3、按学生名称汇总学生年龄。

（六）Hive Join

  语法结构
  join_table:
  table_reference JOINtable_factor [join_condition]
  | table_reference{LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
  | table_reference LEFT SEMIJOIN table_reference join_condition
Hive支持等值连接（equalityjoins）、外连接（outer joins）和（left/rightjoins）。Hive 不支持非等值的连接，因为非等值连接非常难转化到 map/reduce 任务。
另外，Hive 支持多于 2 个表的连接。

写 join 查询时，需要注意几个关键点：

6.1  只支持等值join

例如：

  SELECT a.* FROMa JOIN b ON (a.id = b.id)

  SELECT a.* FROM a JOIN b

    ON (a.id = b.id AND a.department =b.department)

是正确的，然而:

  SELECT a.* FROM a JOIN b ON (a.id>b.id)

是错误的。

6.2 可以 join 多于 2 个表。

例如
SELECT a.val,b.val, c.val FROM a JOIN b  ON (a.key =b.key1)
                                                       JOIN c ON (c.key = b.key2)

如果join中多个表的join key 是同一个，则 join 会被转化为单个map/reduce 任务，例如：

SELECT a.val,b.val, c.val FROM a JOIN b 

                                                  ON (a.key =b.key1) JOIN c

                                                  ON (c.key =b.key1)

被转化为单个 map/reduce 任务，因为 join 中只使用了 b.key1 作为 join key。

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key =b.key1)

                                                                       JOIN c ON(c.key = b.key2)

而这一 join 被转化为2 个 map/reduce 任务。因为 b.key1 用于第一次 join 条件，而 b.key2 用于第二次 join。

6.3．join 时，每次 map/reduce 任务的逻辑

    reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。例如：
 SELECT a.val, b.val, c.val FROM a
                                                   JOIN b ON(a.key = b.key1) 
                                                     JOIN c ON (c.key = b.key1)
所有表都使用同一个 join key（使用 1 次 map/reduce 任务计算）。Reduce 端会缓存 a 表和 b 表的记录，然后每次取得一个 c 表的记录就计算一次 join 结果，类似的还有：
  SELECT a.val,b.val, c.val FROM a
                                                     JOIN b ON(a.key = b.key1) 
                                                     JOIN c ON (c.key = b.key2)

这里用了 2 次map/reduce 任务。第一次缓存 a 表，用 b 表序列化；第二次缓存第一次 map/reduce 任务的结果，然后用 c 表序列化。

6.4  LEFT，RIGHT 和 FULLOUTER 关键字用于处理 join 中空记录的情况

例如：
  SELECT a.val,b.val FROM  a LEFT OUTER  JOIN b ON (a.key=b.key)
对应所有 a 表中的记录都有一条记录输出。输出的结果应该是 a.val, b.val，当 a.key=b.key 时，而当 b.key 中找不到等值的 a.key 记录时也会输出:
          a.val, NULL
所以 a 表中的所有记录都被保留了；
“a RIGHT OUTER JOIN b”会保留所有 b 表的记录。
 
Join 发生在 WHERE 子句之前 。如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件——或是在join 子句中写。这里面一个容易混淆的问题是表分区的情况：
  SELECT a.val,b.val FROM a
                                 LEFT OUTER JOINb ON (a.key=b.key)
                                 WHEREa.ds='2009-07-07' AND b.ds='2009-07-07'
会 join a 表到 b 表（OUTER JOIN），列出 a.val 和 b.val 的记录。WHERE 从句中可以使用其他列作为过滤条件。但是，如前所述，如果 b 表中找不到对应 a 表的记录，b 表的所有列都会列出 NULL， 包括 ds 列 。也就是说，join 会过滤 b 表中不能找到匹配a 表 join key 的所有记录。这样的话，LEFTOUTER 就使得查询结果与 WHERE 子句无关了。解决的办法是在 OUTER JOIN 时使用以下语法：
  SELECT a.val,b.val FROM a LEFT OUTER JOIN b
                                 ON (a.key=b.keyAND
                                         b.ds='2009-07-07' AND
                                         a.ds='2009-07-07')
这一查询的结果是预先在 join 阶段过滤过的，所以不会存在上述问题。这一逻辑也可以应用于 RIGHT 和 FULL 类型的join 中。
 
Join 是不能交换位置的。 无论是 LEFT 还是RIGHT join，都是左连接的。
  SELECT a.val1,a.val2, b.val, c.val   FROM a
                                                                        JOIN b ON(a.key = b.key)
                                                                        LEFT OUTER JOINc ON (a.key = c.key)

先 join a 表到 b 表，丢弃掉所有 join key 中不匹配的记录，然后用这一中间结果和 c 表做 join。这一表述有一个不太明显的问题，就是当一个 key 在 a 表和 c 表都存在，但是 b 表中不存在的时候：整个记录在第一次 join，即 a JOIN b 的时候都被丢掉了（包括a.val1，a.val2和a.key），然后我们再和c 表 join 的时候，如果 c.key 与 a.key 或 b.key 相等，就会得到这样的结果：NULL, NULL, NULL, c.val

  具体实例

1、  获取已经分配班级的学生姓名。

2、  获取尚未分配班级的学生姓名。

3、LEFT  SEMI  JOIN是IN/EXISTS的高效实现。

（七）Hive Shell 参数

7.1 Hive 命令行

语法结构
hive [-hiveconf x=y]* [<-i filename>]* [<-f filename>|<-equery-string>] [-S]
说明：
1、 -i 从文件初始化HQL。
2、 -e从命令行执行指定的HQL
3、 -f 执行HQL脚本
4、 -v 输出执行的HQL语句到控制台
5、 -p <port> connect to Hive Server onport number
6、 -hiveconf x=y Use this to set hive/hadoopconfiguration variables.
具体实例

1、运行一个查询。

2、运行一个文件。

3、运行参数文件。

7.2 Hive参数配置方式

Hive参数大全链接地址

开发Hive应用时，不可避免地需要设定Hive的参数。设定Hive的参数可以调优HQL代码的执行效率，或帮助定位问题。然而实践中经常遇到的一个问题是，为什么设定的参数没有起作用？这通常是错误的设定方式导致的。

对于一般参数，有以下三种设定方式：

l  配置文件

l  命令行参数

l  参数声明

7.2.1  配置文件：Hive的配置文件包括

l 用户自定义配置文件：$HIVE_CONF_DIR/hive-site.xml

l 默认配置文件：$HIVE_CONF_DIR/hive-default.xml

用户自定义配置会覆盖默认配置。

另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。

配置文件的设定对本机启动的所有Hive进程都有效。

7.2.2  命令行参数

启动Hive（客户端或Server方式）时，可以在命令行添加-hiveconf param=value来设定参数，例如：

bin/hive-hiveconf hive.root.logger=INFO,console

这一设定对本次启动的Session（对于Server方式启动，则是所有请求的Sessions）有效。

7.2.3  参数声明

可以在HQL中使用SET关键字设定参数，例如：

setmapred.reduce.tasks=100;

这一设定的作用域也是session级的。

上述三种设定方式的优先级依次递增。即参数声明覆盖命令行参数，命令行参数覆盖配置文件设定。注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在Session建立以前已经完成了。

（八） Hive函数

点击打开链接

（九）Hive练习

案例一：导入本地Local的数据，并进行简单统计

• 准备数据

• 设计schema，建库，建表

• 导入

案例二：两表的Join

• 执行命令：select a.*, b.* from w_a a join w_b b on a.usrid=b.usrid;

案例三：UDF

• UDF：User-Defined-Function 用户自定义函数
• UDF函数可以直接应用于select语句，对查询结构做格式化处理后，再输出内容。
• 编写UDF函数的时候需要注意一下几点：
    – 自定义UDF需要继承org.apache.hadoop.hive.ql.UDF。
    – 需要实现evaluate函。

    – evaluate函数支持重载。

案例四：从HDFS中导入

• 执行命令：
– LOAD DATA INPATH '/user_name.data.utf.txt' OVERWRITE INTO TABLE u_info
• overwrite表示加载的数据会覆盖原来的内容

• 对比本地的方式：LOAD DATA LOCAL INPATH

案例五：利用Insert命令导入数据

• 执行命令（例子）：
    insert into table table1 select usrid, age from w_a limit 3;
• 也可以支持动态分区插入：

    insert into table test1 partition(c) select * from test2;

案例六：直接通过查询插入

• 执行命令（例子）：

• create table test2 as select * from test1;

案例六：数据导出（导出为本地文件）

• 执行命令（例子）：

• insert overwrite local directory '/home/badou/hive_test/1.txt' select usrid, sex from w_a;

案 例 七 ： 数 据 导 出 （ 导 出 为 H D F S 文 件 ）

• 执行命令（例子）：insert overwrite directory '/hive_output' select * from w_b;

案 例 八 ： P a r t i t i o n

• partition是Hive提供的一种机制：用户通过指定一个或多个partitionkey，决定数据存放方式，进而优化数据的查询,一个表可以指定多个partition key，每个partition在hive中以文件夹的形式存在。

案 例 九 ： T r a n s f o r m

• transform功能部分可以用UDF替代，但是如果拼接的字段是根据上一次查询的结果时，UDF就不能用
，UDF只能用在本行操作

• transform功能缺点是效率底了点

案例十：Hive整合Hbase

• 创建Hbase表：
    – create 'classes','user'
• 加入数据：
    – put 'classes','001','user:name','jack'
    – put 'classes','001','user:age','20'
    – put 'classes','002','user:name','liza'

    – put 'classes','002','user:age','18'

• 创建Hive表并验证：
    – create external table classes(id int, name string, age int)
    – STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
    – WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,user:name,user:age")
    – TBLPROPERTIES("hbase.table.name" = "classes");
• 再添加数据到Hbase：
    – put 'classes','003','user:age','1820183291839132'

（十）Hive实战

Hive实战案例