Hive（二）基础教程

Hive（二）基础教程@你宝爷

一、常用交互命令

#beeline jdbc 启动客户端，可多线程访问，hive不可以
$HIVE_HOME/bin/beeline -u jdbc:hive2://hadoop11:10000 -n hduser
#启动hive 客户端
hive
#查看选项信息
$HIVE_HOME/bin/hive -help
#“-e”不进入hive的交互窗口执行sql语句
$HIVE_HOME/bin/hive -e "select * from tableName"
#“-f”执行脚本中sql语句
$HIVE_HOME/bin/hive -f '文件路径' > '文件名'  #执行结果输入到文件
#退出hive客户端
exit;
quit;
#访问hdfs文件系统
dfs -ls /;
#查看客户端历史执行过的命令
cd ~	#进入用户home目录
cat .hivehistory		#hive客户端历史命令
cat .beeline/history	#beeline客户端历史命令

#开启本地模式
set hive.exec.mode.local.auto=true;(默认为false)

二、数据库的操作

#可选项，判断是否存在，满足条件执行后续命令
[IF NOT EXISTS]
[if exists]

2.1、增

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment] #库的说明
[LOCATION hdfs_path] #指定在HDFS的存储路径，不指定，默认存储在/user/hive/warehouse/*.db
[WITH DBPROPERTIES (property_name=property_value, ...)]; #库的属性

2.2、删

drop database [if exists] '库名';	#只能删除空库
drop database [if exists] '库名' cascade; #递归删除

2.3、改

use '库名'; #切换数据库
alter database db_hive set dbproperties('属性名'='属性值'); #更改属性值，数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置

2.4、查

show databases [like '正则表达式']; #显示所有数据库
desc database '库名';	#显示库的信息
desc database extended '库名'; #显示库的详细信息，包括属性值

三、表的操作

3.1、增

#创建表
#添加[EXTERNAL]，代表创建的是外部表，删除表元数据时，数据不删除
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] DELIMITED [FIELDS TERMINATED BY ''] #按照指定字符进行字段的分割
        [COLLECTION ITEMS TERMINATED BY ''] #对于集合数据类型（ARRAY、MAP 和 STRUCT）按照指定字段进行分割
        [MAP KEYS TERMINATED BY ''] #对map集合数据按照指定字符进行分割切出K,V
        [LINES TERMINATED BY ''] #以指定的字符进行行的分割。（一般都不用写因为换行都是以\n结束）
[STORED AS file_format] #存储文件的格式:默认是textfile
#	指定存储位置 
[LOCATION hdfs_path] #指定表所存储的路径
[TBLPROPERTIES (property_name=property_value, ...)]#指定表的属性可以有多个，多个之间使用","分开
[AS select_statement] #基于现有表的数据创建新表（可以把现有数据放到新表中）	
[LIKE table_name] #基于现现的表创建新表（基于现有表的表结构创建新表，没有数据）

#添加列
alter table <表名> add columns(<字段名> <字段数据类型>);

3.2、删

drop table <表名>;
#清空表，只能清空管理表，不能清空外部表
truncate table <表名>;

3.3、改

#更改表名
ALTER TABLE <old表名> RENAME TO <new表名>
#更新列，列名可以随意修改，列的类型只能小改大，不能大改小（遵循自动转换规则）
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
#增加和替换列
#替换列replace时注意：
#			1.从表中的第一个字段开始替换
#			2.如果要替换的字段少于原表中的字段那么原表中多余的字段将会被删除掉
#			3.替换的字段类型必须和原字段的类型能够隐式转换。
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

3.4、查

desc <tableName>;

四、管理表和内部表相互转换

#注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！
#TRUE：外部表，FALSE：内部表
alter table student set tblproperties('EXTERNAL'='TRUE');

五、数据操作

5.1、数据导入

#1.方式一 ：将hdfs和linux本地上的文件导入表中
	load data [local] inpath <数据目录地址hdfs/linux本地> [overwrite] into table <表名> [partition (partcol1=val1,…)];
	#[local] ：如果加上该字段表示从本地导入数据，如果不加表示从hdfs上导入数据
	#[overwrite] ：如果加上该字段那么会将原表中的数据覆盖掉，如果不加则将该内容追加到表中
	#[partition (partcol1=val1,…)] ：将数据导入到该表中的哪个分区中	
#2.方式二：将查询的结果，导入表中，注意字段的匹配
	insert <overwrite/into> table <表名> <select查询语句>; 	  
#3.方式三：根据查询的结果创建成一张新表
	create table <表名> as <select查询语句>;  
#4.方式四：创建表时关联hdfs上的数据文件
    create table <表名>(
        <字段名> <字段数据类型>,
        <字段名> <字段数据类型>
    )
	row format delimited fields terminated by '\t'
	location '<hdfs上文件路径>'
#5.方式五：将hdfs上的数据文件导入表中，且要导入的表名不可以存在
	import table <表名>  from '<hdfs上的export命令导出的元数据目录>'

5.2、数据导出

#1.方式一
#1）将查询的结果导出到本地#[local]-如果加上该字符数据导入到本地，如果不加该字符数据导入到hdfs上
insert overwrite [local] directory <linux本地路径/hdfs路径> 
[row format delimited fields terminated by '\t'] #让导出的数据有分割符
<select查询语句>;
#2.方式二：Hadoop命令导出到本地
dfs -get 'hdfs上表的数据的路径' 'linx本地路径' #hive客户端执行
hadoop fs -get 'hdfs上表的数据的路径' 'linux本地路径' #linux bash窗口执行
#3.方式三：Hive Shell 命令导出：
hive -e '<select查询语句>' > '<linux本地路径>'  #注意使用库名.表名
#4.方式四：导出元数据到HDFS上
export table <表名> to <hdfs路径，此路径用作import导入未创建的表使用>;

六、Select查询

6.1、基本查询

#1）基本格式，表别名
select <字段名A [AS <字段别名>]，字段名B> from <表名>;
#2）常用函数
select
    count(字段名), 	#求字段出现的行数
    max(字段名),		#求字段最大值	
    min(字段名),		#求字段最小值	
    sum(字段名),		#求字段求和值	
    avg(字段名)		#求字段平均值	
from <表名>;
#3）limit 
select * from <表名> limit <数字n>; #查询n行数据
select * from <表名> limit <数字m,n>; #查询m行开始的n条数据
#4）模糊查询
	#4.1）like ：类似mysql模糊查询 '%'：任意多个任意字符， '_'：一个任意字符，占位符
	#4.2）rlike：Java的正则表达式：'^'：以某字符开始，'^.'：一个任意字符，占位符，'[A]'：等同于'%A%'
#5）逻辑运算符（AND，OR，Not）,where ：类似MySql用法

6.2、分组查询

#1）group by ：类似MySql用法
#2）having	：类似MySql用法

6.3、表连接查询

#1）只支持等值连接，不支持非等值连接
#2）支持full join满外连接 
#3）其他left join，right join，inner join类似MySql用法

6.4、排序查询

#1）order by 
	#全局排序，注意：只支持一个Ruduce Task
#2）sort by 
	#排序，为每个reducer产生一个排序文件，注意：需要指定Ruduce Task数量，分区数>Ruduce Task数量
#3）distribute by 
	#分区，类似MR中自定义分区，结合sort by排序
	#set mapreduce.job.reduces=3;
	select * from <表名> distribute by <分区字段> sort by <分区内指定字段进行排序>
#4）cluster by
#除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者#DESC。
 	select * from <表名> cluster by <分区字段>;
#5）如果分区字段和排序字段一致时，如下命令等同
	select * from <表名> distribute by <分区字段> sort by <分区字段>
 	select * from <表名> cluster by <分区字段>;	

七、分区表&分桶表

7.1、分区表

分区，在hdfs上以同一级下多目录的形式存在，

一级分区：一级目录下多目录的形式存在

二级分区：二级目录下多目录的形式存在

以此类推。。。

7.1.1、创建分区表

#一级分区表--------------------------------------------------------------------------------
create table <表名>(
	<字段名> <字段类型>,
    <字段名> <字段类型>, 
    <字段名> <字段类型>
)
partitioned by (<分区字段名> <分区字段类型>