Alibaba Cloud MaxCompute Function

The big data computing service (MaxCompute, formerly called ODPS) is a fast and fully hosted GB/TB/PB level data warehouse solution.

By Editorial Staff Last updated Oct 21, 2019

The big data computing service (MaxCompute, formerly called ODPS) is a fast and fully hosted GB/TB/PB level data warehouse solution. MaxCompute supports a variety of classic distributed computing models that enable you to solve massive data calculation problems while reducing business costs, and maintaining data security.

MaxCompute seamlessly integrates with DataWorks, which provides one-stop data synchronization, task development, data workflow development, data operation and maintenance, and data management for MaxCompute.

MaxCompute is mainly used to store and compute batches of structured data. It provides a massive range of data warehouse solutions as well as big data analysis and modeling services. As data collection techniques are becoming increasingly diverse and comprehensive,industries are amassing larger and larger volumes of data. The scale of data has increased to the level of massive data (100 GB, TB and even PB) that traditional software industry can not carry.

Given these massive data volumes, the limited processing capacity of a single server has prompted analysts to move towards distributed computing. However, distributed computing models are not easy to maintain and demand highly-qualified data analysts. When using a distributed model,data analysts not only need to understand their business needs, but also must be familiar with the underlying computing model. The purpose of MaxCompute is to provide you with a convenient way of analyzing and processing mass data, and you can achieve the purpose of analyzing large data without having to care about the details of distributed computing.

Product advantage

Large-scale computing and storage
MaxCompute is suitable for the storage and processing of large volumes of data (up to PB-level).
Multiple computational models
MaxCompute supports data processing methods based on SQL, MapReduce, Graph, MPI iteration algorithm, and other programming models.
Strong data security
MaxCompute has stabilized all offline analysis for all Alibaba Group’s business for more than seven years, providing multilayer sandbox protection and monitoring.
Cost-effective
MaxCompute can help reduce procurement costs by 20%-30% compared with on-premises private cloud models.

MaxCompute provides SQL computing capabilities. In MaxCompute SQL, you can use the system’s built-in functions to perform common computing and counting tasks. If the built-in functions do not meet your requirements, you can use the Java programming interface provided by MaxCompute to develop user-defined functions (UDFs).

UDFs can be divided into scalar valued functions, user-defined aggregate functions (UDAFs), and user-defined tables functions (UDTFs).

After writing the code for a UDF, you must compile the code into a JAR package and upload this package to MaxCompute. Then, you can register the UDF in MaxCompute.

Mathematical functions:

ABS

Function definition:

Double abs(Double number)
Bigint abs(Bigint number)
Decimal abs(Decimal number)

Usage:

Returns an absolute value.

Parameter description:

number: It is any number of Type Double, Bigint, or Decimal.

If the input is Bigint and return Bigint.
If the input is Double, return Double.
If the input is Decimal, return Decimal.

If the input is String, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

The return result depends on the type of input parameter. Example, if the input is null, return null.

Example:

abs(null) = null
abs(-1) = 1
abs(-1.2) = 1.2
abs("-2") = 2.0
abs(122320837456298376592387456923748) = 1.2232083745629837e32

The following is a completed ABS function example used in SQL. The use methods of other built-in functions (except Window Function and Aggregation Function) are similar.

select abs(id) from tbl1;
-- Take the absolute value of the id field in tbl1.

ACOS

Function definition:

Double acos(Double number)
Decimal acos(Decimal number)

Usage:

Calculates the inverse cosine of a number.

Parameter description:

number: Double or Decima type, -1<=number <=1. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type, the value is between 0 to π. If number is null, return null.

Example:

acos("0.87") = 0.5155940062460905
acos(0) = 1.5707963267948966

ASIN

Function definition:

Double asin(Double number)
Decimal asin(Decimal number)

Usage:

Calculates the inverse sine function of number.

Parameter description:

number: Double or Decima type, -1<=number <=1. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type, the value is between -π/2 to π/2. If the number is null, return null.

Example:

asin(1) = 1.5707963267948966
asin(-1) = -1.5707963267948966

ATAN

Function definition:

Double atan(Double number)

Usage:

Calculates the back-cut function of number.

Parameter description:

Number: Double type, if the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double type, the value is between -π/2 to π/2. If the number is null, return null.

Example:

atan(1) = 0.7853981633974483
atan(-1) = -0.7853981633974483

CEIL

Function definition:

Bigint ceil(Double value)
Bigint ceil(Decimal value)

Usage:

This function returns the smallest integral value not less than the argument.

Parameter description:

value: Double or Decimal type, If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Bigint type. If the number is null, return null.

Example:

ceil(1.1) = 2
ceil(-1.1) = -1

CONV

Function definition:

String conv(String input, Bigint from_base, Bigint to_base)

Usage:

Converts a number into a Hexadecimal number.

Parameter description:

input: an integer to be converted, represented by String. Accept the implicit conversion of Bigint and Double.
from_base, to_base: Decimal value, the acceptable values can be 2, 8, 10 and 16. Accept the implicit conversion of String and Double.

Return value:

Returns the String type. If the number is null, return null. The conversion process runs at a 64-bit precision. An exception is thrown when overflow occurs. If the input is a negative value (begin with ‘-’), an exception is thrown. If the input value is a decimal, it is converted to an integer before hex conversion. The decimal part is excluded.

Example:

conv('1100', 2, 10) = '12'
conv('1100', 2, 16) = 'c'
conv('ab', 16, 10) = '171'
conv('ab', 16, 16) = 'ab'

COS

Function definition:

Double cos(Double number)
Decimal cos(Decimal number)

Usage:

Input is the radian value.

Parameter description:

number: Double or Decimal type. If the input is String, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

Example:

cos(3.1415926/2)=2.6794896585028633e-8
cos(3.1415926)=-0.9999999999999986

COSH

Function definition:

Double cosh(Double number)
Decimal cosh(Decimal number)

Usage:

It is the Hyperbolic cosine function

Parameter description:

number: Double or Decimal type. If the input is String, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

COT

Function definition:

Double cot(Double number)
Decimal cot(Decimal number)

Usage:

Inputs the radian value.

Parameter description:

number: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

EXP

Function definition:

Double exp(Double number)
Decimal exp(Decimal number)

Usage:

It is the Exponential function.

Return value:

Returns the exponent value of number.

Parameter description:

number: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

FLOOR

Function definition:

Bigint floor(Double number)
Bigint floor(Decimal number)

Usage:

Returns the largest integral value not greater than the argument.

Parameter description:

number: Double or Decimal type. If the input is String or Bigint type, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Bigint type. If the input is null, return null.

Example:

floor(1.2)=1
floor(1.9)=1
floor(0.1)=0
floor(-1.2)=-2
floor(-0.1)=-1
floor(0.0)=0
Floor (-0.0) = 0

LN

Function definition:

Double ln(Double number)
Decimal ln(Decimal number)

Usage:

Returns the natural logarithm of the number.

Parameter description:

number: Double or Decimal type.

If the input is String or Bigint type, it is converted to Double by implicit conversion. If the input is another type, an error occurs.
If the number is null, return null. If number is negative or 0, an exception is thrown.

Return value:

Returns the Double or Decimal type.

LOG

Function definition:

Double log(Double base, Double x)
Decimal log (decimal base, decimal X)

Usage:

Returns the logarithm of x whose base number is base.

Parameter description:

base: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.
x: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the logarithm value of Double or Decimal type.

If base or x is null, return null.
If one of base or x is negative or zero, it causes abnormality.
If base is 1, it also causes abnormality.

POW

Function definition:

Double pow(Double x, Double y)
Decimal pow(Decimal x, Decimal y)

Usage:

Return x to the yth power, that is x^y.

Parameter description:

X: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.
Y: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If X or Y is null, return null.

RAND

Function definition:

Double rand(Bigint seed)

Usage:

Return a random number (that changes from row to row), Specifying the seed makes sure the generated random number sequence is deterministic, Return value range is from 0 to 1.

Parameter description:

seed: Bigint type, random number seed, to determine starting values of the random number sequence.

Return Value:

Returns the Double type.

Example:

select rand() from dual;
select rand(1) from dual;

ROUND

Function definition:

Double round(Double number, [Bigint Decimal_places])
Decimal round(Decimal number, [Bigint Decimal_places])

Usage:

Four to five homes to the specific decimal point position.

Parameter description:

number: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.
Decimal_place: A Bigint type constant, four to five homes to the decimal point position. If it is other type, an exception is thrown. If you exclude it, it indicates four to five homes into a single digit. The default value is zero

Return value:

Returns the Double or Decimal type. If number or Decimal_places is null, return null.

Example:

round(125.315) = 125.0
round(125.315, 0) = 125.0
Round (125.315, 1) = 125.3
round(125.315, 2) = 125.32
round(125.315, 3) = 125.315
round(-125.315, 2) = -125.32
round(123.345, -2) = 100.0
round(null) = null
round(123.345, 4) = 123.345
round(123.345, -4) = 0.0

SIN

Function definition:

Double sin(Double number)
Decimal sin(Decimal number)

Usage:

Calculates the sine function of number, the input is the radian value.

Parameter description:

number: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

SINH

Function definition:

Double sinh(Double number)
Decimal sinh(Decimal number)

Usage:

Calculates the hyperbolic sine function of number.

Parameter description:

number: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

SQRT

Function definition:

Double sqrt(Double number)
Decimal sqrt(Decimal number)

Usage:

Calculates the square root of number.

Parameter description:

number: Double or Decimal type, must be greater than zero, if it is less than zero, an exception occur. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

TAN

Function definition:

Double tan(Double number)
Decimal tan(Decimal number)

Usage:

Calculates the tangent function of the number, the input is the radian value.

Parameter description:

number: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

TANH

Function definition:

Double tanh(Double number)
Decimal tanh(Decimal number)

Usage:

Calculates the hyperbolic tangent function of number.

Parameter description:

number: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

TRUNC

Function definition:

Double trunc(Double number[, Bigint Decimal_places])
Decimal trunc(Decimal number[, Bigint Decimal_places])

Usage:

This function is used to intercept the input number to a specified decimal point place.

Parameter description:

number: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.
Decimal_places: a Bigint type constant, the decimal point place to intercept the number. Other types are converted to Bigint. If this parameter is excluded, default to intercept to single digit.

Return value:

Returns the Double or Decimal type. If the number or Decimal_places is NULL, return NULL.

Example:

trunc(125.815) = 125.0
trunc(125.815, 0) =125.0
trunc(125.815, 1) = 125.80000000000001
trunc(125.815, 2) = 125.81
trunc(125.815, 3) = 125.815
trunc(-125.815, 2) = -125.81
trunc(125.815, -1) = 120.0
trunc(125.815, -2) = 100.0
trunc(125.815, -3) = 0.0
trunc(123.345, 4) = 123.345
trunc(123.345, -4) = 0.0

Maxcomputerte2.0 New Extended Mathematical Functions

With the upgrade to MaxCompute 2.0, some mathematical functions have been added to the product. If a new function uses a new data type, add the following set statement before using the new functions SQL statement:

set odps.sql.type.system.odps2=true;

The new extended functions are described as follows.

LOG2

Function definition:

Double log2(Double number)
Double log2(Decimal number)

Usage:

Returns the log base 2 of a specific number.

Parameter description:

number: Double or Decimal type.

Return Value:

Returns the Double type. If the input is zero or null, the returned value is null.

The example is as follows:

log2(null)=null
log2(0)=null
log2(8)=3.0

LOG10

Function definition:

Double log10(Double number)
Double log10(Decimal number)

Usage:

Returns the log base 10 of the specific number.

Parameter description:

number: Double or Decimal type.

Return Value:

Returns the Double type. If the input is zero or null, the returned value is null.

The example is as follows:

log10(null)=null
log10(0)=null
log10(8)=0.9030899869919435

BIN

Function definition:

String bin(Bigint number)

Usage:

Returns the binary code expression for the specific number.

Parameter description:

number: Bigint type.

Return value:

String type. If the input is zero, then zero is returned; if the input is null, null is returned.

Example:

bin(0)='0'
bin(null)='null'
bin(12)='1100'

HEX

Function definition:

String hex(Bigint number) 
String hex(String number)
String hex (binary number)

Usage:

This function is used to converts integers or characters to hexadecimal format.

Parameter description:

number: If number is of the Bigint type, the hexadecimal format of the number is returned. If this variable is a String type, the hexadecimal format of the string is returned.

Return value:

Returns the String type. If the input is zero, then zero is returned; if the input is null, an exception is returned.

Example:

hex(0)=0
hex('abc')='616263'
hex(17)='11'
hex('17')='3137'
hex(null) results in an exception and returns failed.

UNHEX

Function definition:

BINARY unhex(String number)

Usage:

Returns the string represented by a given hexadecimal string.

Parameter description:

number: A hexadecimal string.

Return value:

Returns the Binary type. If the input is zero, failed is returned. If the input is null, null is returned.

Example:

Unhex ('616263') = 'abc'
unhex(616263)='abc'

RADIANS

Function definition:

Double radians(Double number)

Usage:

This function is used to converts degrees to radians.

Parameter description:

number: Double type.

Return value:

Returns the Double type, if the input is null, null is returned.

Example:

radians(90)=1.5707963267948966
radians(0)=0.0
radians(null)=null

DEGREES

Function definition:

Double degrees(Double number) 
Double degrees(Decimal number)

Usage:

This function is used to converts radians to degrees.

Parameter description:

number: Double or Decimal type.

Return value:

Returns Double data type. If the input is null, null is returned.

Example:

degrees(1.5707963267948966)=90.0
degrees(0)=0.0
Degrees (null) = NULL

SIGN

Function definition:

Double sign(Double number)
Double sign(Decimal number)

Usage:

Applies the sign of the input data. 1.0 indicates a positive number and -1.0 indicates a negative number. Otherwise, 0.0 is returned.

Parameter description:

number: Double or Decimal type.

Return value:

Returns Double data type. If the input is 0, 0.0 is returned. If the input is null, null is returned.

Example:

sign(-2.5)=-1.0
Sign (2.5) = 1.0
sign(0)=0.0
sign(null)=null

E

Function definition:

Double e()

Usage:

This function is used to return the e value.

Return Value:

Returns the Double type.

Example:

e()=2.718281828459045

PI

Function definition:

Double pi()

Usage:

This function is used to return the π value.

Return Value:

Returns the Double type.

Example:

pi()=3.141592653589793

FACTORIAL

Function definition:

Bigint factorial(Int number)

Usage:

This function is used to return the factorial for the specific number.

Parameter description:

number: Int-type data, range: [0 –20].

Return value:

Returns the Bigint type, if the input is zero, one is returned. If the input is null or outside the range [0 –20], null is returned.

Example:

factorial(5)=120 --5! = 5*4*3*2*1 = 120

CBRT

Function definition:

Double cbrt(Double number)

Usage:

This function is used to return the cube root.

Parameter description:

number: Double type.

Return value:

Returns Double data type. If the input is null, null is returned.

Example:

cbrt(8)=2
cbrt(null)=null

SHIFTLEFT

Function definition:

Int shiftleft(Tinyint|Smallint|Int number1, Int number2)
Bigint shiftleft(Bigint number1, Int number2)

Usage:

Shifts to the left by a given number of places (<<).

Parameter description:

number1: Tinyint|Smallint|Int|Bigint integer.
number2: An Int integer.

Return value:

Returns the Int or Bingint type.

Example:

shiftleft(1,2)=4  --Shifts the binary value of 1 two places to the left (1<<2,0001 shifted to 0100)
shiftleft(4,3)=32  --Shifts the binary value of 4 three places to the left (4<<3,0100 shifted to 10,0000)

SHIFTRIGHT

Function definition:

Int shiftright(Tinyint|Smallint|Int number1, Int number2)
Bigint shiftright(Bigint number1, Int number2)

Usage:

This function is used for shifts right by a given number of places (>>).

Parameter description:

number1: Tinyint|Smallint|Int|Bigint integer.
number2: An Int integer.

Return value:

Returns the Int or Bigint type.

Example:

shiftright(4,2)=1 -- Shifts the binary value of 4 two places to the right (4>>2,0100 shifted to 0001)
shiftright(32,3)=4 -- Shifts the binary value of 32 three places to the right (32>>3,100000 shifted to 0100)

SHIFTRIGHTUNSIGNED

The command format is as follows:

Int shiftrightunsigned(Tinyint|Smallint|Int number1, Int number2)
Bigint shiftrightunsigned(Bigint number1, Int number2)

The command description is as follows:

This function is used for unsigned right shift by a given number of places (>>>).

Parameter description:

number1: Tinyint|Smallint|Int|Bigint integer.
number2: An Int integer.

Return value:

Returns the Int or Bigint type.

Example:

shiftrightunsigned(8,2)=2 -- Shifts the unsigned binary value of 8 two places to the right (8>>>2,1000 shifted to 0010)
shiftrightunsigned(-14,2)=1073741820  -- Shifts the unsigned binary value of -14 two places to the right (-14>>>2, 11111111 11111111 11111111 11110010 shifted to 00111111 11111111 11111111 11111100)

Summary

In this blog, you’ve got to see a bit more about Alibaba Cloud MaxCompute Functions to take advantage of all of the features included in MaxCompute to help kickstart your data processing and analytics workflow.