Hive数据库的存储位置 & DDL

2025-11-13 01:55:02 8394

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;

+---------------+----------+----------------------------------------------------+-------------+-------------+-------------+

+---------------+----------+----------------------------------------------------+-------------+-------------+-------------+

+---------------+----------+----------------------------------------------------+-------------+-------------+-------------+

1 row selected (0.052 seconds)

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

+---------------+----------------------+----------------------------------------------------+-------------+-------------+-----------------------------------+

+---------------+----------------------+----------------------------------------------------+-------------+-------------+-----------------------------------+

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

+-----------------+---------------+------+-----+---------+-------+

+-----------------+---------------+------+-----+---------+-------+

+-----------------+---------------+------+-----+---------+-------+

7 rows in set (0.00 sec)

mysql> select * from dbs;

+-------+-----------------------+-----------------------------------------------------------+--------------+------------+------------+-----------+

+-------+-----------------------+-----------------------------------------------------------+--------------+------------+------------+-----------+

+-------+-----------------------+-----------------------------------------------------------+--------------+------------+------------+-----------+

7 rows in set (0.00 sec)

mysql>

存放comment参数

mysql> desc database_params ;

+-------------+---------------+------+-----+---------+-------+

+-------------+---------------+------+-----+---------+-------+

+-------------+---------------+------+-----+---------+-------+

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级联删除，有点危险】

Android --- .gradle文件夹的路径
冲浪高手辛吉德皮肤

Hive数据库的存储位置 & DDL

热门文章

友情链接