hive梳理

what's

Hive是由Facebook开发的构建在Hadoop之上的数据仓库平台。
它本身并不存储和处理数据，依赖于HDFS存储数据，依赖MR处理数据。而hive提供了一个类似sql的查询语言HiveQL来进行查询、变换数据等操作。当然HiveQL语句的底层是转换为相应的mapreduce代码进行执行，一般采用批处理的方式对海量数据进行处理。
设计目标就是将hadoop上的数据可以执行SQL操作。
让熟悉SQL编程的开发人员能够轻松的向Hadoop平台上转移。
数据仓库存储的是静态数据，很适合采用MR进行批处理。Hive还提供了一系列对数据进行提取、转换、加载的工具，可以存储、查询和分析存储在HDFS上的数据。

特点

是分布式的关系型数据库。主要用来并行分布式处理大量数据。hive中的所有查询除了select * from table;都是需要通过MapReduce的方式来执行的。
由于要走MapReduce（8088mr监控端口号），即使一个只有1行1列的表，如果不是通过select * from table;方式来查询的，可能也需要8、9秒。
但hive比较擅长处理大量数据。当要处理的数据很多，并且Hadoop集群有足够的规模，这时就能体现出它的优势。

与mysql等数据库比较

1、查询语言
由于 SQL 被广泛的应用在数据仓库中，因此，专门针对 Hive 的特性设计了类 SQL 的查询语言 HQL。熟悉 SQL 开发的开发者可以很方便的使用 Hive 进行开发。
2、数据存储位置
hive的数据都是存储在 HDFS 中的。而mysql数据库则可以将数据保存在本地文件系统中。
3、数据格式
hive 中没有定义专门的数据格式，数据格式可以由用户指定，用户定义数据格式需要指定三个属性：列分隔符（通常为空格、”t”、”x001″）、行分隔符（”n”）以及读取文件数据的方法（Hive 中默认有三个文件格式 TextFile，SequenceFile 以及 RCFile）。由于在加载数据的过程中，不需要从用户数据格式到 Hive 定义的数据格式的转换，因此，Hive 在加载的过程中不会对数据本身进行任何修改，而只是将数据内容复制或者移动到相应的 HDFS 目录中。
而在数据库中，不同的数据库有不同的存储引擎，定义了自己的数据格式。所有数据都会按照一定的组织存储，因此，数据库加载数据的过程会比较耗时。
4、数据更新
由于 hive 是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，hive 中不支持对数据的改写（hive(0.14) 后支持）和添加，所有的数据都是在加载的时候中确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以添加、修改数据。

5、索引
因为hive 在加载数据的过程中不会对数据进行任何处理，甚至不会对数据进行扫描，因此也没有对数据中的某些 Key 建立索引。hive 要访问数据中满足条件的特定值时，需要暴力扫描整个数据，因此访问延迟较高。
不过由于 MapReduce 的引入，hive 可以并行访问数据，因此即使没有索引，对于大数据量的访问，hive 仍然可以体现出优势。
数据库中，通常会针对一个或者几个列建立索引，因此对于少量的特定条件的数据的访问，数据库可以有很高的效率，较低的延迟。由于数据的访问延迟较高，决定了 hive 不适合在线数据查询。

6、执行
hive 中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的（类似 select * from tbl 的查询不需要 MapReduce）。
而数据库通常有自己的执行引擎。

7、执行延迟
之前提到hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外 一个导致 hive 执行延迟高的因素是 MapReduce 框架。由于 MapReduce 本身具有较高的延迟，因此在利用 MapReduce 执行 hive 查询时，也会有较高的延迟。
相对的，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，hive 的并行计算显然能体现出优势。

8、可扩展性
由于 hive 是建立在 Hadoop 之上的，因此 Hive 的可扩展性是和 Hadoop 的可扩展性是 一致的。
而数据库由于 ACID 语义的严格限制，扩展行非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有 100 台左右。

9、数据规模
由于 Hive 建立在集群上并可以利用 MapReduce 进行并行计算，因此可以支持很大规模的数据.
数据库可以支持的数据规模较小。

数据仓库与数据库的区别

数据库是面向事务的设计，数据仓库是面向主题设计的。
数据库一般存储在线交易数据，数据仓库存储的一般是历史数据。
数据库设计是尽量避免冗余，一般采用符合范式的规则来设计，数据仓库在设计是有意引入冗余，采用反范式的方式来设计。
数据库是为捕获数据而设计，数据仓库是为分析数据、用于支持管理决策而设计，它的两个基本的元素是维表和事实表。（维是看问题的角度，比如时间，部门，维表放的就是这些东西的定义，事实表里放着要查询的数据，同时有维的ID）

与hbase比较

1、hive是分布式的关系型数据库，而hbase是分布式的非关系型的。
2、hive是面向行存储的数据库，而hbase是面向列的数据库。
3、hive支持类sql语言，通过数据库的方式来操作hdfs文件系统，为了简化编程，底层计算方式为mapreduce。而hbase的Shell命令是以JRuby为核心编写的，不支持sql。
4、hive本身不存储和计算数据，它完全依赖于HDFS和MapReduce，hive中的表纯逻辑；而hbase是为查询而生的，它通过组织起节点內所有机器的內存，提供一個超大的內存Hash表，是物理表。
5、hive是高延迟、结构化和面向分析的常用于离线分析业务，hbase是低延迟、非结构化和面向编程的，常用于在线业务。
6.hive可以认为是map-reduce的一个包装。hive的意义就是把好写的hive的sql转换为复杂难写的map-reduce程序。而hbase可以认为是hdfs的一个包装。他的本质是数据存储，是个NoSql数据库；hbase部署于hdfs之上，并且克服了hdfs在随机读写方面的缺点。

三个组成模块

用户接口模块

含CLI、client、WebUI、JDBC、Thrift Server等，用来实现对Hive的访问。
CLI是Hive自带的命令行界面hive shell；WebUI是Hive的一个简单网页界面通过hive --server hwi启动后，查看9999端口；JDBC、ODBC以及Thrift Server可向用户提供进行编程的接口，其中Thrift Server是基于Thrift软件框架开发的，提供Hive的RPC通信接口。

元数据存储模块（Metastore）

是一个独立的关系型数据库，通常与MySQL数据库连接后创建的一个MySQL实例，也可以是Hive自带的Derby数据库实例。此模块主要保存表模式和其他系统元数据，如表的名称、表的列及其属性、表的分区及其属性、表的属性、表中数据所在位置信息等。

驱动模块（Driver）

含解析器、编译器、优化器、执行器等，负责把HiveQL语句转换成一系列MR作业，所有命令和查询都会进入驱动模块，通过该模块的解析变异，对计算过程进行优化，然后按照指定的步骤执行。
解析器：将sql字符串转换成抽象语法树AST，一般用第三方工具库如antlr完成。然后对AST进行语法分析，如表是否存在、字段是否存在、sql语义是否有误。
编译器：将AST编译生成逻辑执行计划。
优化器：多逻辑执行计划进行优化；
执行器：把逻辑执行计划转换成可以运行的物理计划，即mapreduce/spark/tez。

有些查询不会走mapreduce，如select * from tbl

安装配置

http://sishuok.com/forum/blog...

hive数据

数据类型

HQL支持基本类型和复杂类型两大类数据类型。
基本类型包括TINYINT(1byte), SMALLINT(2byte), INT(4byte), BIGINT(8byte), FLOAT(4byte), DOUBLE(8byte),DECIMAL(32精度) BOOLEAN(-), STRING(2G)。
复杂类型包括ARRAY(一组有序数组，类型必须一致), MAP(无序键值对，键值内部字段类型必须相同，而且要求key的类型为基本数据类型), STRUCT(一组字段，类型任意)如<a:STRING,B:INT,C:DOUBLE>。

注意是指hql中使用的数据形式，而不是表中序列化存储的格式。

文件格式

hive 中的文件格式有：

  SEQUENCEFILE
  TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  ORC         -- (Note: Available in Hive 0.11.0 and later)
  PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  AVRO        -- (Note: Available in Hive 0.14.0 and later)
  INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

还可以自定通过InputFormat、OutputFormat

采用RCfile的格式读取的数据量（373.94MB）远远小于sequenceFile的读取量（2.59GB）
执行速度前者(68秒)比后者(194秒)快很多。

文件压缩

压缩比：bzip2>gzip>lzo
解压速度：lzo>gzip>bzip2

snappy的执行进度远远高于bz的执行进度。

在hive中使用压缩需要灵活的方式，如果是数据源的话，采用RCFile+bz或RCFile+gz的方式，这样可以很大程度上节省磁盘空间；
而在计算的过程中，为了不影响执行的速度，可以浪费一点磁盘空间，建议采用RCFile+snappy的方式，这样可以整体提升hive的执行速度。

至于lzo的方式，也可以在计算过程中使用，只不过综合考虑（速度和压缩比）还是考虑snappy适宜。

表的类型

管理表（内部表，默认的 ）

默认存储在/user/hive/warehouse下，也可以通过location指定，但一般不会指定。
内部表是hive进行管理的，删除表时，会删除表数据以及元数据。

托管表（外部表，企业用的多）

外部表在创建表时可以自己指定目录位置(LOCATION)，一般会指定（目录要求先存在）。一般情况下，我们在创建外部表的时候会将表数据的存储路径定义在hive的数据仓库路径之外。
外部表的数据不是有hive进行管理的，删除表时，只会删除元数据不会删除表数据。
供多个部门使用（包括删除）。

hive Cli

show

主要作用是查看database、table、function等组件的名称信息，也就是通过show命令我们可以知道我们的hive中有那些database；当前database中有那些table。等等。和mysql的show命令类型。
show databases ;数据库
show databases like 'db_hive*' ;
show tables ;
show create table 表名; 得到创建该表的语句
show partitions 表名 ; 查看表中有多少分区

show functions ;查看自带函数

describe

主要作用是获取database、table、partition的具体描述信息，包括存储位置、字段类型等信息。
desc database db_hive_03 ;
desc database extended db_hive_03 ;

desc student ;看表结构
desc extended student ;看表完整信息，但比较乱
desc formatted student ;看格式化的信息，常用

desc function upper ;看函数upper的使用说明
desc function extended upper ;看函数的案例演示
select id ,upper(name) uname from db_hive.student ;

create

创建数据库

create database db_hive_01 ;
create database if not exists db_hive_02 ; 标准
create database if not exists db_hive_03 location '/user/cyan/hive/warehouse/db_hive_03.db' ;
默认是/user/hive/，没有会自动创建

use db_hive;当前使用数据库，否则用数据库名.表名

创建表

简单如：create table student(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';制表符分隔各行数据。

复杂一点：

create table IF NOT EXISTS default.log_20150913(
ip string COMMENT 'remote ip address' ,
user string ,
req_url string COMMENT 'user request url')
COMMENT 'BeiFeng Web Access Logs'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
STORED AS TEXTFILE ;//以文本文件形式保存，列中的分隔符是空格，行中的分隔符回车
[ROW FORMAT row_format], row_format格式：
        delimited fields terminated by '\001' collection terminated by '\002' map keys terminated by '\003' lines terminated by '\004' NULL DEFINED AS '\N'
[STORED AS file_format]


file_format格式：
        sequencefile
        textfile(default)
        rcfile
        orc
        parquet
        avro

create table IF NOT EXISTS default.log_20150913_sa AS select ip,req_url from default.log_20150913 ;
AS sselect来自另外一张表（分表），利用其他表的格式like：
create table if not exists default.dept_like like default.dept ;

eg:创建员工表

create table IF NOT EXISTS default.emp(
empno int,
ename string,
job string,
mgr int,上级
hiredate string,入职时间
sal double,
comm double,奖金
deptno int部门
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

eg:创建部门表

create table IF NOT EXISTS default.dept(
deptno int,
dname string,
loc string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

创建分区表partitioned by

有时候想把数据细部划分，分各个目录,比如日志文件：

/user/hive/warehouse/log/
                        /20150911/
                            20150911.log
                        /20150912/
                            20150912.log

此时创建表时可用partitioned by指定分区依据：

create EXTERNAL table IF NOT EXISTS default.emp_partition(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
partitioned by (month string,day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;

往分区表导入数据时，需要指定确定分区的字段值：
load data local inpath '/opt/datas/emp.txt' into table default.emp_partition partition (month='201509',day='13') ;

如果建表时指定了数据位置，即加了location '/user/cyan/hive/warehouse/emp_partition';，则直接把txt数据上传至hdfs的该目录中即可。

从分区表查询数据，也需要指定确定分区的字段值：
select * from emp_partition where month = '201509' and day = '13' ;

除了使用patitioned，还可以手动在表的hdfs目录下创建分区目录达到建分区表的目的：

第一种方式
dfs -mkdir -p /user/hive/warehouse/dept_part/day=20150913 ;
dfs -put /opt/datas/dept.txt /user/hive/warehouse/dept_part/day=20150913 ;
hive (default)> msck repair table dept_part ;修复后，才能select出数据

第二种方式
dfs -mkdir -p /user/hive/warehouse/dept_part/day=20150914 ;
dfs -put /opt/datas/dept.txt /user/hive/warehouse/dept_part/day=20150914 ;
alter table dept_part add partition(day='20150914');
show partitions dept_part ; 查看表中有多少分区

load

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)];
local 本地数据，不加则是hdfs数据

partition (partcol1=val1,...)分区表加载，特殊性，详见分区表的创建。

eg:
load data local inpath '/opt/datas/student.txt'into table student ;加载数据 utf-8格式
load data local inpath '/opt/datas/student1.txt' overwrite into table student ;覆盖原数据，默认是追加

insert

创建表时通过insert加载数据

create table default.emp_ci like emp ;
insert into table default.emp_ci select * from default.emp ;

导出数据

insert overwrite local directory '/opt/datas/hive_exp_emp' select * from default.emp ;导出数据到本地
insert overwrite directory '/user/beifeng/hive/hive_exp_emp' select * from default.emp ;导出数据到hdfs

导出数据到本地并定义格式

insert overwrite local directory '/opt/datas/hive_exp_emp2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY '\n'
select * from default.emp ;

location

创建表的时候通过location指定加载数据

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

import

将外部数据导入hive表中

create table db_hive.emp like default.emp ;
import table db_hive.emp from '/user/beifeng/hive/export/emp_exp';

export

EXPORT TABLE default.emp TO '/user/beifeng/hive/export/emp_exp' ;将Hive表中的数据，导出到外部(HDFS)

其他导出数据方式

1.hive shell
bin/hive -e "select * from default.emp ;" > /opt/datas/exp_res.txt

2.sqoop

alter

alter table dept_like rename to dept_like_rename ;改表名
也可以加列名

drop

drop database if exists db_hive_03 ;非空就不能删
drop database db_hive_03 cascade;非空能删，目录也被删了
drop database if exists db_hive_03 ;
清除表里的数据：truncate table db_hive_03 ;

select

select * from student ;没有走mapreduce
select id from student ;走MR
官网LanguageManual中：

[WITH CommonTableExpression (, CommonTableExpression)*]
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[CLUSTER BY col_list
  | [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]


select * from emp 
where 
GROUP BY 
LIMIT number

from语句主要是指定从那个数据表中查询数据，有两种使用方式：分别可以在select后和with后&select前使用，语法格式：

1. [with ...] select ... from ...
2. [with ...] from ... select ...

示例：
select * from students;
from students select *;

子查询

hive对子查询的支持有限，只支持嵌套select子句，而且只能在from和with语句块中使用子查询。语法规则如下：
.... from (select statement) [[as] tmp_name]....

limit、= >= <= between and

select * from emp limit 5 ;
select t.empno, t.ename, t.deptno from emp t where t.sal between 800 and 1500 ;

is null / is not null /in /not in

select t.empno, t.ename, t.deptno from emp t where comm is null ;

max/min/count/sum/avg

select count(*) cnt from emp ;
select max(sal) max_sal from emp ;
select sum(sal) from emp ;
select avg(sal) from emp ;

group by

eg:每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno ;

eg:每个部门中每个岗位的最高薪水
select t.deptno, t.job, max(t.sal) avg_sal from emp t group by t.deptno, job ;

having

eg:每个部门的平均薪水大于2000的部门
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

=======================================

join

目前hive不支持in或not in 中包含查询子句的语法，所以只能通过left join实现。
1.等值jion
join ... on
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno ;
不支持<>等非等值连接方式。

2.左连接
left join
select e.empno, e.ename, d.deptno, d.dname from emp e left join dept d on e.deptno = d.deptno ;

3.右连接
right join
select e.empno, e.ename, e.deptno, d.dname from emp e right join dept d on e.deptno = d.deptno ;

4.全连接
full join
select e.empno, e.ename, e.deptno, d.dname from emp e full join dept d on e.deptno = d.deptno ;

5.半连接(LEFT SEMI JOIN)
是hive特有的，hive中不支持in/exists操作，所以hive提供了一个替代方案。需要注意的是，被连接的表(右表)，不能出现在查询列/其他部分(where等)中，只能出现在on字句中(出现也是无效的)。
提出半连接的主要作用其实是提高查询效率，真正来讲的话，hive中可以使用其他连接方式来代替半连接，但是就效率而已的话，还是半连接比较高效。
语法格式：table_reference LEFT SEMI JOIN table_factor join_condition;

6.多表连接
多表连接的时候，一般先进行left semi join，然后再进行join, 再进行外连接。(减少数据量)。

补充：
join过滤条件，可以将where的过滤条件移动到join的过滤条件中去，这样可以减少网络数据量。
join执行顺序都是从左到右，不管是那种join方式，那么一般将大的表放到右边，这样可以节省内存&减少网络传输。
如果所有被连接的表都是小表，那么可以使用mapjoin,将需要连接的表数据全部读入mapper端内存中。也就是说你使用mapjoin的前提就是你的连接数据比较小，mapjoin需要和其他join方式一起使用，一般情况下使用mapjoin的时候，推荐使用内连接。语法格式为:
select /*+ MAPJOIN(table_ref1) */ ... from table_ref join table_ref1 on ....;
mapjoin只适合连接表是小表的情况，是一种空间换时间的解决方案。

order by

对全局数据的一个排序，仅仅只有1个reduce
select * from emp order by empno desc ;降序

sort by

对每一个reduce内部数据进行排序的，全局结果集来说不是排序
`set mapreduce.job.reduces= 3;
select * from emp sort by empno asc ;
insert overwrite local directory '/opt/datas/sortby-res' select * from emp sort by empno asc ;`

distribute by

分区partition，类似于MapReduce中分区partition,对数据进行分区，结合sort by进行使用（分区再排序）
insert overwrite local directory '/opt/datas/distby-res' select * from emp distribute by deptno sort by empno asc ;

注意事项：
distribute by 必须要在sort by 前面。

cluster by

当distribute by和sort by 字段相同时，可以使用cluster by ;
insert overwrite local directory '/opt/datas/cluster-res' select * from emp cluster by empno ;

怎样预防全表扫描

1.应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫
3.描应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
4.in 和 not in，用具体的字段列表代替，不要返回用不到的任何字段。in 也要慎用，否则会导致全表扫描
5.避免使用模糊查询
6.任何地方都不要使用select* from t;

操作文件系统

dfs -rm -R /user/hive/warehouse/….;删除
dfs -ls / ; 查看hdfs文件系统
!ls /opt/datas ；查看本地文件系统

HQL语句转换成MapReduce作业的基本原理

https://blog.csdn.net/nameles...