hive mysql 编码问题

在安装Hive时,经常报异常。
后来采用输出日志的方式来开启hive:hive -hiveconf hive.root.logger=DEBUG,console
这样在执行命令时,就会输出日志,出现异常可以很快定位。

ERROR DataNucleus.Datastore: Error thrown executing CREATE TABLE SD_PARAMS
(
SD_ID BIGINT NOT NULL,
PARAM_KEY VARCHAR(256) BINARY NOT NULL,
PARAM_VALUE VARCHAR(4000) BINARY NULL,
CONSTRAINT SD_PARAMS_PK PRIMARY KEY (SD_ID,PARAM_KEY)
) ENGINE=INNODB : Specified key was too long; max key length is 767 bytes
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

这是字符集的问题,在数据库上执行 alter database hive character set latin1;来修改字符集,问题解决。

由于正常的情况,为了防止出现乱码,我们需要 将其设置为utf-8.

搭建 hive mysql 的步骤 建议按照以下三步:

1)新配置hive的时候,不要去设定编码为utf8,让hive自动新建mysql中的数据库hive(这点特别重要)。

2)检查发现hive库已经建成的时候,查看hive库的编码是latin1

3)修改hive库为utf8,修改hive库中的所有的表为utf8,还要修改hive库中所有表的所有列为utf8,设置mysql的默认编码为utf8 

alter database hive default character set utf8;

修改表的编码

alter table BUCKETING_COLS default character set utf8;

alter table CDS default character set utf8;

alter table COLUMNS_V2 default character set utf8;

alter table DATABASE_PARAMS default character set utf8;

alter table DBS default character set utf8;

alter table FUNCS default character set utf8;

alter table FUNC_RU default character set utf8;

alter table GLOBAL_PRIVS default character set utf8;

alter table PARTITIONS default character set utf8;

alter table PARTITION_KEYS default character set utf8;

alter table PARTITION_KEY_VALS default character set utf8;

alter table PARTITION_PARAMS default character set utf8;

alter table PART_COL_STATS default character set utf8;

alter table ROLES default character set utf8;

alter table SDS default character set utf8;

alter table SD_PARAMS default character set utf8;

alter table SEQUENCE_TABLE default character set utf8;

alter table SERDES default character set utf8;

alter table SERDE_PARAMS default character set utf8;

alter table SKEWED_COL_NAMES default character set utf8;

alter table SKEWED_COL_VALUE_LOC_MAP default character set utf8;

alter table SKEWED_STRING_LIST default character set utf8;

alter table SKEWED_STRING_LIST_VALUES default character set utf8;

alter table SKEWED_VALUES default character set utf8;

alter table SORT_COLS default character set utf8;

alter table TABLE_PARAMS default character set utf8;

alter table TAB_COL_STATS default character set utf8;

alter table TBLS default character set utf8;

alter table VERSION default character set utf8;

修改列

alter table BUCKETING_COLS convert to character set utf8;

alter table CDS convert to character set utf8;

alter table COLUMNS_V2 convert to character set utf8;

alter table DATABASE_PARAMS convert to character set utf8;

alter table DBS convert to character set utf8;

alter table FUNCS convert to character set utf8;

alter table FUNC_RU convert to character set utf8;

alter table GLOBAL_PRIVS convert to character set utf8;

alter table PARTITIONS convert to character set utf8;

alter table PARTITION_KEYS convert to character set utf8;

alter table PARTITION_KEY_VALS convert to character set utf8;

alter table PARTITION_PARAMS convert to character set utf8;

alter table PART_COL_STATS convert to character set utf8;

alter table ROLES convert to character set utf8;

alter table SDS convert to character set utf8;

alter table SD_PARAMS convert to character set utf8;

alter table SEQUENCE_TABLE convert to character set utf8;

alter table SERDES convert to character set utf8;

alter table SERDE_PARAMS convert to character set utf8;

alter table SKEWED_COL_NAMES convert to character set utf8;

alter table SKEWED_COL_VALUE_LOC_MAP convert to character set utf8;

alter table SKEWED_STRING_LIST convert to character set utf8;

alter table SKEWED_STRING_LIST_VALUES convert to character set utf8;

alter table SKEWED_VALUES convert to character set utf8;

alter table SORT_COLS convert to character set utf8;

alter table TABLE_PARAMS convert to character set utf8;

alter table TAB_COL_STATS convert to character set utf8;

alter table TBLS convert to character set utf8;

alter table VERSION convert to character set utf8;

mysql> SET character_set_client = utf8 ;
Query OK, 0 rows affected (0.02 sec)

mysql> SET character_set_connection = utf8 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET character_set_database = utf8 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET character_set_results = utf8 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET character_set_server = utf8 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET collation_connection = utf8 ;
ERROR 1273 (HY000): Unknown collation: ‘utf8’
mysql> SET collation_database = utf8 ;
ERROR 1273 (HY000): Unknown collation: ‘utf8’
mysql> SET collation_server = utf8 ;
ERROR 1273 (HY000): Unknown collation: ‘utf8’
mysql> SET NAMES ‘utf8’;
Query OK, 0 rows affected (0.00 sec)

————————————————————————
转载自:https://www.it610.com/article/5370773.htm

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值