Hive高级

建表语句

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_dt add partition (gender = "Male",age = 28);

删除分区

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