hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From na...@apache.org
Subject svn commit: r816839 - in /hadoop/hive/trunk: ./ ql/src/java/org/apache/hadoop/hive/ql/exec/ ql/src/java/org/apache/hadoop/hive/ql/udf/ ql/src/test/queries/clientpositive/ ql/src/test/results/clientpositive/
Date Sat, 19 Sep 2009 00:52:52 GMT
Author: namit
Date: Sat Sep 19 00:52:51 2009
New Revision: 816839

URL: http://svn.apache.org/viewvc?rev=816839&view=rev
Log:
HIVE-843. Add UDFs for hour, minute and second
(Zheng Shao via namit)

Summary:

Trac Bug: # 843

Blame Rev:

Reviewed By:

Test Plan:

Revert Plan:

Database Impact:

Memcache Impact:

Other Notes:

EImportant:

- begin *PUBLIC* platform impact section -
Bugzilla: #
- end platform impact -


Added:
    hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFHour.java
    hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFMinute.java
    hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFSecond.java
    hadoop/hive/trunk/ql/src/test/queries/clientpositive/udf_hour_minute_second.q
    hadoop/hive/trunk/ql/src/test/results/clientpositive/udf_hour_minute_second.q.out
Modified:
    hadoop/hive/trunk/CHANGES.txt
    hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java
    hadoop/hive/trunk/ql/src/test/results/clientpositive/show_functions.q.out

Modified: hadoop/hive/trunk/CHANGES.txt
URL: http://svn.apache.org/viewvc/hadoop/hive/trunk/CHANGES.txt?rev=816839&r1=816838&r2=816839&view=diff
==============================================================================
--- hadoop/hive/trunk/CHANGES.txt (original)
+++ hadoop/hive/trunk/CHANGES.txt Sat Sep 19 00:52:51 2009
@@ -42,6 +42,9 @@
     HIVE-667. Provide hive ql to check table/partition status.
     (Yongqiang He via zshao)
 
+    HIVE-843. Add UDFs for hour, minute and second
+    (Zheng Shao via namit)
+
   IMPROVEMENTS
 
     HIVE-760. Add version info to META-INF/MANIFEST.MF.

Modified: hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java
URL: http://svn.apache.org/viewvc/hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java?rev=816839&r1=816838&r2=816839&view=diff
==============================================================================
--- hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java (original)
+++ hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java Sat
Sep 19 00:52:51 2009
@@ -121,6 +121,9 @@
     registerUDF("dayofmonth", UDFDayOfMonth.class, false);
     registerUDF("month", UDFMonth.class, false);
     registerUDF("year", UDFYear.class, false);
+    registerUDF("hour", UDFHour.class, false);
+    registerUDF("minute", UDFMinute.class, false);
+    registerUDF("second", UDFSecond.class, false);
     registerUDF("from_unixtime", UDFFromUnixTime.class, false);
     registerUDF("unix_timestamp", UDFUnixTimeStamp.class, false);
     registerUDF("to_date", UDFDate.class, false);

Added: hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFHour.java
URL: http://svn.apache.org/viewvc/hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFHour.java?rev=816839&view=auto
==============================================================================
--- hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFHour.java (added)
+++ hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFHour.java Sat Sep 19 00:52:51
2009
@@ -0,0 +1,83 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+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.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.hadoop.hive.ql.exec.UDF;
+import org.apache.hadoop.hive.ql.exec.description;
+import org.apache.hadoop.io.IntWritable;
+import org.apache.hadoop.io.Text;
+
+@description(
+    name = "hour",
+    value = "_FUNC_(date) - Returns the hour of date",
+    extended = "date is a string in the format of 'yyyy-MM-dd HH:mm:ss' or " +
+        "'HH:mm:ss'.\n" +
+        "Example:\n " +
+        "  > SELECT _FUNC_('2009-07-30 12:58:59') FROM src LIMIT 1;\n" +
+        "  12\n" +
+        "  > SELECT _FUNC_('12:58:59') FROM src LIMIT 1;\n" +
+        "  12"
+    )
+public class UDFHour extends UDF {
+
+  private static Log LOG = LogFactory.getLog(UDFHour.class.getName());
+
+  private SimpleDateFormat formatter1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+  private SimpleDateFormat formatter2 = new SimpleDateFormat("HH:mm:ss");
+  private Calendar calendar = Calendar.getInstance();
+
+  IntWritable result = new IntWritable();
+  public UDFHour() {
+  }
+
+  /**
+   * Get the hour from a date string.
+   * 
+   * @param dateString the dateString in the format of "yyyy-MM-dd HH:mm:ss" or "yyyy-MM-dd".
+   * @return an int from 0 to 23. null if the dateString is not a valid date string.
+   */
+  public IntWritable evaluate(Text dateString)  {
+    
+    if (dateString == null) {
+      return null;
+    }
+    
+    try {
+      Date date = null;
+      try {
+        date = formatter1.parse(dateString.toString());
+      } catch (ParseException e) {
+        date = formatter2.parse(dateString.toString());
+      }
+      calendar.setTime(date);
+      result.set(calendar.get(Calendar.HOUR_OF_DAY));
+      return result;
+    } catch (ParseException e) {
+      return null;
+    }
+  }
+
+}

Added: hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFMinute.java
URL: http://svn.apache.org/viewvc/hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFMinute.java?rev=816839&view=auto
==============================================================================
--- hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFMinute.java (added)
+++ hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFMinute.java Sat Sep 19
00:52:51 2009
@@ -0,0 +1,83 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+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.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.hadoop.hive.ql.exec.UDF;
+import org.apache.hadoop.hive.ql.exec.description;
+import org.apache.hadoop.io.IntWritable;
+import org.apache.hadoop.io.Text;
+
+@description(
+    name = "minute",
+    value = "_FUNC_(date) - Returns the minute of date",
+    extended = "date is a string in the format of 'yyyy-MM-dd HH:mm:ss' or " +
+        "'HH:mm:ss'.\n" +
+        "Example:\n " +
+        "  > SELECT _FUNC_('2009-07-30 12:58:59') FROM src LIMIT 1;\n" +
+        "  58\n" +
+        "  > SELECT _FUNC_('12:58:59') FROM src LIMIT 1;\n" +
+        "  58"
+    )
+public class UDFMinute extends UDF {
+
+  private static Log LOG = LogFactory.getLog(UDFMinute.class.getName());
+
+  private SimpleDateFormat formatter1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+  private SimpleDateFormat formatter2 = new SimpleDateFormat("HH:mm:ss");
+  private Calendar calendar = Calendar.getInstance();
+
+  IntWritable result = new IntWritable();
+  public UDFMinute() {
+  }
+
+  /**
+   * Get the minute from a date string.
+   * 
+   * @param dateString the dateString in the format of "yyyy-MM-dd HH:mm:ss" or "yyyy-MM-dd".
+   * @return an int from 0 to 59. null if the dateString is not a valid date string.
+   */
+  public IntWritable evaluate(Text dateString)  {
+    
+    if (dateString == null) {
+      return null;
+    }
+    
+    try {
+      Date date = null;
+      try {
+        date = formatter1.parse(dateString.toString());
+      } catch (ParseException e) {
+        date = formatter2.parse(dateString.toString());
+      }
+      calendar.setTime(date);
+      result.set(calendar.get(Calendar.MINUTE));
+      return result;
+    } catch (ParseException e) {
+      return null;
+    }
+  }
+
+}

Added: hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFSecond.java
URL: http://svn.apache.org/viewvc/hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFSecond.java?rev=816839&view=auto
==============================================================================
--- hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFSecond.java (added)
+++ hadoop/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFSecond.java Sat Sep 19
00:52:51 2009
@@ -0,0 +1,83 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+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.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.hadoop.hive.ql.exec.UDF;
+import org.apache.hadoop.hive.ql.exec.description;
+import org.apache.hadoop.io.IntWritable;
+import org.apache.hadoop.io.Text;
+
+@description(
+    name = "second",
+    value = "_FUNC_(date) - Returns the second of date",
+    extended = "date is a string in the format of 'yyyy-MM-dd HH:mm:ss' or " +
+        "'HH:mm:ss'.\n" +
+        "Example:\n " +
+        "  > SELECT _FUNC_('2009-07-30 12:58:59') FROM src LIMIT 1;\n" +
+        "  59\n" +
+        "  > SELECT _FUNC_('12:58:59') FROM src LIMIT 1;\n" +
+        "  59"
+    )
+public class UDFSecond extends UDF {
+
+  private static Log LOG = LogFactory.getLog(UDFSecond.class.getName());
+
+  private SimpleDateFormat formatter1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+  private SimpleDateFormat formatter2 = new SimpleDateFormat("HH:mm:ss");
+  private Calendar calendar = Calendar.getInstance();
+
+  IntWritable result = new IntWritable();
+  public UDFSecond() {
+  }
+
+  /**
+   * Get the minute from a date string.
+   * 
+   * @param dateString the dateString in the format of "yyyy-MM-dd HH:mm:ss" or "yyyy-MM-dd".
+   * @return an int from 0 to 59. null if the dateString is not a valid date string.
+   */
+  public IntWritable evaluate(Text dateString)  {
+    
+    if (dateString == null) {
+      return null;
+    }
+    
+    try {
+      Date date = null;
+      try {
+        date = formatter1.parse(dateString.toString());
+      } catch (ParseException e) {
+        date = formatter2.parse(dateString.toString());
+      }
+      calendar.setTime(date);
+      result.set(calendar.get(Calendar.SECOND));
+      return result;
+    } catch (ParseException e) {
+      return null;
+    }
+  }
+
+}

Added: hadoop/hive/trunk/ql/src/test/queries/clientpositive/udf_hour_minute_second.q
URL: http://svn.apache.org/viewvc/hadoop/hive/trunk/ql/src/test/queries/clientpositive/udf_hour_minute_second.q?rev=816839&view=auto
==============================================================================
--- hadoop/hive/trunk/ql/src/test/queries/clientpositive/udf_hour_minute_second.q (added)
+++ hadoop/hive/trunk/ql/src/test/queries/clientpositive/udf_hour_minute_second.q Sat Sep
19 00:52:51 2009
@@ -0,0 +1,20 @@
+DESCRIBE FUNCTION hour;
+DESCRIBE FUNCTION minute;
+DESCRIBE FUNCTION second;
+
+
+DESCRIBE FUNCTION EXTENDED hour;
+DESCRIBE FUNCTION EXTENDED minute;
+DESCRIBE FUNCTION EXTENDED second;
+
+
+EXPLAIN
+SELECT hour('2009-08-07 13:14:15'), hour('13:14:15'), hour('2009-08-07'),
+       minute('2009-08-07 13:14:15'), minute('13:14:15'), minute('2009-08-07'),
+       second('2009-08-07 13:14:15'), second('13:14:15'), second('2009-08-07')
+FROM src WHERE key = 86;
+
+SELECT hour('2009-08-07 13:14:15'), hour('13:14:15'), hour('2009-08-07'),
+       minute('2009-08-07 13:14:15'), minute('13:14:15'), minute('2009-08-07'),
+       second('2009-08-07 13:14:15'), second('13:14:15'), second('2009-08-07')
+FROM src WHERE key = 86;

Modified: hadoop/hive/trunk/ql/src/test/results/clientpositive/show_functions.q.out
URL: http://svn.apache.org/viewvc/hadoop/hive/trunk/ql/src/test/results/clientpositive/show_functions.q.out?rev=816839&r1=816838&r2=816839&view=diff
==============================================================================
--- hadoop/hive/trunk/ql/src/test/results/clientpositive/show_functions.q.out (original)
+++ hadoop/hive/trunk/ql/src/test/results/clientpositive/show_functions.q.out Sat Sep 19 00:52:51
2009
@@ -47,6 +47,7 @@
 get_json_object
 hash
 hex
+hour
 if
 index
 instr
@@ -66,6 +67,7 @@
 ltrim
 max
 min
+minute
 month
 negative
 not
@@ -85,6 +87,7 @@
 round
 rpad
 rtrim
+second
 sin
 size
 smallint
@@ -131,6 +134,7 @@
 lcase
 like
 locate
+minute
 negative
 positive
 regexp_replace

Added: hadoop/hive/trunk/ql/src/test/results/clientpositive/udf_hour_minute_second.q.out
URL: http://svn.apache.org/viewvc/hadoop/hive/trunk/ql/src/test/results/clientpositive/udf_hour_minute_second.q.out?rev=816839&view=auto
==============================================================================
--- hadoop/hive/trunk/ql/src/test/results/clientpositive/udf_hour_minute_second.q.out (added)
+++ hadoop/hive/trunk/ql/src/test/results/clientpositive/udf_hour_minute_second.q.out Sat
Sep 19 00:52:51 2009
@@ -0,0 +1,97 @@
+query: DESCRIBE FUNCTION hour
+hour(date) - Returns the hour of date
+query: DESCRIBE FUNCTION minute
+minute(date) - Returns the minute of date
+query: DESCRIBE FUNCTION second
+second(date) - Returns the second of date
+query: DESCRIBE FUNCTION EXTENDED hour
+hour(date) - Returns the hour of date
+date is a string in the format of 'yyyy-MM-dd HH:mm:ss' or 'HH:mm:ss'.
+Example:
+   > SELECT hour('2009-07-30 12:58:59') FROM src LIMIT 1;
+  12
+  > SELECT hour('12:58:59') FROM src LIMIT 1;
+  12
+query: DESCRIBE FUNCTION EXTENDED minute
+minute(date) - Returns the minute of date
+date is a string in the format of 'yyyy-MM-dd HH:mm:ss' or 'HH:mm:ss'.
+Example:
+   > SELECT minute('2009-07-30 12:58:59') FROM src LIMIT 1;
+  58
+  > SELECT minute('12:58:59') FROM src LIMIT 1;
+  58
+query: DESCRIBE FUNCTION EXTENDED second
+second(date) - Returns the second of date
+date is a string in the format of 'yyyy-MM-dd HH:mm:ss' or 'HH:mm:ss'.
+Example:
+   > SELECT second('2009-07-30 12:58:59') FROM src LIMIT 1;
+  59
+  > SELECT second('12:58:59') FROM src LIMIT 1;
+  59
+query: EXPLAIN
+SELECT hour('2009-08-07 13:14:15'), hour('13:14:15'), hour('2009-08-07'),
+       minute('2009-08-07 13:14:15'), minute('13:14:15'), minute('2009-08-07'),
+       second('2009-08-07 13:14:15'), second('13:14:15'), second('2009-08-07')
+FROM src WHERE key = 86
+ABSTRACT SYNTAX TREE:
+  (TOK_QUERY (TOK_FROM (TOK_TABREF src)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE))
(TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION hour '2009-08-07 13:14:15')) (TOK_SELEXPR (TOK_FUNCTION
hour '13:14:15')) (TOK_SELEXPR (TOK_FUNCTION hour '2009-08-07')) (TOK_SELEXPR (TOK_FUNCTION
minute '2009-08-07 13:14:15')) (TOK_SELEXPR (TOK_FUNCTION minute '13:14:15')) (TOK_SELEXPR
(TOK_FUNCTION minute '2009-08-07')) (TOK_SELEXPR (TOK_FUNCTION second '2009-08-07 13:14:15'))
(TOK_SELEXPR (TOK_FUNCTION second '13:14:15')) (TOK_SELEXPR (TOK_FUNCTION second '2009-08-07')))
(TOK_WHERE (= (TOK_TABLE_OR_COL key) 86))))
+
+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
+            Filter Operator
+              predicate:
+                  expr: (key = 86)
+                  type: boolean
+              Filter Operator
+                predicate:
+                    expr: (key = 86)
+                    type: boolean
+                Select Operator
+                  expressions:
+                        expr: hour('2009-08-07 13:14:15')
+                        type: int
+                        expr: hour('13:14:15')
+                        type: int
+                        expr: hour('2009-08-07')
+                        type: int
+                        expr: minute('2009-08-07 13:14:15')
+                        type: int
+                        expr: minute('13:14:15')
+                        type: int
+                        expr: minute('2009-08-07')
+                        type: int
+                        expr: second('2009-08-07 13:14:15')
+                        type: int
+                        expr: second('13:14:15')
+                        type: int
+                        expr: second('2009-08-07')
+                        type: int
+                  outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7,
_col8
+                  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
+
+
+query: SELECT hour('2009-08-07 13:14:15'), hour('13:14:15'), hour('2009-08-07'),
+       minute('2009-08-07 13:14:15'), minute('13:14:15'), minute('2009-08-07'),
+       second('2009-08-07 13:14:15'), second('13:14:15'), second('2009-08-07')
+FROM src WHERE key = 86
+Input: default/src
+Output: file:/data/users/zshao/tools/fb_hadoop/trunk/VENDOR.hive/trunk/build/ql/tmp/388087227/10000
+13	13	NULL	14	14	NULL	15	15	NULL



Mime
View raw message