检测到逻辑计划之间的内部连接的隐式笛卡尔积

Question

我正在尝试加入两个数据集

+-------------+-----------------+-----------+
|  countryName|countryPopulation|countrySize|
+-------------+-----------------+-----------+
|        China|       1210004992|    9596960|
|        India|        952107712|    3287590|
| UnitedStates|        266476272|    9372610|
|    Indonesia|        206611600|    1919440|
|       Brazil|        162661216|    8511965|
|       Russia|        148178480|   17075200|
|     Pakistan|        129275664|     803940|
|        Japan|        125449704|     377835|
|   Bangladesh|        123062800|     144000|
|      Nigeria|        103912488|     923770|
|       Mexico|         95772464|    1972550|
|      Germany|         83536112|     356910|
|  Philippines|         74480848|     300000|
|      Vietnam|         73976976|     329560|
|         Iran|         66094264|    1648000|
|        Egypt|         63575108|    1001450|
|       Turkey|         62484480|     780580|
|     Thailand|         58851356|     514000|
|UnitedKingdom|         58489976|     244820|
|       France|         58317448|     547030|
+-------------+-----------------+-----------+

DS2:

+------------+-----------------+-----------+
| countryName|countryPopulation|countrySize|
+------------+-----------------+-----------+
|       China|       1210004992|    9596960|
|       India|        952107712|    3287590|
|UnitedStates|        266476272|    9372610|
|   Indonesia|        206611600|    1919440|
|      Brazil|        162661216|    8511965|
|      Russia|        148178480|   17075200|
|    Pakistan|        129275664|     803940|
|       Japan|        125449704|     377835|
|  Bangladesh|        123062800|     144000|
|     Nigeria|        103912488|     923770|
|     Germany|         83536112|     356910|
|     Vietnam|         73976976|     329560|
|        Iran|         66094264|    1648000|
|    Thailand|         58851356|     514000|
|      France|         58317448|     547030|
|       Italy|         57460272|     301230|
|    Ethiopia|         57171664|    1127127|
|     Ukraine|         50864008|     603700|
|       Zaire|         46498540|    2345410|
|       Burma|         45975624|     678500|
+------------+-----------------+-----------+

当我执行下面的操作时，输出

  Dataset<Row> ds3 = ds2.filter(ds2.col("countryPopulation").cast("int").$greater(100000))
        .join(ds1, ds1.col("countrySize")
                .equalTo(ds2.col("countrySize")));
  ds3.show();

但当我做下面的操作时，我会出错

  Dataset<Row> ds3 = ds2.filter(ds2.col("countryPopulation").cast("int").$greater(100000))
        .join(ds1, ds1.col("countrySize").cast(DataTypes.IntegerType)
                .equalTo(ds2.col("countrySize").cast(DataTypes.IntegerType)), "inner");
  ds3.show();

Exception in thread "main" org.apache.spark.sql.AnalysisException: Detected implicit cartesian product for INNER join between logical plans
Project [country#6.name AS countryName#2, country#6.population AS countryPopulation#3, country#6.area AS countrySize#4]
+- Filter (isnotnull(country#6) && (Contains(country#6.name, a) && ((cast(country#6.population as int) > 100000) && (cast(country#6.area as int) = cast(country#6.area as int)))))
   +- Generate explode(countries#0.country), [0], false, t, [country#6]
      +- Relation[countries#0] json

Answer 1

我见过当where条件包含两个数据帧中的参数的函数调用时会发生笛卡尔联接。类似于df1.join(df2,aFunction(df1.column,df2.column)。在这里我不太清楚，但我怀疑正在发生类似的事情。

请在下面尝试获取在选择条件（而不是在Where）上应用的函数。

    Dataset<Row> ds1_1 = ds1.select(col("countrySize").cast(DataTypes.IntegerType).as("countrySize")) // add all columns here

    Dataset<Row> ds2_1 = ds2.select(col("countrySize").cast(DataTypes.IntegerType).as("countrySize"),ds2.col("countryPopulation").cast("int").as("countryPopulation")) // add all columns here

    Dataset<Row> ds3 = ds2.filter(ds2_1.col("countryPopulation").$greater(100000))
        .join(ds1_1, ds1_1.col("countrySize")
                .equalTo(ds2_1.col("countrySize")), "inner");
    ds3.show();