HiveDay01

一、Hive是什么

1、Hive是建立在Hadoop上的数据仓库基础架构。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL），这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。Hive定义了简单的SQL查询语言，称为HQL，它允许熟悉SQL的用户查询数据。同时，这个语言也允许熟悉MapReduce开发者的开发自定义的mapper和reducer来处理内建的Mapper和reducer无法完成的复杂的分析工作。

2、数据仓库（Data Warehouse），简写为DW或DWH。数据仓库，是为企业所有级别的决策制定过程，提供所有类型的数据支持的战略集合。它出于分析性报告和决策支持目的而建。为需要业务智能化的企业，提供指导业务流程改进、监视时间、成本、质量以及控制。

3、Hive是SQL解析引擎，他将SQL语句转译成M/R Job然后在Hadoop执行。

4、Hive的表其实就是HDFS的目录，按表名把文件夹分开。如果是分区表，则分区值是子文件夹，可以直接在M/R Job里使用这些数据。

5、Hive相当于Hadoop的客户端工具，部署时不一定放在集群管理节点中，可以放在某个节点上。

二、HIVE架构

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

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

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

3、驱动器

​ （1）解析器（SQL Parser）：将SQL字符串转换为抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr：对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。

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

​ （3）优化器（Query Optimizer）：对逻辑执行计划进行优化

​ （4）执行器（Execution）：把逻辑执行计划转换成可运行的物理计划。对于Hive来说，就是MR/Spark。

三、Hive与传统数据库的比较

查询语言	HiveQL	SQL
数据存储位置	HDFS	Raw Device or 本地FS
数据格式	用户定义	系统决定
数据更新	不支持（1.x以后版本支持）	支持
索引	新版本有，但弱	有
执行	MapReduce	Executor
执行延迟	高	低
可扩展性	高	低
数据规模	大	小

1、查询语言。类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便地使用Hive进行开发。

2、数据存储位置。所有Hive的数据都是存储在HDFS中的。而数据库则可以将数据保存在块设备或者本地文件系统中。

3、数据格式。Hive中没有定义专门的数据格式。而在数据库中，所有数据都会按照一定的组织存储，因此，数据库加载数据的过程会比较耗时。

4、数据更新。Hive对数据的改写和添加比较弱化，0.14版本之后支持，需要启动配置项。而数据库中的数据通常是需要经常进行修改的。

5、索引。Hive在加载的过程中不会对数据进行任何的处理。因此访问延迟较高。数据库可以有很高的效率，较低的延迟。由于数据的访问延迟较高，决定了Hive不适合在线数据查询。

6、执行计算。Hive中执行是通过MapReduce来实现的而数据库通常有自己的执行引擎。

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

四、Hive的存储格式

1、Hive的数据存储基于Hadoop HDFS

2、Hive没有专门的数据文件格式，常见的有以下几种

​ TEXTFILE

​ SEQUENCEDILE

​ AVRO

​ RCFILE

​ ORCFILE

​ PARQUET

1、TextFile

​ TextFile即正常的文本格式，是Hive默认文件存储格式，因为大多数情况下元数据文件都是以text文件格式保存（便于查看验数和防止乱码）。此种格式的表文件在HDFS上是明文，可以用hadoop fs -cat命令查看，从HDFS上get下来后也可以直接读取。

​ TEXTFILE存储文件默认每一行就是一条记录，可以指定任意的分隔符进行字段间的分割。但这个格式无压缩，需要很大的存储空间。虽然可结合Gzip、Bzip2、Snappy等使用，使用这种格式，Hive不会对数据进行切分，从而无法对数据进行并行操作。

​ 一般只有与其他系统由数据交换的接口表采用TEXTFILE格式，其他事实表和维度表都不建议使用。

create table students(

  id bigint comment '学生id',

  name string comment '学生姓名',

  age int comment '学生年龄',

  gender string comment '学生性别',

  clazz string comment '学生班级'

) comment '学生信息表' 

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

上传数据至HDFS
dfs -put /usr/local/soft/hive-3.1.2/data/ /user/hive/warehouse/filetest.db/students

通过查看HDFS路径可以看到数据大小没有发生变化 大小为37M

2、RCFile：

​ Record Columnar的缩写。是Hadoop中第一个列文件格式。能够很好的压缩和快速的查询功能。通常写操作比较慢，比非列形式的文件格式需要更多的内存空间和计算量。RCFile是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证在同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取。

create table students_rcFile(
  id bigint comment '学生id',
  name string comment '学生姓名',
  age int comment '学生年龄',
  gender string comment '学生性别',
  clazz string comment '学生班级'
) comment '学生信息表' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS RCFile;

7.插入数据：
insert into table students_rcFile select * from students;

8.通过查看HDFS路径可以看到数据经过压缩大小为26.44 M

3、ORCFile：

​ Hive从0.11版本开始提供了ORC的文件格式，ORC文件不仅仅是一种列式文件存储格式，最重要的是有着很重的压缩比，并且对于MapReduce来说是可切分（Split）的。因此，在Hive中使用ORC作为表的文件存储格式，不仅可以很大程度的节省HDFS存储资源，而且对数据的查询和处理性能有着非常大的提升，因为ORC较其他文件格式压缩比高，查询任务输入的数据量减少，使用多个task也就减少了。ORC能很大程度的节省存储和计算资源，但他在读写的时候需要消耗额外的CPU资源来压缩和解压缩，当然这部分的CPU消耗是非常少的。

create table students_orc(
  id bigint comment '学生id',
  name string comment '学生姓名',
  age int comment '学生年龄',
  gender string comment '学生性别',
  clazz string comment '学生班级'
) comment '学生信息表' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS ORC;

10.插入数据：
insert into table students_orc select * from students;

11.通过查看HDFS路径可以看到数据经过压缩大小为 220.38 KB,同时插入数据时与RCFile格式时间相差不大

4、Parquet：

​ 通常我们使用关系数据库存储结构化数据，而关系数据库中使用数据模型都是扁平式的，遇到诸如List、Map和自定义的Struct的时候就需要用户在应用层解析。但是在大数据环境下，通常数据的来源是服务端的埋点数据，很有可能把程序中的某些对象内容作为输出的一部分，而每一个对象都可能是嵌套的，所以如果能够原生的支持这种数据，这样在查询的时候就不需要额外的解析便能获得想要的结果。

​ Parquet的灵感来自于2010年Google发表的Dremel论文，文中介绍了一种支持嵌套结构的存储格式，并且使用了列式存储的方式提升查询性能。Parquet仅仅是一种存储格式，他是语言、平台无关的，并且不需要和任何一种数据处理框架绑定。这也是Parquet相较于orc的仅有优势：支持嵌套结构。Parquet没有太多其他可圈可点的地方，比如它不支持update操作（数据写成后不可修改），不支持ACID等。

create table students_parquet(
  id bigint comment '学生id',
  name string comment '学生姓名',
  age int comment '学生年龄',
  gender string comment '学生性别',
  clazz string comment '学生班级'
) comment '学生信息表' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS PARQUET;

13.insert into table students_parquet select * from students;

14.通过查看HDFS路径可以看到数据经过压缩大小为 3.01 MB 写入数据所花费时间相比ORCFile更少

5、SEQUENCEFILE：

​ SequenceFile是Hadoop API提供的一种二进制文件，他将数据以<key,value>的形式序列化到文件中。这种二进制文件内部使用Hadoop的标准的Writable接口实现序列化和反序列化。它与Hadoop API中的MapFile是相互兼容的。Hive中的SequenceFile继承自Hadoop API的SequenceFile，不过它的key为空，使用value存放实际的值，这样是为了避免MR在运行map阶段的排序过程。

​ SequenceFile支持三种压缩选择：NONE、RECORD、BLOCK。Record压缩率低，一般建议使用BLOCK压缩。SequenceFile最重要的优点就是Hadoop原生支持较好，有API，但除此之外平平无奇，实际生产中不会使用。

6、AVRO：

​ Avro是一种用于支持数据保密集型的二进制文件格式。他的文件格式更为紧凑，若要读取大量数据时，Avro能够提供更好的序列化和反序列化性能。并且Avro数据文件天生带Schema定义的，所以他不需要开发者在API级别实现自己的Writable对象。Avro提供的机制使动态语言可以方便的处理Avro数据。最近多个Hadoop子项目都支持Avro数据格式，如Pig、Hive、Flume、Sqoop和Hcatalog。

7、Hive的四大常用存储格式存储效率以及执行速度对比

8、Hive操作客户端

常用的两个：CLI、JDBC/ODBC

1、CLI，即Shell命令行

2、JDBC/ODBC是Hive的Java，与使用传统数据库JDBC的方式类似。

3、Hive将元数据存储在数据库中（metastore），目前只支持mysql、derby。

4、Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的说句所在目录等；由解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划（plan）的生成。生成的查询计划在HDFS中，并在随后由MapReduce调用执行。

5、Hive的数据存储在HDFS中，大部分的查询由MaoReduce完成（包含*的查询，比如select * from table 不会生成MapReduce任务）。

9、Hive的metastore

​ 1、metastore是Hive元数据的集中存放地。

​ 2、metastore默认使用内嵌的derby数据库作为存储引擎

​ 3、Derby引擎的缺点：一次只能打开一个会话

​ 4、使用MySQL作为外置存储引擎，多用户同时访问

​ 5、源数据库详见：查看mysql SDS表和TBLS表

​ 6、http://blog.csdn.net/haozhugogo/article/details/73274832

五、Hive–HQL语法-DDL

1、基础语法

创建数据库： create database xxxx;

查看数据库： show databases;

删除数据库： drop database tmp;

强制删除数据库：drop database tmp cascade;

查看表：SHOW TABLES;

查看表的元信息：

​	desc test_table;

​	describe formatted test_table;

​	describe extended test_table;

查看建表语句：show create table table_XXX;

重命名表：

​	alter table test_table rename to new_table;

修改列数据类型：

​	alter table lv_test change column colxx string;

增加、删除分区：

​	alter table test_table add partition (pt=xxxx);

​	alter table test_table drop if exists partition(...);

2、建库语法

CREATE DATABASE [IF NOT EXISTS] database_name

​	[COMMENT database_comment]

​	[LOCATION hdfs_path]

​	[WITH DBPROPERTIES (property_name=property_value,...)];

3、创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 	--EXTERNAL表示外部表的标志

[(col_name data_type [COMMENT col_comment], ...)]	--列名 数据类型 注释信息

[COMMENT table_comment]	--表的注释信息

[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]	--PARTITIONED BY构建分区别 包括列名 数据类型 注释信息

[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]	--CLUSTERED BY分桶 列名

--SORTED BY分桶的排序

[

[ROW FORMAT row_format]	--数据的分割信息

[STORED AS file_format]	--表示存储格式

|STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...)] (Note: only available starting with 0.6.0)	--自定义存储类

]

[LOCATION hdfs_path]	--表的存储位置

[TBLPROPERTIES (property_name=property_value, ...)] (Note: only available starting with 0.6.0)	-- 表示表的配置信息

[AS select_statement] (Note: this feature is only available starting with 0.5.0)	--AS 通过查询表创建（接查询语句）

4、分区

1、分区的概念和分区表：

​	分区指的是在创建表时指定分区空间，实际上就是在hdfs上表的目录下再创建子目录。在使用数据时如果指定了需要访问的分区名称，则只会读取相应的分区，别面全表扫描，提高查询效率。

​	建表语句：

CREATE TABLE page_view(viewTime INT,ip STRING) PARTITIONED BY (dt STRING, country STRING) ROW FORMAT DELIMITED

FIELDS TERMNATED BY '\001'

用法：select * from tab where dt="20181208" and country = "安徽";

2、卡其动态分区支持

hive>set hive.exec.dynamic.partition=true;

hive>set hive.exec.dynamic.partition.mode=nostrict;

hive>set hive.exec.max.dynamic.partitions.pernode=1000;

加大动态分区

3、操作语句

insert into table anhui_air partition(dt) select part,pm25,date_time from anhui_air2;

开启后，查询的语句中最后的对应的字段作为分区的字段，会按照这个字段进行分区，相同的值会放到同一个分区。

5、Hive加载数据

1、使用load data命令

从hdfs导入数据，路径可以是目录，会将目录下所有文件到导入，但是文件格式必须一致

load data inpath "/test" into table dainxin_test;

2、从本地文件系统导入

load data local inpath "/test/" into table dianxin_test;

3、表对表加载

​	1）create table IF NOT EXISTS dianxin_test2 as select * from dianxin_test;

​	2）insert[overwrite] into table dianxin_test2 select * from dianxin_test;

4、注意：

​	1）如果建表语句没有指定存储路径，不管是外部表还是内部表，存储路径都是默认在hive/warehouse/xx.db/表名的目录下。加载的数据也会移动到该表的存储目录下。注意是移动，不是复制。

​	2）删除外部表，文件不会被删除，对应目录也不会删除。

六、Hive的数据类型

基本数据类型

整型

TINYINT——微整型，只占用一个字节，只能存储0-255的整数

SMALL——小整形，占用2个字节，存储范围-32768到32767
INT– 整型，占用4个字节，存储范围-2147483648到2147483647。
BIGINT– 长整型，占用8个字节，存储范围-2^63到263-1。
布尔型BOOLEAN — TRUE/FALSE
浮点型FLOAT– 单精度浮点数。
DOUBLE– 双精度浮点数。
字符串型STRING– 不设定长度。
日期类型：
1，Timestamp 格式“YYYY-MM-DD HH：MM：SS.fffffffff”（9位小数位精度）
2，Date DATE值描述特定的年/月/日，格式为YYYY-MM-DD。
复杂数据类型：
Structs，Maps，Arrays

七、Hive JDBC连接

1.开启命令：
	hive --service hiveserver2

2.连接命令：
	beeline -u jdbc:hive2://master:10000 -n root

	注意： 如果报以下错误：
		Error: Could not open client transport with JDBC Uri: jdbc:hive2://master:10000: 
		Failed to open new session: java.lang.RuntimeException: 
		org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.authorize.AuthorizationException): 
		User: root is not allowed to impersonate root (state=08S01,code=0)
	解决方法：
		先关闭Hadoop集群 ：stop-all.sh
		配置core-site.xml：添加如下内容：
<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>

<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>
		重启集群：start-all.sh

3.查看服务有没有启动：(hive的JDBC启动非常慢，需要等待一定时间，Hive Session ID出现4个以后基本可以看到端口启动)
	netstat -nplt | grep 10000

八、HIVE JDBC基本语法

1、创建数据库

CREATE DATABASE IF NOT EXISTS my_database;

	DROP DATABASE my_database;

	CREATE DATABASE IF NOT EXISTS my_database COMMENT "测试数据库创建";

	通过指定的HDFS路径创建数据库
		dfs -mkdir /testDatabase;
--		CREATE DATABASE IF NOT EXISTS test_database COMMENT "测试数据库创建" LOCATION "/testDatabase";

	创建数据库时增加配置信息
		CREATE DATABASE IF NOT EXISTS test_database2 COMMENT "测试数据库创建" WITH DBPROPERTIES("author"="xingtong","application"="save base data");

2、查看数据库信息

DESC DATABASE my_database;
	注意：如果中文显示乱码，请修改MYSQL中hive库的DBS中的DESC字段的字符集为utf8
			alter table DBS modify column DESC  varchar(4000) character set utf8;

	desc database extended test_database2;
	查看数据库创建的详细信息，包括配置的参数

	SHOW DATABASE; 

3、删除数据库

	 3.1 DROP DATABASE my_database;
	 	如果数据库不为空 删除会报错： message:Database my_database is not empty.

	 3.2 DROP DATABASE my_database cascade;
	 	强制删除数据库

4、创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name  -- EXTERNAL表示外部表的标志  
  [(col_name data_type [COMMENT col_comment], ...)]  -- 列名  数据类型  注释信息
  [COMMENT table_comment]  -- 表的注释信息
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]  -- PARTITIONED BY 构建分区表 包括 列名  数据类型 注释信息
  [CLUSTERED BY (col_name, col_name, ...) --CLUSTERED BY 分桶 列名
  [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] -- SORTED BY 分桶中的排序 INTO num_buckets BUCKETS 具体分成多少桶
  [
   [ROW FORMAT row_format] -- 表示数据的分隔信息
   [STORED AS file_format] -- 表示存储格式
   | STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]  (Note:  only available starting with 0.6.0) --自定义存储类
  ]
  [LOCATION hdfs_path]  -- 表的存储位置
  [TBLPROPERTIES (property_name=property_value, ...)]  (Note:  only available starting with 0.6.0) -- 表示表的配置信息
  [AS select_statement]  (Note: this feature is only available starting with 0.5.0.)  -- AS 通过查询表创建(接查询语句)

4.1 ROW FORMAT用法

CREATE TABLE IF NOT EXISTS filetest.total_score(
student_id STRING COMMENT "学生编号",
score int COMMENT "总分"
)ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
STORED AS TEXTFILE;
load data local inpath "/usr/local/soft/hive-3.1.2/data/total_score.txt" into table filetest.total_score;


CREATE TABLE IF NOT EXISTS filetest.total_score2(
student_id STRING COMMENT "学生编号",
score int COMMENT "总分"
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ","
STORED AS TEXTFILE;
load data local inpath "/usr/local/soft/hive-3.1.2/data/total_score.txt" into table filetest.total_score2;
注意：如果数据和分隔符不匹配则原始数据中的所有数据会变成新表中的一列数据

4.2 LOCATION用法

dfs -mkdir /testDatabase/total_score;
CREATE TABLE IF NOT EXISTS filetest.total_score3(
student_id STRING COMMENT "学生编号",
score int COMMENT "总分"
)ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
STORED AS TEXTFILE
LOCATION "/testDatabase/total_score";
load data local inpath "/usr/local/soft/hive-3.1.2/data/total_score.txt" into table filetest.total_score3;

4.3 EXTERNAL 用法

dfs -mkdir /testDatabase/total_score;
CREATE EXTERNAL TABLE IF NOT EXISTS filetest.total_score3(
student_id STRING COMMENT "学生编号",
score int COMMENT "总分"
)ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
STORED AS TEXTFILE
LOCATION "/testDatabase/total_score";
load data local inpath "/usr/local/soft/hive-3.1.2/data/total_score.txt" into table filetest.total_score3;


CREATE EXTERNAL TABLE IF NOT EXISTS filetest.total_score4(
student_id STRING COMMENT "学生编号",
score int COMMENT "总分"
)ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
STORED AS TEXTFILE;
load data local inpath "/usr/local/soft/hive-3.1.2/data/total_score.txt" into table filetest.total_score4;

DROP TABLE total_score4; -- 数据依然存在
	注： 外部表与普通内部表的区别在于：外部表删除表时不会删除对应的数据 并且和表数据存储位置无关

4.4 STORED AS 用法：设置数据存储格式

使用方法一:
CREATE EXTERNAL TABLE IF NOT EXISTS filetest.total_score5(
student_id STRING COMMENT "学生编号",
score int COMMENT "总分"
)ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
STORED AS ORC;
--load data local inpath "/usr/local/soft/hive-3.1.2/data/total_score.txt" into table filetest.total_score5;
注意：如果数据存储格式为压缩格式，那么就不能直接将文本数据加载至表中
INSERT INTO TABLE filetest.total_score5 SELECT * FROM filetest.total_score

	使用方法二：
通过查询语句结果去创建一个新表
CREATE TABLE IF NOT EXISTS filetest.total_score7
STORED AS ORC
AS SELECT student_id,score FROM filetest.total_score;

4.5 TBLPROPERTIES 用法

CREATE EXTERNAL TABLE IF NOT EXISTS filetest.total_score8(
student_id STRING COMMENT "学生编号",
score int COMMENT "总分"
)ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
STORED AS TEXTFILE
TBLPROPERTIES("author"="xing","application"="学生表,保存学生的总分,如有错误请联系 XXX");

4.6 AS 用法

CREATE TABLE IF NOT EXISTS filetest.total_score8
STORED AS ORC
AS SELECT student_id,score FROM filetest.total_score;

4.7 PARTITIONED BY 分区

-- 将学生信息表中不同性别的学生进行分区保存
 

CREATE TABLE IF NOT EXISTS filetest.partition_student(
id STRING COMMENT "学生ID",
name STRING COMMENT "学生姓名",
age int COMMENT "年龄",
clazz STRING COMMENT "班级"
) PARTITIONED BY (gender STRING COMMENT "性别分区") 
STORED AS TEXTFILE;

INSERT INTO TABLE filetest.partition_student PARTITION(gender="nan")
SELECT id,name,age,clazz FROM filetest.students WHERE gender="男" limit 10;

INSERT INTO TABLE filetest.partition_student PARTITION(gender='nan')
SELECT "1500100002","吕金鹏",24,"文科六班"

5、查看表信息

DESC table_name
		查看表的字段信息

	DESC FORMATTED table_name
		查看表的详细信息

6、删除表

DROP TABLE table_name;
		删除表时，HDFS上的数据也会跟着删除掉

九、Hive-HQL语法-DML

where	用于过滤 分区裁剪 指定条件
join	用于量表关联 left outer join，join，mapjoin（1.2版本后默认开启）
group by	用于分组聚合
order by	用于全局排序 要尽量避免排序 是针对全局排序的 即对所欲的reduce输出是有序的
sort by	sortby：当有多个reduce时 只能保证单个reduce输出有序 不能保证全局有序
			cluster by = distribute by + sort by
distinct	去重