在WSO2 DAS中使用Spark SQL从时间戳中提取年份

Question

在我的hive脚本中，如果我想从时间戳中提取年份，我使用这个:

year(from_unixtime(cast(payload_fecha/1000 as BIGINT),'yyyy-MM-dd HH:mm:ss.SSS' )) as year

现在我正在测试新的DAS快照，我想做同样的事情，但是我不能使用from_unixtime。那么我如何在spark SQL中做同样的事情?

考虑到我使用WSO2 DAS，所以我需要一个与此工具一起工作的解决方案，而不是用于另一个环境的通用解决方案。

Answer 1

我在这个链接中找到了解决方案:

http://thanu912.blogspot.com/2015/08/using-user-defined-function-udf-in.html

我代码:

package org.softdevelop.spark.UDF;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class TimeUDFSpark
{
    /*
    public static void main( String[] args ) throws ParseException {
        TimeUDFSpark timeUdf = new TimeUDFSpark();
        System.out.println("Time: " + timeUdf.getTime(timeUdf.current_time(0)) );
        System.out.println("Year: " + timeUdf.getYear(timeUdf.current_time(0)));
        System.out.println("Month: " + timeUdf.getMonth(timeUdf.current_time(0)));
        System.out.println("Day: " + timeUdf.getDay(timeUdf.current_time(0)));
        System.out.println("Hours: " + timeUdf.getHour(timeUdf.current_time(0)));
        System.out.println("Minutes: " + timeUdf.getMinute(timeUdf.current_time(0)));
        System.out.println("Seconds: " + timeUdf.getSeconds(timeUdf.current_time(0)));
    }
    */
    /**
     * Convert time(ms) to DateFormat
     *
     * @param timeStamp time in ms
     * @return date as String
     */
    public String getTime(Long timeStamp) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date(timeStamp.longValue());
        return sdf.format(date);
    }

    /**
     * Return the year from timeStamp
     *
     * @param timeStamp time in ms
     * @return year as String
     * @throws ParseException
     */
    public String getYear(Long timeStamp) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = sdf.parse(getTime(timeStamp));
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int year = cal.get(Calendar.YEAR);
        return String.valueOf(year);
    }
    /**
     * Return the month from timeStamp
     *
     * @param timeStamp time in ms
     * @return month as String
     * @throws ParseException
     */
    public String getMonth(Long timeStamp) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = sdf.parse(getTime(timeStamp));
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int month = cal.get(Calendar.MONTH) + 1;
        return String.valueOf(month);
    }
    /**
     * Return the day from timeStamp
     *
     * @param timeStamp in ms
     * @return day as String
     * @throws ParseException
     */
    public String getDay(Long timeStamp) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = sdf.parse(getTime(timeStamp));
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int day = cal.get(Calendar.DAY_OF_MONTH);
        return String.valueOf(day);
    }
    /**
     * Return the hour from timeStamp
     *
     * @param timeStamp in ms
     * @return hour as String
     * @throws ParseException
     */
    public String getHour(Long timeStamp) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = sdf.parse(getTime(timeStamp));
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int hour = cal.get(Calendar.HOUR);
        return String.valueOf(hour);
    }
    /**
     * Return the minutes from timeStamp
     *
     * @param timeStamp in ms
     * @return minutes as String
     * @throws ParseException
     */
    public String getMinute(Long timeStamp) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = sdf.parse(getTime(timeStamp));
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int minute = cal.get(Calendar.MINUTE);
        return String.valueOf(minute);
    }
    /**
     * return the seconds from timeStamp
     *
     * @param timeStamp in ms
     * @return seconds as String
     * @throws ParseException
     */
    public String getSeconds(Long timeStamp) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = sdf.parse(getTime(timeStamp));
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int seconds = cal.get(Calendar.SECOND);
        return String.valueOf(seconds);
    }

    /**
     * Get the current time in ms
     *
     * @param param
     * @return
     */
    public long current_time(int param) {
        System.out.println(System.currentTimeMillis());
        return System.currentTimeMillis();
    }
}