I have two tables `source_product` and `target_product` as shown here:

**source\_product**:

```
pitem_id    prev_id citem_id    crev_id qty     check_no    status
-------------------------------------------------------------------
AAA         null    null        null    null    null        null
AAA         A       Item_2      B       2       100         No
AAA         A       Item_3      A       1       100         No

```

**target\_product**:

```
pitem_id    prev_id citem_id    crev_id qty     check_no    status
-------------------------------------------------------------------
null        null    null        null    null    null        null
AAA         A       Item_2      B       2       100         No
AAA         A       Item_3      A       3       110         Yes

```

My required result is to compare the `source_product` and `target_product` tables and place the differences in a `final_product` table like this:

**Final\_product**:

```
pitem_id    prev_id citem_id    crev_id Validation_error    validation_column   Source_value    target_value
-------------------------------------------------------------------------------------------------------------
AAA         A       null        null    pitemid ,prev_id not found in target    null    null    null
AAA         A       Item_2  B   citemid ,crev_id not found in target            
AAA         A       Item_3  A   qty mismatch    qty 1   3
AAA         A       Item_3  A   check_no mismatch   cheeck_no   100 110
AAA         A       Item_3  A   status mismatch status  No  Yes

```

How can I write this query? I tried using union all but am getting duplicates and improper data.
['https://Stackoverflow.com/questions/50378316', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/9463188/']
You can try to hack something like this:

```py
from pyspark.sql.functions import col, lit, posexplode, expr, split

(DF1
    .select("*", posexplode(split(expr("repeat('_', num_elements - 1)"), '_')))
    .select(col("vars").getItem(col("pos")),col("vals").getItem(col("pos")))
    .show())

# +---------+---------+
# |vars[pos]|vals[pos]|
# +---------+---------+
# |        a|        1|
# |        b|        2|
# |        c|        3|
# |        d|        4|
# |        e|        5|
# |        f|        6|
# |        g|        7|
# |        a|        4|
# |        b|        5|
# |        g|        3|
# |        c|        6|
# |        c|        2|
# |        d|        3|
# |        a|        5|
# |        b|        7|
# |        c|        2|
# +---------+---------+

```

but it is anything but "cleaner and faster". Personally I would use `RDD`:

```py
(DF1.rdd
    .flatMap(lambda row: ((val, var) for val, var in zip(row.vals, row.vars)))
    .toDF(["val", "var"])
    .show())

# +---+---+
# |val|var|
# +---+---+
# |  1|  a|
# |  2|  b|
# |  3|  c|
# |  4|  d|
# |  5|  e|
# |  6|  f|
# |  7|  g|
# |  4|  a|
# |  5|  b|
# |  3|  g|
# |  6|  c|
# |  2|  c|
# |  3|  d|
# |  5|  a|
# |  7|  b|
# |  2|  c|
# +---+---+

```

but `udf` will work as well.
***Using SQL DDL schema format is another alternative.***

I have a similar problem in Scala, where we struggled so much to create a dynamic nested structure using case classes. A few days prior, I attended Databricks courses where I learned about a different approach and I'm not sure why nobody is talking about this approch.

The easiest method to make it dynamic is to feed SQL DDL from the configuration file.
Because this method uses SQL DDL, it may be applied to both Scala spark and Pyspark.

An example of how that actually appears is presented below.

**Schema Using Structs.** 

```
StructType([
    StructField(“field1”,  StringType(), true )
    StructField(“field2”, StructType([ 
        StructField(“field3”,  DoubleType(), true )
        StructField(“field4”,  LongType(), true )
    ])

```

**Schema Using SQL DDL.**

```
DDL_Schema =  " 'field1’ STRING, ‘field2’ STRUCT< 'field3’: DOUBLE, 'field4’: BIGINT>"

```

**Creating data frame using SQL DDL**

```
spark.read.schema (DDLSchema)•json(eventsJsonPath)

```

I hope that this may assist with a number of problems involving dynamic structure.

**Please refer to this page for more information.** <https://vincent.doba.fr/posts/20211004_spark_data_description_language_for_defining_spark_schema/>

Thank you