[jira] [Commented] (SPARK-29586) spark jdbc method param lowerBound and upperBound DataType wrong

2019-10-24 Thread Dongjoon Hyun (Jira) 


[ 
https://issues.apache.org/jira/browse/SPARK-29586?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16959243#comment-16959243
 ] 

Dongjoon Hyun commented on SPARK-29586:
---

Thank you for filing a JIRA, [~726575...@qq.com]. For new features, we need to 
use `3.0.0` as `Affected Versions` because Apache Spark doesn't allow new 
feature backporting.

> spark jdbc method param lowerBound and upperBound DataType wrong
> 
>
> Key: SPARK-29586
> URL: https://issues.apache.org/jira/browse/SPARK-29586
> Project: Spark
>  Issue Type: Improvement
>  Components: SQL
>Affects Versions: 3.0.0
>Reporter: daile
>Priority: Major
>
>  
> {code:java}
> private def toBoundValueInWhereClause(
> value: Long,
> columnType: DataType,
> timeZoneId: String): String = {
>   def dateTimeToString(): String = {
> val dateTimeStr = columnType match {
>   case DateType => DateFormatter().format(value.toInt)
>   case TimestampType =>
> val timestampFormatter = TimestampFormatter.getFractionFormatter(
>   DateTimeUtils.getZoneId(timeZoneId))
> DateTimeUtils.timestampToString(timestampFormatter, value)
> }
> s"'$dateTimeStr'"
>   }
>   columnType match {
> case _: NumericType => value.toString
> case DateType | TimestampType => dateTimeToString()
>   }
> }{code}
> partitionColumn supoort NumericType, TimestampType, TimestampType but jdbc 
> method only accept Long
>  
> {code:java}
> test("jdbc Suite2") {
>   val df = spark
> .read
> .option("partitionColumn", "B")
> .option("lowerBound", "2017-01-01 10:00:00")
> .option("upperBound", "2019-01-01 10:00:00")
> .option("numPartitions", 5)
> .jdbc(urlWithUserAndPass, "TEST.TIMETYPES",  new Properties())
>   df.printSchema()
>   df.show()
> }
> {code}
> it's OK
>  
> {code:java}
> test("jdbc Suite") { val df = spark.read.jdbc(urlWithUserAndPass, 
> "TEST.TIMETYPES", "B", 1571899768024L, 1571899768024L, 5, new Properties()) 
> df.printSchema() df.show() }
> {code}
>  
> {code:java}
> java.lang.IllegalArgumentException: Cannot parse the bound value 
> 1571899768024 as datejava.lang.IllegalArgumentException: Cannot parse the 
> bound value 1571899768024 as date at 
> org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.$anonfun$toInternalBoundValue$1(JDBCRelation.scala:184)
>  at scala.Option.getOrElse(Option.scala:189) at 
> org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.parse$1(JDBCRelation.scala:183)
>  at 
> org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.toInternalBoundValue(JDBCRelation.scala:189)
>  at 
> org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.columnPartition(JDBCRelation.scala:88)
>  at 
> org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:36)
>  at 
> org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:339)
>  at 
> org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:240) 
> at 
> org.apache.spark.sql.DataFrameReader.$anonfun$load$2(DataFrameReader.scala:229)
>  at scala.Option.getOrElse(Option.scala:189) at 
> org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:229) at 
> org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:179) at 
> org.apache.spark.sql.DataFrameReader.jdbc(DataFrameReader.scala:255) at 
> org.apache.spark.sql.DataFrameReader.jdbc(DataFrameReader.scala:297) at 
> org.apache.spark.sql.jdbc.JDBCSuite.$anonfun$new$186(JDBCSuite.scala:1664) at 
> scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at 
> org.scalatest.OutcomeOf.outcomeOf(OutcomeOf.scala:85) at 
> org.scalatest.OutcomeOf.outcomeOf$(OutcomeOf.scala:83) at 
> org.scalatest.OutcomeOf$.outcomeOf(OutcomeOf.scala:104) at 
> org.scalatest.Transformer.apply(Transformer.scala:22) at 
> org.scalatest.Transformer.apply(Transformer.scala:20) at 
> org.scalatest.FunSuiteLike$$anon$1.apply(FunSuiteLike.scala:186) at 
> org.apache.spark.SparkFunSuite.withFixture(SparkFunSuite.scala:149) at 
> org.scalatest.FunSuiteLike.invokeWithFixture$1(FunSuiteLike.scala:184) at 
> org.scalatest.FunSuiteLike.$anonfun$runTest$1(FunSuiteLike.scala:196) at 
> org.scalatest.SuperEngine.runTestImpl(Engine.scala:289) at 
> org.scalatest.FunSuiteLike.runTest(FunSuiteLike.scala:196) at 
> org.scalatest.FunSuiteLike.runTest$(FunSuiteLike.scala:178) at 
> org.apache.spark.SparkFunSuite.org$scalatest$BeforeAndAfterEach$$super$runTest(SparkFunSuite.scala:56)
>  at org.scalatest.BeforeAndAfterEach.runTest(BeforeAndAfterEach.scala:221) at 
> org.scalatest.BeforeAndAfterEach.runTest$(BeforeAndAfterEach.scala:214) at 
> org.apache.spark.sql.jdbc.JDBCSuite.org$scalatest$BeforeAndAfter$$super$runTest(JDBCSuite.scala:43)
>  at

[jira] [Commented] (SPARK-29586) spark jdbc method param lowerBound and upperBound DataType wrong

2019-10-24 Thread Hu Fuwang (Jira) 


[ 
https://issues.apache.org/jira/browse/SPARK-29586?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16958610#comment-16958610
 ] 

Hu Fuwang commented on SPARK-29586:
---

I am working on this.

> spark jdbc method param lowerBound and upperBound DataType wrong
> 
>
> Key: SPARK-29586
> URL: https://issues.apache.org/jira/browse/SPARK-29586
> Project: Spark
>  Issue Type: Improvement
>  Components: SQL
>Affects Versions: 2.4.4, 3.0.0
>Reporter: daile
>Priority: Major
>
>  
> {code:java}
> private def toBoundValueInWhereClause(
> value: Long,
> columnType: DataType,
> timeZoneId: String): String = {
>   def dateTimeToString(): String = {
> val dateTimeStr = columnType match {
>   case DateType => DateFormatter().format(value.toInt)
>   case TimestampType =>
> val timestampFormatter = TimestampFormatter.getFractionFormatter(
>   DateTimeUtils.getZoneId(timeZoneId))
> DateTimeUtils.timestampToString(timestampFormatter, value)
> }
> s"'$dateTimeStr'"
>   }
>   columnType match {
> case _: NumericType => value.toString
> case DateType | TimestampType => dateTimeToString()
>   }
> }{code}
> partitionColumn supoort NumericType, TimestampType, TimestampType but jdbc 
> method only accept Long
> test("jdbc Suite2") {
>   val df = spark
>     .read
>     .option("partitionColumn", "B")
>     .option("lowerBound", "2017-01-01 10:00:00")
>     .option("upperBound", "2019-01-01 10:00:00")
>     .option("numPartitions", 5)
>     .jdbc(urlWithUserAndPass, "TEST.TIMETYPES",  new Properties())
>   df.printSchema()
>   df.show()
> }
> test("jdbc Suite2") {
>   val df = spark
>     .read
>     .option("partitionColumn", "B")
>     .option("lowerBound", "2017-01-01 10:00:00")
>     .option("upperBound", "2019-01-01 10:00:00")
>     .option("numPartitions", 5)
>     .jdbc(urlWithUserAndPass, "TEST.TIMETYPES",  new Properties())
>   df.printSchema()
>   df.show()
> }
> test("jdbc Suite") {
>   val df = spark.read.jdbc(urlWithUserAndPass, "TEST.TIMETYPES", "B", 
> 1571899768024L, 1571899768024L, 5, new Properties())
>   df.printSchema()
>   df.show()
> }
> java.lang.IllegalArgumentException: Cannot parse the bound value 
> 1571899768024 as date
>   at 
> org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.$anonfun$toInternalBoundValue$1(JDBCRelation.scala:184)
>   at scala.Option.getOrElse(Option.scala:189)
>   at 
> org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.parse$1(JDBCRelation.scala:183)
>   at 
> org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.toInternalBoundValue(JDBCRelation.scala:189)
>   at 
> org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.columnPartition(JDBCRelation.scala:88)
>   at 
> org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:36)
>   at 
> org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:339)
>   at 
> org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:240)
>   at 
> org.apache.spark.sql.DataFrameReader.$anonfun$load$2(DataFrameReader.scala:229)
>   at scala.Option.getOrElse(Option.scala:189)
>   at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:229)
>   at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:179)
>   at org.apache.spark.sql.DataFrameReader.jdbc(DataFrameReader.scala:255)
>   at org.apache.spark.sql.DataFrameReader.jdbc(DataFrameReader.scala:297)
>   at 
> org.apache.spark.sql.jdbc.JDBCSuite.$anonfun$new$186(JDBCSuite.scala:1664)
>   at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)
>   at org.scalatest.OutcomeOf.outcomeOf(OutcomeOf.scala:85)
>   at org.scalatest.OutcomeOf.outcomeOf$(OutcomeOf.scala:83)
>   at org.scalatest.OutcomeOf$.outcomeOf(OutcomeOf.scala:104)
>   at org.scalatest.Transformer.apply(Transformer.scala:22)
>   at org.scalatest.Transformer.apply(Transformer.scala:20)
>   at org.scalatest.FunSuiteLike$$anon$1.apply(FunSuiteLike.scala:186)
>   at org.apache.spark.SparkFunSuite.withFixture(SparkFunSuite.scala:149)
>   at org.scalatest.FunSuiteLike.invokeWithFixture$1(FunSuiteLike.scala:184)
>   at org.scalatest.FunSuiteLike.$anonfun$runTest$1(FunSuiteLike.scala:196)
>   at org.scalatest.SuperEngine.runTestImpl(Engine.scala:289)
>   at org.scalatest.FunSuiteLike.runTest(FunSuiteLike.scala:196)
>   at org.scalatest.FunSuiteLike.runTest$(FunSuiteLike.scala:178)
>   at 
> org.apache.spark.SparkFunSuite.org$scalatest$BeforeAndAfterEach$$super$runTest(SparkFunSuite.scala:56)
>   at org.scalatest.BeforeAndAfterEach.runTest(BeforeAndAfterEach.scala:221)
>   at org.scalatest.BeforeAndAfterEach.runTest$(BeforeAndAfterEach.scala:214)
>   at 
>