How can one update data in a hive?

1 year ago
Liam
1 minute

Updating data in Hive typically involves the following steps:

  1. Use the INSERT OVERWRITE statement to write new data into a temporary table.
  2. Insert the data from the temporary table into the original table using the INSERT INTO statement, while specifying the update conditions with a WHERE clause.

For example, let’s say there is a table called “employee” that includes fields for employee ID and name. To update the name of the employee with ID 1 to “John,” you can follow these steps:

  1. Create a temporary table named temp_employee and insert the new data into it.
CREATE TABLE temp_employee AS
SELECT 
  CASE
    WHEN id = 1 THEN id
    ELSE id
  END AS id,
  CASE
    WHEN id = 1 THEN 'John'
    ELSE name
  END AS name
FROM employee;
  1. Insert data from the temporary table into the original table and specify the update conditions.
INSERT INTO employee
SELECT * FROM temp_employee;

This completes the operation of updating the data for employee ID 1. It is important to note that updating data in Hive typically requires creating a temporary table, since data in Hive tables is immutable.

bannerAds