hive db的创建与删除 - 道行尚浅的道场

创建数据库

hive> create database dao_db ;
OK
Time taken: 1.506 seconds
hive> create schema dao_db_1 ;
OK
Time taken: 0.097 seconds

查看现有的数据库

hive> show databases ;
OK
dao_db
dao_db_1
default
Time taken: 0.437 seconds, Fetched: 3 row(s)

查看建库脚本

hive> show create database dao_db ;
OK
CREATE DATABASE `dao_db`
LOCATION
  'hdfs://mycluster/user/hive/warehouse/dao_db.db'
Time taken: 0.059 seconds, Fetched: 3 row(s)

hive> show create schema dao_db_1 ;
OK
CREATE DATABASE `dao_db_1`
LOCATION
  'hdfs://mycluster/user/hive/warehouse/dao_db_1.db'
Time taken: 0.055 seconds, Fetched: 3 row(s)

在此处发现，即使我们在命令是show create schema，但在系统返回中，返回的是create database

hive数据库的本质

hive数据库的本质其实是HDFS的目录与子目录，表将会以子目录的形式出现在库的目录中。

[root@node1 ~]# hdfs dfs -lsr /user/hive/warehouse/
lsr: DEPRECATED: Please use 'ls -R' instead.
drwxr-xr-x   - root supergroup          0 2020-08-24 15:06 /user/hive/warehouse/dao_db.db
drwxr-xr-x   - root supergroup          0 2020-08-24 15:06 /user/hive/warehouse/dao_db_1.db
drwxr-xr-x   - root supergroup          0 2020-08-22 02:58 /user/hive/warehouse/dao_t1
-rwxr-xr-x   3 root supergroup         11 2020-08-22 02:57 /user/hive/warehouse/dao_t1/000000_0

其中dao_t1是表，使用的default库

库与表

hive> use dao_db ;
OK
Time taken: 1.227 seconds
hive> create table t1 ( c1 int ) ;
OK
Time taken: 1.155 seconds
hive> show tables ;
OK
t1
Time taken: 0.362 seconds, Fetched: 1 row(s)
hive> show create table t1 ;
OK
CREATE TABLE `t1`(
  `c1` int)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://mycluster/user/hive/warehouse/dao_db.db/t1'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}',
  'numFiles'='0',
  'numRows'='0',
  'rawDataSize'='0',
  'totalSize'='0',
  'transient_lastDdlTime'='1598296765')
Time taken: 0.332 seconds, Fetched: 17 row(s)

查看HDFS文件组织形式

[root@node1 ~]# hdfs dfs -lsr /user/hive/warehouse/dao_db.db/
lsr: DEPRECATED: Please use 'ls -R' instead.
drwxr-xr-x   - root supergroup          0 2020-08-24 15:19 /user/hive/warehouse/dao_db.db/t1

删除数据库

在hive中只能删除空数据库。对于非空数据库可以使用cascade删除数据库

hive> drop database dao_db ;         -- 删除非空数据库引起报错
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database dao_db is not empty. One or more tables exist.)
hive> drop database dao_db_1  ;      -- 删除空数据库可以成功执行
OK
Time taken: 0.331 seconds

删除非空数据库

hive> create database d1 ;
OK
Time taken: 0.354 seconds
hive> create table d1.t1 (c1 int) ;
OK
Time taken: 1.169 seconds
hive> drop database d1 cascade ;
OK
Time taken: 5.737 seconds
 