GitHub user dongjoon-hyun opened a pull request: https://github.com/apache/spark/pull/15868
[SPARK-18413][SQL] Control the number of JDBC connections by repartition with `numPartition` JDBCOption ## What changes were proposed in this pull request? This PR aims to control the number of JDBC connections by repartition with `numPartition` (`JDBC_NUM_PARTITIONS` JDBCOption). Currently, `JDBC_NUM_PARTITIONS` is documented as an optional parameter for only reading. **Reported Scenario** For the following cases, the number of connections becomes 200 and database cannot handle all of them. ```sql CREATE OR REPLACE TEMPORARY VIEW resultview USING org.apache.spark.sql.jdbc OPTIONS ( url "jdbc:oracle:thin:@10.129.10.111:1521:BKDB", dbtable "result", user "HIVE", password "HIVE" ); -- set spark.sql.shuffle.partitions=200 INSERT OVERWRITE TABLE resultview SELECT g, count(1) AS COUNT FROM tnet.DT_LIVE_INFO GROUP BY g ``` ## How was this patch tested? Manual by using `Client Connection Tab` of `MySQLWorkbench`. After creating table t1 by `CREATE TABLE t1 (a INT)` in MySQL, do the following. ```scala SPARK_HOME=$PWD bin/spark-shell --driver-memory 4G --driver-class-path mysql-connector-java-5.1.40-bin.jar scala> sql("CREATE OR REPLACE TEMPORARY VIEW v1 USING org.apache.spark.sql.jdbc OPTIONS (url 'jdbc:mysql://localhost:3306/t', dbtable 't1', user 'root', password '', numPartitions '1')") scala> sql("INSERT OVERWRITE TABLE v1 SELECT 1").show scala> sql("CREATE OR REPLACE TEMPORARY VIEW v1 USING org.apache.spark.sql.jdbc OPTIONS (url 'jdbc:mysql://localhost:3306/t', dbtable 't1', user 'root', password '', numPartitions '20')") scala> sql("INSERT OVERWRITE TABLE v1 SELECT 1").show ``` The total connection is increased by 3 and 20, respectively for the first and second insertion. You can merge this pull request into a Git repository by running: $ git pull https://github.com/dongjoon-hyun/spark SPARK-18413 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/spark/pull/15868.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #15868 ---- commit e29974a08da14b63aec1f6fc9798bac532e4d97f Author: Dongjoon Hyun <dongj...@apache.org> Date: 2016-11-13T04:07:39Z [SPARK-18413][SQL] Control the number of JDBC connections by repartition with `numPartition` JDBCOption ---- --- If your project is set up for it, you can reply to this email and have your reply appear on GitHub as well. If your project does not have this feature enabled and wishes so, or if the feature is enabled but not working, please contact infrastructure at infrastruct...@apache.org or file a JIRA ticket with INFRA. --- --------------------------------------------------------------------- To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org For additional commands, e-mail: reviews-h...@spark.apache.org