sql server - Hive CTE, can I query values as table? -

i'm trying create in-query dimension table want later use in data pull. in ms sql can away cte use values table:

with tbl_test_values (select * (     values           ('number','1','one'),         ('number','5','five'),         ('letter','a','first letter'),         ('human','bob','dude')     )      testvalues --equivalent of table name     (name, value, descript) --essentially field names ) select * actual_data_table f  left outer join tbl_test_values d on f.name=d.name 

is there way replicate above cte "tbl_test_values" in hive, i.e. query custom set of values table?

thanks

1.

inline

with    tbl_test_values         (             select  inline                     (                         array                         (                             struct ('number','1','one')                            ,struct ('number','5','five')                            ,struct ('letter','a','first letter')                            ,struct ('human','bob','dude')                         )                     ) (name, value, descript)         )  select * tbl_test_values ; 

---

+--------+-------+--------------+ |  name  | value |   descript   | +--------+-------+--------------+ | number | 1     | 1          | | number | 5     | 5         | | letter |     | first letter | | human  | bob   | dude         | +--------+-------+--------------+ 

2.

stack

with    tbl_test_values         (             select  stack                     (                         4                        ,'number' ,'1'   ,'one'                        ,'number' ,'5'   ,'five'                        ,'letter' ,'a'   ,'first letter'                        ,'human'  ,'bob' ,'dude'                     ) (name, value, descript)         )  select * tbl_test_values ; 

---

+--------+-------+--------------+ |  name  | value |   descript   | +--------+-------+--------------+ | number | 1     | 1          | | number | 5     | 5         | | letter |     | first letter | | human  | bob   | dude         | +--------+-------+--------------+