Hive 編程入門 之 Join 的失效

  • 2019 年 12 月 25 日
  • 筆記

環境：

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 執行也順利通過：

Tags:

VirMach 便宜 VPS