Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Parse MAP viarable Keys in HIVE


Copy link to this message
-
Re: Parse MAP viarable Keys in HIVE
not quite sure. you're not happy with the suggestion above?

does the below example work any better for you?   ( mytable == the
tablename, myfield == the map column.)

$ hive <<SQL

    select
       count(*) as cnt,
       z.tag

    from
        (
         select tag
         from mytable a
              lateral view explode(map_keys(a.myfield)) b as tag
         ) z

    group by
       z.tag

    order by
       cnt desc
SQL
On Fri, Jul 26, 2013 at 4:02 PM, R J <[EMAIL PROTECTED]> wrote:

> Many thanks to all.
>
> The following command returned records like:
> hive>select  map_keys(myfield) from mytable;
> ["some_key1","some_key2","some_key3"]["some_key1","some_key2","some_key3"]
>
> hive>select explode(arr) as arr from ( select  map_keys(myfield) from
> mytable );
> hive>select explode(arr) as arr ( select  map_keys(myfield) from mytable
> ) ;
>
> Both the above queries give syntax error.
>
> Is there a sample UDF program that could parse a MAP field in hive?
>
>   ------------------------------
>  *From:* Stephen Sprague <[EMAIL PROTECTED]>
> *To:* [EMAIL PROTECTED]
> *Sent:* Thursday, July 25, 2013 2:01 PM
> *Subject:* Re: Parse MAP viarable Keys in HIVE
>
> that sure looks like a nice candidate for a UDF, eh?  Come to think of it
> i'm surprised there isn't a jar file out there in openSource land that
> contains higher order functions already.
>
>
> On Thu, Jul 25, 2013 at 11:20 AM, Arafat, Moiz <[EMAIL PROTECTED]>wrote:
>
>  Try this:
>
>  select
> distinct arr   --- Distinct Values
> from
> (
> select
> explode(arr) as arr     -- Breaks Array in every column, as separate row .
> (
> select
> map_keys(KV) as arr         -- Returns an array of the Key Names for every
> row .
> from
> <table Name>
> )a
> ) c
> order by arr;
>
>  Thanks,
> Moiz
>
>
>  On Jul 25, 2013, at 2:08 PM, R J <[EMAIL PROTECTED]> wrote:
>
>   Hi All,
>
>  I have a table in Apache Hive and I am interested in a specific field of
> the table. Table name: Table1 and field name f1. The field f1 is of type
> MAP<string, string>.
>
> The table is huge. How could I find the distinct key values of the MAP
> variable f1 above?
>
> I tried:
> $hive -e "select distinct map_keys(f1) from Table1;"
> $hive -e "select k from Table1 LATERAL VIEW explode(f1) et as k,v;"
>
> Each of the above returned syntax error. Could you please help.
>
> If the existing values for a few fields of the table ate:
> <x, y>
> <x, p>
> <m, n>
> <empty>
> <m, m>
> <p, q>
>
> I want the return to be:
> x
> m
> p
>
> Thanks a lot.
>
>
>
>
>
>
>
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB