You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by we...@apache.org on 2025/04/17 13:52:16 UTC

(spark) branch master updated: [SPARK-51803][CORE] Store external engine JDBC type in the spark schema in the…

This is an automated email from the ASF dual-hosted git repository.

wenchen 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 45082b8ccd07 [SPARK-51803][CORE] Store external engine JDBC type in the spark schema in the…
45082b8ccd07 is described below

commit 45082b8ccd0799b3da3a7fba122e03eec449ec79
Author: alekjarmov <al...@gmail.com>
AuthorDate: Thu Apr 17 21:51:56 2025 +0800

    [SPARK-51803][CORE] Store external engine JDBC type in the spark schema in the…
    
    … metadata of StructField
    
    ### What changes were proposed in this pull request?
    
    Store in the metadata of each StructField in JDBC connections the external engine data type.
    
    ### Why are the changes needed?
    
    it can be useful when looking at analyzed plan to know how the external engine data types are mapped to Spark types.
    
    ### Does this PR introduce _any_ user-facing change?
    
    No
    
    ### How was this patch tested?
    
    By extending existing unit tests to verify the new funcitonality.
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Closes #50591 from alekjarmov/jdbc-datatypes.
    
    Lead-authored-by: alekjarmov <al...@gmail.com>
    Co-authored-by: Alek Jarmov <al...@Aleks-MacBook-Pro.local>
    Signed-off-by: Wenchen Fan <we...@databricks.com>
---
 .../spark/sql/jdbc/v2/DB2IntegrationSuite.scala    |  11 ++-
 .../sql/jdbc/v2/MsSqlServerIntegrationSuite.scala  |  19 +++-
 .../spark/sql/jdbc/v2/MySQLIntegrationSuite.scala  |  36 +++++--
 .../spark/sql/jdbc/v2/OracleIntegrationSuite.scala |  36 +++++--
 .../sql/jdbc/v2/PostgresIntegrationSuite.scala     |  20 +++-
 .../org/apache/spark/sql/jdbc/v2/V2JDBCTest.scala  |  67 +++++++++++--
 .../sql/execution/datasources/jdbc/JdbcUtils.scala |   1 +
 .../v2/jdbc/JDBCTableCatalogSuite.scala            | 106 +++++++++++++++------
 .../org/apache/spark/sql/jdbc/JDBCSuite.scala      |  59 ++++++++++--
 9 files changed, 290 insertions(+), 65 deletions(-)

diff --git a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/DB2IntegrationSuite.scala b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/DB2IntegrationSuite.scala
index 91a82075a360..d12cfe7785ce 100644
--- a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/DB2IntegrationSuite.scala
+++ b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/DB2IntegrationSuite.scala
@@ -51,6 +51,11 @@ class DB2IntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTest {
   override val namespaceOpt: Option[String] = Some("DB2INST1")
   override val db = new DB2DatabaseOnDocker
 
+  object JdbcClientTypes {
+    val INTEGER = "INTEGER"
+    val DOUBLE = "DOUBLE"
+  }
+
   override def sparkConf: SparkConf = super.sparkConf
     .set("spark.sql.catalog.db2", classOf[JDBCTableCatalog].getName)
     .set("spark.sql.catalog.db2.url", db.getJdbcUrl(dockerIp, externalPort))
@@ -74,12 +79,12 @@ class DB2IntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTest {
     sql(s"CREATE TABLE $tbl (ID INTEGER)")
     var t = spark.table(tbl)
     var expectedSchema = new StructType()
-      .add("ID", IntegerType, true, defaultMetadata(IntegerType))
+      .add("ID", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
     assert(t.schema === expectedSchema)
     sql(s"ALTER TABLE $tbl ALTER COLUMN id TYPE DOUBLE")
     t = spark.table(tbl)
     expectedSchema = new StructType()
-      .add("ID", DoubleType, true, defaultMetadata(DoubleType))
+      .add("ID", DoubleType, true, defaultMetadata(DoubleType, JdbcClientTypes.DOUBLE))
     assert(t.schema === expectedSchema)
     // Update column type from DOUBLE to STRING
     val sql1 = s"ALTER TABLE $tbl ALTER COLUMN id TYPE VARCHAR(10)"
@@ -103,7 +108,7 @@ class DB2IntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTest {
       s" TBLPROPERTIES('CCSID'='UNICODE')")
     val t = spark.table(tbl)
     val expectedSchema = new StructType()
-      .add("ID", IntegerType, true, defaultMetadata(IntegerType))
+      .add("ID", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
     assert(t.schema === expectedSchema)
   }
 
diff --git a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MsSqlServerIntegrationSuite.scala b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MsSqlServerIntegrationSuite.scala
index 10f0413abfaf..0ff5b9aa5656 100644
--- a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MsSqlServerIntegrationSuite.scala
+++ b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MsSqlServerIntegrationSuite.scala
@@ -41,6 +41,11 @@ import org.apache.spark.tags.DockerTest
 @DockerTest
 class MsSqlServerIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTest {
 
+  object JdbcClientTypes {
+    val INTEGER = "int"
+    val STRING = "nvarchar"
+  }
+
   def getExternalEngineQuery(executedPlan: SparkPlan): String = {
     getExternalEngineRdd(executedPlan).asInstanceOf[JDBCRDD].getExternalEngineQuery
   }
@@ -93,18 +98,28 @@ class MsSqlServerIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JD
     ).executeUpdate()
   }
 
+  override def testRenameColumn(tbl: String): Unit = {
+    sql(s"ALTER TABLE $tbl RENAME COLUMN ID TO RENAMED")
+    val t = spark.table(s"$tbl")
+    val expectedSchema = new StructType()
+      .add("RENAMED", StringType, true, defaultMetadata(StringType, JdbcClientTypes.STRING))
+      .add("ID1", StringType, true, defaultMetadata(StringType, JdbcClientTypes.STRING))
+      .add("ID2", StringType, true, defaultMetadata(StringType, JdbcClientTypes.STRING))
+    assert(t.schema === expectedSchema)
+  }
+
   override def notSupportsTableComment: Boolean = true
 
   override def testUpdateColumnType(tbl: String): Unit = {
     sql(s"CREATE TABLE $tbl (ID INTEGER)")
     var t = spark.table(tbl)
     var expectedSchema = new StructType()
-      .add("ID", IntegerType, true, defaultMetadata(IntegerType))
+      .add("ID", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
     assert(t.schema === expectedSchema)
     sql(s"ALTER TABLE $tbl ALTER COLUMN id TYPE STRING")
     t = spark.table(tbl)
     expectedSchema = new StructType()
-      .add("ID", StringType, true, defaultMetadata())
+      .add("ID", StringType, true, defaultMetadata(StringType, JdbcClientTypes.STRING))
     assert(t.schema === expectedSchema)
     // Update column type from STRING to INTEGER
     val sql1 = s"ALTER TABLE $tbl ALTER COLUMN id TYPE INTEGER"
diff --git a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MySQLIntegrationSuite.scala b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MySQLIntegrationSuite.scala
index 3a8ded7cec26..53e38e451cda 100644
--- a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MySQLIntegrationSuite.scala
+++ b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MySQLIntegrationSuite.scala
@@ -59,6 +59,21 @@ class MySQLIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTest
   override val catalogName: String = "mysql"
   override val db = new MySQLDatabaseOnDocker
 
+  case class JdbcClientTypes(INTEGER: String, STRING: String)
+
+  val jdbcClientTypes: JdbcClientTypes =
+    JdbcClientTypes(INTEGER = "INT", STRING = "LONGTEXT")
+
+  override def defaultMetadata(
+      dataType: DataType = StringType,
+      jdbcClientType: String = jdbcClientTypes.STRING): Metadata =
+    new MetadataBuilder()
+      .putLong("scale", 0)
+      .putBoolean("isTimestampNTZ", false)
+      .putBoolean("isSigned", dataType.isInstanceOf[NumericType])
+      .putString("jdbcClientType", jdbcClientType)
+      .build()
+
   override def sparkConf: SparkConf = super.sparkConf
     .set("spark.sql.catalog.mysql", classOf[JDBCTableCatalog].getName)
     .set("spark.sql.catalog.mysql.url", db.getJdbcUrl(dockerIp, externalPort))
@@ -99,7 +114,7 @@ class MySQLIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTest
     sql(s"CREATE TABLE $tbl (ID INTEGER)")
     var t = spark.table(tbl)
     var expectedSchema = new StructType()
-      .add("ID", IntegerType, true, defaultMetadata(IntegerType))
+      .add("ID", IntegerType, true, defaultMetadata(IntegerType, jdbcClientTypes.INTEGER))
     assert(t.schema === expectedSchema)
     sql(s"ALTER TABLE $tbl ALTER COLUMN id TYPE STRING")
     t = spark.table(tbl)
@@ -153,7 +168,7 @@ class MySQLIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTest
       s" TBLPROPERTIES('ENGINE'='InnoDB', 'DEFAULT CHARACTER SET'='utf8')")
     val t = spark.table(tbl)
     val expectedSchema = new StructType()
-      .add("ID", IntegerType, true, defaultMetadata(IntegerType))
+      .add("ID", IntegerType, true, defaultMetadata(IntegerType, jdbcClientTypes.INTEGER))
     assert(t.schema === expectedSchema)
   }
 
@@ -282,11 +297,18 @@ class MySQLIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTest
  */
 @DockerTest
 class MySQLOverMariaConnectorIntegrationSuite extends MySQLIntegrationSuite {
-  override def defaultMetadata(dataType: DataType = StringType): Metadata = new MetadataBuilder()
-    .putLong("scale", 0)
-    .putBoolean("isTimestampNTZ", false)
-    .putBoolean("isSigned", true)
-    .build()
+  override val jdbcClientTypes: JdbcClientTypes =
+    JdbcClientTypes(INTEGER = "INTEGER", STRING = "LONGTEXT")
+
+  override def defaultMetadata(
+      dataType: DataType = StringType,
+      jdbcClientType: String = jdbcClientTypes.STRING): Metadata =
+    new MetadataBuilder()
+      .putLong("scale", 0)
+      .putBoolean("isTimestampNTZ", false)
+      .putBoolean("isSigned", true)
+      .putString("jdbcClientType", jdbcClientType)
+      .build()
 
   override val db = new MySQLDatabaseOnDocker {
     override def getJdbcUrl(ip: String, port: Int): String =
diff --git a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/OracleIntegrationSuite.scala b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/OracleIntegrationSuite.scala
index 2c97a588670a..6499db2cc03b 100644
--- a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/OracleIntegrationSuite.scala
+++ b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/OracleIntegrationSuite.scala
@@ -75,12 +75,24 @@ class OracleIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTes
   override val namespaceOpt: Option[String] = Some("SYSTEM")
   override val db = new OracleDatabaseOnDocker
 
-  override def defaultMetadata(dataType: DataType): Metadata = new MetadataBuilder()
-    .putLong("scale", 0)
-    .putBoolean("isTimestampNTZ", false)
-    .putBoolean("isSigned", dataType.isInstanceOf[NumericType] || dataType.isInstanceOf[StringType])
-    .putString(CHAR_VARCHAR_TYPE_STRING_METADATA_KEY, "varchar(255)")
-    .build()
+  object JdbcClientTypes {
+    val NUMBER = "NUMBER"
+    val STRING = "VARCHAR2"
+  }
+
+  override def defaultMetadata(
+      dataType: DataType = StringType,
+      jdbcClientType: String = JdbcClientTypes.STRING): Metadata =
+    new MetadataBuilder()
+      .putLong("scale", 0)
+      .putBoolean("isTimestampNTZ", false)
+      .putBoolean(
+        "isSigned",
+        dataType.isInstanceOf[NumericType] || dataType.isInstanceOf[StringType])
+      .putString(CHAR_VARCHAR_TYPE_STRING_METADATA_KEY, "varchar(255)")
+      .putString("jdbcClientType", jdbcClientType)
+      .build()
+
 
   override def sparkConf: SparkConf = super.sparkConf
     .set("spark.sql.catalog.oracle", classOf[JDBCTableCatalog].getName)
@@ -105,12 +117,20 @@ class OracleIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTes
     sql(s"CREATE TABLE $tbl (ID INTEGER)")
     var t = spark.table(tbl)
     var expectedSchema = new StructType()
-      .add("ID", DecimalType(10, 0), true, super.defaultMetadata(DecimalType(10, 0)))
+      .add(
+        "ID",
+        DecimalType(10, 0),
+        true,
+        super.defaultMetadata(DecimalType(10, 0), JdbcClientTypes.NUMBER))
     assert(t.schema === expectedSchema)
     sql(s"ALTER TABLE $tbl ALTER COLUMN id TYPE LONG")
     t = spark.table(tbl)
     expectedSchema = new StructType()
-      .add("ID", DecimalType(19, 0), true, super.defaultMetadata(DecimalType(19, 0)))
+      .add(
+        "ID",
+        DecimalType(19, 0),
+        true,
+        super.defaultMetadata(DecimalType(19, 0), JdbcClientTypes.NUMBER))
     assert(t.schema === expectedSchema)
     // Update column type from LONG to INTEGER
     val sql1 = s"ALTER TABLE $tbl ALTER COLUMN id TYPE INTEGER"
diff --git a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/PostgresIntegrationSuite.scala b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/PostgresIntegrationSuite.scala
index 2318fba90f5a..5211b5d328e9 100644
--- a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/PostgresIntegrationSuite.scala
+++ b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/PostgresIntegrationSuite.scala
@@ -38,6 +38,22 @@ import org.apache.spark.tags.DockerTest
 class PostgresIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTest {
   override val catalogName: String = "postgresql"
   override val db = new PostgresDatabaseOnDocker
+
+  object JdbcClientTypes {
+    val INTEGER = "int4"
+    val STRING = "text"
+  }
+
+  override def defaultMetadata(
+      dataType: DataType = StringType,
+      jdbcClientType: String = JdbcClientTypes.STRING): Metadata =
+    new MetadataBuilder()
+      .putLong("scale", 0)
+      .putBoolean("isTimestampNTZ", false)
+      .putBoolean("isSigned", dataType.isInstanceOf[NumericType])
+      .putString("jdbcClientType", jdbcClientType)
+      .build()
+
   override def sparkConf: SparkConf = super.sparkConf
     .set("spark.sql.catalog.postgresql", classOf[JDBCTableCatalog].getName)
     .set("spark.sql.catalog.postgresql.url", db.getJdbcUrl(dockerIp, externalPort))
@@ -193,7 +209,7 @@ class PostgresIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCT
     sql(s"CREATE TABLE $tbl (ID INTEGER)")
     var t = spark.table(tbl)
     var expectedSchema = new StructType()
-      .add("ID", IntegerType, true, defaultMetadata(IntegerType))
+      .add("ID", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
     assert(t.schema === expectedSchema)
     sql(s"ALTER TABLE $tbl ALTER COLUMN id TYPE STRING")
     t = spark.table(tbl)
@@ -222,7 +238,7 @@ class PostgresIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCT
       s" TBLPROPERTIES('TABLESPACE'='pg_default')")
     val t = spark.table(tbl)
     val expectedSchema = new StructType()
-      .add("ID", IntegerType, true, defaultMetadata(IntegerType))
+      .add("ID", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
     assert(t.schema === expectedSchema)
   }
 
diff --git a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/V2JDBCTest.scala b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/V2JDBCTest.scala
index 47e1c8c06dd4..3da2b4f74b82 100644
--- a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/V2JDBCTest.scala
+++ b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/V2JDBCTest.scala
@@ -48,23 +48,52 @@ private[v2] trait V2JDBCTest extends SharedSparkSession with DockerIntegrationFu
 
   def notSupportsTableComment: Boolean = false
 
-  def defaultMetadata(dataType: DataType = StringType): Metadata = new MetadataBuilder()
+  def defaultMetadata(
+      dataType: DataType = StringType,
+      jdbcClientType: String = "STRING"): Metadata = new MetadataBuilder()
     .putLong("scale", 0)
     .putBoolean("isTimestampNTZ", false)
     .putBoolean("isSigned", dataType.isInstanceOf[NumericType])
+    .putString("jdbcClientType", jdbcClientType)
     .build()
 
+  /**
+   * Returns a copy of the given [[StructType]] with the specified metadata key removed
+   * from all of its fields.
+   */
+  def removeMetadataFromAllFields(structType: StructType, metadataKey: String): StructType = {
+    val updatedFields = structType.fields.map { field =>
+      val oldMetadata = field.metadata
+      val newMetadataBuilder = new MetadataBuilder()
+        .withMetadata(oldMetadata)
+        .remove(metadataKey)
+      field.copy(metadata = newMetadataBuilder.build())
+    }
+    StructType(updatedFields)
+  }
+
   def testUpdateColumnNullability(tbl: String): Unit = {
     sql(s"CREATE TABLE $catalogName.alt_table (ID STRING NOT NULL)")
     var t = spark.table(s"$catalogName.alt_table")
     // nullable is true in the expectedSchema because Spark always sets nullable to true
     // regardless of the JDBC metadata https://github.com/apache/spark/pull/18445
     var expectedSchema = new StructType().add("ID", StringType, true, defaultMetadata())
-    assert(t.schema === expectedSchema)
+    // If function is not overriden we don't want to compare external engine types
+    var expectedSchemaWithoutJdbcClientType =
+      removeMetadataFromAllFields(expectedSchema, "jdbcClientType")
+    var schemaWithoutJdbcClientType =
+      removeMetadataFromAllFields(t.schema, "jdbcClientType")
+    assert(schemaWithoutJdbcClientType === expectedSchemaWithoutJdbcClientType)
     sql(s"ALTER TABLE $catalogName.alt_table ALTER COLUMN ID DROP NOT NULL")
     t = spark.table(s"$catalogName.alt_table")
     expectedSchema = new StructType().add("ID", StringType, true, defaultMetadata())
-    assert(t.schema === expectedSchema)
+
+    // If function is not overriden we don't want to compare external engine types
+    expectedSchemaWithoutJdbcClientType =
+      removeMetadataFromAllFields(expectedSchema, "jdbcClientType")
+    schemaWithoutJdbcClientType =
+      removeMetadataFromAllFields(t.schema, "jdbcClientType")
+    assert(schemaWithoutJdbcClientType === expectedSchemaWithoutJdbcClientType)
     // Update nullability of not existing column
     val sqlText = s"ALTER TABLE $catalogName.alt_table ALTER COLUMN bad_column DROP NOT NULL"
     checkError(
@@ -85,7 +114,13 @@ private[v2] trait V2JDBCTest extends SharedSparkSession with DockerIntegrationFu
     val expectedSchema = new StructType().add("RENAMED", StringType, true, defaultMetadata())
       .add("ID1", StringType, true, defaultMetadata())
       .add("ID2", StringType, true, defaultMetadata())
-    assert(t.schema === expectedSchema)
+
+    // If function is not overriden we don't want to compare external engine types
+    val expectedSchemaWithoutJdbcClientType =
+      removeMetadataFromAllFields(expectedSchema, "jdbcClientType")
+    val schemaWithoutJdbcClientType =
+      removeMetadataFromAllFields(t.schema, "jdbcClientType")
+    assert(schemaWithoutJdbcClientType === expectedSchemaWithoutJdbcClientType)
   }
 
   def testCreateTableWithProperty(tbl: String): Unit = {}
@@ -109,18 +144,30 @@ private[v2] trait V2JDBCTest extends SharedSparkSession with DockerIntegrationFu
       var t = spark.table(s"$catalogName.alt_table")
       var expectedSchema = new StructType()
         .add("ID", StringType, true, defaultMetadata())
-      assert(t.schema === expectedSchema)
+      var expectedSchemaWithoutJdbcClientType =
+        removeMetadataFromAllFields(expectedSchema, "jdbcClientType")
+      var schemaWithoutJdbcClientType =
+        removeMetadataFromAllFields(t.schema, "jdbcClientType")
+      assert(schemaWithoutJdbcClientType === expectedSchemaWithoutJdbcClientType)
       sql(s"ALTER TABLE $catalogName.alt_table ADD COLUMNS (C1 STRING, C2 STRING)")
       t = spark.table(s"$catalogName.alt_table")
       expectedSchema = expectedSchema
         .add("C1", StringType, true, defaultMetadata())
         .add("C2", StringType, true, defaultMetadata())
-      assert(t.schema === expectedSchema)
+      expectedSchemaWithoutJdbcClientType =
+        removeMetadataFromAllFields(expectedSchema, "jdbcClientType")
+      schemaWithoutJdbcClientType =
+        removeMetadataFromAllFields(t.schema, "jdbcClientType")
+      assert(schemaWithoutJdbcClientType === expectedSchemaWithoutJdbcClientType)
       sql(s"ALTER TABLE $catalogName.alt_table ADD COLUMNS (C3 STRING)")
       t = spark.table(s"$catalogName.alt_table")
       expectedSchema = expectedSchema
         .add("C3", StringType, true, defaultMetadata())
-      assert(t.schema === expectedSchema)
+      expectedSchemaWithoutJdbcClientType =
+        removeMetadataFromAllFields(expectedSchema, "jdbcClientType")
+      schemaWithoutJdbcClientType =
+        removeMetadataFromAllFields(t.schema, "jdbcClientType")
+      assert(schemaWithoutJdbcClientType === expectedSchemaWithoutJdbcClientType)
       // Add already existing column
       checkError(
         exception = intercept[AnalysisException] {
@@ -152,7 +199,11 @@ private[v2] trait V2JDBCTest extends SharedSparkSession with DockerIntegrationFu
       val t = spark.table(s"$catalogName.alt_table")
       val expectedSchema = new StructType()
         .add("C2", StringType, true, defaultMetadata())
-      assert(t.schema === expectedSchema)
+      val expectedSchemaWithoutJdbcClientType =
+        removeMetadataFromAllFields(expectedSchema, "jdbcClientType")
+      val schemaWithoutJdbcClientType =
+        removeMetadataFromAllFields(t.schema, "jdbcClientType")
+      assert(schemaWithoutJdbcClientType === expectedSchemaWithoutJdbcClientType)
       // Drop not existing column
       val sqlText = s"ALTER TABLE $catalogName.alt_table DROP COLUMN bad_column"
       checkError(
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JdbcUtils.scala b/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JdbcUtils.scala
index 651c29d09766..cdd2131e9cd5 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JdbcUtils.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JdbcUtils.scala
@@ -314,6 +314,7 @@ object JdbcUtils extends Logging with SQLConfHelper {
       metadata.putBoolean("isSigned", isSigned)
       metadata.putBoolean("isTimestampNTZ", isTimestampNTZ)
       metadata.putLong("scale", fieldScale)
+      metadata.putString("jdbcClientType", typeName)
       dialect.updateExtraColumnMeta(conn, rsmd, i + 1, metadata)
 
       val columnType =
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTableCatalogSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTableCatalogSuite.scala
index f6b63d7939a9..45a463822cd1 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTableCatalogSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTableCatalogSuite.scala
@@ -38,10 +38,21 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
   val tempDir = Utils.createTempDir()
   val url = s"jdbc:h2:${tempDir.getCanonicalPath};user=testUser;password=testPass"
 
-  def defaultMetadata(dataType: DataType): Metadata = new MetadataBuilder()
+  object JdbcClientTypes {
+    val CHAR = "CHARACTER"
+    val CLOB = "CHARACTER LARGE OBJECT"
+    val DOUBLE = "DOUBLE PRECISION"
+    val INTEGER = "INTEGER"
+    val STRING = "CHARACTER VARYING"
+  }
+
+  def defaultMetadata(
+      dataType: DataType,
+      jdbcClientType: String): Metadata = new MetadataBuilder()
     .putLong("scale", 0)
     .putBoolean("isTimestampNTZ", false)
     .putBoolean("isSigned", dataType.isInstanceOf[NumericType])
+    .putString("jdbcClientType", jdbcClientType)
     .build()
 
   override def sparkConf: SparkConf = super.sparkConf
@@ -144,8 +155,12 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
   test("load a table") {
     val t = spark.table("h2.test.people")
     val expectedSchema = new StructType()
-      .add("NAME", VarcharType(32), true, defaultMetadata(VarcharType(32)))
-      .add("ID", IntegerType, true, defaultMetadata(IntegerType))
+      .add(
+        "NAME",
+        VarcharType(32),
+        true,
+        defaultMetadata(VarcharType(32), JdbcClientTypes.STRING))
+      .add("ID", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
     assert(t.schema === CharVarcharUtils.replaceCharVarcharWithStringInSchema(expectedSchema))
     Seq(
       "h2.test.not_existing_table" -> "`h2`.`test`.`not_existing_table`",
@@ -187,13 +202,17 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
       sql(s"ALTER TABLE $tableName ADD COLUMNS (C1 INTEGER, C2 STRING)")
       var t = spark.table(tableName)
       var expectedSchema = new StructType()
-        .add("ID", IntegerType, true, defaultMetadata(IntegerType))
-        .add("C1", IntegerType, true, defaultMetadata(IntegerType))
-        .add("C2", StringType, true, defaultMetadata(StringType))
+        .add("ID", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
+        .add("C1", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
+        .add("C2", StringType, true, defaultMetadata(StringType, JdbcClientTypes.CLOB))
       assert(t.schema === expectedSchema)
       sql(s"ALTER TABLE $tableName ADD COLUMNS (c3 DOUBLE)")
       t = spark.table(tableName)
-      expectedSchema = expectedSchema.add("c3", DoubleType, true, defaultMetadata(DoubleType))
+      expectedSchema = expectedSchema.add(
+        "c3",
+        DoubleType,
+        true,
+        defaultMetadata(DoubleType, JdbcClientTypes.DOUBLE))
       assert(t.schema === expectedSchema)
       // Add already existing column
       checkError(
@@ -231,8 +250,8 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
       sql(s"ALTER TABLE $tableName RENAME COLUMN id TO C")
       val t = spark.table(tableName)
       val expectedSchema = new StructType()
-        .add("C", IntegerType, true, defaultMetadata(IntegerType))
-        .add("C0", IntegerType, true, defaultMetadata(IntegerType))
+        .add("C", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
+        .add("C0", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
       assert(t.schema === expectedSchema)
       // Rename to already existing column
       checkError(
@@ -271,7 +290,7 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
       sql(s"ALTER TABLE $tableName DROP COLUMN c3")
       val t = spark.table(tableName)
       val expectedSchema = new StructType()
-        .add("C2", IntegerType, true, defaultMetadata(IntegerType))
+        .add("C2", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
       assert(t.schema === expectedSchema)
       // Drop not existing column
       val sqlText = s"ALTER TABLE $tableName DROP COLUMN bad_column"
@@ -307,8 +326,12 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
       sql(s"ALTER TABLE $tableName ALTER COLUMN deptno TYPE DOUBLE")
       val t = spark.table(tableName)
       val expectedSchema = new StructType()
-        .add("ID", DoubleType, true, defaultMetadata(DoubleType))
-        .add("deptno", DoubleType, true, defaultMetadata(DoubleType))
+        .add("ID", DoubleType, true, defaultMetadata(DoubleType, JdbcClientTypes.DOUBLE))
+        .add(
+          "deptno",
+          DoubleType,
+          true,
+          defaultMetadata(DoubleType, JdbcClientTypes.DOUBLE))
       assert(t.schema === expectedSchema)
       // Update not existing column
       val sqlText = s"ALTER TABLE $tableName ALTER COLUMN bad_column TYPE DOUBLE"
@@ -352,8 +375,12 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
       sql(s"ALTER TABLE $tableName ALTER COLUMN deptno DROP NOT NULL")
       val t = spark.table(tableName)
       val expectedSchema = new StructType()
-        .add("ID", IntegerType, true, defaultMetadata(IntegerType))
-        .add("deptno", IntegerType, true, defaultMetadata(IntegerType))
+        .add("ID", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
+        .add(
+          "deptno",
+          IntegerType,
+          true,
+          defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
       assert(t.schema === expectedSchema)
       // Update nullability of not existing column
       val sqlText = s"ALTER TABLE $tableName ALTER COLUMN bad_column DROP NOT NULL"
@@ -482,8 +509,8 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
       sql(s"CREATE TABLE $tableName (c1 INTEGER NOT NULL, c2 INTEGER)")
       var t = spark.table(tableName)
       var expectedSchema = new StructType()
-        .add("c1", IntegerType, true, defaultMetadata(IntegerType))
-        .add("c2", IntegerType, true, defaultMetadata(IntegerType))
+        .add("c1", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
+        .add("c2", IntegerType, true, defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
       assert(t.schema === expectedSchema)
 
       withSQLConf(SQLConf.CASE_SENSITIVE.key -> "true") {
@@ -503,8 +530,16 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
       withSQLConf(SQLConf.CASE_SENSITIVE.key -> "false") {
         sql(s"ALTER TABLE $tableName RENAME COLUMN C2 TO c3")
         expectedSchema = new StructType()
-          .add("c1", IntegerType, true, defaultMetadata(IntegerType))
-          .add("c3", IntegerType, true, defaultMetadata(IntegerType))
+          .add(
+            "c1",
+            IntegerType,
+            true,
+            defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
+          .add(
+            "c3",
+            IntegerType,
+            true,
+            defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
         t = spark.table(tableName)
         assert(t.schema === expectedSchema)
       }
@@ -526,7 +561,11 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
       withSQLConf(SQLConf.CASE_SENSITIVE.key -> "false") {
         sql(s"ALTER TABLE $tableName DROP COLUMN C3")
         expectedSchema = new StructType()
-          .add("c1", IntegerType, true, defaultMetadata(IntegerType))
+          .add(
+            "c1",
+            IntegerType,
+            true,
+            defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
         t = spark.table(tableName)
         assert(t.schema === expectedSchema)
       }
@@ -548,7 +587,7 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
       withSQLConf(SQLConf.CASE_SENSITIVE.key -> "false") {
         sql(s"ALTER TABLE $tableName ALTER COLUMN C1 TYPE DOUBLE")
         expectedSchema = new StructType()
-          .add("c1", DoubleType, true, defaultMetadata(DoubleType))
+          .add("c1", DoubleType, true, defaultMetadata(DoubleType, JdbcClientTypes.DOUBLE))
         t = spark.table(tableName)
         assert(t.schema === expectedSchema)
       }
@@ -570,7 +609,7 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
       withSQLConf(SQLConf.CASE_SENSITIVE.key -> "false") {
         sql(s"ALTER TABLE $tableName ALTER COLUMN C1 DROP NOT NULL")
         expectedSchema = new StructType()
-          .add("c1", DoubleType, true, defaultMetadata(IntegerType))
+          .add("c1", DoubleType, true, defaultMetadata(DoubleType, JdbcClientTypes.DOUBLE))
         t = spark.table(tableName)
         assert(t.schema === expectedSchema)
       }
@@ -640,8 +679,12 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
       sql(s"ALTER TABLE $tableName ALTER COLUMN deptno TYPE VARCHAR(30)")
       val t = spark.table(tableName)
       val expected = new StructType()
-        .add("ID", CharType(10), true, defaultMetadata(CharType(10)))
-        .add("deptno", VarcharType(30), true, defaultMetadata(VarcharType(30)))
+        .add("ID", CharType(10), true, defaultMetadata(CharType(10), JdbcClientTypes.CHAR))
+        .add(
+          "deptno",
+          VarcharType(30),
+          true,
+          defaultMetadata(VarcharType(30), JdbcClientTypes.STRING))
       val replaced = CharVarcharUtils.replaceCharVarcharWithStringInSchema(expected)
       assert(t.schema === replaced)
     }
@@ -654,13 +697,22 @@ class JDBCTableCatalogSuite extends QueryTest with SharedSparkSession {
         .executeUpdate())
       withSQLConf(SQLConf.LEGACY_CHAR_VARCHAR_AS_STRING.key -> "true") {
         val expected = new StructType()
-          .add("ID", StringType, true, defaultMetadata(StringType))
-          .add("DEPTNO", StringType, true, defaultMetadata(StringType))
+          // We are not changing the remote type name so it remains CHAR
+          .add("ID", StringType, true, defaultMetadata(StringType, JdbcClientTypes.CHAR))
+          .add(
+            "DEPTNO",
+            StringType,
+            true,
+            defaultMetadata(StringType, JdbcClientTypes.STRING))
         assert(sql(s"SELECT * FROM h2.test.char_tbl").schema === expected)
       }
       val expected = new StructType()
-        .add("ID", CharType(5), true, defaultMetadata(CharType(5)))
-        .add("DEPTNO", VarcharType(10), true, defaultMetadata(VarcharType(10)))
+        .add("ID", CharType(5), true, defaultMetadata(CharType(5), JdbcClientTypes.CHAR))
+        .add(
+          "DEPTNO",
+          VarcharType(10),
+          true,
+          defaultMetadata(VarcharType(10), JdbcClientTypes.STRING))
       val replaced = CharVarcharUtils.replaceCharVarcharWithStringInSchema(expected)
       assert(sql(s"SELECT * FROM h2.test.char_tbl").schema === replaced)
     } finally {
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCSuite.scala
index a1d83ee66508..6896f6993fb3 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCSuite.scala
@@ -78,10 +78,18 @@ class JDBCSuite extends QueryTest with SharedSparkSession {
     }
   }
 
-  def defaultMetadata(dataType: DataType): Metadata = new MetadataBuilder()
+  object JdbcClientTypes {
+    val INTEGER = "INTEGER"
+    val STRING = "CHARACTER VARYING"
+  }
+
+  def defaultMetadata(
+      dataType: DataType,
+      jdbcClientType: String): Metadata = new MetadataBuilder()
     .putLong("scale", 0)
     .putBoolean("isTimestampNTZ", false)
     .putBoolean("isSigned", dataType.isInstanceOf[NumericType])
+    .putString("jdbcClientType", jdbcClientType)
     .build()
 
   override def beforeAll(): Unit = {
@@ -1429,8 +1437,18 @@ class JDBCSuite extends QueryTest with SharedSparkSession {
   }
 
   test("SPARK-16848: jdbc API throws an exception for user specified schema") {
-    val schema = StructType(Seq(StructField("name", StringType, false, defaultMetadata(StringType)),
-      StructField("theid", IntegerType, false, defaultMetadata(IntegerType))))
+    val schema = StructType(Seq(
+      StructField(
+        "name",
+        StringType,
+        false,
+        defaultMetadata(StringType, JdbcClientTypes.STRING)),
+      StructField(
+        "theid",
+        IntegerType,
+        false,
+        defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))
+    ))
     val parts = Array[String]("THEID < 2", "THEID >= 2")
     val e1 = intercept[AnalysisException] {
       spark.read.schema(schema).jdbc(urlWithUserAndPass, "TEST.PEOPLE", parts, new Properties())
@@ -1451,8 +1469,17 @@ class JDBCSuite extends QueryTest with SharedSparkSession {
     val df = spark.read.jdbc(urlWithUserAndPass, "TEST.PEOPLE", parts, props)
     assert(df.schema.size === 2)
     val structType = CatalystSqlParser.parseTableSchema(customSchema)
+    val sparkToRemoteDataType: Map[DataType, String] = Map(
+      IntegerType -> JdbcClientTypes.INTEGER,
+      StringType -> JdbcClientTypes.STRING,
+      VarcharType(32) -> JdbcClientTypes.STRING)
     val expectedSchema = new StructType(structType.map(
-      f => StructField(f.name, f.dataType, f.nullable, defaultMetadata(f.dataType))).toArray)
+      f => StructField(
+        f.name,
+        f.dataType,
+        f.nullable,
+        defaultMetadata(f.dataType, sparkToRemoteDataType(f.dataType)))
+    ).toArray)
     assert(df.schema === CharVarcharUtils.replaceCharVarcharWithStringInSchema(expectedSchema))
     assert(df.count() === 3)
   }
@@ -1469,8 +1496,17 @@ class JDBCSuite extends QueryTest with SharedSparkSession {
         """.stripMargin.replaceAll("\n", " "))
       val df = sql("select * from people_view")
       assert(df.schema.length === 2)
+      val sparkToRemoteDataType: Map[DataType, String] = Map(
+        IntegerType -> JdbcClientTypes.INTEGER,
+        StringType -> JdbcClientTypes.STRING,
+        VarcharType(32) -> JdbcClientTypes.STRING)
       val expectedSchema = new StructType(CatalystSqlParser.parseTableSchema(customSchema)
-        .map(f => StructField(f.name, f.dataType, f.nullable, defaultMetadata(f.dataType))).toArray)
+        .map(f => StructField(
+          f.name,
+          f.dataType,
+          f.nullable,
+          defaultMetadata(f.dataType, sparkToRemoteDataType(f.dataType)))
+        ).toArray)
 
       assert(df.schema === CharVarcharUtils.replaceCharVarcharWithStringInSchema(expectedSchema))
       assert(df.count() === 3)
@@ -1616,9 +1652,16 @@ class JDBCSuite extends QueryTest with SharedSparkSession {
     }
 
   test("jdbc data source shouldn't have unnecessary metadata in its schema") {
-    var schema = StructType(
-      Seq(StructField("NAME", VarcharType(32), true, defaultMetadata(VarcharType(32))),
-      StructField("THEID", IntegerType, true, defaultMetadata(IntegerType))))
+    var schema = StructType(Seq(
+      StructField(
+        "NAME",
+        VarcharType(32),
+        true,
+        defaultMetadata(VarcharType(32), JdbcClientTypes.STRING)),
+      StructField("THEID",
+        IntegerType,
+        true,
+        defaultMetadata(IntegerType, JdbcClientTypes.INTEGER))))
     schema = CharVarcharUtils.replaceCharVarcharWithStringInSchema(schema)
     val df = spark.read.format("jdbc")
       .option("Url", urlWithUserAndPass)


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