Support Questions

Find answers, ask questions, and share your expertise

Hive HQL - static columns and values

Explorer

I have below query :

select c1 as "col1", c2 as "col2", c3 as "col3", c4 as "col4", "xxx" as "col5" from tbl1

"xxx" represents a static value to be shown in the query for all resultant rows with a column name of "col5".

But hive is giving error. How do i correctly code this?

BTW we need this to use in a "union" clause with tables with differing columns.

Appreciate the feedback.

1 REPLY 1

Super Guru

@n c

Make sure you are having same number of columns and datatypes of both sides of union are same.

Union:

Eliminates duplicates from the result.

Ex:

hive> select "1" id,"2" age from (select 1)t 
	union 
      select "1" id,"2" age from(select 1) t;
+---------+----------+--+
| _u2.id  | _u2.age  |
+---------+----------+--+
| 1       | 2        |
+---------+----------+--+

as i'm having 1,2 as result but in the result we are having only one row as hive eliminated duplicates.

UnionAll:

Shows all duplicates also.

hive> select "1" id,"2" age from (select 1)t 
	union all 
      select "1" id,"2" age from(select 1) t;
+---------+----------+--+
| _u1.id  | _u1.age  |
+---------+----------+--+
| 1       | 2        |
| 1       | 2        |
+---------+----------+--+

In both cases we need to have same number of columns/datatypes while performing union or union all operations.

If you don't have same number of columns/datatypes from different tables then use null value to match number of columns on both sides.

Ex:

hive> select "1" id,"2" age from (select 1)t 
	union 
      select "1",null  from(select 1) t;
+---------+----------+--+
| _u2.id  | _u2.age  |
+---------+----------+--+
| 1       | NULL     |
| 1       | 2        |
+---------+----------+--+

in the above example i have used null for age column and result we going to have 2 columns.

Refer to this link for more details regards to union and union all operators.