Hive 编程入门之 Join 的失效

Lenis

发布于 2019-12-25 12:56:27

2.6K0

发布于 2019-12-25 12:56:27

文章被收录于专栏：有关SQL有关SQL

环境：

Hive: 2.7.7
Oracle SQL Developer
Cloudera JDBC Driver

案例 - 1 ：非全等 Join 失效

select a.*, b.* 
from default.employee a 
inner join default.employee b on a.salary < b.salary

异常：

SQL 错误: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException Cartesian products are disabled for safety reasons. If you know what you are doing, please sethive.strict.checks.cartesian.product to false and that hive.mapred.mode is not set to 'strict' to proceed. Note that if you may get errors or incorrect results if you make a mistake while using some of the unsafe features.:17:16,
...(省去万字)
SemanticException Cartesian products are disabled for safety reasons. If you know what you are doing, please sethive.strict.checks.cartesian.product to false and that hive.mapred.mode is not set to 'strict' to proceed. Note that if you may get errors or incorrect results if you make a mistake while using some of the unsafe features.), Query: select a.*, b.* 
from default.employee a 
inner join default.employee b on a.salary < b.salary.

为了保障整个集群的稳定性，类似非全等的 Join 是默认禁止的，原因在异常代码中给出了：

FAILED: SemanticException Cartesian products are disabled for safety reasons

这样就可以避免因为大数据的 Join 导致网络瘫痪。

如果清晰的知道，非全等 Join 不会造成影响，那么可以修改这些配置来启动非全等 join 的操作。

set hive.strict.checks.cartesian.product=false
set hive.mapred.mode=nonstrict

select a.*, b.* 
from default.employee a 
inner join default.employee b on a.salary < b.salary

最终非全等 Join 执行也顺利通过：