Home / Spark / Query compilation
Spark: [CAST_INVALID_INPUT] The value cannot be cast because it is malformed
org.apache.spark.SparkNumberFormatException[CAST_INVALID_INPUT] The value 'N/A' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead.Under ANSI mode (the default since Spark 4.0) a CAST of a string that is not a valid number or date raises this error. Spark 3 returned NULL for the same input, so it usually appears right after an upgrade.
Common causes
- A string column contains values such as '', 'N/A' or '12,5' that are cast to a number or date.How to confirm: The message shows the exact value; search the input for it.
How to fix it
Use try_cast, or try_to_number and try_to_date for formatted input, to get NULL for bad values.
SELECT try_cast(amount_text AS DECIMAL(12, 2)) AS amount FROM staging;Clean or filter the bad values before casting if NULL is not acceptable.
Where the message comes from
error-conditions.jsoninapache/sparkatv4.1.3- Message checked in Spark 4.1.3 (error condition CAST_INVALID_INPUT)
Message template in the source: The value <expression> of the type <sourceType> cannot be cast to <targetType> because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead.