[SPARK-16475] Broadcast Hint for SQL Queries

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 2.2.0
• Labels: releasenotes
• Target Version/s: 2.2.0
• Assignee: Reynold Xin
• Reporter: Reynold Xin
• Created: 11/Jul/16 00:34
• Updated: 15/Feb/17 14:55
• Resolved: 14/Feb/17 22:11

Description

Broadcast hint is a way for users to manually annotate a query and suggest to the query optimizer the join method. It is very useful when the query optimizer cannot make optimal decision with respect to join methods due to conservativeness or the lack of proper statistics.

The DataFrame API has broadcast hint since Spark 1.5. However, we do not have an equivalent functionality in SQL queries. We propose adding Hive-style broadcast hint to Spark SQL.

For more information, please see the attached document. One note about the doc: in addition to supporting "MAPJOIN", we should also support "BROADCASTJOIN" and "BROADCAST" in the comment, e.g. the following should be accepted:

SELECT /*+ MAPJOIN(b) */ ...

SELECT /*+ BROADCASTJOIN(b) */ ...

SELECT /*+ BROADCAST(b) */ ...

Issue Links

• is related to: SPARK-16590 Improve LogicalPlanToSQLSuite to check generated SQL directly
• relates to: SPARK-18209 More robust view canonicalization without full SQL expansion
• links to: [Github] Pull Request #14132 (dongjoon-hyun)
• links to: [Github] Pull Request #14426 (dongjoon-hyun)
• links to: [Github] Pull Request #16925 (rxin)
• links to: [Github] Pull Request #16939 (rxin)
• links to: [Github] Pull Request #16941 (viirya)