Clever Geek Handbook

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

I can create a hive table from data in an external file. Now I want to create another table from the data of the previous table with additional columns with a default value.

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

+3


source to share


3 answers


You can specify which columns to select from the table when creating / updating. Just provide the default as one of the columns. Example with UPDATE below:

Creating a simple table and populating it with a 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)

Resolution of dynamic partitions:

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


Creating a second table:

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

Populating it from table 1 with the 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)
+4


source


I was looking for a solution for this too:



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


source


In hive 3.0, we can create a table with "default constrain on column", in the insert statement, if we do not specify the column value, the default value will be inserted. See https://issues.apache.org/jira/browse/HIVE-18726 and https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=75969407 for details . Thank you.

0


source






More articles:


All Articles