蜂巢功能：添加月份至今 [英] Hive function: add month to date

问题描述

例如：

_p add_month（'2014-01-01'，1） - >'2014-02-01'

add_month（'2013-12-01' 1） - >'2014-01-01'

解决方案

您可以创建一个通用UDF（ GenericUDF ）。

这是一个示例UDF：

  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.ql.udf.generic.GenericUDF; 
 import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; 
 import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorUtils; 
 import org.apache.hadoop.hive.serde2.objectinspector.primitive.IntObjectInspector; 
 import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory; 
 import org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector; 
 import org.joda.time.LocalDate; 
 import org.joda.time.format.DateTimeFormat; 
 import org.apache.hadoop.io.IntWritable; 
 import org.apache.hadoop.io.Text; 
 
 public class AddMonth extends GenericUDF {
 
 @Override 
 public ObjectInspector initialize（ObjectInspector [] arguments）throws UDFArgumentException {
 if（arguments.length！ = 2）{
 throw new UDFArgumentLengthException（函数add_month（local_date，months_to_add）需要2个参数。）; 
} 
 
 ObjectInspector localDateVal = arguments [0]; 
 ObjectInspector monthsToAddVal = arguments [1]; 
 
 if（！（localDateVal instanceof StringObjectInspector））{
 throw new UDFArgumentException（第一个参数必须为String（local_date as String））; 
} 
 if（！（monthsToAddVal instanceof IntObjectInspector））{
 throw new UDFArgumentException（第二个参数必须是int类型（添加月））; 
} 
 return PrimitiveObjectInspectorFactory.writableStringObjectInspector; 
 
 
 @Override 
 public Object evaluate（DeferredObject [] arguments）throws HiveException {
 String localDateVal =（String）ObjectInspectorUtils.copyToStandardJavaObject（arguments [0]）。 get（），
 PrimitiveObjectInspectorFactory.javaStringObjectInspector）; 
 IntWritable monthsToAddVal =（IntWritable）ObjectInspectorUtils.copyToStandardJavaObject（arguments [1] .get（），
 PrimitiveObjectInspectorFactory.javaIntObjectInspector）; 
 
 LocalDate localDate = null; 
 try {
 localDate = LocalDate.parse（localDateVal，DateTimeFormat.forPattern（yyyy-MM-dd））; 
} catch（Exception ex）{
 return null; 
} 
 
返回新文本（localDate.plusMonths（monthsToAddVal.get（）。toString（））; 
} 
 
 @Override 
 public String getDisplayString（String [] arguments）{
 assert（arguments.length == 2）; 
返回add_month（+ arguments [0] +，+ arguments [1] + ）; 
} 
} 
  

测试用例来证明正确性： / p>

  import static org.hamcrest.CoreMatchers.is; 
 import static org.junit.Assert.assertNull; 
 import static org.junit.Assert.assertThat; 
 
 import org.apache.hadoop.hive.ql.udf.generic.GenericUDF.DeferredJavaObject; 
 import org.apache.hadoop.hive .ql.udf.generic.GenericUDF.DeferredObject; 
 import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; 
 import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory ; 
 import org.junit.Test; 
 
 public class AddMonthTest {
 private final String TEST_DATA =2014-01-01; 
 private final AddMonth addMonth = new AddMonth（）; 
 ObjectInspector ob = PrimitiveObjectInspectorFactory.javaStringObjectInspector; 
 ObjectInspector ob1 = PrimitiveObjectInspectorFactory.javaIntObjectInspector; 
 private final ObjectInspector [] arg = {ob，ob1}; 
 
 @Test 
 public void testAdd1Month（）throws Exception {
 DeferredJavaObject def1 = new DeferredJavaObject（TEST_DATA）; 
 DeferredJavaObject def2 = new DeferredJavaObject（1）; 
 
 addMonth.initialize（arg）; 
 
 DeferredObject [] def = {def1，def2}; 
 String resultData = addMonth.evaluate（def）.toString（）; 
 assertThat（resultData，is（2014-02-01））; 
} 
 
 @Test 
 public void testAdd12Month（）throws异常{
 DeferredJavaObject def1 = new DeferredJavaObject（TEST_DATA）; 
 DeferredJavaObject def2 = new DeferredJavaObject（12）; 
 
 addMonth.initialize（arg）; 
 
 DeferredObject [] def = {def1，def2}; 
 String resultData = addMonth.evaluate（def）.toString（）; 
 assertThat（resultData，is（2015-01-01））; 
} 
 
 @Test 
 public void testSub1Month（）throws Exception {
 DeferredJavaObject def1 = new DeferredJavaObject（TEST_DATA）; 
 DeferredJavaObject def2 = new DeferredJavaObject（-1）; 
 
 addMonth.initialize（arg）; 
 
 DeferredObject [] def = {def1，def2}; 
 String resultData = addMonth.evaluate（def）.toString（）; 
 assertThat（resultData，is（2013-12-01））; 
 
 
 @Test 
 public void testSub12Month（）throws Exception {
 DeferredJavaObject def1 = new DeferredJavaObject（TEST_DATA）; 
 DeferredJavaObject def2 = new DeferredJavaObject（-12）; 
 
 addMonth.initialize（arg）; 
 
 DeferredObject [] def = {def1，def2}; 
 String resultData = addMonth.evaluate（def）.toString（）; 
 assertThat（resultData，is（2013-01-01））; 
} 
 
 @Test 
 public void incorrectInput（）throws异常{
 DeferredJavaObject def1 = new DeferredJavaObject（InvalidString）; 
 DeferredJavaObject def2 = new DeferredJavaObject（12）; 
 
 addMonth.initialize（arg）; 
 
 DeferredObject [] def = {def1，def2}; 
对象resultData = addMonth.evaluate（def）; 
 assertNull（resultData）; 
} 
} 
  

您可以使用如下功能：

  CREATE TEMPORARY FUNCTION add_month AS'AddMonth'; 
 
 SELECT 
 add_month（'2014-01-01'，1）
 FROM 
 TAB_NAME; 
  

预期的输出将是：

  2014-02-01 
  

I'm searching for a function in Hive, that'll add month to date.

For example :

add_month('2014-01-01',1) -> '2014-02-01'

add_month('2013-12-01',1) -> '2014-01-01'

解决方案

You can create a generic UDF (GenericUDF).

Here is a sample UDF:

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.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorUtils;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.IntObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector;
import org.joda.time.LocalDate;
import org.joda.time.format.DateTimeFormat;
import org.apache.hadoop.io.IntWritable;
import org.apache.hadoop.io.Text;

public class AddMonth extends GenericUDF {

    @Override
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
        if (arguments.length != 2) {
            throw new UDFArgumentLengthException("The function add_month(local_date, months_to_add) requires 2 arguments.");
        }

        ObjectInspector localDateVal = arguments[0];
        ObjectInspector monthsToAddVal = arguments[1];

        if (!(localDateVal instanceof StringObjectInspector)) {
            throw new UDFArgumentException("First argument must be of type String (local_date as String)");
        }
        if (!(monthsToAddVal instanceof IntObjectInspector)) {
            throw new UDFArgumentException("Second argument must be of type int (Month to add)");
        }
        return PrimitiveObjectInspectorFactory.writableStringObjectInspector;
    }

    @Override
    public Object evaluate(DeferredObject[] arguments) throws HiveException {
        String localDateVal = (String) ObjectInspectorUtils.copyToStandardJavaObject(arguments[0].get(),
                PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        IntWritable monthsToAddVal = (IntWritable) ObjectInspectorUtils.copyToStandardJavaObject(arguments[1].get(),
                PrimitiveObjectInspectorFactory.javaIntObjectInspector);

        LocalDate localDate = null;
        try {
            localDate = LocalDate.parse(localDateVal, DateTimeFormat.forPattern("yyyy-MM-dd"));
        } catch (Exception ex) {
            return null;
        }

        return new Text(localDate.plusMonths(monthsToAddVal.get().toString());
    }

    @Override
    public String getDisplayString(String[] arguments) {
        assert (arguments.length == 2);
        return "add_month(" + arguments[0] + ", " + arguments[1] + ")";
    }
}

Test cases to demonstrate the correctness:

import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertThat;

import org.apache.hadoop.hive.ql.udf.generic.GenericUDF.DeferredJavaObject;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF.DeferredObject;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.junit.Test;

public class AddMonthTest {
    private final String TEST_DATA = "2014-01-01";
    private final AddMonth addMonth = new AddMonth();
    ObjectInspector ob = PrimitiveObjectInspectorFactory.javaStringObjectInspector;
    ObjectInspector ob1 = PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    private final ObjectInspector[]arg = { ob, ob1 };

    @Test
    public void testAdd1Month() throws Exception {
        DeferredJavaObject def1 = new DeferredJavaObject(TEST_DATA);
        DeferredJavaObject def2 = new DeferredJavaObject(1);

        addMonth.initialize(arg);

        DeferredObject[] def = { def1, def2 };
        String resultData = addMonth.evaluate(def).toString();
        assertThat(resultData, is("2014-02-01"));
    }

    @Test
    public void testAdd12Month() throws Exception {
        DeferredJavaObject def1 = new DeferredJavaObject(TEST_DATA);
        DeferredJavaObject def2 = new DeferredJavaObject(12);

        addMonth.initialize(arg);

        DeferredObject[] def = { def1, def2 };
        String resultData = addMonth.evaluate(def).toString();
        assertThat(resultData, is("2015-01-01"));
    }

    @Test
    public void testSub1Month() throws Exception {
        DeferredJavaObject def1 = new DeferredJavaObject(TEST_DATA);
        DeferredJavaObject def2 = new DeferredJavaObject(-1);

        addMonth.initialize(arg);

        DeferredObject[] def = { def1, def2 };
        String resultData = addMonth.evaluate(def).toString();
        assertThat(resultData, is("2013-12-01"));
    }

    @Test
    public void testSub12Month() throws Exception {
        DeferredJavaObject def1 = new DeferredJavaObject(TEST_DATA);
        DeferredJavaObject def2 = new DeferredJavaObject(-12);

        addMonth.initialize(arg);

        DeferredObject[] def = { def1, def2 };
        String resultData = addMonth.evaluate(def).toString();
        assertThat(resultData, is("2013-01-01"));
    }

    @Test
    public void incorrectInput() throws Exception {
        DeferredJavaObject def1 = new DeferredJavaObject("InvalidString");
        DeferredJavaObject def2 = new DeferredJavaObject(12);

        addMonth.initialize(arg);

        DeferredObject[] def = { def1, def2 };
        Object resultData = addMonth.evaluate(def);
        assertNull(resultData);
    }
}

You can use the function like:

CREATE TEMPORARY FUNCTION add_month AS 'AddMonth';

SELECT
    add_month ('2014-01-01',1) 
FROM
    TAB_NAME;

Expected output will be:

2014-02-01

这篇关于蜂巢功能：添加月份至今的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！