8

Suppose that a couple hundred Gigs after starting to use HIVE I want to add a column. From the various articles & pages I have seen, I cannot understand the consequences in terms of

  • storage space required (double ?)
  • blocking (can I still read the table in other processes) ?
  • time (is it quick or as slow as a MysqL change ?)
  • underlying storage (do I need to change all the underlying files ? How can it be done using RCFile ?)

Bonus to whoever can answer the same question on structs in a HIVE column.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Philippe Girolami
  • 1,876
  • 1
  • 13
  • 15

3 Answers3

12

If you add a column to a hive table, only the underlying metastore is updated.

  • The required storage space is not increased as long as you do not add data
  • The change can be made while other processes are accessing the table
  • The change is very quick (only the underlying metastore is updated)
  • You do not have to change the underlying files. Existing records have the value null for the new column

I hope this helps.

Helmut Zechmann
  • 894
  • 8
  • 10
  • Yes, it helps Thanks. Regarding that last point, you're saying if I add a column it's nulled for existing rows. What if I remove a column, I suppose the underlying file becomes out of sync and it's undefined ? And what if storage is RCFile ? – Philippe Girolami Feb 24 '11 at 08:37
  • Removing columns in Hive is not possible (as well as deleting individual rows - you can only delete partitions). See http://wiki.apache.org/hadoop/Hive/LanguageManual/DDL – Martin Konicek Jun 03 '11 at 15:18
  • Also we can not add column in between two columns. Hive allows us to add column after last column only. – Pardeep Sharma May 31 '19 at 02:40
4

ALTER TABLE commands modifies the METADATA only. The underlying data remains untouched. However, it is user's responsibility to ensure that the any alteration does not break the data consistency.

Also any changes to METADATA is applied to the metastore - which is most typically MySQL - in which case the response time is comparable.

Kingz
  • 5,086
  • 3
  • 36
  • 25
  • I got in to this problem. I added a column to existing external table and now when I query tables, I see lot of null values (which before I had some actual values). I think the data became inconsistent after adding column. How to detect these? – Balaji Boggaram Ramanarayan May 26 '16 at 05:47
1

Altering the definition will only modify how the files are read, not the contents of the underlying files.

If your files were tab delimited text with 3 columns, you could create a table that references those files with a scheme like new_table(line STRING) that would read the entire line without parsing out columns based upon the tab characters.

When you add a column, since there are no more delimiters in the record, it will default to NULL, as Helmut mentioned.

Matt Tucker
  • 175
  • 5