{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/14426","repository_url":"https://api.github.com/repos/apache/spark","labels_url":"https://api.github.com/repos/apache/spark/issues/14426/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/14426/comments","events_url":"https://api.github.com/repos/apache/spark/issues/14426/events","html_url":"https://github.com/apache/spark/pull/14426","id":168493124,"number":14426,"title":"[SPARK-16475][SQL] Broadcast Hint for SQL Queries","user":{"login":"dongjoon-hyun","id":9700541,"avatar_url":"https://avatars.githubusercontent.com/u/9700541?v=3","gravatar_id":"","url":"https://api.github.com/users/dongjoon-hyun","html_url":"https://github.com/dongjoon-hyun","followers_url":"https://api.github.com/users/dongjoon-hyun/followers","following_url":"https://api.github.com/users/dongjoon-hyun/following{/other_user}","gists_url":"https://api.github.com/users/dongjoon-hyun/gists{/gist_id}","starred_url":"https://api.github.com/users/dongjoon-hyun/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dongjoon-hyun/subscriptions","organizations_url":"https://api.github.com/users/dongjoon-hyun/orgs","repos_url":"https://api.github.com/users/dongjoon-hyun/repos","events_url":"https://api.github.com/users/dongjoon-hyun/events{/privacy}","received_events_url":"https://api.github.com/users/dongjoon-hyun/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":5,"created_at":"2016-07-30T23:57:50Z","updated_at":"2016-08-11T21:31:20Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/14426","html_url":"https://github.com/apache/spark/pull/14426","diff_url":"https://github.com/apache/spark/pull/14426.diff","patch_url":"https://github.com/apache/spark/pull/14426.patch"},"body":"## What changes were proposed in this pull request?\r\nThis PR aims to achieve the following two goals in Spark SQL.\r\n\r\n**1. Generic Hint Syntax**\r\nThe generic hints are parsed and transformed into concrete hints by `SubstituteHints` of **Analyzer**. The unknown hints are removed, too. For example, `Hint(\"MAPJOIN\")` is transformed into `BroadcastJoin` and other hints are removed currently.\r\n\r\n```sql\r\nSELECT /*+ MAPJOIN(t) */ * FROM t\r\nSELECT /*+ STREAMTABLE(a,b,c) */ * FROM t\r\nSELECT /*+ INDEX(t emp_job_ix) */ * FROM t\r\n```\r\nUnlink Hive, `NEWMAPJOIN(t)` is allowed for accepting new Spark Hints.\r\n\r\n\r\n**2. Broadcast Hints**\r\nThe followings are recognized. Technically, broadcast hints are matched `UnresolvedRelation` to support Hive `MetastoreRelation`. The style of `database_name.table_name` is not allowed in this PR.\r\n```sql\r\nSELECT /*+ MAPJOIN(t) */ * FROM t JOIN u ON t.id = u.id\r\nSELECT /*+ BROADCAST(u) */ * FROM t JOIN u ON t.id = u.id\r\nSELECT /*+ BROADCASTJOIN(u) */ * FROM t JOIN u ON t.id = u.id\r\n```\r\n\r\n**Examples**\r\n```scala\r\nscala> spark.range(1000000000).createOrReplaceTempView(\"t\")\r\nscala> spark.range(1000000000).createOrReplaceTempView(\"u\")\r\n\r\nscala> sql(\"SELECT * FROM t JOIN u ON t.id = u.id\").explain\r\n== Physical Plan ==\r\n*SortMergeJoin [id#0L], [id#4L], Inner\r\n:- *Sort [id#0L ASC], false, 0\r\n:  +- Exchange hashpartitioning(id#0L, 200)\r\n:     +- *Range (0, 1000000000, splits=8)\r\n+- *Sort [id#4L ASC], false, 0\r\n   +- ReusedExchange [id#4L], Exchange hashpartitioning(id#0L, 200)\r\n\r\nscala> sql(\"SELECT /*+ MAPJOIN(t) */ * FROM t JOIN u ON t.id = u.id\").explain\r\n== Physical Plan ==\r\n*BroadcastHashJoin [id#0L], [id#4L], Inner, BuildLeft\r\n:- BroadcastExchange HashedRelationBroadcastMode(List(input[0, bigint, false]))\r\n:  +- *Range (0, 1000000000, splits=8)\r\n+- *Range (0, 1000000000, splits=8)\r\n\r\nscala> sql(\"SELECT /*+ MAPJOIN(u) */ * FROM t JOIN u ON t.id = u.id\").explain\r\n== Physical Plan ==\r\n*BroadcastHashJoin [id#0L], [id#4L], Inner, BuildRight\r\n:- *Range (0, 1000000000, splits=8)\r\n+- BroadcastExchange HashedRelationBroadcastMode(List(input[0, bigint, false]))\r\n   +- *Range (0, 1000000000, splits=8)\r\n\r\nscala> sql(\"CREATE TABLE hive_t(id INT)\")\r\nres5: org.apache.spark.sql.DataFrame = []\r\n\r\nscala> sql(\"CREATE TABLE hive_u(id INT)\")\r\nres6: org.apache.spark.sql.DataFrame = []\r\n\r\nscala> sql(\"SELECT /*+ MAPJOIN(hive_u) */ * FROM hive_t JOIN hive_u ON hive_t.id = hive_u.id\").explain\r\n== Physical Plan ==\r\n*BroadcastHashJoin [id#28], [id#29], Inner, BuildRight\r\n:- *Filter isnotnull(id#28)\r\n:  +- HiveTableScan [id#28], MetastoreRelation default, hive_t\r\n+- BroadcastExchange HashedRelationBroadcastMode(List(cast(input[0, int, false] as bigint)))\r\n   +- *Filter isnotnull(id#29)\r\n      +- HiveTableScan [id#29], MetastoreRelation default, hive_u\r\n\r\nscala> sql(\"SELECT * FROM hive_t JOIN hive_u ON hive_t.id = hive_u.id\").explain\r\n== Physical Plan ==\r\n*SortMergeJoin [id#36], [id#37], Inner\r\n:- *Sort [id#36 ASC], false, 0\r\n:  +- Exchange hashpartitioning(id#36, 200)\r\n:     +- *Filter isnotnull(id#36)\r\n:        +- HiveTableScan [id#36], MetastoreRelation default, hive_t\r\n+- *Sort [id#37 ASC], false, 0\r\n   +- Exchange hashpartitioning(id#37, 200)\r\n      +- *Filter isnotnull(id#37)\r\n         +- HiveTableScan [id#37], MetastoreRelation default, hive_u\r\n```\r\n\r\nThe many previous discussions on this issue are at https://github.com/apache/spark/pull/14132 .\r\n\r\n## How was this patch tested?\r\n\r\nPass the Jenkins tests with new testcases."},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/239299220","html_url":"https://github.com/apache/spark/pull/14426#issuecomment-239299220","issue_url":"https://api.github.com/repos/apache/spark/issues/14426","id":239299220,"user":{"login":"dongjoon-hyun","id":9700541,"avatar_url":"https://avatars.githubusercontent.com/u/9700541?v=3","gravatar_id":"","url":"https://api.github.com/users/dongjoon-hyun","html_url":"https://github.com/dongjoon-hyun","followers_url":"https://api.github.com/users/dongjoon-hyun/followers","following_url":"https://api.github.com/users/dongjoon-hyun/following{/other_user}","gists_url":"https://api.github.com/users/dongjoon-hyun/gists{/gist_id}","starred_url":"https://api.github.com/users/dongjoon-hyun/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dongjoon-hyun/subscriptions","organizations_url":"https://api.github.com/users/dongjoon-hyun/orgs","repos_url":"https://api.github.com/users/dongjoon-hyun/repos","events_url":"https://api.github.com/users/dongjoon-hyun/events{/privacy}","received_events_url":"https://api.github.com/users/dongjoon-hyun/received_events","type":"User","site_admin":false},"created_at":"2016-08-11T21:31:20Z","updated_at":"2016-08-11T21:31:20Z","body":"Rebased to resolve conflicts on `SqlBase.g4`."}}
{"actor":{"display_login":"dongjoon-hyun"}}