Merge (upsert) operations in Apache Spark - Mini Project: Build & Apply

Merge (upsert) operations

📖 Scenario: You work in a retail company. You have a current product inventory stored in a Spark DataFrame. New product updates arrive daily in another DataFrame. You want to update your inventory with these new updates. If a product exists, update its details. If it does not exist, add it as a new product.

🎯 Goal: Build a Spark program that merges two DataFrames using a merge (upsert) operation. The program will update existing product records and insert new ones.

📋 What You'll Learn

Create a Spark DataFrame called current_inventory with specific product data.

Create a Spark DataFrame called new_updates with new product data.

Use a merge operation on current_inventory and new_updates based on product_id.

Update existing products and insert new products.

Show the final merged DataFrame.

💡 Why This Matters

🌍 Real World

Retail companies often receive daily updates to their product inventories. Efficiently merging these updates into existing data is crucial to keep stock information accurate.

💼 Career

Data engineers and data scientists use merge (upsert) operations to maintain and update large datasets in data lakes and warehouses.

1

Create the current inventory DataFrame

Create a Spark DataFrame called current_inventory with these exact rows: (1, 'Apple', 50), (2, 'Banana', 30), (3, 'Carrot', 20). Use columns product_id, product_name, and quantity.

Apache Spark

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName('MergeUpsert').getOrCreate()

# Create current_inventory DataFrame with given rows and columns
# Your code here

Hint

Use spark.createDataFrame with a list of tuples and specify the column names.

2

Create the new updates DataFrame

Create a Spark DataFrame called new_updates with these exact rows: (2, 'Banana', 40), (4, 'Dates', 25). Use columns product_id, product_name, and quantity.

Apache Spark

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName('MergeUpsert').getOrCreate()

current_inventory = spark.createDataFrame([
    (1, 'Apple', 50),
    (2, 'Banana', 30),
    (3, 'Carrot', 20)
], ['product_id', 'product_name', 'quantity'])

# Create new_updates DataFrame with given rows and columns
# Your code here

Hint

Use spark.createDataFrame with the new rows and same columns as current_inventory.

3

Perform the merge (upsert) operation

Use the merge operation on current_inventory and new_updates based on product_id. Update the quantity and product_name for matching product_id. Insert new rows from new_updates that do not exist in current_inventory. Store the result in merged_inventory.

Apache Spark

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName('MergeUpsert').getOrCreate()

current_inventory = spark.createDataFrame([
    (1, 'Apple', 50),
    (2, 'Banana', 30),
    (3, 'Carrot', 20)
], ['product_id', 'product_name', 'quantity'])

new_updates = spark.createDataFrame([
    (2, 'Banana', 40),
    (4, 'Dates', 25)
], ['product_id', 'product_name', 'quantity'])

# Perform merge (upsert) operation to create merged_inventory
# Your code here

Hint

Use Delta Lake's merge method with whenMatchedUpdate and whenNotMatchedInsert. Save and read the Delta table from a temporary path.

4

Display the merged inventory

Use print and show() to display the contents of merged_inventory.

Apache Spark

from pyspark.sql import SparkSession
from delta.tables import DeltaTable

spark = SparkSession.builder.appName('MergeUpsert').config('spark.sql.extensions', 'io.delta.sql.DeltaSparkSessionExtension').config('spark.sql.catalog.spark_catalog', 'org.apache.spark.sql.delta.catalog.DeltaCatalog').getOrCreate()

current_inventory = spark.createDataFrame([
    (1, 'Apple', 50),
    (2, 'Banana', 30),
    (3, 'Carrot', 20)
], ['product_id', 'product_name', 'quantity'])

new_updates = spark.createDataFrame([
    (2, 'Banana', 40),
    (4, 'Dates', 25)
], ['product_id', 'product_name', 'quantity'])

current_inventory.write.format('delta').mode('overwrite').save('/tmp/delta/current_inventory')

deltaTable = DeltaTable.forPath(spark, '/tmp/delta/current_inventory')

deltaTable.alias('current').merge(
    new_updates.alias('updates'),
    'current.product_id = updates.product_id'
).whenMatchedUpdate(set = {
    'product_name': 'updates.product_name',
    'quantity': 'updates.quantity'
}).whenNotMatchedInsert(values = {
    'product_id': 'updates.product_id',
    'product_name': 'updates.product_name',
    'quantity': 'updates.quantity'
}).execute()

merged_inventory = spark.read.format('delta').load('/tmp/delta/current_inventory')

# Print the merged_inventory DataFrame
# Your code here

Hint

Use merged_inventory.show() to display the table. Use print to add a label.

Practice

1. What is the main purpose of a merge (upsert) operation in Apache Spark with Delta Lake?

easy

A. To update existing rows and insert new rows in one step

B. To delete all rows from a table

C. To create a new Spark session

D. To read data from a CSV file

Solution

1. Step 1: Understand the merge operation concept

   Merge (upsert) combines update and insert actions to keep data synchronized.

2. Step 2: Identify the main use case in Spark with Delta Lake

   It updates existing rows and inserts new rows efficiently in one command.

3. Final Answer:

   To update existing rows and insert new rows in one step -> Option A

4. Quick Check:

   Merge = update + insert [OK]

Hint: Merge means update existing and insert new rows together [OK]

Common Mistakes:

• Confusing merge with delete operation
• Thinking merge only inserts data
• Assuming merge creates new tables

2. Which of the following is the correct syntax to start a merge operation in Spark SQL with Delta Lake?

easy

A. deltaTable.select(sourceDF).merge()

B. deltaTable.join(sourceDF).update()

C. deltaTable.insert(sourceDF).update()

D. deltaTable.merge(sourceDF, condition).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

Solution

1. Step 1: Recall the merge syntax in Delta Lake

   The merge starts with deltaTable.merge(sourceDF, condition) followed by update and insert clauses.

2. Step 2: Match the full correct method chain

   Correct chain is whenMatchedUpdateAll(), whenNotMatchedInsertAll(), then execute().

3. Final Answer:

   deltaTable.merge(sourceDF, condition).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute() -> Option D

4. Quick Check:

   Correct merge syntax = deltaTable.merge(sourceDF, condition).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute() [OK]

Hint: Merge uses .merge().whenMatchedUpdateAll().whenNotMatchedInsertAll().execute() [OK]

Common Mistakes:

• Using join instead of merge
• Calling update before merge
• Missing execute() at the end

3. Given the following Spark code snippet, what will be the output count of rows in targetTable after the merge?

val sourceDF = spark.createDataFrame(Seq((1, "A"), (3, "C"))).toDF("id", "value")
val deltaTable = DeltaTable.forName("targetTable")
deltaTable.merge(sourceDF, "target.id = source.id")
  .whenMatchedUpdateExpr(Map("value" -> "source.value"))
  .whenNotMatchedInsertExpr(Map("id" -> "source.id", "value" -> "source.value"))
  .execute()
val count = spark.table("targetTable").count()

Assuming targetTable initially has rows with ids 1 and 2.

medium

A. 2

B. 3

C. 4

D. 1

Solution

1. Step 1: Analyze initial data in targetTable

   targetTable has ids 1 and 2 initially.

2. Step 2: Apply merge logic with sourceDF

   Source has ids 1 and 3. Id 1 matches and updates value. Id 3 does not match and inserts new row.

3. Step 3: Calculate final row count

   Rows after merge: ids 1 (updated), 2 (unchanged), 3 (inserted) -> total 3 rows.

4. Final Answer:

   3 -> Option B

5. Quick Check:

   Initial 2 + 1 inserted = 3 rows [OK]

Hint: Count = initial rows + new inserts after merge [OK]

Common Mistakes:

• Counting only updated rows
• Ignoring inserted rows
• Assuming rows are deleted

4. Identify the error in the following merge operation code snippet:

deltaTable.merge(sourceDF, "target.id = source.id")
  .whenMatchedUpdate(Map("value" -> "source.value"))
  .whenNotMatchedInsert(Map("id" -> "source.id", "value" -> "source.value"))
  .execute()

medium

A. The update and insert methods should be whenMatchedUpdateExpr and whenNotMatchedInsertExpr

B. The condition string should use backticks around column names

C. The execute() method is missing

D. The source DataFrame is not registered as a temp view

Solution

1. Step 1: Check method names for update and insert

   In Delta Lake merge, update and insert with expressions require whenMatchedUpdateExpr and whenNotMatchedInsertExpr.

2. Step 2: Verify other parts of the code

   Condition string and execute() are correct; sourceDF does not need temp view for merge.

3. Final Answer:

   The update and insert methods should be whenMatchedUpdateExpr and whenNotMatchedInsertExpr -> Option A

4. Quick Check:

   Use whenMatchedUpdateExpr and whenNotMatchedInsertExpr [OK]

Hint: Use whenMatchedUpdateExpr and whenNotMatchedInsertExpr for expression maps [OK]

Common Mistakes:

• Using whenMatchedUpdate instead of whenMatchedUpdateExpr
• Forgetting execute()
• Miswriting condition string syntax

5. You have a Delta table users with columns user_id and email. You receive a new DataFrame updatesDF with updated emails and new users. How do you write a merge operation to update emails for existing users and insert new users, ensuring no duplicates and preserving existing users without changes?

hard

A. Use filter to remove duplicates before inserting

B. Use join and then insert only new users

C. Use merge with condition on user_id, update email when matched, insert all columns when not matched

D. Overwrite the entire users table with updatesDF

Solution

1. Step 1: Define merge condition on user_id

   Match rows where users.user_id = updatesDF.user_id to identify existing users.

2. Step 2: Specify update and insert actions

   Update email for matched users, insert all columns for unmatched (new) users.

3. Step 3: Preserve existing users without changes

   Rows not matched remain unchanged automatically in merge.

4. Final Answer:

   Use merge with condition on user_id, update email when matched, insert all columns when not matched -> Option C

5. Quick Check:

   Merge = update matched + insert unmatched [OK]

Hint: Merge on user_id, update email if matched, insert if not [OK]

Common Mistakes:

• Overwriting entire table losing data
• Using join without update logic
• Not handling unmatched rows