HIVE学习系列——Hive操作

---

Hive表介绍

• 相关文章持续更新ing！！！欢迎各位大佬批评指正！！！
• windows下hive安装与配置（上）
• windows下hive安装与配置（下）
• 前排提示，windows下hive安装与配置虽已跑通，但由于版本更换导致部分路径存在错误，后续订正
• demo运行
• 函数介绍
• Q&A的学习

Hive中表格共有4类，分别是内部表、外部表、分区表与桶表

• 内部表：删除时会将表中数据与元数据一同删除
• 外部表：删除时仅删除表与数据之间的引用，不会删除表中数据
• 分区表：按照指定分区进行数据存储，映射至hdfs可以理解为在根目录下创建子文件夹
• 分桶表

部分符号释义：
“[ ]”表示该部分内容可以出现，或者不出现
“( )”表示该部分内容必须出现
“A|B”表示该部分内容中A或者B只能出现一个

---

基本句法-创建新表：

create [external] table [if not exist] table_name  --创建一个[外部]表，通过if避免表已经存在的异常
[(
	col_name data_type [comment col_coment],  --为列添加注释
	...
)]
[comment table_comment]  --为表添加注释
[partitioned by  --创建分区
	(col_name data_type [comment col_comment]),
	...
]
[clustered by (col_name, ...)  --创建分桶
	[sorted by (col_name [asc|desc], ...)]
	into num_buckets buckets
]
[row format delimited
	[fields terminated by char]  --这里的char类指代字符，一般有[',', '-', ':']等等
	[collection items terminated by char]
	[map keys terminated by char]
	[lines terminated by char]
]|[row format serde serde_name  --org.apache.hadoop.hive.serde2.RegexSerDe
	[with serdeproperties(
		property_name = property_value,  --input.regex = 正则表达式
		...
	)]
]
[stored as file_format]  --指定文件存储类型：sequencefile二进制序列文件；textfile文本；rcfile列式存储格式；
[location hdfs_path]  --指定表在hdfs上的存储位置
lifecycle 7;

---

Demo运行（以实际使用中的常用句法为编写规范）：

--创建总表
create table if not exists base_table(
	car_id string comment 'id of car',  --汽车id
	car_price double comment 'price of car',  --汽车价格
	max_speed double comment 'max speed of car',  --汽车最高时速
	origin string comment 'manufacturers'  --汽车制造商
)
comment 'info of car'
partitioned by(
	ds string comment 'store part'  --分区表
)
lifecycle 7  --生命周期为7天[针对当前表，若lifecycle周期内没有更新，则由maxcompute回收]
;

--创建GTA汽车表
create external table if not exists gta_table(
	car_id string comment 'id of gta car',
	max_speed double comment 'max speed of gta car'
)
comment 'GTA car'
partitioned by(
	ds string comment 'store part'
)
lifecycle 7
;

Q&A

• 是否可以不指定字段的创建表？
• location关键字安装到了哪里？

---

基本句法-向table添加数据

--从现有数据库添加（类似于add方式，不会覆写表中原始数据）
insert into table_name 
[partition (
	col_name = col_val,
	...
)]  --将数据添加至指定分区
select
	col_name,
	...
from table2_name
;

--从现有数据库添加（覆写形式）
insert overwrite table_name
[partition (
	col_name = col_val,
	...
)] 
select
	col_name,
	...
from table2_name
;

--从文件添加
--添加local表示从本地文件系统添加，反之表示从hdfs文件系统添加
load data [local] inpath file_path [overwrite] into table table_name
[partition (
	col_name=col_val,
	...
)]
;

Demo运行（承接创建的表）

load data local inpath './cars1.xlsx' into table base_table
partition (
	ds='volkswagen'
);
load data local inpath './cars2.xlsx' into table base_table
partition (
	ds='audi'
);
load data local inpath './cars3.xlsx' into table base_table
partition (
	ds='byd'
);
insert overwrite gta_table partition (ds='vlokswagen')
select
	car_id,
	max_speed
from base_table
where ds='vlokswagen';

Q&A

• 静态写入与动态写入的对比与作用

---

基本句法-删除table中数据&删除table

• hive删除不支持Delete from table_name where ...
• hive中truncate不支持外部表删除

--分区粒度删除
--删除整个分区数据
alter table table_name drop partition(col_name='col_value')
--删除分区内的某条数据（利用overwrite进行新一轮覆写）
insert overwrite table table_name partition(col_name='col_value')
select col1, col2, ... from table_name
where col_name='col_value' and col2 is not null;

--表格粒度删除
--仅删除数据，保留结构
----内部表
truncate table table_name;
----外部表（利用overwrite进行覆写）
insert overwrite table table_name select * from table_name where 条件;  --条件为where 1=0时，表示清空
--不保留结构
drop table [if exists] table_name;
drop table [if exists] table_name purge;  --表示永久删除，不再恢复

Demo运行（承接添加数据后的表）

--删除分区内部分数据
insert overwrite table gta_table partition(ds='volkswagen')
select car_id, max_speed from gta_table
where ds='volkswagen' and max_speed >= 100;

---

基本句法-修改table信息

alter table table_name rename to table_name_new;
alter table table_name add columns(
	col_name data_type [comment col_coment],
	...
);
alter table table_name drop columns col_name;
alter table table_name change col_name new_colname new_type;
alter table table_name replace columns(
	col_name data_type [comment col_coment],
	...
);

Demo运行

alter table gta_table rename to gta_car;

Q&A

• 表中有数据的情况下，修改表格对数据的影响？

---

基本句法-查找table中数据

select [all|distinct] col1, col2, ...
from table_name
[where where_condition]
[group by col_name [having have_condition]]
[order by col_name]
[limit num];

Demo

select distinct car_id
from gta_car
limit 2;

---

基本句法-查看table信息、分区信息

Demo

--分区信息查看
show partitions gta_car;

--表格信息查看
--查看create语句
show create table gta_car;
--查看表格结构信息
desc formatted gta_car;
--查看表格
desc gta_car;
--查看详细信息
desc extended gta_car;

---

函数的使用

聚合函数
窗口函数
日期函数
数学运算函数
字符串处理函数
其他函数