Пользовательские функции и таблицы Executable

CREATE FUNCTION sql_udf AS (a, b) -> a * b;

SELECT name, create_query FROM system.functions WHERE origin = 'SQLUserDefined';

SELECT sale_id, product_price, quantity, sql_udf(product_price, quantity) AS total_price FROM sales;

<functions>
    <function>
        <type>executable</type>
        <name>total_price_function</name>
        <return_type>UInt32</return_type>
        <return_name>total_price</return_name>
        <argument>
            <type>UInt32</type>
            <name>product_price</name>
        </argument>
        <argument>
            <type>UInt8</type>
            <name>quantity</name>
        </argument>
        <format>JSONEachRow</format>
        <command>total_price.py</command>
    </function>
</functions>

<user_defined_executable_functions_config>UDFs.xml</user_defined_executable_functions_config>

#!/usr/bin/python3

import sys
import json

if __name__ == '__main__':
    for line in sys.stdin:
        data = json.loads(line)
        price = data['product_price']
        quantity = data['quantity']
        result = {'total_price': price * quantity}
        print(json.dumps(result), end='\n')
        sys.stdout.flush()

$ sudo chmod +x /var/lib/clickhouse/user_scripts/total_price.py

SELECT name FROM system.functions WHERE origin = 'ExecutableUserDefined';

SELECT sale_id, product_price, quantity, total_price_function(product_price, quantity) AS total_price FROM sales;

#!/usr/bin/python3

import sys

if __name__ == '__main__':
     for line in sys.stdin:

           split_line = line.split()

           product = split_line[0]
           price = int(split_line[1])
           price_with_tax = price*1.2

           print(product + '\t' + str(price) + '\t' + str(price_with_tax) + '\n', end='')
           sys.stdout.flush()

$ sudo chmod +x /var/lib/clickhouse/user_scripts/add_tax.py

SELECT * FROM executable(
    'add_tax.py',
    TabSeparated,
    'product  String, price UInt32, price_with_tax Float32',
    (SELECT DISTINCT ON (product_name) product_name, product_price FROM sales));

CREATE TABLE prices_with_tax (product  String, price UInt32, price_with_tax Float32)
ENGINE = Executable('add_tax.py', TabSeparated, (SELECT DISTINCT ON (product_name) product_name, product_price FROM sales));

SELECT * FROM prices_with_tax;