CREATE FUNCTIONCREATE FUNCTION

创建临时或永久函数。Creates a temporary or permanent function. 临时函数的作用域为会话级别,在此级别上,永久函数在持久性目录中创建,并且可供所有会话使用。Temporary functions are scoped at a session level where as permanent functions are created in the persistent catalog and are made available to all sessions. USING第一次执行时,子句中指定的资源将可供所有执行程序使用。The resources specified in the USING clause are made available to all executors when they are executed for the first time. 除了 SQL 接口,Spark 还允许使用 Scala、Python 和 Java Api 创建自定义用户定义的标量函数和聚合函数。In addition to the SQL interface, Spark allows you to create custom user defined scalar and aggregate functions using Scala, Python, and Java APIs. 有关详细信息,请参阅 用户定义的标量函数 (udf) 用户定义的聚合函数 (UDAFs) See User-defined scalar functions (UDFs) and User-defined aggregate functions (UDAFs) for more information.

语法Syntax

CREATE [ OR REPLACE ] [ TEMPORARY ] FUNCTION [ IF NOT EXISTS ]
    function_name AS class_name [ resource_locations ]

参数Parameters

  • 或替换OR REPLACE

    如果指定,将重新加载该函数的资源。If specified, the resources for the function are reloaded. 这主要用于获取对函数实现所做的任何更改。This is mainly useful to pick up any changes made to the implementation of the function. 此参数与互斥, IF NOT EXISTS 不能一起指定。This parameter is mutually exclusive to IF NOT EXISTS and cannot be specified together.

  • TEMPORARYTEMPORARY

    指示正在创建的函数的作用域。Indicates the scope of function being created. TEMPORARY 指定时,创建的函数有效并在当前会话中可见。When TEMPORARY is specified, the created function is valid and visible in the current session. 在目录中不会为这些类型的函数生成永久条目。No persistent entry is made in the catalog for these kind of functions.

  • 如果不存在IF NOT EXISTS

    如果已指定,则仅在不存在时才创建该函数。If specified, creates the function only when it does not exist. 如果系统中已存在指定的函数,则函数的创建将成功 (不会引发错误) 。The creation of function succeeds (no error is thrown) if the specified function already exists in the system. 此参数与互斥, OR REPLACE 不能一起指定。This parameter is mutually exclusive to OR REPLACE and cannot be specified together.

  • function_namefunction_name

    要创建的函数的名称。A name of function to be created. 可以选择使用数据库名称来限定函数名称。The function name may be optionally qualified with a database name.

    语法:[database_name.] function_nameSyntax: [database_name.] function_name

  • class_nameclass_name

    提供要创建的函数的实现的类的名称。The name of the class that provides the implementation for function to be created. 实现类应按如下方式扩展其中一个基类:The implementing class should extend one of the base classes as follows:

    • UDF UDAF 在包中扩展或 org.apache.hadoop.hive.ql.execShould extend UDF or UDAF in org.apache.hadoop.hive.ql.exec package.
    • AbstractGenericUDAFResolver GenericUDF GenericUDTF 在包中扩展、或 org.apache.hadoop.hive.ql.udf.genericShould extend AbstractGenericUDAFResolver, GenericUDF, or GenericUDTF in org.apache.hadoop.hive.ql.udf.generic package.
    • 应扩展 UserDefinedAggregateFunction org.apache.spark.sql.expressions 包。Should extend UserDefinedAggregateFunction in org.apache.spark.sql.expressions package.
  • resource_locationsresource_locations

    包含函数实现及其依赖项的资源列表。The list of resources that contain the implementation of the function along with its dependencies.

    语法:USING { { (JAR | FILE ) resource_uri } , ... }Syntax: USING { { (JAR | FILE ) resource_uri } , ... }

示例Examples

-- 1. Create a simple UDF `SimpleUdf` that increments the supplied integral value by 10.
--    import org.apache.hadoop.hive.ql.exec.UDF;
--    public class SimpleUdf extends UDF {
--      public int evaluate(int value) {
--        return value + 10;
--      }
--    }
-- 2. Compile and place it in a JAR file called `SimpleUdf.jar` in /tmp.

-- Create a table called `test` and insert two rows.
CREATE TABLE test(c1 INT);
INSERT INTO test VALUES (1), (2);

-- Create a permanent function called `simple_udf`.
CREATE FUNCTION simple_udf AS 'SimpleUdf'
    USING JAR '/tmp/SimpleUdf.jar';

-- Verify that the function is in the registry.
SHOW USER FUNCTIONS;
+------------------+
|          function|
+------------------+
|default.simple_udf|
+------------------+

-- Invoke the function. Every selected value should be incremented by 10.
SELECT simple_udf(c1) AS function_return_value FROM t1;
+---------------------+
|function_return_value|
+---------------------+
|                   11|
|                   12|
+---------------------+

-- Created a temporary function.
CREATE TEMPORARY FUNCTION simple_temp_udf AS 'SimpleUdf'
    USING JAR '/tmp/SimpleUdf.jar';

-- Verify that the newly created temporary function is in the registry.
-- The temporary function does not have a qualified
-- database associated with it.
SHOW USER FUNCTIONS;
+------------------+
|          function|
+------------------+
|default.simple_udf|
|   simple_temp_udf|
+------------------+

-- 1. Modify `SimpleUdf`'s implementation to add supplied integral value by 20.
--    import org.apache.hadoop.hive.ql.exec.UDF;

--    public class SimpleUdfR extends UDF {
--      public int evaluate(int value) {
--        return value + 20;
--      }
--    }
-- 2. Compile and place it in a jar file called `SimpleUdfR.jar` in /tmp.

-- Replace the implementation of `simple_udf`
CREATE OR REPLACE FUNCTION simple_udf AS 'SimpleUdfR'
    USING JAR '/tmp/SimpleUdfR.jar';

-- Invoke the function. Every selected value should be incremented by 20.
SELECT simple_udf(c1) AS function_return_value FROM t1;
+---------------------+
|function_return_value|
+---------------------+
|                   21|
|                   22|
+---------------------+