Hive Database 操作

1. 创建数据库

1.1 在默认位置创建数据库

CREATE DATABASE d1;

1.2 LOCATION

CREATE DATABASE d2 LOCATION 'file:/user/hive/warehouse/d2.db';

1.3 WITH DBPROPERTIES

CREATE DATABASE d3 WITH DBPROPERTIES('creator' = 'houzhizhen') ;

1.4 加上 IF NOT EXISTS

CREATE DATABASE  IF NOT EXISTS d4;

1.5 加上 COMMENT

CREATE DATABASE  d5 COMMENT 'comment for d5';

1.6 综合

CREATE DATABASE  IF NOT EXISTS d6 COMMENT 'comment for d6' LOCATION 'file:/user/hive/warehouse/d6.db'  WITH DBPROPERTIES('creator' = 'houzhizhen');

3. ALTER DATABASE

hive>alter database d6  SET DBPROPERTIES('creator' = 'houzhizhen','comment' = 'comment for d6');

2. DESCRIBE DATABASE

hive> DESCRIBE DATABASE d6;
OK
d6	comment for d6	file:/user/hive/warehouse/d6.db	houzhizhen	USER	
Time taken: 0.053 seconds, Fetched: 1 row(s)

hive> DESCRIBE DATABASE EXTENDED d6;
OK
d6	comment for d6	file:/user/hive/warehouse/d6.db	houzhizhen	USER	{creator=houzhizhen}
Time taken: 0.064 seconds, Fetched: 1 row(s)

3. SHOW CREATE DATABASE

hive> SHOW CREATE DATABASE D6;
OK
CREATE DATABASE `d6`
COMMENT
  'comment for d6'
LOCATION
  'file:/user/hive/warehouse/d6.db'
WITH DBPROPERTIES (
  'creator'='houzhizhen')
  Time taken: 0.048 seconds, Fetched: 7 row(s)

4. SHOW CREATE DATABASE

hive> SHOW CREATE DATABASE d6;
OK
CREATE DATABASE `d6`
COMMENT
  'comment for d6'
LOCATION
  'file:/user/hive/warehouse/d6.db'
WITH DBPROPERTIES (
  'creator'='houzhizhen')
Time taken: 0.051 seconds, Fetched: 7 row(s)

5. SHOW DATABASES;

hive> SHOW DATABASES;
OK
d1
d2
d3
d4
d5
d6
default
Time taken: 0.041 seconds, Fetched: 7 row(s)

hive>  SHOW DATABASES LIKE '*4*';
OK
d4
Time taken: 0.047 seconds, Fetched: 1 row(s)

6. DROP DATABASE

hive> DROP DATABASE d1;
OK
Time taken: 0.044 seconds

如果库下有表，则会失败，可以用 CASCADE 级联删除

hive> use d2;
OK
Time taken: 0.03 seconds

hive> create table t1(c1 string);
OK
Time taken: 0.467 seconds

hive> drop database d2;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database d2 is not empty. One or more tables exist.)

hive> drop database d2 cascade;
OK
Time taken: 0.106 seconds

DROP DATABASE RESTRICT

只有库下没有表才会成功。

hive>  DROP DATABASE d3 RESTRICT;
OK
Time taken: 0.038 seconds

7. USE

切换当前数据库。

USE d4;

SCHEMA 和 DATABASE 可以互换

CREATE SCHEMA d7;

等价于

CREATE DATABASE d7;