关于hive的COMMENT 中文乱码问题_string comment-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_40808283/article/details/109615551

#关于hive的COMMENT 中文乱码问题

你好！这是第一次写blog不足之处请多多谅解

hive> create external table ods_payment_info(
    >     `id`   bigint COMMENT '编号',
    >     `out_trade_no`    string COMMENT '对外业务编号',
    >     `order_id`        string COMMENT '订单编号',
    >     `user_id`         string COMMENT '用户编号',
    >     `alipay_trade_no` string COMMENT '支付宝交易流水编号',
    >     `total_amount`    decimal(16,2) COMMENT '支付金额',
    >     `subject`         string COMMENT '交易内容',
    >     `payment_type`    string COMMENT '支付类型',
    >     `payment_time`    string COMMENT '支付时间'
    >    )  COMMENT '支付流水表'
    > PARTITIONED BY (`dt` string)
    > row format delimited fields terminated by '\t'
    > location '/warehouse/gmall/ods/ods_payment_info/'
    > ;
OK
Time taken: 0.309 seconds
hive> desc ods_payment_info;
OK
id                  	bigint              	??                  
out_trade_no        	string              	??????              
order_id            	string              	????                
user_id             	string              	????                
alipay_trade_no     	string              	?????????           
total_amount        	decimal(16,2)       	????                
subject             	string              	????                
payment_type        	string              	????                
payment_time        	string              	????                
dt                  	string

1.编辑配置文件添加如下代码：
init_connect=‘SET collation_connection = utf8_unicode_ci’
init_connect=‘SET NAMES utf8’
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

[root@cdh02 etc]# vim /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

##########添加一下代码##########
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
########################################

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

2.进入MySQL中：修改MySQL中的 metastore
a.cdh版
[root@cdh02 ~]# mysql -uroot -p******

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hive               |
| hue                |
+--------------------+

mysql> use hive;(cdh版本)
mysql> show tables;
+---------------------------+
| Tables_in_hive            |
+---------------------------+
| AUX_TABLE                 |
| BUCKETING_COLS            |
| CDH_VERSION               |
| CDS                       |
| COLUMNS_V2                |
| COMPACTION_QUEUE          |
| COMPLETED_COMPACTIONS     |
| COMPLETED_TXN_COMPONENTS  |
| DATABASE_PARAMS           |
| DBS                       |
| DB_PRIVS                  |
| DELEGATION_TOKENS         |
| SKEWED_VALUES             |
| SORT_COLS                 |
| TABLE_PARAMS              |
| TAB_COL_STATS             |
| TBLS                      |
| TBL_COL_PRIVS             |
.
.
.
| TBL_PRIVS                 |
| TXNS                      |
| TXN_COMPONENTS            |
| TYPES                     |
| TYPE_FIELDS               |
| VERSION                   |
| WRITE_SET                 |
+---------------------------+
mysql> alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;

mysql> alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

mysql> alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;

mysql> alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;

mysql> alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

b.apache(apache版本)
[root@hadoop102 ~]# mysql -uroot -p******

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| company            |
| gmall              |
| kettle             |
| metastore          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql> use metastore;
mysql> alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;

mysql> alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

mysql> alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;

mysql> alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;

mysql> alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

[root@cdh02 ~]# systemctl restart mysqld
进入hive中测试（删除之前建立的表）

[root@cdh02 ~]# hive

hive (test)>
drop table if exists ods_payment_info;
create external table ods_payment_info(
    `id`   bigint COMMENT '编号',
    `out_trade_no`    string COMMENT '对外业务编号',
    `order_id`        string COMMENT '订单编号',
    `user_id`         string COMMENT '用户编号',
    `alipay_trade_no` string COMMENT '支付宝交易流水编号',
    `total_amount`    decimal(16,2) COMMENT '支付金额',
    `subject`         string COMMENT '交易内容',
    `payment_type`    string COMMENT '支付类型',
    `payment_time`    string COMMENT '支付时间'
   )  COMMENT '支付流水表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ods/ods_payment_info/'
;


hive> desc ods_payment_info;
OK
id                  	bigint              	编号                  
out_trade_no        	string              	对外业务编号              
order_id            	string              	订单编号                
user_id             	string              	用户编号                
alipay_trade_no     	string              	支付宝交易流水编号           
total_amount        	decimal(16,2)       	支付金额                
subject             	string              	交易内容                
payment_type        	string              	支付类型                
payment_time        	string              	支付时间                
dt                  	string