hive整理笔记

基本介绍：

概述：

直接使用MapReduce处理大数据，将面临以下问题：

- MapReduce 开发难度大，学习成本高(wordCount => Hello World)
- Hdfs文件没有字段名、没有数据类型，不方便进行数据的有效管理
- 使用MapReduce框架开发，项目周期长，成本高

Hive是基于Hadoop的一个数据仓库工具，可以将 结构化的数据文件映射为一张表(类似于RDBMS中的表)，并提供类SQL查询功能；Hive是由Facebook开源，用于解决海量结构化日志的数据统计。
Hive本质是：将 SQL 转换为 MapReduce 的任务进行运算
底层由HDFS来提供数据存储
可以将Hive理解为一个：将 SQL 转换为 MapReduce 任务的工具

Hive和RDBMS对比：

由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language)，因此很容易将Hive 理解为数据库。
其实从结构上来看，Hive 和传统的关系数据库除了拥有类似的查询语言，再无类似之处。

查询语言相似。
    HQL <=> SQL 高度相似由于SQL被广泛的应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。

数据规模。
    Hive存储海量数据；RDBMS只能处理有限的数据集；由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；而RDBMS可以支持的数据规模较小。

执行引擎。
    Hive的引擎是MR/Tez/Spark/Flink；RDBMS使用自己的执行引擎Hive中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的。而RDBMS通常有自己的执行引擎。

数据存储。
    Hive保存在HDFS上；RDBMS保存在本地文件系统 或 裸设备Hive 的数据都是存储在 HDFS 中的。而RDBMS是将数据保存在本地文件系统或裸设备中。

执行速度。
    Hive相对慢（MR/数据量）；RDBMS相对快；Hive存储的数据量大，在查询数据的时候，通常没有索引，需要扫描整个表；
    加之Hive使用MapReduce作为执行引擎，这些因素都会导致较高的延迟。而RDBMS对数据的访问通常是基于索引的，执行延迟较低。
    当然这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出并行的优势。

可扩展性。
    Hive支持水平扩展；通常RDBMS支持垂直扩展，对水平扩展不友好Hive建立在Hadoop之上，其可扩展性与Hadoop的可扩展性是一致的（Hadoop集群规模可以轻松超过1000个节点）。
    而RDBMS由于 ACID 语义的严格限制，扩展行非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有100台左右。

数据更新。
    Hive对数据更新不友好；RDBMS支持频繁、快速数据更新Hive是针对数据仓库应用设计的，数据仓库的内容是读多写少的。
    因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。而RDBMS中的数据需要频繁、快速的进行更新。

优缺点：

Hive的优点：

学习成本低。Hive提供了类似SQL的查询语言，开发人员能快速上手；
处理海量数据。底层执行的是MapReduce 任务；
系统可以水平扩展。底层基于Hadoop；
功能可以扩展。Hive允许用户自定义函数；
良好的容错性。某个节点发生故障，HQL仍然可以正常完成；
统一的元数据管理。元数据包括：有哪些表、表有什么字段、字段是什么类型    

Hive的缺点：

HQL表达能力有限；
迭代计算无法表达；
Hive的执行效率不高(基于MR的执行引擎)；
Hive自动生成的MapReduce作业，某些情况下不够智能；
Hive的调优困难；    

Hive架构：

cli命令行、HiveServer客户端、Web客户端
解释器（解释成AST语言）
编译器（翻译成执行计划）        MetaStore元数据，一般存储在mysql
优化器（优化执行计划）
执行器（物理执行计划）
Hadoop yarn集群

1. 用户接口 CLI(Common Line Interface)：Hive的命令行，用于接收HQL，并返回结果； JDBC/ODBC：是指Hive的java实现，与传统数据库JDBC类似；
    WebUI：是指可通过浏览器访问Hive；
2. Thrift Server
    Hive可选组件，是一个软件框架服务，允许客户端使用包括Java、C++、Ruby和其他很多种语言，通过编程的方式远程访问Hive；
3. 元数据管理(MetaStore) 
    Hive将元数据存储在关系数据库中(如mysql、derby)。Hive的元数据包括：数据库名、表名及类型、字段名称及数据类型、数据所在位置等；
4. 驱动程序(Driver)
    解析器 (SQLParser) ：使用第三方工具（antlr）将HQL字符串转换成抽象语法树（AST）；对AST进行语法分析，比如字段是否存在、SQL语义是否有误、表是否存在；
    编译器 (Compiler) ：将抽象语法树编译生成逻辑执行计划；
    优化器 (Optimizer) ：对逻辑执行计划进行优化，减少不必要的列、使用分区等；
    执行器 (Executr) ：把逻辑执行计划转换成可以运行的物理计划；

安装运维：

安装：

1、安装MySQL

CREATE USER 'hive'@'%' IDENTIFIED BY '12345678';
GRANT ALL ON *.* TO 'hive'@'%';
FLUSH PRIVILEGES;

2、安装配置Hive

1、下载、上传、解压缩

2、修改环境变量

export HIVE_HOME=/opt/lagou/servers/hive-2.3.7
export PATH=$PATH:$HIVE_HOME/bin

3、修改hive配置

注意jdbc的连接串，如果没有 useSSL=false 会有大量警告
在xml文件中 & 表示 &

4、拷贝JDBC的驱动程序

将 mysql-connector-java-5.1.46.jar 拷贝到 $HIVE_HOME/lib

5、初始化元数据库

schematool -dbType mysql -initSchema
可能需要手动创建database

3、Hive添加常用配置

数据存储位置        
显示当前库
显示表头属性
本地模式
    当 Hive 的输入数据量非常小时，Hive 通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间会明显被缩短。当一个job满足如下条件才能真正使用本地模式：
        job的输入数据量必须小于参数：hive.exec.mode.local.auto.inputbytes.max(默认128MB)
        job的map数必须小于参数：hive.exec.mode.local.auto.tasks.max (默认4)
        job的reduce数必须为0或者1
Hive的日志文件
    配置路径：$HIVE_HOME/conf/hive-log4j2.properties
    property.hive.log.dir = /opt/lagou/servers/hive-2.3.7/logs

参数配置方式：

查看参数配置信息
    -- 查看全部参数
        hive> set;
    -- 查看某个参数
        hive> set hive.exec.mode.local.auto;
        hive.exec.mode.local.auto=false

参数配置的三种方式：
    1、用户自定义配置文件(hive-site.xml)
    2、启动hive时指定参数(-hiveconf)
        仅对本次启动有效。
        hive -hiveconf hive.exec.mode.local.auto=true
    3、hive命令行指定参数(set)
        仅对本次启动有效，set hive.exec.mode.local.auto=false;

    配置信息的优先级：
        set > -hiveconf > hive-site.xml > hive-default.xml

读时模式：

概述：

在传统数据库中，在加载时发现数据不符合表的定义，则拒绝加载数据。数据在写入数据库时对照表模式进行检查，这种模式称为"写时模式"（schema on write）。            
写时模式 -> 写数据检查 -> RDBMS；

Hive：

Hive中数据加载过程采用"读时模式" (schema on read)，加载数据时不进行数据格式的校验，读取数据时如果不合法则显示NULL。这种模式的优点是加载数据迅速。
读时模式 -> 读时检查数据 -> Hive；好处：加载数据快；问题：数据显示NULL

常用命令：

-e：不进入hive交互窗口，执行sql语句hive -e "select * from users"    
-f：执行脚本中sql语句
在命令行执行 shell 命令 / dfs 命令
    hive> ! ls;
    hive> ! clear;
    hive> dfs -ls / ;

常见问题：

1.cli中文乱码问题：

mac需要设置terminal的profiles/environmens

数据类型：

概述：

Hive支持关系型数据库的绝大多数基本数据类型，同时也支持4种集合数据类型。

基本数据类型：

TINYINT -- 1字节的有符号整数    
Integers(整型)                    SMALLINT -- 2字节的有符号整数
INT -- 4字节的有符号整数
BIGINT -- 8字节的有符号整数，无穷用\N表示存储，显示为NULL
Floating point numbers(浮点数)
FLOAT -- 单精度浮点数
DOUBLE -- 双精度浮点数
Fixed point numbers(定点数)        DECIMAL -- 17字节，任意精度数字。通常用户自定义decimal(12, 6)
String(字符串)
STRING -- 可指定字符集的不定长字符串
VARCHAR -- 1-65535长度的不定长字符串
CHAR -- 1-255定长字符串
Datetime(时间日期类型)
STRTIMESTAMP -- 时间戳（纳秒精度）
DATE -- 时间日期类型
Boolean(布尔类型)                 BOOLEAN -- TRUE / FALSE
Binary types(二进制类型)
BINARY -- 字节序列

数据类型的隐式转换：

Hive的数据类型是可以进行隐式转换的，类似于Java的类型转换。如用户在查询中将一种浮点类型和另一种浮点类型的值做对比，Hive会将类型转换成两个浮点类型中值较大的那个类型，
即：将FLOAT类型转换成DOUBLE类型；当然如果需要的话，任意整型会转化成DOUBLE类型。 Hive 中基本数据类型遵循以下层次结构，按照这个层次结构，子类型到祖先类型允许隐式转换。

数据类型的显示转换 ：

使用cast函数进行强制类型转换；如果强制类型转换失败，返回NULL    
select cast('1111s' as int);

集合数据类型：

Hive支持集合数据类型，包括array、map、struct、union
    ARRAY       有序的相同数据类型的集合                            array(1,2)
    MAP         key-value对。key必须是基本数据类型，value不限     map('a', 1, 'b',2)
    STRUCT      不同类型字段的集合。类似于C语言的结构体                struct('1',1,1.0),named_struct('col1', '1', 'col2', 1,'clo3', 1.0)
    UNION       不同类型的元素存储在同一字段的不同行中             create_union(1, 'a', 63)

文本文件数据编码：

概述：

Hive表中的数据在存储在文件系统上，Hive定义了默认的存储格式，也支持用户自定义文件存储格式。    

默认分隔符：

Hive默认使用几个很少出现在字段值中的控制字符，来表示替换默认分隔符的字符。
    \n  换行符     用于分隔行。每一行是一条记录，使用换行符分割数据
    ^A < Ctrl >+A   用于分隔字段。在CREATE TABLE语句中使用八进制编码\001表示
    ^B < Ctrl >+B   用于分隔 ARRAY、MAP、STRUCT 中的元素。在CREATETABLE语句中使用八进制编码\002表示
    ^C < Ctrl +C>   Map中 key、value之间的分隔符。在CREATE TABLE语句中使用八进制编码\003表示

自定义：

Hive 中没有定义专门的数据格式，数据格式可以由用户指定，用户定义数据格式需要指定三个属性：列分隔符（通常为空格、"\t"、"\x001"）、行分隔符（"\n"）以及读取文件数据的方法。
在加载数据的过程中，Hive 不会对数据本身进行任何修改，而只是将数据内容复制或者移动到相应的 HDFS 目录中。    
^A / ^B / ^C 都是特殊的控制字符，使用 more 、 cat 命令是看不见的；可以使用cat -A file.dat    

DDL命令：

概述：

DDL（data definition language）： 主要的命令有CREATE、ALTER、DROP等。
DDL主要是用在定义、修改数据库对象的结构 或 数据类型。

数据库操作：

创建：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
    [COMMENT database_comment]
    [LOCATION hdfs_path]
    [MANAGEDLOCATION hdfs_path]
    [WITH DBPROPERTIES (property_name=property_value, ...)];
示例：
    create database mydb;       // 创建数据库，在HDFS上存储路径为 /user/hive/warehouse/*.db
    create database if not exists mydb;
    create database if not exists mydb2 comment 'this is mydb2' location '/user/hive/mydb2.db';

查看：

show database;
desc database mydb2;
desc database extended mydb2;
describe database extended mydb2;

修改：

alter table add columns(age int)
alter table app_converse_billing rename to app_converse_billing_bak;
alter table app_converse_billing_bak set location '/usr/hive/warehouse/app_converse_billing_bak'   
    内部表相应hdfs路径会被移动,
    外部表不会移动，但元信息会修改，移动路径后反而找不到。

使用：

use mydb;

删除：

drop database databasename;            //只能删除空数据库
drop database databasename cascade;

表操作：

创建：

方式一：

create [external] table [IF NOT EXISTS] table_name
    [(colName colType [comment 'comment'], ...)]
    [comment table_comment]
    [partitioned by (colName colType [comment col_comment], ...)]       //对表中数据进行分区，指定表的分区字段
    [clustered BY (colName, colName, ...)                           //创建分桶表，指定分桶字段
    [sorted by (col_name [ASC|DESC], ...)] into num_buckets buckets]//对桶中的一个或多个列排序，较少使用
    [row format delimited fields terminated by '\t']
    [stored as file_format]     //可选SEQUENCEFILE二进制序列文件|TEXTFILE纯文本|RCFILE/ORC
    [LOCATION hdfs_path]        //表在HDFS上的存放位置
    [TBLPROPERTIES (property_name=property_value, ...)]//定义表的属性
    [AS select_statement];      //后面可以接查询语句，表示根据后面的查询结果创建表，select中选取的列名会作为新表的列名
                                // 会改变表的属性、结构，只能是内部表、分区分桶也没有了。字段的注释comment也会丢掉
注意分区字段不支持中文，否则insert的时候报错MoveTask异常，看具体log（hive-site.yaml）可以看到数据库的哪个sql执行发生异常。
配置：
    数据库、表、字段等都需要支持utf8，看log发现哪个表发生了异常。
    alter database hivemetastore default character set utf8;
    alter table PARTITIONS default character set utf8;
    alter table PARTITIONS modify column PART_NAME varchar(255) CHARACTER SET utf8 DEFAULT NULL;  注意767个字节最长，255个utf8字符。
    alter table SDS modify column LOCATION varchar(4000) CHARACTER SET utf8 DEFAULT NULL;
    alter table PARTITION_KEY_VALS modify column PART_KEY_VAL varchar(256)  CHARACTER SET utf8 DEFAULT NULL;
字符导致的drop超时解决：
    select * from TBLS where TBL_NAME='app_converse_billing'
    delete from PARTITION_KEY_VALS where PART_ID in (select PART_ID from PARTITIONS where TBL_ID=1188);
    delete from PARTITION_PARAMS where PART_ID in (select PART_ID from PARTITIONS where TBL_ID=1188);
    delete from PARTITIONS WHERE TBL_ID=1188;
    再次drop hive table即可。

方式二：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS]
    [db_name.]table_name
    LIKE existing_table_or_view_name  //like 表名，允许用户复制现有的表结构，但是不复制数据
    [LOCATION hdfs_path];

方式三：

create table xxx 
[row format]
[stored as orc]
as 
select * from xxx;
使用查询的结果创建和填充表
注意，这样创建的表不是分区表，同时使用系统默认的字段分隔符\001。
用途：
    创建一张这样的临时表，然后load data进来，然后给分区表动态分区并导入数据。注意字段的位置，分区字段必须是最后。
    insert into table test partition(dt) select * from tmp; 
    示例：
        set hive.exec.dynamic.partition.mode=nonstrict;
        insert into app_converse_billing_tmp partition(dt) select channel,appname,"" as appPkgName,unitprice,conversecnt,feeinyuan,0 as downloadCnt,0 as cpdUnitPrice,dt from app_converse_billing;

存储子句：

ROW FORMAT DELIMITED
[FIELDS TERMINATED 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, ...)]

内部表与外部表：

默认情况下，创建内部表。如果要创建外部表，需要使用关键字 external
在删除内部表时，表的定义(元数据) 和 数据 同时被删除
在删除外部表时，仅删除表的
在生产环境中，多使用外部表
转换：
    alter table t1 set tblproperties('EXTERNAL'='TRUE');
    alter table t1 set tblproperties('EXTERNAL'='FALSE');

分区表：

概述：

Hive在执行查询时，一般会扫描整个表的数据。由于表的数据量大，全表扫描消耗时间长、效率低。
而有时候，查询只需要扫描表中的一部分数据即可，Hive引入了分区表的概念，将表的数据存储在不同的子目录中，每一个子目录对应一个分区。
只查询部分分区数据时，可避免全表扫描，提高查询效率。
在实际中，通常根据时间、地区等信息进行分区。

创建方式：

create table if not exists t3(
    id int
    ,name string
    ,hobby array<string>
    ,addr map<String,string>
)
partitioned by (dt string);
然后导入数据load data local inpath "/home/hadoop/data/t1.dat" into table t3;

导入数据：

单个分区：

load语句：

LOAD data [local] inpath 'dataPath' [overwrite] into table student [partition (partcol1=val1,…)];

alter语句：

ALTER table t3 add partition(dt='2020-06-03') location '/user/hive/warehouse/mydb.db/t3/dt=2020-06-07'

insert语句：

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;

多个分区：

动态分区：

支持插入insert into table_name partition(dt)    select * from table 
支持覆盖insert overwrite table xxxxxx partition(dt) select
注意：
    select中分区字段dt的位置要在最后。

加载hdfs多个分区数据：

set hive.msck.path.validation=ignore;
msck repair table xxx   

分区字段编码要求：

这个中文字段的分区之所以不能建，在日志中提示的很明确就是hive meta store exception,所以就去找hive元数据的问题，经过排查发现，partitions表的par_name字段还是lanten1的编码，将其修改文utf8的编码以后，就可以创建中文分区了。

查看分区：

show partitions table_name;

新增分区并设置数据:

方式一：

alter table t3 add partition(dt='2020-06-03');
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01 /user/hive/warehouse/mydb.db/t3/dt=2020-06-07

方式二：

alter table t3 add partition(dt='2020-06-07') location '/user/hive/warehouse/mydb.db/t3/dt=2020-06-07'

修改分区的hdfs路径：

alter table t3 partition(dt='2020-06-01') set location '/user/hive/warehouse/t3/dt=2020-06-03';

删除分区：

alter table t3 drop partition(dt='2020-06-03'),partition(dt='2020-06-04');

二级分区：

partitioned by (dt string,type string);
动态分区：
    类似一级分区的操作
目录结构：
    /usr/hive/warehouse/dsp_billing/dt=2021-09-13/channel=chuanshanjia

分桶表：

概述：

当单个的分区或者表的数据量过大，分区不能更细粒度的划分数据，就需要使用分桶技术将数据划分成更细的粒度。
将数据按照指定的字段进行分成多个桶中去，即将数据按照字段进行划分，数据按照字段划分到多个文件当中去。
分桶对应不同的文件，分区对应不同的文件夹

原理：

MR中：key.hashCode % reductTask
Hive中：分桶字段.hashCode % 分桶个数

创建：

create table course(
    id int,
    name string,
    score int
)
clustered by (id) into 3 buckets;

加载数据：

从普通表导入数据。
insert into table course select * from course_common;

修改表：

修改表名
    alter table course_common rename to course_common1;
修改列名
    alter table course_common1 change column id cid int;
修改字段类型
    alter table course_common1 change column cid cid string;        //如果不满足类型转换，会报错
增加字段
    alter table course_common1 add columns (common string);
删除字段
    alter table course_common1 replace columns(id string, cname string, score int);

删除表：

drop table course_common1;    

清空表数据：

truncate table tabE;
截断表，清空数据。(注意：仅能操作内部表)

数据操作：

数据导入：

装载数据(Load)：

语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1,partcol2=val2 ...)]
LOCAL：表示从本地文件系统加载数据到Hive表中，否则从HDFS加载数据到Hive表中
INPATH：加载数据的路径
OVERWRITE：覆盖表中已有数据；否则表示追加数据
PARTITION：将数据加载到指定的分区

插入数据(Insert)：

手动插入数据
    insert into table tabC partition(month='202001') values (5, 'wangwu', 'BJ'), (4, 'lishi', 'SH'), (3,'zhangsan', 'TJ');
插入查询的结果数据    
    insert into table tabC partition(month='202002') select id, name, area from tabC where month='202001';
多表（多分区）插入模式
    from tabC insert overwrite table tabC partition(month='202003') select id, name, area where month='202002'

创建表并插入数据(as select)：

create table if not exists tabD as select * from tabC;                        

使用import导入数据：

import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';    

使用SerDe导入JSON格式数据：

SerDe 是Serializer 和 Deserializer 的简写形式。Hive使用Serde进行行对象的序列与反序列化。最后实现把文件内容映射到 hive 表中的字段数据类型。
SerDe包括 Serialize/Deserilize 两个功能：
示例：
    create table jsont2(
    id int,
    ids array<string>,
    total_number int
    )
    ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe';
    load data local inpath '/data/lagoudw/data/json2.dat' into table jsont2;

数据导出：

将查询结果导出到本地：

insert overwrite local directory '/home/hadoop/data/tabC' 
select * from tabC;    

将查询结果格式化输出到本地：

insert overwrite local directory '/home/hadoop/data/tabC2' 
row format delimited fields terminated by ' ' select * from tabC;

将查询结果导出到HDFS：

insert overwrite directory '/user/hadoop/data/tabC3' row format delimited fields terminated by ' ' select * from tabC;

dfs命令导出数据到本地：

本质是执行数据文件的拷贝    
dfs -get /user/hive/warehouse/mydb.db/tabc/month=202001 /home/hadoop/data/tabC4

hive命令导出数据到本地：

执行查询将查询结果重定向到文件    
hive -e "select * from tabC" > a.log

export 导出数据到HDFS：

使用export导出数据时，不仅有数还有表的元数据信息
export table tabC to '/user/hadoop/data/tabC4';
export 导出的数据，可以使用 import 命令导入到 Hive 表中
Hive的数据导入与导出还可以使用其他工具：Sqoop、DataX等；    

DQL命令

select语法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
    FROM table_reference
    [WHERE where_condition]
    [GROUP BY col_list]
    [ORDER BY col_list]
    [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]
    [LIMIT [offset,] rows]

比较运算符：

=、==、<=>
<>、!=
<、<=、>、>=
is [not] null
in (value1,value2, ......)
LIKE
[NOT] BETWEEN... AND ...
RLIKE、REGEXP        基于java的正则表达式，匹配返回TRUE，反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。

表连接：

Hive支持通常的SQL JOIN语句。默认情况下，仅支持等值连接，不支持非等值连接。
连接查询操作分为两大类：内连接和外连接，而外连接可进一步细分为三种类型：
    1. 内连接: [inner] join
    2. 外连接 (outer join)
        - 左外连接。 left [outer] join，左表的数据全部显示
        - 右外连接。 right [outer] join，右表的数据全部显示
        - 全外连接。 full [outer] join，两张表的数据都显示
多表连接：
    连接 n张表，至少需要 n-1 个连接条件。例如：连接四张表，至少需要三个连接条件。  
    Hive总是按照从左到右的顺序执行，Hive会对每对 JOIN 连接对象启动一个MapReduce 任务。   
笛卡尔积：
    满足以下条件将会产生笛卡尔集：
        没有连接条件
        连接条件无效
        所有表中的所有行互相连接    
    如果表A、B分别有M、N条数据，其笛卡尔积的结果将有 M*N 条数据；缺省条件下hive不支持笛卡尔积运算；
        set hive.strict.checks.cartesian.product=false;

排序子句：

全局排序(order by)：

ORDER BY执行全局排序，只有一个reduce；        
排序字段要出现在select子句中。以下语句无法执行（因为select子句中缺少deptno）

每个MR内部排序(sort by)：

对于大规模数据而言order by效率低；
在很多业务场景，我们并不需要全局有序的数据，此时可以使用sort by；
sort by为每个reduce产生一个排序文件，在reduce内部进行排序，得到局部有序的结果；
如何确定分区？
    用distribute by
示例： 
    set mapreduce.job.reduces=2;  这样每个reduce各自有序，输出的文件是各自独立的。
    select * from emp sort by sal desc;

分区排序(distribute by)：

distribute by 将特定的行（作为分区的key）发送到特定的reducer中，便于后继的聚合与排序操作；
distribute by 类似于MR中的分区操作，可以结合sort by操作，使分区数据有序；
distribute by 要写在sort by之前；
与group by对比：都是按key值划分数据 都使用reduce操作 **唯一不同的是**distribute by只是单纯的分散数据，而group by把相同key的数据聚集到一起，后续必须是聚合操作。
示例： 
    set mapreduce.job.reduces=3;
    insert overwrite local directory '/home/hadoop/output/distBy1'
    select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm
        from emp
    distribute by deptno
    sort by salcomm desc;

Cluster By：

当distribute by 与sort by是同一个字段时，可使用cluster by简化语法；cluster by 只能是剩下，不能指定排序规则；
select * from emp distribute by deptno sort by deptno;
select * from emp cluster by deptno;

函数：

查看系统函数：

show functions;
desc function upper;
desc function extended upper;

日期函数：

当前前日期
    select current_date;        
字符串转时间戳：
    select unix_timestamp(string date, string pattern); //建议使用current_timestamp，有没有括号都可以
字符串转时间：
    date_format(from_unixtime(unix_timestamp(dt, 'yyyyMMdd')),'yyyy-MM-dd')
当前时间戳：
    select current_timestamp();     
时间戳转日期    
    select from_unixtime(1505456567);       
    select from_unixtime(1505456567, 'yyyyMMdd');
    select from_unixtime(1505456567, 'yyyy-MM-dd HH:mm:ss');
日期转时间戳
    select unix_timestamp('2019-09-15 14:23:00');
计算时间差
    select datediff('2020-04-18','2019-11-21');
    select datediff('2019-11-21', '2020-04-18');
查询当月第几天
    select dayofmonth(current_date);
计算月末:
    select last_day(current_date);
当月第1天:
    select date_sub(current_date, dayofmonth(current_date)-1)
下个月第1天:
    select add_months(date_sub(current_date,dayofmonth(current_date)-1), 1)
字符串转时间（字符串必须为：yyyy-MM-dd格式）    
    select to_date('2020-01-01');
    select to_date('2020-01-01 12:12:12');
日期、时间戳、字符串类型格式化输出标准时间格式    
    select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss');
    select date_format(current_date(), 'yyyyMMdd');
    select date_format('2020-06-01', 'yyyy-MM-dd HH:mm:ss');
计算emp表中，每个人的工龄    
    select *, round(datediff(current_date, hiredate)/365,1) workingyears from emp;

字符串函数：

转小写。lower
    select lower("HELLO WORLD");    
转大写。upper
    select lower(ename), ename from emp;
求字符串长度。length
    select length(ename), ename from emp;
字符串拼接。 concat / ||
    select empno || " " ||ename idname from emp;
    select concat(empno, " " ,ename) idname from emp;
指定分隔符。concat_ws(separator, [string | array(string)]+)
    SELECT concat_ws('.', 'www', array('lagou', 'com'));
    select concat_ws(" ", ename, job) from emp;
求子串。substr
    SELECT substr('www.lagou.com', 5);
    SELECT substr('www.lagou.com', -5);
    SELECT substr('www.lagou.com', 5, 5);
字符串切分。split，注意 '.' 要转义
    select split("www.lagou.com", "\\.");   

数学函数：

四舍五入。round
    select round(314.15926);
    select round(314.15926, 2);
    select round(314.15926, -2);
向上取整。ceil
    select ceil(3.1415926);
向下取整。floor
    select floor(3.1415926);
其他数学函数包括：绝对值、平方、开方、对数运算、三角运算等    

JSON函数：

get_json_object(string json_string, string path)
    解析json字符串json_string，返回path指定的内容；如果输入的json字符串无效，那么返回NUll；函数每次只能返回一个数据项；
json_tuple(jsonStr, k1, k2, ...)
    一次读取多个key返回

条件函数：

if条件：

格式：

if (boolean testCondition, T valueTrue, T valueFalseOrNull)    

示例：

select sal, if (sal<1500, 1, if (sal < 3000, 2, 3)) from emp;    

应用场景：

多行转多列，然后sum

case条件：

格式：

CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

示例：

select sal, case when sal<=1500 then 1
            when sal<=3000 then 2
            else 3 end sallevel
from emp;
复杂条件用 case when 更直观

场景：

多列转一行

COALESCE条件：

COALESCE(T v1, T v2, ...)。返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
isnull(a)、isnotnull(a)
nvl(T value, T default_value)
nullif(x, y) 相等为空，否则为a

UDTF函数：

概述：

UDTF : User Defined Table-Generating Functions。用户定义表生成函数，一行输入，多行输出。

explode：

概述：

炸裂函数，就是将一行中复杂的 array 或者 map 结构拆分成多行

示例：

select explode(array('A','B','C')) as col;
缺点：单独使用时，不能有额外的列

lateral view：

概述：

lateral view 常与 表生成函数explode结合使用

语法：

FROM baseTable LATERAL VIEW udtf(expression) tableAlias AS columnAlias

示例：

select cola, colc
    from t1
        lateral view explode(colb) t2 as colc;
lateral view json_tuple(json,key1,key2) t1 as id,nums可以将两个column合并为一row。    

range示例：

select dt,date_sub(dt,dt_num) as new_dt from (select dt from yingyongbao_quotation group by dt order by dt desc limit 2) ta lateral view explode(array(1,2,3,4,5,6,7)) t as dt_num

自定义UDF函数：

继承UDF类，重写evaluate方法
    public ArrayList<String> evaluate(String jsonStr, String arrKey)
添加jar
    add jar /data/lagoudw/jars/cn.lagou.dw-1.0-SNAPSHOT-jar-with-dependencies.jar;
创建函数
    create temporary function lagou_json_array as "cn.lagou.dw.hive.udf.ParseJsonArray";
使用函数
    select username, age, sex, lagou_json_array(json, "ids") ids from jsont1;

窗口函数：

概述：

窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数，很多场景都需要用到。
窗口函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
窗口函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变 化而变化。

语法：

window_function (expression) OVER (
   [ PARTITION BY part_list ]
   [ ORDER BY order_list ]
   [ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )

over关键字：

概述：

使用窗口函数之前一般要要通过over()进行开窗
如果over中没有参数，默认的是全部结果集；

示例：

select ename, sal, sum(sal) salsum from emp;  //这样使用是错误的，sum是聚合函数
select ename, sal, sum(sal) over() salsum from emp;

partition by子句：

概述：

在over窗口中进行分区，对某一列进行分区统计，窗口的大小就是分区的大小

示例：

select ename, sal, sum(sal) over(partition by deptno) salsum from emp;

order by 子句：

概述：

order by子句对输入的数据进行排序，同时会从分组的第一行到当前行累计使用聚合函数（count、sum等）

示例：

select ename, sal, deptno, sum(sal) over(partition by deptno order by sal) salsum
from emp;

使用场景：

ORDER BY子句对于诸如row_number()，lead()，lag()等函数是必须的。如果数据无序，这些函数的结果就没有意义。

Window子句：

概述：

如果要对窗口的结果做更细粒度的划分，使用window子句，有如下的几个选项：

unbounded preceding。组内第一行数据
n preceding。组内当前行的前n行数据
current row。当前行数据
n following。组内当前行的后n行数据
unbounded following。组内最后一行数据

语法：

rows：

表示行数范围between ... and ...

range：

表示值范围，逻辑窗口，与当前行的值有关（order by key的key的值）,在key上操作range范围。
注意：
    如果不指定order by，默认会选取窗口内所有行

示例：

select ename, sal, deptno, sum(sal) over(partition by deptno order by ename rows between unbounded preceding and current row )
from emp;
组内，第一行到当前行的和

排名窗口函数：

概述：

都是从1开始，生成数据项在分组中的排名。
ROW_NUMBER()。排名顺序增加不会重复；如1、2、3、4、... ...
RANK()。 排名相等会在名次中留下空位；如1、2、2、4、5、... ...
DENSE_RANK()。 排名相等会在名次中不会留下空位 ；如1、2、2、3、4、... ...

示例：

rank() over (partition by cname order by score desc)

partition by表示分桶，order by表示在桶内排名。

应用：

连续标记问题，某个字段 - row_number得到新的字段。
select distinct team from (
with tmp as (
    select team,year, year - row_number() OVER (PARTITION BY team ORDER BY year) rank
    from t1
    )
select team
from tmp
group by team,rank
having count(*)>=3) tmp1;

序列窗口函数：

lag。返回当前数据行的上一行数据
lead。返回当前数据行的下一行数据
first_value。取分组内排序后，截止到当前行，第一个值
last_value。分组内排序后，截止到当前行，最后一个值
ntile。将分组的数据按照顺序切分成n片，返回当前切片值index。从1开始。
示例：
    lag(pv) over(partition by cid order by ctime) lagpv
场景： 
    波峰波谷问题
    浏览总时长问题

合并函数：

collect_list
    以group by为维度，收集某一列的全部值，返回数组
collect_set
    返回去重后的数组
应用场景：
    1.突破group限制，返回数组第一个元素（原来不可以）
    2.搭配contact_ws，将数组变为字符串

连接函数：

concat(string s1, string s2, ...)
    如果其中某一列为NULL，那么合并结果也为NULL
concat_ws
    制定分隔符将多个字符串连接起来，实现“列转行”
    NULL字段合并对于concat_ws来说无影响

自定义函数：

概述：

当 Hive 提供的内置函数无法满足实际的业务处理需要时，可以考虑使用用户自定义函数进行扩展

分类：

UDF（User Defined Function）。用户自定义函数，一进一出
UDAF（User Defined Aggregation Function）。用户自定义聚集函数，多进一出；类似于：count/max/min
UDTF（User Defined Table-Generating Functions）。用户自定义表生成函数，一进多出；类似于：explode

UDF开发：

继承org.apache.hadoop.hive.ql.exec.UDF
需要实现evaluate函数；evaluate函数支持重载
UDF必须要有返回类型，可以返回null，但是返回类型不能为void

UDF开发步骤：

1.创建maven java 工程，添加依赖

2.开发java类继承UDF，实现evaluate 方法

evaluate(final Text t, final Text x)

3.将项目打包上传服务器

4.添加开发的jar包

add jar /home/1 hadoop/hiveudf.jar;

5.设置函数与自定义函数关联

临时函数：

create temporary function mynvl as "cn.lagou.hive.udf.nvl";

永久函数：

hdfs dfs -put hiveudf.jar jar/
create function mynvl1 as 'cn.lagou.hive.udf.nvl' using jar 'hdfs:/user/hadoop/jar/hiveudf.jar';

6.使用自定义函数（当前数据库）

删除函数：

drop function mynvl1;

DML命令：

概述：

数据操纵语言DML(Data Manipulation Language)，DML主要有三种形式：插入(INSERT)、删除(DELETE)、更新(UPDATE)。
事务具有的四个要素：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），这四个基本要素通常称为ACID特性。

事务：

概述：

Hive从0.14版本开始支持事务 和 行级更新，但缺省是不支持的，需要一些附加的配置。要想支持行级insert、update、delete，需要配置Hive支持事务。

限制：

Hive提供行级别的ACID语义
BEGIN、COMMIT、ROLLBACK 暂时不支持，所有操作自动提交
目前只支持 ORC 的文件格式
默认事务是关闭的，需要设置开启
要是使用事务特性，表必须是分桶的
只能使用内部表
如果一个表用于ACID写入（INSERT、UPDATE、DELETE），必须在表中设置表属性 : "transactional=true"
必须使用事务管理器 org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
目前支持快照级别的隔离。就是当一次数据查询时，会提供一个数据一致性的快照
LOAD DATA语句目前在事务表中暂时不支持，只能insert ... select ... 

支持修改原理：

HDFS是不支持文件的修改；并且当有数据追加到文件，HDFS不对读数据的用户提供一致性的。
为了在HDFS上支持数据的更新：
    表和分区的数据都被存在基本文件中（base files）
    新的记录和更新，删除都存在增量文件中（delta files）
    一个事务操作创建一系列的增量文件
    在读取的时候，将基础文件和修改，删除合并，最后返回给查询

其他实现方式：

insert overwrite，拼接两部分记录

常用操作：

insert into 
update 
delete from 

元数据管理与存储：

Metastore：

概述：

元数据包含用Hive创建的database、table、表的字段等元信息。元数据存储在关系型数据库中。如hive内置的Derby、第三方如MySQL等。
Metastore即元数据服务，是Hive用来管理库表元数据的一个服务。有了它上层的服务不用再跟裸的文件数据打交道，而是可以基于结构化的库表信息构建计算框架。
通过metastore服务将Hive的元数据暴露出去，而不是需要通过对Hive元数据库mysql的访问才能拿到Hive的元数据信息；
metastore服务实际上就是一种thrift服务，通过它用户可以获取到Hive元数据，并且通过thrift获取元数据的方式，屏蔽了数据库访问需要驱动，url，用户名，密码等细节。

三种配置方式：

内嵌模式：

内嵌模式使用的是内嵌的Derby数据库来存储元数据，也不需要额外起Metastore服务。
数据库和Metastore服务都嵌入在主Hive Server进程中。这个是默认的，配置简单，但是一次只能一个客户端连接，适用于用来实验，不适用于生产环境。
优点：配置简单，解压hive安装包 bin/hive 启动即可使用；
缺点：不同路径启动hive，每一个hive拥有一套自己的元数据，无法共享。
schematool -dbType derby -initSchema

本地模式：

本地模式采用外部数据库来存储元数据，目前支持的数据库有：MySQL、Postgres、Oracle、MS SQL Server。教学中实际采用的是MySQL。
本地模式不需要单独起metastore服务，用的是跟Hive在同一个进程里的metastore服务。也就是说当启动一个hive 服务时，其内部会启动一个metastore服务。
Hive根据 hive.metastore.uris 参数值来判断，如果为空，则为本地模式。
缺点：每启动一次hive服务，都内置启动了一个metastore；在hive-site.xml中暴露的数据库的连接信息；
优点：配置较简单，本地模式下hive的配置中指定mysql的相关信息即可

远程模式：

远程模式下，需要单独起metastore服务，然后每个客户端都在配置文件里配置连接到该metastore服务。远程模式的metastore服务和hive运行在不同的进程里。
在生产环境中，建议用远程模式来配置Hive Metastore。在这种模式下，其他依赖hive的软件都可以通过Metastore访问Hive。
此时需要配置hive.metastore.uris 参数来指定 metastore 服务运行的机器ip和端口，并且需要单独手动启动metastore服务。
metastore服务可以配置多个节点上，避免单节点故障导致整个集群的hive client不可用。同时hive client配置多个metastore地址，会自动选择可用节点。
启动： 
    nohup hive --service metastore &
配置： 
    修改hive-site.xml
    <property>
        <name>hive.metastore.uris</name>
        <value>thrift://linux121:9083,thrift://linux123:9083</value>
    </property>

HiveServer2：

概述：

HiveServer2是一个服务端接口，使远程客户端可以执行对Hive的查询并返回结果。目前基于Thrift RPC的实现是HiveServer的改进版本，并支持多客户端并发和身份验证
启动hiveServer2服务后，就可以使用jdbc、odbc、thrift 的方式连接。Thrift是一种接口描述语言和二进制通讯协议，它被用来定义和创建跨语言的服务。
它被当作一个远程过程调用（RPC）框架来使用，是由Facebook为“大规模跨语言服务开发”而开发的。
HS2包括基于Thrift的Hive服务（TCP或HTTP）和用于Web UI 的Jetty Web服务器。

架构：

BeelineCli -> HiveServer2 -> MetaStore

作用：

为Hive提供了一种允许客户端远程访问的服务
基于thrift协议，支持跨平台，跨编程语言对Hive访问
允许远程访问Hive    

搭建：

1、修改集群上的 core-site.xml，增加以下内容：

hadoop.proxyuser.root.hosts
hadoop.proxyuser.root.groups
hadoop.proxyuser.hadoop.hosts
hadoop.proxyuser.hadoop.groups

2、修改 集群上的 hdfs-site.xml，增加以下内容：

dfs.webhdfs.enabled

3、启动linux123上的 HiveServer2 服务

nohup hiveserver2 &
lsof -i:10000
http://linux123:10002/

4、启动 linux122 节点上的 beeline

Beeline：

Beeline是从 Hive 0.11版本引入的，是 Hive 新的命令行客户端工具。
Hive客户端工具后续将使用Beeline 替代 Hive 命令行工具 ，并且后续版本也会废弃掉 Hive 客户端工具。
操作：
    !connect jdbc:hive2://linux123:10000
    即可输入hive命令。
    !connect jdbc:mysql://linux123:3306
    即可输入mysql命令。

HCatalog：

概述：

HCatalog 提供了一个统一的元数据服务，允许不同的工具如 Pig、MapReduce 等通过 HCatalog 直接访问存储在 HDFS 上的底层文件。
HCatalog是用来访问Metastore的Hive子项目，它的存在给了整个Hadoop生态环境一个统一的定义。        

HCatalog 使用了 Hive 的元数据存储，这样就使得像 MapReduce 这样的第三方应用可以直接从 Hive 的数据仓库中读写数据。
同时，HCatalog 还支持用户在MapReduce 程序中只读取需要的表分区和字段，而不需要读取整个表，即提供一种逻辑上的视图来读取数据，而不仅仅是从物理文件的维度。    

HCatalog 提供了一个称为 hcat 的命令行工具。这个工具和 Hive 的命令行工具类似，两者最大的不同就是 hcat 只接受不会产生 MapReduce 任务的命令。

使用：

cd $HIVE_HOME/hcatalog/bin
./hcat -e "create table default.test1(id string, name string,age int)"
./hcat -f createtable.txt

数据存储格式：

概述：

Hive支持的存储数的格式主要有：TEXTFILE（默认格式） 、SEQUENCEFILE、RCFILE、ORCFILE、PARQUET。
textfile为默认格式，建表时没有指定文件格式，则使用TEXTFILE，导入数据时会直接把数据文件拷贝到hdfs上不进行处理；
sequencefile，rcfile，orcfile格式的表不能直接从本地文件导入数据，数据要先导入到textfile格式的表中， 然后再从表中用insert导入sequencefile、rcfile、orcfile表中。

行存储与列存储：

区别：

行式存储下一张表的数据都是放在一起的，但列式存储下数据被分开保存了。

行式存储：

优点：数据被保存在一起，insert和update更加容易
缺点：选择（selection）时即使只涉及某几列，所有数据也都会被读取
常见：TEXTFILE、SEQUENCEFILE

列式存储：

优点：查询时只有涉及到的列会被读取，效率高
缺点：选中的列要重新组装，insert/update比较麻烦
常见：ORC和PARQUET

TextFile：

概述：

Hive默认的数据存储格式，数据不做压缩，磁盘开销大，数据解析开销大。 可结合Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)
但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。

SEQUENCEFILE：

概述：

SequenceFile是Hadoop API提供的一种二进制文件格式，其具有使用方便、可分割、可压缩的特点。 
SequenceFile支持三种压缩选择：none，record，block。Record压缩率低，一般建议使用BLOCK压缩。

RCFile：

概述：

RCFile全称Record Columnar File，列式记录文件，是一种类似于SequenceFile的键值对数据文件。RCFile结合列存储和行存储的优缺点，是基于行列混合存储的RCFile。
RCFile遵循的“先水平划分，再垂直划分”的设计理念。先将数据按行水平划分为行组，这样一行的数据就可以保证存储在同一个集群节点；然后在对行进行垂直划分。

架构：

一张表可以包含多个HDFS block
在每个block中，RCFile以行组为单位存储其中的数据
row group又由三个部分组成
    用于在block中分隔两个row group的16字节的标志区
    存储row group元数据信息的header
    实际数据区，表中的实际数据以列为单位进行存储（一行存的是当前block的所有行的该列数据）

ORCFile：

概述：

ORC File，它的全名是Optimized Row Columnar (ORC) file，其实就是对RCFile做了一些优化，在hive 0.11中引入的存储格式。
这种文件格式可以提供一种高效的方法来存储Hive数据。它的设计目标是来克服Hive其他格式的缺陷。运用ORC File可以提高Hive的读、写以及处理数据的性能。

组成：

ORC文件结构由三部分组成：

文件脚注(file footer)：包含了文件中 stripe 的列表，每个stripe行数，以及每个列的数据类型。还包括每个列的最大、最小值、行计数、求和等信息
postscript：压缩参数和压缩大小相关信息
条带(stripe)：ORC文件存储数据的地方。在默认情况下，一个stripe的大小为250MB
    Index Data：一个轻量级的index，默认是每隔1W行做一个索引。包括该条带的一些统计信息，以及数据在stripe中的位置索引信息
    Rows Data：存放实际的数据。先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个stream来存储Stripe Footer：存放stripe的元数据信息

优点：

ORC在每个文件中提供了3个级别的索引：文件级、条带级、行组级。借助ORC提供的索引信息能加快数据查找和读取效率，规避大部分不满足条件的查询条件的文件和数据块。
使用ORC可以避免磁盘和网络IO的浪费，提升程序效率，提升整个集群的工作负载。

Parquet：

概述：

Apache Parquet是Hadoop生态圈中一种新型列式存储格式，它可以兼容Hadoop生态圈中大多数计算框架(Mapreduce、Spark等)，被多种查询引擎支持（Hive、Impala、Drill等）与语言和平台无关的。
Parquet文件是以二进制方式存储的，不能直接读取的，文件中包括实际数据和元数据，Parquet格式文件是自解析的。

架构：

Row group:

写入数据时的最大缓存单元
MR任务的最小并发单元
一般大小在50MB-1GB之间

Column chunk：

存储当前Row group内的某一列数据
最小的IO并发单元

Page：

压缩、读数据的最小单元
获得单条数据时最小的读取数据单元
大小一般在8KB-1MB之间，越大压缩效率越高

Footer：

数据Schema信息
每个Row group的元信息：偏移量、大小
每个Column chunk的元信息：每个列的编码格式、首页偏移量、首索引页偏移量、个数、大小等信息

读取示例：

stored as parquet

文件存储格式对比：

文件压缩比：

ORC > Parquet > text

执行查询：

orc ≈ parquet > txt

总结：

TextFile文件更多的是作为跳板来使用(即方便将数据转为其他格式)
有update、delete和事务性操作的需求，通常选择ORCFile
没有事务性要求，希望支持Impala、Spark，建议选择Parquet

Hive调优策略：

概述：

影响Hive效率的不仅仅是数据量过大；数据倾斜、数据冗余、job或I/O过多、MapReduce分配不合理等因素都对Hive的效率有影响。
对Hive的调优既包含对HiveQL语句本身的优化，也包含Hive配置项和MR方面的调整。

架构优化：

执行引擎：

概述：

Hive支持多种执行引擎，分别是 MapReduce、Tez、Spark、Flink。可以通过hivesite.xml文件中的hive.execution.engine属性控制。
Tez    
一个构建于YARN之上的支持复杂的DAG（有向无环图）任务的数据处理框架。
由Hontonworks开源，将MapReduce的过程拆分成若干个子过程，同时可以把多个mapreduce任务组合成一个较大的DAG任务，减少了MapReduce之间的文件存储
同时合理组合其子过程从而大幅提升MR作业的性能。
详细文档参考tez.txt

spark:

相关配置：

set spark.master=<Spark Master URL>
set spark.eventLog.enabled=true;
set spark.eventLog.dir=<Spark event log folder (must exist)>
set spark.executor.memory=512m;             
set spark.serializer=org.apache.spark.serializer.KryoSerializer;

设置sparks jars的依赖路径：

<property>
  <name>spark.yarn.jars</name>
  <value>hdfs://xxxx:8020/spark-jars/*</value>        //存放$SPARK_HOME/jars所有的
</property>

对比tez：

spark与tez都是以dag方式处理数据。
tez能够及时的释放资源，重用container，节省调度时间，对内存的资源要求率不高； 而spark如果存在迭代计算时，container一直占用资源；（动态资源分配可以释放）
实验：
    tez为每个cli维护一个session（启动cli时yarn创建一个AM，quit时退出），执行比较快，
    而spark需要启动，多了启动时间。Starting Spark Job

    如果是superset模式，tez的session需要创建启动，但也比spark快很多（15s-1min）。

优化器：

概述：

与关系型数据库类似，Hive会在真正执行计算之前，生成和优化逻辑执行计划与物理执行计划。
Hive有两种优化器：Vectorize(向量化优化器) 与 Cost-Based Optimization (CBO 成本优化器)。
矢量化查询执行    
矢量化查询(要求执行引擎为Tez)执行通过一次批量执行1024行而不是每行一行来提高扫描，聚合，过滤器和连接等操作的性能，这个功能一显着缩短查询执行时间。
要使用矢量化查询执行，必须用ORC格式存储数据
配置：
    set hive.vectorized.execution.enabled = true;
    -- 默认 false
    set hive.vectorized.execution.reduce.enabled = true;
    -- 默认 false
成本优化器    
Hive的CBO是基于apache Calcite的，Hive的CBO通过查询成本(有analyze收集的统计信息)会生成有效率的执行计划，最终会减少执行的时间和资源的利用
使用CBO的配置如下：
    SET hive.cbo.enable=true; --从 v0.14.0默认true
    SET hive.compute.query.using.stats=true; -- 默认false
    SET hive.stats.fetch.column.stats=true; -- 默认false
    SET hive.stats.fetch.partition.stats=true; -- 默认true
要求： 
    定期执行表（analyze）的分析，分析后的数据放在元数据库中。

分区表：

对于一张比较大的表，将其设计成分区表可以提升查询的性能，对于一个特定分区的查询，只会加载对应分区路径的文件数据，所以执行速度会比较快。
分区字段的选择是影响查询性能的重要因素，尽量避免层级较深的分区，这样会造成太多的子文件夹。一些常见的分区字段可以是：
    日期或时间。如year、month、day或者hour，当表中存在时间或者日期字段时
    地理位置。如国家、省份、城市等
    业务逻辑。如部门、销售区域、客户等等

分桶表：

与分区表类似，分桶表的组织方式是将HDFS上的文件分割成多个文件。
分桶可以加快数据采样，也可以提升join的性能(join的字段必须是分桶字段)，因为分桶可以确保某个key对应的数据在一个特定的桶内(文件)，巧妙地选择分桶字段可以大幅度提升join的性能。
通常情况下，分桶字段可以选择经常用在过滤操作或者join操作的字段。

文件格式：

存储格式一般需要根据业务进行选择，生产环境中绝大多数表都采用TextFile、ORC、Parquet存储格式之一。

数据压缩：

压缩技术可以减少map与reduce之间的数据传输，从而可以提升查询性能，关于压缩的配置可以在hive的命令行中或者hive-site.xml文件中进行配置。
    -- 中间结果压缩
    SET hive.exec.compress.intermediate=true
    SET hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec ;
    -- 输出结果压缩
    SET hive.exec.compress.output=true;
    SET mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodc

参数优化：

本地模式：

当Hive处理的数据量较小时，启动分布式去处理数据会有点浪费，因为可能启动的时间比数据处理的时间还要长。
Hive支持将作业动态地转为本地模式，需要使用下面的配置：
    SET hive.exec.mode.local.auto=true; -- 默认 false
    SET hive.exec.mode.local.auto.inputbytes.max=50000000;
    SET hive.exec.mode.local.auto.input.files.max=5; -- 默认 4

严格模式：

所谓严格模式，就是强制不允许用户执行3种有风险的HiveQL语句，一旦执行会直接失败。
这3种语句是：
    查询分区表时不限定分区列的语句；
    两表join产生了笛卡尔积的语句；
    用order by来排序，但没有指定limit的语句。
配置： 
    要开启严格模式，需要将参数hive.mapred.mode 设为strict(缺省值)。

JVM重用：

概述：

默认情况下，Hadoop会为为一个map或者reduce启动一个JVM，这样可以并行执行map和reduce。
当map或者reduce是那种仅运行几秒钟的轻量级作业时，JVM启动进程所耗费的时间会比作业执行的时间还要长。
Hadoop可以重用JVM，通过共享JVM以串行而非并行的方式运行map或者reduce。

场景：

JVM的重用适用于同一个作业的map和reduce，对于不同作业的task不能够共享JVM。

配置：

如果要开启JVM重用，需要配置一个作业最大task数量，默认值为1，如果设置为-1，则表示不限制
    SET mapreduce.job.jvm.numtasks=5;   # 代表同一个MR job中顺序执行的5个task重复使用一个JVM，减少启动和关闭的开销
缺点
开启JVM重用将一直占用使用到的task插槽，以便进行重用，直到任务完成后才能释放。
如果某个“不平衡的”job中有某几个reduce task执行的时间要比其他Reduce task消耗的时间多的多的话，那么保留的插槽就会一直空闲着却无法被其他的job使用
直到所有的task都结束了才会释放。    

并行执行：

Hive的查询通常会被转换成一系列的stage，这些stage之间并不是一直相互依赖的，可以并行执行这些stage，通过下面的方式进行配置：

SET hive.exec.parallel=true; -- 默认false
SET hive.exec.parallel.thread.number=16; -- 默认8
并行执行可以增加集群资源的利用率，如果集群的资源使用率已经很高了，那么并行执行的效果不会很明显。

推测执行：

在分布式集群环境下，因为程序Bug、负载不均衡、资源分布不均等原因，会造成同一个作业的多个任务之间运行速度不一致，有些任务的运行速度可能明显慢于其他任务
（比如一个作业的某个任务进度只有50%，而其他所有任务已经运行完毕），则这些任务会拖慢作业的整体执行进度。
为了避免这种情况发生，Hadoop采用了推测执行机制，它根据一定的规则推测出“拖后腿”的任务，并为这样的任务启动一个备份任务，让该任务与原始任务同时处理同一份数据，
并最终选用最先成功运行完成任务的计算结果作为最终结果。
    set mapreduce.map.speculative=true
    set mapreduce.reduce.speculative=true
    set hive.mapred.reduce.tasks.speculative.execution=true

合并小文件：

在map执行前合并小文件，减少map数
    set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;      # 缺省参数
在Map-Reduce的任务结束时合并小文件
    # 在 map-only 任务结束时合并小文件，默认true
        SET hive.merge.mapfiles = true;
    # 在 map-reduce 任务结束时合并小文件，默认false
        SET hive.merge.mapredfiles = true;
    # 合并文件的大小，默认256M
        SET hive.merge.size.per.task = 268435456;
    # 当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件merge
        SET hive.merge.smallfiles.avgsize = 16777216;

Fetch模式：

Fetch模式是指Hive中对某些情况的查询可以不必使用MapReduce计算。select col1, col2 from tab ;
可以简单地读取表对应的存储目录下的文件，然后输出查询结果到控制台。在开启fetch模式之后，在全局查找、字段查找、limit查找等都不启动 MapReduce 。
    hive.fetch.task.conversion=more  # 默认

SQL优化：

列裁剪和分区裁剪：

列裁剪是在查询时只读取需要的列；分区裁剪就是只读取需要的分区。
简单的说：select 中不要有多余的列，坚决避免 select * from tab;查询分区表，不读多余的数据；

sort by 代替 order by：

HiveQL中的order by与其他关系数据库SQL中的功能一样，是将结果按某字段全局排序，这会导致所有map端数据都进入一个reducer中，在数据量大时可能会长时间计算不完。
如果使用sort by，那么还是会视情况启动多个reducer进行排序，并且保证每个reducer内局部有序。为了控制map端数据分配到reducer的key，往往还要配合distribute by 一同使用。
如果不加 distribute by 的话，map端数据就会随机分配到reducer。

group by 代替 count(distinct)：

当要统计某一列的去重数时，如果数据量很大，count(distinct) 会非常慢。原因与order by类似，count(distinct)逻辑只会有很少的reducer来处理。此时可以用group by 来改写
这样写会启动两个MR job（单纯distinct只会启动一个），所以要确保数据量大到启动job的overhead远小于计算耗时，才考虑这种方法。
当数据集很小或者key的倾斜比较明显时，group by还可能会比distinct慢

group by 配置调整 ：

map端预聚合：

group by时，如果先起一个combiner在map端做部分预聚合，可以有效减少shuffle数据量。
    set hive.map.aggr = true        -- 默认为true
Map端进行聚合操作的条目数
    set hive.groupby.mapaggr.checkinterval = 100000
    通过hive.groupby.mapaggr.checkinterval 参数也可以设置map端预聚合的行数阈值，超过该值就会分拆job，默认值10W。

倾斜均衡配置项：

group by时如果某些key对应的数据量过大，就会发生数据倾斜。Hive自带了一个均衡数据倾斜的配置项hive.groupby.skewindata ，默认值false。
其实现方法是在group by时启动两个MR job。第一个job会将map端数据随机输入reducer，每个reducer做部分聚合，相同的key就会分布在不同的reducer中。
第二个job再将前面预处理过的数据按key聚合并输出结果，这样就起到了均衡的效果。
在join过程中Hive会将计数超过阈值hive.skewjoin.key （默认100000）的倾斜key对应的行临时写进文件中，然后再启动另一个job做map join生成结果。
通过hive.skewjoin.mapjoin.map.tasks 参数还可以控制第二个job的mapper数量，默认10000。
但是，配置项毕竟是死的，单纯靠它有时不能根本上解决问题，建议了解数据倾斜的细节，并优化查询语句。

join 基础优化：

Hive join的三种方式：

1、common join

普通连接，在SQL中不特殊指定连接方式使用的都是这种普通连接。
缺点：性能较差(要将数据分区，有shuffle)
优点：操作简单，普适性强    

2、map join

map端连接，与普通连接的区别是这个连接中不会有reduce阶段存在，连接在map端完成
适用场景：
    大表与小表连接，小表数据量应该能够完全加载到内存，否则不适用优点：在大小表连接时性能提升明显，
备注：
    Hive 0.6 的时候默认认为写在select 后面的是大表，前面的是小表， 或者使用 /*+mapjoin(map_table) / 提示进行手动设定。
        select a., b.* from a join b on a.id =b.id【要求小表在前，大表之后】
    hive 0.7 的时候这个计算是自动化的，它首先会自动判断哪个是小表，哪个是大表，这个参数由（hive.auto.convert.join=true）来控制，然后控制小表的大小由
        （hive.smalltable.filesize=25000000）参数控制（默认是25M），当小表超过这个大小，hive 会默认转化成common join。
    Hive 0.8.1，hive.smalltable.filesize => hive.mapjoin.smalltable.filesize
缺点：
    使用范围较小，只针对大小表且小表能完全加载到内存中的情况。

3、bucket map join

分桶连接：Hive 建表的时候支持hash 分区通过指定clustered by (col_name,xxx ) into number_buckets buckets 关键字.
当连接的两个表的join key 就是bucket column 的时候，就可以通过设置hive.optimize.bucketmapjoin= true 来执行优化。
原理：
    通过两个表分桶在执行连接时会将小表的每个分桶映射成hash表，每个task节点都需要这个小表的所有hash表，
    但是在执行时只需要加载该task所持有大表分桶对应的小表部分的hash表就可以，所以对内存的要求是能够加载小表中最大的hash块即可。
注意点：
    小表与大表的分桶数量需要是倍数关系，这个是因为分桶策略决定的，分桶时会根据分桶字段对桶数取余后决定哪个桶的，所以要保证成倍数关系。
优点：
    比map join对内存的要求降低，能在逐行对比时减少数据计算量（不用比对小表全量）
缺点：
    只适用于分桶表

利用map join特性：

map join特别适合大小表join的情况。Hive会将build table和probe table在map端直接完成join过程，消灭了reduce，效率很高。

分桶表map join：

map join对分桶表还有特别的优化。由于分桶表是基于一列进行hash存储的，因此非常适合抽样（按桶或按块抽样）。

处理空值或无意义值：

日志类数据中往往会有一些项没有记录到，其值为null，或者空字符串、-1等。如果缺失的项很多，在做join时这些空值就会非常集中，拖累进度【备注：这个字段是连接字段】。
若不需要空值数据，就提前写 where 语句过滤掉。需要保留的话，将空值key用随机方式打散，例如将用户ID为null的记录随机改为负值
    case when uid is null then cast(rand()*-10240 as int

单独处理倾斜key：

如果倾斜的 key 有实际的意义，一般来讲倾斜的key都很少，此时可以将它们单独抽取出来，对应的行单独存入临时表中，然后打上一个较小的随机数前缀（比如0~9），最后再进行聚合。
解决方案：
    1.大小表Join，小的维度表（1000条以下的记录条数） 先进内存。
    2.hive.groupby.skewindata=true

join数量：

不要一个Select语句中，写太多的Join。一定要了解业务，了解数据。(A0-A9)分成多条语句，分步执行；(A0-A4; A5-A9)；先执行大表与小表的关联；

调整 Map 数：

通常情况下，作业会通过输入数据的目录产生一个或者多个map任务。
主要因素包括：
    输入文件总数
    输入文件大小
    HDFS文件块大小
如果一个任务有很多小文件（<< 128M），每个小文件也会被当做一个数据块，用一个 Map Task 来完成。
    对于小文件采用的策略是合并。
一个 Map Task 启动和初始化时间 >> 处理时间，会造成资源浪费，而且系统中同时可用的map数是有限的。
有一个125M的文件，一般情况下会用一个Map Task完成。假设这个文件字段很少，但记录数却非常多。如果Map处理的逻辑比较复杂，用一个map任务去做，性能也不好。    
    对于复杂文件采用的策略是增加 Map 数。
    默认计算公式：computeSliteSize(max(minSize, min(maxSize, blocksize))) = blocksize
        minSize : mapred.min.split.size （默认值1）
        maxSize : mapred.max.split.size （默认值256M）
        调整maxSize最大值。让maxSize最大值低于blocksize就可以增加map的个数。建议用set的方式，针对SQL语句进行调整。
oom:
    如果调整mapred.max.split.size失败，可能不适合hbase等数据源，可以尝试hive.tez.container.size增大内存来避免OOM

注意：
    这个不是并行度设置。并行度只能靠yarn多个队列来实现，yarn没有相关配置可以直接限制一个AM申请的container数量。
    yarn只能限制单个container的资源。
    2.7版本新出现的可能有用的参数：(可能需要重启才生效，目前不重启没生效)
        mapreduce.job.running.map.limit
        mapreduce.job.running.reduce.limit

调整 Reduce 数：

reducer数量的确定方法比mapper简单得多。使用参数mapred.reduce.tasks 可以直接设定reducer数量。
如果未设置该参数，Hive会进行自行推测，逻辑如下：
    参数hive.exec.reducers.bytes.per.reducer 用来设定每个reducer能够处理的最大数据量，默认值256M  
    参数hive.exec.reducers.max 用来设定每个job的最大reducer数量，默认值999（1.2版本之前）或1009（1.2版本之后）
    得出reducer数： reducer_num = MIN(total_input_size / reducers.bytes.per.reducer, reducers.max)
reducer数量与输出文件的数量相关。如果reducer数太多，会产生大量小文件，对HDFS造成压力。如果reducer数太少，每个reducer要处理很多数据，容易拖慢运行时间或者造成OOM。    

执行计划：

概念：

能帮助我们了解 Hive 如何将查询转化成 Mapreduce 任务。
Hive的执行计划不是最终真实的执行计划，但是对了解其中的细节仍然有帮助

语法：

EXPLAIN ...

分析：

一条 Hive SQL 语句会包含一个或多个Stage，不同的 Stage 间会存在着依赖关系。
一个Stage可以是：Mapreduce任务（最耗费资源）、Move Operator（数据移动）、Stats-Aggr Operator（搜集统计数据）、Fetch Operator（读取数据）等；
默认情况下，Hive一次只执行一个stage（可以设置并发）。

执行计划关键词信息说明：

Map Reduce：表示当前任务所用的计算引擎是 MapReduce
Map Operator Tree：表示当前描述的 Map 阶段执行的操作信息
Reduce Operator Tree：表示当前描述的 Reduce 阶段执行的操作信息    

Map/Reduce Operator Tree 关键信息说明：

TableScan：表示对关键字 alias 声明的结果集进行扫描
Statistics：表示当前 Stage 的统计信息，这个信息通常是预估值
Filter Operator：表示在数据集上进行过滤
predicate：表示在 Filter Operator 进行过滤时，所用的谓词
Select Operator：表示对结果集上对列进行投影，即筛选列
expressions：表示需要投影的列，即筛选的列
outputColumnNames：表示输出的列名
Group By Operator：表示在结果集上分组聚合
aggregations：表示分组聚合使用的算法
keys：分组的列
Reduce Output Operator：表示当前描述的是对之前结果聚合后的信息
key expressions / value expressions：Map阶段输出key、value所用的数据列
sort order：是否进行排序，+ 正序，- 倒序
Map-reduce partition columns：Map 阶段输出到 Reduce 阶段的分区列
compressed：文件输出的结果是否进行压缩
input format / output format：输入输出的文件类型
serde：数据序列化、反序列化的方式    

stage中map和reduce的数量：

决定map task、reduce task的因素比较多，包括文件格式、文件大小（关键因素）、文件数量、参数设置等。下面是两个重要参数：

mapred.max.split.size=256000000
hive.exec.reducers.bytes.per.reducer=256000000

执行顺序：

Map阶段：

1.执行from加载，进行表的查找与加载

2.执行where过滤，进行条件过滤与筛选

3.执行select查询：进行输出项的筛选

4.执行group by分组：描述了分组后需要计算的函数

5.map端文件合并：map端本地溢出写文件的合并操作，每个map最终形成一个临时文件。然后按列映射到对应的reduceReduce阶段：

Reduce阶段：

1.group by：对map端发送过来的数据进行分组并进行计算。
2.select：最后过滤列用于输出结果
3.limit排序后进行结果输出到HDFS文件

优化思路：

1.减少Map、Reduce Task 数

设置合理的Map、Reduce个

2.减少Stage

使用Hive多表插入语句。可以在同一个查询中使用多个 insert 子句，这样的好处是只需要扫描一次源表就可以生成多个不相交的输出。
from tab1        //从同一张表选取数据，可以将选取的数据插入其他不同的表中（也可以是相同的表）
insert overwrite table tab2 partition (age)
select name,address,school,age
insert overwrite table tab3
select name,address
where age>24;    

Hive Jdbc：

nginx代理转发：

注意：不是http转发，而是rpc转发
    yum install nginx-mod-stream
配置stream转发：
    stream {
        upstream beeline {
             server 192.168.201.8:7001;
        }
        server {
            listen 8686;
            proxy_pass beeline;
        }
    }

springboot：

配置文件application-prod.properties：

配置类：

@Configuration
public class HiveJdbcConfig {
    @Autowired
    private Environment env;

    @Bean(name = "hiveJdbcDataSource")
    @Qualifier("hiveJdbcDataSource")
    public DataSource dataSource() {
        DataSource dataSource = new DataSource();
        dataSource.setUrl(env.getProperty("hive.url"));
        dataSource.setDriverClassName(env.getProperty("hive.driver-class-name"));
        dataSource.setUsername(env.getProperty("hive.username"));
        dataSource.setPassword(env.getProperty("hive.password"));
        return dataSource;
    }

    @Bean(name = "hiveJdbcTemplate")
    public JdbcTemplate hiveJdbcTemplate(@Qualifier("hiveJdbcDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

使用：

    @Autowired
    @Qualifier("hiveJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

Hive同步hbase表：

概述：

当hbase的数据发生变化时，hive也同步更新。

创建：

CREATE  TABLE stage.hbase_news_company_content(            //非外部表的话，需要hbase该表名不存在。
    key string comment "流水号",
    news_id string comment "新闻id",
    news_content string comment "文章内容")   
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'   
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:news_id,cf1:news_content")   //分别对应hive里面的key，new_id,new_content
TBLPROPERTIES("hbase.table.name" = "news_company_content");

原理：

HBaseStorageHandler通过MapReduce读取HBase表数据，具体实现是在MR中，使用HiveHBaseTableInputFormat完成对HBase表的切分，获取RecordReader对象来读取数据。

查询：

只能select，不能count。
性能：
    hive查询时占满集群。

修改：

hive修改，会提示FAILED: SemanticException [Error 10134]: ALTER TABLE cannot be used for a non-native table lagou
hbase修改
    可以新增column成功，但hive字段在创建时已经制定，无法查到新的字段。
    可以删除某个column family，但hive查询会报错。

关于存储路径：

hive desc显示的路径不对，为空路径
真正hbase存储数据的路径为/配置前缀/data/Namespace/MyTable1

删除：

删除hbase的table后，hive再去查询会报错
Failed with exception java.io.IOException:org.apache.hadoop.hbase.TableNotFoundException: Table 'lagou' was not found, got: kylin_metadata.

生产环境问题：

1. 几个触发safety reasons的条件和解决方法：

Cartesian products，解决方法：set hive.mapred.mode=nostrict;
order没有limit，解决方法：limit 9999
表没有指定partition filter，解决方法：where dt>='1971-01-01'