Issue reading Vertica table into Databricks - Numeric value out of range

[vidya_kothavale]

I am trying to read a Vertica table into a Spark DataFrame using JDBC in Databricks.

Here is my sample code:

hostname = ""

username = ""

password = ""

database_port = ""

database_name = ""

qry_col_level = f"""

SELECT * FROM analytics_DS.ansh_units_cum_dash"""

col_level_df = spark.read.format("jdbc") \

    .option("url", f"jdbc:vertica://{hostname}:{database_port}/{database_name}") \

    .option("query", qry_col_level) \

    .option("user", username) \

    .option("password", password) \

    .option("driver", "com.vertica.jdbc.Driver") \

    .option("numericPrecisionMapping", "true") \

    .option("fetchsize", "1000") \

    .load()

display(col_level_df)

I am getting the following error:

[NUMERIC_VALUE_OUT_OF_RANGE.WITHOUT_SUGGESTION] The 21.37807011542558910054047714456053246284 rounded half up from 21.378070115425589100540477144560532462836087844639698200 cannot be represented as Decimal(38, 38). SQLSTATE: 22003

• Is there an option in Databricks or Spark JDBC connector to automatically truncate or round numeric/decimal values when loading?

• is there any option other than casting ?

[Renu_]

Hi @vidya_kothavale, based on my research and understanding, Databricks and Spark's JDBC connectors currently don’t offer an automatic way to truncate or round high precision decimal values when loading data. To handle this, you would need to either:

• Modify your SQL query to explicitly cast the columns to a lower precision, or
• Create a view in Vertica where you round or truncate the values before reading them into Spark.