Qfyilyi

I have two tables with bucketing enabled.

DESCRIBE EXTENDED table1



Table                       |table1                                                                                                 |       |

|Owner                       |user                                                                                                  |       |

|Created                     |Wed Nov 21 16:24:25 CST 2018                                                                             |       |

|Last Access                 |Wed Dec 31 18:00:00 CST 1969                                                                             |       |

|Type                        |MANAGED                                                                                                  |       |

|Provider                    |parquet                                                                                                  |       |

|Num Buckets                 |180                                                                                                      |       |

|Bucket Columns              |[`seq_id`]                                                                                   |       |

|Sort Columns                |[`seq_id`]





DESCRIBE EXTENDED table2



Table                       |table2                                                                                                |       |

|Owner                       |user                                                                                                  |       |

|Created                     |Wed Nov 21 16:15:09 CST 2018                                                                            |       |

|Last Access                 |Wed Dec 31 18:00:00 CST 1969                                                                            |       |

|Type                        |MANAGED                                                                                                 |       |

|Provider                    |parquet                                                                                                 |       |

|Num Buckets                 |180                                                                                                     |       |

|Bucket Columns              |[`seq_id`]                                                                                  |       |

|Sort Columns                |[`seq_id`]

Then I expect that it will let me avoid shuffling (exchange) when I join both of them.

However, exchange is there:

spark.table("table2").join(spark.table("table1"), "seq_id").explain

== Physical Plan ==

Project [seq_id#0, field1#1, ... 165 more fields]

+- SortMergeJoin [seq_id#0], [seq_id#196], Inner

   :- *Sort [seq_id#0 ASC NULLS FIRST], false, 0

   :  +- Exchange(coordinator id: 713544719) hashpartitioning(seq_id#0, 200), coordinator[target post-shuffle partition size: 77108864]

   :     +- *Project [seq_id#0, field1#1,  ... 73 more fields]

   :        +- *Filter isnotnull(seq_id#0)

   :           +- *FileScan parquet 

   test2[seq_id#0, field1#1,... 73 more fields] Batched: true, Format: Parquet, Location: InMemoryFileIndex[maprfs:/ds/hive/warehouse/test2..., PartitionFilters: , PushedFilters: [IsNotNull(seq_id)], ReadSchema: struct<seq_id:string,field1:string...

   +- *Sort [seq_id#196 ASC NULLS FIRST], false, 0

      +- Exchange(coordinator id: 713544719) hashpartitioning(seq_id#196, 200), coordinator[target post-shuffle partition size: 77108864]

         +- *Project [line_s#195, seq_id#196, field1#197, ... 69 more fields]

            +- *Filter isnotnull(seq_id#196)

               +- *FileScan parquet test1[line_s#195,seq_id#196,field1#197,69 more fields] Batched: true, Format: Parquet, Location: InMemoryFileIndex[maprfs:/ds/test1..., PartitionFilters: , PushedFilters: [IsNotNull(seq_id)], ReadSchema: struct<line_s:string,seq_id:string,field1:string,...

I am using Spark 2.2.1, any idea what could be the reason that exchange still happens there?

Tables (table1 and tables2) were created as below:

spark.table("src_table1").write

  .bucketBy(180, "seq_id")

  .sortBy("seq_id")

  .saveAsTable("table1")



spark.table("src_table2").write

  .bucketBy(180, "seq_id")

  .sortBy("seq_id")

  .saveAsTable("table2")

Hive tables src_table1 and src_table2 are parquet format with no buckets.

Seems like adaptive query execution enabled (spark.sql.adaptive.enabled=true) was the problem. After disabling this, exchange is not there anymore. Need to dig more, why it happens.