ByteHouse Cloud Data Warehouse Edition supports user-defined functions (UDF, User Defined Functions), which allow you to process data by calling any external executable program or script.
ByteHouse Cloud Data Warehouse Edition supports the following types of UDF:

1. Lambda UDF: user-defined Lambda function.
2. Python UDF: functions defined using the Python language that return a result.

Lambda UDF #

Users can create user-defined functions through a Lambda expression. The expression must consist of function parameters, constants, operators, or other function calls.

Syntax #

CREATE FUNCTION name AS (parameter0, ...) -> expression

Limitations #

There is no limit to the number of function parameters.
Restrictions:

• The function name must be unique and must not duplicate any existing user-defined function or system function.
• Defining recursive functions is not allowed.
• All variables used in the function must be specified in its parameter list.

If the UDF does not meet any of the above restrictions, an exception may occur.

Usage examples #

Example 1:

CREATE FUNCTION linear_equation AS (x, k, b) -> k*x + b; 
SELECT number, linear_equation(number, 2, 1) FROM numbers(3);

Result 1:

┌─number─┬─plus(multiply(2, number), 1)─┐ 
│      0 │                            1 │ 
│      1 │                            3 │ 
│      2 │                            5 │ 
└────────┴──────────────────────────────┘

Example 2:
In the following query, the conditional function if is called within the user-defined function:

CREATE FUNCTION parity_str AS (n) -> if(n % 2, 'odd', 'even'); 
SELECT number, parity_str(number) FROM numbers(3);

Result 2:

┌─number─┬─if(modulo(number, 2), 'odd', 'even')─┐ 
│      0 │ even                                 │ 
│      1 │ odd                                  │ 
│      2 │ even                                 │ 
└────────┴──────────────────────────────────────┘

Python UDF #

Python UDF is a Python script invoked by ByteHouse. A Python UDF contains a Python program that runs when the function is called and returns a single value.
When a query calls a Python UDF, the following steps occur at runtime:

1. The function converts the input parameters to Python Numpy data types.
2. The function passes the converted input parameters and then runs the Python program.
3. The Python code returns a single value. The data type of the return value must correspond to the RETURNS data type specified in the function definition.
4. The function converts the Python return value to the defined data type and then returns this value to the query.

Usage restrictions #

• Python UDF is a Beta feature. Please contact the ByteHouse product manager to obtain allowlist privileges.
• Please ensure that the ByteHouse engine version you are using is v2.3.x or above. You can log in to the ByteHouse Cloud Data Warehouse Edition console, click Tenant Management at the top, and view the engine version you are using on the Basic Information page.
  
• Currently, only Python 3.9 is supported.

Data types #

| | | | \

Syntax #

Create function

CREATE FUNCTION function_name [TYPE udf_type] [RETURNS data_type] [FLAGS flags] LANGUAGE PYTHON AS 
$custom_tag$ 

class function_name(): 
    def entry(self, output, inputs): 
        expression 
        
$custom_tag$ 

# udf_type: SCALAR (default), AGGREGATE (Not supported for current release) 
# data_type: use the type of the first input column if not set, check 'Data Types' section above for all supported types

• $custom_tag$: Between the $$ symbols, you can use any custom tag, just ensure you terminate it with the same tag. (Refer to PostgreSQL 4.1.2.4)
  For example, here are two different ways to specify the string "Dianne's horse":

  $$Dianne's horse$$ 
  $SomeTag$Dianne's horse$SomeTag$
  

  All content within $tag$ is treated as Python code. No Python syntax check is performed during Python UDF creation.

DROP function

DROP FUNCTION [IF EXISTS] function_name;

• IF EXISTS is optional; if the function does not exist, it can prevent an error from occurring. Under normal circumstances, if the function does not exist and you try to delete it, the system will report an error.

SHOW function

View all UDF functions.

SHOW functions;  -- View all UDF

SHOW CREATE function

View the definition of a specific UDF.

SHOW CREATE FUNCTION udf_name;

SELECT function

UDFs work the same as any other function in ByteHouse.
For example, to use a UDF that calculates the area of a circle:

drop function if exists area_of_circle; 

CREATE FUNCTION area_of_circle RETURNS Float64 LANGUAGE PYTHON AS 
$pikachu$ 
from iudf import IUDF 
from overload import overload 
from math import pi 

class area_of_circle(IUDF): 
    @overload 
    def process(radius): 
        return radius*radius*pi 
$pikachu$;

Usage example #

Example 1:

This UDF example will add the values of each row in this table and output the result.

• Create the database and table required for the UDF

CREATE DATABASE test; 
 
CREATE TABLE test.test_udf_int 
( 
    a Int64, 
    b Int64, 
    c Int64, 
    d Int64 
) 
ENGINE = CnchMergeTree() 
PRIMARY KEY a 
ORDER BY a; 

INSERT INTO test.test_udf_int(a, b, c, d) 
VALUES (0, 0, 0, 0 ) (1, 1, 1, 1) (2, 2, 2, 2);

• Create UDF

Next, use CREATE FUNCTION to create the UDF function. This is an example of how to write the entry function in Python, using [code] as the delimiter.
ByteHouse is a column-based database, but usually you want to calculate for each row. In this example, the entry function uses an iterator to loop through each row and sums the four input values in each row to obtain the output value.

CREATE FUNCTION f_plus_int RETURNS Int64 LANGUAGE PYTHON AS 
$code$ 
import numpy as np 

class f_plus_int: 
    def entry(self, output, inputs): 
        nargs = len(inputs) 

        it = np.nditer([output] + inputs, 
                ['refs_ok', 'buffered'], [['writeonly']] + [['readonly']]*nargs) 

        with it: 
            for out, *ins in it: 
                out[...] = self.process(*[x.item() for x in ins]) 
                
    def process(self, x1, y1, x2, y2): 
       return x1 + y2 + x2 + y2 
       
$code$;

Alternatively, you can use a fully column-based method provided by Python Numpy. It performs the same actions as f_plus_int.
Caution: The performance of the column-based method is superior to using loops.

CREATE FUNCTION f_plus_int RETURNS Int64 LANGUAGE PYTHON AS 
$code$ 
import numpy as np 

class f_plus_int: 
    def entry(self, output, inputs): 
        np.add.reduce(inputs, out=output) 
$code$;

• Call UDF

After the Python UDF function is created, it can be called like a general built-in function.

select f_plus_int(a,b,c,d) from test.test_udf_int;

• Result

┌─f_plus_int(a, b, c, d)─┐ 
│                           0 │ 
│                           4 │ 
│                           8 │ 
└─────────────────────────────┘

• Delete UDF

drop function f_plus_int;

Example 2:

• Create the database and table for the UDF

CREATE TABLE test.py_fixedstring 
( 
    a FixedString(2), 
    b FixedString(3) 
) 
ENGINE = CnchMergeTree() 
ORDER BY b; 

INSERT INTO test.py_fixedstring(a, b) 
VALUES ('aa', 'aaa') ('bb', 'bbb') ('cc', 'ccc') ('dd', 'ddd') ('ee', 'eee') ('ff', 'fff') ('gg', 'ggg') ('ab', 'abc') ('cb', 'cba') ('tt', 'zzz');

• Create UDF

For return types like FixedString, you must specify the length of the returned string.

CREATE FUNCTION py_fixed_string 
RETURNS FixedString(5) 
LANGUAGE PYTHON AS 
$code$ 
import numpy as np 

class py_fixed_string: 
    def entry(self, output, inputs): 
        nargs = len(inputs) 

        it = np.nditer([output] + inputs, 
                ['refs_ok', 'buffered'], [['writeonly']] + [['readonly']]*nargs) 

        with it: 
            for out, *ins in it: 
                out[...] = self.process(*[x.item() for x in ins]) 

    def process(self, a, b): 
        return a + b 
$code$;

• Call UDF

select py_fixed_string(a, b) from test.py_fixedstring;

• Result

┌─py_fixed_string(a, b)─┐ 
│ aaaaa                      │ 
│ ababc                      │ 
│ bbbbb                      │ 
│ cbcba                      │ 
│ ccccc                      │ 
│ ddddd                      │ 
│ eeeee                      │ 
│ fffff                      │ 
│ ggggg                      │ 
│ ttzzz                      │ 
└────────────────────────────┘

• Delete UDF

drop function py_fixedstring;

Overloaded function:

In addition, ByteHouse provides overload.py, which allows you to create multiple UDFs in a single CREATE FUNCTION statement. Each function can accept different Params as input, as shown below:

CREATE FUNCTION f_plus_int 
RETURNS Date 
LANGUAGE PYTHON AS 
$code$ 
from iudf import IUDF 
from overload import overload 
 
class f_plus_int(IUDF): 
    @overload 
    def process(a): 
        return a + 1 

    @overload 
    def process(a, b): 
        return a + b 
$code$;

agate==1.6.3 
aiohttp==3.8.3 
aiosignal==1.3.1 
async-timeout==4.0.2 
asynctest==0.13.0 
attrs==22.1.0 
Babel==2.11.0 
base58==2.1.1 
bitarray==2.6.0 
certifi==2022.6.15 
cffi==1.15.1 
charset-normalizer==2.1.0 
click==8.1.3 
clickhouse-connect==0.4.0 
clickhouse-driver==0.2.4 
colorama==0.4.5 
cytoolz==0.12.0 
dbt-clickhouse==1.3.0 
dbt-core==1.3.0 
dbt-extractor==0.4.1 
eth-abi==3.0.1 
eth-account==0.7.0 
eth-hash==0.3.3 
eth-keyfile==0.6.0 
eth-keys==0.4.0 
eth-rlp==0.3.0 
eth-typing==3.2.0 
eth-utils==2.0.0 
ethereum-abi-utils==0.4.7 
ethereum-tester==0.1.0b3 
ethereum-utils==0.6.2 
frozenlist==1.3.3 
future==0.18.2 
hexbytes==0.3.0 
hologram==0.0.15 
idna==3.3 
importlib-metadata==5.0.0 
ipfshttpclient==0.7.0 
isodate==0.6.1 
Jinja2==3.1.2 
jsonschema==3.2.0 
leather==0.3.4 
llvmlite==0.39.1 
Logbook==1.5.3 
lru-dict==1.1.8 
MarkupSafe==2.1.1 
mashumaro==3.0.4 
minimal-snowplow-tracker==0.0.2 
msgpack==1.0.4 
multiaddr==0.0.9 
multidict==6.0.2 
netaddr==0.8.0 
networkx==2.8.8 
numba==0.56.4 
numpy==1.23.2 
packaging==21.3 
pandas==1.5.1 
parsedatetime==2.4 
parsimonious==0.8.1 
pathspec==0.9.0 
protobuf==4.21.9 
pycparser==2.21 
pycryptodome==3.15.0 
pylru==1.2.1 
pyparsing==3.0.9 
pyrsistent==0.19.2 
pysha3==1.0.2 
python-dateutil==2.8.2 
python-slugify==6.1.2 
pytimeparse==1.1.8 
pytz==2022.6 
pytz-deprecation-shim==0.1.0.post0 
PyYAML==6.0 
requests==2.28.1 
rlp==3.0.0 
scipy==1.9.3 
six==1.16.0 
sqlparse==0.4.3 
text-unidecode==1.3 
toolz==0.12.0 
typing_extensions==4.4.0 
tzdata==2022.6 
tzlocal==4.2 
urllib3==1.26.11 
varint==1.0.2 
web3==3.16.3 
websockets==10.4 
Werkzeug==2.2.2 
yarl==1.8.1 
zipp==3.10.0