Hive基础

Hive的语法基本与mysql相同 ，且同样不区分大小写

1.安装

apache hive安装教程，安装过程有问题可以留言。

---

2.数据类型

Hive数据类型	对应java类型
TINYINT	byte	1byte
SMALLINT	short	2byte
INT	int	4byte
BIGINT	long	8byte
BOOLEAN	boolean
FLOAT	float
DOUBLE	double
STRING	string	单引号或双引号
TIMESTAMP		时间戳
BINARY		字节数组

除此之外，hive还提供了以下三种集合类型的数据结构

集合类型	说明
STRUCT
MAP
ARRAY

---

3.基本语法

3.1 DDL

3.1.1 数据库层面

• 创建数据库

create database database_name if not exists;
#关键词  creat database + 数据库名称
#执行完之后可以在hdfs看到生成一个文件夹，如果没有在hive-site.xml中指定地址，默认hdfs的存储路径是/user/hive/warehouse/database_name.db，之后建的表数据均存储在该路径下
#也可以通过添加参数 location 来指定hdfs的存储地址
create database database_name location 'hdfs_url';
#location后指定hdfs的存储路径
#如果url已经存在了，比如/usr/hive/warehouse，不会报错，但不会在该路径下生成database_name.db的文件夹；如果url不存在，则会自动创建
# 建议建表时路径最好规范化，即 数据库名.db 结尾，方便区分。  

• 查看所有已经创建的数据库

show database database_name;
#关键词  show database + 数据库名称

• 查看数据库信息

desc database_name;
#关键词  desc + 数据库名称,desc即description的缩写

• 删除数据库

drop database database_name;
#关键词  drop database + 数据库名称，该方式只能删除空数据库
drop database database_name cascade;
#关键词 cascade 用于强制删除非空数据库

3.1.2 建表语句

# []为可选参数
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[
  (col1_name data_type [COMMENT col1_comment],
   col2_name data_type [COMMENT col2_comment],
   ...)
]
[COMMENT table_comment]
#第一部分是表的最基本信息，如表名，有哪些列，每列是什么数据类型，并为列或表添加注解，语法完全根mysql一致
# EXTERNAL外部表，之后会介绍
[
  PARTITIONED BY (partition_col1_name data_type[COMMENT partition_col1_comment],
  partition_col2_name data_type [COMMENT partition_col2_comment],
  ...)
]
[
    CLUSTERED BY (colx_name,coly_name,...)
	[SORTED BY (colz_name[ASC|DESC],...)] INTO num_buckets BUCKETS
]
#第二部分用于优化sql语句的效率
#该部分介绍详看下表
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
# 第三部分是存储格式
# ROW，指定hdfs文件的行分割符
# STORED，指定hdfs文件的格式
# LOCATION hdfs路径

• 管理表(外部表)与外部表

	管理表	外部表
关键词		EXTERNAL
区别	drop后metadata和数据文件均被删除	drop后只删除metadata，数据文件仍存在；drop后可通过CREATE重建metadata，之后便可正常使用(无需再导入数据)

管理表与外部报表切换

alter table table_name set tblproperties('EXTERNAL'='FALSE');

！！HIVE没有办法建立索引，只能通过PARTITION和CLUSTERED来提高sql效率！！

	PARTITION	CLUSTERED
作用	分区	分桶
column	分区column不在create的column内	分桶column在create的column内
实质	分区实质是将数据存储按指定文件夹来存储	分桶的实质是将数据按指定文件来存储
对象	针对整个hive表	针对每一个partition

• PARTITION与CLUSTERED说明
  • CLUSTERED
    分桶描述的是每一个partition下的数据的存储方式，将partition内的数据按照某些column以hash的方式存储在不同的文件下。
  • PARTITION
    假设按year,month,day来进行分区，则一级分区为year，二级分区为month。
    如果我们存放了2019-2020年的数据，那么在table的hdfs存储路径下，将有以下文件夹，不同年份及月份的数据将被存储在不同的文件夹下，该存储方式在查找时能很快定位到某一个文件夹，避免了全表扫描。
    表的分区信息存在mysql的hive metadata数据库下的PARTITIONXXXX下

只要mysql中的metadata与hdfs中的数据url对应上，建表和导数据完全可以分开，使用hdfs dfs -put仍能实现insert的功能

3.1.3修改表

• 重命名

alter table table_name rename to new_table_name;

• 修改列

#增加列
alter table table_name add columns

#修改列名、列类型
alter table table_name change column column_name new_column_name new_data_type;

#替换表的所有字段
alter table table_name replace columns(column1 data_type,...) #用()内的字段替换原表所有的列，是所有的列！！加入原表5个字段，replace只给4个字段，则replace完后只能select出4个字段；加入新的字段的数据类型与原表对应字段的数据类型不一致，则select返回NULL；
#但该种操作不会真正删除调hdfs的数据，当重新replace，且column数量原原表一样、数据类型一致，则select返回正常结果

3.2 DML

3.2.1 数据导入

• load

load data [local] inpath url [overwrite] into table table_name [partition(partiton_col='')]
#[local]表示将本地文件导入表中，否则路径默认为hdfs路径
#[overwrite]覆盖原表内容
#[partition] 指定导入哪一个分区
# 文件中不需要有分区字段的数据，hive会自动将分区信息填充到表中

• insert

insert overwrite|into table_name 
select * from other_table_name;
#将other_table_name数据插入table_name

insert into table_name values(...),(...),...;

• location

create table table_name(col1....) location hdfs_url;
#直接将url的数据导入到表中

3.2.3 数据导出

将sql结果导出到本地或hdfs文件夹

insert [overwrite] [local] directory 'url' 
row format delimited fields terminated by '\t' 
select * from table_name; 

3.2.4 清空表

truncate table_name;
#只能用于管理表(内部表)

3.3 DDL之分区操作

• 增加分区

alter table table_name add partition(year='2020');
# 为table_name关于year字段增加一个2020分区

#一次加多个分区
alter table table_name add partition(year='2019') partition(year='2020');
#多个分区之间用空格隔开

• 删除分区

alter table table_name drop partition(year='2019'),partition(year='2020');
#多个分区之间用逗号,隔开

• 查看所有分区

show partitions partition_table_name;
#只有分区表可以查看分区信息

• 直接将数据文件上传到hdfs分区目录，并与hive产生联系的三种方式

#方式1
#以hdfs shell 创建分区目录
#用put方式将数据文件导入到分区目录下
#用hive的修复命令msck repair 为表添加metadata
hdfs dfs -mkdir  -p hdfs_url
hdfs dfs -put xxxxx hdfs_url
msck repair table table_name;

#方式2
#以hdfs shell 创建分区目录
#用put方式将数据文件导入到分区目录下
#为hive表添加对应于分区目录的partition
hdfs dfs -mkdir  -p hdfs_url
hdfs dfs -put xxxxx hdfs_url
alter table table_name add partition(xxxx);

#方式3
#以hdfs shell 创建分区目录
#使用hive的load方法为表添加分区数据
hdfs dfs -mkdir  -p hdfs_url
load data inpath xxx into table table_name partition(xxxx)

3.4 查询

# 全表查询
select * from table_name;

# 查询某些列,并重命名
select id [as] new_id,name from table_name;

#where 
selct * from table_name
where id >= 5 and salary between 10000 and 20000
and name is not null
and class in (9,10)
and name like 'chen%';


#group
select count(1) as cnt from table_a a 
join table_b b
on a.class = b.class  
group by class having cnt > 10;

#order by 全局排序
select * from table_name
order by id [desc|asc];
#最后只有一个reducer

#sort by reducer内部排序
select * from table_name 
sort by id;

#distribute by 
select * from table_name
distribute by name
sort by id;
# 未指定distribute字段，则默认随机分布
# 指定distribute字段，则会通过hash算法将数据分散到不同的reducer，再进行sort by

# cluster by
select * from table_name
cluster by id;
#distribute和sort是同一个字段的话，可用cluster代替

3.5 分桶与分区

• 分桶

# 分桶表需要设置以下参数
#1.打开分桶功能
#  set hive.enforce.bucketing=true;
#2.设置reducer个数为-1
#  set mapreduce.job.reduces=-1;
create table table_name(id int,name string ,age int)
clustered by id 
into 4 buckets
row format delimited fields terminated by '\t';
#将分桶数量设置为4

• 分区

create table table_name2(id int,name string, oper_time string)
partitioned by (year string,month string,day string)
row format delimited fields terminated by ',';

往分区插入数据有静态和动态方法，之前介绍的均为静态方法，即在insert或load时必须指定是要为哪一个分区添加数据；而动态的方法则不需要，有hive自行动态判断。

#动态添加数据
#动态分区需设置以下参数
#1.	 set hive.exec.dynamic.partition.mode=nonstrict;
#2.  set hive.exec.dynamic.partition=true;
#3. set hive.exec.max.dynamic.partitions=1000; 所有mapreduce节点上的分区数(整个集群中的执行MR的节点)
#4. set hive.exec.max.dynamic.partitions.pernode = 100;  (执行MR的单个节点)
insert into table table_name
select * from other_table_name;
#从另外一种表中导入，默认以最后几列作为分区字段,分区使用了几级，就默认作后几列

insert into table table_name partition(year,month,day)
select id,name,oper_time,year,month,day);
# 该操作与上述不指定partition效果一样，仍使用默认的最后三列作为分区列，且是按顺序的，如果select中列的顺序与partition列的顺序不一致将按select的列顺序进行动态分区


insert into table_name values(1,'xiaoming','10:10:10',2020,10,10),
(2,'xiaohong','09:09:09',2020,10,11)；
#该方式也是动态insert

3.6 常用函数

count
max
min
sum
avg