You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Chalcy Raja <Ch...@careerbuilder.com> on 2011/11/23 16:48:07 UTC
hive map field question
Hello HiveUsers,
I have a need to convert a map<string,string> field to string field and vice versa in a hive table. I could not do cast.
I created two external tables with one has string and another map. I can join both to get what I want, takes a long time.
Any ideas of how it can be done efficiently ?
Thanks,
Chalcy
RE: hive map field question
Posted by Chalcy Raja <Ch...@careerbuilder.com>.
Wow, this is great news. I read about explode and wished map had that.
Thanks so much,
Chalcy
From: Bejoy Ks [mailto:bejoy_ks@yahoo.com]
Sent: Wednesday, November 23, 2011 1:05 PM
To: user@hive.apache.org
Subject: Re: hive map field question
Hi Chalcy
Support for splitting a column of map data type into key and values is available on the hive latest trunk. You can use a explode() UDF to achieve the same, which used to support only arrays previously.
http://svn.apache.org/viewvc/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDTFExplode.java?view=markup
You need to patch your hive source installation to get this functionality running. Or as a quick fix (not highly recommended) take the source code include the same as a Custom UDF for your temporary usage. You can utilize the explode functionality as follows combining the same with Lateral Views.
Sample DDL
CREATE TABLE test_map
(
clmn1 STRING,
clmn2 STRING,
clmnMap MAP<STRING,STRING>
)
Sample DML - to extraxt key values from map
SELECT clmn1,clmn2,clmnMap_key,clmnMap_value FROM test_map
LATERAL VIEW explode(clmnMap) cl AS clmnMap_key,clmnMap_value;
Hope it helps!...
Regards
Bejoy.K.S
________________________________
From: Mark Grover <mg...@oanda.com>
To: user@hive.apache.org
Sent: Wednesday, November 23, 2011 9:55 PM
Subject: Re: hive map field question
Chalcy,
I don't know of an existing UDF that lists all keys and values of a map. You may want to check ql/src/java/org/apache/hadoop/hive/ql/udf in Hive source to double check.
But, as you might have guessed, it'd be pretty straightforward to do that in your own UDF:-)
Mark
----- Original Message -----
From: "Chalcy Raja" <Ch...@careerbuilder.com>>
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Sent: Wednesday, November 23, 2011 11:14:09 AM
Subject: RE: hive map field question
Thanks, Mark. I'll try that.
Also, is there a way to list all the keys and values in a map field?
Thanks,
Chalcy
-----Original Message-----
From: Mark Grover [mailto:mgrover@oanda.com<ma...@oanda.com>]
Sent: Wednesday, November 23, 2011 11:01 AM
To: user@hive.apache.org<ma...@hive.apache.org>
Subject: Re: hive map field question
Hi Chalcy,
You could write a UDF that takes in a Map object and returns you the string you need.
Alternatively, you could use Hive Transform. Details here:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Transform
When a map gets passed to your custom transform script, you could read it as a serialized string.
Mark
----- Original Message -----
From: "Chalcy Raja" <Ch...@careerbuilder.com>>
To: user@hive.apache.org<ma...@hive.apache.org>
Sent: Wednesday, November 23, 2011 10:48:07 AM
Subject: hive map field question
Hello HiveUsers,
I have a need to convert a map<string,string> field to string field and vice versa in a hive table. I could not do cast.
I created two external tables with one has string and another map. I can join both to get what I want, takes a long time.
Any ideas of how it can be done efficiently ?
Thanks,
Chalcy
Re: hive map field question
Posted by Bejoy Ks <be...@yahoo.com>.
Hi Chalcy
Support for splitting a column of map data type into key and values is available on the hive latest trunk. You can use a explode() UDF to achieve the same, which used to support only arrays previously.
http://svn.apache.org/viewvc/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDTFExplode.java?view=markup
You need to patch your hive source installation to get this functionality running. Or as a quick fix (not highly recommended) take the source code include the same as a Custom UDF for your temporary usage. You can utilize the explode functionality as follows combining the same with Lateral Views.
Sample DDL
CREATE TABLE test_map
(
clmn1 STRING,
clmn2 STRING,
clmnMap MAP<STRING,STRING>
)
Sample DML - to extraxt key values from map
SELECT clmn1,clmn2,clmnMap_key,clmnMap_value FROM test_map
LATERAL VIEW explode(clmnMap) cl AS clmnMap_key,clmnMap_value;
Hope it helps!...
Regards
Bejoy.K.S
________________________________
From: Mark Grover <mg...@oanda.com>
To: user@hive.apache.org
Sent: Wednesday, November 23, 2011 9:55 PM
Subject: Re: hive map field question
Chalcy,
I don't know of an existing UDF that lists all keys and values of a map. You may want to check ql/src/java/org/apache/hadoop/hive/ql/udf in Hive source to double check.
But, as you might have guessed, it'd be pretty straightforward to do that in your own UDF:-)
Mark
----- Original Message -----
From: "Chalcy Raja" <Ch...@careerbuilder.com>
To: "user@hive.apache.org" <us...@hive.apache.org>
Sent: Wednesday, November 23, 2011 11:14:09 AM
Subject: RE: hive map field question
Thanks, Mark. I'll try that.
Also, is there a way to list all the keys and values in a map field?
Thanks,
Chalcy
-----Original Message-----
From: Mark Grover [mailto:mgrover@oanda.com]
Sent: Wednesday, November 23, 2011 11:01 AM
To: user@hive.apache.org
Subject: Re: hive map field question
Hi Chalcy,
You could write a UDF that takes in a Map object and returns you the string you need.
Alternatively, you could use Hive Transform. Details here:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Transform
When a map gets passed to your custom transform script, you could read it as a serialized string.
Mark
----- Original Message -----
From: "Chalcy Raja" <Ch...@careerbuilder.com>
To: user@hive.apache.org
Sent: Wednesday, November 23, 2011 10:48:07 AM
Subject: hive map field question
Hello HiveUsers,
I have a need to convert a map<string,string> field to string field and vice versa in a hive table. I could not do cast.
I created two external tables with one has string and another map. I can join both to get what I want, takes a long time.
Any ideas of how it can be done efficiently ?
Thanks,
Chalcy
Re: hive map field question
Posted by Mark Grover <mg...@oanda.com>.
Chalcy,
I don't know of an existing UDF that lists all keys and values of a map. You may want to check ql/src/java/org/apache/hadoop/hive/ql/udf in Hive source to double check.
But, as you might have guessed, it'd be pretty straightforward to do that in your own UDF:-)
Mark
----- Original Message -----
From: "Chalcy Raja" <Ch...@careerbuilder.com>
To: "user@hive.apache.org" <us...@hive.apache.org>
Sent: Wednesday, November 23, 2011 11:14:09 AM
Subject: RE: hive map field question
Thanks, Mark. I'll try that.
Also, is there a way to list all the keys and values in a map field?
Thanks,
Chalcy
-----Original Message-----
From: Mark Grover [mailto:mgrover@oanda.com]
Sent: Wednesday, November 23, 2011 11:01 AM
To: user@hive.apache.org
Subject: Re: hive map field question
Hi Chalcy,
You could write a UDF that takes in a Map object and returns you the string you need.
Alternatively, you could use Hive Transform. Details here:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Transform
When a map gets passed to your custom transform script, you could read it as a serialized string.
Mark
----- Original Message -----
From: "Chalcy Raja" <Ch...@careerbuilder.com>
To: user@hive.apache.org
Sent: Wednesday, November 23, 2011 10:48:07 AM
Subject: hive map field question
Hello HiveUsers,
I have a need to convert a map<string,string> field to string field and vice versa in a hive table. I could not do cast.
I created two external tables with one has string and another map. I can join both to get what I want, takes a long time.
Any ideas of how it can be done efficiently ?
Thanks,
Chalcy
RE: hive map field question
Posted by Chalcy Raja <Ch...@careerbuilder.com>.
Thanks, Mark. I'll try that.
Also, is there a way to list all the keys and values in a map field?
Thanks,
Chalcy
-----Original Message-----
From: Mark Grover [mailto:mgrover@oanda.com]
Sent: Wednesday, November 23, 2011 11:01 AM
To: user@hive.apache.org
Subject: Re: hive map field question
Hi Chalcy,
You could write a UDF that takes in a Map object and returns you the string you need.
Alternatively, you could use Hive Transform. Details here:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Transform
When a map gets passed to your custom transform script, you could read it as a serialized string.
Mark
----- Original Message -----
From: "Chalcy Raja" <Ch...@careerbuilder.com>
To: user@hive.apache.org
Sent: Wednesday, November 23, 2011 10:48:07 AM
Subject: hive map field question
Hello HiveUsers,
I have a need to convert a map<string,string> field to string field and vice versa in a hive table. I could not do cast.
I created two external tables with one has string and another map. I can join both to get what I want, takes a long time.
Any ideas of how it can be done efficiently ?
Thanks,
Chalcy
Re: hive map field question
Posted by Mark Grover <mg...@oanda.com>.
Hi Chalcy,
You could write a UDF that takes in a Map object and returns you the string you need.
Alternatively, you could use Hive Transform. Details here:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Transform
When a map gets passed to your custom transform script, you could read it as a serialized string.
Mark
----- Original Message -----
From: "Chalcy Raja" <Ch...@careerbuilder.com>
To: user@hive.apache.org
Sent: Wednesday, November 23, 2011 10:48:07 AM
Subject: hive map field question
Hello HiveUsers,
I have a need to convert a map<string,string> field to string field and vice versa in a hive table. I could not do cast.
I created two external tables with one has string and another map. I can join both to get what I want, takes a long time.
Any ideas of how it can be done efficiently ?
Thanks,
Chalcy