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 <[email protected]>
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 [email protected] or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
