HIVE 基础（三）

小咪咪 2024-03-26 12:06 145阅读 0赞

**目录**

建表语句

表数据

Hive建表高阶语句 - CTAS and WITH

CTAS – as select方式建表

CTE (CTAS with Common Table Expression)

创建临时表

清空表数据

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

### 表数据 ###

![3ed5c4d7087845eab525271638032c60.png][]

### 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);

[创建分区][Link 1]

### 动态分区 ###

#### 使用动态分区需设定属性 ####

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

![b106713260b045e4a2358e9ef407dd94.png][]

### 添加分区 ###

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

![ba38933f171a4a5299b2b7b19ac9a5fc.png][]

### 删除分区 ###

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

[3ed5c4d7087845eab525271638032c60.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/28f4fc0a1ffb42328980b7577ead2a3c.png
[Link 1]: https://blog.csdn.net/qq_57151931/article/details/129089023?spm=1001.2014.3001.5501#t6
[b106713260b045e4a2358e9ef407dd94.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/18fc3b8710aa40f5ac9ab1deb0877b29.png
[ba38933f171a4a5299b2b7b19ac9a5fc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/46cc2be5f454440694d984333e54af5d.png