hive研究学习整理

hive的认识

hive的设计目的是为了让那些精通sql技能的而java技能较弱的数据分析师能够利用hadoop进行各做数据分析。也就是mapreduce不需要每个都写java代码了，hive可以将用户输入的hiveQl脚本转化为一个或多个MapReduce作业在集群上运行。【pig和hive类似，只是不同的是pig使用的是pig latin语言接口提供，更加灵活学习成本高】，

hive与关系型数据库的区别

数据存储位置：hive是基于hadoop的，所以hive都在hdfs上存放，而数据库可以将数据保存在块设备或本地文件系统中

数据格式：对于hive的数据格式，用户可以自由定义行列的分隔符，由用户自己指定，而关系型数据库都有自己存储引擎，所有的数据都会安装一定的组织存储。

数据更新：hive主要是作为数据仓库，他们的特性都是“一次写入多次读取”，所以hive不支持数据的更新和添加，所有数据在加载时候已经确定不能修改；而关系型数据库可以通过update和insert语句进行操作。

索引：hive在加载数据的过程中不会对数据进行任何的处理，甚至不会对数据进行扫描，因此也没有对数据中的某些列建立索引。

执行：hive中大多数的查询执行时通过hadoop提供的MapReduce计算框架来实现的，而数据库通常有自己的执行引擎。

执行延迟：hive中没有索引，所以在查询数据时候必须进行全表扫描，因此延迟较高，另一个原因导致hive执行延迟较高的原因是MapReduce计算框架。MapReduce计算框架本身也具有较高的延迟，相对来说数据的执行延迟比较低。

可扩展性：hive是hadoop的，因此hive的可扩展性和hadoop的可扩展性是一致的，而数据库由于ACID语义的严格限制，扩展有限。

数据规模：hive建立在集群上并可以利用MapReduce进行并行计算，因此可以很大规模的数据，相应的，数据库可以支持数据规模比较小。
以上的对比可以发现，对于面向小数据量，查询延迟敏感的关系数据库更适应在线数据处理。而面向大数据量，查询延迟不敏感的hive更适用于离线数据分析。
Hive集成Mysql作为元数据
默认情况下，Hive元数据保存在内嵌的 Derby 数据库中，只能允许一个会话连接，只适合简单的测试。为了支持多用户多会话，则需要一个独立的元数据库，我们使用 MySQL 作为元数据库，这里公司已经配置好了。
建立专用的hive数据库，记得创建时用刚才创建的“hive”账号登陆。

```
mysql> create database hive;
mysql> exit;
hive@node01: mysql -uhive -pmysql
```

在Hive的conf目录下的文件“hive-site.xml”中增加如下配置:
```

 <property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://node03.soyoung.com/hive?createDatabaseIfNotExist=true</value>
</property>
 <property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>hive</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>mysql</value>
</property>

```
启动hive和mysql那些不啰嗦了
1)在Hive上建立数据表
```

hive> CREATE TABLE xp(id INT,name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
```

2)从 MySQL 数据库上查看元数据信息
用到的 SQL 语句:
```

use hive; //使用 hive 数据库库//

show tables;//显示 hive 数据库中的数据表//

select * from TBLS;//查看 hive 的元数据信息//

到此Hive集成Mysql作为元数据已完成。



hive数据类型和存储格式

hive支持关系型数据库的大多数类型，也支持一些独有的数据类型，并且hive对于数据文件中的编码方式具有很大的灵活性。

基本类型:hive的基本类型和java的基本类型一一对应，hive是java编写的嘛^-^

复杂数据类型：主要是3种集合类型，
struct:如struct(first string,last string) 访问时候可以使用列名.first访问对应的数据值,

map：是一组键值对元组的集合map('first','secend','last')，可以通过访问键访问值，列名[first]

array:数组是一组具有相同类型的变量的集合，可以通过下标访问值，如第二个元素可以通过 列名[1]访问
hive使用

执行命令进入hive，先看看简单操作基本和mysql的操作相同。

show databases;
use tmp;
show tables;

创建hive的table
假设我们已经创建了自己的hive数据库

create database tmp;//不存在数据库的情况下//
create database if not exists tmp; 

create database tmp1 > location '/user/hadoop/tmp'
describe database tmp;

drop database if exists tmp casecade; //casecade表示删除时候将表一起删除//

这里我们创建一个hive的table，这里我写的相对多一定的类型。主要是为了使用时候不用再创建新的表。hive-site.xml中配置hive.metastore.warehouse.dir我的是/apps/hive/warehouse默认应该是/user/hive/warehouse

create table if not exists student (
name string,
age int,
cource array<string>,
body map<string,float>,
address struct<string:string,city:string,state:string>
)
comment 'the info of student' 
row format delimited 
fields terminated by '\001' 
collection items terminated by '002' 
map keys terminated by '\003' 
lines terminated by '\n' 
stored as textfile
location '/apps/hive/warehouse/tmp.db/student';

desc extended student; 或 desc formatted student;

//复制一张表结构//
create table if not exists tmp.student2 like tmp.student;
//如果建立表时候对数据格式没有太多的要求，可以全部使用hive默认的格式，某些子句可以省略不写，这样除了下边的制表符外别的都是hive的默认格式简写版本//
create table student (
name string,
age int,
cource array<string>,
body map<string,float>,
address struct<string:string,city:string,state:string>
) row format delimited 
fields terminated by '\t' 

创建hive的外部表
如果数据在被多个数据工具分析时候意味着这份数据的所有权不是由hive拥有，这时可以创建一个外部表（external table）指向这份数据。

create external table if not exists tmp.student_external (
name string,
age int,
cource array<string>,
body map<string,float>,
address struct<string:string,city:string,state:string>
)
location '/tmp/student_external';

desc extended student_external;//看看路径location:hdfs://name-ha/tmp/student_external//

//删除外部数据表，//
drop table student_external;
//再使用//
hive> dfs -ls /tmp;  //这里看到并没有删除创建的表的数据，这是因为hive认为没有完全拥有这份数据，所以hive只会删除外部表的元数据信息不删除该表的数据//
hive分区表
//学生信息表，建表语句没有什么不同，有区别的是通过partitioned by子句指定表按照学生家庭住址的city和province进行分区，此处注意的字段不能喝定义表的字段重合，否则会出现//
create table student_info(
student_id string,
name string,
age int,
sex string,
father_name string,
//province string,   这个clumn不能喝partition的冲突要不会出现“Column repeated in partitioning columns”//
mother_name string)
partitioned by (province string,city string);

show partitions student_info;

hive中标是以目录的形式存放在hdfs上，而表的分区则是以表目录的子目录存在，例如学生信息表的数据是这样的。

/apps/hive/warehouse/student_info/province=shanxi/city=lvliang

/apps/hive/warehouse/student_info/province=shanxi/city=yuncheng

...

/apps/hive/warehouse/student_info/province=beijing/city=beijing

/apps/hive/warehouse/student_info/province=shanxi/city=lvliang

...

可以看见分区表水平切分为若干个文件，而切分的规则就是分区字段，如上是按照省市不同分别保存在不同的目录下。 分区表和未分区表的性能差别很大，看看查询

select * from student_info where province='shanxi' and city='lvliang';

想想也知道所有的数据都在一个目录下，所以hive只会扫描该文件夹下的数据，对于大数据集，直接命中的分区hive不会执行Mapreduce作业。

执行所有分区的查询将耗费集群巨大的时间和资源，对此可以将hive的安全措施设定为“strict”模式，这样如果一个针对分区表的查询没有对分区限制的话该作业将会被禁止提交，可以修改hive-site.xml文件的hive.mapred.mode配置项为strict或者在

hive>set hive.mapred.mode=strict

hive修改表

//增加分区（通常是外部表）这里在建立table时候要有分区建立//
alter table student_info add partition(province='sichuan',city='yaan') location '/tmp/student_info/sichuan/yaan';
//修改分区,修改的是已经存在的分区路径//
alter table student_info add partition(province='sichuan',city='yaan') location '/tmp/student_info/sichuan/XXX';
//删除分区//
alter table student_info drop partition(province='sichuan',city='yaan');
//修改列信息//
alter table student change column name enname string comment 'the student name';
//增加列//
alter table student_info add columns(new_col int,new_col2 string);
//删除或者替换[注意该命令删除了student_info表的所有列并重新定义了字段，由于只是改变了元数据，表并不会因此而改变。]//
alter table student_info replace columns(new_col int,new_col2 string);

alter table语句只是修改了表的元数据，所以一定要保证表的数据和修改后的元数据要匹配，否则将会变得不可用。

装载数据
在这里有详细操作http://janle.iteye.com/admin/blogs/2327870
通过查询语句向表中插入数据。



查询语句向表中插入数据
hive支持通过查询语句向表中插入数据。

```
insert overwrite table student_info test select * from source_table;
```

当有分区表时候必须指定分区。

```
insert overwrite table student_info partition(provice="shanxi",city='lvliang') test select * from source_table;
```

下边是比较很有用的特性，不过低版本【0.7以前】不支持，可以通过一次查询产生多个不相交的输出，记住下边不要加“；”。

```
from source_table
insert overwrite table student_info partition(provice="shanxi",city='lvliang')
select student_id ,name,age where student_id>=0 and student_id<100
insert overwrite table student_info partition(provice="shanxi",city='lvliang')
select student_id ,name,age where student_id>=100 and student_id<200
insert overwrite table student_info partition(provice="shanxi",city='lvliang')
select student_id ,name,age where student_id>=200 and student_id<300
```

这样对一次查询就可以将符合条件的数据插入test表的各个分区，非常方便，如果要使用这样的方式from子句要写在前面。

动态分区向表中插入数据
虽然可以通过一次查询产生多个不相交的输出，但是有些不方便的地方在于如果插入的分区非常多，hql将显得非常庞大，这里我就说说另一个强大的特性“动态分区”支持查询语句自动推断出需要创建的分区。
```

insert overwrite table student patition(time) select id,modify_time from source_table;
```

student表中的分区字段为time，hive会自动根据modify_time不同的值创建分区，值得注意的是hive会根据select语句的最后一个查询字段作为动态分区的一句，而不是根据字段名来选择，如果指定了n个动态分区的字段，hive会将select语句中最后n个字段作为动态分区的依据。hive默认不开启动态分区，所以在执行前要设置hive参数
```

set hive.exec.dynamic.partition =true;//设置为true表示开启动态分区的功能。//
set hive.exec.dynamic.partition.mode =nostrict;//表示所有的分区都是动态的，并且静态分区必须位于动态分区之前//

//其他和动态参数相关的配置//
set hive.exec.dynamic.partition.pernode =nostrict;//每个mapper和reducer可以创建最大的分区数//
set hive.exec.dynamic.partitions =nostrict//一条动态分区创建语句能创建的最大分区//
set hive.exec.dynamic.partition.files;//一个mapreduce作业能够创建的最大文件数//
```

通过CTAS加载数据
这个自己比较简单create table ...as select的缩写了。

导出数据
上边说了半天装载导入数据，现在要将导入的数据我们也能导出。
可通过查询语句选取需要的数据格式，再用insert子句将数据导出到hdfs或者是本地
```

insert overwrite directory '/user/student/student_info' select * from student_info;
insert overwrite local directory '/tmp/student/student_info' select * from student_info;

```
如果数据正好能满足用户需要的数据格式，直接可以复制文件或者目录就可以了
hadoop dfs -cp /user/student/student_info /tmp/student/