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.

This feature requires Spark 3.4 or higher.

Example

-- Create Function
CREATE 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 FUNCTION mydb.simple_udf;