- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Hive HQL - static columns and values
- Labels:
-
Apache Hive
Created ‎11-05-2018 05:01 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Created ‎11-06-2018 03:58 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
