You can use built-in functions or user-defined functions (UDFs) of MaxCompute for data computing. Built-in functions can be called directly. UDFs can be called only after being customized. This topic describes common operations on functions, such as creating, deleting, and viewing functions.

The following table describes common operations on functions.
Operation Description Performed by Operation platform
Create a UDF Creates a UDF in a MaxCompute project. Users who have the Write permission on functions You can execute the statements described in this topic on the following platforms:
Delete a UDF Deletes an existing UDF from a MaxCompute project. Users who have the Delete permission on functions
View a UDF Views the information of a specified UDF in a MaxCompute project. Users who have the Read permission on functions
View the UDF list Views the information of all UDFs in a MaxCompute project. Users who have the List permission on objects in a project
View the built-in function list Views the information of all built-in functions in the current MaxCompute project.

Create a UDF

Creates a UDF in a MaxCompute project.

  • Limits
    • Function names must be unique in a project. You cannot create a function that has the same name as an existing function in the project.
    • UDFs cannot overwrite built-in functions of MaxCompute. Only the project owner can use UDFs to overwrite built-in functions. If you use a UDF that overwrites a built-in function, warning information is displayed in Summary of the Logview of your job after the SQL statement is executed.
  • Syntax
    create function <function_name> as <'package_to_class'> using <'resource_list'>;
  • Parameters
    • function_name: required. The name of the UDF that you want to create.
    • package_to_class: required. The class of the UDF. This parameter is case-sensitive and must be enclosed in single quotation marks (').
      • For a Java UDF, specify this name as a fully qualified class name from the top-level package name to the UDF class name.
      • For a Python UDF, specify this name in the Python script name.Class name format.
        Note The Python script name refers to the underlying resource name that uniquely identifies a resource. The resource name of MaxCompute is not case-sensitive. For example, the resource name is pyudf_test.py the first time you upload a resource. If you rename the resource to PYUDF_TEST.py in DataStudio or use PYUDF_TEST.py to overwrite pyudf_test.py on the MaxCompute client, the underlying resource name that uniquely identifies the resource is still pyudf_test.py. In this case, when you create a UDF based on the resource, the class name must be pyudf_test.SampleUDF. You can run the list resource; command to view the underlying resource names that uniquely identify all resources.
    • resource_list: required. The list of resources used by the UDF.
      • The resource list must include the resource that contains UDF code. Make sure that the resource is uploaded to MaxCompute.
      • If the code calls the Distributed Cache API to read resource files, this resource list must also contain the list of resource files that are read by the UDF.
      • The resource list consists of multiple resource names and must be enclosed in single quotation marks ('). The resource names are separated by commas (,).
      • To specify the project that contains the resource, write the parameter in the <project_name>/resources/<resource_name> format.
  • Examples
    • Example 1: Create the my_lower function. In this example, the Java UDF class org.alidata.odps.udf.examples.Lower is in my_lower.jar. 
      create function my_lower as 'org.alidata.odps.udf.examples.Lower' using 'my_lower.jar';
    • Example 2: Create the my_lower function. In this example, the Python UDF class MyLower is in the pyudf_test.py script of the test_project project. 
      create function my_lower as 'pyudf_test.MyLower' using 'test_project/resources/pyudf_test.py';
    • Example 3: Create the test_udtf function. In this example, the Java UDF class com.aliyun.odps.examples.udf.UDTFResource is in udtfexample1.jar. The function depends on the file resource file_resource.txt, the table resource table_resource1, and the archive resource test_archive.zip. 
      create function test_udtf as 'com.aliyun.odps.examples.udf.UDTFResource' using 'udtfexample1.jar, file_resource.txt, table_resource1, test_archive.zip';

Delete a UDF

Deletes an existing UDF from a MaxCompute project.

  • Syntax
    drop function <function_name>;
  • Parameters

    function_name: required. The name of an existing UDF.

  • Example
    -- Delete the my_lower function. 
drop function my_lower;

View a UDF

Views the information of a specified UDF in a MaxCompute project. The information includes the name, owner, creation time, class name, and resource list of the UDF.

  • Syntax
    desc function <function_name>;
  • Parameters

    function_name: required. The name of an existing function.

  • Return value
    • Name: the name of the UDF.
    • Owner: the account that owns the UDF.
    • Created Time: the time when the UDF is created.
    • Class: the class of the UDF, which is case-sensitive.
    • Resources: the list of resources that are used by the UDF.
  • Example
    -- View the information of the my_lower function. 
desc function my_lower;
    The following result is returned: 
    Name                                    my_lower
Owner                                   ALIYUN$****
Created Time                            2020-06-18 15:50:19
Class                                   org.alidata.odps.udf.examples.Lower
Resources                               project_name/my_lower.jar

View the UDF list

Views the information of all UDFs in a MaxCompute project.

  • Syntax
    list functions [-p <project_name>];
  • Parameters

    project_name: optional. The name of a MaxCompute project.

  • Example
    list functions;
    The following result is returned: 
    Name              Owner                                        Create Time           Class                             Resources
ipv4_ipv6_aton    ALIYUN$****@aliyun.com 2021-11-15 13:42:14   com.aliyun.odps.udf.udfFunction.IpLocation ipv4.txt,ipv6.txt,udf-1.0-SNAPSHOT.jar
Lower_test        ALIYUN$****@aliyun.com 2021-08-25 15:51:22   com.aliyun.odps.udf.example.Lower udf-1.0-SNAPSHOT.jar
my_add            ALIYUN$****@aliyun.com 2021-05-08 11:26:02
my_index          ALIYUN$****@aliyun.com 2021-08-25 12:01:05   com.aliyun.odps.examples.udf.UdfArray udf-1.0-SNAPSHOT.jar
my_sum            ALIYUN$****@aliyun.com 2021-05-08 10:24:58
my_udtf           ALIYUN$****@aliyun.com 2021-02-23 11:37:30   com.aliyun.odps.examples.udf.UDTFResource udf-1.0-SNAPSHOT.jar
numpy             ALIYUN$****@aliyun.com 2020-11-11 14:12:50   numpy.TryImport                   numpy.py,numpy-1.19.4-cp37-cp37m-manylinux1_x86_64.zip
ST_Aggr_ConvexHull ALIYUN$****@aliyun.com 2021-03-18 17:06:29   com.esri.hadoop.hive.ST_Aggr_ConvexHull esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Aggr_Intersection ALIYUN$****@aliyun.com 2021-03-18 17:06:29   com.esri.hadoop.hive.ST_Aggr_Intersection esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Aggr_Union     ALIYUN$****@aliyun.com 2021-03-18 17:06:30   com.esri.hadoop.hive.ST_Aggr_Union esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Area           ALIYUN$****@aliyun.com 2021-03-18 17:06:30   com.esri.hadoop.hive.ST_Area      esri-geometry-api.jar,spatial-sdk-hive.jar
ST_AsBinary       ALIYUN$****@aliyun.com 2021-03-18 17:06:30   com.esri.hadoop.hive.ST_AsBinary  esri-geometry-api.jar,spatial-sdk-hive.jar
ST_AsGeoJson      ALIYUN$****@aliyun.com 2021-03-18 17:06:49   com.esri.hadoop.hive.ST_AsGeoJson esri-geometry-api.jar,spatial-sdk-hive.jar
ST_AsJson         ALIYUN$****@aliyun.com 2021-03-18 17:06:50   com.esri.hadoop.hive.ST_AsJson    esri-geometry-api.jar,spatial-sdk-hive.jar
ST_AsShape        ALIYUN$****@aliyun.com 2021-03-18 17:06:50   com.esri.hadoop.hive.ST_AsShape   esri-geometry-api.jar,spatial-sdk-hive.jar
ST_AsText         ALIYUN$****@aliyun.com 2021-03-18 17:06:50   com.esri.hadoop.hive.ST_AsText    esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Bin            ALIYUN$****@aliyun.com 2021-03-18 17:06:50   com.esri.hadoop.hive.ST_Bin       esri-geometry-api.jar,spatial-sdk-hive.jar
ST_BinEnvelope    ALIYUN$****@aliyun.com 2021-03-18 17:07:01   com.esri.hadoop.hive.ST_BinEnvelope esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Boundary       ALIYUN$****@aliyun.com 2021-03-18 17:07:01   com.esri.hadoop.hive.ST_Boundary  esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Buffer         ALIYUN$****@aliyun.com 2021-03-18 17:07:01   com.esri.hadoop.hive.ST_Buffer    esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Centroid       ALIYUN$****@aliyun.com 2021-03-18 17:07:01   com.esri.hadoop.hive.ST_Centroid  esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Contains       ALIYUN$****@aliyun.com 2021-03-18 17:07:01   com.esri.hadoop.hive.ST_Contains  esri-geometry-api.jar,spatial-sdk-hive.jar
ST_ConvexHull     ALIYUN$****@aliyun.com 2021-03-18 17:07:13   com.esri.hadoop.hive.ST_ConvexHull esri-geometry-api.jar,spatial-sdk-hive.jar
ST_CoordDim       ALIYUN$****@aliyun.com 2021-03-18 17:07:14   com.esri.hadoop.hive.ST_CoordDim  esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Crosses        ALIYUN$****@aliyun.com 2021-03-18 17:07:14   com.esri.hadoop.hive.ST_Crosses   esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Difference     ALIYUN$****@aliyun.com 2021-03-18 17:07:14   com.esri.hadoop.hive.ST_Difference esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Dimension      ALIYUN$****@aliyun.com 2021-03-18 17:07:14   com.esri.hadoop.hive.ST_Dimension esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Disjoint       ALIYUN$****@aliyun.com 2021-03-18 17:07:31   com.esri.hadoop.hive.ST_Disjoint  esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Distance       ALIYUN$****@aliyun.com 2021-03-18 17:07:31   com.esri.hadoop.hive.ST_Distance  esri-geometry-api.jar,spatial-sdk-hive.jar
ST_EndPoint       ALIYUN$****@aliyun.com 2021-03-18 17:07:31   com.esri.hadoop.hive.ST_EndPoint  esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Envelope       ALIYUN$****@aliyun.com 2021-03-18 17:07:32   com.esri.hadoop.hive.ST_Envelope  esri-geometry-api.jar,spatial-sdk-hive.jar
ST_EnvIntersects  ALIYUN$****@aliyun.com 2021-03-18 17:07:32   com.esri.hadoop.hive.ST_EnvIntersects esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Equals         ALIYUN$****@aliyun.com 2021-03-18 17:07:44   com.esri.hadoop.hive.ST_Equals    esri-geometry-api.jar,spatial-sdk-hive.jar
ST_ExteriorRing   ALIYUN$****@aliyun.com 2021-03-18 17:07:44   com.esri.hadoop.hive.ST_ExteriorRing esri-geometry-api.jar,spatial-sdk-hive.jar
ST_GeodesicLengthWGS84 ALIYUN$****@aliyun.com 2021-03-18 17:07:44   com.esri.hadoop.hive.ST_GeodesicLengthWGS84 esri-geometry-api.jar,spatial-sdk-hive.jar
ST_GeomCollection ALIYUN$****@aliyun.com 2021-03-18 17:07:44   com.esri.hadoop.hive.ST_GeomCollection esri-geometry-api.jar,spatial-sdk-hive.jar
ST_Geometry       ALIYUN$****@aliyun.com 2021-03-18 17:07:44   com.esri.hadoop.hive.ST_Geometry  esri-geometry-api.jar,spatial-sdk-hive.jar
ST_GeometryN      ALIYUN$****@aliyun.com 2021-03-18 17:07:55   com.esri.hadoop.hive.ST_GeometryN esri-geometry-api.jar,spatial-sdk-hive.jar
ST_GeometryType   ALIYUN$****@aliyun.com 2021-03-18 17:07:55   com.esri.hadoop.hive.ST_GeometryType esri-geometry-api.jar,spatial-sdk-hive.jar
ST_GeomFromGeoJson ALIYUN$****@aliyun.com 2021-03-18 17:07:55   com.esri.hadoop.hive.ST_GeomFromGeoJson esri-geometry-api.jar,spatial-sdk-hive.jar
ST_GeomFromJson   ALIYUN$****@aliyun.com 2021-03-18 17:07:55   com.esri.hadoop.hive.ST_GeomFromJson esri-geometry-api.jar,spatial-sdk-hive.jar
ST_GeomFromShape  ALIYUN$****@aliyun.com 2021-03-18 17:07:56   com.esri.hadoop.hive.ST_GeomFromShape esri-geometry-api.jar,spatial-sdk-hive.jar
ST_GeomFromText   ALIYUN$****@aliyun.com 2021-03-18 17:08:10   com.esri.hadoop.hive.ST_GeomFromText esri-geometry-api.jar,spatial-sdk-hive.jar
ST_GeomFromWKB    ALIYUN$****@aliyun.com 2021-03-18 17:08:10   com.esri.hadoop.hive.ST_GeomFromWKB esri-geometry-api.jar,spatial-sdk-hive.jar

View the built-in function list

Views the information of all built-in functions in the current MaxCompute project.

  • Syntax
    show functions;
  • Example
    show functions;
    The following result is returned: 
    ID = 20211214091641326gg0g****
::::ABS SCALAR  INT ABS(INT arg0),DOUBLE ABS(DOUBLE arg0),DECIMAL(?,?) ABS(DECIMAL(?,?) arg0),BIGINT ABS(BIGINT arg0)
::::ACOS        SCALAR  DOUBLE ACOS(DOUBLE arg0),DOUBLE ACOS(DECIMAL(?,?) arg0)
::::ADD_MONTHS  SCALAR  STRING ADD_MONTHS(TIMESTAMP arg0, BIGINT arg1),STRING ADD_MONTHS(STRING arg0, BIGINT arg1),STRING ADD_MONTHS(DATE arg0, BIGINT arg1)
::::ALL_MATCH   SCALAR  BOOLEAN ALL_MATCH(ARRAY<T> arg0, java.util.function.Function<T, java.lang.Boolean> arg1)
::::ANY_MATCH   SCALAR  BOOLEAN ANY_MATCH(ARRAY<T> arg0, java.util.function.Function<T, java.lang.Boolean> arg1)
::::ANY_VALUE   AGGREGATOR      T ANY_VALUE([DISTINCT] T arg1)
::::APPROX_DISTINCT     AGGREGATOR      BIGINT APPROX_DISTINCT([DISTINCT] P arg1, DOUBLE arg2),BIGINT APPROX_DISTINCT([DISTINCT] P arg1)
::::ARG_MAX     AGGREGATOR      R ARG_MAX([DISTINCT] T arg1, R arg2)
::::ARG_MIN     AGGREGATOR      R ARG_MIN([DISTINCT] T arg1, R arg2)
::::ARRAY       SCALAR  ARRAY<STRING> ARRAY(),ARRAY<T> ARRAY(T arg0...)
::::ARRAYS_OVERLAP      SCALAR  BOOLEAN ARRAYS_OVERLAP(ARRAY<T> arg0, ARRAY<T> arg1)
::::ARRAYS_ZIP  SCALAR  null
::::ARRAY_CONTAINS      SCALAR  BOOLEAN ARRAY_CONTAINS(ARRAY<T> arg0, T arg1)
::::ARRAY_DISTINCT      SCALAR  ARRAY<T> ARRAY_DISTINCT(ARRAY<T> arg0)
::::ARRAY_EXCEPT        SCALAR  ARRAY<T> ARRAY_EXCEPT(ARRAY<T> arg0, ARRAY<T> arg1)
::::ARRAY_INTERSECT     SCALAR  ARRAY<T> ARRAY_INTERSECT(ARRAY<T> arg0, ARRAY<T> arg1)
::::ARRAY_JOIN  SCALAR  STRING ARRAY_JOIN(ARRAY<STRING> arg0, STRING arg1),STRING ARRAY_JOIN(ARRAY<STRING> arg0, STRING arg1, STRING arg2)
::::ARRAY_MAX   SCALAR  T ARRAY_MAX(ARRAY<T> arg0)
::::ARRAY_MIN   SCALAR  T ARRAY_MIN(ARRAY<T> arg0)
::::ARRAY_POSITION      SCALAR  BIGINT ARRAY_POSITION(ARRAY<T> arg0, T arg1)
::::ARRAY_REDUCE        SCALAR  OUT ARRAY_REDUCE(ARRAY<IN> arg0, BUF arg1, java.util.function.BiFunction<BUF, IN, BUF> arg2, java.util.function.Function<BUF, OUT> arg3)
::::ARRAY_REMOVE        SCALAR  ARRAY<T> ARRAY_REMOVE(ARRAY<T> arg0, T arg1)
::::ARRAY_REPEAT        SCALAR  ARRAY<T> ARRAY_REPEAT(T arg0, BIGINT arg1)
::::ARRAY_SORT  SCALAR  ARRAY<T> ARRAY_SORT(ARRAY<T> arg0, java.util.function.BiFunction<T, T, java.lang.Long> arg1)
::::ARRAY_UNION SCALAR  ARRAY<T> ARRAY_UNION(ARRAY<T> arg0, ARRAY<T> arg1)
::::ASCII       SCALAR  BIGINT ASCII(STRING arg0)
::::ASIN        SCALAR  DOUBLE ASIN(DOUBLE arg0),DOUBLE ASIN(DECIMAL(?,?) arg0)
::::ATAN        SCALAR  DOUBLE ATAN(DOUBLE arg0),DOUBLE ATAN(DECIMAL(?,?) arg0)
::::AT_MOST_ONE_ROW     AGGREGATOR      T AT_MOST_ONE_ROW([DISTINCT] T arg1)
::::AVG AGGREGATOR      DECIMAL(?,?) AVG([DISTINCT] DECIMAL(?,?) arg1),DOUBLE AVG([DISTINCT] DOUBLE arg1)
::::AVG WINDOW  DOUBLE AVG([DISTINCT] DOUBLE arg0),DECIMAL(?,?) AVG([DISTINCT] DECIMAL(?,?) arg0)
::::BASE64      SCALAR  STRING BASE64(BINARY arg0)
::::BIN SCALAR  STRING BIN(BIGINT arg0)
::::BITAND      SCALAR  BIGINT BITAND(BIGINT arg0, BIGINT arg1)
::::BITNOT      SCALAR  BIGINT BITNOT(BIGINT arg0)
::::BITOR       SCALAR  BIGINT BITOR(BIGINT arg0, BIGINT arg1)
::::BITXOR      SCALAR  BIGINT BITXOR(BIGINT arg0, BIGINT arg1)
::::BROUND      SCALAR  DOUBLE BROUND(DOUBLE arg0, BIGINT arg1),DOUBLE BROUND(DOUBLE arg0)
::::CASE        SCALAR  null
......