开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第6天，点击查看活动详情

第四章 DDL库操作

DDL： Data Definition Language     数据定义语言
DML： Data Manipulation Language   数据操纵语言
DQL ：Data Query Language 数据查询语言

分为两大类
    库 ：创建库、查看库、删除库、修改库、切换库  等
    表 ：创建表、查看表、删除表、修改表、查看某张表的详细信息 等

DDL操作官网链接：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Create/Drop/Alter/UseDatabase

4.1 创建库

语法结构：

CREATE [REMOTE] (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [MANAGEDLOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

具体案例：

1、 创建普通库 
create database myhivedb; 
hive (mydb)> create database myhivedb; 

2、 创建库的时候检查存与否 
create database if not exists myhivedb; 
hive (mydb)> create database if not exists myhivedb; 

3、 创建库的时候带注释 
create database if not exists myhivedb comment 'create my db named myhivedb'; 
hive (mydb)> create database if not exists myhivedb comment 'create my db named myhivedb'; 
OK
Time taken: 0.021 seconds

4、创建库的时候可以指定非默认路径
hive (mydb2)> create database if not exists myhivedb3 comment "myhivedb3 comment" location "/myhivedb3";

4.2 查看库

1、查看有哪些数据库

show databases;
hive (mydb)> show databases;
OK
database_name
default
mydb
myhivedb
Time taken: 0.015 seconds, Fetched: 3 row(s)

2、显示数据库的详细属性信息

语法：

desc  database  [extended]  dbname;

示例：

hive (mydb)> desc database myhivedb;
OK
db_name comment location    owner_name  owner_type  parameters
myhivedb        hdfs://hadoop10/user/hive/warehouse/myhivedb.db root    USER    
Time taken: 0.021 seconds, Fetched: 1 row(s)
hive (mydb)> desc database extended myhivedb;
OK
db_name comment location    owner_name  owner_type  parameters
myhivedb        hdfs://hadoop10/user/hive/warehouse/myhivedb.db root    USER    
Time taken: 0.02 seconds, Fetched: 1 row(s)

3、查看正在使用哪个库

select current_database(); 
hive (mydb)> select current_database();
OK
_c0
mydb
Time taken: 0.939 seconds, Fetched: 1 row(s)

4、过滤显示查询的数据库

hive (mydb)> show databases;
OK
database_name
default
mydb
myhivedb
Time taken: 0.018 seconds, Fetched: 3 row(s)
hive (mydb)> show databases like 'my*';
OK
database_name
mydb
myhivedb
Time taken: 0.022 seconds, Fetched: 2 row(s)
hive (mydb)> show databases like "my*";
OK
database_name
mydb
myhivedb
Time taken: 0.015 seconds, Fetched: 2 row(s)
hive (mydb)>

4.3 删除库

语法结构：

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

删除库一系列测试操作：

hive (mydb)> create database myhivedb2;
OK
Time taken: 0.043 seconds
hive (mydb)> use myhivedb2;
OK
Time taken: 0.017 seconds
hive (myhivedb2)> show tables;
OK
tab_name
Time taken: 0.02 seconds
hive (myhivedb2)> create table student(id int, name string, sex string, age int, department string) row format delimited fields terminated by ",";
OK
Time taken: 0.242 seconds
//其实不给数据添加进去，哪怕就是一个一个库下面只有一个空的表，也不能直接删除掉，需要加cascade强制删除。
hive (myhivedb2)> load data local inpath "/home/data/student.txt" into table student;
Loading data to table myhivedb2.student
OK
Time taken: 0.207 seconds
hive (myhivedb2)> drop database myhivedb2;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database myhivedb2 is not empty. One or more tables exist.)
hive (myhivedb2)> drop database myhivedb2 restrict;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database myhivedb2 is not empty. One or more tables exist.)
hive (myhivedb2)> drop database myhivedb2 cascade;
OK
Time taken: 0.24 seconds
hive (myhivedb2)>

默认情况下，hive 不允许删除包含表的数据库，有两种解决办法：

1、手动删除库下所有表，然后删除库

2、使用 cascade 关键字

drop database if exists dbname cascade;

默认情况下就是 restrict

drop database if exists myhive;
等价于 
drop database if exists myhive restrict;

4.4 切换库

切换库操作：

语法：

use database_name;

示例：

hive (myhivedb2)> use myhivedb;
OK
Time taken: 0.023 seconds

4.5 修改库

语法：

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- (Note: SCHEMA added in Hive 0.14.0)
 
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)
  
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)
 
ALTER (DATABASE|SCHEMA) database_name SET MANAGEDLOCATION hdfs_path; -- (Note: Hive 4.0.0 and later)

示例：

可以使用 ALTER DATABASE 命令为某个数据库的 DBPROPERTIES 设置键值对属性值，来描述这个数据库的属性信息

hive (myhivedb)> desc database extended myhivedb;
OK
db_name	comment	location	owner_name	owner_type	parameters
myhivedb		hdfs://hadoop10/user/hive/warehouse/myhivedb.db	root	USER	
Time taken: 0.027 seconds, Fetched: 1 row(s)
hive (myhivedb)> alter database myhivedb set dbproperties('createtime'='20210924');
OK
Time taken: 0.029 seconds
hive (myhivedb)> desc database extended myhivedb;
OK
db_name	comment	location	owner_name	owner_type	parameters
myhivedb		hdfs://hadoop10/user/hive/warehouse/myhivedb.db	root	USER	{createtime=20210924}
Time taken: 0.016 seconds, Fetched: 1 row(s)
hive (myhivedb)>

其实修改库很少使用啦。

声明：
文章中代码及相关语句为自己根据相应理解编写，文章中出现的相关图片为自己实践中的截图和相关技术对应的图片，若有相关异议，请联系删除。感谢。转载请注明出处，感谢。

落叶飘雪

Hive之DDL库操作