{"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":66,"created_at":"2016-07-11T08:34:55Z","updated_at":"2016-07-16T18:16:01Z","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## 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/233143653","html_url":"https://github.com/apache/spark/pull/14132#issuecomment-233143653","issue_url":"https://api.github.com/repos/apache/spark/issues/14132","id":233143653,"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-07-16T18:16:01Z","updated_at":"2016-07-16T18:16:01Z","body":"Hi, @rxin and @gatorsmile . \r\n\r\nWhile I'm investigating `SQLBuilder` and building more exhaustive testcases, I found that `SQLBuilder` seems to be **not exhaustive currently**. It's not about `Hint` issue.\r\n```scala\r\nscala> spark.range(10).createOrReplaceTempView(\"t\")\r\nscala> new org.apache.spark.sql.catalyst.SQLBuilder(sql(\"select * from t\")).toSQL\r\njava.lang.UnsupportedOperationException: unsupported plan Range (0, 10, splits=8)\r\nscala> new org.apache.spark.sql.catalyst.SQLBuilder(sql(\"select * from parquet.`examples/src/main/resources/users.parquet`\")).toSQL\r\njava.lang.UnsupportedOperationException: unsupported plan Relation[name#21,favorite_color#22,favorite_numbers#23] parquet\r\n```\r\n\r\nI tried to make this PR as similar as possible with `broadcast()` behaviors, but it seems to be the beyond of the scope in `SQLBuilder`. We can not put all of them into this PR since they are not `Hint`."}}
{"actor":{"display_login":"dongjoon-hyun"}}