＜Zhuuu_ZZ＞HIVE(三)高阶建表&分区&分桶

一.Hive(二)

如果有需要查看一些Hive基础知识点请点击下方链接
链接: Hive(二)知识点3600+.

二.高阶建表-CATS and WITH

1.CTAS – as select方式建表

• CTAS不能创建partition, external, bucket table

//复制表机构和数据
//ctas_employee by copying data from employee_internal 
CREATE TABLE ctas_employee as SELECT * FROM employee;

//只复制表结构
CREATE TABLE ctas_employee as SELECT * FROM employee where 1=0;

2.CTE (CTAS with Common Table Expression)

//cte_employee with male named Michael and all females 
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 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..

• 面试题：如何确定一个表是否为临时表？
  • show create table 表名;查看建表语句
• 以CTE方式创表示例：

create temporary table j_d as
with
tmp as(select name, jobs from employee_partition where info.gender='Male'),
t2 as (select name, jobs from employee_partition where info.gender