HiveDDL库表操作

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. 修改表字段信息

1. 添加字段，新字段要指定类型

alter table 表名 add columns();
alter table 表名 add columns(age int, appid string);

2. 修改字段，修改字段类型

注意表类型之间的匹配，只能小 -> 大，hive1.2.2中没有限制，各种类型之间可以相互修改。

alter table 表名 change 原字段 新字段 新字段类型;
alter table 表名 change age age string;

3. 替换列

alter table 表名 replace columns(id int, name string);

4. 修改表分区信息

1. 添加分区，可以指定分区存储路径

alter table '分区表' add if not exists partition(`date`='xxx') location '/user/xxx';

2. 修改分区，可以修改分区存储路径

alter table '分区表' partition(`date`='xxx')' set location '/user/hive/warehouse/xx.db/xx_table/xxx';

3. 删除分区

alter table '分区表' drop if exists partition(`date`='xxx');