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