Hive数据库的存储位置 & DDL

Hive数据库的存储位置 & DDL

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