Adding a default value to a column while creating table in hive

Question

I'm able to create a hive table from data in external file. Now I wish to create another table from data in previous table with additional columns with default value.

I understand that CREATE TABLE AS SELECT can be used but how do I add additional columns with default value?

Answer 1

You could specify which columns to select from table on create/update. Simply provide default value as one of columns. Example with UPDATE is below:

Creating simple table and populating it with value:

hive> create table table1(col1 string);
hive> insert into table table1 values('val1');
hive> select col1 from table1;
OK
val1
Time taken: 0.087 seconds, Fetched: 1 row(s)

Allowing dynamic partitions:

hive> SET hive.exec.dynamic.partition.mode=nonstrict;

Creating second table:

hive> create table table2(col1 string, col2 string);

Populating it from table1 with default value:

hive> insert overwrite table table2 select col1, 'DEFAULT' from table1;
hive> select * from table2;
OK
val1    DEFAULT
Time taken: 0.081 seconds, Fetched: 1 row(s)

Answer 2

I've been looking for a solution for this too and came up with this:

CREATE TABLE test_table AS SELECT
 CASE 
  WHEN TRUE
  THEN "desired_value" 
 END AS default_column_name;