HiveDDL库表操作

淡淡的烟草味﹌ 2021-09-23 06:54 377阅读 0赞

> DDL( Database Defined laniage ) 数据库定义语言，如create、alter、drop。主要用于定义或改变表结构、数据类型、表之间的链接和约束等初始化工作。

**目录**

*  1. 库操作
 *  2. 表操作
 *  1. 建表语句说明
 *  2. 创建表
 *  1. 内部表
 *  2. 外部表
 *  3. 分区表
 *  4. 分桶表
 *  5. 复制表 like
 *  6 查询表
 *  3. 查看表
 *  4. 查看表信息
 *  5. 删除表
 *  6. 清空表
 *  7. 修改表 alter
 *  1. 修改表名字
 *  2. 修改表字段信息
 *  3. 替换列
 *  4. 修改表分区信息
 *  3. 删除分区

--------------------

# 1. 库操作 #

create database 库名;                  创建库  
    create database if not exists 库名;
    show databases;                        查看库
    use database;                          使用库
    select current_database();             查看正在使用的库
    desc database;                         查看库信息
    drop database 库名;                    删除库,只能删除空库
    drop database if exists 库名;          if exists 避免错误
    drop database 库名 cascade;            删除库,非空库
    drop database 库名 restrict;           默认情况下的删除库的操作

# 2. 表操作 #

## **1. 建表语句说明** ##

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
     [(col_name data_type [COMMENT col_comment], ...)]  //添加字段描述信息
     [COMMENT table_comment]  //添加表描述信息
     [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
     [CLUSTERED BY (col_name, col_name, ...)
     [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
     [ROW FORMAT row_format]
     [STORED AS file_format]
     [LOCATION hdfs_path] 
    
    1. EXTERNAL          是否创建外部表,不加创建内部表,加上创建外部表
    2. COMMENT           描述信息,后面跟 'xxx'
    3. PARTITIONED BY    指定分区字段,分区字段不是表中的字段,是全新的
    4. CLUSTERED BY      指定分桶字段,必须是表中某个字段,因为它没给定类型
    5. SORTED BY         指定排序字段,必须是表中某个字段,因为它没给定类型
                           排序规则指定的是在同一个分桶内的排序规则
    6. INTO num_buckets BUCKETS            指定分桶个数
    7. ROW FORMAT                          指定分隔符
        delimited fields terminated by ''  指定列分隔符
        lines terminated by ''             指定行分隔符
    8. STORED AS:        指定最终表数据的存储格式
       textfile          默认文本格式/ rcfile 行列结合格式/ parquet 压缩格式/ orc / json
    9. LOCATION:         指定hive上表的存储路径,存在dhfs上的路径,不指定默认在配置的路径下存储
                            没配置也没有指定,默认在/user/hive/warehouse

** 分隔符指定 由外向内顺序。**

create external table user_info(
        id int,
        address array<string>,
        score map<string,int>,
        info struct<age:int,
        sex:string,
        hobby:string>
    )
    row format delimited fields terminated by '\t'    // 字段分隔符
    collection items terminated by ','                // 集合之间分隔符
    map keys terminated by ':'                        // key/value分隔符
    lines terminated by '\n';                         // 行分隔符

## 2. 创建表 ##

### 1. 内部表 ###

create table if not exists '内部表' (id int, name string) 
    row format delimited fields terminated by ',';

### 2. 外部表 ###

使用location指定HDFS原存储路径，避免数据移动。

实际生产中公共数据必须使用外部表，并使用location指定路径。数据从HDFS加载是移动过程，所以指定HDFS原存储路径，否则会造成hdfs数据移动。

create external table if not exists '外部表'(id int, name string) 
    row format delimited fields terminated by ',';

### 3. 分区表 ###

create table if not exists '分区表'(id int, name string) partitioned by (`date` string) 
    row format delimited fields terminated by ',';

不同目录，在添加数据前要先添加分区。

add partition() 添加分区
    alter table ptn_table add if not exists partition(`date` = 'xx');

### 4. 分桶表 ###

字段是表中字段。

create table '分桶表'(id int, age int, name string) clustered by (id) 
    sorted by (age desc) into 4 buckets row format delimited fields terminated by ',';

clustered by (id) 等于map端发送的key，默认按照hash分区==id.hashcode() into 4 == numReduceTasks() == id.hashCode() % 4

### 5. 复制表 like ###

create table '复制表' like external_table;

复制的表得自定义external内外部表，只复制表结构，不复制表数据。

### 6 查询表 ###

create table '查询表' as select ... from '原表';

# #

## 3. 查看表 ##

show tables;  
    show tables in 库名;                查看某库中的表
    show tables like 's*';             查看s开头的表
    show create table 表名;             查看建表语句
    show partitions 表名;               查看表的分区信息,针对分区表

# #

## 4. 查看表信息 ##

desc 表名:              显示表的字段
    desc formatted 表名:    格式化显示表的详细信息
    desc extended 表名:     显示表的详细信息(整段显示)

## 5. 删除表 ##

drop table if exists 表名;        -- 使用 if exists 避免错误

## 6. 清空表 ##

truncate table 表名;     清空数据,保留表结构

# #

## 7. 修改表 alter ##

### 1. 修改表名字 ###

alter table 表名 reanme to 新表名;

### 2. 修改表字段信息 ###