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

vidya_kothavale — Sat, 26 Apr 2025 18:53:29 GMT

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 ?

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

Renu_ — Mon, 28 Apr 2025 10:21:39 GMT

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.

topic Issue reading Vertica table into Databricks - Numeric value out of range in Data Engineering

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

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