Hive---Hive语法(三)

Hive建表高阶语句

---

---

Hive建表高阶语句

CTAS – as select方式建表

CREATE TABLE ctas_employee as SELECT * FROM employee;

注意：CTAS不能创建partition, external, bucket

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;

创建临时表— TEMPORARY

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

CREATE TEMPORARY TABLE tmp_table_name1 (c1 string);

CREATE TEMPORARY TABLE tmp_table_name2 AS..

CREATE TEMPORARY TABLE tmp_table_name3 LIKE..

表只对当前session有效，session退出后自动删除
表空间位于/tmp/hive-<user_name>(安全考虑)
如果创建的临时表表名已存在，实际用的是临时表

清空表数据—TRUNCATE

TRUNCATE TABLE employee; 

创建动态分区

---动态分区需设定属性
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";

插入数据

/*将employeept中的数据导入到employee_dt表中*/
insert into table employee_dt partition (age,gender)
select name, workplace, skills_score, depart_title,gender,agefrom employeept;

查询分区

show partitions employee_dt;

添加分区

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

删除分区

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

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