大数据分析利器之Hive

1.数据仓库

1）数据仓库的基本概念

　　数据仓库的英文名称为Data Warehouse,可简写为DW或DWH。

　　数据仓库的目的是构建面相分析的集成化数据环境，为企业提供决策支持（Decision Support）。它出于分析性报告和决策支持的目的而创建。

　　数据仓库本身并不“生产”任何数据，同时自身也不需要“消费”任何数据，数据源于外部，并且开放给外部应用，这也是为什么叫“仓库”，而不是“工厂”的原因。

2）数据仓库的主要特征

　　数据仓库是面向主题的（Subject-Oriented）、集成的（Integrated）、非易失的（Non-Volatile）和时变的（Time-Variant）数据集合，用以支持管理决策。

3）数据仓库与数据库的区别

　　数据库与数据仓库的区别实际讲的是OLTP与OLAP的区别。

　　操作型处理，叫联机事务处理OLTP（On-Line Transaction Processing），也可以称面向交易的处理系统，他是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性、和并发支持的用户数等问题。传统的数据库系统作为数据管理的主要手段，主要用于操作型处理OLTP。

　　分析型处理，叫联机分析处理OLAP（On-Line Analytical Processing），一般针对某些主题的历史数据进行分析，支持管理决策。

　　数据仓库的出现并不是要取代数据库。

　　　　数据库是面向事务的设计，数据仓库是面向主题设计的。

　　　　数据库一般存储业务数据，数据仓库存储的一般是历史数据。

　　　　数据库设计师尽量避免冗余，一般针对某一业务应用进行设计，比如一张简单的User表，记录用户名，密码等简单数据即可，符合业务应用，但是不符合分析；数据仓库在设计上是有意引入冗余，依照分析需求，分析维度，分析指标进行设计。

　　　　数据库是为捕获数据而设计，数据仓库是为分析数据而设计。

　　　　　　以银行业务为例。数据库是事务系统的数据平台，客户在银行做的每笔交易都会写入数据库，被记录下来，这里，可以简单地理解为数据库记账。数据仓库是分析系统的数据平台，它从事务系统获取数据，并做汇总、加工，为决策者提供决策依据。比如某银行分行一个月发生多少交易，该分行当前存款余额是多少。如果存款又多，消费交易又多，那么该地区就有必要设立ATM。

　　　　　　显然，银行的交易量是巨大的，通常以百万甚至千万次来计算。事务系统是实时的，这就要求时效性，客户存一笔需要几十秒是无法忍受的，这就要求数据库智能存储很短一段时间的数据。而分析系统是事后的，它要提供关注时间段内所有的有效数据。这些数据是海量的，汇总计算起来也要慢一些，但是只要提供有效的分析数据就达到目的了。

　　　　数据仓库，是数据库大量存在的情况下，为了进一步挖掘数据资源、为了决策需要而产生的，它绝不是所谓的“大型数据库”。

4）数据仓库的分层架构

　　按照数据流入流出的过程，数据仓库结构可分为三层——源数据层、数据仓库层、数据应用层。

　　数据仓库的数据源于不同的源数据，并提供多样的数据应用，数据自下而上流入数据仓库后向上层开放应用，而数据仓库只是中间集成化数据管理的一个平台。

　　源数据层（ODS）：此层数据无任何更改，直接沿用外围系统数据结构和数据，不对外开放；为临时存储层，是接口数据的临时存储区域，为后一步数据处理做准备。

　　数据仓库层（DW）：也成为细节层，DW层的数据应该是一致的、准确的、干净的数据，即对源系统数据进行了清洗（去除了杂质）后的数据。

　　数据应用层（DA或APP）：前端应用直接读取的数据源；根据报表、专题分析需求而计算生成的数据。

　　数据仓库从各数据源获取数据及在数据仓库内的数据转换和里流动都可以认为是ETL（抽取Extra，转化Transfer，装载：oad）的过程，ETL是数据仓库的流水线，也可以认为是数据仓库的血液，它维系着数据仓库中数据的新城代谢，而数据仓库日常的管理和维护工作的大部分精力就是保持ETL的正常和稳定。

　　为什么要对数据仓库分层？

　　　　用空间换时间，通过大量的预处理来提升应用系统的用户体验（效率），因此数据仓库会存在大量的冗余数据；不分层的话，如果源业务数据规则发生变化将会影响整个数据清洗过程，工作量巨大。

　　　　通过把数据分层管理可以简化数据的清洗过程，因为把原来一步的工作分到了多个步骤去完成，相当于把一个复杂的工作拆成了多个简单的工作，把一个大的黑盒变成了一个白盒，每一层的处理逻辑都相对简单和容易理解，这样我们比较容易保证每一个步骤的正确性，当数据发生错误的时候，往往我们只需局部调整某个步骤即可。

2.Hive

1）Hive的概念

　　hive是基于hadoop的一个数据仓库工具

　　　　可以将结构化的数据文件映射为一张数据库表，并提供类似SQL查询功能

　　　　其本质是将SQL转换为MapReduce的任务进行运算，底层有HDFS来提供数据的存储支持，说白了hive可以理解为一个将SQL转换为MapReduce任务的工具，甚至更进一步可以说hive就是一个MapReduce的客户端

2）Hive与数据库的区别

 

　　Hive具有SQL数据库的外表，但是应用场景完全不同。

　　Hive只适合用来做海量离线数据统计分析，也就是数据仓库。

3）Hive的优缺点

　　优点：

　　　　操作接口采用类SQL语法，提供快速开发的能力（简单容易上手）。

　　　　避免了去写MapReduce，减少开发人员的学习成本。

　　　　Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

　　缺点：

　　　　Hive的查询延迟严重

　　　　Hive不支持事务

4）Hive架构原理

　　①用户接口：Client

　　　　CLI（hive shell）

　　　　　　JDBC/ODBC（java访问hive）

　　　　WEBUI（浏览器访问hive）

　　②元数据：Metastore

　　　　元数据包括：表名、表所属的数据库（默认default）、表的拥有者、列/分区字段、表的类型（内部表或外部表）、表的数据所在目录等；

　　　　默认储存在自带的derby数据库，推荐使用MySQL存储Metastore

　　③Hadoop集群

　　　　使用HDFS进行存储，使用MapReduce进行计算

　　④Driver：驱动器

　　　　解释器（SQL Parser）：将sql字符串转换成抽象语法树AST，对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。

　　　　编译器（Physical Plan）：将AST编译生成逻辑执行计划。

　　　　优化器（Query Optimizer）：将逻辑执行计划进行优化。

　　　　执行器（Execution）：把逻辑执行转换成可以运行的物理计划。对于hive来说默认就是Mapreduce任务

3.Hive的交互方式

　　使用hive前需要启动hadoop、mysql。因为hql语句会被编译成mr任务并提交到集群运行；hive表数据一般存在hdfs上；hive的操作过程需要访问MySQL中存储元数据的库和表。

1）hive交互shell

　　任意路径下运行 hive（不推荐使用）

2）Hive JDBC服务

　　①启动hiveserver2服务，前台启动或后台启动均可

　　　　前台启动：hive --service hiveserver2

　　　　后台启动：nohup hive --service hiveserver2 &

　　②beeline连接hiveserver2

　　　　若是前台启动需要开启新的会话使用beeline连接hive

　　　　beeline-》！connect jdbc:hive2://node03:10000-》输入任意用户名密码-》!help(查看使用帮助)

3）Hive命令

　　hive -e 执行hql语句

　　hive -f 执行hql脚本

4Hive的数据类型

1）基本数据类型

类型名称	描述	举例
boolean	true/false	true
tinyint	1字节的有符号整数	1
smallint	2字节的有符号整数	1
==int==	4字节的有符号整数	1
==bigint==	8字节的有符号整数	1
float	4字节单精度浮点数	1.0
==double==	8字节单精度浮点数	1.0
==string==	字符串(不设长度)	“abc”
varchar	字符串（1-65355长度，超长截断）	“abc”
timestamp	时间戳	1563157873
date	日期	20190715

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2）复合数据类型

类型名称	描述	举例
array	一组有序的字段，字段类型必须相同 array(元素1，元素2)	Array（1,2,3）
map	一组无序的键值对 map(k1,v1,k2,v2)	Map(‘a’,1,'b',2)
struct	一组命名的字段，字段类型可以不同 struct(元素1，元素2)	Struct('a',1,2,0)

 

 

 

 

 

 

　　①参数说明

　　创建表的时候可以指定每行数据的格式，如果使用的是复合数据类型，还需要指定复合数据类型中的元素的分隔符号

　　row format delimited

　　　　[fields terminated by char [escaped by char]]

　　　　[collection items terminated by char]

　　　　[map keys terminated by char]

　　　　[lines terminated by char]

　　其中

　　　　fields terminated by char 指定每一行记录中多字段的分隔符

　　　　collection items terminated by char 指定符合类型中多元素的分隔符

　　　　map keys terminated by char 指定map集合中每一个key，value之间的分隔符

　　　　lines terminated by char 指定每行记录的换行符，一般有默认 就是\n

　　②Array类型

　　　　array中的数据为相同类型，如，array A中元素['a','b','c']，则A[1]的值为'b'

　　　　数据文件：t_array.txt（字段空格分隔)

1 zhangsan beijing,shanghai
2 lisi shanghai,tianjin

 　　　　建表语法：

create table t_array(
id string,
name string,
locations array<string>
) row format delimited fields terminated by ' ' collection items terminated by ',';

　　　　加载数据：

load data local inpath '/home/hadoop/t_array.txt' into table t_array;

　　　　查询语法：

select id,name,locations[0],locations[1] from t_array;

　　③Map类型

　　　　map类型中存储k/v类型的数据，后期可以通过指定k名称访问

　　　　数据文件：t_map.txt

1 name:zhangsan#age:30
2 name:lisi#age:40

　　　　建表语法：

create table t_map(
id string,
info map<string, string>
) row format delimited fields terminated by ' ' collection items terminated by '#' map keys terminated by ':';

　　　　加载数据：

load data local inpath '/home/hadoop/t_map.txt' into table t_map;

　　　　查询数据：

select id, info['name'], info['age'] from t_map;

　　④Struct类型

　　　　可以存储不同类型的数据。ex：c struct{a int; b int},可以通过c.a来访问a

　　　　数据文件：t_struct.txt

1 zhangsan:30:beijing
2 lisi:40:shanghai

　　　　建表方法：

create table t_struct(
id string,
info struct<name:string, age:int, address:string>
) row format delimited fields terminated by ' ' collection items terminated by ':';

　　　　加载数据：

load data local inpath '/home/hadoop/t_struct.txt' into table t_struct;

　　　　查询数据：

select id, info.name, info.age, info.address from t_struct;

5.Hive的DDL操作

1）数据库的DDL操作

#创建数据库
create database db_hive;
create database if not exists db_hive;#db_hive不存在才创建
#显示所有数据库
show databases;
#查询所有数据库
show databases like 'db_live*';
#查看数据库详情
desc database db_hive;
#查看数据库详细信息
desc database extended db_hive;
#切换数据库
use db_hive;
#删除数据库
drop database db_hive;
drop database if exists db_hive;#数据库存在才删除
drop database if exists db_hive cascade;#如果数据库中有表需要使用 cascade强制删除

2）表的DDL操作

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] #分区
[CLUSTERED BY (col_name, col_name, ...)]#分桶
[SORTED BY (col_name [ASC|DESC], ...) INFO num_buckets BUCKETS]
[ROW FORMAT row_format] row format delimited fields terminated by "分隔符"
[STORED AS file_format]
[LOCATION hdfs_path]

字段解释

　　CREATE TABLE 创建一个指定表

　　EXTERNAL 创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），指定表的数据保在哪里

　　COMMENT 为表和列添加注释

　　PARTITIONED BY 创建分区表

　　CLUSTERED BY 创建分桶表

　　SORTED BY 按照字段排序（一般不用、使用只能创建一个分区）

　　ROW FORMAT 指定每一行中字段的分隔符：row format delimited fields terminated by '\t'

　　STORED AS 指定存储文件类型

　　　　常用存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、ORCFILE（列式存储文件）

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

　　LOCATION 指定表在HDFS上的存储位置

　　①创建内部表

#使用标准的建表语句直接建表
use myhive;
create table stu(id int, string name);
#可以通过insert into向hive表中插入数据，但是不建议在工作当中这么做；因为每个insert语句会转换mr后生成一个文件，产生大量小文件
insert into stu(id,name) values(1,"zhangshan");
insert into stu(id,name) values(2,"lisi");
select * from stu;

#查询建表法，通过as查询语句完成建表，将查询结果存入新表里
create table if not exists myhive.stu1 as select id, name from stu;
select * from stu1;#表中有数据

#like建表法，根据已存在的表结构创建表
create table if not exists myhive.stu2 like stu;
select * from stu2;#表中没有数据

#查询表类型
desc formatted myhive.stu;

　　②创建外部表

　　外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表时，数据仍存放在hdfs当中，不会删掉

create external table myhive.teacher(t_id string, t_name string)
row format delimited fields terminated by '\t';

　　创建外部表的时候需要加上external关键字

　　LOCATION字段可以指定，也可以不指定，指定就是数据存放的具体目录，不指定使用默认目录：/usr/hive/warehouse

　　向外部表当中加载数据：

　　　　外部表也可以通过insert的方式进行插入数据，一般不推荐，实际工作中都是使用load的方式加载数据到内部表或外部表

　　load数据可以冲本地文件系统加载或者也可以冲hdfs上面的数据进行加载

　　　　从本地文件系统加载数据到teacher表当中去：

load data local inpath '/home/hadoop/teacher.csv' into table myhive.teacher;

　　　　从hdfs上加载文件到teacher（将teacher.csv上传到 hdfs中，在从hdfs上加载）

load data inpath '/hdfsdatas/teachers.csv' into table myhive.teacher;

　　③内部表与外部表的转换

#内部表转换为外部表
alter table stu set tblproperties('EXTERNAL'='TRUE');

#外部表转换为内部表
alter table teacher set tblproperties('EXTERNAL'='FALSE');

　　④内部表与外部表的区别

　　外部表在建表时需要添加external关键字

　　内部表删除后，表的元数据与真实数据都被删除，外部表删除后，仅仅是把该表的元数据删除，真实数据还在，后期还可以恢复出来

　　⑤内部表与外部表的使用时机

　　内部表犹豫删除表的同时会删除hdfs的数据文件，所以确定如果一个表仅仅是你独占使用，其他人不适用的时候可以创建内部表，如果一个表的数据，其他人也要使用，那么就创建外部表。

　　一般外部表都是用在数据仓库的ODS层，内部表都是用在数据仓库的DW层。

　　⑥hive的分区表

　　如果hive当中所有的数据都存入到一个文件夹下面，那么在使用MR计算程序的时候，读取一整个目录下面的所有文件来进行计算就会变得特别慢，因为数据量太大了。

　　实际工作中一般都是计算前一天的数据，所以我们只需要将前一天的数据挑出来放到一个文件夹下面即可，专门去计算一天的数据。

　　这样就可以使用hive当中的分区表，通过分文件夹的形式，将每一天的数据都分成为一个文件夹，然后我们计算数据的时候通过指定前一天的文件夹即可只计算前一天的数据。

　　在大数据中，最常用的一种思想就是分治，我么可以把大的文件切割成一个个小的文件，这样每次操作一个小的文件就很容易了，同样的道理，在hive中也支持这种思想，就是我们可以把大量的数据按照每天，或者每个小时进行切分成一个个的小文件，这样去操作小文件就会容易的多了。

　　在文件系统上建立文件夹，把表的数据放在不同文件夹下面，加快查询速度。

　　创建分区表语法

create table score(s_id string, c_id string, s_score int) partitioned by (month string) row format delimited fields terminated by '\t';

　　创建一个表带有多个分区

create table score2(s_id string, c_id string, s_score int) partitioned by (year string, month string, day string) row format delimited fields terminated by '\t';

　　加载数据到分区表当中

load data local inpath '/home/hadoop/score.csv' into table score partition (month='202011');

　　加载数据到多分区表中

load data local inpath '/home/hadoop/socre.csv' into table score2 partition(year='2020', month='11', day='11');

　　查看分区

show partitions score;

　　添加一个分区

alter table score add partition(month='202010');

　　添加多个分区:添加分区后hdfs文件系统当中看到表下面多了一个文件夹

alter table score add partition(month='202009') partition(month='202008');

　　删除分区

alter table score drop partition(month='202009');

　　表的修复：建立表与数据文件之间的关系映射

msck repair table score4;

 　　⑦hive的分桶表

 

　　分桶是相对分区进行更细颗粒度的划分

　　　　hive表或分区表可进一步分桶

　　　　分桶将整个数据内容按照某列取hash值，对桶的个数取模的方式决定该条记录存放到哪个桶当中；具有相同hash值的数据进入到同一个文件中

　　作用：取样sampling更高效，没有分桶的话需要扫描整个数据集，提升某些查询操作效率，例如map、side、join

　　创use myhive;

#创建分桶表前需要开启分桶表的支持
set hive.enforce.bucketing=true;
#设置与桶相同的reduce个数（默认只有一个reduce）
set mapreduce.job.reduces=4;

#创建分桶表
create table myhive.user_buckets_demo(id int, name string)
clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';

#无法直接将数据加载到分桶表中，需要先将数据加载到普通表，普通表到分桶表导入数据会根据id进行hash分桶
#创建普通表
create table user_demo(id int, name string)
row format delimited fields terminated by '\t';

　　数据文件buckets.txt

1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
7 test7
8 test8
9 test9

　　加载数据到普通表user_demo中

load data local inpath '/home/hadoop/buckets.txt' overwrite into table user_demo;

　　加载数据到分桶表user_buckets_demo

insert into table user_buckets_demo select * from user_demo;

　　抽样查询桶表的数据

　　　　tablesample(bucket x out of y)，x表示从第几个桶开始抽取数据，y与进行采样的桶数的个数、每个采样桶的采样比例有关。

select * from user_buckets_demo tablesample(bucket 1 out of 2);
#需要采样的总桶数=4/2=2个
#先从第一个桶中取出数据
#1+2=3再从第3个桶中取出数据

6.Hive数据导入

#直接向表中插入数据（不推荐使用）
insert into table score3 partition（month='201807'）values('001', '002', '100');

#通过load加载数据
load data [local] inpath 'datapath' [overwrite] into table stu [partcol1=val1,...];

#通过查询加载数据
insert overwrite table score3 partition(month='201806') select s_id,c_id,s_score from score;

#查询语句创建表并加载数据
create table score3 as select * from score;

#创建表示指定location，将文件上传到hdfs的指定目录
create table score3(s_id string, c_id string, s_score int)
row format delimited fields terminated by '\t'
location '/myscore3';
#export与import（内部表操作）
export table teacher to '/home/hadoop/teacher';导出数据
import table teacher from '/home/hadop/teacher';导入数据

7.Hive数据导出

1）insert导出

#将查询结果导出到本地
insert overwrite local directory '/home/hadoop/stu' select * from stu;

#将查询结果格式化导出到本地
insert overwrite local directory 'home/hadoop/stu' row format delimited fields terminated by ',' select * from stu;

#将查询结果导出到hdfs上
insert overwrite directory 'hdfsdatas/stu' row format delimited fields terminated by ',' select * from stu;

2)hive shell 命令导出

　hive -e "hql语句" > file

　hive -f hql脚本 > file

3)export导出到hdfs上

　　export table stu to '/hdfsdatas/stu'

8.Hive的静态分区与动态分区

　　静态分区：表的分许字段值需要开发人员手动给定

　　　　创建表导入数据查看ddl中的分区表

　　动态分区：按照需求实现吧数据自动导入到表的不同分区中，不需要手动指定

#创建普通表
create table t_order(order_numer string, order_price double, order_time string)
row format delimited fields terminated by '\t';

#创建目标分区表
create table t_dynamic_partition(order_number string, order_price double)
partitioned by(order_time string)
row format delimited fields terminated by '\t';

#数据文件 order_partition.txt
10001 100 2019-03-02
10002 200 2019-03-02
10003 300 2019-03-03
10004 400 2019-03-03
10005 500 2019-03-04
10006 600 2019-03-04


#像普通表中加载数据
load data local inpath '/home/hadoop/order_partition.txt' into table t_order;

#动态加载数据到分区表中
#开启动态分区功能
set hive.exec.dynamic.partition=true;
#设置hive为非严格模式
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table order_dynamic_partition partition(order_time) select order_number, order_price, order_time from t_order;