hive:大数据最重要的一部分

概述

1. Hive是Apache提供的一套基于Hadoop的数据仓库的管理工具，提供了读写以及管理数据的功能 
2. 提供了类SQL语言来操作Hadoop，在底层将类SQL转化为MapReduce来执行，所以Hive的执行效率相对比较低的，适合于离线批处理 
3. 在安装Hive之前，需要先去安装Hadoop 
4. 在Hive中，每一个database/table在HDFS上对应一个目录（最终的数据就是一个文件里面的数据）
5. Hive中没有主键的概念 
6. Hive在建表的时候，需要指定字段之间的间隔字符。一张表一旦建好，那么间隔字符就不能改变 
7. into表示向表中追加数据，overwrite表示将表中的数据覆盖掉 

hive 版本

hadoop1.0
hadoop2.0 hive1.0 hive2.0
hadoop3.0 hive2.0 hive3.0
学习版本：hive 1.0

hive安装

需要先安装hadoop
然后下载hive安装包
wget http://bj-yzjd.ufile.cn-north-02.ucloud.cn/apache-hive-1.2.0-bin.tar.gz
直接解压
启动hadoop伪分布式
cd hive/bin
sh hive 启动hive

基本命令

• 创建数据库
  create database hivedemo;
  文件系统上会多出一个/user/hive/warehouse/hivedemo.db目录
• 创建数据表
  drop table person;
  create table person(id int,name string,age int);
  文件系统上也会出现目录/user/hive/warehouse/hivedemo.db/person
• 使用数据库
  use hivedemo
• 插入数据
  insert into person values(1,‘hello’,15)
  底层会转换成一个MR任务执行，所以不会一条一条插入，会出现一个文件
• 将/home/hivedata/person.txt文件加载到Hive的person表中
  load data local inpath ‘/home/hivedata/person.txt’ into table person;
• 建表的时候指定字段之间的间隔字符是空格
  create table person(id int, name string, age int) row format delimited fields terminated by ’ ';
• 建立一张和person结构一样的表p2
  create table p2 like person;
• 将person表中age>=14的数据查询出来放到p2中
  insert into table p2 select * from person where age >= 14;
• 将person表中age>=14的数据查询出来覆盖p2
  insert overwrite table p2 select * from person where age >= 14;
• 将person表中age>=14的数据查询出来放到p2中，同时将id<3的数据查询出来放到p3表中,一个表中查，放入多张表中
  from person insert into table p2 select * where age >= 14 insert into table p3 select * where id < 3;
• 将person表中的数据查询出来写到本地的目录中，并且在本地目录中，字段之间需要用,作为间隔
  并且要求本地目录中没有文件
  insert overwrite local directory ‘/home/hivedata’ row format delimited fields terminated by ‘,’ select * from person where id >= 3;
  insert overwrite local directory ‘/home/hivedata’ select * from person where id >= 3;
• 将person表中的数据查询出来放到HDFS的指定目录下
  insert overwrite directory ‘/person’ row format delimited fields terminated by ’ ’ select * from person;
• 将person表改名为p1
  alter table person rename to p1;
• 向p1表中添加一个字段
  alter table p1 add columns (gender string);

show databases;show tables;desc table;

表结构

内部表和外部表

	a. 内部表：在Hive中手动创建手动添加数据的表;由hive管理
	b. 外部表：在Hive中建表管理HDFS已经存在的数据;
	c. 外部表的建表语句：create external table score(name string, chinese int, math int, english int) row format delimited fields terminated by ' ' location '/score'; 
	d. 在删除内部表的时候，在HDFS上对应的目录也会被删除；在删除外部表的时候，在HDFS上对应的目录不会被删除 
	e. 实际过程中，往往是先建立外部表管理原始数据，然后再建立内部表对数据进行清洗 （把外部表查出来插入到内部表）

分区表

分区表的作用是对数据进行分类（partition）

建表语句

创建一张分区表，一个分区也是一个目录
create table cities(id int, name string) partitioned by(province string) row format delimited fields terminated by ’ ';

加载数据

	load data local inpath '/home/hivedata/hebei.txt' into table cities partition(province='hebei');
	load data local inpath '/home/hivedata/jiangsu.txt' into table cities partition (province= 'jiangsu');

每一个分区在在HDFS上对应一个目录

	目录名叫做province=hebei

在建立分区表的时候，分区字段在原始数据中要求不存在，而是在加载数据的时候手动指定

如果手动在HDFS上新建了一个目录作为分区，那么需要在Hive中添加这个分区：（相当于添加元数据到hive，分区的目录要符合标准）

	alter table cities add partition (province='shandong') location '/user/hive/warehouse/hivedemo.db/cities/province=shandong';
	# 或者利用下述命令来修复分区。但是这个命令不稳定，有时候会修复失败
	msck repair table cities;

删除分区

alter table cities drop partition(province=‘guangdong’);

修改分区名

alter table cities partition(province=‘jiangxi’) rename to partition(province=‘jiangsu’);

分区表有可能可以提高查询效率

在分区表中，如果按照分区字段进行查询，那么大幅度的提高查询效率，因为此时只需要查询对应目录即可而不需要将整个表全部查询；
如果在查询的时候进行了跨分区的查询，那么此时查询效率反而会变低，因为需要读取多个文件

动态分区

查询未分区的表，写入分区表，会在分区表下面新建目录
将数据从未分区的表中向分区表中来放；原来的表中就必须有分区字段才可以。
# 建表管理原始数据
create table c_tmp(cid int, cname string, cpro string) row format delimited fields terminated by ’ ';
# 加载数据
load data local inpath ‘/home/hivedata/city.txt’ into table c_tmp;
# 开启动态分区，动态分区默认是严格模式不开启
set hive.exec.dynamic.partition.mode=nonstrict;
# 动态分区
insert into table cities partition(province) select cid, cname, cpro from c_tmp distribute by(cpro);

多字段分区

在Hive中，分区字段可以有多个，往往是用于对数据进行多级分类的。写在前面的分区，表示大类，包含后边的分区
不建议太深

create table product(id int, name string) partitioned by(kind string, subkind string, grandkind string) row format delimited fields terminated by ’ ';

load data local inpath ‘/home/hivedata/shoes.txt’ into table product partition(kind=‘clothes’,subkind=‘shoes’,grandkind=‘male’);

分桶表

概要

	a. 作用：对数据进行抽样 
	b. 分桶表的特点在于不支持load加载方式，而是需要从其他表中查询然后放到分桶表中 ，只支持查别的表然后插入
	c.分6个桶就代表表的目录下面有6个文件 000000_0,000001_0

创建分桶表

create table score_sample(name string, chinese int, math int, english int) clustered by (name) into 6 buckets row format delimited fields terminated by ’ ';
表示根据name字段的hash码值来进行分桶，将数据分到6个桶中，hash取余算法
先计算name字段的值的哈希码，然后利用这个哈希码对桶数进行取余，取余完成之后决定放到哪个桶中

开启分桶机制

分桶默认不开的
set hive.enforce.bucketing = true;

添加数据

分桶表不支持load插入数据

• 创建一张外部表
  create external table score(name string, chinese int, math int, english int) row format delimited fields terminated by ’ ’ location ‘/score’;
• 将数据从score表中查询出来放到score_sample表中，在这个过程中自动进行分桶
  insert overwrite table score_sample select * from score;

数据抽样

select * from score_sample tablesample (bucket 1 out of 3 on name);

tablesample 抽样函数
抽样 - bucket x out of y — x表示起始桶编号， y表示抽样的步长
bucket 1 out of 3表示从第1个桶(对应的编号为0)中抽，每隔三个桶抽一次 -> 抽取的编号就是0,3

复杂数据类型

基本数据类型 hive java类型
tinyint byte
smallint short
int int
bigint long
boolean boolean
float float
double double
string String
timestamp TimeStamp
binary byte[]

array类型

数组类型，对应了Java中的数组或者集合，字段的类型是一个数组
原始数据：分了两个字段，每个字段的长度不一样，每个字段中还有多个元素
2,4,6,2,5 3,2,4,5
32,48,23,52 54,23,12,57,95
122,23,53,4,4,23 64,23,86,4,84,4
27,48 90,12,44

• 建表
  create table nums(na1 array, na2 array) row format delimited fields terminated by ’ ’ collection items terminated by ‘,’;
• 加载数据
  load data local inpath ‘/home/hivedata/nums.txt’ into table nums;
• 查询第一个数组第一个元素
  select na1[0] from nums;
• 查询非空数据，查询第一个字段下标为5的元素非空
  select na1[5] from nums where na1[5] is not null;

map类型

映射类型，对应了Java中的Map类型 ，字段类型是键值对
原始数据：两个字段，第二个字段是一个键值对
1 Amy:female
2 Sam:male
3 Alex:male
4 Lily:female
5 Jack:male

• 建表语句
  create table infos (id int, info map<string,string>) row format delimited fields terminated by ’ ’ map keys terminated by ‘:’;
• 加载数据
  load data local inpath ‘/home/hivedata/info.txt’ into table infos;
• 查询非空值，info字段，key值是Sam对应的值
  select info[‘Sam’] from infos where info[‘Sam’] is not null;

struct类型

结构体类型，对应了Java中的对象，一个字段的结构是一个复杂多元结构

• 建表
  create external table orders(o struct<orderId:int, orderDate:string, productId:int, num:int>) row format delimited collection items terminated by ’ ’ location ‘/order’;
• 查询语句，查询字段中的某个属性
  select o.orderid from orders;

SerDe

如果要处理的数据字段间隔符不一样怎么办

概要

1. SerDe (Serializar/Deserializar)是Hive提供的一套序列化/反序列化
2. 实际开发中，SerDe机制专门用于处理不规则的数据
3. 在SerDe中，需要提供正则表达式来匹配数据格式，并且匹配过程中，需要用捕获组来对应Hive表中的字段，捕获组就是正则表达式的一个()

案例：

• 建表
  create table logs(ip string, time string, timezone string, request_way string, resource string, protocols string, statusid int)
  row format serde ‘org.apache.hadoop.hive.serde2.RegexSerDe’
  with serdeproperties (“input.regex” = “(.) -- \[(.) (.)\] "(.) (.) (.)” ([0-9]+) -")
  stored as textfile; //这个可加可不加，表示在hdfs上以文本格式存在

  \-\- 匹配上面的 --，需要转义
  (.*) 表示捕获组
  中括号转义需要两个斜杠

• 加载数据
  load data local inpath ‘/home/hivedata/logs.txt’ into table logs;

Join

• 在Hive中，提供了inner/left/right/full outer/left semi join

products p left semi join orders o表示查询products表中有哪些数据在orders表中出现过（有点像in）

create external table orders(orderid int, orderdate string, proid int, num int) row format【按行处理】 delimited fields terminated by ’ ’ location ‘/order’;
create external table products (proid int, name string, price double) row format delimited fields terminated by ’ ’ location ‘/product’;

select * from products p left semi join orders o on p.proid = o.proid;

如果join的时候，没有明确指定形式，那么默认就是inner join

beeline

在云主机上安装hadoop，同时安装hive，sh hive启动hive
如果需要通过远程连接hive怎么办？自己的电脑连接到云主机上的hive
Beeline是hive提供的一种远程连接方式，允许连接远程主机上的hive
先启动hive后台连接服务：
sh hive --service hiveserver2 &
jps查看多一个runjar进程

sh beeline -u jdbc:hive2//hadoop01:10000/hivedemo
本质上是通过jdbc去连接的

sh beeline -u jdbc:hive2//hadoop01:10000/hivedemo -n root 以root用户连接hive

视图和索引

视图

在一个表中包含很多字段，但是每一个字段的使用频率并不相同，那么此时可以考虑将使用频率较高的字段抽取出来形成一个子表，又希望子表能与主表自动关联，那么此时这个子表可以以视图形式来体现

虚拟视图和物化视图

如果抽取出来的视图放到了内存中，称之为虚拟视图；如果将抽取来的视图放到了磁盘中，称之为物化视图
Hive中只支持虚拟视图不支持物化视图

建立视图

只是保存了一份元数据，是不会生成一个目录的
create view o_view as select orderid, num from orders;

查询语句

select orderid from o_view;

删除视图

drop view o_view;

官网上指出，定义视图时子查询不会执行，而是在第一次使用视图的时候才会触发这句select

索引

在数据库中，因为在建表的时候存在主键，所以会自动的根据主键来建立索引（主键索引）。在Hive中，没有主键的概念，所以也不会自动建立索引

在Hive中可以针对任何一个字段来建立索引，而且一张表可以存在多个索引

建立索引表

create index oin(索引名) on table orders(orderid) as ‘org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler’(通过类建索引) with deferred rebuild in table o_index;(创建的索引放到哪个表里面)
针对orders表建索引，把索引放到o_index表中，这个表中有一个字段叫做oin；hive的索引底层是一张表

手动建立索引

alter index oin on orders rebuild;
o_index是一张表

id 1002在哪个文件的那个字节的地方

删除索引

drop index oin on orders;

hive的JDBC操作

用得不多
使用过程和mysql的jdbc是一样的
查看与预习文档

运算符

= 等于
is null
is not null
like
查看预习文档

函数

其他函数可以参考预习文档

explode函数：
explode是Hive提供的函数，将数组中的每一个元素提取出来形成单独的一行

案例 - 单词统计：

建表管理数据

create external table words(warr array) row format delimited collection items terminated by ’ ’ location ‘/words’;

统计个数

select w, count(w) from (select explode(warr) w from words)ws group by(w);

UDF 自定义函数

• 导入依赖：
  

• 自定义一个类继承UDF，实现evaluate方法，这个方法的返回值和参数类型由业务场景决定
  函数的输入值是什么，期望返回值是什么

• 打成jar包放到Linux上，位置不固定
  打包方式：参考自定义flume source

• 统一格式
  上传jar包后执行下面的语句，防止报错
  zip -d H_Hive.jar ‘META-INF/.SF’ ‘META-INF/.RSA’ ‘META-INF/*SF’

• 在Hive中：
  ·添加jar包：add jar /home/hivedata/H_Hive.jar;
  创建临时函数，给函数起名，并且绑定这个函数对应的类：create temporary function repeatstring as ‘cn.tedu.hive.Repeat’;
  调用函数：select repeatstring(“abc”, 5);

package cn.tedu.hive;

import org.apache.hadoop.hive.ql.exec.UDF;

// abc 5 -> abcabcabcabcabc
public class Repeat extends UDF {

    // Hive自动去寻找这个evaluate函数
    // 根据场景要求决定这个函数的参数和返回值类型
    public String evaluate(String str, int n) {
        if (n < 1)
            throw new IllegalArgumentException("个数至少为1！！！");
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < n; i++) {
            sb.append(str);
        }
        return sb.toString();
    }

}

元数据

在Hive中database名、table名、字段名、分区名、索引名、视图名等这些都是元数据 - 描述其他数据的数据
在Hive中，元数据是存储在关系型数据库中。目前为止，hive的元数据默认存储在derby中，derby只支持单连接。可以配置成mysql
Derby是单连接数据库，不符合大数据的并发场景，所以需要将Derby改为MySQL
MySQL尽量不要重复安装，一旦重复安装，会出现PID file问题

mysql安装

如果已经安装MySQL
1.	编辑my.cnf
	vim /usr/my.cnf
	添加：
	[client]
	default-character-set=utf8
	[mysql]
	default-character-set=utf8
	[mysqld]
	character_set_server=utf8
	sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
2.	重启MySQL
	service mysql restart
	
如果第一次安装MySQL
1.	先查看是否有残缺的MySQL
	rpm -qa | grep mysql
2.	删除残缺的MySQL
	rpm -ev --nodeps mysql-libs-5.1.73-8.el6_8.x86_64
	rpm -ev --nodeps tcl-mysqltcl-3.052-1.el6.x86_64
3.	新添MySQL用户和用户组
	groupadd mysql
	useradd -r -g mysql mysql
4.	下载MySQL的安装包
	cd /home/software
	wget http://bj-yzjd.ufile.cn-north-02.ucloud.cn/MySQL-client-5.6.29-1.linux_glibc2.5.x86_64.rpm
	wget http://bj-yzjd.ufile.cn-north-02.ucloud.cn/MySQL-server-5.6.29-1.linux_glibc2.5.x86_64.rpm
5.	安装MySQL
	rpm -ivh MySQL-server-5.6.29-1.linux_glibc2.5.x86_64.rpm 
	rpm -ivh MySQL-client-5.6.29-1.linux_glibc2.5.x86_64.rpm 
6.	编辑my.cnf
	vim /usr/my.cnf
	清空内容
	添加：
	[client]
	default-character-set=utf8
	[mysql]
	default-character-set=utf8
	[mysqld]
	character_set_server=utf8
	sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
7.	添加到开机启动
	cp /usr/share/mysql/mysql.server   /etc/init.d/mysql
8.	重启MySQL
	service mysql restart
9.	查看MySQL的密码
	cat /root/.mysql_secret 
10.	修改密码
	mysqladmin -u root -p password root
	
	
如果重复安装MySQL，那么
rpm qa | grep mysql --- 如果出现结果，那么移除掉 rpm -ev --nodeps  名字
rpm qa | grep MySQL --- 如果出现结果，那么移除掉
查看以下目录中是否有和MySQL相关的内容，如果有，则移除掉
rm -rf /usr/bin/mysql*
rm -rf /usr/sbin/mysql*
rm -rf /var/lib/mysql*
rm -rf /usr/share/info/mysql*
rm -rf /usr/share/man/mysql*
rm -rf /usr/include/mysql*
rm -rf /usr/lib/mysql*
rm -rf /usr/share/mysql*
rm -rf /root/.mysql_secret
全部移除掉之后，重新安装MySQL

在hive中配置mysql

1.	下载MySQL的驱动包
	cd /home/software/apache-hive-1.2.0-bin/lib
	wget http://bj-yzjd.ufile.cn-north-02.ucloud.cn/mysql-connector-java-5.1.38-bin.jar
2.	编辑hive-site.xml
	cd ../conf
	vim hive-site.xml
	添加：
	<configuration>
	# mysql连接地址
	<property>
		<name>javax.jdo.option.ConnectionURL</name>
		<value>jdbc:mysql://hadoop01:3306/hive?createDatabaseIfNotExist=true</value>
	</property>
	<property>
	# mysql驱动
		<name>javax.jdo.option.ConnectionDriverName</name>
		<value>com.mysql.jdbc.Driver</value>
	</property>
	<property>
	# mysql用户名
		<name>javax.jdo.option.ConnectionUserName</name>
		<value>root</value>
	</property>
	<property>
	# mysql密码
		<name>javax.jdo.option.ConnectionPassword</name>
		<value>root</value>
	</property>
	</configuration>
3.	进入MySQL，执行
    授权mysql远程主机登录
	grant all privileges on *.*（所有库的所有表） to 'root'@'hadoop01' identified by 'root' with grant option;
	grant all on *.* to 'root'@'%' identified by 'root';
	flush privileges;
	create database hive character set latin1; #创建元数据库
4.	进入Hive安装目录的bin目录下
	cd ../bin
	sh hive

如果启动失败，从第三个错误开始查看
出现READ COMMITTED or UNREAD COMMITTED，解决方案：
vim /usr/my.cnf
添加：
binlog_format=mixed
重启MySQL
service mysql restart

测试

在hive中创建数据库
hive>create database h1;
mysql> select * from DBS; # mysql 的hive数据库中查询刚刚创建的数据库的元数据信息

体系结构

hive组件：
Driver：负责对外交互，对内的协调
Compiler：负责将SQL转化为MapReduce
ExecutionEngine：负责和YARN进行交互

hive要转很大的一圈，所以执行的效率比较低，只能用来做离线

优化

需要掌握几个
可以查看预习笔记

map side join

如果a join b，且a表较小，b表较大，那么可以考虑将a表放到内存中，然后专心处理b表，如果处理过程中需要a表的数据，那么从内存中将a表的数据找出来，而不需要从HDFS上再读取数据；默认小表的大小不过25M（20000行数据左右）；要求小表 join 大表，不能是大表 join 小表。
通过set hive.auto.convert.join=true;设置完之后会自动尝试把前面的小表往内存中塞。

join语句的优化

如果出现join和where，先where减少整体数据量，然后再进行join

group by优化

将相同的值放到一组，因为数据本身有倾斜特性(因为数据本身不均匀)，就会导致有的组数据多，有的组数据少，分到数据少的组的ReduceTask执行的就快；分到数据多的组的ReduceTask执行的就慢，这就导致了数据倾斜。
通过set hive.groupby.skewindata=true;来启动二阶段聚合（解决数据倾斜的一种方式）

distinct和聚合函数的优化

count属于聚合函数，当聚合函数和distinct同时出现的时候，可以考虑先用多个ReduceTask去重，最后再利用一个ReduceTask来聚合

调整切片数

如果数据字段比较多处理逻辑比较复杂，可以考虑将切片调小增多任务数；（因为1个切片对应一个任务）如果数据字段少处理逻辑比较简单，可以考虑将切片调大减少任务数

JVM重用

每一个MapTask或者ReduceTask都会启动和关闭一次JVM子进程，那么如果一个节点接受到大量的MapTask或者ReduceTask就需要频繁的开启和关闭JVM子进程。
可以考虑JVM重用，即JVM子进程启动之后执行多个MapTask或者ReduceTask再结束，而不是执行一次就关闭，这样子可以减少JVM子进程频繁的开启和关闭，节省资源。

启用严格模式

用的不多
用户可以通过 set hive.mapred.mode=strict 来设置严格模式，改成unstrict则为非严格模式
在严格模式下，用户在运行如下query的时候会报错：
1、分区表的查询没有使用分区字段来限制
2、使用了order by 但没有使用limit语句（如果不使用limit，会对查询结果进行全局排序，消耗时间长）
3、产生了笛卡尔积

关闭推测执行机制

慢任务的优化，慢任务拷贝到其他节点执行
数据倾斜时导致的慢任务没有优化意义，反而造成资源浪费
可通过如下设置关闭推测执行：
set mapreduce.map.speculative=false
set mapreduce.reduce.speculative=false
set hive.mapred.reduce.tasks.speculative.execution=false

sqoop

概述

1. sqoop是Apache 提供的工具，用于hdfs和关系型数据库之间数据的导入和导入
2. 可以从hdfs导出数据到关系型数据库（做可视化），也可以从关系型数据库导入数据到hdfs（数据收集以及数据仓库）

安装

下载安装包
wget http://bj-yzjd.ufile.cn-north-02.ucloud.cn/sqoop-1.4.4.bin__hadoop-2.0.4-alpha.tar.gz
解压安装包
进入安装目录的lib目录
下载mysql的数据库驱动
wget http://bj-yzjd.ufile.cn-north-02.ucloud.cn/mysql-connector-java-5.1.38-bin.jar
进入bin目录

hdfs数据导入RDBMS

需要先在数据库中建库建表
sh sqoop export --connect jdbc:mysql://192.168.150.138:3306/test --username root --password root --export-dir ‘/order/order.txt’ --table orders(导入到mysql的哪一张表上) -m 1(忽略权限校验) --fields-terminated-by ’ '(hdfs上的字段分隔符是空格)

将rdbms数据导入到hdfs

网站流量分析

练手项目，不算真实项目
zebra只是一个项目名称

项目需求：zebra是对电信运营商收集的用户上网数据进行分析的一个应用程序。通过分析得到的结果可以展现不同小区的上网详情

日志字段说明：hive-prepare文件

日志分析框架：

不同的业务需求，从大宽表中提取不同的字段形成子表。
1、哪个APP更受欢迎（从事实表中提取需要的字段即可）
2、分析不同小区的上网流量

数据仓库和数据库的区别

结构化数据：数据可以用有限个数的表格来表示，行列分明
本结构化：爬虫的，日志的；数据具有一定的格式，但是无法用有限个数的表格来限定。例如json字符串，有一定格式，但是不能用表格存储任意一个json
非结构化： 数据本省没有格式，例如图片，视频