Yes, the UDTF explode() is probably the easiest way to get multiple rows from an array. Another way would be to write your own custom script and use the transform clause. To generate the index of the info, you'll need the field() UDF, but there is currently an issue with it:
https://issues.apache.org/jira/browse/HIVE-1385 From: Viraj Bhat [mailto:[email protected]] Sent: Tuesday, June 29, 2010 2:06 PM To: [email protected] Subject: Best way to create a view with index from explode ? Hi all, I have an input table which has 3 columns one of which is an array list, bcookie, id, info [Schema: string, string, arraylist<map<string,string>>] Here is a sample row 1245, 1, {[myid#id2, myage#100], [myid#id3, myage#101], [myid#id3, myage#102]} I wanted to explode the arraylist column and create an additional column which represents the index of the info bcookie, id, info, index 1245, 1, [myid#id2, myage#100], 0 1245, 1, [myid#id3, myage#101], 1 1245, 1, [myid#id3, myage#102] , 2 I was thinking of the lines of: SELECT * FROM srctable LATERAL VIEW explode(info)... ; Is UDTF the only way to achieve this? Thanks Viraj
