Hive function: add month to date

问题:

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'

回答1:

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

回答2:

Just type:

 select add_months('2014-01-01', 1);