diff --git ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java index 425900d..9a35ae2 100644 --- ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java +++ ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java @@ -126,6 +126,7 @@ import org.apache.hadoop.hive.ql.udf.UDFUnixTimeStamp; import org.apache.hadoop.hive.ql.udf.UDFUpper; import org.apache.hadoop.hive.ql.udf.UDFWeekOfYear; import org.apache.hadoop.hive.ql.udf.UDFYear; +import org.apache.hadoop.hive.ql.udf.UDFQuarter; import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFAverage; import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFBridge; import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFCollectSet; @@ -325,6 +326,7 @@ public final class FunctionRegistry { registerUDF("unix_timestamp", UDFUnixTimeStamp.class, false); registerUDF("to_date", UDFDate.class, false); registerUDF("weekofyear", UDFWeekOfYear.class, false); + registerUDF("quarter", UDFQuarter.class, false); registerUDF("date_add", UDFDateAdd.class, false); registerUDF("date_sub", UDFDateSub.class, false); diff --git ql/src/java/org/apache/hadoop/hive/ql/udf/UDFQuarter.java ql/src/java/org/apache/hadoop/hive/ql/udf/UDFQuarter.java new file mode 100644 index 0000000..1b79725 --- /dev/null +++ ql/src/java/org/apache/hadoop/hive/ql/udf/UDFQuarter.java @@ -0,0 +1,95 @@ +package org.apache.hadoop.hive.ql.udf; + +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.Calendar; +import java.util.Date; + +import org.apache.hadoop.hive.ql.exec.Description; +import org.apache.hadoop.hive.ql.exec.UDF; +import org.apache.hadoop.hive.serde2.io.TimestampWritable; +import org.apache.hadoop.io.IntWritable; +import org.apache.hadoop.io.Text; + +/** + * + * UDFQuarter + * + */ +@Description(name = "quarter", value = "_FUNC_(date or timestamp) -" + + " Returns the quarter of the year corresponding to date or timestamp") +public class UDFQuarter extends UDF { + private final SimpleDateFormat formatter = new SimpleDateFormat( + "yyyy-MM-dd"); + private final Calendar calendar = Calendar.getInstance(); + private IntWritable result = new IntWritable(); + + public UDFQuarter() { + + } + /** + * Get the quarter of the year from a date string. + * + * @param dateString + * the dateString in the format of "yyyy-MM-dd HH:mm:ss" or + * "yyyy-MM-dd". + * @return an IntWritable from 1 to 4. null if the dateString is not a valid date + * string. + */ + public IntWritable evaluate(Text dateString) { + if (dateString == null) { + return null; + } + try { + Date date = formatter.parse(dateString.toString()); + calendar.setTime(date); + int month = calendar.get(Calendar.MONTH) + 1; + result = getQuarter(month); + + } catch (ParseException e) { + return null; + } + return result; + + } + /** + * Get the quarter of the year from a date Timestamp. + * + * @param dateTimeStamp + * the dateTimeStamp in the format of "yyyy-MM-dd HH:mm:ss" or + * "yyyy-MM-dd". + * @return an IntWritable from 1 to 4. null if the dateTimeStamp is not a valid date + * TimeStamp. + */ + public IntWritable evaluate(TimestampWritable dateTimeStamp) { + if (dateTimeStamp == null) { + return null; + } + calendar.setTime(dateTimeStamp.getTimestamp()); + int month = calendar.get(Calendar.MONTH) + 1; + result = getQuarter(month); + return result; + } + + /** + * getQuarter method calculates in which quarter the date falls + * + * @param monthOfYear + * @return quarter + */ + public IntWritable getQuarter(int monthOfYear) { + IntWritable quarter = new IntWritable(); + + if (monthOfYear >= 1 && monthOfYear <= 3) { + quarter.set(1); + } else if (monthOfYear >= 4 && monthOfYear <= 6) { + quarter.set(2); + } else if (monthOfYear >= 7 && monthOfYear <= 9) { + quarter.set(3); + } else if (monthOfYear >= 10 && monthOfYear <= 12) { + quarter.set(4); + } + return quarter; + + } +} diff --git ql/src/test/queries/clientpositive/udf_quarter.q ql/src/test/queries/clientpositive/udf_quarter.q new file mode 100644 index 0000000..06e3c78 --- /dev/null +++ ql/src/test/queries/clientpositive/udf_quarter.q @@ -0,0 +1,32 @@ +DESCRIBE FUNCTION quarter; +DESCRIBE FUNCTION EXTENDED quarter; +EXPLAIN SELECT '2010-10-12', + quarter("2010-10-12") FROM src LIMIT 1; +SELECT '2009-03-20', + quarter("2009-03-20") FROM src LIMIT 1; + +SELECT + '2009/03/20', + quarter("2009/03/20") FROM src LIMIT 1; + + +SELECT + '2009/03/20 11:30:01', + quarter("2009/03/20 11:30:01") +FROM src LIMIT 1; + +SELECT + '2009-03-20 11:30:01', + quarter("2009-03-20 11:30:01") +FROM src LIMIT 1; + +SELECT + 'random_string', + quarter('random_string') +FROM src LIMIT 1; + +SELECT + '2009 Mar 20 11:30:01 am', + quarter("2009 Mar 20 11:30:01 am") +FROM src LIMIT 1; + diff --git ql/src/test/results/clientpositive/udf_quarter.q.out ql/src/test/results/clientpositive/udf_quarter.q.out new file mode 100644 index 0000000..c31213a --- /dev/null +++ ql/src/test/results/clientpositive/udf_quarter.q.out @@ -0,0 +1,134 @@ +PREHOOK: query: DESCRIBE FUNCTION quarter +PREHOOK: type: DESCFUNCTION +POSTHOOK: query: DESCRIBE FUNCTION quarter +POSTHOOK: type: DESCFUNCTION +quarter(date or timestamp) - Returns the quarter of the year corresponding to date or timestamp +PREHOOK: query: DESCRIBE FUNCTION EXTENDED quarter +PREHOOK: type: DESCFUNCTION +POSTHOOK: query: DESCRIBE FUNCTION EXTENDED quarter +POSTHOOK: type: DESCFUNCTION +quarter(date or timestamp) - Returns the quarter of the year corresponding to date or timestamp +PREHOOK: query: EXPLAIN SELECT '2010-10-12', + quarter("2010-10-12") FROM src LIMIT 1 +PREHOOK: type: QUERY +POSTHOOK: query: EXPLAIN SELECT '2010-10-12', + quarter("2010-10-12") FROM src LIMIT 1 +POSTHOOK: type: QUERY +ABSTRACT SYNTAX TREE: + (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR '2010-10-12') (TOK_SELEXPR (TOK_FUNCTION quarter "2010-10-12"))) (TOK_LIMIT 1))) + +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 is a root stage + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Alias -> Map Operator Tree: + src + TableScan + alias: src + Select Operator + expressions: + expr: '2010-10-12' + type: string + expr: quarter('2010-10-12') + type: int + outputColumnNames: _col0, _col1 + Limit + File Output Operator + compressed: false + GlobalTableId: 0 + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat + + Stage: Stage-0 + Fetch Operator + limit: 1 + + +PREHOOK: query: SELECT '2009-03-20', + quarter("2009-03-20") FROM src LIMIT 1 +PREHOOK: type: QUERY +PREHOOK: Input: default@src +#### A masked pattern was here #### +POSTHOOK: query: SELECT '2009-03-20', + quarter("2009-03-20") FROM src LIMIT 1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@src +#### A masked pattern was here #### +2009-03-20 1 +PREHOOK: query: SELECT + '2009/03/20', + quarter("2009/03/20") FROM src LIMIT 1 +PREHOOK: type: QUERY +PREHOOK: Input: default@src +#### A masked pattern was here #### +POSTHOOK: query: SELECT + '2009/03/20', + quarter("2009/03/20") FROM src LIMIT 1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@src +#### A masked pattern was here #### +2009/03/20 NULL +PREHOOK: query: SELECT + '2009/03/20 11:30:01', + quarter("2009/03/20 11:30:01") +FROM src LIMIT 1 +PREHOOK: type: QUERY +PREHOOK: Input: default@src +#### A masked pattern was here #### +POSTHOOK: query: SELECT + '2009/03/20 11:30:01', + quarter("2009/03/20 11:30:01") +FROM src LIMIT 1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@src +#### A masked pattern was here #### +2009/03/20 11:30:01 NULL +PREHOOK: query: SELECT + '2009-03-20 11:30:01', + quarter("2009-03-20 11:30:01") +FROM src LIMIT 1 +PREHOOK: type: QUERY +PREHOOK: Input: default@src +#### A masked pattern was here #### +POSTHOOK: query: SELECT + '2009-03-20 11:30:01', + quarter("2009-03-20 11:30:01") +FROM src LIMIT 1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@src +#### A masked pattern was here #### +2009-03-20 11:30:01 1 +PREHOOK: query: SELECT + 'random_string', + quarter('random_string') +FROM src LIMIT 1 +PREHOOK: type: QUERY +PREHOOK: Input: default@src +#### A masked pattern was here #### +POSTHOOK: query: SELECT + 'random_string', + quarter('random_string') +FROM src LIMIT 1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@src +#### A masked pattern was here #### +random_string NULL +PREHOOK: query: SELECT + '2009 Mar 20 11:30:01 am', + quarter("2009 Mar 20 11:30:01 am") +FROM src LIMIT 1 +PREHOOK: type: QUERY +PREHOOK: Input: default@src +#### A masked pattern was here #### +POSTHOOK: query: SELECT + '2009 Mar 20 11:30:01 am', + quarter("2009 Mar 20 11:30:01 am") +FROM src LIMIT 1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@src +#### A masked pattern was here #### +2009 Mar 20 11:30:01 am NULL