You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by tmyklebu <gi...@git.apache.org> on 2015/01/29 04:45:45 UTC

[GitHub] spark pull request: A JDBC driver for Spark SQL.

GitHub user tmyklebu opened a pull request:

    https://github.com/apache/spark/pull/4261

    A JDBC driver for Spark SQL.

    This pull request contains a Spark SQL data source that can pull data from, and can put data into, a JDBC database.
    
    I have tested both read and write support with H2, MySQL, and Postgres.  It would surprise me if both read and write support worked flawlessly out-of-the-box for any other database; different databases have different names for different JDBC data types and different meanings for SQL types with the same name.  However, this code is designed (see `DriverQuirks.scala`) to make it *relatively* painless to add support for another database by augmenting the type mapping contained in this PR.

You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/tmyklebu/spark master

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/spark/pull/4261.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 #4261
    
----
commit 176bb9841718eed25a2cfc354299276ccca0e5e7
Author: Tor Myklebust <tm...@gmail.com>
Date:   2015-01-29T03:29:10Z

    Add test deps for JDBC support.

commit a09eeac34c6e3632895ac5a7ef1664804cb832b7
Author: Tor Myklebust <tm...@gmail.com>
Date:   2015-01-29T03:29:53Z

    JDBC data source for Spark SQL.

----


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by marmbrus <gi...@git.apache.org>.
Github user marmbrus commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-72566713
  
    Thanks for working on this @tmyklebu, I'm excited to have this feature!  I'm going to merge as is since we are close to the deadline for 1.3.  We can do style fixes after the fact.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: A JDBC driver for Spark SQL.

Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-71964610
  
    Test FAILed.
    Refer to this link for build results (access rights to CI server needed): 
    https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/26286/
    Test FAILed.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-71971275
  
      [Test build #26288 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/26288/consoleFull) for   PR 4261 at commit [`41647ef`](https://github.com/apache/spark/commit/41647ef4aab44cc0cf698f003143d3d5b30fab10).
     * This patch **fails Spark unit tests**.
     * This patch merges cleanly.
     * This patch adds the following public classes _(experimental)_:
      * `public class JDBCUtils `
      * `          logWarning(s"Couldn't find class $driver", e);`
      * `  implicit class JDBCContext(sql: SQLContext) `
      * `  implicit class JDBCDataFrame(rdd: DataFrame) `



---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-72219502
  
      [Test build #26403 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/26403/consoleFull) for   PR 4261 at commit [`cf167ce`](https://github.com/apache/spark/commit/cf167cea9457e933b1b8ed5f0eff708e6535ef99).
     * This patch merges cleanly.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-72052329
  
      [Test build #26320 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/26320/consoleFull) for   PR 4261 at commit [`585f95b`](https://github.com/apache/spark/commit/585f95b2d2a7282e20d922bce57118cd490f7226).
     * This patch **fails Spark unit tests**.
     * This patch merges cleanly.
     * This patch adds the following public classes _(experimental)_:
      * `public class JDBCUtils `
      * `          logWarning(s"Couldn't find class $driver", e);`
      * `  implicit class JDBCContext(sql: SQLContext) `
      * `  implicit class JDBCDataFrame(rdd: DataFrame) `



---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by tmyklebu <gi...@git.apache.org>.
Github user tmyklebu commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23772508
  
    --- Diff: project/SparkBuild.scala ---
    @@ -397,6 +397,11 @@ object TestSettings {
         testOptions += Tests.Argument(TestFrameworks.JUnit, "-v", "-a"),
         // Enable Junit testing.
         libraryDependencies += "com.novocode" % "junit-interface" % "0.9" % "test",
    +    // Needed for SQL JDBC tests.
    +    libraryDependencies += "com.h2database" % "h2" % "1.4.183" % "test",
    --- End diff --
    
    OK, thanks.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by rxin <gi...@git.apache.org>.
Github user rxin commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23901085
  
    --- Diff: sql/core/src/main/scala/org/apache/spark/sql/jdbc/JDBCRDD.scala ---
    @@ -0,0 +1,417 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more
    + * contributor license agreements.  See the NOTICE file distributed with
    + * this work for additional information regarding copyright ownership.
    + * The ASF licenses this file to You under the Apache License, Version 2.0
    + * (the "License"); you may not use this file except in compliance with
    + * the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.spark.sql.jdbc
    +
    +import java.sql.{Connection, DatabaseMetaData, DriverManager, ResultSet, ResultSetMetaData, SQLException}
    +import scala.collection.mutable.ArrayBuffer
    +
    +import org.apache.spark.{Logging, Partition, SparkContext, TaskContext}
    +import org.apache.spark.rdd.RDD
    +import org.apache.spark.util.NextIterator
    +import org.apache.spark.sql.catalyst.analysis.HiveTypeCoercion
    +import org.apache.spark.sql.catalyst.expressions.{Row, SpecificMutableRow}
    +import org.apache.spark.sql.types._
    +import org.apache.spark.sql.sources._
    +
    +private[sql] object JDBCRDD extends Logging {
    +  /**
    +   * Maps a JDBC type to a Catalyst type.  This function is called only when
    +   * the DriverQuirks class corresponding to your database driver returns null.
    +   *
    +   * @param sqlType - A field of java.sql.Types
    +   * @return The Catalyst type corresponding to sqlType.
    +   */
    +  private def getCatalystType(sqlType: Int): DataType = {
    +    val answer = sqlType match {
    +      case java.sql.Types.ARRAY         => null
    +      case java.sql.Types.BIGINT        => LongType
    +      case java.sql.Types.BINARY        => BinaryType
    +      case java.sql.Types.BIT           => BooleanType // Per JDBC; Quirks handles quirky drivers.
    +      case java.sql.Types.BLOB          => BinaryType
    +      case java.sql.Types.BOOLEAN       => BooleanType
    +      case java.sql.Types.CHAR          => StringType
    +      case java.sql.Types.CLOB          => StringType
    +      case java.sql.Types.DATALINK      => null
    +      case java.sql.Types.DATE          => DateType
    +      case java.sql.Types.DECIMAL       => DecimalType.Unlimited
    +      case java.sql.Types.DISTINCT      => null
    +      case java.sql.Types.DOUBLE        => DoubleType
    +      case java.sql.Types.FLOAT         => FloatType
    +      case java.sql.Types.INTEGER       => IntegerType
    +      case java.sql.Types.JAVA_OBJECT   => null
    +      case java.sql.Types.LONGNVARCHAR  => StringType
    +      case java.sql.Types.LONGVARBINARY => BinaryType
    +      case java.sql.Types.LONGVARCHAR   => StringType
    +      case java.sql.Types.NCHAR         => StringType
    +      case java.sql.Types.NCLOB         => StringType
    +      case java.sql.Types.NULL          => null
    +      case java.sql.Types.NUMERIC       => DecimalType.Unlimited
    +      case java.sql.Types.OTHER         => null
    +      case java.sql.Types.REAL          => DoubleType
    +      case java.sql.Types.REF           => StringType
    +      case java.sql.Types.ROWID         => LongType
    +      case java.sql.Types.SMALLINT      => IntegerType
    +      case java.sql.Types.SQLXML        => StringType
    +      case java.sql.Types.STRUCT        => StringType
    +      case java.sql.Types.TIME          => TimestampType
    +      case java.sql.Types.TIMESTAMP     => TimestampType
    +      case java.sql.Types.TINYINT       => IntegerType
    +      case java.sql.Types.VARBINARY     => BinaryType
    +      case java.sql.Types.VARCHAR       => StringType
    +      case _ => null
    +    }
    +
    +    if (answer == null) throw new SQLException("Unsupported type " + sqlType)
    +    answer
    +  }
    +
    +  /**
    +   * Takes a (schema, table) specification and returns the table's Catalyst
    +   * schema.
    +   *
    +   * @param url - The JDBC url to fetch information from.
    +   * @param table - The table name of the desired table.  This may also be a
    +   *   SQL query wrapped in parentheses.
    +   *
    +   * @return A StructType giving the table's Catalyst schema.
    +   * @throws SQLException if the table specification is garbage.
    +   * @throws SQLException if the table contains an unsupported type.
    +   */
    +  def resolveTable(url: String, table: String): StructType = {
    +    val quirks = DriverQuirks.get(url)
    +    val conn: Connection = DriverManager.getConnection(url)
    +    try {
    +      val rs = conn.prepareStatement(s"SELECT * FROM $table WHERE 1=0").executeQuery()
    +      try {
    +        val rsmd = rs.getMetaData
    +        val ncols = rsmd.getColumnCount
    +        var fields = new Array[StructField](ncols);
    +        var i = 0
    +        while (i < ncols) {
    +          val columnName = rsmd.getColumnName(i + 1)
    +          val dataType = rsmd.getColumnType(i + 1)
    +          val typeName = rsmd.getColumnTypeName(i + 1)
    +          val fieldSize = rsmd.getPrecision(i + 1)
    +          val nullable = rsmd.isNullable(i + 1) != ResultSetMetaData.columnNoNulls
    +          val metadata = new MetadataBuilder().putString("name", columnName)
    +          var columnType = quirks.getCatalystType(dataType, typeName, fieldSize, metadata)
    +          if (columnType == null) columnType = getCatalystType(dataType)
    +          fields(i) = StructField(columnName, columnType, nullable, metadata.build())
    +          i = i + 1
    +        }
    +        return new StructType(fields)
    +      } finally {
    +        rs.close()
    +      }
    +    } finally {
    +      conn.close()
    +    }
    +
    +    throw new RuntimeException("This line is unreachable.")
    --- End diff --
    
    this is reachable if the connection failed, isn't it?


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by tmyklebu <gi...@git.apache.org>.
Github user tmyklebu commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23904154
  
    --- Diff: sql/core/src/main/scala/org/apache/spark/sql/jdbc/JDBCRDD.scala ---
    @@ -0,0 +1,417 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more
    + * contributor license agreements.  See the NOTICE file distributed with
    + * this work for additional information regarding copyright ownership.
    + * The ASF licenses this file to You under the Apache License, Version 2.0
    + * (the "License"); you may not use this file except in compliance with
    + * the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.spark.sql.jdbc
    +
    +import java.sql.{Connection, DatabaseMetaData, DriverManager, ResultSet, ResultSetMetaData, SQLException}
    +import scala.collection.mutable.ArrayBuffer
    +
    +import org.apache.spark.{Logging, Partition, SparkContext, TaskContext}
    +import org.apache.spark.rdd.RDD
    +import org.apache.spark.util.NextIterator
    +import org.apache.spark.sql.catalyst.analysis.HiveTypeCoercion
    +import org.apache.spark.sql.catalyst.expressions.{Row, SpecificMutableRow}
    +import org.apache.spark.sql.types._
    +import org.apache.spark.sql.sources._
    +
    +private[sql] object JDBCRDD extends Logging {
    +  /**
    +   * Maps a JDBC type to a Catalyst type.  This function is called only when
    +   * the DriverQuirks class corresponding to your database driver returns null.
    +   *
    +   * @param sqlType - A field of java.sql.Types
    +   * @return The Catalyst type corresponding to sqlType.
    +   */
    +  private def getCatalystType(sqlType: Int): DataType = {
    +    val answer = sqlType match {
    +      case java.sql.Types.ARRAY         => null
    +      case java.sql.Types.BIGINT        => LongType
    +      case java.sql.Types.BINARY        => BinaryType
    +      case java.sql.Types.BIT           => BooleanType // Per JDBC; Quirks handles quirky drivers.
    +      case java.sql.Types.BLOB          => BinaryType
    +      case java.sql.Types.BOOLEAN       => BooleanType
    +      case java.sql.Types.CHAR          => StringType
    +      case java.sql.Types.CLOB          => StringType
    +      case java.sql.Types.DATALINK      => null
    +      case java.sql.Types.DATE          => DateType
    +      case java.sql.Types.DECIMAL       => DecimalType.Unlimited
    +      case java.sql.Types.DISTINCT      => null
    +      case java.sql.Types.DOUBLE        => DoubleType
    +      case java.sql.Types.FLOAT         => FloatType
    +      case java.sql.Types.INTEGER       => IntegerType
    +      case java.sql.Types.JAVA_OBJECT   => null
    +      case java.sql.Types.LONGNVARCHAR  => StringType
    +      case java.sql.Types.LONGVARBINARY => BinaryType
    +      case java.sql.Types.LONGVARCHAR   => StringType
    +      case java.sql.Types.NCHAR         => StringType
    +      case java.sql.Types.NCLOB         => StringType
    +      case java.sql.Types.NULL          => null
    +      case java.sql.Types.NUMERIC       => DecimalType.Unlimited
    +      case java.sql.Types.OTHER         => null
    +      case java.sql.Types.REAL          => DoubleType
    +      case java.sql.Types.REF           => StringType
    +      case java.sql.Types.ROWID         => LongType
    +      case java.sql.Types.SMALLINT      => IntegerType
    +      case java.sql.Types.SQLXML        => StringType
    +      case java.sql.Types.STRUCT        => StringType
    +      case java.sql.Types.TIME          => TimestampType
    +      case java.sql.Types.TIMESTAMP     => TimestampType
    +      case java.sql.Types.TINYINT       => IntegerType
    +      case java.sql.Types.VARBINARY     => BinaryType
    +      case java.sql.Types.VARCHAR       => StringType
    +      case _ => null
    +    }
    +
    +    if (answer == null) throw new SQLException("Unsupported type " + sqlType)
    +    answer
    +  }
    +
    +  /**
    +   * Takes a (schema, table) specification and returns the table's Catalyst
    +   * schema.
    +   *
    +   * @param url - The JDBC url to fetch information from.
    +   * @param table - The table name of the desired table.  This may also be a
    +   *   SQL query wrapped in parentheses.
    +   *
    +   * @return A StructType giving the table's Catalyst schema.
    +   * @throws SQLException if the table specification is garbage.
    +   * @throws SQLException if the table contains an unsupported type.
    +   */
    +  def resolveTable(url: String, table: String): StructType = {
    +    val quirks = DriverQuirks.get(url)
    +    val conn: Connection = DriverManager.getConnection(url)
    +    try {
    +      val rs = conn.prepareStatement(s"SELECT * FROM $table WHERE 1=0").executeQuery()
    +      try {
    +        val rsmd = rs.getMetaData
    +        val ncols = rsmd.getColumnCount
    +        var fields = new Array[StructField](ncols);
    +        var i = 0
    +        while (i < ncols) {
    +          val columnName = rsmd.getColumnName(i + 1)
    +          val dataType = rsmd.getColumnType(i + 1)
    +          val typeName = rsmd.getColumnTypeName(i + 1)
    +          val fieldSize = rsmd.getPrecision(i + 1)
    +          val nullable = rsmd.isNullable(i + 1) != ResultSetMetaData.columnNoNulls
    +          val metadata = new MetadataBuilder().putString("name", columnName)
    +          var columnType = quirks.getCatalystType(dataType, typeName, fieldSize, metadata)
    +          if (columnType == null) columnType = getCatalystType(dataType)
    +          fields(i) = StructField(columnName, columnType, nullable, metadata.build())
    +          i = i + 1
    +        }
    +        return new StructType(fields)
    +      } finally {
    +        rs.close()
    +      }
    +    } finally {
    +      conn.close()
    +    }
    +
    +    throw new RuntimeException("This line is unreachable.")
    +  }
    +
    +  /**
    +   * Prune all but the specified columns from the specified Catalyst schema.
    +   *
    +   * @param schema - The Catalyst schema of the master table
    +   * @param columns - The list of desired columns
    +   *
    +   * @return A Catalyst schema corresponding to columns in the given order.
    +   */
    +  private def pruneSchema(schema: StructType, columns: Array[String]): StructType = {
    +    val fieldMap = Map(schema.fields map { x => x.metadata.getString("name") -> x }: _*)
    +    new StructType(columns map { name => fieldMap(name) })
    +  }
    +
    +  /**
    +   * Given a driver string and an url, return a function that loads the
    +   * specified driver string then returns a connection to the JDBC url.
    +   * getConnector is run on the driver code, while the function it returns
    +   * is run on the executor.
    +   *
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   *
    +   * @return A function that loads the driver and connects to the url.
    +   */
    +  def getConnector(driver: String, url: String): () => Connection = {
    +    () => {
    +      try {
    +        if (driver != null) Class.forName(driver)
    +      } catch {
    +        case e: ClassNotFoundException => {
    +          logWarning(s"Couldn't find class $driver", e);
    +        }
    +      }
    +      DriverManager.getConnection(url)
    +    }
    +  }
    +  /**
    +   * Build and return JDBCRDD from the given information.
    +   *
    +   * @param sc - Your SparkContext.
    +   * @param schema - The Catalyst schema of the underlying database table.
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   * @param fqTable - The fully-qualified table name (or paren'd SQL query) to use.
    +   * @param requiredColumns - The names of the columns to SELECT.
    +   * @param filters - The filters to include in all WHERE clauses.
    +   * @param parts - An array of JDBCPartitions specifying partition ids and
    +   *    per-partition WHERE clauses.
    +   *
    +   * @return An RDD representing "SELECT requiredColumns FROM fqTable".
    +   */
    +  def scanTable(sc: SparkContext,
    +                schema: StructType,
    +                driver: String,
    +                url: String,
    +                fqTable: String,
    +                requiredColumns: Array[String],
    +                filters: Array[Filter],
    +                parts: Array[Partition]): RDD[Row] = {
    +    val prunedSchema = pruneSchema(schema, requiredColumns)
    +
    +    return new JDBCRDD(sc,
    +        getConnector(driver, url),
    +        prunedSchema,
    +        fqTable,
    +        requiredColumns,
    +        filters,
    +        parts)
    +  }
    +}
    +
    +/**
    + * An RDD representing a table in a database accessed via JDBC.  Both the
    + * driver code and the workers must be able to access the database; the driver
    + * needs to fetch the schema while the workers need to fetch the data.
    + */
    +private[sql] class JDBCRDD(
    +    sc: SparkContext,
    +    getConnection: () => Connection,
    +    schema: StructType,
    +    fqTable: String,
    +    columns: Array[String],
    +    filters: Array[Filter],
    +    partitions: Array[Partition])
    +  extends RDD[Row](sc, Nil) {
    +
    +  /**
    +   * Retrieve the list of partitions corresponding to this RDD.
    +   */
    +  override def getPartitions: Array[Partition] = partitions
    +
    +  /**
    +   * `columns`, but as a String suitable for injection into a SQL query.
    +   */
    +  private val columnList: String = {
    +    val sb = new StringBuilder()
    +    columns.foreach(x => sb.append(",").append(x))
    +    if (sb.length == 0) "1" else sb.substring(1)
    +  }
    +
    +  /**
    +   * Turns a single Filter into a String representing a SQL expression.
    +   * Returns null for an unhandled filter.
    +   */
    +  private def compileFilter(f: Filter): String = f match {
    +    case EqualTo(attr, value) => s"$attr = $value"
    +    case LessThan(attr, value) => s"$attr < $value"
    +    case GreaterThan(attr, value) => s"$attr > $value"
    +    case LessThanOrEqual(attr, value) => s"$attr <= $value"
    +    case GreaterThanOrEqual(attr, value) => s"$attr >= $value"
    +    case _ => null
    +  }
    +
    +  /**
    +   * `filters`, but as a WHERE clause suitable for injection into a SQL query.
    +   */
    +  private val filterWhereClause: String = {
    +    val filterStrings = filters map compileFilter filter (_ != null)
    +    if (filterStrings.size > 0) {
    +      val sb = new StringBuilder("WHERE ")
    +      filterStrings.foreach(x => sb.append(x).append(" AND "))
    +      sb.substring(0, sb.length - 5)
    +    } else ""
    +  }
    +
    +  /**
    +   * A WHERE clause representing both `filters`, if any, and the current partition.
    +   */
    +  private def getWhereClause(part: JDBCPartition): String = {
    +    if (part.whereClause != null && filterWhereClause.length > 0) {
    +      filterWhereClause + " AND " + part.whereClause
    +    } else if (part.whereClause != null) {
    +      "WHERE " + part.whereClause
    +    } else {
    +      filterWhereClause
    +    }
    +  }
    +
    +  // Each JDBC-to-Catalyst conversion corresponds to a tag defined here so that
    +  // we don't have to potentially poke around in the Metadata once for every
    +  // row.  
    +  // Is there a better way to do this?  I'd rather be using a type that
    +  // contains only the tags I define.
    +  abstract class JDBCConversion
    --- End diff --
    
    @rxin: That's a straightforward change.  This conversion code is hot, though; I'd want to benchmark that change and see whether it impairs performance.
    
    To the JVM, iterating over the array and calling one function per field retrieved would look like a single highly-polymorphic call site.  Maybe Hotspot can deal with that now, but it couldn't inline through such call sites 5 years ago.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by rxin <gi...@git.apache.org>.
Github user rxin commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23903940
  
    --- Diff: sql/core/src/main/scala/org/apache/spark/sql/jdbc/JDBCRDD.scala ---
    @@ -0,0 +1,417 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more
    + * contributor license agreements.  See the NOTICE file distributed with
    + * this work for additional information regarding copyright ownership.
    + * The ASF licenses this file to You under the Apache License, Version 2.0
    + * (the "License"); you may not use this file except in compliance with
    + * the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.spark.sql.jdbc
    +
    +import java.sql.{Connection, DatabaseMetaData, DriverManager, ResultSet, ResultSetMetaData, SQLException}
    +import scala.collection.mutable.ArrayBuffer
    +
    +import org.apache.spark.{Logging, Partition, SparkContext, TaskContext}
    +import org.apache.spark.rdd.RDD
    +import org.apache.spark.util.NextIterator
    +import org.apache.spark.sql.catalyst.analysis.HiveTypeCoercion
    +import org.apache.spark.sql.catalyst.expressions.{Row, SpecificMutableRow}
    +import org.apache.spark.sql.types._
    +import org.apache.spark.sql.sources._
    +
    +private[sql] object JDBCRDD extends Logging {
    +  /**
    +   * Maps a JDBC type to a Catalyst type.  This function is called only when
    +   * the DriverQuirks class corresponding to your database driver returns null.
    +   *
    +   * @param sqlType - A field of java.sql.Types
    +   * @return The Catalyst type corresponding to sqlType.
    +   */
    +  private def getCatalystType(sqlType: Int): DataType = {
    +    val answer = sqlType match {
    +      case java.sql.Types.ARRAY         => null
    +      case java.sql.Types.BIGINT        => LongType
    +      case java.sql.Types.BINARY        => BinaryType
    +      case java.sql.Types.BIT           => BooleanType // Per JDBC; Quirks handles quirky drivers.
    +      case java.sql.Types.BLOB          => BinaryType
    +      case java.sql.Types.BOOLEAN       => BooleanType
    +      case java.sql.Types.CHAR          => StringType
    +      case java.sql.Types.CLOB          => StringType
    +      case java.sql.Types.DATALINK      => null
    +      case java.sql.Types.DATE          => DateType
    +      case java.sql.Types.DECIMAL       => DecimalType.Unlimited
    +      case java.sql.Types.DISTINCT      => null
    +      case java.sql.Types.DOUBLE        => DoubleType
    +      case java.sql.Types.FLOAT         => FloatType
    +      case java.sql.Types.INTEGER       => IntegerType
    +      case java.sql.Types.JAVA_OBJECT   => null
    +      case java.sql.Types.LONGNVARCHAR  => StringType
    +      case java.sql.Types.LONGVARBINARY => BinaryType
    +      case java.sql.Types.LONGVARCHAR   => StringType
    +      case java.sql.Types.NCHAR         => StringType
    +      case java.sql.Types.NCLOB         => StringType
    +      case java.sql.Types.NULL          => null
    +      case java.sql.Types.NUMERIC       => DecimalType.Unlimited
    +      case java.sql.Types.OTHER         => null
    +      case java.sql.Types.REAL          => DoubleType
    +      case java.sql.Types.REF           => StringType
    +      case java.sql.Types.ROWID         => LongType
    +      case java.sql.Types.SMALLINT      => IntegerType
    +      case java.sql.Types.SQLXML        => StringType
    +      case java.sql.Types.STRUCT        => StringType
    +      case java.sql.Types.TIME          => TimestampType
    +      case java.sql.Types.TIMESTAMP     => TimestampType
    +      case java.sql.Types.TINYINT       => IntegerType
    +      case java.sql.Types.VARBINARY     => BinaryType
    +      case java.sql.Types.VARCHAR       => StringType
    +      case _ => null
    +    }
    +
    +    if (answer == null) throw new SQLException("Unsupported type " + sqlType)
    +    answer
    +  }
    +
    +  /**
    +   * Takes a (schema, table) specification and returns the table's Catalyst
    +   * schema.
    +   *
    +   * @param url - The JDBC url to fetch information from.
    +   * @param table - The table name of the desired table.  This may also be a
    +   *   SQL query wrapped in parentheses.
    +   *
    +   * @return A StructType giving the table's Catalyst schema.
    +   * @throws SQLException if the table specification is garbage.
    +   * @throws SQLException if the table contains an unsupported type.
    +   */
    +  def resolveTable(url: String, table: String): StructType = {
    +    val quirks = DriverQuirks.get(url)
    +    val conn: Connection = DriverManager.getConnection(url)
    +    try {
    +      val rs = conn.prepareStatement(s"SELECT * FROM $table WHERE 1=0").executeQuery()
    +      try {
    +        val rsmd = rs.getMetaData
    +        val ncols = rsmd.getColumnCount
    +        var fields = new Array[StructField](ncols);
    +        var i = 0
    +        while (i < ncols) {
    +          val columnName = rsmd.getColumnName(i + 1)
    +          val dataType = rsmd.getColumnType(i + 1)
    +          val typeName = rsmd.getColumnTypeName(i + 1)
    +          val fieldSize = rsmd.getPrecision(i + 1)
    +          val nullable = rsmd.isNullable(i + 1) != ResultSetMetaData.columnNoNulls
    +          val metadata = new MetadataBuilder().putString("name", columnName)
    +          var columnType = quirks.getCatalystType(dataType, typeName, fieldSize, metadata)
    +          if (columnType == null) columnType = getCatalystType(dataType)
    +          fields(i) = StructField(columnName, columnType, nullable, metadata.build())
    +          i = i + 1
    +        }
    +        return new StructType(fields)
    +      } finally {
    +        rs.close()
    +      }
    +    } finally {
    +      conn.close()
    +    }
    +
    +    throw new RuntimeException("This line is unreachable.")
    +  }
    +
    +  /**
    +   * Prune all but the specified columns from the specified Catalyst schema.
    +   *
    +   * @param schema - The Catalyst schema of the master table
    +   * @param columns - The list of desired columns
    +   *
    +   * @return A Catalyst schema corresponding to columns in the given order.
    +   */
    +  private def pruneSchema(schema: StructType, columns: Array[String]): StructType = {
    +    val fieldMap = Map(schema.fields map { x => x.metadata.getString("name") -> x }: _*)
    +    new StructType(columns map { name => fieldMap(name) })
    +  }
    +
    +  /**
    +   * Given a driver string and an url, return a function that loads the
    +   * specified driver string then returns a connection to the JDBC url.
    +   * getConnector is run on the driver code, while the function it returns
    +   * is run on the executor.
    +   *
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   *
    +   * @return A function that loads the driver and connects to the url.
    +   */
    +  def getConnector(driver: String, url: String): () => Connection = {
    +    () => {
    +      try {
    +        if (driver != null) Class.forName(driver)
    +      } catch {
    +        case e: ClassNotFoundException => {
    +          logWarning(s"Couldn't find class $driver", e);
    +        }
    +      }
    +      DriverManager.getConnection(url)
    +    }
    +  }
    +  /**
    +   * Build and return JDBCRDD from the given information.
    +   *
    +   * @param sc - Your SparkContext.
    +   * @param schema - The Catalyst schema of the underlying database table.
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   * @param fqTable - The fully-qualified table name (or paren'd SQL query) to use.
    +   * @param requiredColumns - The names of the columns to SELECT.
    +   * @param filters - The filters to include in all WHERE clauses.
    +   * @param parts - An array of JDBCPartitions specifying partition ids and
    +   *    per-partition WHERE clauses.
    +   *
    +   * @return An RDD representing "SELECT requiredColumns FROM fqTable".
    +   */
    +  def scanTable(sc: SparkContext,
    +                schema: StructType,
    +                driver: String,
    +                url: String,
    +                fqTable: String,
    +                requiredColumns: Array[String],
    +                filters: Array[Filter],
    +                parts: Array[Partition]): RDD[Row] = {
    +    val prunedSchema = pruneSchema(schema, requiredColumns)
    +
    +    return new JDBCRDD(sc,
    +        getConnector(driver, url),
    +        prunedSchema,
    +        fqTable,
    +        requiredColumns,
    +        filters,
    +        parts)
    +  }
    +}
    +
    +/**
    + * An RDD representing a table in a database accessed via JDBC.  Both the
    + * driver code and the workers must be able to access the database; the driver
    + * needs to fetch the schema while the workers need to fetch the data.
    + */
    +private[sql] class JDBCRDD(
    +    sc: SparkContext,
    +    getConnection: () => Connection,
    +    schema: StructType,
    +    fqTable: String,
    +    columns: Array[String],
    +    filters: Array[Filter],
    +    partitions: Array[Partition])
    +  extends RDD[Row](sc, Nil) {
    +
    +  /**
    +   * Retrieve the list of partitions corresponding to this RDD.
    +   */
    +  override def getPartitions: Array[Partition] = partitions
    +
    +  /**
    +   * `columns`, but as a String suitable for injection into a SQL query.
    +   */
    +  private val columnList: String = {
    +    val sb = new StringBuilder()
    +    columns.foreach(x => sb.append(",").append(x))
    +    if (sb.length == 0) "1" else sb.substring(1)
    +  }
    +
    +  /**
    +   * Turns a single Filter into a String representing a SQL expression.
    +   * Returns null for an unhandled filter.
    +   */
    +  private def compileFilter(f: Filter): String = f match {
    +    case EqualTo(attr, value) => s"$attr = $value"
    +    case LessThan(attr, value) => s"$attr < $value"
    +    case GreaterThan(attr, value) => s"$attr > $value"
    +    case LessThanOrEqual(attr, value) => s"$attr <= $value"
    +    case GreaterThanOrEqual(attr, value) => s"$attr >= $value"
    +    case _ => null
    +  }
    +
    +  /**
    +   * `filters`, but as a WHERE clause suitable for injection into a SQL query.
    +   */
    +  private val filterWhereClause: String = {
    +    val filterStrings = filters map compileFilter filter (_ != null)
    +    if (filterStrings.size > 0) {
    +      val sb = new StringBuilder("WHERE ")
    +      filterStrings.foreach(x => sb.append(x).append(" AND "))
    +      sb.substring(0, sb.length - 5)
    +    } else ""
    +  }
    +
    +  /**
    +   * A WHERE clause representing both `filters`, if any, and the current partition.
    +   */
    +  private def getWhereClause(part: JDBCPartition): String = {
    +    if (part.whereClause != null && filterWhereClause.length > 0) {
    +      filterWhereClause + " AND " + part.whereClause
    +    } else if (part.whereClause != null) {
    +      "WHERE " + part.whereClause
    +    } else {
    +      filterWhereClause
    +    }
    +  }
    +
    +  // Each JDBC-to-Catalyst conversion corresponds to a tag defined here so that
    +  // we don't have to potentially poke around in the Metadata once for every
    +  // row.  
    +  // Is there a better way to do this?  I'd rather be using a type that
    +  // contains only the tags I define.
    +  abstract class JDBCConversion
    --- End diff --
    
    how about just creating a bunch of conversion functions, and then put those into an array and invoke them?


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by liancheng <gi...@git.apache.org>.
Github user liancheng commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23980822
  
    --- Diff: sql/core/src/main/scala/org/apache/spark/sql/jdbc/JDBCRDD.scala ---
    @@ -0,0 +1,417 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more
    + * contributor license agreements.  See the NOTICE file distributed with
    + * this work for additional information regarding copyright ownership.
    + * The ASF licenses this file to You under the Apache License, Version 2.0
    + * (the "License"); you may not use this file except in compliance with
    + * the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.spark.sql.jdbc
    +
    +import java.sql.{Connection, DatabaseMetaData, DriverManager, ResultSet, ResultSetMetaData, SQLException}
    +import scala.collection.mutable.ArrayBuffer
    +
    +import org.apache.spark.{Logging, Partition, SparkContext, TaskContext}
    +import org.apache.spark.rdd.RDD
    +import org.apache.spark.util.NextIterator
    +import org.apache.spark.sql.catalyst.analysis.HiveTypeCoercion
    +import org.apache.spark.sql.catalyst.expressions.{Row, SpecificMutableRow}
    +import org.apache.spark.sql.types._
    +import org.apache.spark.sql.sources._
    +
    +private[sql] object JDBCRDD extends Logging {
    +  /**
    +   * Maps a JDBC type to a Catalyst type.  This function is called only when
    +   * the DriverQuirks class corresponding to your database driver returns null.
    +   *
    +   * @param sqlType - A field of java.sql.Types
    +   * @return The Catalyst type corresponding to sqlType.
    +   */
    +  private def getCatalystType(sqlType: Int): DataType = {
    +    val answer = sqlType match {
    +      case java.sql.Types.ARRAY         => null
    +      case java.sql.Types.BIGINT        => LongType
    +      case java.sql.Types.BINARY        => BinaryType
    +      case java.sql.Types.BIT           => BooleanType // Per JDBC; Quirks handles quirky drivers.
    +      case java.sql.Types.BLOB          => BinaryType
    +      case java.sql.Types.BOOLEAN       => BooleanType
    +      case java.sql.Types.CHAR          => StringType
    +      case java.sql.Types.CLOB          => StringType
    +      case java.sql.Types.DATALINK      => null
    +      case java.sql.Types.DATE          => DateType
    +      case java.sql.Types.DECIMAL       => DecimalType.Unlimited
    +      case java.sql.Types.DISTINCT      => null
    +      case java.sql.Types.DOUBLE        => DoubleType
    +      case java.sql.Types.FLOAT         => FloatType
    +      case java.sql.Types.INTEGER       => IntegerType
    +      case java.sql.Types.JAVA_OBJECT   => null
    +      case java.sql.Types.LONGNVARCHAR  => StringType
    +      case java.sql.Types.LONGVARBINARY => BinaryType
    +      case java.sql.Types.LONGVARCHAR   => StringType
    +      case java.sql.Types.NCHAR         => StringType
    +      case java.sql.Types.NCLOB         => StringType
    +      case java.sql.Types.NULL          => null
    +      case java.sql.Types.NUMERIC       => DecimalType.Unlimited
    +      case java.sql.Types.OTHER         => null
    +      case java.sql.Types.REAL          => DoubleType
    +      case java.sql.Types.REF           => StringType
    +      case java.sql.Types.ROWID         => LongType
    +      case java.sql.Types.SMALLINT      => IntegerType
    +      case java.sql.Types.SQLXML        => StringType
    +      case java.sql.Types.STRUCT        => StringType
    +      case java.sql.Types.TIME          => TimestampType
    +      case java.sql.Types.TIMESTAMP     => TimestampType
    +      case java.sql.Types.TINYINT       => IntegerType
    +      case java.sql.Types.VARBINARY     => BinaryType
    +      case java.sql.Types.VARCHAR       => StringType
    +      case _ => null
    +    }
    +
    +    if (answer == null) throw new SQLException("Unsupported type " + sqlType)
    +    answer
    +  }
    +
    +  /**
    +   * Takes a (schema, table) specification and returns the table's Catalyst
    +   * schema.
    +   *
    +   * @param url - The JDBC url to fetch information from.
    +   * @param table - The table name of the desired table.  This may also be a
    +   *   SQL query wrapped in parentheses.
    +   *
    +   * @return A StructType giving the table's Catalyst schema.
    +   * @throws SQLException if the table specification is garbage.
    +   * @throws SQLException if the table contains an unsupported type.
    +   */
    +  def resolveTable(url: String, table: String): StructType = {
    +    val quirks = DriverQuirks.get(url)
    +    val conn: Connection = DriverManager.getConnection(url)
    +    try {
    +      val rs = conn.prepareStatement(s"SELECT * FROM $table WHERE 1=0").executeQuery()
    +      try {
    +        val rsmd = rs.getMetaData
    +        val ncols = rsmd.getColumnCount
    +        var fields = new Array[StructField](ncols);
    +        var i = 0
    +        while (i < ncols) {
    +          val columnName = rsmd.getColumnName(i + 1)
    +          val dataType = rsmd.getColumnType(i + 1)
    +          val typeName = rsmd.getColumnTypeName(i + 1)
    +          val fieldSize = rsmd.getPrecision(i + 1)
    +          val nullable = rsmd.isNullable(i + 1) != ResultSetMetaData.columnNoNulls
    +          val metadata = new MetadataBuilder().putString("name", columnName)
    +          var columnType = quirks.getCatalystType(dataType, typeName, fieldSize, metadata)
    +          if (columnType == null) columnType = getCatalystType(dataType)
    +          fields(i) = StructField(columnName, columnType, nullable, metadata.build())
    +          i = i + 1
    +        }
    +        return new StructType(fields)
    --- End diff --
    
    Also, you don't need `new` since `StructType` is a case class.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by rxin <gi...@git.apache.org>.
Github user rxin commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23904605
  
    --- Diff: sql/core/src/main/scala/org/apache/spark/sql/jdbc/JDBCRDD.scala ---
    @@ -0,0 +1,417 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more
    + * contributor license agreements.  See the NOTICE file distributed with
    + * this work for additional information regarding copyright ownership.
    + * The ASF licenses this file to You under the Apache License, Version 2.0
    + * (the "License"); you may not use this file except in compliance with
    + * the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.spark.sql.jdbc
    +
    +import java.sql.{Connection, DatabaseMetaData, DriverManager, ResultSet, ResultSetMetaData, SQLException}
    +import scala.collection.mutable.ArrayBuffer
    +
    +import org.apache.spark.{Logging, Partition, SparkContext, TaskContext}
    +import org.apache.spark.rdd.RDD
    +import org.apache.spark.util.NextIterator
    +import org.apache.spark.sql.catalyst.analysis.HiveTypeCoercion
    +import org.apache.spark.sql.catalyst.expressions.{Row, SpecificMutableRow}
    +import org.apache.spark.sql.types._
    +import org.apache.spark.sql.sources._
    +
    +private[sql] object JDBCRDD extends Logging {
    +  /**
    +   * Maps a JDBC type to a Catalyst type.  This function is called only when
    +   * the DriverQuirks class corresponding to your database driver returns null.
    +   *
    +   * @param sqlType - A field of java.sql.Types
    +   * @return The Catalyst type corresponding to sqlType.
    +   */
    +  private def getCatalystType(sqlType: Int): DataType = {
    +    val answer = sqlType match {
    +      case java.sql.Types.ARRAY         => null
    +      case java.sql.Types.BIGINT        => LongType
    +      case java.sql.Types.BINARY        => BinaryType
    +      case java.sql.Types.BIT           => BooleanType // Per JDBC; Quirks handles quirky drivers.
    +      case java.sql.Types.BLOB          => BinaryType
    +      case java.sql.Types.BOOLEAN       => BooleanType
    +      case java.sql.Types.CHAR          => StringType
    +      case java.sql.Types.CLOB          => StringType
    +      case java.sql.Types.DATALINK      => null
    +      case java.sql.Types.DATE          => DateType
    +      case java.sql.Types.DECIMAL       => DecimalType.Unlimited
    +      case java.sql.Types.DISTINCT      => null
    +      case java.sql.Types.DOUBLE        => DoubleType
    +      case java.sql.Types.FLOAT         => FloatType
    +      case java.sql.Types.INTEGER       => IntegerType
    +      case java.sql.Types.JAVA_OBJECT   => null
    +      case java.sql.Types.LONGNVARCHAR  => StringType
    +      case java.sql.Types.LONGVARBINARY => BinaryType
    +      case java.sql.Types.LONGVARCHAR   => StringType
    +      case java.sql.Types.NCHAR         => StringType
    +      case java.sql.Types.NCLOB         => StringType
    +      case java.sql.Types.NULL          => null
    +      case java.sql.Types.NUMERIC       => DecimalType.Unlimited
    +      case java.sql.Types.OTHER         => null
    +      case java.sql.Types.REAL          => DoubleType
    +      case java.sql.Types.REF           => StringType
    +      case java.sql.Types.ROWID         => LongType
    +      case java.sql.Types.SMALLINT      => IntegerType
    +      case java.sql.Types.SQLXML        => StringType
    +      case java.sql.Types.STRUCT        => StringType
    +      case java.sql.Types.TIME          => TimestampType
    +      case java.sql.Types.TIMESTAMP     => TimestampType
    +      case java.sql.Types.TINYINT       => IntegerType
    +      case java.sql.Types.VARBINARY     => BinaryType
    +      case java.sql.Types.VARCHAR       => StringType
    +      case _ => null
    +    }
    +
    +    if (answer == null) throw new SQLException("Unsupported type " + sqlType)
    +    answer
    +  }
    +
    +  /**
    +   * Takes a (schema, table) specification and returns the table's Catalyst
    +   * schema.
    +   *
    +   * @param url - The JDBC url to fetch information from.
    +   * @param table - The table name of the desired table.  This may also be a
    +   *   SQL query wrapped in parentheses.
    +   *
    +   * @return A StructType giving the table's Catalyst schema.
    +   * @throws SQLException if the table specification is garbage.
    +   * @throws SQLException if the table contains an unsupported type.
    +   */
    +  def resolveTable(url: String, table: String): StructType = {
    +    val quirks = DriverQuirks.get(url)
    +    val conn: Connection = DriverManager.getConnection(url)
    +    try {
    +      val rs = conn.prepareStatement(s"SELECT * FROM $table WHERE 1=0").executeQuery()
    +      try {
    +        val rsmd = rs.getMetaData
    +        val ncols = rsmd.getColumnCount
    +        var fields = new Array[StructField](ncols);
    +        var i = 0
    +        while (i < ncols) {
    +          val columnName = rsmd.getColumnName(i + 1)
    +          val dataType = rsmd.getColumnType(i + 1)
    +          val typeName = rsmd.getColumnTypeName(i + 1)
    +          val fieldSize = rsmd.getPrecision(i + 1)
    +          val nullable = rsmd.isNullable(i + 1) != ResultSetMetaData.columnNoNulls
    +          val metadata = new MetadataBuilder().putString("name", columnName)
    +          var columnType = quirks.getCatalystType(dataType, typeName, fieldSize, metadata)
    +          if (columnType == null) columnType = getCatalystType(dataType)
    +          fields(i) = StructField(columnName, columnType, nullable, metadata.build())
    +          i = i + 1
    +        }
    +        return new StructType(fields)
    +      } finally {
    +        rs.close()
    +      }
    +    } finally {
    +      conn.close()
    +    }
    +
    +    throw new RuntimeException("This line is unreachable.")
    +  }
    +
    +  /**
    +   * Prune all but the specified columns from the specified Catalyst schema.
    +   *
    +   * @param schema - The Catalyst schema of the master table
    +   * @param columns - The list of desired columns
    +   *
    +   * @return A Catalyst schema corresponding to columns in the given order.
    +   */
    +  private def pruneSchema(schema: StructType, columns: Array[String]): StructType = {
    +    val fieldMap = Map(schema.fields map { x => x.metadata.getString("name") -> x }: _*)
    +    new StructType(columns map { name => fieldMap(name) })
    +  }
    +
    +  /**
    +   * Given a driver string and an url, return a function that loads the
    +   * specified driver string then returns a connection to the JDBC url.
    +   * getConnector is run on the driver code, while the function it returns
    +   * is run on the executor.
    +   *
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   *
    +   * @return A function that loads the driver and connects to the url.
    +   */
    +  def getConnector(driver: String, url: String): () => Connection = {
    +    () => {
    +      try {
    +        if (driver != null) Class.forName(driver)
    +      } catch {
    +        case e: ClassNotFoundException => {
    +          logWarning(s"Couldn't find class $driver", e);
    +        }
    +      }
    +      DriverManager.getConnection(url)
    +    }
    +  }
    +  /**
    +   * Build and return JDBCRDD from the given information.
    +   *
    +   * @param sc - Your SparkContext.
    +   * @param schema - The Catalyst schema of the underlying database table.
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   * @param fqTable - The fully-qualified table name (or paren'd SQL query) to use.
    +   * @param requiredColumns - The names of the columns to SELECT.
    +   * @param filters - The filters to include in all WHERE clauses.
    +   * @param parts - An array of JDBCPartitions specifying partition ids and
    +   *    per-partition WHERE clauses.
    +   *
    +   * @return An RDD representing "SELECT requiredColumns FROM fqTable".
    +   */
    +  def scanTable(sc: SparkContext,
    +                schema: StructType,
    +                driver: String,
    +                url: String,
    +                fqTable: String,
    +                requiredColumns: Array[String],
    +                filters: Array[Filter],
    +                parts: Array[Partition]): RDD[Row] = {
    +    val prunedSchema = pruneSchema(schema, requiredColumns)
    +
    +    return new JDBCRDD(sc,
    +        getConnector(driver, url),
    +        prunedSchema,
    +        fqTable,
    +        requiredColumns,
    +        filters,
    +        parts)
    +  }
    +}
    +
    +/**
    + * An RDD representing a table in a database accessed via JDBC.  Both the
    + * driver code and the workers must be able to access the database; the driver
    + * needs to fetch the schema while the workers need to fetch the data.
    + */
    +private[sql] class JDBCRDD(
    +    sc: SparkContext,
    +    getConnection: () => Connection,
    +    schema: StructType,
    +    fqTable: String,
    +    columns: Array[String],
    +    filters: Array[Filter],
    +    partitions: Array[Partition])
    +  extends RDD[Row](sc, Nil) {
    +
    +  /**
    +   * Retrieve the list of partitions corresponding to this RDD.
    +   */
    +  override def getPartitions: Array[Partition] = partitions
    +
    +  /**
    +   * `columns`, but as a String suitable for injection into a SQL query.
    +   */
    +  private val columnList: String = {
    +    val sb = new StringBuilder()
    +    columns.foreach(x => sb.append(",").append(x))
    +    if (sb.length == 0) "1" else sb.substring(1)
    +  }
    +
    +  /**
    +   * Turns a single Filter into a String representing a SQL expression.
    +   * Returns null for an unhandled filter.
    +   */
    +  private def compileFilter(f: Filter): String = f match {
    +    case EqualTo(attr, value) => s"$attr = $value"
    +    case LessThan(attr, value) => s"$attr < $value"
    +    case GreaterThan(attr, value) => s"$attr > $value"
    +    case LessThanOrEqual(attr, value) => s"$attr <= $value"
    +    case GreaterThanOrEqual(attr, value) => s"$attr >= $value"
    +    case _ => null
    +  }
    +
    +  /**
    +   * `filters`, but as a WHERE clause suitable for injection into a SQL query.
    +   */
    +  private val filterWhereClause: String = {
    +    val filterStrings = filters map compileFilter filter (_ != null)
    +    if (filterStrings.size > 0) {
    +      val sb = new StringBuilder("WHERE ")
    +      filterStrings.foreach(x => sb.append(x).append(" AND "))
    +      sb.substring(0, sb.length - 5)
    +    } else ""
    +  }
    +
    +  /**
    +   * A WHERE clause representing both `filters`, if any, and the current partition.
    +   */
    +  private def getWhereClause(part: JDBCPartition): String = {
    +    if (part.whereClause != null && filterWhereClause.length > 0) {
    +      filterWhereClause + " AND " + part.whereClause
    +    } else if (part.whereClause != null) {
    +      "WHERE " + part.whereClause
    +    } else {
    +      filterWhereClause
    +    }
    +  }
    +
    +  // Each JDBC-to-Catalyst conversion corresponds to a tag defined here so that
    +  // we don't have to potentially poke around in the Metadata once for every
    +  // row.  
    +  // Is there a better way to do this?  I'd rather be using a type that
    +  // contains only the tags I define.
    +  abstract class JDBCConversion
    --- End diff --
    
    btw do we care as much about performance here? seems like jdbc itself can be very slow anyway ....


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by rxin <gi...@git.apache.org>.
Github user rxin commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23903163
  
    --- Diff: sql/core/src/main/scala/org/apache/spark/sql/jdbc/JDBCRDD.scala ---
    @@ -0,0 +1,417 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more
    + * contributor license agreements.  See the NOTICE file distributed with
    + * this work for additional information regarding copyright ownership.
    + * The ASF licenses this file to You under the Apache License, Version 2.0
    + * (the "License"); you may not use this file except in compliance with
    + * the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.spark.sql.jdbc
    +
    +import java.sql.{Connection, DatabaseMetaData, DriverManager, ResultSet, ResultSetMetaData, SQLException}
    +import scala.collection.mutable.ArrayBuffer
    +
    +import org.apache.spark.{Logging, Partition, SparkContext, TaskContext}
    +import org.apache.spark.rdd.RDD
    +import org.apache.spark.util.NextIterator
    +import org.apache.spark.sql.catalyst.analysis.HiveTypeCoercion
    +import org.apache.spark.sql.catalyst.expressions.{Row, SpecificMutableRow}
    +import org.apache.spark.sql.types._
    +import org.apache.spark.sql.sources._
    +
    +private[sql] object JDBCRDD extends Logging {
    +  /**
    +   * Maps a JDBC type to a Catalyst type.  This function is called only when
    +   * the DriverQuirks class corresponding to your database driver returns null.
    +   *
    +   * @param sqlType - A field of java.sql.Types
    +   * @return The Catalyst type corresponding to sqlType.
    +   */
    +  private def getCatalystType(sqlType: Int): DataType = {
    +    val answer = sqlType match {
    +      case java.sql.Types.ARRAY         => null
    +      case java.sql.Types.BIGINT        => LongType
    +      case java.sql.Types.BINARY        => BinaryType
    +      case java.sql.Types.BIT           => BooleanType // Per JDBC; Quirks handles quirky drivers.
    +      case java.sql.Types.BLOB          => BinaryType
    +      case java.sql.Types.BOOLEAN       => BooleanType
    +      case java.sql.Types.CHAR          => StringType
    +      case java.sql.Types.CLOB          => StringType
    +      case java.sql.Types.DATALINK      => null
    +      case java.sql.Types.DATE          => DateType
    +      case java.sql.Types.DECIMAL       => DecimalType.Unlimited
    +      case java.sql.Types.DISTINCT      => null
    +      case java.sql.Types.DOUBLE        => DoubleType
    +      case java.sql.Types.FLOAT         => FloatType
    +      case java.sql.Types.INTEGER       => IntegerType
    +      case java.sql.Types.JAVA_OBJECT   => null
    +      case java.sql.Types.LONGNVARCHAR  => StringType
    +      case java.sql.Types.LONGVARBINARY => BinaryType
    +      case java.sql.Types.LONGVARCHAR   => StringType
    +      case java.sql.Types.NCHAR         => StringType
    +      case java.sql.Types.NCLOB         => StringType
    +      case java.sql.Types.NULL          => null
    +      case java.sql.Types.NUMERIC       => DecimalType.Unlimited
    +      case java.sql.Types.OTHER         => null
    +      case java.sql.Types.REAL          => DoubleType
    +      case java.sql.Types.REF           => StringType
    +      case java.sql.Types.ROWID         => LongType
    +      case java.sql.Types.SMALLINT      => IntegerType
    +      case java.sql.Types.SQLXML        => StringType
    +      case java.sql.Types.STRUCT        => StringType
    +      case java.sql.Types.TIME          => TimestampType
    +      case java.sql.Types.TIMESTAMP     => TimestampType
    +      case java.sql.Types.TINYINT       => IntegerType
    +      case java.sql.Types.VARBINARY     => BinaryType
    +      case java.sql.Types.VARCHAR       => StringType
    +      case _ => null
    +    }
    +
    +    if (answer == null) throw new SQLException("Unsupported type " + sqlType)
    +    answer
    +  }
    +
    +  /**
    +   * Takes a (schema, table) specification and returns the table's Catalyst
    +   * schema.
    +   *
    +   * @param url - The JDBC url to fetch information from.
    +   * @param table - The table name of the desired table.  This may also be a
    +   *   SQL query wrapped in parentheses.
    +   *
    +   * @return A StructType giving the table's Catalyst schema.
    +   * @throws SQLException if the table specification is garbage.
    +   * @throws SQLException if the table contains an unsupported type.
    +   */
    +  def resolveTable(url: String, table: String): StructType = {
    +    val quirks = DriverQuirks.get(url)
    +    val conn: Connection = DriverManager.getConnection(url)
    +    try {
    +      val rs = conn.prepareStatement(s"SELECT * FROM $table WHERE 1=0").executeQuery()
    +      try {
    +        val rsmd = rs.getMetaData
    +        val ncols = rsmd.getColumnCount
    +        var fields = new Array[StructField](ncols);
    +        var i = 0
    +        while (i < ncols) {
    +          val columnName = rsmd.getColumnName(i + 1)
    +          val dataType = rsmd.getColumnType(i + 1)
    +          val typeName = rsmd.getColumnTypeName(i + 1)
    +          val fieldSize = rsmd.getPrecision(i + 1)
    +          val nullable = rsmd.isNullable(i + 1) != ResultSetMetaData.columnNoNulls
    +          val metadata = new MetadataBuilder().putString("name", columnName)
    +          var columnType = quirks.getCatalystType(dataType, typeName, fieldSize, metadata)
    +          if (columnType == null) columnType = getCatalystType(dataType)
    +          fields(i) = StructField(columnName, columnType, nullable, metadata.build())
    +          i = i + 1
    +        }
    +        return new StructType(fields)
    +      } finally {
    +        rs.close()
    +      }
    +    } finally {
    +      conn.close()
    +    }
    +
    +    throw new RuntimeException("This line is unreachable.")
    +  }
    +
    +  /**
    +   * Prune all but the specified columns from the specified Catalyst schema.
    +   *
    +   * @param schema - The Catalyst schema of the master table
    +   * @param columns - The list of desired columns
    +   *
    +   * @return A Catalyst schema corresponding to columns in the given order.
    +   */
    +  private def pruneSchema(schema: StructType, columns: Array[String]): StructType = {
    +    val fieldMap = Map(schema.fields map { x => x.metadata.getString("name") -> x }: _*)
    +    new StructType(columns map { name => fieldMap(name) })
    +  }
    +
    +  /**
    +   * Given a driver string and an url, return a function that loads the
    +   * specified driver string then returns a connection to the JDBC url.
    +   * getConnector is run on the driver code, while the function it returns
    +   * is run on the executor.
    +   *
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   *
    +   * @return A function that loads the driver and connects to the url.
    +   */
    +  def getConnector(driver: String, url: String): () => Connection = {
    +    () => {
    +      try {
    +        if (driver != null) Class.forName(driver)
    +      } catch {
    +        case e: ClassNotFoundException => {
    +          logWarning(s"Couldn't find class $driver", e);
    +        }
    +      }
    +      DriverManager.getConnection(url)
    +    }
    +  }
    +  /**
    +   * Build and return JDBCRDD from the given information.
    +   *
    +   * @param sc - Your SparkContext.
    +   * @param schema - The Catalyst schema of the underlying database table.
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   * @param fqTable - The fully-qualified table name (or paren'd SQL query) to use.
    +   * @param requiredColumns - The names of the columns to SELECT.
    +   * @param filters - The filters to include in all WHERE clauses.
    +   * @param parts - An array of JDBCPartitions specifying partition ids and
    +   *    per-partition WHERE clauses.
    +   *
    +   * @return An RDD representing "SELECT requiredColumns FROM fqTable".
    +   */
    +  def scanTable(sc: SparkContext,
    --- End diff --
    
    just a quick note - the spark coding style does 4 space indent for function parameters in definitions. e.g.
    ```scala
    def scanTable(
        sc: SparkContext,
        schema: StructType,
        ...): RDD[Row => {
    ```


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-72230810
  
    Test PASSed.
    Refer to this link for build results (access rights to CI server needed): 
    https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/26403/
    Test PASSed.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by rxin <gi...@git.apache.org>.
Github user rxin commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23902625
  
    --- Diff: sql/core/src/main/scala/org/apache/spark/sql/jdbc/JDBCRDD.scala ---
    @@ -0,0 +1,417 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more
    + * contributor license agreements.  See the NOTICE file distributed with
    + * this work for additional information regarding copyright ownership.
    + * The ASF licenses this file to You under the Apache License, Version 2.0
    + * (the "License"); you may not use this file except in compliance with
    + * the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.spark.sql.jdbc
    +
    +import java.sql.{Connection, DatabaseMetaData, DriverManager, ResultSet, ResultSetMetaData, SQLException}
    +import scala.collection.mutable.ArrayBuffer
    +
    +import org.apache.spark.{Logging, Partition, SparkContext, TaskContext}
    +import org.apache.spark.rdd.RDD
    +import org.apache.spark.util.NextIterator
    +import org.apache.spark.sql.catalyst.analysis.HiveTypeCoercion
    +import org.apache.spark.sql.catalyst.expressions.{Row, SpecificMutableRow}
    +import org.apache.spark.sql.types._
    +import org.apache.spark.sql.sources._
    +
    +private[sql] object JDBCRDD extends Logging {
    +  /**
    +   * Maps a JDBC type to a Catalyst type.  This function is called only when
    +   * the DriverQuirks class corresponding to your database driver returns null.
    +   *
    +   * @param sqlType - A field of java.sql.Types
    +   * @return The Catalyst type corresponding to sqlType.
    +   */
    +  private def getCatalystType(sqlType: Int): DataType = {
    +    val answer = sqlType match {
    +      case java.sql.Types.ARRAY         => null
    +      case java.sql.Types.BIGINT        => LongType
    +      case java.sql.Types.BINARY        => BinaryType
    +      case java.sql.Types.BIT           => BooleanType // Per JDBC; Quirks handles quirky drivers.
    +      case java.sql.Types.BLOB          => BinaryType
    +      case java.sql.Types.BOOLEAN       => BooleanType
    +      case java.sql.Types.CHAR          => StringType
    +      case java.sql.Types.CLOB          => StringType
    +      case java.sql.Types.DATALINK      => null
    +      case java.sql.Types.DATE          => DateType
    +      case java.sql.Types.DECIMAL       => DecimalType.Unlimited
    +      case java.sql.Types.DISTINCT      => null
    +      case java.sql.Types.DOUBLE        => DoubleType
    +      case java.sql.Types.FLOAT         => FloatType
    +      case java.sql.Types.INTEGER       => IntegerType
    +      case java.sql.Types.JAVA_OBJECT   => null
    +      case java.sql.Types.LONGNVARCHAR  => StringType
    +      case java.sql.Types.LONGVARBINARY => BinaryType
    +      case java.sql.Types.LONGVARCHAR   => StringType
    +      case java.sql.Types.NCHAR         => StringType
    +      case java.sql.Types.NCLOB         => StringType
    +      case java.sql.Types.NULL          => null
    +      case java.sql.Types.NUMERIC       => DecimalType.Unlimited
    +      case java.sql.Types.OTHER         => null
    +      case java.sql.Types.REAL          => DoubleType
    +      case java.sql.Types.REF           => StringType
    +      case java.sql.Types.ROWID         => LongType
    +      case java.sql.Types.SMALLINT      => IntegerType
    +      case java.sql.Types.SQLXML        => StringType
    +      case java.sql.Types.STRUCT        => StringType
    +      case java.sql.Types.TIME          => TimestampType
    +      case java.sql.Types.TIMESTAMP     => TimestampType
    +      case java.sql.Types.TINYINT       => IntegerType
    +      case java.sql.Types.VARBINARY     => BinaryType
    +      case java.sql.Types.VARCHAR       => StringType
    +      case _ => null
    +    }
    +
    +    if (answer == null) throw new SQLException("Unsupported type " + sqlType)
    +    answer
    +  }
    +
    +  /**
    +   * Takes a (schema, table) specification and returns the table's Catalyst
    +   * schema.
    +   *
    +   * @param url - The JDBC url to fetch information from.
    +   * @param table - The table name of the desired table.  This may also be a
    +   *   SQL query wrapped in parentheses.
    +   *
    +   * @return A StructType giving the table's Catalyst schema.
    +   * @throws SQLException if the table specification is garbage.
    +   * @throws SQLException if the table contains an unsupported type.
    +   */
    +  def resolveTable(url: String, table: String): StructType = {
    +    val quirks = DriverQuirks.get(url)
    +    val conn: Connection = DriverManager.getConnection(url)
    +    try {
    +      val rs = conn.prepareStatement(s"SELECT * FROM $table WHERE 1=0").executeQuery()
    +      try {
    +        val rsmd = rs.getMetaData
    +        val ncols = rsmd.getColumnCount
    +        var fields = new Array[StructField](ncols);
    --- End diff --
    
    var -> val. also can remove the trailing semicolon


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by rxin <gi...@git.apache.org>.
Github user rxin commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23901087
  
    --- Diff: sql/core/src/main/scala/org/apache/spark/sql/jdbc/JDBCRDD.scala ---
    @@ -0,0 +1,417 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more
    + * contributor license agreements.  See the NOTICE file distributed with
    + * this work for additional information regarding copyright ownership.
    + * The ASF licenses this file to You under the Apache License, Version 2.0
    + * (the "License"); you may not use this file except in compliance with
    + * the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.spark.sql.jdbc
    +
    +import java.sql.{Connection, DatabaseMetaData, DriverManager, ResultSet, ResultSetMetaData, SQLException}
    +import scala.collection.mutable.ArrayBuffer
    +
    +import org.apache.spark.{Logging, Partition, SparkContext, TaskContext}
    +import org.apache.spark.rdd.RDD
    +import org.apache.spark.util.NextIterator
    +import org.apache.spark.sql.catalyst.analysis.HiveTypeCoercion
    +import org.apache.spark.sql.catalyst.expressions.{Row, SpecificMutableRow}
    +import org.apache.spark.sql.types._
    +import org.apache.spark.sql.sources._
    +
    +private[sql] object JDBCRDD extends Logging {
    +  /**
    +   * Maps a JDBC type to a Catalyst type.  This function is called only when
    +   * the DriverQuirks class corresponding to your database driver returns null.
    +   *
    +   * @param sqlType - A field of java.sql.Types
    +   * @return The Catalyst type corresponding to sqlType.
    +   */
    +  private def getCatalystType(sqlType: Int): DataType = {
    +    val answer = sqlType match {
    +      case java.sql.Types.ARRAY         => null
    +      case java.sql.Types.BIGINT        => LongType
    +      case java.sql.Types.BINARY        => BinaryType
    +      case java.sql.Types.BIT           => BooleanType // Per JDBC; Quirks handles quirky drivers.
    +      case java.sql.Types.BLOB          => BinaryType
    +      case java.sql.Types.BOOLEAN       => BooleanType
    +      case java.sql.Types.CHAR          => StringType
    +      case java.sql.Types.CLOB          => StringType
    +      case java.sql.Types.DATALINK      => null
    +      case java.sql.Types.DATE          => DateType
    +      case java.sql.Types.DECIMAL       => DecimalType.Unlimited
    +      case java.sql.Types.DISTINCT      => null
    +      case java.sql.Types.DOUBLE        => DoubleType
    +      case java.sql.Types.FLOAT         => FloatType
    +      case java.sql.Types.INTEGER       => IntegerType
    +      case java.sql.Types.JAVA_OBJECT   => null
    +      case java.sql.Types.LONGNVARCHAR  => StringType
    +      case java.sql.Types.LONGVARBINARY => BinaryType
    +      case java.sql.Types.LONGVARCHAR   => StringType
    +      case java.sql.Types.NCHAR         => StringType
    +      case java.sql.Types.NCLOB         => StringType
    +      case java.sql.Types.NULL          => null
    +      case java.sql.Types.NUMERIC       => DecimalType.Unlimited
    +      case java.sql.Types.OTHER         => null
    +      case java.sql.Types.REAL          => DoubleType
    +      case java.sql.Types.REF           => StringType
    +      case java.sql.Types.ROWID         => LongType
    +      case java.sql.Types.SMALLINT      => IntegerType
    +      case java.sql.Types.SQLXML        => StringType
    +      case java.sql.Types.STRUCT        => StringType
    +      case java.sql.Types.TIME          => TimestampType
    +      case java.sql.Types.TIMESTAMP     => TimestampType
    +      case java.sql.Types.TINYINT       => IntegerType
    +      case java.sql.Types.VARBINARY     => BinaryType
    +      case java.sql.Types.VARCHAR       => StringType
    +      case _ => null
    +    }
    +
    +    if (answer == null) throw new SQLException("Unsupported type " + sqlType)
    +    answer
    +  }
    +
    +  /**
    +   * Takes a (schema, table) specification and returns the table's Catalyst
    +   * schema.
    +   *
    +   * @param url - The JDBC url to fetch information from.
    +   * @param table - The table name of the desired table.  This may also be a
    +   *   SQL query wrapped in parentheses.
    +   *
    +   * @return A StructType giving the table's Catalyst schema.
    +   * @throws SQLException if the table specification is garbage.
    +   * @throws SQLException if the table contains an unsupported type.
    +   */
    +  def resolveTable(url: String, table: String): StructType = {
    +    val quirks = DriverQuirks.get(url)
    +    val conn: Connection = DriverManager.getConnection(url)
    +    try {
    +      val rs = conn.prepareStatement(s"SELECT * FROM $table WHERE 1=0").executeQuery()
    +      try {
    +        val rsmd = rs.getMetaData
    +        val ncols = rsmd.getColumnCount
    +        var fields = new Array[StructField](ncols);
    +        var i = 0
    +        while (i < ncols) {
    +          val columnName = rsmd.getColumnName(i + 1)
    +          val dataType = rsmd.getColumnType(i + 1)
    +          val typeName = rsmd.getColumnTypeName(i + 1)
    +          val fieldSize = rsmd.getPrecision(i + 1)
    +          val nullable = rsmd.isNullable(i + 1) != ResultSetMetaData.columnNoNulls
    +          val metadata = new MetadataBuilder().putString("name", columnName)
    +          var columnType = quirks.getCatalystType(dataType, typeName, fieldSize, metadata)
    +          if (columnType == null) columnType = getCatalystType(dataType)
    +          fields(i) = StructField(columnName, columnType, nullable, metadata.build())
    +          i = i + 1
    +        }
    +        return new StructType(fields)
    +      } finally {
    +        rs.close()
    +      }
    +    } finally {
    +      conn.close()
    +    }
    +
    +    throw new RuntimeException("This line is unreachable.")
    --- End diff --
    
    ah nvm you didn't catch the exception


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by liancheng <gi...@git.apache.org>.
Github user liancheng commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23980805
  
    --- Diff: sql/core/src/main/scala/org/apache/spark/sql/jdbc/JDBCRDD.scala ---
    @@ -0,0 +1,417 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more
    + * contributor license agreements.  See the NOTICE file distributed with
    + * this work for additional information regarding copyright ownership.
    + * The ASF licenses this file to You under the Apache License, Version 2.0
    + * (the "License"); you may not use this file except in compliance with
    + * the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.spark.sql.jdbc
    +
    +import java.sql.{Connection, DatabaseMetaData, DriverManager, ResultSet, ResultSetMetaData, SQLException}
    +import scala.collection.mutable.ArrayBuffer
    +
    +import org.apache.spark.{Logging, Partition, SparkContext, TaskContext}
    +import org.apache.spark.rdd.RDD
    +import org.apache.spark.util.NextIterator
    +import org.apache.spark.sql.catalyst.analysis.HiveTypeCoercion
    +import org.apache.spark.sql.catalyst.expressions.{Row, SpecificMutableRow}
    +import org.apache.spark.sql.types._
    +import org.apache.spark.sql.sources._
    +
    +private[sql] object JDBCRDD extends Logging {
    +  /**
    +   * Maps a JDBC type to a Catalyst type.  This function is called only when
    +   * the DriverQuirks class corresponding to your database driver returns null.
    +   *
    +   * @param sqlType - A field of java.sql.Types
    +   * @return The Catalyst type corresponding to sqlType.
    +   */
    +  private def getCatalystType(sqlType: Int): DataType = {
    +    val answer = sqlType match {
    +      case java.sql.Types.ARRAY         => null
    +      case java.sql.Types.BIGINT        => LongType
    +      case java.sql.Types.BINARY        => BinaryType
    +      case java.sql.Types.BIT           => BooleanType // Per JDBC; Quirks handles quirky drivers.
    +      case java.sql.Types.BLOB          => BinaryType
    +      case java.sql.Types.BOOLEAN       => BooleanType
    +      case java.sql.Types.CHAR          => StringType
    +      case java.sql.Types.CLOB          => StringType
    +      case java.sql.Types.DATALINK      => null
    +      case java.sql.Types.DATE          => DateType
    +      case java.sql.Types.DECIMAL       => DecimalType.Unlimited
    +      case java.sql.Types.DISTINCT      => null
    +      case java.sql.Types.DOUBLE        => DoubleType
    +      case java.sql.Types.FLOAT         => FloatType
    +      case java.sql.Types.INTEGER       => IntegerType
    +      case java.sql.Types.JAVA_OBJECT   => null
    +      case java.sql.Types.LONGNVARCHAR  => StringType
    +      case java.sql.Types.LONGVARBINARY => BinaryType
    +      case java.sql.Types.LONGVARCHAR   => StringType
    +      case java.sql.Types.NCHAR         => StringType
    +      case java.sql.Types.NCLOB         => StringType
    +      case java.sql.Types.NULL          => null
    +      case java.sql.Types.NUMERIC       => DecimalType.Unlimited
    +      case java.sql.Types.OTHER         => null
    +      case java.sql.Types.REAL          => DoubleType
    +      case java.sql.Types.REF           => StringType
    +      case java.sql.Types.ROWID         => LongType
    +      case java.sql.Types.SMALLINT      => IntegerType
    +      case java.sql.Types.SQLXML        => StringType
    +      case java.sql.Types.STRUCT        => StringType
    +      case java.sql.Types.TIME          => TimestampType
    +      case java.sql.Types.TIMESTAMP     => TimestampType
    +      case java.sql.Types.TINYINT       => IntegerType
    +      case java.sql.Types.VARBINARY     => BinaryType
    +      case java.sql.Types.VARCHAR       => StringType
    +      case _ => null
    +    }
    +
    +    if (answer == null) throw new SQLException("Unsupported type " + sqlType)
    +    answer
    +  }
    +
    +  /**
    +   * Takes a (schema, table) specification and returns the table's Catalyst
    +   * schema.
    +   *
    +   * @param url - The JDBC url to fetch information from.
    +   * @param table - The table name of the desired table.  This may also be a
    +   *   SQL query wrapped in parentheses.
    +   *
    +   * @return A StructType giving the table's Catalyst schema.
    +   * @throws SQLException if the table specification is garbage.
    +   * @throws SQLException if the table contains an unsupported type.
    +   */
    +  def resolveTable(url: String, table: String): StructType = {
    +    val quirks = DriverQuirks.get(url)
    +    val conn: Connection = DriverManager.getConnection(url)
    +    try {
    +      val rs = conn.prepareStatement(s"SELECT * FROM $table WHERE 1=0").executeQuery()
    +      try {
    +        val rsmd = rs.getMetaData
    +        val ncols = rsmd.getColumnCount
    +        var fields = new Array[StructField](ncols);
    +        var i = 0
    +        while (i < ncols) {
    +          val columnName = rsmd.getColumnName(i + 1)
    +          val dataType = rsmd.getColumnType(i + 1)
    +          val typeName = rsmd.getColumnTypeName(i + 1)
    +          val fieldSize = rsmd.getPrecision(i + 1)
    +          val nullable = rsmd.isNullable(i + 1) != ResultSetMetaData.columnNoNulls
    +          val metadata = new MetadataBuilder().putString("name", columnName)
    +          var columnType = quirks.getCatalystType(dataType, typeName, fieldSize, metadata)
    +          if (columnType == null) columnType = getCatalystType(dataType)
    +          fields(i) = StructField(columnName, columnType, nullable, metadata.build())
    +          i = i + 1
    +        }
    +        return new StructType(fields)
    +      } finally {
    +        rs.close()
    +      }
    +    } finally {
    +      conn.close()
    +    }
    +
    +    throw new RuntimeException("This line is unreachable.")
    --- End diff --
    
    You can just remove the `return`. In this way, types of both `try` expressions are `StructType`, and you don't need the last `RuntimeException`.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: A JDBC driver for Spark SQL.

Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-71964600
  
      [Test build #26286 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/26286/consoleFull) for   PR 4261 at commit [`a09eeac`](https://github.com/apache/spark/commit/a09eeac34c6e3632895ac5a7ef1664804cb832b7).
     * This patch merges cleanly.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:

    https://github.com/apache/spark/pull/4261


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: A JDBC driver for Spark SQL.

Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-71964607
  
      [Test build #26286 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/26286/consoleFull) for   PR 4261 at commit [`a09eeac`](https://github.com/apache/spark/commit/a09eeac34c6e3632895ac5a7ef1664804cb832b7).
     * This patch **fails RAT tests**.
     * This patch merges cleanly.
     * This patch adds the following public classes _(experimental)_:
      * `public class JDBCUtils `
      * `          logWarning(s"Couldn't find class $driver", e);`
      * `case class JDBCPartitioningInfo(`
      * `class JavaJDBCTrampoline `
      * `  implicit class JDBCContext(sql: SQLContext) `
      * `  implicit class JDBCDataFrame(rdd: DataFrame) `



---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by rxin <gi...@git.apache.org>.
Github user rxin commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23904284
  
    --- Diff: sql/core/src/main/scala/org/apache/spark/sql/jdbc/JDBCRDD.scala ---
    @@ -0,0 +1,417 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more
    + * contributor license agreements.  See the NOTICE file distributed with
    + * this work for additional information regarding copyright ownership.
    + * The ASF licenses this file to You under the Apache License, Version 2.0
    + * (the "License"); you may not use this file except in compliance with
    + * the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.spark.sql.jdbc
    +
    +import java.sql.{Connection, DatabaseMetaData, DriverManager, ResultSet, ResultSetMetaData, SQLException}
    +import scala.collection.mutable.ArrayBuffer
    +
    +import org.apache.spark.{Logging, Partition, SparkContext, TaskContext}
    +import org.apache.spark.rdd.RDD
    +import org.apache.spark.util.NextIterator
    +import org.apache.spark.sql.catalyst.analysis.HiveTypeCoercion
    +import org.apache.spark.sql.catalyst.expressions.{Row, SpecificMutableRow}
    +import org.apache.spark.sql.types._
    +import org.apache.spark.sql.sources._
    +
    +private[sql] object JDBCRDD extends Logging {
    +  /**
    +   * Maps a JDBC type to a Catalyst type.  This function is called only when
    +   * the DriverQuirks class corresponding to your database driver returns null.
    +   *
    +   * @param sqlType - A field of java.sql.Types
    +   * @return The Catalyst type corresponding to sqlType.
    +   */
    +  private def getCatalystType(sqlType: Int): DataType = {
    +    val answer = sqlType match {
    +      case java.sql.Types.ARRAY         => null
    +      case java.sql.Types.BIGINT        => LongType
    +      case java.sql.Types.BINARY        => BinaryType
    +      case java.sql.Types.BIT           => BooleanType // Per JDBC; Quirks handles quirky drivers.
    +      case java.sql.Types.BLOB          => BinaryType
    +      case java.sql.Types.BOOLEAN       => BooleanType
    +      case java.sql.Types.CHAR          => StringType
    +      case java.sql.Types.CLOB          => StringType
    +      case java.sql.Types.DATALINK      => null
    +      case java.sql.Types.DATE          => DateType
    +      case java.sql.Types.DECIMAL       => DecimalType.Unlimited
    +      case java.sql.Types.DISTINCT      => null
    +      case java.sql.Types.DOUBLE        => DoubleType
    +      case java.sql.Types.FLOAT         => FloatType
    +      case java.sql.Types.INTEGER       => IntegerType
    +      case java.sql.Types.JAVA_OBJECT   => null
    +      case java.sql.Types.LONGNVARCHAR  => StringType
    +      case java.sql.Types.LONGVARBINARY => BinaryType
    +      case java.sql.Types.LONGVARCHAR   => StringType
    +      case java.sql.Types.NCHAR         => StringType
    +      case java.sql.Types.NCLOB         => StringType
    +      case java.sql.Types.NULL          => null
    +      case java.sql.Types.NUMERIC       => DecimalType.Unlimited
    +      case java.sql.Types.OTHER         => null
    +      case java.sql.Types.REAL          => DoubleType
    +      case java.sql.Types.REF           => StringType
    +      case java.sql.Types.ROWID         => LongType
    +      case java.sql.Types.SMALLINT      => IntegerType
    +      case java.sql.Types.SQLXML        => StringType
    +      case java.sql.Types.STRUCT        => StringType
    +      case java.sql.Types.TIME          => TimestampType
    +      case java.sql.Types.TIMESTAMP     => TimestampType
    +      case java.sql.Types.TINYINT       => IntegerType
    +      case java.sql.Types.VARBINARY     => BinaryType
    +      case java.sql.Types.VARCHAR       => StringType
    +      case _ => null
    +    }
    +
    +    if (answer == null) throw new SQLException("Unsupported type " + sqlType)
    +    answer
    +  }
    +
    +  /**
    +   * Takes a (schema, table) specification and returns the table's Catalyst
    +   * schema.
    +   *
    +   * @param url - The JDBC url to fetch information from.
    +   * @param table - The table name of the desired table.  This may also be a
    +   *   SQL query wrapped in parentheses.
    +   *
    +   * @return A StructType giving the table's Catalyst schema.
    +   * @throws SQLException if the table specification is garbage.
    +   * @throws SQLException if the table contains an unsupported type.
    +   */
    +  def resolveTable(url: String, table: String): StructType = {
    +    val quirks = DriverQuirks.get(url)
    +    val conn: Connection = DriverManager.getConnection(url)
    +    try {
    +      val rs = conn.prepareStatement(s"SELECT * FROM $table WHERE 1=0").executeQuery()
    +      try {
    +        val rsmd = rs.getMetaData
    +        val ncols = rsmd.getColumnCount
    +        var fields = new Array[StructField](ncols);
    +        var i = 0
    +        while (i < ncols) {
    +          val columnName = rsmd.getColumnName(i + 1)
    +          val dataType = rsmd.getColumnType(i + 1)
    +          val typeName = rsmd.getColumnTypeName(i + 1)
    +          val fieldSize = rsmd.getPrecision(i + 1)
    +          val nullable = rsmd.isNullable(i + 1) != ResultSetMetaData.columnNoNulls
    +          val metadata = new MetadataBuilder().putString("name", columnName)
    +          var columnType = quirks.getCatalystType(dataType, typeName, fieldSize, metadata)
    +          if (columnType == null) columnType = getCatalystType(dataType)
    +          fields(i) = StructField(columnName, columnType, nullable, metadata.build())
    +          i = i + 1
    +        }
    +        return new StructType(fields)
    +      } finally {
    +        rs.close()
    +      }
    +    } finally {
    +      conn.close()
    +    }
    +
    +    throw new RuntimeException("This line is unreachable.")
    +  }
    +
    +  /**
    +   * Prune all but the specified columns from the specified Catalyst schema.
    +   *
    +   * @param schema - The Catalyst schema of the master table
    +   * @param columns - The list of desired columns
    +   *
    +   * @return A Catalyst schema corresponding to columns in the given order.
    +   */
    +  private def pruneSchema(schema: StructType, columns: Array[String]): StructType = {
    +    val fieldMap = Map(schema.fields map { x => x.metadata.getString("name") -> x }: _*)
    +    new StructType(columns map { name => fieldMap(name) })
    +  }
    +
    +  /**
    +   * Given a driver string and an url, return a function that loads the
    +   * specified driver string then returns a connection to the JDBC url.
    +   * getConnector is run on the driver code, while the function it returns
    +   * is run on the executor.
    +   *
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   *
    +   * @return A function that loads the driver and connects to the url.
    +   */
    +  def getConnector(driver: String, url: String): () => Connection = {
    +    () => {
    +      try {
    +        if (driver != null) Class.forName(driver)
    +      } catch {
    +        case e: ClassNotFoundException => {
    +          logWarning(s"Couldn't find class $driver", e);
    +        }
    +      }
    +      DriverManager.getConnection(url)
    +    }
    +  }
    +  /**
    +   * Build and return JDBCRDD from the given information.
    +   *
    +   * @param sc - Your SparkContext.
    +   * @param schema - The Catalyst schema of the underlying database table.
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   * @param fqTable - The fully-qualified table name (or paren'd SQL query) to use.
    +   * @param requiredColumns - The names of the columns to SELECT.
    +   * @param filters - The filters to include in all WHERE clauses.
    +   * @param parts - An array of JDBCPartitions specifying partition ids and
    +   *    per-partition WHERE clauses.
    +   *
    +   * @return An RDD representing "SELECT requiredColumns FROM fqTable".
    +   */
    +  def scanTable(sc: SparkContext,
    +                schema: StructType,
    +                driver: String,
    +                url: String,
    +                fqTable: String,
    +                requiredColumns: Array[String],
    +                filters: Array[Filter],
    +                parts: Array[Partition]): RDD[Row] = {
    +    val prunedSchema = pruneSchema(schema, requiredColumns)
    +
    +    return new JDBCRDD(sc,
    +        getConnector(driver, url),
    +        prunedSchema,
    +        fqTable,
    +        requiredColumns,
    +        filters,
    +        parts)
    +  }
    +}
    +
    +/**
    + * An RDD representing a table in a database accessed via JDBC.  Both the
    + * driver code and the workers must be able to access the database; the driver
    + * needs to fetch the schema while the workers need to fetch the data.
    + */
    +private[sql] class JDBCRDD(
    +    sc: SparkContext,
    +    getConnection: () => Connection,
    +    schema: StructType,
    +    fqTable: String,
    +    columns: Array[String],
    +    filters: Array[Filter],
    +    partitions: Array[Partition])
    +  extends RDD[Row](sc, Nil) {
    +
    +  /**
    +   * Retrieve the list of partitions corresponding to this RDD.
    +   */
    +  override def getPartitions: Array[Partition] = partitions
    +
    +  /**
    +   * `columns`, but as a String suitable for injection into a SQL query.
    +   */
    +  private val columnList: String = {
    +    val sb = new StringBuilder()
    +    columns.foreach(x => sb.append(",").append(x))
    +    if (sb.length == 0) "1" else sb.substring(1)
    +  }
    +
    +  /**
    +   * Turns a single Filter into a String representing a SQL expression.
    +   * Returns null for an unhandled filter.
    +   */
    +  private def compileFilter(f: Filter): String = f match {
    +    case EqualTo(attr, value) => s"$attr = $value"
    +    case LessThan(attr, value) => s"$attr < $value"
    +    case GreaterThan(attr, value) => s"$attr > $value"
    +    case LessThanOrEqual(attr, value) => s"$attr <= $value"
    +    case GreaterThanOrEqual(attr, value) => s"$attr >= $value"
    +    case _ => null
    +  }
    +
    +  /**
    +   * `filters`, but as a WHERE clause suitable for injection into a SQL query.
    +   */
    +  private val filterWhereClause: String = {
    +    val filterStrings = filters map compileFilter filter (_ != null)
    +    if (filterStrings.size > 0) {
    +      val sb = new StringBuilder("WHERE ")
    +      filterStrings.foreach(x => sb.append(x).append(" AND "))
    +      sb.substring(0, sb.length - 5)
    +    } else ""
    +  }
    +
    +  /**
    +   * A WHERE clause representing both `filters`, if any, and the current partition.
    +   */
    +  private def getWhereClause(part: JDBCPartition): String = {
    +    if (part.whereClause != null && filterWhereClause.length > 0) {
    +      filterWhereClause + " AND " + part.whereClause
    +    } else if (part.whereClause != null) {
    +      "WHERE " + part.whereClause
    +    } else {
    +      filterWhereClause
    +    }
    +  }
    +
    +  // Each JDBC-to-Catalyst conversion corresponds to a tag defined here so that
    +  // we don't have to potentially poke around in the Metadata once for every
    +  // row.  
    +  // Is there a better way to do this?  I'd rather be using a type that
    +  // contains only the tags I define.
    +  abstract class JDBCConversion
    --- End diff --
    
    It wouldn't be able to JIT that now either, but it might be more efficient than 20 branches with equality comparisons?


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by tmyklebu <gi...@git.apache.org>.
Github user tmyklebu commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23904561
  
    --- Diff: sql/core/src/main/scala/org/apache/spark/sql/jdbc/JDBCRDD.scala ---
    @@ -0,0 +1,417 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more
    + * contributor license agreements.  See the NOTICE file distributed with
    + * this work for additional information regarding copyright ownership.
    + * The ASF licenses this file to You under the Apache License, Version 2.0
    + * (the "License"); you may not use this file except in compliance with
    + * the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.spark.sql.jdbc
    +
    +import java.sql.{Connection, DatabaseMetaData, DriverManager, ResultSet, ResultSetMetaData, SQLException}
    +import scala.collection.mutable.ArrayBuffer
    +
    +import org.apache.spark.{Logging, Partition, SparkContext, TaskContext}
    +import org.apache.spark.rdd.RDD
    +import org.apache.spark.util.NextIterator
    +import org.apache.spark.sql.catalyst.analysis.HiveTypeCoercion
    +import org.apache.spark.sql.catalyst.expressions.{Row, SpecificMutableRow}
    +import org.apache.spark.sql.types._
    +import org.apache.spark.sql.sources._
    +
    +private[sql] object JDBCRDD extends Logging {
    +  /**
    +   * Maps a JDBC type to a Catalyst type.  This function is called only when
    +   * the DriverQuirks class corresponding to your database driver returns null.
    +   *
    +   * @param sqlType - A field of java.sql.Types
    +   * @return The Catalyst type corresponding to sqlType.
    +   */
    +  private def getCatalystType(sqlType: Int): DataType = {
    +    val answer = sqlType match {
    +      case java.sql.Types.ARRAY         => null
    +      case java.sql.Types.BIGINT        => LongType
    +      case java.sql.Types.BINARY        => BinaryType
    +      case java.sql.Types.BIT           => BooleanType // Per JDBC; Quirks handles quirky drivers.
    +      case java.sql.Types.BLOB          => BinaryType
    +      case java.sql.Types.BOOLEAN       => BooleanType
    +      case java.sql.Types.CHAR          => StringType
    +      case java.sql.Types.CLOB          => StringType
    +      case java.sql.Types.DATALINK      => null
    +      case java.sql.Types.DATE          => DateType
    +      case java.sql.Types.DECIMAL       => DecimalType.Unlimited
    +      case java.sql.Types.DISTINCT      => null
    +      case java.sql.Types.DOUBLE        => DoubleType
    +      case java.sql.Types.FLOAT         => FloatType
    +      case java.sql.Types.INTEGER       => IntegerType
    +      case java.sql.Types.JAVA_OBJECT   => null
    +      case java.sql.Types.LONGNVARCHAR  => StringType
    +      case java.sql.Types.LONGVARBINARY => BinaryType
    +      case java.sql.Types.LONGVARCHAR   => StringType
    +      case java.sql.Types.NCHAR         => StringType
    +      case java.sql.Types.NCLOB         => StringType
    +      case java.sql.Types.NULL          => null
    +      case java.sql.Types.NUMERIC       => DecimalType.Unlimited
    +      case java.sql.Types.OTHER         => null
    +      case java.sql.Types.REAL          => DoubleType
    +      case java.sql.Types.REF           => StringType
    +      case java.sql.Types.ROWID         => LongType
    +      case java.sql.Types.SMALLINT      => IntegerType
    +      case java.sql.Types.SQLXML        => StringType
    +      case java.sql.Types.STRUCT        => StringType
    +      case java.sql.Types.TIME          => TimestampType
    +      case java.sql.Types.TIMESTAMP     => TimestampType
    +      case java.sql.Types.TINYINT       => IntegerType
    +      case java.sql.Types.VARBINARY     => BinaryType
    +      case java.sql.Types.VARCHAR       => StringType
    +      case _ => null
    +    }
    +
    +    if (answer == null) throw new SQLException("Unsupported type " + sqlType)
    +    answer
    +  }
    +
    +  /**
    +   * Takes a (schema, table) specification and returns the table's Catalyst
    +   * schema.
    +   *
    +   * @param url - The JDBC url to fetch information from.
    +   * @param table - The table name of the desired table.  This may also be a
    +   *   SQL query wrapped in parentheses.
    +   *
    +   * @return A StructType giving the table's Catalyst schema.
    +   * @throws SQLException if the table specification is garbage.
    +   * @throws SQLException if the table contains an unsupported type.
    +   */
    +  def resolveTable(url: String, table: String): StructType = {
    +    val quirks = DriverQuirks.get(url)
    +    val conn: Connection = DriverManager.getConnection(url)
    +    try {
    +      val rs = conn.prepareStatement(s"SELECT * FROM $table WHERE 1=0").executeQuery()
    +      try {
    +        val rsmd = rs.getMetaData
    +        val ncols = rsmd.getColumnCount
    +        var fields = new Array[StructField](ncols);
    +        var i = 0
    +        while (i < ncols) {
    +          val columnName = rsmd.getColumnName(i + 1)
    +          val dataType = rsmd.getColumnType(i + 1)
    +          val typeName = rsmd.getColumnTypeName(i + 1)
    +          val fieldSize = rsmd.getPrecision(i + 1)
    +          val nullable = rsmd.isNullable(i + 1) != ResultSetMetaData.columnNoNulls
    +          val metadata = new MetadataBuilder().putString("name", columnName)
    +          var columnType = quirks.getCatalystType(dataType, typeName, fieldSize, metadata)
    +          if (columnType == null) columnType = getCatalystType(dataType)
    +          fields(i) = StructField(columnName, columnType, nullable, metadata.build())
    +          i = i + 1
    +        }
    +        return new StructType(fields)
    +      } finally {
    +        rs.close()
    +      }
    +    } finally {
    +      conn.close()
    +    }
    +
    +    throw new RuntimeException("This line is unreachable.")
    +  }
    +
    +  /**
    +   * Prune all but the specified columns from the specified Catalyst schema.
    +   *
    +   * @param schema - The Catalyst schema of the master table
    +   * @param columns - The list of desired columns
    +   *
    +   * @return A Catalyst schema corresponding to columns in the given order.
    +   */
    +  private def pruneSchema(schema: StructType, columns: Array[String]): StructType = {
    +    val fieldMap = Map(schema.fields map { x => x.metadata.getString("name") -> x }: _*)
    +    new StructType(columns map { name => fieldMap(name) })
    +  }
    +
    +  /**
    +   * Given a driver string and an url, return a function that loads the
    +   * specified driver string then returns a connection to the JDBC url.
    +   * getConnector is run on the driver code, while the function it returns
    +   * is run on the executor.
    +   *
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   *
    +   * @return A function that loads the driver and connects to the url.
    +   */
    +  def getConnector(driver: String, url: String): () => Connection = {
    +    () => {
    +      try {
    +        if (driver != null) Class.forName(driver)
    +      } catch {
    +        case e: ClassNotFoundException => {
    +          logWarning(s"Couldn't find class $driver", e);
    +        }
    +      }
    +      DriverManager.getConnection(url)
    +    }
    +  }
    +  /**
    +   * Build and return JDBCRDD from the given information.
    +   *
    +   * @param sc - Your SparkContext.
    +   * @param schema - The Catalyst schema of the underlying database table.
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   * @param fqTable - The fully-qualified table name (or paren'd SQL query) to use.
    +   * @param requiredColumns - The names of the columns to SELECT.
    +   * @param filters - The filters to include in all WHERE clauses.
    +   * @param parts - An array of JDBCPartitions specifying partition ids and
    +   *    per-partition WHERE clauses.
    +   *
    +   * @return An RDD representing "SELECT requiredColumns FROM fqTable".
    +   */
    +  def scanTable(sc: SparkContext,
    +                schema: StructType,
    +                driver: String,
    +                url: String,
    +                fqTable: String,
    +                requiredColumns: Array[String],
    +                filters: Array[Filter],
    +                parts: Array[Partition]): RDD[Row] = {
    +    val prunedSchema = pruneSchema(schema, requiredColumns)
    +
    +    return new JDBCRDD(sc,
    +        getConnector(driver, url),
    +        prunedSchema,
    +        fqTable,
    +        requiredColumns,
    +        filters,
    +        parts)
    +  }
    +}
    +
    +/**
    + * An RDD representing a table in a database accessed via JDBC.  Both the
    + * driver code and the workers must be able to access the database; the driver
    + * needs to fetch the schema while the workers need to fetch the data.
    + */
    +private[sql] class JDBCRDD(
    +    sc: SparkContext,
    +    getConnection: () => Connection,
    +    schema: StructType,
    +    fqTable: String,
    +    columns: Array[String],
    +    filters: Array[Filter],
    +    partitions: Array[Partition])
    +  extends RDD[Row](sc, Nil) {
    +
    +  /**
    +   * Retrieve the list of partitions corresponding to this RDD.
    +   */
    +  override def getPartitions: Array[Partition] = partitions
    +
    +  /**
    +   * `columns`, but as a String suitable for injection into a SQL query.
    +   */
    +  private val columnList: String = {
    +    val sb = new StringBuilder()
    +    columns.foreach(x => sb.append(",").append(x))
    +    if (sb.length == 0) "1" else sb.substring(1)
    +  }
    +
    +  /**
    +   * Turns a single Filter into a String representing a SQL expression.
    +   * Returns null for an unhandled filter.
    +   */
    +  private def compileFilter(f: Filter): String = f match {
    +    case EqualTo(attr, value) => s"$attr = $value"
    +    case LessThan(attr, value) => s"$attr < $value"
    +    case GreaterThan(attr, value) => s"$attr > $value"
    +    case LessThanOrEqual(attr, value) => s"$attr <= $value"
    +    case GreaterThanOrEqual(attr, value) => s"$attr >= $value"
    +    case _ => null
    +  }
    +
    +  /**
    +   * `filters`, but as a WHERE clause suitable for injection into a SQL query.
    +   */
    +  private val filterWhereClause: String = {
    +    val filterStrings = filters map compileFilter filter (_ != null)
    +    if (filterStrings.size > 0) {
    +      val sb = new StringBuilder("WHERE ")
    +      filterStrings.foreach(x => sb.append(x).append(" AND "))
    +      sb.substring(0, sb.length - 5)
    +    } else ""
    +  }
    +
    +  /**
    +   * A WHERE clause representing both `filters`, if any, and the current partition.
    +   */
    +  private def getWhereClause(part: JDBCPartition): String = {
    +    if (part.whereClause != null && filterWhereClause.length > 0) {
    +      filterWhereClause + " AND " + part.whereClause
    +    } else if (part.whereClause != null) {
    +      "WHERE " + part.whereClause
    +    } else {
    +      filterWhereClause
    +    }
    +  }
    +
    +  // Each JDBC-to-Catalyst conversion corresponds to a tag defined here so that
    +  // we don't have to potentially poke around in the Metadata once for every
    +  // row.  
    +  // Is there a better way to do this?  I'd rather be using a type that
    +  // contains only the tags I define.
    +  abstract class JDBCConversion
    --- End diff --
    
    Hmm.  I switched from integer tags to these Conversion case classes and didn't see a performance drop.  (One could argue that I could have looked harder.)  If that's really what this code compiles to, I may as well make the change you suggest and benchmark it.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by marmbrus <gi...@git.apache.org>.
Github user marmbrus commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-72588221
  
    Thanks! Merged to master.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by rxin <gi...@git.apache.org>.
Github user rxin commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23750587
  
    --- Diff: project/SparkBuild.scala ---
    @@ -397,6 +397,11 @@ object TestSettings {
         testOptions += Tests.Argument(TestFrameworks.JUnit, "-v", "-a"),
         // Enable Junit testing.
         libraryDependencies += "com.novocode" % "junit-interface" % "0.9" % "test",
    +    // Needed for SQL JDBC tests.
    +    libraryDependencies += "com.h2database" % "h2" % "1.4.183" % "test",
    --- End diff --
    
    build dependencies are now defined in pom files i think. sbt just loads them from the pom file.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-72230800
  
      [Test build #26403 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/26403/consoleFull) for   PR 4261 at commit [`cf167ce`](https://github.com/apache/spark/commit/cf167cea9457e933b1b8ed5f0eff708e6535ef99).
     * This patch **passes all tests**.
     * This patch merges cleanly.
     * This patch adds the following public classes _(experimental)_:
      * `public class JDBCUtils `
      * `          logWarning(s"Couldn't find class $driver", e);`
      * `  implicit class JDBCDataFrame(rdd: DataFrame) `



---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by tmyklebu <gi...@git.apache.org>.
Github user tmyklebu commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23901190
  
    --- Diff: sql/core/src/main/scala/org/apache/spark/sql/jdbc/JDBCRDD.scala ---
    @@ -0,0 +1,417 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more
    + * contributor license agreements.  See the NOTICE file distributed with
    + * this work for additional information regarding copyright ownership.
    + * The ASF licenses this file to You under the Apache License, Version 2.0
    + * (the "License"); you may not use this file except in compliance with
    + * the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.spark.sql.jdbc
    +
    +import java.sql.{Connection, DatabaseMetaData, DriverManager, ResultSet, ResultSetMetaData, SQLException}
    +import scala.collection.mutable.ArrayBuffer
    +
    +import org.apache.spark.{Logging, Partition, SparkContext, TaskContext}
    +import org.apache.spark.rdd.RDD
    +import org.apache.spark.util.NextIterator
    +import org.apache.spark.sql.catalyst.analysis.HiveTypeCoercion
    +import org.apache.spark.sql.catalyst.expressions.{Row, SpecificMutableRow}
    +import org.apache.spark.sql.types._
    +import org.apache.spark.sql.sources._
    +
    +private[sql] object JDBCRDD extends Logging {
    +  /**
    +   * Maps a JDBC type to a Catalyst type.  This function is called only when
    +   * the DriverQuirks class corresponding to your database driver returns null.
    +   *
    +   * @param sqlType - A field of java.sql.Types
    +   * @return The Catalyst type corresponding to sqlType.
    +   */
    +  private def getCatalystType(sqlType: Int): DataType = {
    +    val answer = sqlType match {
    +      case java.sql.Types.ARRAY         => null
    +      case java.sql.Types.BIGINT        => LongType
    +      case java.sql.Types.BINARY        => BinaryType
    +      case java.sql.Types.BIT           => BooleanType // Per JDBC; Quirks handles quirky drivers.
    +      case java.sql.Types.BLOB          => BinaryType
    +      case java.sql.Types.BOOLEAN       => BooleanType
    +      case java.sql.Types.CHAR          => StringType
    +      case java.sql.Types.CLOB          => StringType
    +      case java.sql.Types.DATALINK      => null
    +      case java.sql.Types.DATE          => DateType
    +      case java.sql.Types.DECIMAL       => DecimalType.Unlimited
    +      case java.sql.Types.DISTINCT      => null
    +      case java.sql.Types.DOUBLE        => DoubleType
    +      case java.sql.Types.FLOAT         => FloatType
    +      case java.sql.Types.INTEGER       => IntegerType
    +      case java.sql.Types.JAVA_OBJECT   => null
    +      case java.sql.Types.LONGNVARCHAR  => StringType
    +      case java.sql.Types.LONGVARBINARY => BinaryType
    +      case java.sql.Types.LONGVARCHAR   => StringType
    +      case java.sql.Types.NCHAR         => StringType
    +      case java.sql.Types.NCLOB         => StringType
    +      case java.sql.Types.NULL          => null
    +      case java.sql.Types.NUMERIC       => DecimalType.Unlimited
    +      case java.sql.Types.OTHER         => null
    +      case java.sql.Types.REAL          => DoubleType
    +      case java.sql.Types.REF           => StringType
    +      case java.sql.Types.ROWID         => LongType
    +      case java.sql.Types.SMALLINT      => IntegerType
    +      case java.sql.Types.SQLXML        => StringType
    +      case java.sql.Types.STRUCT        => StringType
    +      case java.sql.Types.TIME          => TimestampType
    +      case java.sql.Types.TIMESTAMP     => TimestampType
    +      case java.sql.Types.TINYINT       => IntegerType
    +      case java.sql.Types.VARBINARY     => BinaryType
    +      case java.sql.Types.VARCHAR       => StringType
    +      case _ => null
    +    }
    +
    +    if (answer == null) throw new SQLException("Unsupported type " + sqlType)
    +    answer
    +  }
    +
    +  /**
    +   * Takes a (schema, table) specification and returns the table's Catalyst
    +   * schema.
    +   *
    +   * @param url - The JDBC url to fetch information from.
    +   * @param table - The table name of the desired table.  This may also be a
    +   *   SQL query wrapped in parentheses.
    +   *
    +   * @return A StructType giving the table's Catalyst schema.
    +   * @throws SQLException if the table specification is garbage.
    +   * @throws SQLException if the table contains an unsupported type.
    +   */
    +  def resolveTable(url: String, table: String): StructType = {
    +    val quirks = DriverQuirks.get(url)
    +    val conn: Connection = DriverManager.getConnection(url)
    +    try {
    +      val rs = conn.prepareStatement(s"SELECT * FROM $table WHERE 1=0").executeQuery()
    +      try {
    +        val rsmd = rs.getMetaData
    +        val ncols = rsmd.getColumnCount
    +        var fields = new Array[StructField](ncols);
    +        var i = 0
    +        while (i < ncols) {
    +          val columnName = rsmd.getColumnName(i + 1)
    +          val dataType = rsmd.getColumnType(i + 1)
    +          val typeName = rsmd.getColumnTypeName(i + 1)
    +          val fieldSize = rsmd.getPrecision(i + 1)
    +          val nullable = rsmd.isNullable(i + 1) != ResultSetMetaData.columnNoNulls
    +          val metadata = new MetadataBuilder().putString("name", columnName)
    +          var columnType = quirks.getCatalystType(dataType, typeName, fieldSize, metadata)
    +          if (columnType == null) columnType = getCatalystType(dataType)
    +          fields(i) = StructField(columnName, columnType, nullable, metadata.build())
    +          i = i + 1
    +        }
    +        return new StructType(fields)
    +      } finally {
    +        rs.close()
    +      }
    +    } finally {
    +      conn.close()
    +    }
    +
    +    throw new RuntimeException("This line is unreachable.")
    --- End diff --
    
    @rxin: Yeah.  Is there a "please emit a compiler warning if you can't prove this line is unreachable" in Scala?  Or a more idiomatic way to do this?


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by rxin <gi...@git.apache.org>.
Github user rxin commented on a diff in the pull request:

    https://github.com/apache/spark/pull/4261#discussion_r23903167
  
    --- Diff: sql/core/src/main/scala/org/apache/spark/sql/jdbc/JDBCRDD.scala ---
    @@ -0,0 +1,417 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more
    + * contributor license agreements.  See the NOTICE file distributed with
    + * this work for additional information regarding copyright ownership.
    + * The ASF licenses this file to You under the Apache License, Version 2.0
    + * (the "License"); you may not use this file except in compliance with
    + * the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.spark.sql.jdbc
    +
    +import java.sql.{Connection, DatabaseMetaData, DriverManager, ResultSet, ResultSetMetaData, SQLException}
    +import scala.collection.mutable.ArrayBuffer
    +
    +import org.apache.spark.{Logging, Partition, SparkContext, TaskContext}
    +import org.apache.spark.rdd.RDD
    +import org.apache.spark.util.NextIterator
    +import org.apache.spark.sql.catalyst.analysis.HiveTypeCoercion
    +import org.apache.spark.sql.catalyst.expressions.{Row, SpecificMutableRow}
    +import org.apache.spark.sql.types._
    +import org.apache.spark.sql.sources._
    +
    +private[sql] object JDBCRDD extends Logging {
    +  /**
    +   * Maps a JDBC type to a Catalyst type.  This function is called only when
    +   * the DriverQuirks class corresponding to your database driver returns null.
    +   *
    +   * @param sqlType - A field of java.sql.Types
    +   * @return The Catalyst type corresponding to sqlType.
    +   */
    +  private def getCatalystType(sqlType: Int): DataType = {
    +    val answer = sqlType match {
    +      case java.sql.Types.ARRAY         => null
    +      case java.sql.Types.BIGINT        => LongType
    +      case java.sql.Types.BINARY        => BinaryType
    +      case java.sql.Types.BIT           => BooleanType // Per JDBC; Quirks handles quirky drivers.
    +      case java.sql.Types.BLOB          => BinaryType
    +      case java.sql.Types.BOOLEAN       => BooleanType
    +      case java.sql.Types.CHAR          => StringType
    +      case java.sql.Types.CLOB          => StringType
    +      case java.sql.Types.DATALINK      => null
    +      case java.sql.Types.DATE          => DateType
    +      case java.sql.Types.DECIMAL       => DecimalType.Unlimited
    +      case java.sql.Types.DISTINCT      => null
    +      case java.sql.Types.DOUBLE        => DoubleType
    +      case java.sql.Types.FLOAT         => FloatType
    +      case java.sql.Types.INTEGER       => IntegerType
    +      case java.sql.Types.JAVA_OBJECT   => null
    +      case java.sql.Types.LONGNVARCHAR  => StringType
    +      case java.sql.Types.LONGVARBINARY => BinaryType
    +      case java.sql.Types.LONGVARCHAR   => StringType
    +      case java.sql.Types.NCHAR         => StringType
    +      case java.sql.Types.NCLOB         => StringType
    +      case java.sql.Types.NULL          => null
    +      case java.sql.Types.NUMERIC       => DecimalType.Unlimited
    +      case java.sql.Types.OTHER         => null
    +      case java.sql.Types.REAL          => DoubleType
    +      case java.sql.Types.REF           => StringType
    +      case java.sql.Types.ROWID         => LongType
    +      case java.sql.Types.SMALLINT      => IntegerType
    +      case java.sql.Types.SQLXML        => StringType
    +      case java.sql.Types.STRUCT        => StringType
    +      case java.sql.Types.TIME          => TimestampType
    +      case java.sql.Types.TIMESTAMP     => TimestampType
    +      case java.sql.Types.TINYINT       => IntegerType
    +      case java.sql.Types.VARBINARY     => BinaryType
    +      case java.sql.Types.VARCHAR       => StringType
    +      case _ => null
    +    }
    +
    +    if (answer == null) throw new SQLException("Unsupported type " + sqlType)
    +    answer
    +  }
    +
    +  /**
    +   * Takes a (schema, table) specification and returns the table's Catalyst
    +   * schema.
    +   *
    +   * @param url - The JDBC url to fetch information from.
    +   * @param table - The table name of the desired table.  This may also be a
    +   *   SQL query wrapped in parentheses.
    +   *
    +   * @return A StructType giving the table's Catalyst schema.
    +   * @throws SQLException if the table specification is garbage.
    +   * @throws SQLException if the table contains an unsupported type.
    +   */
    +  def resolveTable(url: String, table: String): StructType = {
    +    val quirks = DriverQuirks.get(url)
    +    val conn: Connection = DriverManager.getConnection(url)
    +    try {
    +      val rs = conn.prepareStatement(s"SELECT * FROM $table WHERE 1=0").executeQuery()
    +      try {
    +        val rsmd = rs.getMetaData
    +        val ncols = rsmd.getColumnCount
    +        var fields = new Array[StructField](ncols);
    +        var i = 0
    +        while (i < ncols) {
    +          val columnName = rsmd.getColumnName(i + 1)
    +          val dataType = rsmd.getColumnType(i + 1)
    +          val typeName = rsmd.getColumnTypeName(i + 1)
    +          val fieldSize = rsmd.getPrecision(i + 1)
    +          val nullable = rsmd.isNullable(i + 1) != ResultSetMetaData.columnNoNulls
    +          val metadata = new MetadataBuilder().putString("name", columnName)
    +          var columnType = quirks.getCatalystType(dataType, typeName, fieldSize, metadata)
    +          if (columnType == null) columnType = getCatalystType(dataType)
    +          fields(i) = StructField(columnName, columnType, nullable, metadata.build())
    +          i = i + 1
    +        }
    +        return new StructType(fields)
    +      } finally {
    +        rs.close()
    +      }
    +    } finally {
    +      conn.close()
    +    }
    +
    +    throw new RuntimeException("This line is unreachable.")
    +  }
    +
    +  /**
    +   * Prune all but the specified columns from the specified Catalyst schema.
    +   *
    +   * @param schema - The Catalyst schema of the master table
    +   * @param columns - The list of desired columns
    +   *
    +   * @return A Catalyst schema corresponding to columns in the given order.
    +   */
    +  private def pruneSchema(schema: StructType, columns: Array[String]): StructType = {
    +    val fieldMap = Map(schema.fields map { x => x.metadata.getString("name") -> x }: _*)
    +    new StructType(columns map { name => fieldMap(name) })
    +  }
    +
    +  /**
    +   * Given a driver string and an url, return a function that loads the
    +   * specified driver string then returns a connection to the JDBC url.
    +   * getConnector is run on the driver code, while the function it returns
    +   * is run on the executor.
    +   *
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   *
    +   * @return A function that loads the driver and connects to the url.
    +   */
    +  def getConnector(driver: String, url: String): () => Connection = {
    +    () => {
    +      try {
    +        if (driver != null) Class.forName(driver)
    +      } catch {
    +        case e: ClassNotFoundException => {
    +          logWarning(s"Couldn't find class $driver", e);
    +        }
    +      }
    +      DriverManager.getConnection(url)
    +    }
    +  }
    +  /**
    +   * Build and return JDBCRDD from the given information.
    +   *
    +   * @param sc - Your SparkContext.
    +   * @param schema - The Catalyst schema of the underlying database table.
    +   * @param driver - The class name of the JDBC driver for the given url.
    +   * @param url - The JDBC url to connect to.
    +   * @param fqTable - The fully-qualified table name (or paren'd SQL query) to use.
    +   * @param requiredColumns - The names of the columns to SELECT.
    +   * @param filters - The filters to include in all WHERE clauses.
    +   * @param parts - An array of JDBCPartitions specifying partition ids and
    +   *    per-partition WHERE clauses.
    +   *
    +   * @return An RDD representing "SELECT requiredColumns FROM fqTable".
    +   */
    +  def scanTable(sc: SparkContext,
    +                schema: StructType,
    +                driver: String,
    +                url: String,
    +                fqTable: String,
    +                requiredColumns: Array[String],
    +                filters: Array[Filter],
    +                parts: Array[Partition]): RDD[Row] = {
    +    val prunedSchema = pruneSchema(schema, requiredColumns)
    +
    +    return new JDBCRDD(sc,
    +        getConnector(driver, url),
    --- End diff --
    
    and 2 space indent when calling them


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by tmyklebu <gi...@git.apache.org>.
Github user tmyklebu commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-72037926
  
    I don't think these test failures are my fault, unless I need to handle SparkContext lifetimes differently .  One thing that I see in the test failure log is this:
    
        [info] Test org.apache.spark.sql.api.java.JavaAPISuite.udf1Test started
        20:59:18.609 WARN org.apache.spark.SparkContext: Multiple running SparkContexts detected in the same JVM!
        org.apache.spark.SparkException: Only one SparkContext may be running in this JVM (see SPARK-2243). To ignore this error, set spark.driver.allowMultipleContexts = true. The currently running SparkContext was created at:
        org.apache.spark.SparkContext.<init>(SparkContext.scala:124)
        org.apache.spark.sql.test.TestSQLContext$.<init>(TestSQLContext.scala:29)
        org.apache.spark.sql.test.TestSQLContext$.<clinit>(TestSQLContext.scala)
        [...]
            at org.apache.spark.SparkContext.<init>(SparkContext.scala:159)
            at org.apache.spark.api.java.JavaSparkContext.<init>(JavaSparkContext.scala:67)
            at org.apache.spark.sql.api.java.JavaAPISuite.setUp(JavaAPISuite.java:40)
    
    `JavaAPISuite` spins up a new SparkContext:
    
          @Before
          public void setUp() {
            sc = new JavaSparkContext("local", "JavaAPISuite");
            sqlContext = new SQLContext(sc);
          }
    
    and destroys it when it's done:
    
          @After
          public void tearDown() {
            sc.stop();
            sc = null;
          }
    
    Should it?  There's already a SQLContext out there; it's called `TestSQLContext$.MODULE$`.
    
    I can't reproduce the test failures on my side.  The test failures (all three in JavaJDBCSuite) look like this:
    
        [info] Test org.apache.spark.sql.jdbc.JavaJDBCTest.basicTest started
        [error] Test org.apache.spark.sql.jdbc.JavaJDBCTest.basicTest failed: Task not serializable
        [error]     at org.apache.spark.util.ClosureCleaner$.ensureSerializable(ClosureCleaner.scala:166)
        [error]     at org.apache.spark.util.ClosureCleaner$.clean(ClosureCleaner.scala:158)
        [error]     at org.apache.spark.SparkContext.clean(SparkContext.scala:1488)
        [error]     at org.apache.spark.rdd.RDD.map(RDD.scala:290)
        [error]     at org.apache.spark.sql.DataFrame.rdd(DataFrame.scala:527)
        [error]     at org.apache.spark.sql.DataFrame.collect(DataFrame.scala:484)
        [error]     at org.apache.spark.sql.jdbc.JavaJDBCTest.basicTest(JavaJDBCTest.java:62)
        [error]     ...
        [error] Caused by: java.lang.NullPointerException
        [error]     at org.apache.spark.util.ClosureCleaner$.ensureSerializable(ClosureCleaner.scala:164)
        [error]     ... 44 more
    
    Line 164 is
    
        SparkEnv.get.closureSerializer.newInstance().serialize(func)
    
    I think `SparkEnv.get` is returning null.  When you spin up a SparkContext, it creates a `SparkEnv` and does `SparkEnv.set(env)`.  When you `stop()` it, it does `SparkEnv.set(null)`.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-72052339
  
    Test FAILed.
    Refer to this link for build results (access rights to CI server needed): 
    https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/26320/
    Test FAILed.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-72050061
  
      [Test build #26320 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/26320/consoleFull) for   PR 4261 at commit [`585f95b`](https://github.com/apache/spark/commit/585f95b2d2a7282e20d922bce57118cd490f7226).
     * This patch merges cleanly.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC data source for Spark...

Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-71971280
  
    Test FAILed.
    Refer to this link for build results (access rights to CI server needed): 
    https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/26288/
    Test FAILed.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] spark pull request: [SPARK-5472][SQL] A JDBC driver for Spark SQL.

Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:

    https://github.com/apache/spark/pull/4261#issuecomment-71966019
  
      [Test build #26288 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/26288/consoleFull) for   PR 4261 at commit [`41647ef`](https://github.com/apache/spark/commit/41647ef4aab44cc0cf698f003143d3d5b30fab10).
     * This patch merges cleanly.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org