Hive 教程

一、hive概述

二、Hive工作原理

三、在mysql中创建hive用的数据库和hive用户

# 启动mysql
mysql：systemctl start mysqld.service
# 关闭mysql：systemctl stop mysqld.service

--登录mysql
mysql -uroot -p'12345678'                            
set global validate_password_policy=0;
--创建hive用户
CREATE USER 'hive'@'%' IDENTIFIED BY '12345678';   
--在mysql中创建hive_meta数据库
create database hive_meta default charset utf8 collate utf8_general_ci;
--给hive用户增加hive_meta数据库权限
grant all privileges on hive_meta.* to 'hive'@'%' identified by '12345678';
--更新
flush privileges; 

四、hive的三种模式

Hive 主要由三个模块组成：用户接口模块、驱动模块以及元数据存储模块。

1、使用内置的derby数据库做元数据的存储

使用内置的derby数据库做元数据的存储，操作derby数据库做元数据的管理，使用derby存储方式时，运行hive会在当前目录生成一个derby文件和一个metastore_db目录。这种存储方式的弊端是在同一个目录下同时只能有一个hive客户端能使用数据库，目录不同时元数据也无法共享，不适合生产环境只适合练习

2、本地模式

使用mysql做元数据的存储，操作mysql数据库做元数据的管理，可以多个hive client一起使用，并且可以共享元数据。
mysql的连接信息明文存储在客户端配置，不便于数据库连接信息保密和以后对元数据库进行更改
客户端太多也会对mysql造成较大的压力，因为每个客户端都自己发起连接

3、远程模式

使用mysql做元数据的存储，使用metastore服务做元数据的管理，client->MetaStore Server（thrift方式）->Mysql
优点便于元数据库信息的保密，因为只需要在运行metastore的机器上配置元数据库连接信息，客户端只需要配置metastore连接信息即可
缺点会引发单点问题，例如metastore服务挂了，其它hive终端就获取不到元数据信息了。企业环境推荐使用第此种模式

4、本地模式和远程模式的区别：

本地模式不需要单独起metastore服务，用的是跟hive在同一个进程里的metastore服务。
远程模式需要单独起metastore服务，然后每个客户端都在配置文件里配置连接到该metastore服务。远程模式的metastore服务和hive运行在不同的进程里
服务端：Metastore服务所在的机器（安装metastore的机器）

五、启动hive

nohup hive --service metastore > /dev/null 2>&1 &（启动metastore）
hive(启动hive客户端)

六、常用操作

#一次使用命令hive -e 
hive -e 'use manager;select * from yingxiong;'(-S可以省略OK和time taken)
hive -f可以执行hive脚本
hive -f f2（f2的内容是：use manager;select * from yingxiong;）

#在hive中使用hadoop命令：
dfs -ls /

#查看所有的数据库（有s）
show databases;

#生成创建数据库的脚本
show create database hainiu;

#创建数据库
create database hainiu;

#查看数据库创建信息
show create database hainiu;

#切换当前数据库
use hainiu;

#查看当前数据库名称
select current_database();

#设置显示客户端数据库
/usr/local/hive/conf/hive-site.xml中设置为true可以显示hive (hainiu)> 
#是临时的，退出后就没有了
set hive.cli.print.current.db=true（或false）
 	<property> 
          <name>hive.cli.print.current.db</name>
          <value>true</value>
   	</property>

#查看当前数据库所有的表
show tables;

show tables 'person1.'; #点代表一个字符
show tables 'person1*'; #*代表多个字符
show create table tableName; #查看建表信息
describe extended tablename; #查看table信息

# 创建简单表
create table tb1 (name string,age int);

# 创建指定格式的表
CREATE TABLE `user_info`
	(  `id` int,   `name` string)
ROW FORMAT SERDE   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT   'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION  'hdfs://ns1/hive/warehouse/hainiu.db/user_info'
TBLPROPERTIES (  'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}',   'numFiles'='4',   'numRows'='2',   'rawDataSize'='39',   'totalSize'='136',   'transient_lastDdlTime'='1527601686')

#忽略异常
if not exist; 

#复制表结构创建表（可以是外部表或者内部表），LOCATION 不同，其余脚本内容相同
create table test_02 like test;

#删除表
drop table dual;

七、hive的数据类型

基本数据类型
数据类型 大小 范围 示例
TINYINT 1byte -128 ~ 127 100Y
SMALLINT 2byte -32,768 ~ 32,767 100S
INT/INTEGER 4byte -2,147,483,648 ~ 2,147,483,647 100
BIGINT 8byte -9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807 100L
FLOAT 4byte 单精度浮点数 3.1415926
DOUBLE 8byte 双精度浮点数 3.1415926
DECIMAL - 高精度浮点数 DECIMAL(9,8)
BOOLEAN - 布尔型，TRUE/FALSE true
BINARY - 二进制类型（字节数组） -
string 字符序列，可以指定字符集，可以使用单引号或者双引号,尾部空格影响比较 ‘now is time’,“for all good”
varchar 1-65535，超出部分会被截断，尾部的空格也会作为字符串的一部分，影响字符串的比较
char char是固定长度的，最大长度255，而且尾部的空格不影响字符串的比较
timestamp 整数，浮点数或者字符串java.sql.Timestamp时间格式 2018-5-30 19:47:19.99912345678.222（新世纪元年毫秒数）纳米级
DATE
集合数据类型（大多数关系型数据库不支持）

建表 create table persons7 (name string,salary float,xiashu array,deduction map<string , float>,address structcity:string,shi:string,jvli:int)row format delimited fields terminated by ','collection items terminated by '-'map items terminated by ‘:’; 1、设置列之间的分隔符为逗号(放在前面)row format delimited fields terminated by ','2、设置集合之间元素的分隔符为横杠collection items terminated by '-'3、设置map的key(value)名称和key(value)值之间的分隔符为分号map items terminated by ‘:’
上传文件到表下 hadoop fs -put -f f1 /hive/warehouse/hainiu.db/persons7/ 王蓉,9500.50,王大毛-王二毛-王三毛-王四毛-王小毛,国税:300.3-地税:100.2,河北-烟台-300李青,1200.55,李大刚-李二刚-李三刚,国税:500.3-地税:200.2,天津-烟台-300
查询 王蓉,9500.50,王大毛-王二毛-王三毛-王四毛-王小毛,国税:300.3-地税:100.2,河北-烟台-300李青,1200.55,李大刚-李二刚-李三刚,国税:500.3-地税:200.2,天津-烟台-300
建表 create table person11 (id int,name string,salary float,xiashu array,deduction map<string , double>,address structcity:string,shi:string,jvli:int)row format delimited fields terminated by ','collection items terminated by '-'MAP KEYS TERMINATED BY ‘:’;
insert插入 insert into table person11selectid,‘张三’,12.12f,array (‘王大毛’,‘王小毛’) as xiashu,map (‘张三’,15.5,‘李四’,78.2) as deduction,NAMED_STRUCT(‘city’,‘山东’,‘shi’,‘烟台’,‘jvli’,100)from dual;
查询 4 张三 12.12 [“王大毛”,“王小毛”] {“张三”:15.5,“李四”:78.2} {“city”:“山东”,“shi”:“烟台”,“jvli”:100}2 张三 12.12 [“王大毛”,“王小毛”] {“张三”:15.5,“李四”:78.2} {“city”:“山东”,“shi”:“烟台”,“jvli”:100}

八、创建表

1、语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
  [(col_name data_type [COMMENT col_comment], ...)] 
  [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] 
  [ROW FORMAT row_format] 
  [STORED AS file_format] 
  [LOCATION hdfs_path]

2、说明

comment 可以为表与字段增加描述（和sql建表描述一样）,同MySQL
create table tb5(name string) comment ‘miao shu’;

partitioned by --创建分区表，分区就是特殊的列，有文件专门的存储，分区表的文件必须都在分区目录下（不存在就会创建目录）partitioned by (teacher string)

分区操作

#给分区表添加分区文件
alter table person55 add IF not exists partition (teacher='wangdachui')
location '/hive/warehouse/hainiu/externa55/wangdachui';

#添加多个分区
alter table person55 add if not exists partition(teacher='wangdachui10')
location 'wangdachui10' partition (teacher='wangdachui1') 
location '/hive/warehouse/hainiu/externa55/wangdachui1' partition (teacher='wangdachui2') 
location '/hive/warehouse/hainiu/externa55/wangdachui2';

#查看表分区
show partitions tableName;

#删除分区（内部表删除HDFS目录，外部表不删除）
alter table person55 drop if exists partition(teacher='wangdachui2');

分桶

#分桶表
clustered by(age) into 10 buckets

分隔符

#指定列的分隔符char1，集合的分隔符char2，map的分隔符char3，行之间的分隔符是char4。
row format delimited 
	[FIELDS TERMINATED BY char1] 
	[COLLECTION ITEMS TERMINATED BY char2] 
	[MAP KEYS TERMINATED BY char3] 
	[LINES TERMINATED BY char4]

5、序列化

SerDe serde_name 
	[WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] 
# SerDe是Serialize/Deserilize的简称，serde_name 是序列化的名称（指定读取什么类型的文件(此文件可以是hadoop内置的，也可以是自定义的类)）；

#指定自定义的属性key和value
SERDEPROPERTIES

#存储类型
stored as
sequencefile --包含键值对的二进制的文件存储格式，支持压缩，可以节省存储空间;对应的是org.apache.hadoop.mapred.SequenceFileInputFormat和org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputForma
textFile --最普通的文件存储格式，内容是可以直接查看;对应的是org.apache.hadoop.mapred.TextInputFormat和org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat。
avro--带有schema文件格式的，任何其他表都可以转成avro表（进行增加字段），avro也可以转成任何其他表，适合需求的前期字段不是很确定的时候；但是有冗余的数据，会使数据的文件变的很大，新增的字段必须给默认值
ORC --RC的优化；有索引，文件小。读取数据时，根据FileFooter（该表的统计结果和每个Stripe的位置信息）读出Stripe（一组行）的信息，每个Stripe都包含index data、row data以及stripe footer，Index data提供了偏移量，可以跳到正确的压缩块位置，根据IndexData读出数据的偏移量跳到正确的压缩块位置读取数据。
RCFile --是列式存储文件格式，适合压缩处理;对应的是org.apache.hadoop.hive.ql.io.RCFileInputFormat和org.apache.hadoop.hive.ql.io.RCFileOutputFormat。
inputFormat

location

HDFS上的存储位置，如：'hdfs://ns1/hive/warehouse/hainiu/externa2'
tblproperties --配置属性
表的分类
临时表 CREATE TEMPORARY：临时表是session内可见；
内部表 CREATE：不指定类型默认为内部表，内部表的特点是删除表时会同时删除表数据
外部表 CREATE EXTERNAL：创建表时指定数据目录，删除表时不会删除表数据

九、创建简单表

create table stu (name string , age int);
show create table stu;查询建表脚本
CREATE TABLE `stu`(
  `name` string, 
  `age` int)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://ns1/hive/warehouse/hainiu.db/stu'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}', 
  'numFiles'='0', 
  'numRows'='0', 
  'rawDataSize'='0', 
  'totalSize'='0', 
  'transient_lastDdlTime'='1527697070')

十、创建表

#默认路径在数据库目录下,external 是外部表
create external table person27 (name string) location '/hive/warehouse/';

十二、视图

视图只能查询，不能Load/Insert/Update/Delete数据； 
alter view 和重建效果一致

十三、加载数据 load data [local] inpath

#不会对数据做任何处理，也不会分区
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

load data inpath '/user/zhangjian/f2' into table yingxiong partition(type='adc');
load data local inpath '/home/zhangjian/temp/f1' into table yingxiong partition (type='adc');
# local 是cp（复制）
# hdfs 是mv（移动）

三种导入数据的方式

1.load、
2.insert 会创建元数据，
3.mkdir + put + create + add partition

十四、加载数据 select overwrite table

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

十五、动态分区（创建时和分区表相同，select 插入时使用partition自动分区）

set hive.exec.dynamic.partition=true;  #开启动态分区
set hive.exec.dynamic.partition.mode=nonstrict; #默认是strict,即第一个必须是静态分区；nonstrict代表可以没有静态分区
set hive.exec.max.dynamic.partitions.pernode=10000;  #表示每个节点生成动态分区的最大个数，默认是100
set hive.exec.max.dynamic.partitions=100000;  #表示一个DML操作可以创建的最大动态分区数，默认是1000
set hive.exec.max.created.files=150000;  #表示一个DML操作可以创建的最大文件数，默认是100000

#插入或覆盖到动态分区,最后一列是分区列
insert into table yingxiong1 partition(type) select * from yingxiong where type='zhongdan';

十六、导出数据insert overwrite local directory

#1、语法格式
INSERT OVERWRITE [LOCAL] DIRECTORY directory1  [ROW FORMAT row_format] [STORED AS file_format]  
SELECT ... FROM ...;

#2、导出到HDFS中，没有目录会创建
insert overwrite directory 'hdfs://ns1/user/zhangjian/text1/yingxiong_out'
select id,name,age from yingxiong;

#3、导出到local中，没有目录会创建
insert overwrite local directory '/home/zhangjian/temp/yingxiong' stored as avro select id,name,age from yingxiong;

十七、导出到多个目录（需要导出文件的类型一致，一个avro一个text会报错。可从集群快速快速获得数据）

导出到文件系统的数据都序列化成text，非原始类型字段会序列化成json，导出文件以^A分隔 \n结尾的文本数据。

#1、语法格式
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 row_format
select_statement1 where 
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 row_format
select_statement2 where ] ...
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]

#2、测试
from yingxiong 
insert overwrite directory 'hdfs://ns1/user/zhangjian/text1/yingxiong_out_nan_av'
stored as avro
select id,name,age,xingbie
where xingbie='男' 
insert overwrite local directory '/home/zhangjian/temp/yingxiong_nv_av'
stored as avro
select id,name,age,xingbie
where xingbie='女';

十八、hive的MapReduce执行过程

Map阶段：
执行from加载，进行表的查找与加载
执行where过滤，进行条件过滤与筛选
执行select查询：进行输出项的筛选
执行groupby分组：描述了分组后需要计算的函数
map端文件合并：map端本地溢出写文件的合并操作，每个map最终形成一个临时文件。
然后按列映射到对应的Reduce阶段：
Reduce阶段：
groupby：对map端发送过来的数据进行分组并进行计算。
select：最后过滤列用于输出结果
limit排序后进行结果输出到HDFS文件
有聚合函数、group by 、order by 时走MapReduce，简单查询和where条件是分区的不走MapReduce；where不走
where中不能使用列别名

十九、select … from 语句

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 number]

group by常和聚合函数一起，此时select中只能有分组列和聚合函数
like的内容不是正则，而是通配符；%代表任意字符任意次,_代表一个任意字符
rlike的内容可以是正则，点(’.’)表示任意字符，星（*）表示重复左边0次到无数次

十二、join(连接条件是where和on的到的结果不一样，除inner join外)

Hive中Join的关联键必须在ON ()中指定，不能在Where中指定，否则就会先做笛卡尔积，再过滤。
只支持等值连接

二十一、inner join

select * from person1 inner join person2 on person1.age=person2.age;

二十二、left （outer）join … on

select * from person2 left outer join person1 on person1.age=person2.age;左表为准，右表没有的显示null
select * from person2 left outer join person1 on person1.age=person2.age where person1.age=null;查询person2有二person1没有的字段
select * from person2 left outer join person1 on person1.age=person2.age where person1.age is not null;查询表一表二都有的，is not null 的是表二（in/not in）

二十二、right （outer）join … on

二十三、多表join

select * from teacher t inner join person1 p1 on p1.teacher=t.name inner join person2 p2 on p1.age = p2.age;显示
select * from person1 p1,person2 p2,teacher t where p1.age = p2.age and t.name=p1.teacher;隐式
查询新增
select count(1) from 
(select name,count(1) c1,'nn'from person1  group by name) a
left join
(select name,count(1) c2,'nn' from person1 where teacher='yutujing' group by name) b
on a.name=b.name
where b.name is null;

二十四、全连接full （out） join … on

select * from person1 p full join teacher t on p.teacher=t.name;
全连接相当于=左联接 union 右连接
select * from person1 right join teacher on person1.teacher=teacher.name
union
select * from person1 left join teacher on person1.teacher=teacher.name;

二十五、严格模式不能允许的三个操作

set hive.mapred.mode=strict;开启严格模式
set hive.mapred.mode=nonstrict;取消严格模式
限制笛卡尔积的查询（笛卡尔积Join）。这种情况由于没有指定reduce join key，所以只会启用一个reducer，数据量大时会造成性能瓶颈；
partition表不加分区使用
order by后面不跟limit。order by会强制将reduce number设置成1，不加limit，会将所有数据sink到reduce端来做全排序；
动态分区的第一个分区必须是静态的

二十六、去重连接union

select age from person1 union select age from person2;

二十七、不去重连接union all

select age from person1 union all select age from person2;

二十八、hive的调优

十二九、MapJoin

set hive.mapjoin.smalltable.filesize=2500000; //刷入内存表的大小(字节)，文件小于这个值才会执行MapJoin
hive 默认是开启MAPJOIN（将前面的表刷入内存）。即下面两条，false是关闭
set hive.auto.convert.join=true; //将小表刷入内存中，默认是true
set hive.ignore.mapjoin.hint=true;默认是true
关闭时：select /* +mapjoin(p1) /表示把p1加载到内存中
select / +mapjoin(p1) */ * from person1 p1 inner join person2 p2 on p1.age=p2.age;

三十、grouping sets

#只能显示聚合函数和分组的字段
select count(1),xingbie from yingxiong group by xingbie;
#按性别和type分组
select count(*),xingbie,type from yingxiong group by xingbie,type;
#等价于上面那条
select count(*),xingbie,type from yingxiong group by xingbie,type;
--------------------------------------------------------------------------------
select count(*),xingbie,type from yingxiong group by xingbie,type grouping sets (xingbie,type);
#等价于
select count(*),null,type from yingxiong group by type
union 
select count(*),xingbie,null from yingxiong group by xingbie;
--------------------------------------------------------------------------------
select count(*),xingbie,type from yingxiong group by xingbie,type grouping sets ((xingbie,type),type);
#等价于
select count(*),xingbie,type from yingxiong group by xingbie,type
union
select count(*),null,type from yingxiong group by type;
--------------------------------------------------------------------------------
select count(*),xingbie,type from yingxiong group by xingbie,type grouping sets ((xingbie,type),type,xingbie);
#等价于
select count(*),xingbie,type from yingxiong group by xingbie,type
union
select count(*),null,type from yingxiong group by type
union
select count(*),xingbie,null from yingxiong group by xingbie;
--------------------------------------------------------------------------------
select count(*),xingbie,type from yingxiong group by xingbie,type grouping sets ((xingbie,type),type,xingbie,());
#等价于
select count(*),xingbie,type from yingxiong group by xingbie,type
union
select count(*),null,type from yingxiong group by type
union
select count(*),xingbie,null from yingxiong group by xingbie
union
select count(*),null,null from yingxiong ;

#多维度查询：
#创建yingxiong_grouping表
#导入grouping sets ((xingbie,type),type,xingbie,())中的数据
1、create table yingxiong_grouping as select count(*),xingbie,type from yingxiong group by xingbie,type grouping sets ((xingbie,type),type,xingbie,())
2、select * from yingxiong_grouping where xingbie is null and type is null;--查询总人数
3、select * from yingxiong_grouping where xingbie is null and type is not null;--查询各type的数量
4、select * from yingxiong_grouping where xingbie is not null and type is null;--查询各性别的数量
5、select * from yingxiong_grouping where xingbie is not null and type is not null;--查询性别和类型组合的数量
--------------------------------------------------------------------------------
if（条件，结果1，结果2）

case when 条件 then 结果1 else 结果2 end

coalesce(null,null,8,3,null)返回第一个非空值；

with cube 可以代替 grouping sets 中的所有组合

select count(*),xingbie,type from yingxiong group by xingbie,type with cube;

with rollup 是向右递减的组合，和 with cube不同

select count(*),xingbie,type from yingxiong group by xingbie,type with rollup;

三十一、常用函数

时间转string：from_unixtime(bigInt,[string format])；返回string
select from_unixtime(12346548,'y-M-d H:m:s:S');
select from_unixtime(12346548);
获得当前日期：current_date();返回date
获得当前时间戳：current_timestamp()；返回timestamp
select current_timestamp();
格式化timestamp/date/string为string：date_format();返回string，不能格式化long类型
select date_format('2018-05-6','y-M-d H:m:s');
select format(current_date(),'yyyy-MM-dd HH:mm:ss');
select date_format(from_unixtime(1234567),'yyyy-MM-dd HH:mm:ss');
随机函数rand()
round(m,n);对m四舍五入，保留n个小数
concat(string str1,string str2)
split(string str1,string str2);返回字符串数组
repeat(string str, int n);重复字符串函数,返回重复n次后的str字符串
lpad(string str, int len, string pad)。左补足函数,将str进行用pad进行左补足到len位
find_in_set(string str, string strList);集合查找函数,返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0
next_day(); SELECT next_day('2015-01-14', 'TU');'2015-01-20'
 窗口函数

三十二、排序比较

order by 全局排序，只会有一个reduce执行
sort by 每个reduce输出有序，mapred.reduce.tasks
distribute by 相同的key分到同一个reduce
cluster by ：distribute by + sort by；但是只能默认asc，不能指定desc

三十三、Mysql导数语法

load data  [low_priority] [local] infile 'file_name txt' [replace | ignore]      into table tbl_name      [	  fields      [terminated by't']      [OPTIONALLY] enclosed by '']      [escaped by'\' ]	  ]      [lines terminated by'n']      [ignore number lines]      [(col_name,   )]