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');

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;