Hive基础知识

一.基础知识

  Hadoop是大数据处理的一个生态系统。Hadoop实现了一个特别的计算模型——MapReduce，它可以将计算任务分割成多个水平处理单元，然后分散到服务器上，降低成本。这种计算模型下面是一个被成为Hadoop分布式文件系统（HDFS）的分布式系统。这个系统是可插拔的。

  在Hive出现之前，如果用户从现有的数据基础架构转移到Hadoop上，比如从传统的关系型数据库和结构话查询语句转到Hadoop上，成本会非常大。因此Hive提供了一个HiveQl，用来查询Hadoop集群中的数据。Hive可以将大多数的查询转为MapReduce任务，让哟哦难怪乎更关注查询本身。

  Hive适合于数据仓库应用程序，这种应用程序进行相关的数据分析，不需要快速响应给出结果，而且数据本身不会频繁变化。

二.数据类型和格式

  Hive支持关系型数据库中的大多数基本数据类型，同时也支持关系型数据库中比较少出现的3中集合数据类型。

---

---

表1

数据类型	长度	例子
TINYINT	1byte有符号整数	20
SMALINT	2byte有符号整数	12
INT	4byte有符号整数	20
BIGINT	8byte有符号整数	20
BOOLEAN	布尔类型，true、false	TRUE
FLOAT	单精度浮点数	3.1425
DOUBLE	双精度浮点数	3.982
STRING	字符串	“thisisad.ds”
TIMESTAMP(v0.8.0+)	整数，浮点或者字符串
BINARY(v0.8.0+)	字节数组
STRUCT	和C语言中的Struct或者对象类似，可以通过‘点’符号的方式访问字段中的内容。如果某个字段类型为STRUCT{first STRING, last STRING},那么第一个元素可以通过字段名.first来引用	structure(‘John’,‘Doe’)
MAP	map是一组键-值对元组集合，使用数组表示法可以来访问元素，例如一个字段存储的值 ‘first’:‘Jhon’,‘last’:'Doe’那么可以通过字段名[‘last’]来访问最后一个元素	map(‘first’:‘Jhon’,‘last’:‘Doe’)
ARRAY	数组是一组具有相同类型和名称的变量的集合。数组编号从0开始。	Array(‘John’,‘Doe’)

---

---

三.HiveQL

3.1 HiveQL数据定义

  HiveQL和MySQl语法很相近，但是两者还是存在极为明显的差异的。Hive不支持行级插入、更新、删除操作，Hive也不支持事务。

3.1.1 Hive中的数据库

  Hive中数据库本质上就是表的一个目录或者说是命名空间，通过库可以避免表命名冲突。

CREATE DATABASE testbd;

可以通过上边这条指令来创建数据库，如果数据库已经存在，则会抛出异常信息。可以使用下边的语句创建数据库，避免因数据库已存在而抛出异常。

CREATE DATABASE IF NOT EXISTS testbd;

数据库创建完成后，可以通过下边这个命令查看数据库

SHOW DATABASES;

如果数据库很多的话，可以通过正则表达式来筛选出所需要的数据库名

SHOW DATABSES LIKE ‘t.*’;

在创建数据库的时候，可以为其添加注释，表明这个库的功能。

CREATE DATABASE testdb COMMENT ‘this is a test databse’；

然后通过 DESCRIBE 命令可以查看数据库的位置和描述信息。

DESCRIBE DATABSE testdb;

在命令行中可以通过 use 命令来切换当前使用的数据库，并可以让其在命令行中显示

hive>USE testdb;
#如果不进行设置的话，命令行里边是不会显示当前所在数据库的名字的
set hive.cli.print.current.db=true;
#使用上边这个命令进行设置之后,命令行中就会显示名字
hive(testdb)>

数据库删除命令.

DROP DATABASE IF EXISTS testdb;

Hive中是不允许用户删除一个存在表的数据库的，因此如果想删除这样的库，可以想把所有的表删掉，然后再删除数据库，也可以在删除命令后边加上关键字， CASCADE 加上关键字后，Hive在删除库之前就会先删除数据库中的表

DROP DATABASE IF EXISTS testdb CASCADE;

3.1.2 Hive中的表

  Hive中的建表语句遵从SQL的语法，但是相比于MySQL，Hive有更好的灵活性。例如可以定义表的数据文件存储在什么位置，使用什么样的存储格式。
下边使用员工表的例子说明Hive中复杂类型的使用

CREATE TABLE IF NOT EXISTS mydb.employees(
name STRING COMMENT’Employee name’,
salary FLOAT COMMENT’Employee salary’,
subordinates ARRAY COMMENT’names of subordinates’,
deductions MAP<STRING, FLOAT> COMMENT ‘keys of deductions names, value are percentages’,
address STRUCT<street:STRING, city:STRING,state:STRING,zip:INT> COMMENT ‘home address’)
COMMENT ‘THIS IS DESCRPTION OF THIS TABLE’
TBLPROPERTIES('creator‘=‘me’, ‘create_at’=‘2019-04-12 10:00:00’,…)
LOCATION ‘/user/hive/warehouse/mydb.db/employees’;

  以上边的例子来说，当前没有在要建表的数据库中，所以指定数据库mydb.db。在使用ARRAY，MAP的时候，要指定其值或键值的类型，使用STRUCT时，要指定字段名称，和类型。
   TBLPROPERTIES 指定表的基本信息。 LOCATION 指定表文件所在的位置。
hive中可以通过命令copy一张表

CREATE TABLE IF NOT EXISTS mydb.employees2 LIKE mydb.employees;

查看当前库中有哪些表

#第一种方式
USE mydb;
SHOW TABLES;
#第二种方式
SHOW TABLES IN mydb;
#第三种方式
SHOW TABLES ‘emplo.*’

查看某张表的详细结构信息

DESCRIBE EXTENDED mydb.employees;
#可以使用FORMATTED代替EXTENDED关键字，而且前者的信息更详细，可读性也高。

外部表和管理表：
  像上边的建表的例子，Hive可以管理这个表的生命周期，可以删除表，同时里边的数据也会被删除，这样的表被成为管理表，或内部表。而外部表，Hive并非完全拥有这份数据，因此删除表的时候并不会删除数据。

CREATE EXTERNAL TABLE IF NOT EXISTS stocks(
 symbol STRING
 …
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
LOCATION ‘/data/stocks’

关键字extenal表明Hive是哥外部表，Location语句则告诉Hive数据位于哪个路径下。

3.1.3 分区表

数据分区，是将数据通过某一个维度，进行分层存储，分散数据查询的压力。

CREATE TABLE employees(
 name STRING,
 salary FLOAT,
 subordinates ARRAY,
 deductions MAP<STRING, FLOAT>,
 address STRUCT<street:STRING, city:STRING, state:STRING>
)
PARTITIONED BY (country STRING, state STRING);

PARTITIONED BY 就指明了分区的标准。

3.2 HiveQL数据操作

  关注的是向表中装载数据和从表中抽取数据到文件系统的操作语言。

3.2.1 向表中装载数据

  Hive中没有行级的插入，更新，删除操作，因此往数据表中装载数据的唯一方式就是批量处理，或者是直接把文件写到响应的目录下。
  可以通过查询语句向目标表中插入数据。

INSERT OVERWRITE TABLE employees
PARTITION (country=‘US’, state=‘OR’)
SELECT * FROM staged_employees se WHERE se.cnty=‘US’ AND se.st=‘OR’;

因为这里使用了OVERWRITE所以之前分区中存在的数据会被覆盖掉。
可以使用INFO关键字替换OVERWRITE，那么数据会以追加的方式进行存储。使用这种方式存储如果staged_employees很大，而且要对它所有的分区进行数据存储，那么就需要执行很多次这个语句。可以用下边的额这种方式进行数据更新。

FROM staged_employees se
INSERT OVERWRITE TABLE employees
 PARTITION (country=‘US’, state=‘OR’)
 SELECT * WHERE se.cnty=‘US’ AND se.st=‘OR’
INSERT OVERWRITE TABLE employees
 PARTITION (country=‘US’, state=‘OR’)
 SELECT * WHERE se.cnty=‘US’ AND se.st=‘CA’;

使用这个方式，对staged_employees表只进行了一次查询，就可以把所有分区的数据进行存储。使用这种方式也是有问题的，如果分区数据有100个，都需要存储，那这个sql就会非常大
不过幸运的是，Hive提供了动态分区插入的方式。所以上边的SQL可以进行优化。

INSERT OVERWRITE TABLE employees
PARTITION (country, state)
SELECT …,se.cnty, se.st
FROM staged_employees se;

这里有个需要注意的地方，原表字段值和输出的分区表之间的关系是根据位置确定的，而不是根据字段名来匹配的。

---

---

表2 动态分区属性

属性名称	缺省值	描述
hive.exec.dynamic.partition	false	设置成true，标识开启动态分区功能
hive.exec.dynamic.partition.mode	strict	设置成nonstrict,表示允许所有分区都是动态的
hive.exec.max.dynamic.partition.pernode	100	每一个mapper或reducer可以创建的最大动态分区个数，如果超过，会报错
hive.exec.max.dynamic.partitions	+1000	一个动态分区语句可以创建的最大动态分区个数，如果超过会报错
hive.exec.max.create.files	100000	全局可以创建的最大文件个数

---

---

3.3 HiveQL查询

  基础的语法和SQL是很相似的，这里只看ARRAY，STRUCT，MAP复杂类型的查询。

ARRAY查询

  数组就可以通过下标来查询数据，如果这个元素不存在时，会返回NULL

SELECT name, subordinates[0] FROM employees;

MAP查询

  可以通过键来查询值，如果通过不存在的键去查询会报错。

SELECT name, deductions[‘state Taxes’] FROM employees;

STRUCT查询

  对于STRUCT类型的数据，可以通过’点’的方式来获取数据

SELECT name, address.city FROM employees;

使用正则表达式来指定查询的列

SELECT symbol, ‘price.*’ FROM stocks;

五.函数，索引

5.1 索引

CREATE INDEX employees_index
ON TABLE employees(country)
AS ‘org.apache.hadoop.hive.ql.index.copact.CompactIndexHandler’
WITH DEFERRED REBUILD
IDXPROPERTIES(‘createor’=‘me’, ‘create_at’=‘some_time’)
PARTITIONED BY (country, name)
COMMENT ‘Employees indexed by country and name.’;

AS 指定了索引的处理器，是一个实现了索引接口的Java类
DEFERRED REBUILD 用户指定了这个属性时，可以在任何时候进行第一次索引创建，或者使用ALTER INDEX 对索引进行重建。

ALTER INDEX employees_index
ON TABLE employees
PARTITIOn (country=‘US’)
REBUILD

如果省略PARTITION会对所有分区进行索引重建
显示索引

SHOW FORMATTED INDEX ON employees;

删除索引

DROP INDEX IF EXISTS employees_index ON TABLE employees;

5.2 函数

5.2.1 数学函数

返回类型	样式	描述
DOUBLE	rand(),rand(INT, seed)	double类型随机数
DOUBLE	log10(DOUBLE d) log2(DOUBLE d) log(DOUBLE base, DOUBLE d)	以10为底，d的对数 以2为底，d的对数 以base为底，d的对数
DOUBLE	pow(DOUBLE d,DOUBLE p),power(DOUBLE d, DOUBLE p)	d的p次幂
STRING	counv(STRING num, INT from_base, INT to_base)	字符串的num从from_base转成to_base进制
…	…	…

5.2.2 聚合函数

返回类型	样式	描述
DOUBLE	stddev_pop(col)	返回集合col的标准差
DOUBLE	var_pop(col) variance(col)	返回集合col的方差
DOUBLE	corr(col1, col2)	返回两组数的关系数
…	…	…

5.2.3 表生成函数

返回类型	样式	描述
多行数据	explode(MAp map)	返回多行结果
多行数据	explode(ARRAY a)	返回多行结果
TUPLE	json_tuple(STRING jsonStr, p1,p2…,pn)	对输入的json串做处理，与get_json_object类似
…	…	…