高阶建表&分区&分桶
一.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