MaxCompute supports two types of functions: built-in functions and user-defined functions (UDFs). Built-in functions are available immediately — no setup required. UDFs must be registered before they can be called in SQL statements.
This topic covers common UDF management operations: creating, deleting, and viewing functions.
Operations at a glance
| Operation | Required permission | Platforms |
|---|---|---|
| Create a UDF | Write permission on functions | MaxCompute client, DataWorks console, MaxCompute Studio |
| Delete a UDF | Delete permission on functions | — |
| View a UDF | Read permission on functions | — |
| List all UDFs | List permission on project objects | — |
| List all built-in functions | List permission on project objects | — |
Prerequisites
Before you begin, ensure that you have:
A JAR or Python resource containing the UDF code, uploaded to MaxCompute
The required permissions for the operation (see the table above)
Create a UDF
Registers a UDF in a MaxCompute project so it can be called in SQL statements.
Limitations
Function names must be unique within a project. Creating a function with the same name as an existing function is not allowed.
UDFs cannot overwrite built-in functions. Only the project owner can register a UDF that shares a name with a built-in function. If such a UDF is invoked, a warning appears in the Logview Summary after the SQL job runs.
Syntax
create function <function_name> as '<package_to_class>' using '<resource_list>';Parameters
| Parameter | Required | Description |
|---|---|---|
function_name | Yes | The name of the UDF to create. Must be unique in the project. |
package_to_class | Yes | The fully qualified class path of the UDF. Case-sensitive. Must be enclosed in single quotation marks ('). For a Java UDF, specify the full path from the top-level package name to the UDF class name. For a Python UDF, use the script_name.ClassName format. |
resource_list | Yes | A comma-separated list of resource names, enclosed in single quotation marks ('). Must include the resource containing UDF code. If the UDF calls the Distributed Cache API to read resource files, also include those resource files. To reference a resource from another project, use the <project_name>/resources/<resource_name> format. |
Note
Python resource names are not case-sensitive at the underlying level. The underlying resource name is determined by the name used during the first upload. For example, if you first upload pyudf_test.py and later rename it to PYUDF_TEST.py, the underlying resource name remains pyudf_test.py. When registering the UDF, the class name must match the underlying name: pyudf_test.SampleUDF. Run list resources; to check the underlying names of all resources.
Examples
Example 1: Java UDF
Create the my_lower function using the Java class org.alidata.odps.udf.examples.Lower in my_lower.jar.
create function my_lower as 'org.alidata.odps.udf.examples.Lower' using 'my_lower.jar';Example 2: Python UDF from another project
Create the my_lower function using the Python class MyLower in the pyudf_test.py script, which is a resource in test_project.
create function my_lower as 'pyudf_test.MyLower' using 'test_project/resources/pyudf_test.py';Example 3: Java user-defined table-valued function (UDTF) with multiple resources
Create test_udtf using the class com.aliyun.odps.examples.udf.UDTFResource in udtfexample1.jar. The function also depends on a file resource, a table resource, and an archive resource.
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
Removes an existing UDF from a MaxCompute project.
Syntax
drop function <function_name>;Parameters
| Parameter | Required | Description |
|---|---|---|
function_name | Yes | The name of the UDF to delete. |
Example
-- Delete the my_lower function.
drop function my_lower;View a UDF
Returns the metadata of a specific UDF: its name, owner, creation time, class, and resource list.
Syntax
desc function <function_name>;Parameters
| Parameter | Required | Description |
|---|---|---|
function_name | Yes | The name of the UDF to inspect. |
Output fields
| Field | Description |
|---|---|
Name | The name of the UDF. |
Owner | The account that registered the UDF. |
Created Time | The time the UDF was created. |
Class | The class path of the UDF. Case-sensitive. |
Resources | The resource list associated with the UDF. |
Example
-- View details of the my_lower function.
desc function my_lower;Output:
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.jarList all UDFs
Returns all UDFs registered in a MaxCompute project.
Syntax
Use either of the following commands:
list functions [-p <project_name>];show functions;Parameters
| Parameter | Required | Description |
|---|---|---|
project_name | No | The name of the MaxCompute project to query. If omitted, the current project is used. Only supported with list functions. |
Example
list functions;Output:
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.jarThe output columns are:
| Column | Description |
|---|---|
Name | The name of the UDF. |
Owner | The account that registered the UDF. |
Create Time | The time the UDF was created. |
Class | The class path of the UDF. Empty if the class information is unavailable. |
Resources | The resources associated with the UDF. Empty if no resources are recorded. |
List all built-in functions
Returns information about built-in functions available in MaxCompute, including function signatures and supported data types.
Note
This command requires MaxCompute client V0.43.0 or later.
Syntax
show builtin functions [<function_name>];Parameters
| Parameter | Required | Description |
|---|---|---|
function_name | No | The name of a specific built-in function to look up. If omitted, all built-in functions are returned. |
Example
show builtin functions;Output (partial):
ID = 20230307081023424gef2hwowr1
::ABS SCALAR DECIMAL(?,?) ABS(DECIMAL(?,?) arg0),DOUBLE ABS(DOUBLE arg0),BIGINT ABS(BIGINT arg0),INT ABS(INT arg0)
::ACOS SCALAR DOUBLE ACOS(DOUBLE arg0),DOUBLE ACOS(DECIMAL(?,?) arg0)
::ADD_MONTHS SCALAR STRING ADD_MONTHS(DATE arg0, BIGINT arg1),STRING ADD_MONTHS(TIMESTAMP arg0, BIGINT arg1),STRING ADD_MONTHS(STRING 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 null
::ARRAY_JOIN SCALAR STRING ARRAY_JOIN(ARRAY<STRING> arg0, STRING arg1, STRING arg2),STRING ARRAY_JOIN(ARRAY<STRING> arg0, STRING arg1)
::ARRAY_MAX SCALAR T ARRAY_MAX(ARRAY<T> arg0)
::ARRAY_MIN SCALAR T ARRAY_MIN(ARRAY<T> arg0)
::ARRAY_NORMALIZE SCALAR ARRAY<FLOAT> ARRAY_NORMALIZE(ARRAY<FLOAT> arg0, FLOAT arg1),ARRAY<DOUBLE> ARRAY_NORMALIZE(ARRAY<DOUBLE> arg0, DOUBLE arg1)
::ARRAY_POSITION SCALAR BIGINT ARRAY_POSITION(ARRAY<T> arg0, T arg1, BIGINT arg2),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(DECIMAL(?,?) arg0),DOUBLE ATAN(DOUBLE arg0)
::ATAN2 SCALAR DOUBLE ATAN2(DECIMAL(?,?) arg0, DECIMAL(?,?) arg1),DOUBLE ATAN2(DOUBLE arg0, DOUBLE 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)
::BITWISE_AND_AGG AGGREGATOR BIGINT BITWISE_AND_AGG([DISTINCT] BIGINT arg1)
::BITWISE_OR_AGG AGGREGATOR BIGINT BITWISE_OR_AGG([DISTINCT] BIGINT arg1)
::BITXOR SCALAR BIGINT BITXOR(BIGINT arg0, BIGINT arg1)
::BROUND SCALAR DOUBLE BROUND(DOUBLE arg0, BIGINT arg1),DOUBLE BROUND(DOUBLE arg0)
......Each line in the output follows the format: function_name function_type signature(s). The function type is one of SCALAR, AGGREGATOR, or WINDOW.