Hive工作所遇问题之Hive -e命令中使用正则表达式问题

前言

今天工作因为之前建表时，看不到数据，导致建表的字段格式有问题，然后使用split函数拆分时，发现是正则表达式使用的问题。

下面来说明问题

一、数据准备

--创建码表：
create table hive_sql.d_type(
type_id string,
type string
)
stored as orc

--插入数据：
insert into table hive_sql.d_type
select '1','居民身份证' union  all
select '10','居民户口簿' union  all
select '11','驾驶证' union  all

--创建建表有问题的表：types 应该是字符串，但因为以前系统的人使用的浮点型
create table hive_sql.agent_type(
name string,
types decimal(22,3)
)
stored as orc

--插入数据
insert into table hive_sql.agent_type
select '张三',1
union all
select '李四',1

二、问题复现及解决方案

2.1、问题复现

2.1.1、源库（历史库）查询及问题说明

注：因为时间有限，就使用Hive中的表模拟

因为这个涉及到自动类型转换的问题，所以历史库或者Hive中都没有出现问题。但是实际工作过程中数据库的查询引擎是Impala，因为a表的types字段和b表的type_id类型不一致，必须使用cast转换，所以处理下面的问题：

types是1.000和1不匹配，导致张三和李四的证件类型都取不到，只能拆分：

这个能取到，没有任何问题，但是生产上用的是Hive -e的方式执行：

bin/hive -e "select a.name,b.type
 from (select a.*
      ,split(cast(a.types as string),'\\.')[0] types_new
 from hive_sql.agent_type a) a
  left join hive_sql.d_type b
   on a.types_new = b.type_id"

结果如下：证件类型又取不到了

2.2、问题原因说明

Hive 是基于 Java 的，而 Java 字符串中反斜杠 \ 本身需要转义。因此在 Hive 中，要表示正则表达式的 \\.，需要写成 \\\\.

当通过 hive -e 在命令行中执行时，Shell 会先解析参数中的引号和反斜杠。如果直接在 Shell 中写 \\.，Shell 会将其解析为 \.（实际传递到 Hive 的参数会丢失一个反斜杠）。因此需要额外增加一层转义：

• 在 Shell 中，\\\\. → 实际传递给 Hive 的字符串是 \\. → 在 Hive 中解析为 \. → 正则引擎最终视为 . 的转义。

修正后的逻辑：

bin/hive -e "select a.name,b.type
 from (select a.*
      ,split(cast(a.types as string),'\\\\.')[0] types_new
 from hive_sql.agent_type a) a
  left join hive_sql.d_type b
   on a.types_new = b.type_id"

结果：