– 创建一个外部表

create external table if not exists employee_external(
name string,
work_place array<string>,
sex_age struct<sex:string,age:int>,
skills_score map<string,int>,
depart_title map<string,array<string>>
)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
stored as textfile
location '/data/hive/employee_external';

– 上传数据到指定路径

hdfs dfs -put employee.txt /data/hive/employee_external

– 查看完整的建表信息

show create table employee_external;

– 查看元数据信息

desc formatted employee_external;


create table if not exists employee2(
name string,
work_place array<string>,
sex_age struct<sex:string,age:int>,
skills_score map<string,int>,
depart_title map<string,array<string>>
);

– 插入数据

insert into table employee2 
select * from employee_external;

– 创建一个临时表

create temporary table if not exists employee_temp(
name string,
work_place array<string>,
sex_age struct<sex:string,age:int>,
skills_score map<string,int>,
depart_title map<string,array<string>>
);

– CTAS

create table ctas_employee 
as select * from employee_external;

– cte

create table cte_employee as
with
r1 as (select * from employee_external limit 1),
r2 as (select * from employee_external where name='Will')
select * from r1 union all select * from r2;

select * from cte_employee;
show create table cte_employee;
desc formatted cte_employee;


create external table ctas_employee 
as select * from employee_external;

-- SemanticException [Error 10070]: CREATE-TABLE-AS-SELECT cannot create external table (state=42000,code=10070)

create table like_employee like employee_external;
show create table like_employee;

create external table if not exists employee_external2(
name string,
work_place array<string>,
sex_age struct<sex:string,age:int>,
skills_score map<string,int>,
depart_title map<string,array<string>>
)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
location '/data/hive/employee_external';

– 删除一个表

drop table if exists like_employee;
drop table like_employee;


create table employee2(
name string,
work_place array<string>,
sex_age struct<sex:string,age:int>,
skills_score map<string,int>,
depart_title map<string,array<string>>
);


drop table employee_external2;

alter table cte_employee rename to cte_employee2;


-- load local overwrite

create table if not exists employee(
name string,
work_place array<string>,
sex_age struct<sex:string,age:int>,
skills_score map<string,int>,
depart_title map<string,array<string>>
)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
stored as textfile;

load data local inpath '/opt/datas/employee.txt' 
into table employee;

load data local inpath '/opt/datas/employee.txt' 
overwrite into table employee;

select * from employee;

– 测试从hdfs加载数据

hdfs dfs -mkdir -p /data/hive/employee
hdfs dfs -put employee.txt /data/hive/employee

load data inpath '/data/hive/employee/employee.txt' 
overwrite into table employee;

– 创建单级分区表

create table if not exists employee_partition(
name string,
work_place array<string>,
sex_age struct<sex:string,age:int>,
skills_score map<string,int>,
depart_title map<string,array<string>>
)
partitioned by (month string)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
stored as textfile;

– 加载数据

alter table employee_partition add partition(month='202011');
-- 向分区表加载数据
load data local inpath '/opt/datas/employee.txt' 
into table employee_partition partition(month='202012');

create table if not exists employee_partition2(
name string,
work_place array<string>,
sex_age struct<sex:string,age:int>,
skills_score map<string,int>,
depart_title map<string,array<string>>
)
partitioned by (month string,date string)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
stored as textfile;

alter table employee_partition2 add 
partition(month="202011",date="01") 
partition(month="202011",date="02") 
partition(month="202012",date="01") 
partition(month="202012",date="02");

– 向分区表加载数据

load data local inpath '/opt/datas/employee.txt' 
into table employee_partition2 partition(month='202012',date='01');

– 查看分区表有多少分区

show partitions employee_partition2;

create table if not exists employee_hr(
name string,
id int,
num string,
time2 string
)
row format delimited
fields terminated by '|';

load data local inpath '/opt/datas/employee_hr.txt'
into table employee_hr;

– set 动态分区的

-- Matias McGrirl|1|945-639-8596|2011-11-24
create table if not exists employee_hr_partition(
name string,
id int,
num string,
time2 string
)
partitioned by (month string,date string)
row format delimited
fields terminated by '|';

insert into table employee_hr_partition partition(month,date)
select 
name,
id,
num,
time2,
month(time2) as month,
date(time2) as date
from employee_hr;