Hive不使用upper/lower实现大小写不敏感关联的问题咨询

业务需求为跨系统实现大小写不敏感关联查询，要求不使用upper()/lower()函数对字段做转换处理。
尝试在表级设置TBLPROPERTIES('serialization.encoding'='utf8mb4_unicode_ci')参数后，字符串比较仍区分大小写，复现代码如下：

drop table test.caseI;
create table test.caseI
(name string, id int)
TBLPROPERTIES('serialization.encoding'='utf8mb4_unicode_ci');
insert into test.caseI values ('hj',1);

drop table test.caseI_2;
create table test.caseI_2
(name string, id int)
TBLPROPERTIES('serialization.encoding'='utf8mb4_unicode_ci');
insert into test.caseI_2 values ('HJ',1);

select * from test.caseI i
inner join test.caseI_2 i2 on i.name=i2.name;
-- 执行后无返回结果

将编码参数替换为'SQL_Latin1_General_CP1_CI_AI'后重试，仍然得到相同的空结果。

serialization.encoding参数仅控制表数据序列化、反序列化阶段的字符集解析规则，不参与字符串等值比较、排序的逻辑判断，因此设置带_ci后缀的编码值无法改变默认大小写敏感的比较行为。
主流开源大数据查询引擎（原生Hive、Spark SQL等）默认字符串比较逻辑始终为大小写敏感，表级TBLPROPERTIES无内置配置项可直接修改默认比较规则。

方案1：字段级指定排序规则（推荐，无额外性能损耗） #

Hive 3.0+、Spark 3.4+ 及以上版本已原生支持字段级排序规则（Collation）配置，建表时直接为字符串字段指定大小写不敏感规则，后续所有关联、过滤操作自动忽略大小写，无需调用任何转换函数：

-- Hive 3.0+ 建表示例
create table test.caseI
(name string collate utf8mb4_general_ci, id int);
insert into test.caseI values ('hj',1);

create table test.caseI_2
(name string collate utf8mb4_general_ci, id int);
insert into test.caseI_2 values ('HJ',1);

-- 直接关联即可匹配成功
select * from test.caseI i
inner join test.caseI_2 i2 on i.name=i2.name;

Spark 3.4+ 可将排序规则替换为utf8_lcase，Trino/Presto可在关联条件中临时指定规则：on i.name = i2.name collate utf8mb4_general_ci，无需修改表结构。

方案2：视图层统一封装转换逻辑（兼容低版本引擎） #

如果使用的引擎版本较低不支持排序规则配置，又不希望在业务SQL中重复书写upper()/lower()，可以预先为物理表创建视图，在视图层统一完成字符串大小写转换，业务查询直接关联视图即可：

-- 预创建封装了转换逻辑的视图
create view test.v_caseI as select upper(name) as name, id from test.caseI;
create view test.v_caseI_2 as select upper(name) as name, id from test.caseI_2;

-- 业务查询直接关联视图，无需额外写转换函数
select * from test.v_caseI i
inner join test.v_caseI_2 i2 on i.name=i2.name;

方案3：自定义通用比较UDF #

可以注册一个大小写不敏感的等值比较UDF（例如命名为ci_equal），关联时直接调用UDF完成匹配，相比每次手写upper()/lower()复用性更强，也可避免字段书写遗漏：

select * from test.caseI i
inner join test.caseI_2 i2 on ci_equal(i.name, i2.name);

内容的提问来源于stack exchange，提问作者HIMANSHU JAIN