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.
