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

原创已于 2023-08-10 08:42:06 修改 · 2.4k 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#sql #hive #数据库

于 2023-08-02 19:07:14 首次发布

数据库专栏收录该内容

4 篇文章

订阅专栏

本文讨论了HiveSQL中的非等值连接限制，包括不支持>、<等操作符的连接和LIKE、OR等模糊匹配。提供了使用子查询、连接后过滤以及利用SparkSQL提高性能的方法。

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;