Hive(三)【DDL 数据定义】
目录
一.DDL数据定义
1.库的DDL
1.1创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name --指定数据库名称
[COMMENT database_comment] --指定数据库描述
[LOCATION hdfs_path] --指定创建的数据库在hdfs上存储的路径
[WITH DBPROPERTIES (property_name=property_value, ...)]; --指定库的一些属性
案例实操
create database if not exists db_hive
comment "this is my first db"
with dbproperties ("name"="db_hive","owner"="atguigu");
create database if not exists db_hive;
--创建数据库，并指定在hdfs上的路径
create database if not exists db_hive2
location '/db_hive2';
1.2查询数据库
show databases；
1.3查看数据库详情
--简单查看
desc database 数据库名;
--详细查看 （详细查看可以看到库的属性信息，简单查看看不到）
desc database extended 数据库名;
1.4切换数据库
use 数据库名;
1.5修改数据库
只能修改数据库的属性信息，别的都无法更改。例如：库名，库的存储位置等元数据信息无法更改。
案例实操
alter database db_hive set dbproperties('createtime'='20200624');
1.6删除数据库
drop database 数据库名;
--如果数据库不为空，可以在最后加上cascade强制删除
drop database 数据库名 cascade;
--为了更严谨，我们可以在删除之前判断数据库是否存在
drop database if exists 数据库名 cascade;
注意：删除掉数据库以后，hdfs上对应的目录也会删除，谨慎操作。
2.表的DDL
2.1创建表
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name --指定表名 【external 外部表/内部表】
[(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 DELIMITED --指定hive表在hdfs上存储的原始数据的格式
[FIELDS TERMINATED BY char] --每行数据中字段的分隔符 ascII码表的第一个字符 ^A
[COLLECTION ITEMS TERMINATED BY char] --集合元素分隔符 ascII码表的第二个字符 ^B
[MAP KEYS TERMINATED BY char] --map集合中 key 和 value 的分隔符 ascII码表的第三个字符 ^C
[LINES TERMINATED BY char] --每行数据的分隔符 默认值：'\n'
]
[STORED AS file_format] --指定hive的数据在hdfs上存储的格式
[LOCATION hdfs_path] --指定hive数据在hdfs上存储的路径
[TBLPROPERTIES (property_name=property_value, ...)] --指定表的属性
[AS select_statement] --按照as后面的查询语句的结果来创建表，复制表结构以及表数据
[LIKE table_name] --按照like后面的表结构来创建表，只复制表结构，不复制表数据
2.2管理表(内部表)和外部表
管理表（内部表）：hive掌控者这个数据的生命周期，如果删除一个管理表，hdfs上存储的数据也跟着一起删除。所以一般我们创建管理表时，一般不会再location表的存储路径，就默认放在/user/hive/warehouse下
外部表：hive不完全掌控外部表的数据的生命周期，删除外部表，只删除hive表的元数据，不会删除掉hdfs上存储的数据
一般外部表都是先有的hdfs上的数据，然后我们创建一个外部表，手动指定这个外部表的存储路径。
2.3创建表的三种方式
方式一：直接创建
create table student(
id int,name string
)
row format delimited fields terminated by '\t';
方式二：根据AS select语句查询结构创建表,复制表结构，复制表数据
create table student3 as select * from student;
方式三：根据like 创建表，只复制表结构，不复制表数据
create table student4 like student;
2.4创建管理表、外部表
管理表：不加external关键字
create table student(
id int,name string
)
row format delimited fields terminated by '\t';
外部表：
create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t'
location '/company/dept';
2.5外部表和内部表转换
通过设置表属性“EXTERNAL”来控制表是外部表还是内部表
TRUE 是 外部表 FALSE 是内部表
alter table student set tblproperties('EXTERNAL'='TRUE/FALSE');
2.6查看表结构
--简单查看表信息
desc 表名;
--详细查看表信息
desc formatted 表名;
2.7修改表
重命名表
ALTER TABLE table_name RENAME TO new_table_name
案例：alter table student2 rename to student3
更新列
列名可以随意修改，列的类型只能小改大，不能大改小（遵循自动转换规则）
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
案例：alter table dept change column deptdesc desc string;
增加列
ADD是代表新增一字段，字段位置在所有列后面(partition列前)
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)
案例：alter table dept add columns(deptdesc string);
替换列
REPLACE则是表示替换表中所有字段，REPLACE使用的时候，字段的类型要跟之前的类型对应上，数量可以减少或者增加，其实就是包含了更新列，增加列，删除列的功能。
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
案例：alter table dept replace columns(deptno string, dname，string, loc string);
2.8删除表
drop table dept;
2.9清空表数据（Truncate）
注意：Truncate只能删除管理表，不能删除外部表中数据
truncate table student;
