Hive数据库的存储位置 & DDL

• 1. Hive数据库&表在HDFS中的存储位置
• 2. Hive元数据metadata在mysql数据库中的存储位置
• 3.Hive的DDL操作
• • 3.1.CREATE（新增）
  • 3.2.ALTER（修改）
  • 3.3.DROP（删除)

1. Hive数据库&表在HDFS中的存储位置

Hive对应的database 和 table都是对应分布式文件系统的一个路径：

• /user/hive/warehouse【即hive.metastore.warehouse.dir的文件夹路径地址】
• 在hive中默认有一个数据库：default
• 库、表、分区表 都是在hive.metastore.warehouse.dir这个目录下

（-）	默认数据库default	非default数据库
数据库位置	（-）	/user/hive/warehouse/数据库名字.db
新创建的表位置	/user/hive/warehouse/表名	/user/hive/warehouse/数据库名字.db/表名

2. Hive元数据metadata在mysql数据库中的存储位置

mysql> use myhive; // 在hive-site.xml配置的mysql数据库库名。
mysql> show tables;
+-------------------------------+
| Tables_in_myhive              |
+-------------------------------+
| aux_table                     |
| bucketing_cols                |
| cds                           |
| columns_v2                    |
| compaction_queue              |
| completed_compactions         |
| completed_txn_components      |
| ctlgs                         |
| database_params【存放元数据参数配置】              |
| db_privs                      |
| dbs     【存放metadata元数据】                  |
| delegation_tokens             |
| func_ru                       |

3.Hive的DDL操作

3.1.CREATE（新增）

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

[] ：可有可无
(DATABASE|SCHEMA) N选1操作

• 普通创建 & 数据库存储路径

[xiaofeng@maggie101 ~]$ beeline.sh
// 切换到hiveserver2下面创建myhive_test1.db数据库
0: jdbc:hive2://localhost:10000/> Create database myhive_test1;

// 查看/user/hive/warehouse/路径下的文件
[xiaofeng@maggie101 ~]$ hadoop fs -ls /user/hive/warehouse/
	Found 3 items
	drwxr-xr-x   - xiaofeng supergroup          0 2022-11-26 00:17 /user/hive/warehouse/myhive_test1.db
	drwxr-xr-x   - xiaofeng supergroup          0 2022-11-25 01:51 /user/hive/warehouse/test.db
	drwxr-xr-x   - xiaofeng supergroup          0 2022-11-25 11:04 /user/hive/warehouse/test_user

• 普通再次创建 & IF NOT EXISTS

0: jdbc:hive2://localhost:10000/> Create database myhive_test1;
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database myhive_test1 already exists (state=42000,code=1)

0: jdbc:hive2://localhost:10000/> Create database IF NOT EXISTS myhive_test1;
	No rows affected (0.03 seconds)

• 创建库的时候，同时指定LOCATION

0: jdbc:hive2://localhost:10000/>  Create database myhive_test3 LOCATION '/user/hive/myhive_test3';

• 创建库的时候，添加备注COMMENT

 Create database myhive_test4 COMMENT 'maggie创建的Hive测试数据库';
 
 Create database myhive_test5 COMMENT 'maggie创建的Hive测试数据库5' WITH DBPROPERTIES ('cretor'='maggie', 'date'='2022-11-26');

• 查看数据库

0: jdbc:hive2://localhost:10000/> show databases;
+----------------+
| database_name  |
+----------------+
| default        |
| myhive_test1   |
| myhive_test2   |
| myhive_test3   |
| myhive_test4   |
| myhive_test5   |
| test           |
+----------------+
7 rows selected (0.088 seconds)

0: jdbc:hive2://localhost:10000/> show databases like 'myhive*';
+----------------+
| database_name  |
+----------------+
| myhive_test1   |
| myhive_test2   |
| myhive_test3   |
| myhive_test4   |
| myhive_test5   |
+----------------+
5 rows selected (0.047 seconds)

• 查看元数据信息matadata

0: jdbc:hive2://localhost:10000/> desc database myhive_test1;

+---------------+----------+----------------------------------------------------+-------------+-------------+-------------+
|    db_name    | comment  |                      location                      | owner_name  | owner_type  | parameters  |
+---------------+----------+----------------------------------------------------+-------------+-------------+-------------+
| myhive_test1  |          | hdfs://maggie101:9000/user/hive/warehouse/myhive_test1.db | xiaofeng    | USER        |             |
+---------------+----------+----------------------------------------------------+-------------+-------------+-------------+
1 row selected (0.052 seconds)

0: jdbc:hive2://localhost:10000/> desc database extended myhive_test5;

+---------------+----------------------+----------------------------------------------------+-------------+-------------+-----------------------------------+
|    db_name    |       comment        |                      location                      | owner_name  | owner_type  |            parameters             |
+---------------+----------------------+----------------------------------------------------+-------------+-------------+-----------------------------------+
| myhive_test5  | maggie???Hive?????5  | hdfs://maggie101:9000/user/hive/warehouse/myhive_test5.db | xiaofeng    | USER        | {date=2022-11-26, cretor=maggie}  |
+---------------+----------------------+----------------------------------------------------+-------------+-------------+-----------------------------------+
1 row selected (0.036 seconds)
0: jdbc:hive2://localhost:10000/>

• 在mysql中元数据存放位置和具体数值

mysql> desc dbs;
+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| DB_ID           | bigint(20)    | NO   | PRI | NULL    |       |
| DESC            | varchar(4000) | YES  |     | NULL    |       |
| DB_LOCATION_URI | varchar(4000) | NO   |     | NULL    |       |
| NAME            | varchar(128)  | YES  | MUL | NULL    |       |
| OWNER_NAME      | varchar(128)  | YES  |     | NULL    |       |
| OWNER_TYPE      | varchar(10)   | YES  |     | NULL    |       |
| CTLG_NAME       | varchar(256)  | NO   | MUL | hive    |       |
+-----------------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> select * from dbs;
+-------+-----------------------+-----------------------------------------------------------+--------------+------------+------------+-----------+
| DB_ID | DESC                  | DB_LOCATION_URI                                           | NAME         | OWNER_NAME | OWNER_TYPE | CTLG_NAME |
+-------+-----------------------+-----------------------------------------------------------+--------------+------------+------------+-----------+
|     1 | Default Hive database | hdfs://maggie101:9000/user/hive/warehouse                 | default      | public     | ROLE       | hive      |
|     2 | NULL                  | hdfs://maggie101:9000/user/hive/warehouse/test.db         | test         | xiaofeng   | USER       | hive      |
|     6 | NULL                  | hdfs://maggie101:9000/user/hive/warehouse/myhive_test1.db | myhive_test1 | xiaofeng   | USER       | hive      |
|     7 | NULL                  | hdfs://maggie101:9000/user/hive/warehouse/myhive_test2.db | myhive_test2 | xiaofeng   | USER       | hive      |
|     8 | NULL                  | hdfs://maggie101:9000/user/hive/myhive_test3              | myhive_test3 | xiaofeng   | USER       | hive      |
|     9 | maggie???Hive?????    | hdfs://maggie101:9000/user/hive/warehouse/myhive_test4.db | myhive_test4 | xiaofeng   | USER       | hive      |
|    10 | maggie???Hive?????5   | hdfs://maggie101:9000/user/hive/warehouse/myhive_test5.db | myhive_test5 | xiaofeng   | USER       | hive      |
+-------+-----------------------+-----------------------------------------------------------+--------------+------------+------------+-----------+
7 rows in set (0.00 sec)

mysql>

• 存放comment参数

mysql> desc database_params ;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| DB_ID       | bigint(20)    | NO   | PRI | NULL    |       |
| PARAM_KEY   | varchar(180)  | NO   | PRI | NULL    |       |
| PARAM_VALUE | varchar(4000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from database_params  ;
+-------+-----------+-------------+
| DB_ID | PARAM_KEY | PARAM_VALUE |
+-------+-----------+-------------+
|    10 | cretor    | maggie      |
|    10 | date      | 2022-11-26  |
+-------+-----------+-------------+
2 rows in set (0.00 sec)

3.2.ALTER（修改）

ALTER DATABASE myhive_test5 SET DBPROPERTIES ('updatetime'='30000101'); 

3.3.DROP（删除)

DROP DATABASE pk_hivedfasdfafasdfdasfas ;         【不存在的时候，会报错】
DROP DATABASE IF EXISTS pk_hivedfasdfafasdfdasfas ;     【加上IF EXISTS的时候，就不会报错】
DROP DATABASE IF EXISTS pk_hivedfasdfafasdfdasfas  CASCADE;  【CASCADE级联删除，有点危险】