Query Map values in Hive

Question

I have a table in hive which is updated every hour by Spark/Parquet

CREATE TABLE IF NOT EXISTS user
(
    name STRING,
    creation_date DATE,
    cards map<STRING,STRING>
) STORED AS PARQUET ; 

Let's suppose that I want to query the number of Gobelin cards per user.

My query looks like this:

 select * from user where card["Gobelin"] IS NOT NULL ;

The result looks like this

KillerZord1001      2016-01-02      {"Archer":"2","Gobelin":"6"}
HalfAMill       2016-02-05      {"Witch":"7","Gobelin":"8"}

But what I would like to have is the value of the key that I am looking for, more like:

KillerZord1001      2016-01-02      6
HalfAMill       2016-02-05      8

Can Hive perform such queries?

Answer 1

You can simply do

SELECT name, creation_date, card["Gobelin"] FROM USER 
  WHERE card["Gobelin"] IS NOT NULL