This documentation is for an unreleased version of Apache Paimon. We recommend you use the latest stable version.

SQL Functions #

This section introduce all available Paimon Spark functions.

Built-in Function #

max_pt #

max_pt($table_name)

It accepts a string type literal to specify the table name and return a max-valid-toplevel partition value.

valid: the partition which contains data files
toplevel: only return the first partition value if the table has multi-partition columns

It would throw exception when:

the table is not a partitioned table
the partitioned table does not have partition
all of the partitions do not contains data files

Example

SELECT max_pt('t');
-- 20250101
 
SELECT * FROM t where pt = max_pt('t');
-- a, 20250101

Since: 1.1.0

User-defined Function #

Paimon Spark supports two types of user-defined functions: lambda functions and file-based functions.

This feature currently only supports the REST catalog.

Lambda Function #

Empowering users to define functions using Java lambda expressions, enabling inline, concise, and functional-style operations.

Example

-- Create Function
CALL sys.create_function(`function` => 'my_db.area_func',
  `inputParams` => '[{"id": 0, "name":"length", "type":"INT"}, {"id": 1, "name":"width", "type":"INT"}]',
  `returnParams` => '[{"id": 0, "name":"area", "type":"BIGINT"}]',
  `deterministic` => true,
  `comment` => 'comment',
  `options` => 'k1=v1,k2=v2'
);

-- Alter Function
CALL sys.alter_function(`function` => 'my_db.area_func',
  `change` => '{"action" : "addDefinition", "name" : "spark", "definition" : {"type" : "lambda", "definition" : "(Integer length, Integer width) -> { return (long) length * width; }", "language": "JAVA" } }'
);

-- Drop Function
CALL sys.drop_function(`function` => 'my_db.area_func');

File Function #

Users can define functions within a file, providing flexibility and modular support for function definition, only supports jar files now.

Currently, supports Spark or Hive implementations of UDFs and UDAFs, see Spark UDFs

This feature requires Spark 3.4 or higher.

Example

-- Create Function or Temporary Function (Temporary function should not specify database name)
CREATE [TEMPORARY] FUNCTION <mydb>.simple_udf
AS 'com.example.SimpleUdf' 
USING JAR '/tmp/SimpleUdf.jar' [, JAR '/tmp/SimpleUdfR.jar'];

-- Create or Replace Function
CREATE OR REPLACE FUNCTION <mydb>.simple_udf 
AS 'com.example.SimpleUdf'
USING JAR '/tmp/SimpleUdf.jar';
       
-- Describe Function
DESCRIBE FUNCTION [EXTENDED] <mydb>.simple_udf;

-- Drop Function
DROP [TEMPORARY] FUNCTION <mydb>.simple_udf;