HIVE总结

简述

CREATE TABLE records2 (station STRING, year STRING, temperature INT, quality INT)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\t';


LOAD DATA LOCAL INPATH '/Users/tom/book-workspace/hadoop-book/input/ncdc/micro-tab/sample2.txt'
OVERWRITE INTO TABLE records2;

OVERWRITE ： Hive删除表对应目录中已有的所有文件

运行HIVE

配置HIVE

设置属性优先级:

1. Hive SET 命令
2. 命令行 -hiveconf 选项
3. hive-site.xml 和站点文件（hadoop配置文件）
4. Hive默认值和Hadoop默认文件（xxx.-default.xml）

执行引擎

MapReduce，Tez，Spark. 

具体使用哪种引擎由属性 hive.execution.engine 来控制

日志记录

hive.log.dir

日志配置存放在 conf/hive-log4j.properties中

Hive服务

• cli （shell环境）
• hiveserver2 让Hive以提供 Thrift 服务的服务器形式运行，允许用不同语言编写客户端进行访问
• beeline 以嵌入方式工作的 Hive命令行接口（类似cli），或者使用JDBC连接到一个HiveServer2
• hwi  Hive的web接口
• jar
• metastore

Hive客户端

Metastore

metastore是Hive元数据的集中存放地，包括两部分：服务和后台数据的存储，包含一个内嵌的一半地磁盘为存储的Derby数据库实例，称为内嵌配置

1. 内嵌hive是入门最简单方法，每次只有一个内嵌Derby数据库可以访问磁盘上的数据库文件，只能为每个metastore打开一个hive会话
2. 如果需要支持多会话，需要使用一个独立数据库，这中配置称为本体metastore配置
3. 对于独立metastore，Mysql是一种很受欢迎的选择
4. 更进一步，远程metastore配置，一个或多个metastore服务器和Hive服务运行在不同的进程内，数据库层可以完全置于防火墙之后，提供了更好的可管理和安全性

Hive于传统数据库相比

读时模式 vs 写时模式

写时模式： 数据在写入数据库时对照模式进行检查

读时模式： Hive对数据的验证并不在加载数据时进行，在查询时进行

更新，事务，索引

锁：表级和分区级锁，由zookeeper透明管理

索引：紧凑（compact）索引和位图（bitmap）索引

HiveQL

SQL-92，mysql，oracle sql 混合体

数据类型

TINYINT	SMALLINT	INT	BIGINT	DECIMAL	String	BINARY	TIMESTAMP
byte	short	int	long	BigDecimal			时间戳
1	2	4	8				DATE
				主要存储货币	存储2GB	二进制数据	年月日

复杂类型

ARRAY，MAP，STRUCT，UNION

STRUCT：一种记录类型，封装了一个命名的字段集合

UNION ：是几种数据类型中知名选中一种，UNION的值必须与这些数据类型完全一致

CREATE TABLE complex (
  c1 ARRAY<INT>,
  c2 MAP<STRING, INT>,
  c3 STRUCT<a:STRING, b:INT, c:DOUBLE>,
  c4 UNIONTYPE<STRING, INT>
);

SELECT c1[0], c2['b'], c3.c, c4 FROM complex;

操作与函数

不同之处： || 是逻辑或（OR）操作符，而不是字符串 “连接”，Hive中应使用concat函数

Hive提供大量内置函数，可在 hive shell环境中，show functions 查看函数列表，describe function 了解详细

类型转换

Hive不会进行反向转换，除非使用CAST操作

转换规则：

1. 任何数值都可以隐式转换为一个范围更广的类型或者文本类型（String，varchar，char）。
2. 所有文本类型都可以隐式转换为另一种文本类型
3. 文本类型都能隐式转换为Double或DECIMAL
4. Boolean类型不能转换为其他任何数据类型
5. TimeStamp和Date可以隐式转换为文本类型

可以使用CAST显示进行数据类型转换 

如： CAST（'1' AS INT） 如果转换失败返回NULL

表

托管表和外部表

托管表负责管理数据

外部表访问数据

区别在于 LOAD  和 DROP 语义上

加载数据到托管表时，Hive把数据移动仓库目录

CREATE TABLE users (id INT, name STRING);

LOAD DATA LOCAL INPATH 'input/hive/tables/users.txt'
OVERWRITE INTO TABLE users;

把表users.txt移动到Hive的users表的仓库目录中：即hdfs://user/hive/warehouse/users

由于加载操作就是文件系统中的文件移动或文件命名，即执行速度很快

DROP TABLE users;

包括元数据和数据，会被一起删除，因为 Load是一个移动操作

CREATE EXTERNAL TABLE users (id INT, name STRING);
 LOCATION '/usr/tom/users';
LOAD DATA LOCAL INPATH 'input/tom/users.txt'
INTO TABLE users;

使用 EXTERNAL 关键字后，Hive知道数据并不由自己管理，丢弃外部表时，Hive不会碰数据，只会删除元数据

分区和桶

Hive把表组织成分区（partition），根据分区列（partition column，如 日期）的值对表进行粗略划分，可以加快数据分片（slice）的查询速度

分区

一个表可以多个维度来进行分区，还可以进一步根据国家对每个分区进行 子分区

CREATE TABLE logs (ts BIGINT, line STRING)
PARTITIONED BY (dt STRING, country STRING);

LOAD DATA LOCAL INPATH 'input/hive/partitions/file1'
INTO TABLE logs
PARTITION (dt='2001-01-01', country='GB');

分区只是表目录下嵌套子目录，可以使用 show partitions table 查看表的分区信息

PARTITIONED BY 字句中的列定义是表中正式的列，列值源于目录名，称为 分区列

SELECT ts, dt, line
FROM logs
WHERE country='GB';

桶

把表（或分区）组织成桶（bucket）有两个理由：

• 获得更高的查询处理效率。桶为表加上了额外结构。具体来说：连接两个在（包含连接列的）相同列上划分了桶，可以使用map端连接（map-side join）高效地实现
• 取样 或者说 采样更高效，在处理大规模数据时，在开发和修改查询阶段，如果在一小部分数据上运行会带来很多方便

CLUSTERED BY 字句来制定划分桶所有列和要划分的个数

CREATE TABLE bucketed_users (id INT, name STRING)
CLUSTERED BY (id) INTO 4 BUCKETS;

Hive对值进行哈希，将结果除以桶的个数取余，任何一个同里都会有一个随机的用户集合

对于Map端连接：两个表以相同方式划分桶，处理左边表内某个通的mapper知道右边表内相匹配的行在对应的桶内，这样，mapper只需要获取那个桶即可进行连接

桶中的数据可以根据一个或多个列另外进行排序，这样可以对每个通的连接变成了高效的归并排序(merge-sort)，可以进一步提升map端连接效率

CREATE TABLE bucketed_users (id INT, name STRING)
CLUSTERED BY (id) SORTED BY (id) INTO 4 BUCKETS;

建议让Hive来进行划分桶的操作

SET hive.enforce.bucketing=true;

这样Hive就知道用表定义中声明的数量来创建桶

INSERT OVERWRITE TABLE bucketed_users
SELECT * FROM users;

物理上，每个桶就是表（或分区）目录里的一个文件

桶相对于MapReduce的输出文件分区:一个作业产生的桶（输出文件）和reduce任务个数相同

dfs -ls /user/hive/warehouse/bucketed_users;

dfs -cat /user/hive/warehouse/bucketed_users/000000_0;

如果分了4个桶，则会产生0-3 一共4个桶文件，0文件中存储的，即除以4的余数

0

4

用TABLESAMPLE子句对表进行取样

SELECT * FROM bucketed_users
TABLESAMPLE(BUCKET 1 OUT OF 4 ON id);

1/4

4

0

SELECT * FROM bucketed_users
TABLESAMPLE(BUCKET 1 OUT OF 2 ON id);

1/2

4

0

2

HIVE MapReduce脚本

ADD FILE /.../.../XX.py python脚本

将此脚本传输到hadoop集群上，类似hadoop分布式缓存

select ...from..

using 'XXX.py'

AS field1,field2

Hive存储格式

 

Hive 从两个维度对表存储进行管理 行格式 和 文件格式

默认存储格式：分隔的文本

SerDe 序列化和反序列化工具 （类似Insert 或 Ctas 时）

如果默认没有使用一下两种格式：

row format 

stored as

则默认格式是分隔的文本，每行（line）存储一个数据行（row）

Hive支持8级分隔符，只能重载其中3个

嵌套的层次Level决定了使用哪种分隔符 array(array(1,2),array(3,4))  外层 control-B 内层 control-C

使用hexdump或类似命令可查看输出文件的分隔符

二进制存储格式：顺序文件，Avro数据文件，Parquet，RCFile，ORCFile

二进制使用方法：只需要通过 create table 语句中的 stored as做相应声明，不需要指定 row format

二进制分为两大类： 行格式 和 文件格式

......

导入数据

之前通过 LOAD DATA ，通过复制将文件移动到表的目录中

可以以通过 INSERT ，把数据从一个 Hive表填充到另一个

INSERT OVERWRITE TABLE stations_by_year
  SELECT year, COUNT(DISTINCT station)
  GROUP BY year 

INSERT语句

OVERWRITE 意味着会替换掉原分区数据

如果追加： 使用 INSERT INTO TABLE

可以指定要插入哪个分区：

INSERT TABLE TARGET
PARTITION (dt = '2010-11-11')
select col1,col2
FROM source

动态指明分区：

INSERT TABLE TARGET
PARTITION (dt)
select col1,col2，dt
FROM source

多表插入：

在HiveQL中可以把Insert语句倒过来，把FROM子句放在最前面，查询效果一样

FROM records2
INSERT OVERWRITE TABLE stations_by_year
  SELECT year, COUNT(DISTINCT station)
  GROUP BY year 

可以在一个查询中使用多个insert子句

FROM records2
INSERT OVERWRITE TABLE stations_by_year
  SELECT year, COUNT(DISTINCT station)
  GROUP BY year 
INSERT OVERWRITE TABLE records_by_year
  SELECT year, COUNT(1)
  GROUP BY year
INSERT OVERWRITE TABLE good_records_by_year
  SELECT year, COUNT(1)
  WHERE temperature != 9999 AND quality IN (0, 1, 4, 5, 9)
  GROUP BY year;  

create table  。。 as select语句

  ctreate table target
  as
  select col1,col2
  from source

表的修改

  alter table source rname to target

在更新表的元数据以外，alter table 语句还把表目录移到新名称所对应的目录下，对于外部表， 这个操作值更新元数据，而不会移动目录

添加一个新列

alter table target add columns (col3 STRING)

HIVE不允许更新已有的记录，需要使用其他几只更新底层文件，常用方法是创建一个定义了新列的新表，使用select把数据填充进去

表的丢弃

DROP TABLE

外部表：只删除元数据

内部表：删除表内所有记录，如果要保留表的定义，使用 truncate table

外部表则在shell环境中使用 dfs  -rmr 来直接删除外部表目录

使用like创建一个与第一个表模式相同的新表

  create table new_table like table2

查询数据

排序和聚集

order by : 将对数据进行全排序

sort by ： 部分排序，为每个reducer产生一个排序文件

distribute by ：控制某个特定的行应该到哪个reducer，为了后续的聚集操作

FROM records2
SELECT year, temperature
DISTRIBUTE BY year
SORT BY year ASC, temperature DESC;

如果sort by 和 distribute by 所用的列相同，可以缩写为 cluster by 

MapReduce脚本

using '脚本'

通常运行前将脚本上传到Hadoop集群(分布式缓存)

连接

1.内连接

SELECT sales.*, things.*
FROM sales JOIN things ON (sales.id = things.id);

与Mysql Oracle可以有一下写法：

SELECT sales.*, things.*
FROM sales, things
WHERE sales.id = things.id

EXPLAN可查看Hive将为某个查询使用多少个MapReduce作业：

EXPLAN
SELECT sales.*, things.*
FROM sales, things
WHERE sales.id = things.id

2.外链接

外链接可以找到连接中不匹配的数据行

SELECT sales.*, things.*
FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);

SELECT sales.*, things.*
FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id);

SELECT sales.*, things.*
FROM sales FULL OUTER JOIN things ON (sales.id = things.id);

3.半连接

SELECT *
FROM things
WHERE things.id in (select id from sales)

可以重写另一种格式：

SELECT *
FROM things LEFT SEMI JOIN sales ON (sales.id = things.id);

4.map连接

SELECT sales.*, things.*
FROM sales JOIN things ON (sales.id = things.id);

如果有一个连接表小道足以放入内存，如 things, Hive就可以把较小的表放入每个mapper的内存来执行连接操作

这个查询不涉及reducer操作，因为没有涉及聚集操作

map连接可以利用分桶的表，需要设置：

SET hive.optimize.bucketmapjoin=true;

子查询

Hive支持有限，只允许出现在 select 语句的 from 子句中，或某些特殊情况

试图

试图使一种用select语句定义的虚表，也可以用来限制用户，使其只能访问被授权可以看到的子表

Hive中，创建试图时并不把试图无话存储到磁盘上，相反，试图的select语句只是在执行引用试图的语句时才执行

CREATE VIEW viewName

as

SELECT * FROM

创建试图时并不执行查询操作，只是存储在metastore中

describe extended view_name 查看试图详细信息

HIve自定义函数

UDF

Hive有三种UDF：

1. UDF 操作用于单个数据行，且产生一个数据行
2. UDAF 接受多个输入数据行，产生一个输出数据行，类似 count ，max
3. UDTF 操作作用于但是数据行，且产生多个数据行

CREATE TABLE arrays (x ARRAY<STRING>)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\001'
  COLLECTION ITEMS TERMINATED BY '\002';

a^Bb

c^Bd^Be

LOAD DATA LOCAL INPATH 'input/hive/udfs/arrays.txt'
OVERWRITE INTO TABLE arrays;

SELECT * FROM arrays;

["a","b"]

["c","d","e"]

SELECT explode(x) AS y FROM arrays;

a

b

c

d

e

UDTF的select 使用有一定限制，不能检索额外的列表达式，因此Hive支持lateral view 查询，功能更强大

写UDF

public class Strip extends UDF {
  private Text result = new Text();
  
  public Text evaluate(Text str) {
    if (str == null) {
      return null;
    }
    result.set(StringUtils.strip(str.toString()));
    return result;
  }
  
  public Text evaluate(Text str, String stripChars) {
    if (str == null) {
      return null;
    }
    result.set(StringUtils.strip(str.toString(), stripChars));
    return result;
  }
}

在netastore中注册这个函数并使用 create function 语句为它起名

create function strip as 'com....hive.strip'

using jar '/path/.../hive-example.jar'

本地运行写本地路径，集群上应将jar辅助到HDFS中,要写 hdfs url 地址

UDF 名对大小写不敏感

删除函数： DROP FUNCTION strop；

temporary 关键字可以创建一个仅在Hive会话期间有效的函数

CREATE TEMPORARY FUNCTION maximum AS 'com.hadoopbook.hive.Maximum';
SELECT maximum(temperature) FROM records;

每次运行Hive时自动添加你的UDF库：

hive --auxpath /.../.../hive-examples.jar 启动hive时生效

或 设置 HIVE_AUX_JARS_PATH 环境变量

写UDAF

比普通的UDF难写，因为值实在块内进行聚集的（块可能分布在很多任务）

• init() 初始化计算函数，并重设它的内部状态
• iterate() 每次对一个新值进行聚集计算时都会调用iterate()
• terminatePartial() Hive需要部分聚集结果时会调用terminatePartial()
• merge() Hive决定要合并一个部分聚集值和另一个部分聚集值时会调用merge()方法
• terminate() Hive最终聚集结果时调用

public class Mean extends UDAF {
  public static class MeanDoubleUDAFEvaluator implements UDAFEvaluator {
    public static class PartialResult {
      double sum;
      long count;
    }
    
    private PartialResult partial;

    public void init() {
      partial = null;
    }

    public boolean iterate(DoubleWritable value) {
      if (value == null) {
        return true;
      }
      if (partial == null) {
        partial = new PartialResult();
      }
      partial.sum += value.get();
      partial.count++;
      return true;
    }

    public PartialResult terminatePartial() {
      return partial;
    }

    public boolean merge(PartialResult other) {
      if (other == null) {
        return true;
      }
      if (partial == null) {
        partial = new PartialResult();
      }
      partial.sum += other.sum;
      partial.count += other.count;
      return true;
    }

    public DoubleWritable terminate() {
      if (partial == null) {
        return null;
      }
      return new DoubleWritable(partial.sum / partial.count);
    }
  }
}

。。。。。