Absolutely! While spark.read.jdbc gives you column names and their mapped Spark SQL types (e.g., VARCHAR becomes StringType, which doesn’t carry length information), you can access the underlying JDBC Database Metadata directly through Spark to fetch those VARCHAR lengths. Here’s how to do it:

Approach 1: Directly Query JDBC Database Metadata via Spark #

Spark runs on the JVM, so you can leverage Java’s JDBC APIs directly within your Spark code (whether using Scala or Python). This lets you pull the same column metadata you’d get with raw JDBC, including VARCHAR length details.

Example in Python #

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("FetchVarcharLengths").getOrCreate()

# Your existing JDBC configuration
db_connection_string = "jdbc:mysql://your-db-host:3306/your-db-name"
connection_props = {
    "user": "your-username",
    "password": "your-password",
    "driver": "com.mysql.cj.jdbc.Driver"
}
target_schema = "your-schema"
target_table = "your-table"

# Access JVM classes to create a JDBC connection
jvm = spark._jvm
conn = jvm.java.sql.DriverManager.getConnection(
    db_connection_string,
    connection_props["user"],
    connection_props["password"]
)

try:
    # Get database metadata
    meta_data = conn.getMetaData()
    # Fetch all columns for the target table (wildcard "%")
    columns_result = meta_data.getColumns(None, target_schema, target_table, "%")

    # Iterate through results to filter VARCHAR columns
    print("VARCHAR Columns & Their Lengths:")
    while columns_result.next():
        col_name = columns_result.getString("COLUMN_NAME")
        col_type = columns_result.getString("TYPE_NAME")
        col_length = columns_result.getInt("COLUMN_SIZE")
        
        if col_type.upper() == "VARCHAR":
            print(f"- {col_name}: {col_length} characters")
finally:
    # Always close the connection to avoid leaks
    conn.close()

Example in Scala #

import org.apache.spark.sql.SparkSession

val spark = SparkSession.builder.appName("FetchVarcharLengths").getOrCreate()

// Your JDBC configuration
val dbConnectionString = "jdbc:mysql://your-db-host:3306/your-db-name"
val connectionProps = new java.util.Properties()
connectionProps.setProperty("user", "your-username")
connectionProps.setProperty("password", "your-password")
connectionProps.setProperty("driver", "com.mysql.cj.jdbc.Driver")
val targetSchema = "your-schema"
val targetTable = "your-table"

// Create JDBC connection
val conn = java.sql.DriverManager.getConnection(
    dbConnectionString,
    connectionProps.getProperty("user"),
    connectionProps.getProperty("password")
)

try {
    val metaData = conn.getMetaData()
    val columnsResult = metaData.getColumns(null, targetSchema, targetTable, "%")

    println("VARCHAR Columns & Their Lengths:")
    while (columnsResult.next()) {
        val colName = columnsResult.getString("COLUMN_NAME")
        val colType = columnsResult.getString("TYPE_NAME")
        val colLength = columnsResult.getInt("COLUMN_SIZE")
        
        if (colType.equalsIgnoreCase("VARCHAR")) {
            println(s"- $colName: $colLength characters")
        }
    }
} finally {
    conn.close()
}

Key Notes #

• Database-Specific Adjustments: Some databases use different type names (e.g., NVARCHAR for Unicode strings, VARCHAR2 in Oracle). Adjust the type check (col_type.upper() == "VARCHAR") to match your database’s terminology.
• COLUMN_SIZE Meaning: For most databases (like MySQL, PostgreSQL), COLUMN_SIZE for VARCHAR represents the maximum number of characters. In Oracle, it might refer to bytes depending on the column’s character set—verify your database’s documentation if needed.
• Combine with Spark DataFrames: You can store the length info in a dictionary/Map and pair it with your DataFrame’s schema for validation, data quality checks, or custom transformations.

内容的提问来源于stack exchange，提问作者Arnon Rodman