开发利用水资源北京优化推广
目录
建表语句
表数据
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);