You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by xu ji <ji...@gmail.com> on 2012/11/30 19:18:39 UTC
Any existing UDTF to flatten map
Hi all,
I have a table like this:
hive> desc mytable;
ts bigint
content map<string,string>
hive> select * from mytable;
1354299050 {"F1":"id-1"}
1354299040 {"F1":"id-2","F2":"id-3"}
1354299030 {"F1":"id-3","F2":"id-1","F3":"id-4"}
Does anyone know how to generate a table like this:
hive> desc resulttable;
id string
ts bigint
hive> select * from resulttable;
id-1 1354299050
id-2 1354299040
id-3 1354299040
id-3 1354299030
id-1 1354299030
id-4 1354299030
I mean I can certainly write a UDTF to generate a table like this, but I
wonder is there an existing UDTF to do this job?
Thanks,
Xu
--
Ji Xu
Mobile: (+86) 138-115-10101
E-mail: jixuxjixu@gmail.com
Homepage: http://about.me/jixu
Room 825, School of Software,
Main Building, Tsinghua University,
Beijing, 100084, P. R. China.
Re: Any existing UDTF to flatten map
Posted by Matt Tucker <ma...@gmail.com>.
I ended up getting an error (Hive 0.7.1), but I would have thought
something like the following would work:
SELECT
user_id,
obj_key,
obj[obj_key] AS obj_item
FROM (
SELECT
"user1" user_id,
MAP("k1", "v1", "k2", "v2") obj
FROM calendar
LIMIT 1
) tmp
LATERAL VIEW EXPLODE(MAP_KEYS(obj)) obj_tbl AS obj_key;
There error that I get for this is: FAILED: Error in semantic analysis:
Line 3:4 Non-constant expression for map indexes not supported obj_key
Matt
On Fri, Nov 30, 2012 at 1:18 PM, xu ji <ji...@gmail.com> wrote:
> Hi all,
> I have a table like this:
> hive> desc mytable;
> ts bigint
> content map<string,string>
> hive> select * from mytable;
> 1354299050 {"F1":"id-1"}
> 1354299040 {"F1":"id-2","F2":"id-3"}
> 1354299030 {"F1":"id-3","F2":"id-1","F3":"id-4"}
>
> Does anyone know how to generate a table like this:
>
> hive> desc resulttable;
> id string
> ts bigint
> hive> select * from resulttable;
> id-1 1354299050
> id-2 1354299040
> id-3 1354299040
> id-3 1354299030
> id-1 1354299030
> id-4 1354299030
>
>
> I mean I can certainly write a UDTF to generate a table like this, but I
> wonder is there an existing UDTF to do this job?
>
> Thanks,
> Xu
>
> --
> Ji Xu
> Mobile: (+86) 138-115-10101
> E-mail: jixuxjixu@gmail.com
> Homepage: http://about.me/jixu
>
> Room 825, School of Software,
> Main Building, Tsinghua University,
> Beijing, 100084, P. R. China.
>
>