CODEWITHSUNDEEP
sql-serverhadoophivecommon-table-expression
Gleb Igorevich
Gleb Igorevich

Reputation: 23

Hive CTE, can I query values as table?

I'm trying to create in-query dimension table that I want to later use in my data pull. In MS SQL I can get away with this CTE which will use values as table:

with tbl_test_values as (select * from (
    VALUES  
        ('Number','1','One'),
        ('Number','5','Five'),
        ('Letter','A','First Letter'),
        ('Human','Bob','Dude')
    ) as 
    TestValues --equivalent of table name
    (Name, Value, Descript) --essentially field names
)
select * from actual_data_table f 
left outer join tbl_test_values d on f.Name=d.Name

Is there a way to replicate the above CTE "tbl_test_values" in Hive, i.e. query custom set of values as table?

Thanks

Upvotes: 1

Views: 2974

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

1.

inline

with    tbl_test_values as
        (
            select  inline
                    (
                        array
                        (
                            struct ('Number','1','One')
                           ,struct ('Number','5','Five')
                           ,struct ('Letter','A','First Letter')
                           ,struct ('Human','Bob','Dude')
                        )
                    ) as (Name, Value, Descript)
        )

select * from tbl_test_values
;

+--------+-------+--------------+
|  name  | value |   descript   |
+--------+-------+--------------+
| Number | 1     | One          |
| Number | 5     | Five         |
| Letter | A     | First Letter |
| Human  | Bob   | Dude         |
+--------+-------+--------------+

2.

stack

with    tbl_test_values as
        (
            select  stack
                    (
                        4
                       ,'Number' ,'1'   ,'One'
                       ,'Number' ,'5'   ,'Five'
                       ,'Letter' ,'A'   ,'First Letter'
                       ,'Human'  ,'Bob' ,'Dude'
                    ) as (Name, Value, Descript)
        )

select * from tbl_test_values
;

+--------+-------+--------------+
|  name  | value |   descript   |
+--------+-------+--------------+
| Number | 1     | One          |
| Number | 5     | Five         |
| Letter | A     | First Letter |
| Human  | Bob   | Dude         |
+--------+-------+--------------+

Upvotes: 5

Related Questions