【SQL相关】Hive进行表关联时on后不支持非等值连接的解决方法

1.报错原因

        Hive查询语言的设计目标是与传统的SQL类似，但它在某些方面有一些限制。标准的SQL（如ORACLE、MySQL）是支持对连接条件进行非等值连接的，但是在Hive中不支持。主要原因是mapreduce很难实现这种类型的连接，因为这些类型的连接会导致非常复杂的查询计划和性能问题。为了避免这种情况，Hive限制了连接操作的类型，只支持等值连接。

        非等值连接主要包括在连接条件中使用>、<、>=、<=和<>等操作符，以及使用OR和LIKE等进行模糊匹配。

在连接条件中使用>、<、>=、<=和<>等操作符会导致连接条件无法被优化和索引化，这意味着Hive无法有效地使用表的索引来加速查询，而是需要执行全表扫描，影响了查询的性能。

使用OR和LIKE等运算符进行模糊匹配会导致连接条件的模糊性增加，Hive优化器无法有效地处理这种模糊连接条件，因为它需要考虑多种可能的连接路径，这会导致查询的执行计划变得非常复杂，可能导致性能下降。

2.报错信息

2.1 Error 10017

--like实例
   select b.xxx 
     from table1 a
left join table2 b
       on a.xxx = b.xxx and b.xxx like concat('%',a.xxx,'%');

Error: Error while compiling statement: FAILED: SemanticException [Error 10017]: Line 10:3 Both left and right aliases encountered in JOIN ''%'' (state=42000,code=10017) 

--不等号实例
   select b.xxx 
     from table1 a
left join table2 b
       on a.xxx > b.xxx;

Error: Error while compiling statement: FAILED: SemanticException [Error 10017]: Line 10:3 Both left and right aliases encountered in JOIN 'xxx' (state=42000,code=10017)

2.2 Error 10019

--or实例
   select b.xxx 
     from table1 a
left join table2 b
       on a.xxx = b.xxx or b.xxx is not null;

Error: Error while compiling statement: FAILED: SemanticException [Error 10019]: Line 10:3 OR not supported in JOIN currently 'path_code' (state=42000,code=10019)

3.解决方法

        要解决Hive中非等值连接的问题，可以通过使用子查询、连接后过滤或使用其他查询引擎（如Apache Spark、Presto等）等方法。

3.1 子查询

--like实例
   select b.xxx 
     from table1 a
left join (select * from table2 where xxx like concat('%',a.xxx,'%')) b
       on a.xxx = b.xxx; 
--or实例
  select b.xxx from table1 a
left join (select * from table2 where xxx is not null) b
       on a.xxx = b.xxx;

3.2 连接后过滤--where

--like实例
   select b.xxx 
     from table1 a
left join table2 b
       on a.xxx = b.xxx
    where xxx like concat('%',a.xxx,'%');
--不等号实例
   select b.xxx 
     from table1 a
left join table2 b
       on 1=1
    where a.xxx > b.xxx;
--or实例
   select b.xxx 
     from table1 a
left join table2 b
       on a.xxx = b.xxx
    where xxx is not null;

拓展：on和where后的条件位置

inner join/full join------写哪里无所谓

left  join------单独右表的条件写在on后面，单独左表的条件写在where后面

right join------单独左表的条件写在on后面，单独右表的条件写在where后面

3.3 Spark SQL

        Hive是将Hive SQL转换成MapReduce然后提交到集群上执行，大大简化了编写MapReduc的程序的复杂性，但是MapReduce这种计算模型执行效率比较慢。Spark可以将Spark SQL转换成RDD，然后提交到集群执行，执行效率非常快，并且支持非等值连接。