Hive学习总结及应用

一、文档说明

熟悉Hive功能，了解基本开发过程，及在项目中的基本应用。

注意：本文档中但凡有hive库操作的语句，其后面的“；”是语句后面的，非文档格式需要。每个hive语句都要以“；”来结束，否则将视相邻两个分号“；”之间的所有语句为一条语句。

二、Hive（数据提取）概述

Hive是构建在HDFS 和Map/Reduce之上的可扩展的数据仓库。是对HADOOP的Map-Reduce进行了封装，类似于sql语句（hive称之为HQL）计算数据从而代替编写代码对mapreduce的操作，数据的来源还是HDFS上面的文件。

Hive中的表可以分为托管表和外部表，托管表的数据移动到数据仓库目录下，由Hive管理，外部表的数据在指定位置，不在Hive 的数据仓库中，只是在Hive元数据库中注册。创建外部表采用“create external tablename”方式创建，并在创建表的同时指定表的位置。

Hive本身是没有专门的数据存储格式，也没有为数据建立索引，只需要在创建表的时候告诉Hive数据中的列分隔符和行分隔符，Hive就可以解析数据。所以往Hive表里面导入数据只是简单的将数据移动到表所在的目录中（如果数据是在HDFS上；但如果数据是在本地文件系统中，那么是将数据复制到表所在的目录中）。

三、Hive的元数据

Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。由于Hive的元数据需要不断的更新、修改，而HDFS系统中的文件是多读少改的，这显然不能将Hive的元数据存储在HDFS中。目前Hive将元数据存储在数据库中，如Mysql、Derby中。

Hive metastore 三种存储方式：

Hive的meta 数据支持以下三种存储方式，其中两种属于本地存储，一种为远端存储。远端存储比较适合生产环境。

1、使用derby数据库存储元数据（内嵌的以本地磁盘作为存储），这称为“内嵌配置”。

这种方式是最简单的存储方式，只需要在或做如下配置便可。使用derby存储方式时，运行hive会在当前目录生成一个derby文件和一个metastore_db目录。这种存储方式的弊端是在同一个目录下同时只能有一个hive客户端能使用数据库，否则会提示如下错误（这是一个很常见的错误）。

2、使用本机mysql服务器存储元数据，这称为“本地metastore”。这种存储方式需要在本地运行一个mysql服务器，

3、使用远端mysql服务器存储元数据。这称为“远程metastore”。这种存储方式需要在远端服务器运行一个mysql服务器，并且需要在Hive服务器启动meta服务。

四、Hive数据导入方式

1、从本地文件系统中导入数据到Hive表

hive> load data local inpath '' into table wyp;

2、HDFS上导入数据到Hive表

hive> load data inpath '/home/wyp/' into table wyp;

3、从别的表中查询出相应的数据并导入到Hive表中

hive> insert into table test

> partition (age='25')

> select id, name, tel

> from wyp;

五、Hive数据导出方式

1、导出到本地文件系统

hive> insert overwrite local directory '/home/wyp/wyp' > select * from wyp;

[boco@cloud216~]$hive -e "select * from wyp" >> local/

2、导出到HDFS中

hive> insert overwrite directory '/home/wyp/hdfs'

> select * from wyp;

3、导出到Hive的另一个表中

hive> insert into table test

> partition (age='25')

> select id, name, tel

> from wyp;

六、HiveQL的常用操作

HiveQL是一种类似SQL的语言，它与大部分的SQL语法兼容，但是并不完全支持SQL标准，如HiveQL不支持更新操作，也不支持索引和事务，它的子查询和join操作也很局限。

#hive 启动

1、退出hive

hive> quit;

2、查看数据库

hive> show databases;

3、创建数据库

hive> create database test;

4、创建数据库，并设置其存放的位置，注意该路径在hdfs下

hive> CREATE DATABASE financials

> LOCATION '/my/preferred/directory';

5、使用哪个数据库

hive> use default;

6、创建表

hive>create table t1 (key string);

7、判断一个表是外部表还是内部表

hive>DESCRIBE EXTENDED tablename；

8、查看该数据库中的所有表

hive>show tables;

9、显示表结构

hive>desc tablename;

hive> desc extended tmr_format;

10、查看表有哪些分区

hive>show partitions t1;

11、删除分区：ALTER TABLE table_name DROP partition_spec

hive>ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');

12、删除表

hive>drop table t1 ;

13、移动hdfs中数据到t1表中

hive>load data inpath ‘/root/’ into table t1;

14、上传本地数据到hdfs中

hive>load data local inpath ‘/root/’ into table t1;

15、查询当前linux文件夹下的文件

hive> !ls;

16、查询当前hdfs文件系统下’/'目录下的文件

hive> dfs -ls /;

?以非交互式模式执行sql文件

?HIVE_HOME/bin/hive -f /home/my/

?在进入交互模式之前，执行初始化sql文件

?HIVE_HOME/bin/hive -i /home/my/

六、Hive内部表、外部表

1. 内部表：

create table tt (name string , age string) location '/input/table_data';

上传hdfs数据到表中：

load data inpath '/input/data' into table tt;

此时会将hdfs上的/input/data目录下的数据转移到/input/table_data目录下。

删除tt表后，会将tt表的数据和元数据信息全部删除，即最后/input/table_data下无数据，当然/input/data下再上一步已经没有了数据！

如果创建内部表时没有指定location，就会在/user/hive/warehouse/下新建一个表目录，其余情况同上。

注意的地方就是：load data会转移数据！

2. 外部表：

create external table et (name string , age string);

此时，会在/user/hive/warehouse/新建一个表目录et

load data inpath '/input/edata' into table et;

此时会把hdfs上/input/edata/下的数据转到/user/hive/warehouse/et下，删除这个外部表后，/user/hive/warehouse/et下的数据不会删除，但是/input/edata/下的数据在上一步load后已经没有了！数据的位置发生了变化！本质是load一个hdfs上的数据时会转移数据！

3. 其他：

（1）加上location用法一样，只不过表目录的位置不同而已。

（2）加上partition用法也一样，只不过表目录下会有分区目录而已。

（3）load data local inpath直接把本地文件系统的数据上传到hdfs上，有location上传到location指定的位置上，没有的话上传到hive默认配置的数据仓库中。

七、Hive日期函数

UNIX时间戳转日期函数: from_unixtime

日期转年函数: year

语法: year(string date)

返回值: int

说明: 返回日期中的年。

举例：

hive> select year(’2011-12-08 10:03:01′) from dual; 2011

hive> select year(’2012-12-08′) from dual;

2012

日期转天函数: day

语法: day (string date)

返回值: int

说明: 返回日期中的天。

举例：

hive> select day(’2011-12-08 10:03:01′) from dual; 8

hive> select day(’2011-12-24′) from dual;

24

2014-04-25 08:47:31

select to_date(from_unixtime(unix_timestamp())) from enodeb limit 1;

2014-04-25

select hour(from_unixtime(unix_timestamp())) from enodeb limit 1;

8

select from_unixtime(unix_timestamp(),'yyyyMMddHH') from enodeb limit 1;

08

select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') from

enodeb limit 1;

2014-04-25 09:15:58

七、Hive开发配置（java）

在Hive的jar包中，“负责提供JDBC接口，客户端展示程序有了这个包，就可以把Hive当成一个数据库来使用。建立与Hive的连接：

默认只能连接到default，通过上面的两行代码建立连接后，其他的操作与传统数据库无太大差别。

详细配置参见《细细品味Hadoop_Hadoop集群（第14期）_Hive 应用开发》第三章节。

八、数据类型

原子数据类型

复杂数据类型

八、Hive表分区

a、单分区建表语句：create table day_table (id int, content string) partitioned by (dt string);单分区表，按天分区，在表结构中存在id，content，dt三列。以dt为文件夹区分

b、双分区建表语句：create table day_hour_table (id int, content string) partitioned by (dt string, hour string);双分区表，按天和小时分区，在表结构中新增加了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')

location '/path/'

删除分区语法：

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='09');

数据加载进分区表中语法：

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

LOAD DATA INPATH '/user/' 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 >= '2008-08-08';

查看分区语句：

hive> show partitions day_hour_table; OK dt=2008-08-08/hour=08 dt=2008-08-08/hour=09 dt=2008-08-09/hour=09

八、Hsq执行过程（explain）

Explain + 具体执行的sql

八、问题

1、hive数据导入的中文乱码问题

解决：hive属于java体系，数据编码是unicode，所以从生产库抽过来的GB18030极其子编码集的数据，load到hive是可能出现乱码的。将GBK转为UTF-8

[boco@cloud216 ]$ iconv -f GBK -t UTF8 > ;

2、hive与传统数据库的性能差距有多大

3、Hql在生产环境如何进行调度控制（被调起），如何控制性能

4、先把hive跑起来（桦林）

By zhaofeng 2014-3-25 beijin（一

天）