Hive中UDF、UDAF和UDTF使用

1.Hive中的内置函數

org.apache.hadoop.hive.ql.exec.FunctionRegistry類中定義了Hive目前内置的自定義函數

registerGenericUDF("concat", GenericUDFConcat.class);
    registerUDF("substr", UDFSubstr.class, false);
    registerUDF("substring", UDFSubstr.class, false);
    registerUDF("space", UDFSpace.class, false);
    registerUDF("repeat", UDFRepeat.class, false);
    registerUDF("ascii", UDFAscii.class, false);
    registerGenericUDF("lpad", GenericUDFLpad.class);
    registerGenericUDF("rpad", GenericUDFRpad.class);

    registerUDF("ln", UDFLn.class, false);
    registerUDF("log2", UDFLog2.class, false);
    registerUDF("sin", UDFSin.class, false);
    registerUDF("asin", UDFAsin.class, false);
    registerUDF("cos", UDFCos.class, false);
    registerUDF("acos", UDFAcos.class, false);
    registerUDF("log10", UDFLog10.class, false);
    registerUDF("log", UDFLog.class, false);
    registerUDF("exp", UDFExp.class, false);
    registerGenericUDF("power", GenericUDFPower.class);
    registerGenericUDF("pow", GenericUDFPower.class);
    registerUDF("sign", UDFSign.class, false);
    registerUDF("pi", UDFPI.class, false);
    registerUDF("degrees", UDFDegrees.class, false);
    registerUDF("radians", UDFRadians.class, false);
    registerUDF("atan", UDFAtan.class, false);
    registerUDF("tan", UDFTan.class, false);
    registerUDF("e", UDFE.class, false);

    registerUDF("conv", UDFConv.class, false);
    registerUDF("bin", UDFBin.class, false);
    registerUDF("hex", UDFHex.class, false);
    registerUDF("unhex", UDFUnhex.class, false);
    registerUDF("base64", UDFBase64.class, false);
    registerUDF("unbase64", UDFUnbase64.class, false);

    registerGenericUDF("encode", GenericUDFEncode.class);
    registerGenericUDF("decode", GenericUDFDecode.class);

    registerGenericUDF("upper", GenericUDFUpper.class);
    registerGenericUDF("lower", GenericUDFLower.class);
    registerGenericUDF("ucase", GenericUDFUpper.class);
    registerGenericUDF("lcase", GenericUDFLower.class);
    registerGenericUDF("trim", GenericUDFTrim.class);
    registerGenericUDF("ltrim", GenericUDFLTrim.class);
    registerGenericUDF("rtrim", GenericUDFRTrim.class);
    registerUDF("length", UDFLength.class, false);
    registerUDF("reverse", UDFReverse.class, false);
    registerGenericUDF("field", GenericUDFField.class);
    registerUDF("find_in_set", UDFFindInSet.class, false);

    registerUDF("like", UDFLike.class, true);
    registerUDF("rlike", UDFRegExp.class, true);
    registerUDF("regexp", UDFRegExp.class, true);
    registerUDF("regexp_replace", UDFRegExpReplace.class, false);
    registerUDF("regexp_extract", UDFRegExpExtract.class, false);
    registerUDF("parse_url", UDFParseUrl.class, false);
    registerGenericUDF("nvl", GenericUDFNvl.class);
    registerGenericUDF("split", GenericUDFSplit.class);
    registerGenericUDF("str_to_map", GenericUDFStringToMap.class);
    registerGenericUDF("translate", GenericUDFTranslate.class);

    registerGenericUDF("date_add", GenericUDFDateAdd.class);
    registerGenericUDF("date_sub", GenericUDFDateSub.class);
    registerGenericUDF("datediff", GenericUDFDateDiff.class);

    registerUDF("get_json_object", UDFJson.class, false);

           

2.UDF

Hive的UDF開發隻需要重構UDF類的evaluate函數即可。例：

package hive.connect;

import org.apache.hadoop.hive.ql.exec.UDF;

public final class Add extends UDF {
public Integer evaluate(Integer a, Integer b) {
               if (null == a || null == b) {
                               return null;
               } return a + b;
}

public Double evaluate(Double a, Double b) {
               if (a == null || b == null)
                               return null;
                               return a + b;
               }

public Integer evaluate(Integer... a) {
               int total = 0;
               for (int i = 0; i < a.length; i++)
                               if (a[i] != null)
                                             total += a[i];
                                              return total;
                               }
}
           

3.UDAF

1、一下兩個包是必須的import org.apache.hadoop.hive.ql.exec.UDAF和 org.apache.hadoop.hive.ql.exec.UDAFEvaluator。

2、函數類需要繼承UDAF類，内部類Evaluator實UDAFEvaluator接口。

3、Evaluator需要實作 init、iterate、terminatePartial、merge、terminate這幾個函數。

a）init函數實作接口UDAFEvaluator的init函數。

b）iterate接收傳入的參數，并進行内部的輪轉。其傳回類型為boolean。

c）terminatePartial無參數，其為iterate函數輪轉結束後，傳回輪轉資料，terminatePartial類似于hadoop的Combiner。

d）merge接收terminatePartial的傳回結果，進行資料merge操作，其傳回類型為boolean。

e）terminate傳回最終的聚集函數結果。

package hive.udaf;

import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;
public class Avg extends UDAF {
         public static class AvgState {
         private long mCount;
         private double mSum;
}

public static class AvgEvaluator implements UDAFEvaluator {
         AvgState state;
         public AvgEvaluator() {
                   super();
                   state = new AvgState();
                   init();
}

/** * init函數類似于構造函數，用于UDAF的初始化 */

public void init() {
         state.mSum = 0;
         state.mCount = 0;
}

/** * iterate接收傳入的參數，并進行内部的輪轉。其傳回類型為boolean * * @param o * @return */

public boolean iterate(Double o) {
         if (o != null) {
                   state.mSum += o;
                   state.mCount++;
         } return true;
}

/** * terminatePartial無參數，其為iterate函數輪轉結束後，傳回輪轉資料， * terminatePartial類似于hadoop的Combiner * * @return */

public AvgState terminatePartial() {
         // combiner
         return state.mCount == 0 ? null : state;
}

/** * merge接收terminatePartial的傳回結果，進行資料merge操作，其傳回類型為boolean * * @param o * @return */

public boolean merge(Double o) {                
         if (o != null) {
                   state.mCount += o.mCount;
                   state.mSum += o.mSum;
         }

         return true;
}

/** * terminate傳回最終的聚集函數結果 * * @return */

public Double terminate() {
         return state.mCount == 0 ? null : Double.valueOf(state.mSum / state.mCount);
}

}
           

4.UDTF

(1) 繼承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF。

 (2)實作initialize, process, close三個方法。

UDTF首先會調用initialize方法，此方法傳回UDTF的傳回行的資訊（傳回個數，類型）。初始化完成後，會調用process方法，對傳入的參數進行處理，可以通過forword()方法把結果傳回。最後close()方法調用，對需要清理的方法進行清理。

下面是我寫的一個用來切分”key:value;key:value;”這種字元串，傳回結果為key, value兩個字段。供參考：

import java.util.ArrayList;
   
    import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
    import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
    import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
    import org.apache.hadoop.hive.ql.metadata.HiveException;
    import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
    import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
    import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
   import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
  
   public class ExplodeMap extends GenericUDTF{
  
       @Override
       public void close() throws HiveException {
           // TODO Auto-generated method stub    
       }
  
       @Override
       public StructObjectInspector initialize(ObjectInspector[] args)
               throws UDFArgumentException {
           if (args.length != 1) {
               throw new UDFArgumentLengthException("ExplodeMap takes only one argument");
           }
           if (args[0].getCategory() != ObjectInspector.Category.PRIMITIVE) {
               throw new UDFArgumentException("ExplodeMap takes string as a parameter");
           }
  
           ArrayList<String> fieldNames = new ArrayList<String>();
           ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
           fieldNames.add("col1");
           fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
           fieldNames.add("col2");
           fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
  
           return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames,fieldOIs);
       }
  
      @Override
       public void process(Object[] args) throws HiveException {
           String input = args[0].toString();
           String[] test = input.split(";");
           for(int i=0; i<test.length; i++) {
               try {
                   String[] result = test[i].split(":");
                   forward(result);
               } catch (Exception e) {
                  continue;
              }
         }
       }
   }