20191012 Hive

[TOC]

Hive 简介

Hive 数据仓库
Hive 解释器，编译器，优化器
Hive 运行时，元数据存储在关系型数据库里面

Hive 架构

CLI： command line interface 命令行接口

JDBC/ODBC: Java 连接数据库（MySQL、Oracle）

Web GUI： Hive web 用户界面

metastore：表、字段的约束

Driver： Driver 服务，负责 Hadoop 和 Hive 之间的联系（）

（1）用户接口主要有三个：CLI，Client 和 WUI。其中最常用的是CLI，Cli启动的时候，会同时启动一个Hive副本。Client是Hive的客户端，用户连接至Hive Server。在启动 Client模式的时候，需要指出Hive Server所在节点，并且在该节点启动Hive Server。 WUI是通过浏览器访问Hive。
（2）Hive将元数据存储在数据库中，如mysql、derby。Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。
（3）解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS中，并在随后有MapReduce调用执行。
（4）Hive的数据存储在HDFS中，大部分的查询、计算由MapReduce完成（包含*的查询，比如select * from tbl不会生成MapRedcue任务）。

Hive 的架构

编译器将一个Hive SQL转换操作符
操作符是Hive的最小的处理单元
每个操作符代表HDFS的一个操作或者一道MapReduce作业

Operator

Operator都是hive定义的一个处理过程
Operator都定义有:
protected List <Operator<? extends Serializable >> childOperators;
protected List <Operator<? extends Serializable >> parentOperators;
protected boolean done; // 初始化值为false

ANTER 词法语法分析工具解析 SQL

Hive 搭建模式

单机模式

通过网络连接到一个数据库中

节点部署：

HOST/Soft	MySQL	Hive
node01/192.168.170.101	*
hode02/192.168.170.102		*

搭建过程步骤：

安装 MySQL

# 安装 mysql 服务
yum install mysql-server -y 

# 输入 msyql  会出现这样的错误信息， 原因是 mysqld 服务未启动
# ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

# 启动 mysqld 服务
service mysqld start

# 配置 mysql 远程连接以及用户名和密码
grant all privileges on *.* to root@'%' identified by '123' with grant option;

# 刷新权限
flush privileges;

# 删除 mysql.user 表中的除了远程连接用户外其他用户的记录
delete from user where mysql.host != '%'

#  查看 mysql 用户表
select host,user,password from mysql.user

安装 Hive

  # 安装 hive
  # 上传 hive 压缩包并解压
tar -zxvf hive.x.y.z.tar.gz
  # 移动到 /opt/sxt 目录下
  mv  hive.x.y.z  /opt/sxt
  # 配置 hive 环境变量,编辑 vi /etc/profile 文件
  export HIVE_HOME=/opt/sxt/hive.x.y.z
  export PATH=$PATH:$HIVE_HOME/bin
  # 使  /etc/profile 生效
  . /etc/profile
  # 输入 hive 命令，查看 hive 是否安装成功
  
  # 修改配置文件
  cp hive-.xml  hive-site.xml  
  vi hive-site.xml
  <property>
      <name>hive.metastore.warehouse.dir</name>
      <value>/user/hive_remote/warehouse</value>
  </property>
  <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://node01/hive_remote?createDatabaseIfNotExist=true</value>
  </property>
  <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>com.mysql.jdbc.Driver</value>
  </property>
  <property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>root</value>
  </property>
  <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>123</value>
  </property>
  
  # 更新 jar  资源
  # 将 jline.jar 调整为高版本，同时将 hadoop 的低版本删除
  cd $HODOOP_HOME/share/hadoop/yarn/lib/ 
  rm -fr jline-0.9.94.jar 
  cp $HIVE_HOME/lib/jline-2.12.jar ./ 
  
  # 启动
  hive

分布式模式

用于非Java客户端访问元数据库，在服务器端启动 MetaStoreServer，客户端利用 Thrift 协议通过MetaStoreServer访问元数据库

搭建环境准备：

	mysql	hive-server	hive-client
node01	*
node03		*
node04			*

搭建步骤：

# 搭建分布式 Hive 是建立在单机模式之上
# 从之前的 node02 节点上拷贝 $HIVE_HOME 目录 到 node03、node04 上
# 其中 node03 作为 Hive 服务端， node04 作为 Hive 客户端
scp -r apache-hive-1.2.1-bin/ root@node03:`pwd`/ 
scp -r apache-hive-1.2.1-bin/ root@node04:`pwd`/ 

# 配置 node03 node04 的 HIVE 环境变量

# 更新 jar 资源
# 将 jline.jar 调整为高版本，同时将 hadoop 的低版本删除
cd $HODOOP_HOME/share/hadoop/yarn/lib/ 
rm -fr jline-0.9.94.jar 
cp $HIVE_HOME/lib/jline-2.12.jar ./ 

# 在 node03 上 启动 hive metastore 服务
hive --service metastore
# 在 node04 上启用 hive 客户端
hive

配置环境变量的目的：

找可执行性文件
方便其他框架或者服务使用。 eg: HIVE 通过 HADOOP 的环境变量连接到 Hadoop 上

Hive 之 DDL

官方文档地址：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Overview

DDL 语法

创建表

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
 
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];
 
data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)
 
primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)
 
array_type
  : ARRAY < data_type >
 
map_type
  : MAP < primitive_type, data_type >
 
struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>
 
union_type
   : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)
 
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
 
file_format:
  : 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)
  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
 
column_constraint_specification:
  : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
 
default_value:
  : [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] 
 
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 
    [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

练习：

create table psn
(
id int,
name string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';


load data local inpath '/root/data/data' into table psn;

-- 查看表结构
desc formatted 表名

Hive 表

内部表

create table psn3
(
id int,
name string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by '\001'
collection items terminated by '\002'
map keys terminated by '\003';

外部表

create external table psn4
(
id int,
name string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/data/hive/input/';

区别*

	内部表 MANAGED	外部表 EXTERNAL
创建表时	直接存储在默认的hdfs路径	需要自己指定路径
删除表时	将数据和元数据全部删除	只删除元数据，数据不删除

先有表，后有数据，使用内部表。先有数据，后有表，使用外部表。

注意：

删除外部表中不会删除 HDFS 中的数据
Hive 读时检查（解耦，便于数据读取）; 关系数据库写时检查

Hive 分区

分区表的意义在于优化查询。查询时尽量利用分区字段。如果不使用分区字段，就会全部扫描。

注意：分区属于元数据，不能通过外部表直接从 HDFS 加载 Hive 中，必须在表定义时指定对应的partition字段

分区建表

a. 单分区建表语句：

1	create table day_table (id int, content string) partitioned by (dt string);

单分区表，按天分区，在表结构中存在 id，content，dt 三列。
以 dt 为文件夹区分

b. 双分区建表语句：

1	create table day_hour_table (id int, content string) partitioned by (dt string, hour string);

双分区表，按天和小时分区，在表结构中新增加了 dt 和 hour 两列。
先以 dt 为文件夹，再以 hour 子文件夹区分

添加分区表语法

（表已创建，在此基础上添加分区）：
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION ‘location1’] partition_spec [LOCATION ‘location2’] …;

partition_spec:: (partition_column = partition_col_value, partition_column = partition_col_value, …)
例：

1	ALTER TABLE day_table ADD PARTITION (dt='2008-08-08', hour='08')

删除分区

LTER TABLE table_name DROP partition_spec, partition_spec,… partition_spec:: (partition_column = partition_col_value, partition_column = partition_col_value, …)

用户可以用 ALTER TABLE DROP PARTITION 来删除分区。
内部表中、对应分区的元数据和数据将被一并删除。

例：

1	ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');

向指定分区添加数据语法

1 2	LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

例：

-- 从 HDFS 中加载数据
LOAD DATA INPATH '/user/pv.txt' 
INTO TABLE day_hour_table PARTITION(dt='2008-08- 08', hour='08'); 

-- 从本地文件系统中加载数据
LOAD DATA local INPATH '/user/hua/*' 
INTO TABLE day_hour partition(dt='2010-07- 07');

当数据被加载至表中时，不会对数据进行任何转换。

Load 操作只是将数据复制至 Hive 表对应的位置。数据加载时在表下自动创建一个目录。

查询执行分区语法

1	SELECT day_table.* FROM day_table WHERE day_table.dt>= '2008-08-08';

分区表的意义在于优化查询。查询时尽量利用分区字段。如果不使用分区字段，就会全部扫描。

Hive查询表的分区信息语法

1	SHOW PARTITIONS day_hour_table;

预先导入分区数据，但是无法识别怎么办？

1	Msck repair table tablename

直接添加分区

动态分区

开启支持动态分区
1
set hive.exec.dynamic.partition=true;
默认：true
1
set hive.exec.dynamic.partition.mode=nostrict;
默认：strict（至少有一个分区列是静态分区）
相关参数
1
set hive.exec.max.dynamic.partitions.pernode;
每一个执行mr节点上，允许创建的动态分区的最大数量(100)
1
set hive.exec.max.dynamic.partitions;
所有执行mr节点上，允许创建的所有动态分区的最大数量(1000)
1
set hive.exec.max.created.files;
所有的mr job允许创建的文件的最大数量(100000)

加载数据

create external table psn21(
id int,
name string,
sex string,
age int,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/data/bucket/input';


create table psn22(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by (age int,sex string) 
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';

# hive 命令行中设置动态分区为非严格模式
set hive.exec.dynamic.partition.mode=nonstrict

# 注意： 参数的位置要对应
from psn21
insert overwrite table psn22 partition(age, sex)  
select id, name, likes, address, age,sex distribute by age, sex;

Hive 之 DML*

官方文档地址：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-HiveDataManipulationLanguage

加载数据的方式

Loading files into tables 从文件中加载数据

Hive does not do any transformation while loading data into tables. Load operations are currently pure copy/move operations that move datafiles into locations corresponding to Hive tables.

语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] 
INTO TABLE tablename 
[PARTITION (partcol1=val1, partcol2=val2 ...)]

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] 
INTO TABLE tablename 
[PARTITION (partcol1=val1, partcol2=val2 ...)] 
[INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)

注意：从 HDFS 中加载数据，数据发生移动，而从本地加载数据，数据发生拷贝。

Inserting data into Hive Tables from queries 从查询结果集中加载数据

Query Results can be inserted into tables by using the insert clause.

语法：

Standard syntax:
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;
 
Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;
FROM from_statement
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2]
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;
 
Hive extension (dynamic partition inserts):
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;
INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

例子：

FROM psn
INSERT OVERWRITE TABLE psn10
SELECT id,name
insert into psn11
select id,likes 

insert overwrite local directory '/root/result' 
select * from psn;

更新操作

ACID 事务的特性
三大范式

Hive SerDe

SerDe 用于做序列化和反序列化。

构建在数据存储和执行引擎之间，对两者实现解耦。
Hive通过 ROW FORMAT DELIMITED 以及 SERDE 进行内容的读写。

row_format
: DELIMITED 
          [FIELDS TERMINATED BY char [ESCAPED BY char]] 
          [COLLECTION ITEMS TERMINATED BY char] 
          [MAP KEYS TERMINATED BY char] 
          [LINES TERMINATED BY char] 
: SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

Hive 正则匹配

CREATE TABLE logtbl (
    host STRING,
    identity STRING,
    t_user STRING,
    time STRING,
    request STRING,
    referer STRING,
    agent STRING)
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES (
    "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[(.*)\\] \"(.*)\" (-|[0-9]*) (-|[0-9]*)"
  )
  STORED AS TEXTFILE;

Hive Beeline

提供了 JDBC 的访问方式

beenline 不能用于 DML 操作，只能执行一些查询操作

-- 第一种方式
beeline 
!connect jdbc:hive2://node04:10000/default root 123

-- 第二种方式
beeline -u connect jdbc:hive2://node04:10000/default -n root

Hive JDBC

public class HiveDemo {
	
//	private final static String driver = "org.apache.hive.jdbc.HiveDriver";
//	private final static String url = "jdbc:hive2://node04:10000/default";
//	private final static String username = "root";
//	private final static String password = "123";
	
	public static void main(String[] args) {
		try {
			Properties prop = new Properties();
			prop.load(new FileInputStream(new File("jdbc.properties")));
			String driver = prop.getProperty("driver");
			String url = prop.getProperty("url");
			String username = prop.getProperty("username");
			String password = prop.getProperty("password");
            
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url,username,password);
			Statement st = conn.createStatement();
			String sql = "select * from psn";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				System.out.println(id+"\t"+name);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

Hive 函数

官方文档地址：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

自定义 UDF

官方文档地址：

https://cwiki.apache.org/confluence/display/Hive/HivePlugins

java 代码

public class TuoMing extends UDF{
	
	public Text evaluate(final Text s) {
		if (s == null) {
			return null;
		}
		String str = s.toString().substring(0, 3) + "***";
		return new Text(str);
	}
}

将 java 代码文件打包成 jar ，上传 Linux 上的 HDFS 中

创建临时函数

-- 本地文件系统加载
add jar /root/tm/tm.jar;
create temporary function tm as 'com.szxy.hive.TuoMing';

-- 从 HDFS 中加载
create temporary function tms as 'com.szxy.hive.TuoMing' 
using jar 'hdfs://node01:8020/data/jar/tm/tm.jar';

使用临时函数
1
select tms(name) from psn;
结果

Hive 案例

struct 结构体

测试数据

1001,zhangsan:24
1002,lisi:25
1003,wangwu:26
1004,zhaoliu:27

创建表

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

load data inpath '/data/struct/input' into table student;

WordCount

create external table hello(
line string 
)
location '/data/wc/input'

create table hello_wc(
word string ,
num int 
);

from (select explode(split(line,' ')) word from hello ) t 
insert into hello_wc 
select word,count(word) group by word;

基站掉话率统计

需求：

找出掉线率最高的前10基站

sql 语句

create table tb_cell_result(
imei string,
drop_num int,
duration int,
drop_rate double
);

create external table tb_cell(
record_time string,
imei string,
cell string,
ph_num int,
call_num string,
drop_num int,
duration int,
drop_rate int,
net_type string,
erl int 
)
row format delimited fields terminated by ','
location '/data/cell/input';

from tb_cell 
insert into tb_cell_result
select imei,sum(drop_num) sdrop,sum(duration) sdura, sum(drop_num)/sum(duration) srate group by imei sorted by srate desc;

select * from tb_cell_result limit 10;

Hive 参数

hive 参数、变量

hive当中的参数、变量，都是以命名空间开头

命名空间	读写权限	含义
hiveconf	可读写	hive-site.xml 中配置各种变量例：`hive --hiveconf hive.cli.print.header=true`
System	可读写	系统变量，包括 JVM 运行参数等例：system:user.name=root
env	只读	环境变量“ 例：env:JAVA_HOME
hivevar	可读写	例：hive -d val=key

通过 ${} 方式进行引用，其中 system、env 下的变量必须以前缀开头

hive 参数设置方式
1. 修改配置文件 ${HIVE_HOME}/conf/hive-site.xml
  1
  2
  3
  4
  <property>
  <name>hive.cli.print.header</name>
  <value>true</value>
  </property>
2. 启动 hive cli 时，通过 --hiveconf key=value的方式进行设置
  
  例：hive --hiveconf hive.cli.print.header=true
3. 进入cli之后，通过使用set命令设置
  1
  set hive.cli.print.header=true;

Hive 分桶

分桶概念

分桶表是对列值取哈希值的方式，将不同数据放到不同文件中存储。
对于hive中每一个表、分区都可以进一步进行分桶。
由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中。

适用场景

数据抽样（ sampling ）

分桶操作

开启支持分桶
1
set hive.enforce.bucketing=true;
默认：false；设置为 true之后，mr运行时会根据 bucket 的个数自动分配 reduce task 个数。

（用户也可以通过mapred.reduce.tasks自己设置reduce任务个数，但分桶时不推荐使用）
注意：一次作业产生的桶（文件数量）和reduce task个数一致。

往分桶表中加载数据

1 2	insert into table bucket_table select columns from tbl; insert overwrite table bucket_table select columns from tbl;

桶表抽样查询

1	select * from bucket_table tablesample(bucket 1 out of 4 on columns);

TABLESAMPLE 语法
1
TABLESAMPLE(BUCKET x OUT OF y)
x：表示从哪个bucket开始抽取数据
y：必须为该表总bucket数的倍数或因子
栗子
当表总 bucket 数为32时

TABLESAMPLE(BUCKET 3 OUT OF 8)，抽取哪些数据？

答：共抽取2（32/16）个bucket的数据，抽取第2、第18（16+2）个bucket的数据

TABLESAMPLE(BUCKET 3 OUT OF 256)，抽取哪些数据？

分桶案例

测试数据

1,tom,11
2,cat,22
3,dog,33
4,hive,44
5,hbase,55
6,mr,66
7,alice,77
8,scala,88

创建 hive 表

create external table tb_bucket(
id int,
name string,
score int
)
row format delimited 
fields terminated by ','
location '/data/bucket/input';

创建分桶表

create table psn_bucket(
id int,
name string,
score int
)
clustered by(score) into 4 buckets
row format delimited
fields terminated by ',';

向分桶表中添加数据
1
insert into psn_bucket select id,name,score from tb_bucket;
注意：Hive 分桶默认是关闭的,通过 set hive.enforce.bucketing=true;开启分桶

抽样

1	select id,name,score from psn_bucket tablesample(bucket 2 out of 4);

Hive Laternal View

在 UDTF 函数中使用

Lateral View用于和UDTF函数（explode、split）结合来使用。
首先通过UDTF函数拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
主要解决在select使用UDTF做查询过程中，查询只能包含单个UDTF，不能包含其他字段、以及多个UDTF的问题

语法：

1	LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)

注意：列别名有多个，并且可以重复

栗子

统计人员表中共有多少种爱好、多少个城市?

select count(distinct(myCol1)), count(distinct(myCol2)) from psn
LATERAL VIEW explode(likes) myTable1 AS myCol1 
LATERAL VIEW explode(address) myTable2 AS myCol2, myCol3;  

select count(distinct(mycol)) from psn lateral view explode(likes)  myTable as mycol;

Hive 视图

视图本质上就是一个虚拟表 Virtual Table,和关系型数据库中的普通视图一样，hive也支持视图

特点：

不支持物化视图
只能查询，不能做加载数据操作
视图的创建，只是保存一份元数据，查询视图时才执行对应的子查询
view定义中若包含了ORDER BY/LIMIT语句，当查询视图时也进行ORDER BY/LIMIT语句操作，view当中定义的优先级更高
view支持迭代视图

view语法

创建视图：

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name 
  [(column_name [COMMENT column_comment], ...) ]
  [COMMENT view_comment]
  [TBLPROPERTIES (property_name = property_value, ...)]
  AS SELECT ... ;

查询视图：
1
select colums from view;

删除视图：

1	DROP VIEW [IF EXISTS] [db_name.]view_name

Hive 索引

目的

优化查询以及检索性能

创建索引：

create index t1_index on table psn(name) 
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' 
with deferred rebuild 
in table t1_index_table;
-- as：指定索引器；
-- in table：指定索引表，若不指定默认生成在default__psn2_t1_index__表中

create index t1_index on table psn2(name) 
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' 
with deferred rebuild;

查询索引
1
show index on psn2;
重建索引（建立索引之后必须重建索引才能生效）
1
ALTER INDEX t1_index ON psn REBUILD;
删除索引
1
DROP INDEX IF EXISTS t1_index ON psn;

Hive 运行方式

命令行方式cli：控制台模式
- 与hdfs交互
  执行执行dfs命令
  例：dfs –ls /
- 与Linux交互
  ！开头
  例：!pwd

脚本运行方式（实际生产环境中用最多）

hive -e ""
hive -e "">aaa
hive -S -e "">aaa
hive -f file
hive -i /home/my/hive-init.sql
hive> source file (在hive cli中运行)

JDBC方式：hiveserver2
web GUI接口（hwi、hue等）

Hive 权限管理

官方文档地址：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Authorization

三种授权模型：

Storage Based Authorization in the Metastore Server

基于存储的授权 - 可以对Metastore中的元数据进行保护，但是没有提供更加细粒度的访问控制（例如：列级别、行级别）。
SQL Standards Based Authorization in HiveServer2

基于SQL标准的Hive授权 - 完全兼容SQL的授权模型，推荐使用该模式。
https://cwiki.apache.org/confluence/display/Hive/SQL+Standard+Based+Hive+Authorization

Default Hive Authorization (Legacy Mode)

hive默认授权 - 设计目的仅仅只是为了防止用户产生误操作，而不是防止恶意用户访问未经授权的数据。

SQL Standards Based Authorization in HiveServer2

完全兼容SQL的授权模型
除支持对于用户的授权认证，还支持角色 role 的授权认证

role可理解为是一组权限的集合，通过role为用户授权

一个用户可以具有一个或多个角色

默认包含另种角色：public、admin
限制：
1、启用当前认证方式之后，dfs, add, delete, compile, and reset等命令被禁用。

2、通过set命令设置hive configuration的方式被限制某些用户使用。

（可通过修改配置文件hive-site.xml中hive.security.authorization.sqlstd.confwhitelist进行配置）

3、添加、删除函数以及宏的操作，仅为具有admin的用户开放。

4、用户自定义函数（开放支持永久的自定义函数），可通过具有admin角色的用户创建，其他用户都可以使用。

5、Transform功能被禁用。

在hive服务端修改配置文件hive-site.xml添加以下配置内容：

<property>
  <name>hive.security.authorization.enabled</name>
  <value>true</value>
</property>
<property>
  <name>hive.server2.enable.doAs</name>
  <value>false</value>
</property>
<property>
  <name>hive.users.in.admin.role</name>
  <value>root</value>
</property>
<property>
  <name>hive.security.authorization.manager</name>
  <value>org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory</value>
</property>
<property>
  <name>hive.security.authenticator.manager</name>
  <value>org.apache.hadoop.hive.ql.security.SessionStateUserAuthenticator</value>
</property>

服务端启动hiveserver2；客户端通过beeline进行连接

Hive 优化*

核心思想：把 Hive SQL 当做Mapreduce程序去优化

以下 SQL 不会转为Mapreduce来执行：

select 仅查询本表字段
where 仅对本表字段做条件过滤

Explain 显示执行计划

1	EXPLAIN [EXTENDED] query

Hive抓取策略

Hive中对某些情况的查询不需要使用 MapReduce 计算

抓取策略

1	Set hive.fetch.task.conversion=none/more;

Hive运行方式

本地模式
集群模式

开启本地模式

1	set hive.exec.mode.local.auto=true;

注意：
hive.exec.mode.local.auto.inputbytes.max 默认值为128M
表示加载文件的最大值，若大于该配置仍会以集群方式来运行

并行计算

通过设置以下参数开启并行模式：

1	set hive.exec.parallel=true;

注意：hive.exec.parallel.thread.number
（一次SQL计算中允许并行执行的job个数的最大值）

严格模式

通过设置以下参数开启严格模式：

1	set hive.mapred.mode=strict;

（默认为：nonstrict非严格模式）

查询限制

1、对于分区表，必须添加where对于分区字段的条件过滤；

2、order by语句必须包含limit输出限制；

3、限制执行笛卡尔积的查询。

Hive排序

Order By - 对于查询结果做全排序，只允许有一个reduce处理
（当数据量较大时，应慎用。严格模式下，必须结合limit来使用）
Sort By - 对于单个reduce的数据进行排序
Distribute By - 分区排序，经常和Sort By结合使用
Cluster By - 相当于 Sort By + Distribute By
（Cluster By不能通过asc、desc的方式指定排序规则；
可通过 distribute by column sort by column asc|desc 的方式）

Hive Join

Join计算时，将小表（驱动表）放在join的左边

Map Join：在Map端完成Join

两种实现方式：

SQL方式，在SQL语句中添加 MapJoin 标记（mapjoin hint）
语法：

1 2	SELECT /+ MAPJOIN(smallTable) / smallTable.key, bigTable.value FROM smallTable JOIN bigTable ON smallTable.key = bigTable.key;

开启自动的MapJoin

自动的mapjoin
通过修改以下配置启用自动的 mapjoin：
1
set hive.auto.convert.join = true;
（该参数为true时，Hive 自动对左边的表统计量，如果是小表就加入内存，即对小表使用 Map join）

相关配置参数：
1
hive.mapjoin.smalltable.filesize;
（大表小表判断的阈值，如果表的大小小于该值则会被加载到内存中运行）
1
hive.ignore.mapjoin.hint；
（默认值：true；是否忽略mapjoin hint 即mapjoin标记）
1
hive.auto.convert.join.noconditionaltask;
（默认值：true；将普通的join转化为普通的mapjoin时，是否将多个mapjoin转化为一个mapjoin）
1
hive.auto.convert.join.noconditionaltask.size;
（将多个mapjoin转化为一个mapjoin时，其表的最大值）

注意：hive.exec.parallel.thread.number（一次SQL计算中允许并行执行的job个数的最大值）
- 尽可能使用相同的连接键（会转化为一个MapReduce作业）

大表join大表

空key过滤：有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。
空key转换：有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上

Map-Side聚合

通过设置以下参数开启在Map端的聚合：

1	set hive.map.aggr=true;

合并小文件

文件数目小，容易在文件存储端造成压力，给hdfs造成压力，影响效率

设置合并属性

是否合并map输出文件：hive.merge.mapfiles=true
是否合并reduce输出文件：hive.merge.mapredfiles=true;
合并文件的大小：hive.merge.size.per.task=25610001000
去重统计

数据量小的时候无所谓，数据量大的情况下，由于 COUNT DISTINCT 操作需要用一个 Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换

控制Hive中Map以及Reduce的数量

Map 数量相关的参数

参数设置	解释
mapred.max.split.size	一个split的最大值，即每个map处理文件的最大值
mapred.min.split.size.per.node	一个节点上split的最小值
mapred.min.split.size.per.rack	一个机架上split的最小值

Reduce 数量相关的参数
| 参数| 解释|
| —- | —- | —- |
| mapred.reduce.tasks | 强制指定reduce任务的数量|
| hive.exec.reducers.bytes.per.reducer | 每个reduce任务处理的数据量 |
| hive.exec.reducers.max| 每个任务最大的reduce数|

Hive- JVM重用

适用场景：
1、小文件个数过多
2、task个数过多

通过 set mapred.job.reuse.jvm.num.tasks=n;来设置
（n为task插槽个数）

缺点：

设置开启之后，task插槽会一直占用资源，不论是否有task运行，

直到所有的 task 即整个 job 全部执行完成时，才会释放所有的 task 插槽资源！