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_dt
add partition (gender = "Male",age = 28);
删除分区
alter table employee_dt
drop 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 employee
lateral view explode(workplace) work_place as wps
lateral view explode(skills_score) sks as skill, score
lateral view explode(depart_title) ga as depart, title;
![](https://i-blog.csdnimg.cn/blog_migrate/58c5ed685f77d7f293ae106d02bf6b96.png)