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.
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 classorg.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 thetest_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 classcom.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';
- Example 1: Create the
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
The following result is returned:-- View the information of the my_lower function. desc function my_lower;
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
The following result is returned:list functions;
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
The following result is returned:show functions;
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 ......