This is an automated email from the ASF dual-hosted git repository.
yao pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new f41cd1706941 [SPARK-47515][SQL] Save TimestampNTZType as DATETIME in
MySQL
f41cd1706941 is described below
commit f41cd17069419efa9616f1220e30996c82ff40dc
Author: Kent Yao <y...@apache.org>
AuthorDate: Fri Mar 22 17:01:49 2024 +0800
[SPARK-47515][SQL] Save TimestampNTZType as DATETIME in MySQL
### What changes were proposed in this pull request?
DATETIME represents TIMESTAMP WITHOUT TIME ZONE, we need to map
TimestampNTZType to DATETIME in the write path. Otherwise, we cannot read it
back as TimestampNTZType in a roundtrip.
### Why are the changes needed?
Fix type mapping
### Does this PR introduce _any_ user-facing change?
yes, a column of TimestampNTZType generates `x DATETIME` at MYSQL DB
### How was this patch tested?
new tests
### Was this patch authored or co-authored using generative AI tooling?
no
Closes #45655 from yaooqinn/SPARK-47515.
Authored-by: Kent Yao <y...@apache.org>
Signed-off-by: Kent Yao <y...@apache.org>
---
.../org/apache/spark/sql/jdbc/MySQLIntegrationSuite.scala | 12 ++++++++++++
.../main/scala/org/apache/spark/sql/jdbc/MySQLDialect.scala | 5 +++++
2 files changed, 17 insertions(+)
diff --git
a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/MySQLIntegrationSuite.scala
b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/MySQLIntegrationSuite.scala
index 5b2214f2efd6..772c6112083d 100644
---
a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/MySQLIntegrationSuite.scala
+++
b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/MySQLIntegrationSuite.scala
@@ -249,4 +249,16 @@ class MySQLIntegrationSuite extends
DockerJDBCIntegrationSuite {
.add("b2", BooleanType, nullable = true, mb.putBoolean("isSigned",
false).build())
.add("b3", BooleanType, nullable = true, mb.putBoolean("isSigned",
true).build()))
}
+
+ test("SPARK-47515: Save TimestampNTZType as DATETIME in MySQL") {
+ val expected = sql("select timestamp_ntz'2018-11-17 13:33:33' as col0")
+ expected.write.format("jdbc")
+ .option("url", jdbcUrl)
+ .option("dbtable", "TBL_DATETIME_NTZ")
+ .save()
+
+ val answer = spark.read
+ .option("preferTimestampNTZ", true).jdbc(jdbcUrl, "TBL_DATETIME_NTZ",
new Properties)
+ checkAnswer(answer, expected)
+ }
}
diff --git
a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/MySQLDialect.scala
b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/MySQLDialect.scala
index 292e3ca2d5e4..94d498f6189a 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/MySQLDialect.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/MySQLDialect.scala
@@ -210,6 +210,11 @@ private case class MySQLDialect() extends JdbcDialect with
SQLConfHelper {
case FloatType => Option(JdbcType("FLOAT", java.sql.Types.FLOAT))
case StringType => Option(JdbcType("LONGTEXT", java.sql.Types.LONGVARCHAR))
case ByteType => Option(JdbcType("TINYINT", java.sql.Types.TINYINT))
+ // scalastyle:off line.size.limit
+ // In MYSQL, DATETIME is TIMESTAMP WITHOUT TIME ZONE
+ //
https://github.com/mysql/mysql-connector-j/blob/8.3.0/src/main/core-api/java/com/mysql/cj/MysqlType.java#L251
+ // scalastyle:on line.size.limit
+ case TimestampNTZType => Option(JdbcType("DATETIME",
java.sql.Types.TIMESTAMP))
case _ => JdbcUtils.getCommonJDBCType(dt)
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org
For additional commands, e-mail: commits-h...@spark.apache.org
