TopolearnSpark 4 turned on ANSI SQL mode by default, so bad casts and overflows now fail jobs instead of silently producing null.
Spark SQL types include int, bigint, double, decimal(p,s), string, boolean, date, timestamp (with session time zone), timestamp_ntz, binary, and the complex array, map, struct and variant types.
Convert with F.col("x").cast("int") or .cast(IntegerType()). Since Spark 4.0, ANSI mode (spark.sql.ansi.enabled) is on by default:
- Casting an invalid string like
"abc"tointraises an error instead of returning null. - Integer overflow and division by zero raise errors.
- Use
F.try_to_number,F.expr("try_cast(x AS INT)"),F.try_divideorF.try_addwhen invalid input should become null.
Nulls follow SQL rules: comparisons with null give null, and filter drops those rows. Use isNull(), isNotNull(), or the null-safe equality eqNullSafe (<=> in SQL). Handling them:
F.coalesce("a", "b", F.lit(0))returns the first non-null value.df.na.fill({"qty": 0, "city": "unknown"})anddf.na.drop(subset=["id"]).- Aggregates skip nulls;
count("col")counts only non-null values.
For dates, parse with F.to_date("s", "dd/MM/yyyy") or F.to_timestamp, which use Java datetime patterns.
Example
from pyspark.sql import SparkSession, functions as F
spark = SparkSession.builder.master("local[2]").getOrCreate()
df = spark.createDataFrame([("1", "10"), ("2", "x"), (None, "5")], "id STRING, qty STRING")
print(spark.conf.get("spark.sql.ansi.enabled")) # true
try:
df.select(F.col("qty").cast("int")).collect()
except Exception as e:
print(type(e).__name__) # NumberFormatException (ANSI: bad cast fails)
safe = df.select(
F.col("id").cast("int").alias("id"),
F.expr("try_cast(qty AS INT)").alias("qty"),
)
print([tuple(r) for r in safe.collect()]) # [(1, 10), (2, None), (None, 5)]
filled = safe.na.fill({"qty": 0}).withColumn("key", F.coalesce("id", F.lit(-1)))
print([tuple(r) for r in filled.collect()]) # [(1, 10, 1), (2, 0, 2), (None, 5, -1)]Remember
- Spark 4 runs in ANSI mode: bad casts and overflows raise errors
- Use
try_cast,try_divideand friends where invalid input should be null - Handle nulls with
coalesce,na.fill,na.dropandeqNullSafe
Watch out for
- Upgrading Spark 3 code that relied on silent null-producing casts and seeing jobs fail under ANSI mode.