Hive1.1.0升级至2.3.6 踩坑记录

1、union all 左右字段类型不匹配

Hive尝试跨Hive类型组执行隐式转换。隐式转换支持类型如下表：

例：

hive> select 1 as c2,    2 as c2
    > union all
    > select 1.0 as c1, "2" as c1;
FAILED: SemanticException Schema of both sides of union should match: 
Column _col1 is of type int on first table and type string on second table. 
Cannot tell the position of null AST.

需要cast(“2” as int)强制将"2"转换为int

2、date_add和date_sub函数返回类型不再是string而是date

这会导致在使用自己写的一些UDF时，抛类型不匹配异常。

3、union all之间不能有order by，order by只能出现在union all之后

虽然order by之后再union all也没什么意义，但是就是有这种SQL出现在线上环境。

3、表/字段名或别名与保留字冲突

以下保留字若需要作为表/字段名或别名，需要加上反引号``

ALL, ALTER, AND, ARRAY, AS, AUTHORIZATION, BETWEEN, BIGINT, BINARY, BOOLEAN, BOTH, BY, CASE, CAST, CHAR, COLUMN, CONF, CREATE, CROSS, CUBE, CURRENT, CURRENT_DATE, CURRENT_TIMESTAMP, CURSOR, DATABASE, DATE, DECIMAL, DELETE, DESCRIBE, DISTINCT, DOUBLE, DROP, ELSE, END, EXCHANGE, EXISTS, EXTENDED, EXTERNAL, FALSE, FETCH, FLOAT, FOLLOWING, FOR, FROM, FULL, FUNCTION, GRANT, GROUP, GROUPING, HAVING, IF, IMPORT, IN, INNER, INSERT, INT, INTERSECT, INTERVAL, INTO, IS, JOIN, LATERAL, LEFT, LESS, LIKE, LOCAL, MACRO, MAP, MORE, NONE, NOT, NULL, OF, ON, OR, ORDER, OUT, OUTER, OVER, PARTIALSCAN, PARTITION, PERCENT, PRECEDING, PRESERVE, PROCEDURE, RANGE, READS, REDUCE, REVOKE, RIGHT, ROLLUP, ROW, ROWS, SELECT, SET, SMALLINT, TABLE, TABLESAMPLE, THEN, TIMESTAMP, TO, TRANSFORM, TRIGGER, TRUE, TRUNCATE, UNBOUNDED, UNION, UNIQUEJOIN, UPDATE, USER, USING, UTC_TMESTAMP, VALUES, VARCHAR, WHEN, WHERE, WINDOW, WITH, COMMIT, ONLY, REGEXP, RLIKE, ROLLBACK, START, CACHE, CONSTRAINT, FOREIGN, PRIMARY, REFERENCES, DAYOFWEEK, EXTRACT, FLOOR, INTEGER, PRECISION, VIEWS

4、设置参数大小写敏感

5、不指定类型 null类型已不兼容

6、元数据兼容升级

对Hive1.1元数据库进行以下操作，然后mysqldump -t导出数据，再删库重新用Hive2.3 schemaTool初始化元数据，最后将数据导入即可。

alter table partitions drop foreign key PARTITIONS_FK3;
alter table partitions drop link_target_id;
alter table tbls drop foreign key TBLS_FK3;
alter table tbls drop link_target_id;
alter table tbls drop OWNER_TYPE;
update roles set role_name=lower(role_name);

alter table tbls add `IS_REWRITE_ENABLED` bit(1) NOT NULL default '';

alter table notification_log add `MESSAGE_FORMAT` varchar(16) DEFAULT NULL;

drop table auth_user;
drop table tmp_user_map_d;
drop table version;

CREATE TABLE `key_constraints` (
`CHILD_CD_ID` bigint(20) DEFAULT NULL,
`CHILD_INTEGER_IDX` int(11) DEFAULT NULL,
`CHILD_TBL_ID` bigint(20) DEFAULT NULL,
`PARENT_CD_ID` bigint(20) NOT NULL,
`PARENT_INTEGER_IDX` int(11) NOT NULL,
`PARENT_TBL_ID` bigint(20) NOT NULL,
`POSITION` bigint(20) NOT NULL,
`CONSTRAINT_NAME` varchar(400) NOT NULL,
`CONSTRAINT_TYPE` smallint(6) NOT NULL,
`UPDATE_RULE` smallint(6) DEFAULT NULL,
`DELETE_RULE` smallint(6) DEFAULT NULL,
`ENABLE_VALIDATE_RELY` smallint(6) NOT NULL,
PRIMARY KEY (`CONSTRAINT_NAME`,`POSITION`),
KEY `CONSTRAINTS_PARENT_TABLE_ID_INDEX` (`PARENT_TBL_ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

7、对于用户角色，Hive2.3会将所有新建角色全转为小写存入mysql，而在鉴权的时候，又是直接用=比较的。这样就会导致之前Hive1.1中有大写字符的roles在鉴权的时候会失败。

异常如下：

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Error granting roles for A to role B: null

所以需要将roles表的role_name全转为小写:

update roles set role_name=lower(role_name)

8、SQL中单引号内的双引号个数若为奇数，则需要转义

如:'date":"(.+)"}'出现在SQL中会报<EOF>的错误
需要改成'date\":\"(.+)\