Differentiate sql null and json null pyspark dataframe

user_3220247 0
Viewed 57

I have a json file with inconsistent schema in which some fields may or may not present in the successive rows

Sample JSON file

{"table":"TABLEA","ID":1,"COLUMN1":283,"COLUMN2":0,"COLUMN3":0}
{"table":"TABLEA","ID":1,"COLUMN1":null,"COLUMN2":null,"COLUMN3":null}
{"table":"TABLEA","ID":1,"COLUMN4":"CLOSE","COLUMN5":"user"}
{"table":"TABLEA","ID":1,"COLUMN5":"user","COLUMN6":355}
{"table":"TABLEA","ID":1,"COLUMN5":"user","COLUMN4":"NOTE"}
{"table":"TABLEA","ID":1,"COLUMN5":"user","COLUMN4":"NOTE"}

The above json represents various updates that happened to a particular table In the above JSON,

  • the first event has updates on those only those 3 cols
  • the second event has updates as null for those 3 cols
  • the third event has updates for the other 2 cols

Basically, each event contains only columns that only has updates in it. If there are no updates then the column wont be available.

Problem

I want to differentiate the nulls that comes as part of updates in the event vs the nulls that got generated when loading this data to dataframe Since the schema here is dynamic, when I tried to load the json in the dataframe and tried to to display it. this is how it got stored

+-------+-------+-------+-------+------------+-------+---+------+
|COLUMN1|COLUMN2|COLUMN3|COLUMN4|     COLUMN5|COLUMN6| ID| table|
+-------+-------+-------+-------+------------+-------+---+------+
|    283|      0|      0|   null|        null|   null|  1|TABLEA|
|   null|   null|   null|   null|        null|   null|  1|TABLEA|
|   null|   null|   null|  CLOSE|        user|   null|  1|TABLEA|
|   null|   null|   null|   null|        user|    355|  1|TABLEA|
|   null|   null|   null|   NOTE|        user|   null|  1|TABLEA|
|   null|   null|   null|   NOTE|        user|   null|  1|TABLEA|
+-------+-------+-------+-------+------------+-------+---+------+

In which the second row is actually null updates that has been happened from the first 3 columns where as for the other rows, since those columns are not part of the event it just loaded with null values by default.

I wanted to differentiate the nulls that comes in the json file vs the nulls that loaded by default because of schema inconsistency.

What I tried

Tried couple of approaches but nothing works

spark = SparkSession \
        .builder \
        .appName("Test") \
        .getOrCreate()
    applicationId = spark.sparkContext.applicationId
    sc = spark.sparkContext

    print(sc.getConf().getAll())
    input_file_path = "above json file"
    print(str(input_file_path))
    json_df = spark.read.json(input_file_path)

    json_df.show()

    #method1
    json_df.withColumn("testcol", F.when(F.isnull('COLUMN1'), F.lit('NaN')).otherwise(F.col('COLUMN1'))).show()

    #method2
def has_column(df,col):
    try:
       df[col]
       return F.lit(True)
    except Exception:
       return F.lit(False)
    json_df.withColumn("testcol", F.when(has_column(json_df, 'COLUMN1'), F.col('COLUMN1')).otherwise(F.lit('NaN'))).show()

Any help would be appreciated. Thanks!

pyspark apache-spark python
1 Answers

As the nulls become same as soon as data is read , I would try to replace the null in file first and then read it .

with open(input_file_path) as f:
    newText=f.read().replace(":null",":NaN")

with open(input_file_path, "w") as f:
    f.write(newText)

Then json_df.show() should give below table

+-------+-------+-------+-------+-------+-------+---+------+
|COLUMN1|COLUMN2|COLUMN3|COLUMN4|COLUMN5|COLUMN6| ID| table|
+-------+-------+-------+-------+-------+-------+---+------+
|  283.0|    0.0|    0.0|   null|   null|   null|  1|TABLEA|
|    NaN|    NaN|    NaN|   null|   null|   null|  1|TABLEA|
|   null|   null|   null|  CLOSE|   user|   null|  1|TABLEA|
|   null|   null|   null|   null|   user|    355|  1|TABLEA|
|   null|   null|   null|   NOTE|   user|   null|  1|TABLEA|
|   null|   null|   null|   NOTE|   user|   null|  1|TABLEA|
+-------+-------+-------+-------+-------+-------+---+------+
user_190757710
Related