Search code examples
hadoophivehiveqlhue

How to create Fact table in Hive and replace original values in table with key (id) values

What I want to create I explanied below. Is that possible to do in Hive?

I could do that in Python using Pandas and replace over columns, but I was wondering can that be done with query in Hive?

I have uploaded Source table in Hive and created Dimensional tables like below also (in Cloudera HUE), so is it possible to somehow create that Fact table by using Dimensional tables id values and replace values in Source table?

I have my Source table:

SOURCE TABLE

I create Dimensional tables from Source table:

enter image description here enter image description here enter image description here

And I want to create Fact table like this:

enter image description here

Solution

  • Join by values with source table and select IDs:

    insert overwrite table fact
select pr.id as property, t.id as type, pl.id as place, s.price
 from source_table s 
      left join property_dim pr on s.property=pr.property
      left join type_dim     t  on s.type=t.type
      left join place_dim    pl on s.place=pl.place