大数据（二十五）Hive【Hive 函数 及其 自定义函数】

原生函数

        参考链接https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

自定义函数

        一、3种自定义函数

                1、UDF-----User Defined Function （一进一出）

                2、UDAF-----User Aggregation Defined Function  （多进一出）

                3、UDTF-----User Defined Table-Generating Function （一进多出）

        二、创建函数

                参考链接：https://cwiki.apache.org/confluence/display/Hive/Hiveplugins

        三、实例（建立UDF函数）

                例子：我们要写一个函数（“2016-01-01 12:00:00”，“YYYY-MM-DD HH:MM:SS”）返回

                1、新建如下类

               import org.apache.hadoop.hive.metastore.api.Date;               import org.apache.hadoop.hive.ql.exec.UDF;               import org.apache.hadoop.hive.serde2.io.TimestampWritable;               import org.apache.hadoop.io.Text;               import java.sql.Timestamp;               import java.text.SimpleDateFormat;               /**                * Created by ZhangJintao on 2017/10/31.                */               public class TestHive2 extends UDF{                   public Timestamp evaluate(final Text data , final Text fmt) throws  Exception{                       SimpleDateFormat sd = new SimpleDateFormat(fmt.toString());                       java.util.Date parse = sd.parse(data.toString());                       TimestampWritable tw = new TimestampWritable();                       tw.setTime(parse.getTime());                       return tw.getTimestamp();                   }               }

                2、打包

  

              之后我们可以看到下图琐事地方有个jar包

              将其拷贝至/opt/sxt/soft

             3、 在hive下执行  add jar  /opt/sxt/soft/testJDBChive.jar

            0: jdbc:hive2://localhost:10000> add jar  /opt/sxt/soft/testJDBChive.jar ;                       INFO  : Added [/opt/sxt/soft/testJDBChive.jar] to class path                       INFO  : Added resources: [/opt/sxt/soft/testJDBChive.jar]                       No rows affected (0.054 seconds)                       0: jdbc:hive2://localhost:10000> list jars;                       +---------------------------------+--+                       |            resource             |                       +---------------------------------+--+                       | /opt/sxt/soft/testJDBChive.jar  |                       +---------------------------------+--+                       1 row selected (0.311 seconds)

             4、 在hive下执行 

                       0: jdbc:hive2://localhost:10000>  CREATE FUNCTION StringToDate AS 'TestHive2' ;                       No rows affected (0.333 seconds)                       0: jdbc:hive2://localhost:10000> select StringToDate('2017-10-03 15:32:10','YYYY-MM-DD HH:MM:SS') from people ;                       +-------------------------+--+                       |           _c0           |                       +-------------------------+--+                       | 2017-01-01 15:00:00.01  |                       | 2017-01-01 15:00:00.01  |                       +-------------------------+--+