Update , SET option in Hive

by 
INSERT OVERWRITE TABLE _tableName_ PARTITION (_partitionColumn_= _partitionValue_) 
SELECT [other Things], CASE WHEN id=206 THEN 'florida' ELSE location END AS location, [other Other Things] 
FROM _tableName_ WHERE [_whereClause_];

You can have multiple partitions listed by separating them by commas. ... PARTITION (_partitionColumn_= _partitionValue1_, _partitionColumn_= _partitionValue2_, ...). I haven’t done this with multiple partitions, just one at a time, so I’d check the results on a test/dev env before doing all partitions at once. I had other reasons for limiting each OVERWRITE to a single partition as well.

This page https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML has a little more on it.
This site https://cwiki.apache.org/confluence/display/Hive/LanguageManual, in general, is your best friend when working with HiveSQL.

I’ve developed something identical to this to migrate some data and it’s worked. I haven’t tried it against large datasets, only a few GB and it has worked perfectly.

To Note – This will OVERWRITE the partition. It will make previous files go bye-bye. Create backup and restore scripts/procedures.
The [other Things] and [other Other Things] are the rest of the columns from the table. They need to be in the correct order. This is very important or else your data will be corrupted.

Hope this helps. 🙂

More Related Contents:

  1. How to set variables in HIVE scripts
  2. How to create n number of external tables with a single hdfs path using Hive
  3. Create Table in Hive with one file
  4. Specify minimum number of generated files from Hive insert
  5. Hive Explode / Lateral View multiple arrays
  6. java.lang.RuntimeException:Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClient
  7. When creating an external table in hive can I point the location to specific files in a directory?
  8. How to skip CSV header in Hive External Table?
  9. How to update partition metadata in Hive , when partition data is manualy deleted from HDFS
  10. SQL split comma separated row [duplicate]
  11. HIVE select count(*) non null returns higher value than select count(*)
  12. How to create SparkSession with Hive support (fails with “Hive classes are not found”)?
  13. How to control partition size in Spark SQL
  14. What is the difference between partitioning and bucketing a table in Hive ?
  15. How do I output the results of a HiveQL query to CSV?
  16. How to export a Hive table into a CSV file?
  17. Sqoop import : composite primary key and textual primary key
  18. Use collect_list and collect_set in Spark SQL
  19. Hive query performance for high cardinality field
  20. java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
  21. How to convert .txt file to Hadoop’s sequence file format
  22. Find last day of a month in Hive
  23. Overwrite only some partitions in a partitioned spark Dataset
  24. Export as csv in beeline hive
  25. What is Hive: Return Code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask
  26. How can I convert array to string in hive sql?
  27. Hive loading in partitioned table
  28. How do I limit the number of rows per field value in SQL?
  29. Can Hive recursively descend into subdirectories without partitions or editing hive-site.xml?
  30. How do I Combine or Merge Small ORC files into Larger ORC file?

Leave a Comment