Add a column in a table in HIVE QL

Question

I'm writing a code in HIVE to create a table consisting of 1300 rows and 6 columns:

create table test1 as SELECT cd_screen_function,
     SUM(access_count) AS max_count,
     MIN(response_time_min) as response_time_min,
     AVG(response_time_avg) as response_time_avg,
     MAX(response_time_max) as response_time_max,
     SUM(response_time_tot) as response_time_tot,
     COUNT(*) as row_count
     FROM sheet WHERE  ts_update BETWEEN unix_timestamp('2012-11-01 00:00:00') AND 
     unix_timestamp('2012-11-30 00:00:00') and cd_office = '016'
     GROUP BY cd_screen_function ORDER BY max_count DESC, cd_screen_function;

Now I want to add another column as access_count1 which consists one unique value for all 1300 rows and value will be sum(max_count). max_count is a column in my existing table. How I can do that? I am trying to alter the table by this code ALTER TABLE test1 ADD COLUMNS (access_count1 int) set default sum(max_count);

Determined

Answer 1

You cannot add a column with a default value in Hive. You have the right syntax for adding the column ALTER TABLE test1 ADD COLUMNS (access_count1 int);, you just need to get rid of default sum(max_count). No changes to that files backing your table will happen as a result of adding the column. Hive handles the "missing" data by interpreting NULL as the value for every cell in that column.

So now your have the problem of needing to populate the column. Unfortunately in Hive you essentially need to rewrite the whole table, this time with the column populated. It may be easier to rerun your original query with the new column. Or you could add the column to the table you have now, then select all of its columns plus value for the new column.

You also have the option to always COALESCE the column to your desired default and leave it NULL for now. This option fails when you want NULL to have a meaning distinct from your desired default. It also requires you to depend on always remembering to COALESCE.

If you are very confident in your abilities to deal with the files backing Hive, you could also directly alter them to add your default. In general I would recommend against this because most of the time it will be slower and more dangerous. There might be some case where it makes sense though, so I've included this option for completeness.

Shankle

How does this work if I want to add multiple columns? – Treenware

ALTER TABLE test1 ADD COLUMNS (access_count1 int,access_count2 date,access_count3 string, ...); – Earpiece

Does the order matter in which we add the column ? – Inapt

Column order does not matter unless you plan on editing the files backing your data directly (not recommended). – Shankle

Suppose I have 1 Million record set, If I add column does it take more performance and time to add in Hive QL? Since MySQL takes more time and performance to achieve this.. Also What happen if I use Partitioned column in Hive table? – Wyoming

This might help #30034024 – Paregoric