spark-non-sql.md

Spark - using Pyspark functions

usually prefer using Spark SQL

but sometimes makes sense to use pyspark functions

select where like

df.filter((col"name").like("%rose%")).show()

sql = [f"drop table {row.tableName}" for row in df.filter(col("tableName").like("%products%")).select("tableName").collect()]

change empty array to null

a column of type array[struct<>] cannot be inserted if the array contents is empty. Change it to null.

array_struct_cols = ['some_col']
    from pyspark.sql import functions as F

    for col_name, col_type in df.dtypes:
        if col_type.startswith("array") and col_name in array_struct_cols:
            df = df.withColumn(
                col_name,
                F.when((F.size(F.col(col_name)) == 0), F.lit(None)).otherwise(
                    F.col(col_name)
                ),
            )

However in my case, the data type of the column is incorrect in the first place (because it sees [] it assumes type is array)