{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/15868","repository_url":"https://api.github.com/repos/apache/spark","labels_url":"https://api.github.com/repos/apache/spark/issues/15868/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/15868/comments","events_url":"https://api.github.com/repos/apache/spark/issues/15868/events","html_url":"https://github.com/apache/spark/pull/15868","id":188951669,"number":15868,"title":"[SPARK-18413][SQL] Add `maxConnections` JDBCOption","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":20,"created_at":"2016-11-13T04:31:51Z","updated_at":"2016-11-16T06:49:09Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/15868","html_url":"https://github.com/apache/spark/pull/15868","diff_url":"https://github.com/apache/spark/pull/15868.diff","patch_url":"https://github.com/apache/spark/pull/15868.patch"},"body":"## What changes were proposed in this pull request?\r\n\r\nThis PR aims to control the number of JDBC connections by `coalesce` with the given `maxConnection` (`JDBC_MAX_CONNECTIONS` JDBCOption).\r\n\r\n**Reported Scenario**\r\n\r\nFor the following cases, the number of connections becomes 200 and database cannot handle all of them.\r\n\r\n```sql\r\nCREATE OR REPLACE TEMPORARY VIEW resultview\r\nUSING org.apache.spark.sql.jdbc\r\nOPTIONS (\r\n  url \"jdbc:oracle:thin:@10.129.10.111:1521:BKDB\",\r\n  dbtable \"result\",\r\n  user \"HIVE\",\r\n  password \"HIVE\"\r\n);\r\n-- set spark.sql.shuffle.partitions=200\r\nINSERT OVERWRITE TABLE resultview SELECT g, count(1) AS COUNT FROM tnet.DT_LIVE_INFO GROUP BY g\r\n```\r\n\r\n## How was this patch tested?\r\n\r\nManual. Do the followings and see Spark UI.\r\n\r\n**Step 1 (MySQL)**\r\n```\r\nCREATE TABLE t1 (a INT);\r\nCREATE TABLE data (a INT);\r\nINSERT INTO data VALUES (1);\r\nINSERT INTO data VALUES (2);\r\nINSERT INTO data VALUES (3);\r\n```\r\n\r\n**Step 2 (Spark)**\r\n```scala\r\nSPARK_HOME=$PWD bin/spark-shell --driver-memory 4G --driver-class-path mysql-connector-java-5.1.40-bin.jar\r\nscala> sql(\"SET spark.sql.shuffle.partitions=3\")\r\nscala> sql(\"CREATE OR REPLACE TEMPORARY VIEW data USING org.apache.spark.sql.jdbc OPTIONS (url 'jdbc:mysql://localhost:3306/t', dbtable 'data', user 'root', password '')\")\r\nscala> sql(\"CREATE OR REPLACE TEMPORARY VIEW t1 USING org.apache.spark.sql.jdbc OPTIONS (url 'jdbc:mysql://localhost:3306/t', dbtable 't1', user 'root', password '', maxConnections '1')\")\r\nscala> sql(\"INSERT OVERWRITE TABLE t1 SELECT a FROM data GROUP BY a\")\r\nscala> sql(\"CREATE OR REPLACE TEMPORARY VIEW t1 USING org.apache.spark.sql.jdbc OPTIONS (url 'jdbc:mysql://localhost:3306/t', dbtable 't1', user 'root', password '', maxConnections '2')\")\r\nscala> sql(\"INSERT OVERWRITE TABLE t1 SELECT a FROM data GROUP BY a\")\r\nscala> sql(\"CREATE OR REPLACE TEMPORARY VIEW t1 USING org.apache.spark.sql.jdbc OPTIONS (url 'jdbc:mysql://localhost:3306/t', dbtable 't1', user 'root', password '', maxConnections '3')\")\r\nscala> sql(\"INSERT OVERWRITE TABLE t1 SELECT a FROM data GROUP BY a\")\r\nscala> sql(\"CREATE OR REPLACE TEMPORARY VIEW t1 USING org.apache.spark.sql.jdbc OPTIONS (url 'jdbc:mysql://localhost:3306/t', dbtable 't1', user 'root', password '', maxConnections '4')\")\r\nscala> sql(\"INSERT OVERWRITE TABLE t1 SELECT a FROM data GROUP BY a\")\r\n```\r\n\r\n![maxconnections](https://cloud.githubusercontent.com/assets/9700541/20287987/ed8409c2-aa84-11e6-8aab-ae28e63fe54d.png)\r\n"},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/260867852","html_url":"https://github.com/apache/spark/pull/15868#issuecomment-260867852","issue_url":"https://api.github.com/repos/apache/spark/issues/15868","id":260867852,"user":{"login":"AmplabJenkins","id":4031291,"avatar_url":"https://avatars.githubusercontent.com/u/4031291?v=3","gravatar_id":"","url":"https://api.github.com/users/AmplabJenkins","html_url":"https://github.com/AmplabJenkins","followers_url":"https://api.github.com/users/AmplabJenkins/followers","following_url":"https://api.github.com/users/AmplabJenkins/following{/other_user}","gists_url":"https://api.github.com/users/AmplabJenkins/gists{/gist_id}","starred_url":"https://api.github.com/users/AmplabJenkins/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/AmplabJenkins/subscriptions","organizations_url":"https://api.github.com/users/AmplabJenkins/orgs","repos_url":"https://api.github.com/users/AmplabJenkins/repos","events_url":"https://api.github.com/users/AmplabJenkins/events{/privacy}","received_events_url":"https://api.github.com/users/AmplabJenkins/received_events","type":"User","site_admin":false},"created_at":"2016-11-16T06:49:08Z","updated_at":"2016-11-16T06:49:08Z","body":"Merged build finished. Test FAILed."}}
{"actor":{"display_login":"AmplabJenkins"}}