Hive语法总结

一、建表语句

建表语句：

# IF NOT EXISTS可选，如果表存在，则忽略
CREATE EXTERNAL TABLE IF NOT EXISTS employee_external (
	# 列出所有列和数据类型
    name string,
    work_place ARRAY<string>,
    sex_age STRUCT<sex:string,age:int>,
    skills_score MAP<string,int>,
    depart_title MAP<STRING,ARRAY<STRING>>
)
COMMENT 'This is an external table'
# 如何分隔列（字段
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
# 如何分隔集合和映射
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
# 文件存储格式
STORED AS TEXTFILE
# 数据存储路径（HDFS)
LOCATION '/home/hadoop/hive/warehouse/employee'; 

创建内部表只需把external去掉即可

查看你表的详细信息：desc formatted employee_external ;

内部表和外部表的区别：
在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
外部表external：建表时需要加上location，指向数据存放的位置如/data/test/student/，如果hdfs上没有此目录，创建表时他会在hdfs上自动创建目录结构，添加数据有两种方式：
- 方式一：通过load方法直接将文件映射到location的目录下
- 方式二：shell命令，通过hdfs dfs -put来将数据文件放入到location的hdfs的目录中
内部表：建表时一般不需要加location，它会默认在hdfs的houseware目录下自动创建目录存放数据，结构是这样的：.../houseware/数据库名.db/创建的数据表名/存放数据表的数据，导入数据是和外部表的相同

二、Hive建表高阶语句-CTAS and WITH

1.CTAS

用此方法创建的只能是内部表

CREATE TABLE ctas_employee as SELECT * FROM employee;

注意：CTAS不能创建partition, external, bucket table

2.CTS

CREATE TABLE cte_employee AS
WITH 
r1 AS  (SELECT name FROM r2 WHERE name = 'Michael'),
r2 AS  (SELECT name FROM employee WHERE sex_age.sex= 'Male'),
r3 AS  (SELECT name FROM employee  WHERE sex_age.sex= 'Female')
SELECT * FROM r1 UNION ALL SELECT * FROM r3;

3.like-只创建表结构

create table student3 like student;
select * from student3;
+----------------+------------------+-------------------+-----------------+--+
| student3.s_id  | student3.s_name  | student3.s_birth  | student3.s_sex  |
+----------------+------------------+-------------------+-----------------+--+
+----------------+------------------+-------------------+-----------------+--+

三、表操作

1.删除表

-- 删除表
drop table if exists employee;
-- 清空表数据
truncate table employee;

2.修改表结构(Alter)

-- 修改表名， 常用于数据备份
ALTER TABLE employee RENAME TO new_employee;
ALTER TABLE c_employee SET TBLPROPERTIES ('comment'='New name, comments');
ALTER TABLE employee_internal SET SERDEPROPERTIES ('field.delim' = '$');
ALTER TABLE c_employee SET FILEFORMAT RCFILE; -- 修正表文件格式
-- 修改表的列操作
ALTER TABLE employee_internal CHANGE 老的列名字 新的列名字 STRING; -- 修改列名
ALTER TABLE c_employee ADD COLUMNS (work string); -- 添加列
ALTER TABLE c_employee REPLACE COLUMNS (name string); -- 替换列

三、装载数据：load

语法：

load data [local] inpath '/opt/datas/student.txt' [overwrite] into table 
student [partition (partcol1=val1,…)];

LOAD用于在Hive中移动数据

有OVERWRITE：覆盖原有的数据
无OVERWRITE：追加到原来的表

1.加local关键字，原始文件位于Linux本地，执行后为拷贝数据

LOAD DATA LOCAL INPATH '/data/mytest/employee.txt' OVERWRITE INTO TABLE employee;

2.没有local关键字，表示文件位于hdfs上执行后为直接移动数据

将hdfs上的数据一道hive表中的分区中

LOAD DATA INPATH '/tmp/employee.txt'  OVERWRITE INTO TABLE employee_partitioned PARTITION (year=2017, month=12);

四、Hive分区（重点！！！！！）

分区主要用于提高性能

• 分区列的值将表划分为一个个的文件夹
• 查询时语法使用"分区"列和常规列类似
• 查询时Hive会只从指定分区查询数据，提高查询效率

分为静态分区和动态分区
静态分区与动态分区的主要区别：在于静态分区是手动指定，而动态分区是通过数据来进行判断

1.建表

动态分区建表语句和静态分区相同

# 建一个分区表
CREATE TABLE IF NOT EXISTS employee_partitioned (
    name string,
    work_place ARRAY<string>,
    sex_age STRUCT<sex:string,age:int>,
)
# 以year，month分区
partitioned by (year int,month int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE
; 

# 建一个数据源表employee_hr
CREATE TABLE IF NOT EXISTS employee_hr (
name string,
employee_id int,
number string,
start_data string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
; 
# 导入数据到employee_hr表中
load data local inpath '/data/big_data_training-master/data/employee_hr.txt' into table employee_hr;

2.静态分区

需要手动创建分区

# 手动添加分区
alter talbe employee_partitioned add
partition (year=2019,month=7)
partition (year=2020,month=7)
partition (year=2020,month=8)
;

# 数据导入静态分区的两种方式
# 方式一：外部文件导入，入csv文件
load data local inpath '/data/emp/employee.txt' into table employee_partitioned partition (year=2020,month=7);
# 方式二：通过查询插入静态分区的指定分区中
insert into employee_partitioned partition (year=2019,month=7)
select name,employee_id,number,year(start_data),month(start_data) from employee_hr

3.动态分区

需要开启动态分区

# 开启动态分区
set hive.exec.dynamic.partition=true;
# 开启非严格模式，严格模式需要指定分区
set hive.exec.dynamic.partition.mode=nonstrict;

insert方式添加动态分区数据

# 字段一定要对齐
insert into employee_partitioned partition (year,month)
select name,enployee_id,number,year(start_data),month(start_data) from employee_hr

4.删除分区

删除单个分区

 alter table dept_partition drop partition (month='201904');

删除多个分区

# 删除多个分区有逗号，添加多个分区没有逗号
alter table dept_partition drop partition (month='201905'), 
partition (month='201906');

5.查看分区数

show partitions dept_partition;

6.查看分区表结构

desc formatted dept_partition;

五、分桶表

Hive:按照分桶字段(列)的 hash 值除以分桶的个数进行取余(bucket_id = column.hashcode % bucket.num)。

1.设置Hive的分桶开关

# 手动开启分桶
set hive.enforce.bucketing=true;

2.创建分桶表

create table test_bucket(
id int,
name string,
age int
)
# 由age字段来分桶，分4个桶
clustered by (age) into 4 buckets
row format delimited
fields terminated by '';

3.将查询的数据插入到分桶表中

insert into test_bucket
select id,name,age from people;

4.查看hdfs中的目录

每个分桶对应一个文件

六、视图(View)

1.视图概念

• 通过隐藏子查询、连接和函数来简化查询的逻辑结构
• 只保存定义，不存储数据
• 如果删除或更改基础表，则查询视图将失败
• 视图是只读的，不能插入或装载数据

2.应用场景

• 将特定的列提供给用户，保护数据隐私
• 用于查询语句复杂的场景

3.视图操作

视图操作命令：CREATE、SHOW、DROP、ALTER

-- 创建视图，支持 CTE, ORDER BY, LIMIT, JOIN,等
CREATE VIEW view_name AS SELECT statement; 
-- 查找视图 (SHOW VIEWS 在 hive v2.2.0之后)
SHOW TABLES; 
-- 查看视图定义
SHOW CREATE TABLE view_name; 
-- 删除视图
DROP view_name; 
--更改视图属性
ALTER VIEW view_name SET TBLPROPERTIES ('comment' = 'This is a view');
-- 更改视图定义,
ALTER VIEW view_name AS SELECT statement; 

4.侧视图(Lateral View) 了解即可

select name,work_place,loc from employee lateral view outer explode(split(null,',')) a as loc;

支持多层级

select name,wps,skill,score from employee 
lateral view explode(work_place) work_place_single as wps
lateral view explode(skills_score) sks as skill,score;
-- skills_score以map键值对形式保存数据，skill是key，score是value