Functions

PPL supports a wide range of built-in functions for data processing and analysis. These functions are organized into categories based on their functionality and can be used within PPL queries to manipulate and transform data.

• Aggregation functions:
  • COUNT.
  • SUM.
  • AVG.
  • MAX.
  • MIN.
  • VAR_SAMP.
  • VAR_POP.
  • STDDEV_SAMP.
  • STDDEV_POP.
  • DISTINCT_COUNT, DC.
  • DISTINCT_COUNT_APPROX.
  • EARLIEST.
  • LATEST.
  • TAKE.
  • PERCENTILE, PERCENTILE_APPROX.
  • MEDIAN.
  • FIRST.
  • LAST.
  • LIST.
  • VALUES.
• Collection functions:
  • ARRAY.
  • ARRAY_LENGTH.
  • FORALL.
  • EXISTS.
  • FILTER.
  • TRANSFORM.
  • REDUCE.
  • MVJOIN.
  • MVAPPEND.
  • SPLIT.
  • MVDEDUP.
  • MVFIND.
  • MVINDEX.
  • MVMAP.
  • MVZIP.
• Conditional functions:
  • ISNULL.
  • ISNOTNULL.
  • EXISTS.
  • IFNULL.
  • NULLIF.
  • IF.
  • CASE.
  • COALESCE.
  • ISPRESENT.
  • ISBLANK.
  • ISEMPTY.
  • EARLIEST.
  • LATEST.
  • REGEXP_MATCH.
• Type conversion functions:
  • CAST.
  • TOSTRING.
  • TONUMBER.
• Cryptographic functions:
  • MD5.
  • SHA1.
  • SHA2.
• Date and time functions:
  • ADDDATE.
  • ADDTIME.
  • CONVERT_TZ.
  • CURDATE.
  • CURRENT_DATE.
  • CURRENT_TIME.
  • CURRENT_TIMESTAMP.
  • CURTIME.
  • DATE.
  • DATE_ADD.
  • DATE_FORMAT.
  • DATETIME.
  • DATE_SUB.
  • DATEDIFF.
  • DAY.
  • DAYNAME.
  • DAYOFMONTH.
  • DAY_OF_MONTH.
  • DAYOFWEEK.
  • DAY_OF_WEEK.
  • DAYOFYEAR.
  • DAY_OF_YEAR.
  • EXTRACT.
  • FROM_DAYS.
  • FROM_UNIXTIME.
  • GET_FORMAT.
  • HOUR.
  • HOUR_OF_DAY.
  • LAST_DAY.
  • LOCALTIMESTAMP.
  • LOCALTIME.
  • MAKEDATE.
  • MAKETIME.
  • MICROSECOND.
  • MINUTE.
  • MINUTE_OF_HOUR.
  • MONTH.
  • MONTH_OF_YEAR.
  • MONTHNAME.
  • NOW.
  • PERIOD_ADD.
  • PERIOD_DIFF.
  • QUARTER.
  • SEC_TO_TIME.
  • SECOND.
  • SECOND_OF_MINUTE.
  • STRFTIME.
  • STR_TO_DATE.
  • SUBDATE.
  • SUBTIME.
  • SYSDATE.
  • TIME.
  • TIME_FORMAT.
  • TIME_TO_SEC.
  • TIMEDIFF.
  • TIMESTAMP.
  • TIMESTAMPADD.
  • TIMESTAMPDIFF.
  • TO_DAYS.
  • TO_SECONDS.
  • UNIX_TIMESTAMP.
  • UTC_DATE.
  • UTC_TIME.
  • UTC_TIMESTAMP.
  • WEEK.
  • WEEKDAY.
  • WEEK_OF_YEAR.
  • YEAR.
  • YEARWEEK.
• Expressions:
  • Arithmetic operators.
  • Predicate operators.
• IP address functions:
  • CIDRMATCH.
  • GEOIP.
• JSON functions:
  • JSON.
  • JSON_VALID.
  • JSON_OBJECT.
  • JSON_ARRAY.
  • JSON_ARRAY_LENGTH.
  • JSON_EXTRACT.
  • JSON_DELETE.
  • JSON_SET.
  • JSON_APPEND.
  • JSON_EXTEND.
  • JSON_KEYS.
• Mathematical functions:
  • ADD.
  • SUBTRACT.
  • MULTIPLY.
  • DIVIDE.
  • SUM.
  • AVG.
  • ACOS.
  • ASIN.
  • ATAN.
  • ATAN2.
  • CEIL.
  • CEILING.
  • CONV.
  • COS.
  • COSH.
  • COT.
  • CRC32.
  • DEGREES.
  • E.
  • EXP.
  • EXPM1.
  • FLOOR.
  • LN.
  • LOG.
  • LOG2.
  • LOG10.
  • MOD.
  • MODULUS.
  • PI.
  • POW.
  • POWER.
  • RADIANS.
  • RAND.
  • ROUND.
  • SIGN.
  • SIGNUM.
  • SIN.
  • SINH.
  • SQRT.
  • CBRT.
  • RINT.
• Relevance functions:
  • MATCH.
  • MATCH_PHRASE.
  • MATCH_PHRASE_PREFIX.
  • MULTI_MATCH.
  • SIMPLE_QUERY_STRING.
  • MATCH_BOOL_PREFIX.
  • QUERY_STRING.
• Statistical functions:
  • MAX.
  • MIN.
• String functions:
  • CONCAT.
  • CONCAT_WS.
  • LENGTH.
  • LIKE.
  • ILIKE.
  • LOCATE.
  • LOWER.
  • LTRIM.
  • POSITION.
  • REPLACE.
  • REVERSE.
  • RIGHT.
  • RTRIM.
  • SUBSTRING.
  • TRIM.
  • UPPER.
  • REGEXP_REPLACE.
• System functions:
  • TYPEOF.

Related documentation

• Aggregation functions
• Collection functions
• Conditional functions
• Type conversion functions
• Cryptographic functions
• Date and time functions
• Expressions
• IP address functions
• JSON functions
• Mathematical functions
• Relevance functions
• Statistical functions
• String functions
• System functions