You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by na...@apache.org on 2009/09/19 02:52:52 UTC
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/
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