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 >> aliases for histogram_numeric (a UDTF)


Copy link to this message
-
aliases for histogram_numeric (a UDTF)
I'm trying to use histogram_numeric(), and output the results as a table with an x and y column, one row per bin.

SELECT histogram_numeric(freight, 10) from flights
produces an array of JSON objects:
[{"x":25702.200152788293,"y":79849},{"x":2287471.0415704413,"y":866},{"x":5190680.02686567,"y":335},{"x":8346807.222222222,"y":18},{"x":10504284.416666666,"y":12},{"x":12534902.199999997,"y":15},{"x":17483294.5,"y":4},{"x":20163190,"y":1},{"x":27849459,"y":2},{"x":30078666,"y":1}]

select explode(histogram_numeric(freight, 10)) as foo from flights
produces ten rows of JSON objects:
{"x":25702.200152788293,"y":79849}

{"x":2287471.0415704413,"y":866}

{"x":5190680.02686567,"y":335}
...

select inline(histogram_numeric(freight, 10)) as foo from flights
gets the error "FAILED: SemanticException [Error 10083]: The number of aliases supplied in the AS clause does not match the number of columns output by the UDTF expected 2 aliases but got 1"
which is not unreasonable, since there should be two columns of output

However
select inline(histogram_numeric(freight, 10)) as foo,bar from flights
gets the error "FAILED: SemanticException 1:53 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'bar'"

and
select inline(histogram_numeric(freight, 10)) as [foo,bar] from flights
gets the error "FAILED: ParseException line 1:46 mismatched input 'as' expecting FROM near ')' in from clause"

and
select inline(histogram_numeric(freight, 10)) as (foo,bar) from flights
gets the error "java.lang.Exception: Total MapReduce CPU Time Spent: 6 seconds 30 msec: Job 0: Map: 1 Reduce: 1 Cumulative CPU: 6.03 sec HDFS Read: 17108227 HDFS Write: 0 FAIL"
Is there a syntax to do this, or do I need to use LATERAL VIEW syntax? (If so, is there a clearer explanation of LATERAL VIEW than https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView  ?)

DISCLAIMER
=========This e-mail may contain privileged and confidential information which is the property of Persistent Systems Ltd. It is intended only for the use of the individual or entity to which it is addressed. If you are not the intended recipient, you are not authorized to read, retain, copy, print, distribute or use this message. If you have received this communication in error, please notify the sender and delete all copies of this message. Persistent Systems Ltd. does not accept any liability for virus infected mails.

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