Hive Functions Cheat-sheet, by Qubole How to create and use Hive Functions, Listing of Built-In Functions that are supported in Hive Hive Function Meta commands SHOW FUNCTIONS– lists Hive functions and operators DESCRIBE FUNCTION [function name]– displays short description of the function DESCRIBE FUNCTION EXTENDED [function name]– access extended description of the function Types of Hive Functions UDF– is a function that takes one or more columns from a row as argument and returns a single value or object. Eg: concat(col1, col2) UDAF- aggregates column values in multiple rows and returns a single value. Eg: sum(c1) UDTF— takes zero or more inputs and and produces multiple columns or rows of output. Eg: explode() Macros— a function that users other Hive functions. How To Develop UDFs package org.apache.hadoop.hive.contrib.udf.example; import java.util.Date; import java.text.SimpleDateFormat; import org.apache.hadoop.hive.ql.exec.UDF; @Description(name = "YourUDFName", value = "_FUNC_(InputDataType) - using the input datatype X argument, "+ "returns YYY.", extended = "Example:\n" + " > SELECT _FUNC_(InputDataType) FROM tablename;") public class YourUDFName extends UDF{ .. public YourUDFName( InputDataType InputValue ){ ..; } public String evaluate( InputDataType InputValue ){ ..; } } How To Develop UDFs, GenericUDFs, UDAFs, and UDTFs Mathematical Functions Return Type Name (Signature) Description BIGINT DOUBLE BIGINT BIGINT double round(double a) round(double a, int d) floor(double a) ceil(double a), ceiling(double a) rand(), rand(int seed) double double double double double double double string string exp(double a) ln(double a) log10(double a) log2(double a) log(double base, double a) pow(double a, double p), power(double a, double p) sqrt(double a) bin(BIGINT a) hex(BIGINT a) hex(string a) string unhex(string a) string conv(BIGINT num, int from_base, int to_base), conv(STRING num, int from_base, int to_base) abs(double a) pmod(int a, int b) pmod(doub

阅读全文