Funciones definidas por el usuario (UDFs)

ClickHouse admite varios tipos de funciones definidas por el usuario (UDFs):

• Executable UDFs inician un programa o script externo (Python, Bash, etc.) y envían bloques de datos a través de STDIN / STDOUT. Úselas para integrar código o herramientas existentes sin recompilar ClickHouse. Tienen una mayor sobrecarga por llamada en comparación con las opciones dentro del proceso y son más adecuadas para lógica más pesada o cuando se requiere un entorno de ejecución diferente.
• SQL UDFs se definen con CREATE FUNCTION únicamente en SQL. Se insertan o expanden en el plan de consulta (sin cambiar de proceso), lo que las hace ligeras e ideales para reutilizar lógica de expresiones o simplificar columnas calculadas complejas.
• Experimental WebAssembly UDFs ejecutan código compilado en WebAssembly dentro de un entorno aislado en el proceso del servidor. Ofrecen una menor sobrecarga por llamada que los ejecutables externos, con mejor aislamiento que las extensiones nativas, lo que las hace adecuadas para algoritmos personalizados escritos en lenguajes que pueden compilarse a WASM (p. ej., C/C++/Rust).
• Experimental driver-based executable UDFs permiten que un "driver" proporcionado por el operador convierta, en el momento de crear la función, un fragmento de código proporcionado en CREATE FUNCTION ... ENGINE = DriverName(...) AS '...' en una UDF ejecutable (por ejemplo, compilándolo). Se basan en las UDFs ejecutables y requieren configuración del driver del lado del servidor.

ClickHouse puede invocar cualquier programa ejecutable externo o script para procesar datos.

La configuración de las funciones ejecutables definidas por el usuario puede encontrarse en uno o varios archivos XML. La ruta de la configuración se especifica en el parámetro user_defined_executable_functions_config.

La configuración de una función contiene las siguientes opciones:

El comando debe leer los argumentos desde STDIN y escribir el resultado en STDOUT. El comando debe procesar los argumentos de forma iterativa. Es decir, después de procesar un fragmento de argumentos, debe esperar al siguiente fragmento.

Cree test_function_sum manualmente, estableciendo execute_direct en 0 mediante una configuración XML o YAML.

<functions>
    <function>
        <type>executable</type>
        <name>test_function_sum</name>
        <return_type>UInt64</return_type>
        <argument>
            <type>UInt64</type>
            <name>lhs</name>
        </argument>
        <argument>
            <type>UInt64</type>
            <name>rhs</name>
        </argument>
        <format>TabSeparated</format>
        <command>cd /; clickhouse-local --input-format TabSeparated --output-format TabSeparated --structure 'x UInt64, y UInt64' --query "SELECT x + y FROM table"</command>
        <execute_direct>0</execute_direct>
        <deterministic>true</deterministic>
    </function>
</functions>

functions:
  type: executable
  name: test_function_sum
  return_type: UInt64
  argument:
    - type: UInt64
      name: lhs
    - type: UInt64
      name: rhs
  format: TabSeparated
  command: 'cd /; clickhouse-local --input-format TabSeparated --output-format TabSeparated --structure ''x UInt64, y UInt64'' --query "SELECT x + y FROM table"'
  execute_direct: 0
  deterministic: true

SELECT test_function_sum(2, 2);

┌─test_function_sum(2, 2)─┐
│                       4 │
└─────────────────────────┘

En este ejemplo, creamos una UDF que lee un valor de STDIN y lo devuelve como una cadena.

Cree test_function mediante una configuración XML o YAML.

<functions>
    <function>
        <type>executable</type>
        <name>test_function_python</name>
        <return_type>String</return_type>
        <argument>
            <type>UInt64</type>
            <name>value</name>
        </argument>
        <format>TabSeparated</format>
        <command>test_function.py</command>
    </function>
</functions>

functions:
  type: executable
  name: test_function_python
  return_type: String
  argument:
    - type: UInt64
      name: value
  format: TabSeparated
  command: test_function.py

Cree el archivo de script test_function.py dentro de la carpeta user_scripts (/var/lib/clickhouse/user_scripts/test_function.py con la ruta predeterminada).

#!/usr/bin/python3

import sys

if __name__ == '__main__':
    for line in sys.stdin:
        print("Value " + line, end='')
        sys.stdout.flush()

SELECT test_function_python(toUInt64(2));

┌─test_function_python(2)─┐
│ Value 2                 │
└─────────────────────────┘

Cree test_function_sum_json con argumentos con nombre y el formato JSONEachRow mediante configuración XML o YAML.

<functions>
    <function>
        <type>executable</type>
        <name>test_function_sum_json</name>
        <return_type>UInt64</return_type>
        <return_name>result_name</return_name>
        <argument>
            <type>UInt64</type>
            <name>argument_1</name>
        </argument>
        <argument>
            <type>UInt64</type>
            <name>argument_2</name>
        </argument>
        <format>JSONEachRow</format>
        <command>test_function_sum_json.py</command>
    </function>
</functions>

functions:
  type: executable
  name: test_function_sum_json
  return_type: UInt64
  return_name: result_name
  argument:
    - type: UInt64
      name: argument_1
    - type: UInt64
      name: argument_2
  format: JSONEachRow
  command: test_function_sum_json.py

Cree el archivo de script test_function_sum_json.py dentro de la carpeta user_scripts (/var/lib/clickhouse/user_scripts/test_function_sum_json.py con la ruta predeterminada).

#!/usr/bin/python3

import sys
import json

if __name__ == '__main__':
    for line in sys.stdin:
        value = json.loads(line)
        first_arg = int(value['argument_1'])
        second_arg = int(value['argument_2'])
        result = {'result_name': first_arg + second_arg}
        print(json.dumps(result), end='\n')
        sys.stdout.flush()

SELECT test_function_sum_json(2, 2);

┌─test_function_sum_json(2, 2)─┐
│                            4 │
└──────────────────────────────┘

Las funciones definidas por el usuario ejecutables pueden aceptar parámetros constantes configurados en la opción command (esto solo funciona para funciones definidas por el usuario de tipo executable). También requiere la opción execute_direct para garantizar que no exista ninguna vulnerabilidad por expansión de argumentos del shell.

<functions>
    <function>
        <type>executable</type>
        <execute_direct>true</execute_direct>
        <name>test_function_parameter_python</name>
        <return_type>String</return_type>
        <argument>
            <type>UInt64</type>
        </argument>
        <format>TabSeparated</format>
        <command>test_function_parameter_python.py {test_parameter:UInt64}</command>
    </function>
</functions>

functions:
  type: executable
  execute_direct: true
  name: test_function_parameter_python
  return_type: String
  argument:
    - type: UInt64
  format: TabSeparated
  command: test_function_parameter_python.py {test_parameter:UInt64}

Cree el archivo de script test_function_parameter_python.py dentro de la carpeta user_scripts (/var/lib/clickhouse/user_scripts/test_function_parameter_python.py con la ruta predeterminada).

#!/usr/bin/python3

import sys

if __name__ == "__main__":
    for line in sys.stdin:
        print("Parameter " + str(sys.argv[1]) + " value " + str(line), end="")
        sys.stdout.flush()

SELECT test_function_parameter_python(1)(2);

┌─test_function_parameter_python(1)(2)─┐
│ Parameter 1 value 2                  │
└──────────────────────────────────────┘

En este ejemplo, creamos un script de shell que multiplica cada valor por 2.

<functions>
    <function>
        <type>executable</type>
        <name>test_shell</name>
        <return_type>String</return_type>
        <argument>
            <type>UInt8</type>
            <name>value</name>
        </argument>
        <format>TabSeparated</format>
        <command>test_shell.sh</command>
    </function>
</functions>

functions:
  type: executable
  name: test_shell
  return_type: String
  argument:
    - type: UInt8
      name: value
  format: TabSeparated
  command: test_shell.sh

Cree el archivo de script test_shell.sh dentro de la carpeta user_scripts (/var/lib/clickhouse/user_scripts/test_shell.sh con la ruta predeterminada).

#!/bin/bash

while read read_data;
    do printf "$(expr $read_data \* 2)\n";
done

SELECT test_shell(number) FROM numbers(10);

    ┌─test_shell(number)─┐
 1. │ 0                  │
 2. │ 2                  │
 3. │ 4                  │
 4. │ 6                  │
 5. │ 8                  │
 6. │ 10                 │
 7. │ 12                 │
 8. │ 14                 │
 9. │ 16                 │
10. │ 18                 │
    └────────────────────┘

Algunas funciones pueden lanzar una excepción si los datos no son válidos. En ese caso, la consulta se cancela y se devuelve un mensaje de error al cliente. En el procesamiento distribuido, cuando se produce una excepción en uno de los servidores, los demás servidores también intentan cancelar la consulta.

En casi todos los lenguajes de programación, puede que uno de los argumentos no se evalúe para ciertos operadores. Normalmente, se trata de los operadores &&, || y ?:. En ClickHouse, los argumentos de las funciones (operadores) siempre se evalúan. Esto se debe a que se evalúan partes enteras de las columnas de una sola vez, en lugar de calcular cada fila por separado.

En el procesamiento distribuido de consultas, se realizan en servidores remotos tantas etapas del procesamiento de la consulta como sea posible, y el resto de las etapas (la fusión de resultados intermedios y todo lo posterior) se realiza en el servidor solicitante.

Esto significa que las funciones pueden ejecutarse en distintos servidores. Por ejemplo, en la consulta SELECT f(sum(g(x))) FROM distributed_table GROUP BY h(y),

• si una distributed_table tiene al menos dos segmentos, las funciones 'g' y 'h' se ejecutan en servidores remotos, y la función 'f' se ejecuta en el servidor solicitante.
• si una distributed_table tiene solo un segmento, todas las funciones 'f', 'g' y 'h' se ejecutan en el servidor de ese segmento.

El resultado de una función normalmente no depende del servidor en el que se ejecute. Sin embargo, a veces esto sí es importante. Por ejemplo, las funciones que trabajan con diccionarios usan el diccionario que existe en el servidor en el que se están ejecutando. Otro ejemplo es la función hostName, que devuelve el nombre del servidor en el que se está ejecutando, para poder hacer GROUP BY por servidores en una consulta SELECT.

Si una función de una consulta se ejecuta en el servidor solicitante, pero necesita ejecutarla en servidores remotos, puede envolverla en una función de agregación 'any' o añadirla a una clave de GROUP BY.

Las funciones personalizadas a partir de expresiones lambda se pueden crear mediante la sentencia CREATE FUNCTION. Para eliminar estas funciones, utilice la sentencia DROP FUNCTION.

Las funciones definidas por el usuario (UDFs) de WebAssembly (WASM) permiten ejecutar código personalizado compilado a WebAssembly dentro del proceso del servidor de ClickHouse.

Habilite la compatibilidad experimental con WebAssembly en la configuración de ClickHouse:

<clickhouse>
    <allow_experimental_webassembly_udf>true</allow_experimental_webassembly_udf>
</clickhouse>

Inserte el módulo WASM compilado en la tabla del sistema:

INSERT INTO system.webassembly_modules (name, code)
SELECT 'my_module', base64Decode('AGFzbQEAAAA...');

Cree una función con su módulo WASM:

CREATE FUNCTION my_function
LANGUAGE WASM
ABI ROW_DIRECT
FROM 'my_module'
ARGUMENTS (x UInt32, y UInt32)
RETURNS UInt32;

Utilice la función en sus consultas:

SELECT my_function(10, 20);

Consulta la documentación sobre funciones definidas por el usuario de WebAssembly para más información.

Un driver es un adaptador proporcionado por el operador que convierte un fragmento de código del usuario en una UDF ejecutable. Cuando se crea una función con ENGINE = DriverName(...), ClickHouse ejecuta el create_command del driver y le pasa la firma de la función y el cuerpo del código; el driver compila o procesa de otro modo el cuerpo e imprime una configuración de UDF ejecutable, que ClickHouse luego almacena y carga.

Esto permite a los administradores ofrecer a los usuarios una forma segura y restringida de definir funciones en un lenguaje arbitrario (por ejemplo, C compilado dentro de un contenedor aislado) sin darles acceso a los archivos de configuración ni al sistema de archivos del servidor. El conjunto de drivers disponibles está controlado por completo por el operador.

Las UDF ejecutables basadas en drivers están deshabilitadas de forma predeterminada. Para habilitarlas:

1. Active la opción Experimental en la configuración del servidor:

   <clickhouse>
       <allow_experimental_executable_udf_drivers>true</allow_experimental_executable_udf_drivers>
   </clickhouse>

2. Haga que user_defined_executable_function_drivers_config apunte a uno o varios archivos de configuración de drivers (se admite un patrón glob) y, opcionalmente, establezca dynamic_user_defined_executable_functions_path, el directorio donde se almacenan las configuraciones generadas de UDF ejecutables:

   <clickhouse>
       <user_defined_executable_function_drivers_config>user_defined_executable_function_drivers_config.d/*_driver.xml</user_defined_executable_function_drivers_config>
       <dynamic_user_defined_executable_functions_path>/var/lib/clickhouse/dynamic_user_defined_executable_functions/</dynamic_user_defined_executable_functions_path>
   </clickhouse>

El registro de drivers se carga al iniciar el servidor y se actualiza con SYSTEM RELOAD CONFIG, por lo que se pueden agregar, modificar o eliminar drivers sin reiniciar el servidor.

Un driver se describe mediante un archivo XML (o YAML) con un elemento <driver> de nivel superior. Se admiten los siguientes campos:

Ejemplo de configuración del driver:

<clickhouse>
    <driver>
        <name>DockerC</name>
        <create_command>../user_defined_executable_function_drivers/docker_c_create.sh</create_command>
        <drop_command>../user_defined_executable_function_drivers/docker_c_drop.sh</drop_command>
        <engine_arguments>
            <opt_level><required>false</required></opt_level>
        </engine_arguments>
        <env>
            <CLICKHOUSE_C_DRIVER_MEMORY>256m</CLICKHOUSE_C_DRIVER_MEMORY>
            <CLICKHOUSE_C_DRIVER_CPUS>1.0</CLICKHOUSE_C_DRIVER_CPUS>
        </env>
    </driver>
</clickhouse>

Cuando se ejecuta CREATE FUNCTION, se invoca create_command con las variables env configuradas y los siguientes argumentos:

• --name <function_name>
• --return <return_type> (si hay una cláusula RETURNS)
• --args <signature> (si hay una cláusula ARGUMENTS), donde la firma es la lista de argumentos declarados; por ejemplo, x UInt8, y DateTime
• --<key> <value> para cada argumento de engine declarado proporcionado en ENGINE = DriverName(key = value)

El cuerpo del código de usuario (el texto después de AS) se envía a la entrada estándar del comando. El comando debe imprimir la configuración de una UDF ejecutable en su salida estándar. El formato se detecta automáticamente: la salida que comienza con < se trata como XML; de lo contrario, como YAML. El nombre de la función definido en la configuración generada debe coincidir con el nombre que se está creando. Si create_command finaliza con un código distinto de cero, la instrucción falla con una excepción que incluye el código de salida y el error estándar del driver.

drop_command, cuando está presente, se invoca de la misma manera (sin un cuerpo de código en stdin) cuando se elimina la función.

CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS] name [ON CLUSTER cluster]
    ARGUMENTS (a UInt8, b String) RETURNS UInt64
    ENGINE = DriverName(key1 = 'value1', key2 = 42)
    AS '...code body...'

ClickHouse ejecuta el create_command del driver, escribe la configuración generada en dynamic_user_defined_executable_functions_path y el cargador existente de UDF ejecutables la detecta. Luego, la función puede llamarse como cualquier otra función.

DROP FUNCTION [IF EXISTS] name [ON CLUSTER cluster]

DROP FUNCTION invoca el drop_command del driver (si existe), elimina la configuración dinámica generada y el directorio de trabajo de cada función, recarga el cargador de UDF ejecutables y elimina la consulta persistida.

La consulta original se almacena como una sentencia ATTACH FUNCTION ... en el directorio de objetos SQL definidos por el usuario, por lo que la función sobrevive a un reinicio del servidor. Al iniciarse, las configuraciones generadas en dynamic_user_defined_executable_functions_path se cargan directamente sin volver a ejecutar el driver. Si una sentencia ATTACH FUNCTION almacenada no tiene una configuración generada correspondiente (por ejemplo, si se perdió el directorio dinámico), el driver se vuelve a ejecutar para recrearla.

• La funcionalidad es experimental y está habilitada mediante allow_experimental_executable_udf_drivers.
• Las funciones basadas en drivers no son compatibles con el almacenamiento replicado de funciones definidas por el usuario (ON CLUSTER y <user_defined_zookeeper_path>), porque solo se replica la consulta original, no los artefactos generados.
• La operación RESTORE de una función basada en drivers incluida en una copia de seguridad conserva la consulta, pero no vuelve a ejecutar el driver; la configuración generada se materializa más adelante durante la recuperación tras el reinicio.

El árbol del código fuente incluye drivers de prueba de concepto en programs/server/user_defined_executable_function_drivers_config.d/ que compilan y ejecutan el cuerpo de una función en C. Son ejemplos y no se instalan mediante paquetes:

• DockerC - compila y ejecuta el código dentro de contenedores de Docker aislados (--network=none --read-only --cap-drop=ALL --security-opt=no-new-privileges, además de límites de memoria/CPU/PID), y produce una UDF executable_pool.
• GVisorC - una variante que ejecuta el binario compilado con el runtime runsc de gVisor.
• UnsafeC - compila y ejecuta el código directamente en el host sin aislamiento. Como indica el nombre, no proporciona ningún aislamiento y está pensado solo para entornos de confianza y pruebas.

Estos drivers de ejemplo sirven como punto de partida; revise y endurezca el aislamiento según su entorno antes de exponerlos a usuarios no confiables.

• Funciones definidas por el usuario en ClickHouse Cloud