HIVE 基础（三）

目录

建表语句

表数据

Hive建表高阶语句 - CTAS and WITH

CTAS – as select方式建表

CTE (CTAS with Common Table Expression)

LIKE

创建临时表

清空表数据

修改表（Alter针对元数据）

改名

修正表文件格式

修改列名

添加列

替换列

动态分区

使用动态分区需设定属性

建表语句

 动态分区插入数据

查询分区

 添加分区

删除分区

---

建表语句

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

表数据

Michael|Montreal,Toronto|Male|30|DB:80|Product:Developer Lead
Will|Montreal|Male|35|Perl:85|Product:Lead,Test:Lead
Shelley|New York|Female|27|Python:80|Test:Lead,COE:Architect
Lucy|Vancouver|Female|57|Sales:89,HR:94|Sales:Lead

Hive建表高阶语句 - CTAS and WITH

CTAS – as select方式建表

CREATE TABLE ctas_employee as SELECT * FROM employee;

CTAS不能创建partition, external, bucket table

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;

创建临时表

临时表是应用程序自动管理在复杂查询期间生成的中间数据的方法

表只对当前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 TABLE employee; 

修改表（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);

创建分区

动态分区

使用动态分区需设定属性

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";

 动态分区插入数据

insert into table employee_dt partition (gender,age)
select name, workplace, skills_score, depart_title, gender, age
from employee2;

查询分区

show partitions employee_dt;

 添加分区

alter table employee_dtadd partition (gender = "Male",age = 28);

 

删除分区

alter table employee_dtdrop partition (gender = "Male",age = 28);