内建函数概述

MaxCompute自身预置了诸多函数,可以满足大部分业务场景的数据处理需求。本文为您介绍MaxCompute提供的函数类型及函数使用相关说明。

背景信息

MaxCompute预置的函数类型如下。

函数类型

说明

日期与时间函数

支持处理DATE、DATETIME、TIMESTAMP等日期类型数据,实现加减日期、计算日期差值、提取日期字段、获取当前时间、转换日期格式等业务处理能力。

数学函数

支持处理BIGINT、DOUBLE、DECIMAL、FLOAT等数值类型数据,实现转换进制、数学运算、四舍五入、获取随机数等业务处理能力。

窗口函数

支持在指定的开窗列中,实现求和、求最大最小值、求平均值、求中间值、数值排序、数值偏移、抽样等业务处理能力。

聚合函数

支持将多条输入记录聚合成一条输出值,实现求和、求平均值、求最大最小值、求平均值、参数聚合、字符串连接等业务处理能力。

字符串函数

支持处理STRING类型字符串,实现截取字符串、替换字符串、查找字符串、转换大小写、转换字符串格式等业务处理能力。

复杂类型函数

支持处理MAP、ARRAY、STRUCT及JSON类型数据,实现去重元素、聚合元素、元素排序、合并元素等业务处理能力。其中JSON函数的使用限制请参见JSON函数的使用限制

加密函数

支持处理STRING、BINARY类型的表数据,实现加密、解密等业务处理能力。

其他函数

除上述函数之外,提供支持其他业务场景的函数。

各类型函数与开源函数的对照关系,请参见与Hive、MySQL、Oracle内建函数对照表

注意事项

在使用内建函数时,需要注意:

  • 内建函数的入参类型、入参数量、函数格式必须满足函数语法要求,否则MaxCompute无法成功解析函数,SQL运行会报错。

  • 如果内建函数的入参涉及2.0新数据类型(例如TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY),您需要打开2.0新数据类型开关,否则运行会报错。开关打开方式如下:

    • Session级别:您需要在SQL语句前加上set odps.sql.type.system.odps2=true;,与SQL语句一起提交执行。该配置仅对本次运行的SQL有效。

    • Project级别:Project Owner可根据需要对MaxCompute项目进行设置,等待10~15分钟后才会生效。该配置对后续运行的所有SQL有效。

      setproject odps.sql.type.system.odps2=true;
  • 当MaxCompute项目打开2.0新数据类型开关时,部分隐式类型转换会被禁用,包括STRING转换为BIGINT、STRING转换为DATETIME、DOUBLE转换为BIGINT、DECIMAL转换为DOUBLE、DECIMAL转换为BIGINT,都有精度损失或报错的风险。此时,您可以通过CAST函数执行强制转换解决该问题,或关闭2.0新数据类型开关。

  • 当自定义函数的名称与内建函数的名称相同时,自定义函数会覆盖同名的内建函数。例如,MaxCompute中存在一个名称为CONCAT的自定义函数,则系统默认会调用自定义的CONCAT,不会调用内建函数CONCAT。如果您希望调用内建函数,需要在内建函数前增加::符号,例如select ::concat('ab', 'c');

  • 如果MaxCompute项目的全局属性设置不同,内建函数运行的结果可能会不一致,您可以通过setproject;命令查看项目的全局属性信息。

JSON函数的使用限制

  • 目前支持的开发工具包括odpscmd客户端和Studio,暂不支持DataWorks,Dataphin等外围生态,如果需要跟外部系统做组合使用时,请先确认后再使用。使用odpscmd客户端和Studio时需要关注以下内容。

    使用odpscmd客户端

    使用Studio

    • 需要将客户端升级到最新版本,否则无法使用desc json_table命令。

    • 需要将客户端安装路径下的conf\odps_config.ini文件中的参数use_instance_tunnel设置为false,否则查询会报错

    Studio只支持查询JSON类型的操作,不支持上传、下载JSON类型数据。

  • 如果表存在其他引擎读取情况,比如Hologres等,目前不支持读取JSON数据类型。

  • 暂不支持对一张表新增JSON列。

  • 暂不支持对JSON类型的比较操作,也不支持对JSON类型进行ORDER BYGROUP BY或作为JOIN的key等。

  • 目前JSON NUMBER的整数和小数分别使用BIGINT和DOUBLE类型进行存储。当整数部分超出BIGINT范围时会溢出,小数转为DOUBLE时会损失精度。

  • 生成JSON类型数据所用的字符串里不支持UNICODE\u0000

  • Java UDF和Python UDF暂不支持JSON类型。

  • 目前JSON类型不支持Cluster表。

字符串函数的使用限制

以下函数只支持英文字符的转换:

  • TRIM/RTRIM/LTRIM:trimChars只支持英文字符。

  • REVERSE:在Hive模式下只支持英文字符。

  • SOUNDEX:仅转换英文字符。

  • TOLOWER:将字符串中的英文字符转换为小写形式。

  • TOUPPER:将字符串中的英文字符转换为大写形式。

  • INITCAP:将字符串中每个单词首字母(英文字符)转化为大写形式,其余为小写。