Hive comment中文乱码解决

zrxrz

已于 2024-02-20 22:53:24 修改

阅读量607

点赞数 7

分类专栏： bigdata 文章标签： hive hadoop 数据仓库

于 2024-02-20 22:51:53 首次发布

本文链接：https://blog.csdn.net/llllalune/article/details/136200186

版权

bigdata 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本文介绍了Hive中由于底层MySQL编码限制导致中文注释乱码的问题，提供了解决方案，包括修改表的列和参数字符集，并指出对已有乱码表的操作可能无效，需先删除并重建表。

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

文章目录

1 原因分析

由于hive底层的运行机制，即把元数据存储在MySQL中，但是MySQL的默认编码是支持Latin1的编码，不支持中文编码。

2 解决方案

首先就是进入MySQL

[hadoop@node1 ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 103
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hive               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

数据库hive中的表就存储了hive的各种元数据信息

mysql> use hive;
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_hive                |
+-------------------------------+
| AUX_TABLE                     |
| BUCKETING_COLS                |
| CDS                           |
| COLUMNS_V2                    |
| COMPACTION_QUEUE              |
| COMPLETED_COMPACTIONS         |
| COMPLETED_TXN_COMPONENTS      |
| CTLGS                         |
| DATABASE_PARAMS               |
| DBS                           |
| DB_PRIVS                      |
| DELEGATION_TOKENS             |
| FUNCS                         |
| FUNC_RU                       |
| GLOBAL_PRIVS                  |
| HIVE_LOCKS                    |
| IDXS                          |
| INDEX_PARAMS                  |
| I_SCHEMA                      |
| KEY_CONSTRAINTS               |
| MASTER_KEYS                   |
| MATERIALIZATION_REBUILD_LOCKS |
| METASTORE_DB_PROPERTIES       |
| MIN_HISTORY_LEVEL             |
| MV_CREATION_METADATA          |
| MV_TABLES_USED                |
| NEXT_COMPACTION_QUEUE_ID      |
| NEXT_LOCK_ID                  |
| NEXT_TXN_ID                   |
| NEXT_WRITE_ID                 |
| NOTIFICATION_LOG              |
| NOTIFICATION_SEQUENCE         |
| NUCLEUS_TABLES                |
| PARTITIONS                    |
| PARTITION_EVENTS              |
| PARTITION_KEYS                |
| PARTITION_KEY_VALS            |
| PARTITION_PARAMS              |
| PART_COL_PRIVS                |
| PART_COL_STATS                |
| PART_PRIVS                    |
| REPL_TXN_MAP                  |
| ROLES                         |
| ROLE_MAP                      |
| RUNTIME_STATS                 |
| SCHEMA_VERSION                |
| 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                     |
| TXNS                          |
| TXN_COMPONENTS                |
| TXN_TO_WRITE_ID               |
| TYPES                         |
| TYPE_FIELDS                   |
| VERSION                       |
| WM_MAPPING                    |
| WM_POOL                       |
| WM_POOL_TO_TRIGGER            |
| WM_RESOURCEPLAN               |
| WM_TRIGGER                    |
| WRITE_SET                     |
+-------------------------------+
74 rows in set (0.00 sec)

mysql> alter table hive.COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
Query OK, 106 rows affected (0.04 sec)
Records: 106  Duplicates: 0  Warnings: 0

mysql> alter table hive.TABLE_PARAMS modify column PARAM_VALUE varchar (4000) character set utf8;
Query OK, 101 rows affected (0.03 sec)
Records: 101  Duplicates: 0  Warnings: 0

mysql> alter table hive.PARTITION_PARAMS modify column PARAM_VALUE varchar (4000) character set utf8;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table hive.PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table hive.INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

3 补充

DESC FORMATTED tblname是Hive中用于查看表的详细信息的命令。它会显示表的元数据信息，包括表的结构、存储信息、分区信息、注释(comment)信息等。如果MySQL不支持中文编码，当注释是中文时，就会乱码。
对于是在MySQL不支持中文编码时创建的表，在进行如上操作后，原来的表的中文注释仍然会乱码。需要先使用DROP TABLE tblname删除该表，再重新创建该表，才能正常显示