Spark SQL中regexp_replace仅显示替换结果，无法更新表列值

Hey there, let’s clear up the core confusion here—neither Hive nor Spark SQL will modify your original table with a simple SELECT statement. What you’re seeing is just a computed result set, not an actual update to the underlying data.

Let’s break down both scenarios: #

• Your Hive misunderstanding
  When you ran select id, regexp_replace(full_name,'A','C') from table in Hive, you weren’t updating the table—you were just viewing a transformed version of the data. The original full_name values in your table stayed exactly the same. If you actually want to modify the table in Hive, you need to use an explicit update or overwrite operation, like:

  -- For ACID-compliant Hive tables (supports row-level updates)
  UPDATE table SET full_name = regexp_replace(full_name,'A','C');
  
  -- Or overwrite the entire table with transformed data (use carefully!)
  INSERT OVERWRITE TABLE table
  SELECT id, regexp_replace(full_name,'A','C') FROM table;
  

• Spark SQL’s behavior makes sense
  In Spark SQL, hiveContext.sql("select id, regexp_replace(full_name,'A','C') from table") creates a temporary DataFrame that holds the transformed results. Calling .show() just prints this temporary data—it never touches your original table. To persist these changes back to the table, you need to explicitly write the DataFrame back, like:

  // Capture the transformed data in a DataFrame
  val updatedDF = hiveContext.sql("select id, regexp_replace(full_name,'A','C') as full_name from table")
  
  // Write back to the original table (choose mode carefully!)
  // "overwrite" replaces the entire table; "append" adds new rows (not ideal here)
  updatedDF.write.mode("overwrite").saveAsTable("table")
  

Key takeaway: #

SELECT statements are read-only operations across both Hive and Spark SQL. They return computed results but never alter the source table. To make permanent changes, you need to use explicit write/update commands tailored to each engine’s capabilities.

内容的提问来源于stack exchange，提问作者Neha