Hive中的CATS和WITH（保姆级案例）

CTAS – as select方式建表:

create temporary table tmp_employee as select name,work_place from employee;

WITH:

create table if not exists employee(
	name string,
	work_place array<string>,
	gender_age struct<gender:string,age:int>,
	skills_score map<string,int>,
	depart_title map<string,array<string>>
)
comment 'this is an internal table'
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

例：查询所有名字是Will性别是Male的人和所有性别是Female的人。

方式一：select t.* from(select * from employee where gender_age.gender='Male') as t where t.name = 'Will' union all select * from employee where gender_age.gender ='Female;'

方式二：
with
t1 as (select * from employee where gender_age.gender = 'Male'),
t2 as (select * from t1 where t1.name = 'Will'),
t3 as (select * from employee where gender_age.gender = 'Female')
select * from t2 union all select * from t3;

可以看到这两种方式的结果是一样的，但是方法二的可读性更高。