Hive学习

Hive简介

使用SQL来快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，学习成本低，十分适合数据仓库的统计分析。

Hive是基于Hadoop的一个【数据仓库工具】，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。数据仓库是用来查询分析的数据库，基本不用来做修改，删除操作。

Hive架构原理

（1） 用户接口主要有三个：CLI，Client和WUI。

其中常用的是CLI命令行，Cli启动的时候，会同时启动一个Hive副本；

Client是Hive的客户端，用户连接值Hive Server。在启动Client模式的时候，需要指出Hive Server所在节点,并且在该节点启动Hive Server。

WUI是通过浏览器访问Hive。

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

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

（4） Hive的数据存储在HDFS中，大部分的查询、计算由MapReduce完成（包含*的查询，比如select * from tbl 不会生成MapReduce任务）。

流程：

①HQL通过命令行或者客户端提交到Driver（驱动）

②Driver与Metastore交互找到与表相关的元数据信息

③Compiler解释器编译器转换成MapReduce任务

④Hadoop执行MapReduce任务

Hive 使用ANTLR词法语法分析工具解析hql

①解释器：解释器的作用是将HiveSQL语句转换为抽象语法树（AST）

②编译器：编译器是将语法树编译为逻辑执行计划

③优化器：优化器是对逻辑执行计划进行优化

④执行器：执行器是调用底层的运行框架执行逻辑执行计划

Hive搭建及三种模式

A、内嵌模式（元数据保存在内嵌的derby中，允许一个会话链接，尝试多个会话链接时会报错）

B、单用户模式

安装mysql 替代derby存储元数据

C、远程模式/多用户模式

在服务器端启动MetaStoreServer，客户端利用Thrift协议通过MetaStoreServer访问元数据库

内部表与外部表

外部表：外部关键字EXTERNAL允许您创建一个表,并提供一个位置,以便hive不使用这个表的默认位置。这方便如果你已经生成的数据。当删除一个外部表,表中的数据不是从文件系统中删除。外部表指向任何HDFS的存储位置,而不是存储在配置属性指定的文件夹[hive.metastore.warehouse.dir.]中

内部表 删除连带表中数据一起删除

更新数据

UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

删除数据

DELETE FROM tablename [WHERE expression]

注意: 更新和删除需要hive开启ACID机制

四种插入/导入数据

Hive不能很好的支持用insert语句一条一条的进行插入操作，不支持update操作。数据是以load的方式加载到建立好的表中。数据一旦导入就不可以修改。

种：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

第二种：

创建person2表，然后从表person1.....中导入数据：

INSERT OVERWRITE TABLE person2 [PARTITION(dt='2008-06-08', country)]

​ SELECT id,name, age From ppt;

第三种：

FROM person t1

INSERT OVERWRITE TABLE person1 [PARTITION(dt='2008-06-08', country)]

​ SELECT t1.id, t1.name, t1.age ;

【from放前面好处就是后面可以插入多条语句 】

FROM abc t1,sufei t2

INSERT OVERWRITE TABLE qidu

SELECT t1.id,t1.name,t1.age,t1.likes,t1.address

INSERT OVERWRITE TABLE wbb

SELECT t2.id,t2.name,t2.age,t2.likes,t2.address;

注意： 使用多插入模式时，不能插入相同的表

第四种：

本地load数据和从HDFS上load加载数据的过程有什么区别？

本地： local 会自动复制到HDFS上的hive的目录下

Hdfs导入 后移动到hive的目录下

注意：查看用谷歌浏览器 360有时有bug

查询数据并保存

A、保存数据到本地：

例： insert overwrite local directory '/opt/datas/hive_exp_emp2'

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

select * from db_1128.emp ;

hive命令行中查看linux上的数据：

! cat /opt/datas/hive_exp_emp2/000000_0;

B、保存数据到HDFS上：

例： insert overwrite directory '/user/beifeng/hive/hive_exp_emp'

select * from db_1128.emp ;

hive命令行中查看HDFS上的数据：

dfs -cat /user/beifeng/hive/hive_exp_emp/000000_0;

Hive的group by\join(left join right joinHive的group by\join(left join right join等)\having\sort by \order by等操作和MySQL没有什么大的区别：

Hive分区

Hive的分区partition

假如现在我们公司一天产生3亿的数据量，那么为了方便管理和查询，此时可以建立分区（可按日期 部门等具体业务分区）。分门别类的管理。

注意：可在表定义时创建partition ，也可在以后添加分区

分区分为：单分区和多分区

创建分区

A、单分区建表语句：create table day_table(id int, content string) partitioned by (dt string) row format delimited fields terminated by ',';

【单分区表，如：按天分区，在表结构中存在id，content，dt三列；以dt为文件夹区分】

B、双分区建表语句：create table day_hour_table (id int, content string) partitioned by (dt string, hour string) row format delimited fields terminated by ',';

【双分区表，按天和小时分区，在表结构中新增加了dt和hour两列；先以dt为文件夹，再以hour子文件夹区分】

注意：在创建 删除多分区等操作时一定要注意分区的先后顺序，他们是父子节点的关系。分区字段不要和表字段相同

添加分区表的分区

（表已创建，在此基础上添加分区）：

ALTER TABLE table_name ADD partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...

例： ALTER TABLE day_table ADD PARTITION (dt='2008-08-08', hour='08')

删除分区

语法：ALTER TABLE table_name DROP partition_spec, partition_spec,...

– 用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。

例：ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='08');

数据加载进分区表中

语法：LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1,partcol2=val2 ...)]

例： LOAD DATA INPATH '/user/pv.txt' INTO TABLE day_hour_table PARTITION(dt='2008-08-08', hour='08');

LOAD DATA local INPATH '/user/hua/*' INTO TABLE day_hour partition(dt='2010-07- 07');

当数据被加载至表中时，不会对数据进行任何转换。Load操作只是将数据复制至Hive表对应的位置。数据加载时在表下自动创建一个目录基于分区的查询的语句：SELECT day_table.* FROM day_table WHERE day_table.dt >= '2008-08-08';

查看分区语句

hive> show partitions day_hour_table;

重命名分区

ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec

ALTER TABLE day_table PARTITION (tian='2018-05-01') RENAME TO PARTITION (tain='2018-06-01');

动态分区--注意外部表

1.在本地文件/opt/soft/a.txt中写入以下4行数据： 1 zs 18 bj 2 ls 19 sh 3 cc 20 hz 4 yy 23 bj 5 mm 22 sh 6 lal 17 hz

2.建立非分区表（中间表）并加载数据

create table mid_psn(id int,name string,age int,address string) row format delimited fields terminated by ',';

LOAD DATA LOCAL INPATH '/opt/soft/a.txt' INTO TABLE mid_psn;

3.建立外部分区表并动态加载数据 （注意删除外部表的相关事项）

create table psn(id int, name string,age int) partitioned by (address string) row format delimited fields terminated by ',';

4.将严格模式改为非严格模式： set hive.exec.dynamic.partition.mode=nonstrict;

加载数据到动态分区

from mid_psn insert overwrite table psn partition(address) select id,name,age,address distribute by address;

这时候就需要使用动态分区来实现，使用动态分区需要注意设定以下参数：

hive.exec.dynamic.partition

默认值：false

是否开启动态分区功能，默认false关闭。

使用动态分区时候，该参数必须设置成true;

hive.exec.dynamic.partition.mode

默认值：strict

动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。

一般需要设置为nonstrict

hive.exec.max.dynamic.partitions.pernode

默认值：100

在每个执行MR的节点上，大可以创建多少个动态分区。

该参数需要根据实际的数据来设定。

比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。

hive.exec.max.created.files

默认值：100000

整个MR Job中，大可以创建多少个HDFS文件。

一般默认值足够了，除非你的数据量非常大，需要创建的文件数大于100000，可根据实际情况加以调整。

hive.error.on.empty.partition

默认值：false

当有空分区生成时，是否抛出异常。

一般不需要设置。

Beeline、Hiveserver2与JDBC

Beeline与HiveServer2

服务端 Hiveserver2直接启动

客户端连接服务端的两种方式

1、 beeline -u jdbc:hive2://node5:10000/default -n root

2、 先启动 beeline，然后在交互式界面上使用命令：

​ !connect jdbc:hive2://node5:10000 root 123

Hive的JDBC

一般是平台使用 展示或接口，服务端启动hiveserver2后，在java代码中通过调用hive的jdbc访问默认端口10000进行连接、访问

分桶

分桶表及应用场景

分桶表是对列值取哈希值的方式，将不同数据放到不同文件中存储。

对于hive中每一个表、分区都可以进一步进行分桶。

由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中。

适用场景：

数据抽样（ sampling ）、map-join

开启支持分桶

set hive.enforce.bucketing=true;

默认：false；设置为true之后，mr运行时会根据bucket的个数自动分配reduce task个数。（用户也可以通过mapred.reduce.tasks自己设置reduce任务个数，但分桶时不推荐使用）

注意：一次作业产生的桶（文件数量）和reduce task个数一致。

往分桶表中加载数据

insert into table bucket_table select columns from tbl;

insert overwrite table bucket_table select columns from tbl;

桶表 抽样查询

select * from bucket_table tablesample(bucket 1 out of 4 on columns);

TABLESAMPLE语法：

TABLESAMPLE(BUCKET x OUT OF y)

x：表示从哪个bucket开始抽取数据

y：必须为该表总bucket数的倍数或因子

创建普通表

例：

CREATE TABLE psn31( id INT, name STRING, age INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

测试数据：

1,tom,11

2,cat,22

3,dog,33

4,hive,44

5,hbase,55

6,mr,66

7,alice,77

8,scala,88

创建分桶表

CREATE TABLE psnbucket( id INT, name STRING, age INT)

CLUSTERED BY (age) INTO 4 BUCKETS

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

加载数据：

insert into table psnbucket select id, name, age from psn31;

抽样

select id, name, age from psnbucket tablesample(bucket 2 out of 4 on age);