hive知识点总结

Hive

Hive基本概念

什么是Hive
Hive:由FaceBook开源，用于解决海量结构化日志的数据统计
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能
本质：将HQL转换成MapReduce程序
Hive处理的数据存储在HDFS
Hive分析数据底层的实现是MapReduce
执行程序运行在YARN上
Hive的优缺点
优点
操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）
避免了去写MapReduce，减少开发人员的学习成本。
Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合；
Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高。
Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。
缺点
Hive的HQL表达能力有限
迭代式算法无法表达
数据挖掘方面不擅长
Hive的效率比较低
Hive自动生成的MapReduce作业，通常情况下不够智能化
Hive调优比较困难，粒度较粗
Hive架构原理
Hive架构
Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。
用户接口：Client
CLI（hive shell）、JDBC/ODBC(java访问hive)、WEBUI（浏览器访问hive）
元数据：Metastore
元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；
默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore
Hadoop
使用HDFS进行存储，使用MapReduce进行计算。
驱动器：Driver
解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
编译器（Physical Plan）：将AST编译生成逻辑执行计划。
优化器（Query Optimizer）：对逻辑执行计划进行优化。
执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。
Hive和数据库比较
查询语言
由于SQL被广泛的应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。
数据存储位置
Hive 是建立在 Hadoop 之上的，所有 Hive 的数据都是存储在 HDFS 中的。而数据库则可以将数据保存在块设备或者本地文件系统中。
数据更新
由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive中不支持对数据的改写和添加，所有的数据都是在加载的时候确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用 INSERT INTO … VALUES 添加数据，使用 UPDATE … SET修改数据。
索引
执行
Hive中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的。而数据库通常有自己的执行引擎。
执行延迟
可扩展性
由于Hive是建立在Hadoop之上的，因此Hive的可扩展性是和Hadoop的可扩展性是一致的（世界上最大的Hadoop 集群在 Yahoo!，2009年的规模在4000 台节点左右）。而数据库由于 ACID 语义的严格限制，扩展行非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有100台左右。
数据规模
由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。

Hive常用客户端命令

Hive安装部署

Hive安装及配置（Hadoop、MySQL已安装完成）
1.解压
2.配置hive-env.xml
	配置HADOOP_HOME路径
	配置HIVE_CONF_DIR路径
3.配置hive-site.xml
	配置元数据库地址
	配置元数据服务地址

Hive基本命令

[hadoop@hadoop102 hive]$ bin/hive -help
“-e”不进入hive的交互窗口执行sql语句
“-f”执行脚本中sql语句
（1）在/opt/install/datas目录下创建hivef.sql文件
（2）执行文件中的sql语句
（3）执行文件中的sql语句并将结果写入文件中

Hive其他命令操作

# 退出hive窗口：
hive(default)>exit;
hive(default)>quit;
# 在hive cli命令窗口中如何查看hdfs文件系统
hive(default)>dfs -ls /;
# 在hive cli命令窗口中如何查看hdfs本地系统
hive(default)>! ls /opt/install/datas;

Hive常见属性配置
Hive数据仓库位置配置
1.Default数据仓库的最原始位置是在hdfs上的：/user/hive/warehouse路径下
2.在仓库目录下，没有对默认的数据库default创建文件夹。如果某张表属于default数据库，直接在数据仓库目录下创建一个文件夹。
3.修改default数据仓库原始位置（将hive-default.xml.template如下配置信息拷贝到hive-site.xml文件中）
配置同组用户有执行权限
bin/hdfs dfs -chmod g+w /user/hive/warehouse
显示当前数据库，以及查询表的头信息配置
1.在hive-site.xml文件中添加如下配置信息，就可以实现显示当前数据库，以及查询表的头信息配置。
2.重新启动hive，对比配置前后差异
Hive运行日志信息配置
1.Hive的log默认存放在/tmp/hadoop/hive.log目录下（当前用户名下）。
2.修改hive的log存放日志到/opt/install/hive/logs
（1）修改/opt/install/hive/conf/hive-log4j.properties.template文件名称为hive-log4j.properties
（2）在hive-log4j.properties文件中修改log存放位置
参数配置方式
1.查看当前所有的配置信息
hive>set;
2.参数的配置三种方式及优先级
（1）配置文件方式
（2）命令行参数方式
[hadoop@hadoop103 hive]$ bin/hive -hiveconf mapred.reduce.tasks=10;
（3）参数声明方式
hive (default)> set mapred.reduce.tasks=100;
上述三种设定方式的优先级依次递增。即配置文件<命令行参数<参数声明。注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在会话建立以前已经完成了。

Hive数据类型

基本数据类型：
TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、BINARY、BOOLEAN、STRING、CHAR、VARCHAR、DATE、TIMESTAMP
复杂数据类型：
ARRAY、MAP、STRUCT

DDL数据定义

数据库

# 创建数据库
# 创建一个数据库，数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db。可以通过hive.metastore.warehouse.dir属性指定
# 避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）
hive (default)> create database if not exists myhivebook;
# 修改数据库
# 用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。
hive (default)> alter database myhivebook set dbproperties('createtime'='20191230');
# 查看修改结果
hive> desc database extended myhivebook;
# 查询数据库
# 显示数据库
# 显示数据库
hive> show databases;
# 过滤显示查询的数据库
hive> show databases like 'myhivebook*';
# 查看数据库详情
# 显示数据库信息
hive> desc database myhivebook;
# 显示数据库详细信息
hive> desc database extended myhivebook;
# 使用数据库
hive (default)> use myhivebook;
# 删除数据库
# 删除空数据库
hive>drop database myhivebook;
# 如果删除的数据库不存在，最好采用 if exists判断数据库是否存在
hive> drop database if exists myhivebook;
# 如果数据库不为空，可以采用cascade命令，强制删除
hive> drop database myhivebook cascade;

数据表

创建表
内部表
理论
内部表有时也被成为管理表。因为这种表，Hive控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。当删除一个内部表时，Hive也会删除这个表中数据。内部表不适合和其他工具共享数据。
语法

  CREATE TABLE IF NOT EXISTS employee (
      name string,
      work_place ARRAY<string>,
      sex_age STRUCT<sex:string,age:int>,
      skills_score MAP<string,int>,
      depart_title MAP<STRING,ARRAY<STRING>>
  )
  COMMENT 'This is an manage table'
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '|'
  COLLECTION ITEMS TERMINATED BY ','
  MAP KEYS TERMINATED BY ':'
  STORED AS TEXTFILE;

外部表
理论
因为表是外部表，所有Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。
语法

  CREATE EXTERNAL TABLE IF NOT EXISTS employee_external (
      name string,
      work_place ARRAY<string>,
      sex_age STRUCT<sex:string,age:int>,
      skills_score MAP<string,int>,
      depart_title MAP<STRING,ARRAY<STRING>>
  )
  COMMENT 'This is an external table'
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '|'
  COLLECTION ITEMS TERMINATED BY ','
  MAP KEYS TERMINATED BY ':'
  STORED AS TEXTFILE
  LOCATION '/user/root/employee';

外部表使用external关键字，location指定外部表位置
管理表和外部表的使用场景：
每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。
其他创建方式

# CTAS
  CREATE TABLE ctas_employee as SELECT * FROM employee;
注意：CTAS不能创建partition, external, bucket table
# CTE
  CREATE TABLE cte_employee AS
  WITH 
  r1 AS  (SELECT name FROM r2 WHERE name = 'Michael'),
  r2 AS  (SELECT name FROM employee WHERE sex_age.sex= 'Male'),
  r3 AS  (SELECT name FROM employee  WHERE sex_age.sex= 'Female')
  SELECT * FROM r1 UNION ALL SELECT * FROM r3;
# like
  CREATE TABLE employee_like LIKE employee;

分区表
分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。
分区表基本操作
创建分区表语法

  CREATE TABLE employee_partitioned(
      name string,
      work_place ARRAY<string>,
      sex_age STRUCT<sex:string,age:int>,
      skills_score MAP<string,int>,
      depart_title MAP<STRING,ARRAY<STRING>> )
  PARTITIONED BY (year INT, month INT)
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '|'
  COLLECTION ITEMS TERMINATED BY ','
  MAP KEYS TERMINATED BY ':';

加载数据到分区表中

  hive (default)> load data local inpath '/opt/install/datas/employee.txt' into table default.employee_partitioned partition(year=2019,month=10);
  hive (default)> load data local inpath '/opt/install/datas/employee.txt' into table default.employee_partitioned partition(year=2019,month=11);
  hive (default)> load data local inpath '/opt/module/datas/employee.txt' into table default.employee_partitioned partition(year=2019,month=12);

查询分区表中数据

# 单分区查询
  select * from employee_partition where year = 2019 and month=10;
# 多分区联合查询
  hive (default)> select * from employee_partition where year = 2019 and month=10
                union
                select * from employee_partition where year = 2019 and month=11
                union
                select * from employee_partition where year = 2019 and month=12;

增加分区

# 创建单个分区
  hive (default)>  alter table  mployee_partitioned add partition(year=2019,month=10) ;
# 同时创建多个分区
  hive (default)>  alter table  mployee_partitioned add partition(year=2019,month=11) partition(year=2019,month=12);

删除分区

# 删除单个分区
  hive (default)> alter table employee_partition drop partition (year = 2019,month=10);
# 同时删除多个分区
  hive (default)> alter table employee_partition drop partition (year = 2019,month=11), partition (year = 2019,month=12);

查看分区表有多少分区(重要)

  hive>show partitions employee_partition;

动态分区

  insert into table employee_partitioned partition(year, month)
  select name,array('Toronto') as work_place,
  named_struct("sex","male","age",30) as sex_age,
  map("python",90) as skills_score,
  map("r&d", array('developer')) as depart_title,
  year(start_date) as year,month(start_date) as month
  from employee_hr eh ;

注意事项
开启相关配置

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

分区列选择必须可枚举

分桶表

更高的查询处理效率
使抽样（sampling）更高效
根据“桶列”的哈希函数将数据进行分桶

分桶表基本操作
创建分桶表

  CREATE TABLE employee_id_buckets                         
  (
    name string,
    employee_id int,
    work_place ARRAY<string>,
    sex_age STRUCT<sex:string,age:int>,
    skills_score MAP<string,int>,
    depart_title MAP<STRING,ARRAY<STRING>>
  )
  CLUSTERED BY (employee_id) INTO 2 BUCKETS
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '|'
  COLLECTION ITEMS TERMINATED BY ','
  MAP KEYS TERMINATED BY ':';

注意事项：

SET hive.enforce.bucketing = true;
# 必须使用INSERT方式加载数据
INSERT OVERWRITE TABLE employee_id_buckets SELECT * FROM employee_id;

视图

基本操作

# 创建视图
CREATE VIEW view_name AS SELECT statement;
# 查找视图
SHOW TABLES; 
# 查看视图定义
SHOW CREATE TABLE view_name; 
# 删除视图
DROP view_name;
# 更改视图属性
ALTER VIEW view_name SET TBLPROPERTIES ('comment' = 'This is a view');
# 更改视图定义
ALTER VIEW view_name AS SELECT statement;

侧视图

# 常与表生成函数结合使用，将函数的输入和输出连接，通常用于规范化行或解析JSON
select name,wps,skill,score from employee 
lateral view explode(work_place) work_place_single as wps
lateral view explode(skills_score) sks as skill,score;
注：使用outer，为空也会生成结果

修改表

# 重命名表
ALTER TABLE table_name RENAME TO new_table_name
# 增加、修改和删除表分区
# 增加/修改/替换列信息
# 更新列
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
# 增加和替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 
注意：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。

删除表（慎重去删除表）

hive (default)> drop table employee_partition;

查询

基本查询（select…from）
全表和特定字段查询
全表查询
hive (default)> select * from employee;
选择特定列查询
hive (default)> select empno, ename from employee;
注意：
SQL 语言大小写不敏感。
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写。
使用缩进提高语句的可读性。
列别名
重命名一个列。
便于计算。
紧跟列名，也可以在列名和别名之间加入关键字‘AS’
select ename AS name, from employee;
Limit语句
典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
hive (default)> select * from employee limit 5;
Join语句
等值Join
Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
表的别名
1）好处
（1）使用别名可以简化查询。
（2）使用表名前缀可以提高执行效率。
内连接
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
hive (default)> select * from
emp_basic eb join emp_psn ep on eb.emp_id = ep.emp_id limit 5;

hive (default)> select * from
emp_basic eb, emp_psn ep where eb.emp_id = ep.emp_id limit 5;
左外连接
左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
hive (default)> select * from
emp_basic eb left join emp_psn ep on eb.emp_id = ep.emp_id limit 5;
右外连接
右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
hive (default)> hive (default)> select * from
emp_basic eb right join emp_psn ep on eb.emp_id = ep.emp_id limit 5;
全外链接
满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
hive (default)> hive (default)> select * from
emp_basic eb full join emp_psn ep on eb.emp_id = ep.emp_id limit 5;
笛卡尔积 JOIN
1.笛卡尔集会在下面条件下产生:
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
MapJoin
开启MapJoin（默认开启）
set hive.auto.convert.join = true
运行时自动将连接转换为MAPJOIN
集合操作
并集
union
select * from emp_basic eb where eb.company = ‘Roomm’
union
select * from emp_basic eb where eb.company = ‘Cogilith’
order by emp_id;
union all
select * from emp_basic eb where eb.company = ‘Roomm’
union all
select * from emp_basic eb where eb.company = ‘Cogilith’
order by emp_id;
区别：union all不去重，union去重
交集
SELECT
name
FROM employee a
JOIN employee_hr b
ON a.name = b.name;
差集
SELECT
name
FROM employee a
LEFT JOIN employee_hr b
ON a.name = b.name
WHERE b.name IS NULL;
分组
Group By语句
select
if(category > 4000, ‘GOOD’, ‘BAD’) as newcat,
max(offervalue)
from offers
group by category if(category > 4000, ‘GOOD’, ‘BAD’);
GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
Having语句
select
a.age
from (
select
count(*) as cnt,
sex_age.age
from employee
group by
sex_age.age
) a
where a.cnt <= 1;
having与where不同点
（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
（2）where后面不能写分组函数，而having后面可以使用分组函数。
（3）having只用于group by分组统计语句。
排序
全局排序（Order By）
Order By：全局排序，一个MapReduce
使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
ORDER BY 子句在SELECT语句的结尾。
每个MapReduce内部排序（Sort By）
Sort By：每个MapReduce内部进行排序，对全局结果集来说不是排序
1.设置reduce个数
hive (default)> set mapreduce.job.reduces=3;
2.查看设置reduce个数
hive (default)> set mapreduce.job.reduces;
分区排序（Distribute By）
Distribute By：类似MR中partition，进行分区，结合sort by使用。
注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
Cluster By
当distribute by和sorts by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。
以下两种写法等价
select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;
窗口函数
扫描多个输入行来计算每个输出值，为每行数据生成一行结果
可以通过窗口函数来实现复杂的计算和聚合
语法
Function (arg1,…, arg n) OVER ([PARTITION BY <…>] [ORDER BY <…>] [<window_clause>])
注：PARTITION BY类似于GROUP BY，未指定则按整个结果集
只有指定ORDER BY子句之后才能进行窗口定义
可同时使用多个窗口函数
过滤窗口函数计算结果必须在外面一层
分类
排序
ROW_NUMBER()
RANK()
DENSE_RANK()
PERCENT_RANK()
集合
COUNT
SUM
AVG
MIN
MAX
分析
LEAD
LAG
FIRST_VALUE
LAST_VALUE
窗口

  SELECT
  name, dept_num AS dept, salary AS sal,
  MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) win1,
  MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) win2,
  MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) win3,
  MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) win4,
  MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) win5,
  MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND CURRENT ROW) win6,
  MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) win7,
  MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) win8,
  MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) win9,
  MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) win10,
  MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) win11,
  MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS 2 PRECEDING) win12
  FROM employee_contract  ORDER BY dept, name;

行类型窗口
根据当前行之前或之后的行号确定的窗口
范围类型窗口
范围窗口是取分组内的值在指定范围区间内的行
该范围值/区间必须是数字或日期类型

DML数据操作

数据导入
向表中装载数据（Load）

  LOAD DATA LOCAL INPATH '/home/dayongd/Downloads/employee.txt' 
  OVERWRITE INTO TABLE employee;
  LOAD DATA LOCAL INPATH '/home/dayongd/Downloads/employee.txt' 
  OVERWRITE INTO TABLE employee_partitioned
  PARTITION (year=2014, month=12);
  LOAD DATA INPATH '/tmp/employee.txt' 
  OVERWRITE INTO TABLE employee_partitioned
  PARTITION (year=2017, month=12);

语法

load data [local] inpath into/overwrite table
注：LOCAL：指定文件位于本地文件系统
    OVERWRITE：表示覆盖现有数据

注意事项
如果文件是在本地，文件不会被删除，如果是在HDFS上，会被剪切
通过查询语句向表中插入数据（Insert）

# 基本插入数据
  insert into employee(name) value('Judy'),('John');
# 基本模式插入（根据单张表查询结果）
  insert into employee select * from ctas_employee;
# 多插入模式（根据多张表查询结果）
  from ctas_employee
  insert overwrite table employee select *
  insert overwrite table employee_internal select *;
# 插入分区
  from ctas_patitioned 
  insert overwrite table employee PARTITION (year, month)
  select *,'2018','09';

查询语句中创建表并加载数据（As Select）

# 根据查询结果创建表（查询的结果会添加到新创建的表中）
  create table if not exists employee1
  as select * from employee;

Import数据到指定Hive表中

  import table employee_partition partition(year=2019,month=10) from '/user/hive/warehouse/export/employee_partition';
先用export导出后，再将数据导入。

数据导出

Insert导出
# 将查询的结果导出到本地
  from ctas_employee
  insert overwrite local directory '/tmp/out1'  select *
# 将查询的结果格式化导出到本地
  -以指定格式插入数据
  insert overwrite directory '/tmp/out3'
  row format delimited fields terminated by ','
  select * from ctas_employee;
# 将查询的结果导出到HDFS上(没有local)
  insert overwrite directory '/tmp/out1' select *
Export导出到HDFS上
  EXPORT TABLE employee TO '/tmp/output3';
  EXPORT TABLE employee_partitioned partition (year=2014, month=11) TO '/tmp/output5';
清除表中数据（Truncate）
hive (default)> truncate table student;
注意：Truncate只能删除管理表，不能删除外部表中数据

函数

常用函数
日期类
字符串
子主题 3
常用函数
求总行数（count）
求工资的最大值（max）
求工资的最小值（min）
求工资的总和（sum）
求工资的平均值（avg）
标准函数UDF
一行数据中的一列或多列为输入，结果为单一值
分类
字符
concat(string|binary A, string|binary B…)
对二进制字节码或字符串按次序进行拼接
concat_ws
使用指定的分隔符进行拼接
length
返回字符串的长度
regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)
按正则表达式PATTERN将字符串中符合条件的部分替换成REPLACEMENT所指定的字符串
split(string str, string pat)
按照正则表达式pat来分割字符串str
str_to_map(text[, delimiter1, delimiter2])
将字符串str按照指定分隔符转换成Map
类型转换函数
cast(expr as )
将expr转换成type类型 如：cast(“1” as BIGINT) 将字符串1转换成了BIGINT类型
数字函数

# 返回对a四舍五入的BIGINT值
round(DOUBLE a)
# 返回对a四舍五入并保留d位小数位的值
round(DOUBLE a, INT d)
# 向下取整，如：6.10->6   -3.4->-4
floor(DOUBLE a)
# 返回一个DOUBLE型随机数，seed是随机因子
rand(INT seed)
# 计算a的p次幂
power(DOUBLE a, DOUBLE p)
# 计算a的绝对值
abs(DOUBLE a)

日期函数（重点掌握）

# 将时间戳转换成format格式
from_unixtime(bigint unixtime[, string format])
# 获取本地时区下的时间戳
unix_timestamp()
# 将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳
unix_timestamp(string date)
# 返回时间字符串的日期部分
to_date(string timestamp)
# 返回时间字符串的年份部分
year(string date)
# 返回月/天/时/分/秒/第几周
month/day/hour/minute/second/weekofyear
# 计算开始时间到结束时间相差的天数
datediff(string enddate, string startdate)
# 从开始时间startdate加上days
date_add(string startdate, int days)
# 从开始时间startdate减去days
date_sub(string startdate, int days)
# 返回当前时间的日期
current_date
# 返回当前时间戳
current_timestamp
# 按指定格式返回时间date 如：date_format("2016-06-22","MM-dd")=06-22
date_format(date/timestamp/string ts, string fmt)

集合函数

# 返回map中键值对个数
size(Map<K.V>)
# 返回数组的长度
size(Array<T>)
# 返回map中的所有key
map_keys(Map<K.V>)
# 返回map中的所有value
map_values(Map<K.V>)
# 如该数组Array<T>包含value返回true。，否则返回false
array_contains(Array<T>, value)
# 对数组进行排序
sort_array(Array<T>)

条件函数

# 如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull
if(boolean testCondition, T valueTrue, T valueFalseOrNull)
# value为NULL返回default_value,否则返回value
nvl(T value, T default_value)
# 返回第一非null的值，如果全部都为NULL就返回NULL
COALESCE(T v1, T v2, ...)
# 如果a=b就返回c,a=d就返回e，否则返回f
CASE a WHEN b THEN c 
[WHEN d THEN e]* [ELSE f] END
# 如果a为null就返回true，否则返回false
isnull( a )
# 如果a为非null就返回true，否则返回false
isnotnull ( a )

聚合函数UDAF
多行的零列到多列为输入，结果为单一值
常用函数
count
sum
max
min
avg
表生成函数UDTF：零个或多个输入，结果为多列或多行
常用函数

# 对于array中的每个元素生成一行且包含该元素
explode(array<T>)
# 每行对应每个map键值对，其中一个字段是map的键，另一个字段是map的值
explode(MAP)
# 与explode类似，不同的是还返回各元素在数组中的位置
posexplode(ARRAY)
# 把k列转换成n行，每行有k/n个字段，其中n必须是常数
stack(INT n, v_1, v_2, ..., v_k)
# 从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值
json_tuple(jsonStr, k1, k2, ...)

自定义UDF
步骤
继承UDF类或GenericUDF类
重写evaluate()方法并实现函数逻辑
编译打包为jar文件
复制到正确的HDFS路径
使用jar创建临时/永久函数
调用函数

企业级调优

Fetch抓取
Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算。例如：SELECT * FROM employees;在这种情况下，Hive可以简单地读取employee对应的存储目录下的文件，然后输出查询结果到控制台。
在hive-default.xml.template文件中hive.fetch.task.conversion默认是more，老版本hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。
本地模式
大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过，有时Hive的输入数据量是非常小的。在这种情况下，为查询触发执行任务时消耗可能会比实际job的执行时间要多的多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。
用户可以通过设置hive.exec.mode.local.auto的值为true，来让Hive在适当的时候自动启动这个优化。
表的优化
小表、大表Join
将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率；再进一步，可以使用Group让小的维度表（1000条以下的记录条数）先进内存。在map端完成reduce。
新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别。
大表Join大表
空KEY过滤：
有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。
空key转换：
有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上。
MapJoin
如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join，避免reducer处理
Group By
默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。
并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。
1）开启Map端聚合参数设置
（1）是否在Map端进行聚合，默认为True
hive.map.aggr = true
（2）在Map端进行聚合操作的条目数目
hive.groupby.mapaggr.checkinterval = 100000
（3）有数据倾斜的时候进行负载均衡（默认是false）
hive.groupby.skewindata = true
当选项设定为 true，生成的查询计划会有两个MR Job。第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作。
Count(Distinct) 去重统计
数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换
笛卡尔积
尽量避免笛卡尔积，join的时候不加on条件，或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积
行列过滤
列处理：在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。
行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤
动态分区调整
关系型数据库中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用Hive的动态分区，需要进行相应的配置。
1）开启动态分区参数设置
（1）开启动态分区功能（默认true，开启）
hive.exec.dynamic.partition=true
（2）设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）
hive.exec.dynamic.partition.mode=nonstrict
（3）在所有执行MR的节点上，最大一共可以创建多少个动态分区。
hive.exec.max.dynamic.partitions=1000
（4）在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。
hive.exec.max.dynamic.partitions.pernode=100
（5）整个MR Job中，最大可以创建多少个HDFS文件。
hive.exec.max.created.files=100000
（6）当有空分区生成时，是否抛出异常。一般不需要设置。
hive.error.on.empty.partition=false
分桶
分区
数据倾斜
Map数
Map数
1）通常情况下，作业会通过input的目录产生一个或者多个map任务。
主要的决定因素有：input的文件总个数，input的文件大小，集群设置的文件块大小。
2）是不是map数越多越好？
答案是否定的。如果一个任务有很多小文件（远远小于块大小128m），则每个小文件也会被当做一个块，用一个map任务来完成，而一个map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的map数是受限的。
3）是不是保证每个map处理接近128m的文件块，就高枕无忧了？
答案也是不一定。比如有一个127m的文件，正常会用一个map去完成，但这个文件只有一个或者两个小字段，却有几千万的记录，如果map处理的逻辑比较复杂，用一个map任务去做，肯定也比较耗时。
针对上面的问题2和3，我们需要采取两种方式来解决：即减少map数和增加map数；

小文件进行合并
在map执行前合并小文件，减少map数：CombineHiveInputFormat具有对小文件进行合并的功能（系统默认的格式）。HiveInputFormat没有对小文件合并功能。
set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

复杂文件增加Map数
当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。
增加map的方法为：根据computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M公式，调整maxSize最大值。让maxSize最大值低于blocksize就可以增加map的个数。
案例实操：

  # 执行查询
  hive (default)> select count(*) from emp;
  Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
  # 设置最大切片值为100个字节
  hive (default)> set mapreduce.input.fileinputformat.split.maxsize=100;
  hive (default)> select count(*) from emp;
  Hadoop job information for Stage-1: number of mappers: 6; number of reducers: 1

Reduce数
1）调整reduce个数方法一
（1）每个Reduce处理的数据量默认是256MB
hive.exec.reducers.bytes.per.reducer=256000000
（2）每个任务最大的reduce数，默认为1009
hive.exec.reducers.max=1009
（3）计算reducer数的公式
N=min(参数2，总输入数据量/参数1)
2）调整reduce个数方法二
在hadoop的mapred-default.xml文件中修改
设置每个job的Reduce个数
set mapreduce.job.reduces = 15;
3）reduce个数并不是越多越好
1）过多的启动和初始化reduce也会消耗时间和资源；
2）另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；
在设置reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的reduce数；使单个reduce任务处理数据量大小要合适；

并行执行
Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段。或者Hive执行过程中可能需要的其他阶段。默认情况下，Hive一次只会执行一个阶段。不过，某个特定的job可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个job的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么job可能就越快完成。
通过设置参数hive.exec.parallel值为true，就可以开启并发执行。不过，在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。
set hive.exec.parallel=true; //打开任务并行执行
set hive.exec.parallel.thread.number=16; //同一个sql允许最大并行度，默认为8。
严格模式
Hive提供了一个严格模式，可以防止用户执行那些可能意向不到的不好的影响的查询。通过设置属性hive.mapred.mode值为默认是非严格模式nonstrict 。开启严格模式需要修改hive.mapred.mode值为strict，开启严格模式。
1）对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。
2）对于使用了order by语句的查询，要求必须使用limit语句。因为order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。
3）限制笛卡尔积的查询。对关系型数据库非常了解的用户可能期望在执行JOIN查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是，Hive并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况。
JVM重用
JVM重用是Hadoop调优参数的内容，其对Hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或task特别多的场景，这类场景大多数执行时间都很短。
Hadoop的默认配置通常是使用派生JVM来执行map和Reduce任务的。这时JVM的启动过程可能会造成相当大的开销，尤其是执行的job包含有成百上千task任务的情况。JVM重用可以使得JVM实例在同一个job中重新使用N次。N的值可以在Hadoop的mapred-site.xml文件中进行配置。通常在10-20之间，具体多少需要根据具体业务场景测试得出。
这个功能的缺点是，开启JVM重用将一直占用使用到的task插槽，以便进行重用，直到任务完成后才能释放。如果某个“不平衡的”job中有某几个reduce task执行的时间要比其他Reduce task消耗的时间多的多的话，那么保留的插槽就会一直空闲着却无法被其他的job使用，直到所有的task都结束了才会释放
推测执行
在分布式集群环境下，因为程序Bug（包括Hadoop本身的bug），负载不均衡或者资源分布不均等原因，会造成同一个作业的多个任务之间运行速度不一致，有些任务的运行速度可能明显慢于其他任务（比如一个作业的某个任务进度只有50%，而其他所有任务已经运行完毕），则这些任务会拖慢作业的整体执行进度。为了避免这种情况发生，Hadoop采用了推测执行（Speculative Execution）机制，它根据一定的法则推测出“拖后腿”的任务，并为这样的任务启动一个备份任务，让该任务与原始任务同时处理同一份数据，并最终选用最先成功运行完成任务的计算结果作为最终结果。
压缩
执行计划（Explain）
1）基本语法

EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query

2）案例实操

# 查看下面这条语句的执行计划
hive (default)> explain select * from emp;
hive (default)> explain select deptno, avg(sal) avg_sal from emp group by deptno;
# 查看详细执行计划
hive (default)> explain extended select * from emp;
hive (default)> explain extended select deptno, avg(sal) avg_sal from emp group by deptno;

数据仓库