Upserts

What are Upserts?

Upserts are database operations that combine the functionality of updates and inserts. In simpler terms, they allow you to update a record in a table if it already exists or insert a new record if it doesn’t. This eliminates the need for separate checks to see if a record exists before performing the appropriate operation.

Benefits of Upserts

• Efficiency: Upserts save time and effort compared to manually checking for existing records before inserting or updating.
• Simplified Code: Upserts can streamline code by handling both insert and update scenarios in a single operation.
• Data Consistency: Upserts help ensure data consistency by avoiding the possibility of inserting duplicate records.

How Upserts Work

Upserts typically rely on a unique identifier, such as a primary key, to determine whether a record already exists in the table. The upsert operation checks for this identifier. If it finds a match, the existing record is updated with the new data. If no match is found, a new record is inserted.

When to Use Upserts

Upserts are particularly useful in scenarios like:

• Synchronizing data from multiple sources
• Maintaining customer or user information in a database
• Loading data from flat files into a database

Example of upserts:

Consider a scenario where you have a database table ‘users’ with columns ‘id’, ‘username’, and ‘email.’ You want to add a new user if the username doesn’t exist or update the email address if the username already exists. An upsert operation in SQL might look like this:

In this example

• If the username ‘john_doe’ doesn’t exist, a new record is inserted.
• If the username ‘john_doe’ already exists, the email is updated to ‘john.doe@newemail.com’.

While upserts are a powerful tool, it’s important to remember that not all database systems offer a built-in upsert command.

How Apache Hudi Enables Upserts and Inserts

Apache Hudi, an open-source data lake framework, brings functionalities like upserts and inserts for data management in data lakes built on top of storage systems like HDFS or cloud object storage.

Here’s how Hudi achieves upserts:

• Upsert as Default: Hudi’s write operation defaults to upsert behavior. Incoming data is first matched against an index to identify existing records based on a designated key.
• Updates vs. Inserts: Records with matching keys are flagged as updates, and their data is overwritten with the new information. Records without matches are treated as new entries and written as inserts.

Hudi’s upsert and insert operations manage data at a record level, enabling fine-grained updates and insertions. These operations work alongside Hudi’s data management features like file size optimization and versioning for efficient storage and data history.

FAQs