Hadoop Hive

How to Update and Delete Hive Tables

With the Hive version 0.14 and above, you can perform the update and delete on the Hive tables. In this post, we are going to see how to perform the update and delete operations in Hive.

But update delete in Hive is not automatic and you will need to enable certain properties to enable ACID operation in Hive.

Update Delete Hive Table

Update and Delete Operations in Hive

If you haven’t enabled the properties in Hive and try to delete a certain record from the Hive table, then you may get following error-

That means the properties are not enabled in your system to perform the update and delete operations in Hive.

Hive update deleteSo, if you are also getting this error, here is the solution to resolve this.

Prerequisite to perform Hive CRUD using ACID operations

Here are some perquisites to perform the update and delete operation on Hive tables. These are the minimum requirements for the CRUD operation using the ACID properties in Hive.

  • The version of Hive should be minimum 0.14 and above
  • File format must be in ORC file format with TBLPROPERTIES(‘transactional’=’true’)
  • Table on which you want to perform the update and delete operation must be CLUSTERED BY with some Buckets
  • Properties (explained below) must be enabled. You can add these properties in Hive-Site.xml for the global changes or on the command like for the session changes.

If you’re fulfilling these requirements, you can go ahead and perform the update delete in hive.

If your table is not bucketed, again you’ll get the following error:

Hive update deleteProperties to enable update and delete operation in Hive

Here are some of the properties you need to add in the hive-site.xml file in order to enable the update and delete in Hive tables.

You need to add these parameters to the hive-site.xml file.

Once done, restart the hive services for the changes to take place.

If you are not comfortable making changes in Hive-site.xml, you can do it from command line also. But when you do from the command line, the changes will be valid for that session only.

To enable these CRUD operation properties in Hive from the command line, you can simply run these on the command line like below-

You need to do this for all the properties like below-

Hive ACID Property

Once done, you are good to perform the update and delete operations on Hive tables.

How to perform the update and delete on Hive tables

By now, we have seen what all need to be done in order to perform the update and delete on Hive tables.

Now, let’s us take an example and show how to do that-

I am creating a normal table in Hive with just 3 columns-

And load some data in that table as shown below-

Hive update deleteOnce done, you need to create the table on which you want to perform the CRUD operations. Make sure the table is bucketed else you won’t be able to perform the update and delete operations.

Hive update deleteNow we have the table HiveTest1 on which we will perform the CRUD operations. But before that, we need to add some data in it. For this, we can take the data of our normal table HiveTest2 which we created above and loaded data.

Hive update deleteAnd you’re done. Now you can easily perform whatever update and delete operations you want to perform.

Update Hive Table

Now let’s say we want to update the above Hive table, we can simply write the command like below-

This will run the complete MapReduce job and you will get the job done as shown below-

Update Hive TableInsert into Hive Table

You can insert a new record also into a hive table as below-

Insert Single Record in Hive Table

Delete operation on Hive table

Similarly, you can perform the delete operation on hive table like below-

Conclusion

These were the ways using which you can perform CRUD operations in Hive. Although update and delete operations in Hive is not much preferred as Hive is majorly meant for the batch operations. But if you still need, you can do so.

Do try this update and delete operations on Hive table and let me know if you will find any problem doing it.

Leave a Comment