{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/14132","repository_url":"https://api.github.com/repos/apache/spark","labels_url":"https://api.github.com/repos/apache/spark/issues/14132/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/14132/comments","events_url":"https://api.github.com/repos/apache/spark/issues/14132/events","html_url":"https://github.com/apache/spark/pull/14132","id":164785460,"number":14132,"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":93,"created_at":"2016-07-11T08:34:55Z","updated_at":"2016-07-19T01:06:23Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/14132","html_url":"https://github.com/apache/spark/pull/14132","diff_url":"https://github.com/apache/spark/pull/14132.diff","patch_url":"https://github.com/apache/spark/pull/14132.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 handled by **Analyzer** or **Optimizers**. The unknown hints are ignored with DEBUG level message as a default operation.\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\n\r\n**2. Broadcast Hints**\r\nThe followings are recognized.\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\n\r\n**Note**\r\n- Like Hive, `MAPJOIN(database_name.table_name)` is not allowed.\r\n- Unlink Hive, `NEWMAPJOIN(t)` is allowed for accepting new Spark Hints.\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/233503432","html_url":"https://github.com/apache/spark/pull/14132#issuecomment-233503432","issue_url":"https://api.github.com/repos/apache/spark/issues/14132","id":233503432,"user":{"login":"rxin","id":323388,"avatar_url":"https://avatars.githubusercontent.com/u/323388?v=3","gravatar_id":"","url":"https://api.github.com/users/rxin","html_url":"https://github.com/rxin","followers_url":"https://api.github.com/users/rxin/followers","following_url":"https://api.github.com/users/rxin/following{/other_user}","gists_url":"https://api.github.com/users/rxin/gists{/gist_id}","starred_url":"https://api.github.com/users/rxin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rxin/subscriptions","organizations_url":"https://api.github.com/users/rxin/orgs","repos_url":"https://api.github.com/users/rxin/repos","events_url":"https://api.github.com/users/rxin/events{/privacy}","received_events_url":"https://api.github.com/users/rxin/received_events","type":"User","site_admin":false},"created_at":"2016-07-19T01:06:23Z","updated_at":"2016-07-19T01:06:23Z","body":"I was referring to NormalizeBroadcastHint -- there are many cases in there and it seems error prone against future changes. Do we need all those rules?\r\n\r\n"}}
{"actor":{"display_login":"rxin"}}