Hive建表高阶语句

CTAS -as select方式建表

CREATE TABLE ctas_employee as SELECT * FROM employee;

CTE (CTAS with Common Table Expression)

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

LIKE

CREATE TABLE employee_like LIKE employee;

创建临时表--temporary

临时表是应用程序自动管理在复杂查询期间生成的中间数据的方法

表只对当前session有效，session退出后自动删除

表空间位于/tmp/hive-<user_name>(安全考虑)

如果创建的临时表表名已存在，实际用的是临时表

CREATE TEMPORARY TABLE tmp_table_name1 (c1 string);
CREATE TEMPORARY TABLE tmp_table_name2 AS..
CREATE TEMPORARY TABLE tmp_table_name3 LIKE..

清空表数据--truncate

TRUNCATE TABLE employee; 

创建动态分区

---动态分区需设定属性
set hive.exec.dynamic.partition=true; ---是否开启动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
---建表
create table employee_dt(name         string,workplace    array<string>,skills_score map<string,int>,depart_title map<string,string>
) partitioned by (gender string,age int )row format delimited fields terminated by ','collection items terminated by '-'map keys terminated by ':'lines terminated by "\n";

动态表插入数据

/*将employeept中的数据导入到employee_dt表中*/
insert into table employee_dt partition (age,gender)
select name, workplace, skills_score, depart_title,gender,agefrom employee2;

查询分区

show partitions employee_dt;

添加分区

alter table employee_dtadd partition (gender = "Male",age = 28);

删除分区

alter table employee_dtdrop partition (gender = "Male",age = 28);

修改表（alter针对元数据）

改名

alter table employee rename to new_employee;

修正表文件格式

alter table employee set fileformat rcfile; 

修改列名

alter table employee change name  employee_name string; 

添加列

alter table employee add columns (work string);

替换列

alter table employee replace columns (name string);

Hive视图(view)

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

创建视图

create view view_name as select statement;

查看视图定义

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;

Hive侧视图（Lateral View）

select name,wps,gender_age.gender, gender_age.age,skill,score,depart,title
from employeelateral view explode(workplace) work_place as wpslateral view explode(skills_score) sks as skill, scorelateral view explode(depart_title) ga as depart, title;