Hive遇到的坑点_mysql中dbs 和sds 有 hive 的元数据信息,修改了hdfs的端口-CSDN博客

本文链接：https://blog.csdn.net/csdn_learner7/article/details/88304948

在使用Hive时遇到因HDFS路径不一致导致的SemanticException错误。通过登录Hive客户端，发现问题源于集群Namenode端口号变更。为解决此问题，对Hive元数据库MySQL中的DBS和SDS表进行更新，特别是调整LOCATION字段的HDFS路径。执行SQL更新SDS表的LOCATION字段后，Hive表信息查询恢复正常。

摘要由CSDN通过智能技术生成

1.登录hive客户端，查询表信息出现以下错误

hive (hive_db)> select * from star;
FAILED: SemanticException Unable to determine if hdfs://bigdata11:50090/user/hive/warehouse/hive_db.db/star is encrypted: java.lang.IllegalArgumentException: Wrong FS: hdfs://bigdata11:50090/user/hive/warehouse/hive_db.db/star, expected: hdfs://bigdata11:9000

查询报错信息后，发现是原先修改了集群namenode的端口号，导致hive在hdfs路径不一致的问题

2.解决方案修改hive的元数据库MySQL

hive元数据库MySQL存储了hive的元数据信息，以下修改DBS、SDS两个表

DBS：存储了hive的database信息

SDS：存储输入输出format等信息，包括表的format和分区的format。关联字段CD_ID,SERDE_ID

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| metastore          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> use metastore;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_metastore       |
+---------------------------+
| BUCKETING_COLS            |
| CDS                       |
| COLUMNS_V2                |
| DATABASE_PARAMS           |
| DBS                       |
| DB_PRIVS                  |
| FUNCS                     |
| FUNC_RU                   |
| GLOBAL_PRIVS              |
| IDXS                      |
| INDEX_PARAMS              |
| PARTITIONS                |
| PARTITION_KEYS            |
| PARTITION_KEY_VALS        |
| PARTITION_PARAMS          |
| PART_COL_PRIVS            |
| PART_COL_STATS            |
| PART_PRIVS                |
| ROLES                     |
| SDS                       |
| SD_PARAMS                 |
| SEQUENCE_TABLE            |
| SERDES                    |
| SERDE_PARAMS              |
| SKEWED_COL_NAMES          |
| SKEWED_COL_VALUE_LOC_MAP  |
| SKEWED_STRING_LIST        |
| SKEWED_STRING_LIST_VALUES |
| SKEWED_VALUES             |
| SORT_COLS                 |
| TABLE_PARAMS              |
| TAB_COL_STATS             |
| TBLS                      |
| TBL_COL_PRIVS             |
| TBL_PRIVS                 |
| VERSION                   |
+---------------------------+
36 rows in set (0.00 sec)

2.1 修改DBS

mysql> mysql> select * from DBS;
+-------+-----------------------+-----------------------------------------------------------+-------------+------------+------------+
| DB_ID | DESC                  | DB_LOCATION_UR