spark union doesn't work as expect, add new rows

Question

I can't use simple test set to reproduce this problem, it only happen on my dataset. So I can only tell the situation .

df have many distinct store_id,product_id groups, each group has many rows .

df1 have many distinct store_id,product_id groups with just one row or not.

df is order history table, I need get history price from it, and get current price from df1 . Union they to construct a whole time price change line .

But the strange thing is

 sid = '00fbb2a6-f2de-42f1-a07b-163e3a050ddb' pid = '66e06f08-dec2-498d-883f-24771da18358' filtersp = lambda df: df.filter(col('store_id')==sid).filter(col('product_id')==pid) filtersp(df).show() +----------------+--------+----------+-----------+---+ |store_product_id|store_id|product_id|price_guide| ds| +----------------+--------+----------+-----------+---+ +----------------+--------+----------+-----------+---+ filtersp(df1).show() +----------------+----------+--------+-----------+---+ |store_product_id|product_id|store_id|price_guide| ds| +----------------+----------+--------+-----------+---+ +----------------+----------+--------+-----------+---+ filtersp(df1).union(filtersp(df)).show() +----------------+----------+--------+-----------+---+ |store_product_id|product_id|store_id|price_guide| ds| +----------------+----------+--------+-----------+---+ +----------------+----------+--------+-----------+---+ filtersp(df1.union(df)).show() +----------------+----------+--------+-----------+---+ |store_product_id|product_id|store_id|price_guide| ds| +----------------+----------+--------+-----------+---+ +----------------+----------+--------+-----------+---+ filtersp(df.union(df1)).show() +--------------------+--------------------+--------------------+-----------+-------------------+ | store_product_id| store_id| product_id|price_guide| ds| +--------------------+--------------------+--------------------+-----------+-------------------+ |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00| +--------------------+--------------------+--------------------+-----------+-------------------+

Then I add a new column to track where these rows come from

df = df.withColumn('c', lit('df')) df1 = df1.withColumn('c', lit('df1')) filtersp(df.union(df1)).show() +--------------------+--------------------+--------------------+-----------+-------------------+---+ | store_product_id| store_id| product_id|price_guide| ds| c| +--------------------+--------------------+--------------------+-----------+-------------------+---+ |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00|df1| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00|df1| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00|df1| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00|df1| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00|df1| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00|df1| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00|df1| |996864cf-8432-43d...|00fbb2a6-f2de-42f...|66e06f08-dec2-498...| 480|2019-08-06 09:00:00|df1| +--------------------+--------------------+--------------------+-----------+-------------------+---+

Find the rows come from df1.

I don't understand in what situation filtersp(df.union(df1)).show() would show result, it is impossible .

Mithril · Accepted Answer · 2019-08-06 02:30:03Z

To knock myself. Though I found the answer https://stackoverflow.com/a/55310670/1637673 :

def unionByName(other: Dataset[T]): Dataset[T]

The difference between this function and union is that this function resolves columns by name (not by position):

val df1 = Seq((1, 2, 3)).toDF("col0", "col1", "col2") val df2 = Seq((4, 5, 6)).toDF("col1", "col2", "col0") df1.union(df2).show // output: // +----+----+----+ // |col0|col1|col2| // +----+----+----+ // | 1| 2| 3| // | 4| 5| 6| // +----+----+----+

But I don't think I have this problem, after some struglling finnally find the columns order are different.

df is

+----------------+--------+----------+-----------+---+---+ |store_product_id|store_id|product_id|price_guide| ds| c| +----------------+--------+----------+-----------+---+---+ +----------------+--------+----------+-----------+---+---+

df1 is

+----------------+----------+--------+-----------+---+---+ |store_product_id|product_id|store_id|price_guide| ds| c| +----------------+----------+--------+-----------+---+---+ +----------------+----------+--------+-----------+---+---+

The position of product_id|store_id is differnt .

Collectives™ on Stack Overflow

spark union doesn't work as expect, add new rows

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related