Fonctions définies par l'utilisateur (UDFs)

ClickHouse prend en charge plusieurs types de fonctions définies par l'utilisateur (UDFs) :

• UDFs exécutables lancent un programme externe ou un script (Python, Bash, etc.) et lui transmettent des blocs de données en flux via STDIN / STDOUT. Utilisez-les pour intégrer du code ou des outils existants sans recompiler ClickHouse. Leur surcoût par appel est plus élevé que celui des options exécutées dans le processus, et elles conviennent mieux à une logique plus lourde ou aux cas où un environnement d'exécution différent est nécessaire.
• SQL UDFs sont définies avec CREATE FUNCTION uniquement en SQL. Elles sont intégrées/dépliées dans le plan de requête (sans passer par un processus distinct), ce qui les rend légères et idéales pour réutiliser une logique d'expression ou simplifier des colonnes calculées complexes.
• Experimental WebAssembly UDFs exécutent du code compilé en WebAssembly dans un sandbox au sein du processus serveur. Elles offrent un surcoût par appel plus faible que les exécutables externes, avec une meilleure isolation que les extensions natives, ce qui les rend adaptées aux algorithmes personnalisés écrits dans des langages pouvant cibler WASM (par ex. C/C++/Rust).
• Experimental UDF exécutable basé sur un driver permettent à un "driver" fourni par l'opérateur de transformer un extrait de code fourni dans CREATE FUNCTION ... ENGINE = DriverName(...) AS '...' en une UDF exécutable au moment de la création de la fonction (par exemple, en le compilant). Elles s'appuient sur les UDFs exécutables et nécessitent une configuration du driver côté serveur.

ClickHouse peut appeler n’importe quel programme exécutable externe ou script pour traiter les données.

La configuration des fonctions exécutables définies par l’utilisateur peut être stockée dans un ou plusieurs fichiers XML. Le chemin d’accès à la configuration est spécifié dans le paramètre user_defined_executable_functions_config.

Une configuration de fonction contient les paramètres suivants :

La commande doit lire les arguments depuis STDIN et écrire le résultat sur STDOUT. Elle doit traiter les arguments de manière itérative. Autrement dit, après avoir traité un fragment d’arguments, elle doit attendre le fragment suivant.

Créez manuellement test_function_sum en définissant execute_direct sur 0, à l’aide d’une configuration XML ou 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 │
└─────────────────────────┘

Dans cet exemple, nous créons une UDF qui lit une valeur sur STDIN et la renvoie sous forme de chaîne de caractères.

Créez test_function à l’aide d’une configuration XML ou 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

Créez un fichier script test_function.py dans le dossier user_scripts (/var/lib/clickhouse/user_scripts/test_function.py avec le chemin par défaut).

#!/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                 │
└─────────────────────────┘

Créez test_function_sum_json avec des arguments nommés et le format JSONEachRow à l’aide d’une configuration XML ou 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

Créez le fichier de script test_function_sum_json.py dans le dossier user_scripts (/var/lib/clickhouse/user_scripts/test_function_sum_json.py avec les paramètres de chemin par défaut).

#!/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 │
└──────────────────────────────┘

Les fonctions définies par l'utilisateur exécutables peuvent accepter des paramètres constants configurés dans le paramètre command (cela fonctionne uniquement pour les fonctions définies par l'utilisateur de type executable). Cela nécessite également l'option execute_direct pour éviter toute vulnérabilité liée à l'expansion des arguments par le 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}

Créez le script test_function_parameter_python.py dans le dossier user_scripts (/var/lib/clickhouse/user_scripts/test_function_parameter_python.py avec les chemins par défaut).

#!/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                  │
└──────────────────────────────────────┘

Dans cet exemple, nous créons un script shell qui multiplie chaque valeur par 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

Créez le fichier de script test_shell.sh dans le dossier user_scripts (/var/lib/clickhouse/user_scripts/test_shell.sh si vous utilisez les chemins par défaut).

#!/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                 │
    └────────────────────┘

Certaines fonctions peuvent lever une exception si les données sont non valides. Dans ce cas, la requête est annulée et un message d'erreur est renvoyé au client. Pour le traitement distribué, lorsqu'une exception se produit sur l'un des serveurs, les autres serveurs tentent également d'interrompre la requête.

Dans presque tous les langages de programmation, il arrive que, pour certains opérateurs, l’un des arguments ne soit pas évalué. Il s’agit généralement des opérateurs &&, || et ?:. Dans ClickHouse, les arguments des fonctions (opérateurs) sont toujours évalués. Cela s’explique par le fait que des parties entières de colonnes sont évaluées en une seule fois, au lieu de calculer chaque ligne séparément.

Pour le traitement distribué des requêtes, autant d'étapes du traitement des requêtes que possible sont exécutées sur des serveurs distants, et les étapes restantes (fusion des résultats intermédiaires et tout ce qui suit) sont exécutées sur le serveur à l'origine de la requête.

Cela signifie que les fonctions peuvent être exécutées sur différents serveurs. Par exemple, dans la requête SELECT f(sum(g(x))) FROM distributed_table GROUP BY h(y),

• si distributed_table a au moins deux shards, les fonctions 'g' et 'h' sont exécutées sur des serveurs distants, et la fonction 'f' est exécutée sur le serveur à l'origine de la requête.
• si distributed_table n'a qu'un seul shard, toutes les fonctions 'f', 'g' et 'h' sont exécutées sur le serveur de ce shard.

Le résultat d'une fonction ne dépend généralement pas du serveur sur lequel elle est exécutée. Cependant, cela peut parfois avoir de l'importance. Par exemple, les fonctions qui utilisent des dictionnaires s'appuient sur le dictionnaire présent sur le serveur où elles s'exécutent. Autre exemple : la fonction hostName, qui renvoie le nom du serveur sur lequel elle s'exécute, afin de permettre un GROUP BY par serveurs dans une requête SELECT.

Si une fonction d'une requête est exécutée sur le serveur à l'origine de la requête, mais que vous devez l'exécuter sur des serveurs distants, vous pouvez l'encapsuler dans une fonction d'agrégation 'any' ou l'ajouter à une clé du GROUP BY.

Des fonctions personnalisées à partir d'expressions lambda peuvent être créées à l'aide de l'instruction CREATE FUNCTION. Pour supprimer ces fonctions, utilisez l'instruction DROP FUNCTION.

Les WebAssembly User Defined Functions (WASM UDFs) permettent d’exécuter du code personnalisé compilé en WebAssembly au sein du processus du serveur ClickHouse.

Activez la prise en charge expérimentale de WebAssembly dans la configuration de ClickHouse :

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

Insérez votre module WASM compilé dans la table système :

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

Créez une fonction à l’aide de votre module WASM :

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

Utilisez la fonction dans vos requêtes :

SELECT my_function(10, 20);

Pour en savoir plus, consultez la documentation sur WebAssembly User Defined Functions.

Un driver est un adaptateur fourni par l'opérateur qui transforme un extrait de code utilisateur en UDF exécutable. Lorsqu'une fonction est créée avec ENGINE = DriverName(...), ClickHouse exécute la commande create_command du driver en lui transmettant la signature de la fonction et le corps du code ; le driver compile ce corps ou le traite d'une autre manière, puis produit une configuration d'UDF exécutable, que ClickHouse stocke et charge ensuite.

Cela permet aux administrateurs d'offrir aux utilisateurs un moyen sûr et limité de définir des fonctions dans n'importe quel langage (par exemple, du C compilé dans un conteneur isolé) sans leur donner accès aux fichiers de configuration ni au système de fichiers du serveur. L'ensemble des drivers disponibles est entièrement contrôlé par l'opérateur.

Les UDF exécutables basées sur des drivers sont désactivées par défaut. Pour les activer :

1. Activez l'option expérimentale dans la configuration du serveur :

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

2. Faites pointer user_defined_executable_function_drivers_config vers un ou plusieurs fichiers de configuration de driver (les motifs glob sont pris en charge) et, si nécessaire, définissez dynamic_user_defined_executable_functions_path, le répertoire où sont stockées les configurations générées des UDF exécutables :

   <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>

Le registre des drivers est chargé au démarrage du serveur et actualisé lors de SYSTEM RELOAD CONFIG, ce qui permet d'ajouter, de modifier ou de supprimer des drivers sans redémarrer le serveur.

Un driver est décrit par un fichier XML (ou YAML) avec un élément <driver> à la racine. Les champs suivants sont pris en charge :

Exemple de configuration de 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>

Lorsque CREATE FUNCTION s'exécute, create_command est invoquée avec les variables env configurées et les arguments suivants :

• --name <function_name>
• --return <return_type> (si une clause RETURNS est présente)
• --args <signature> (si une clause ARGUMENTS est présente), où la signature correspond à la liste des arguments déclarés, par exemple x UInt8, y DateTime
• --<key> <value> pour chaque argument d'engine déclaré fourni dans ENGINE = DriverName(key = value)

Le corps du code utilisateur (le texte après AS) est envoyé sur l'entrée standard de la commande. La commande doit écrire la configuration d'une UDF exécutable sur sa sortie standard. Le format est détecté automatiquement : toute sortie commençant par < est traitée comme du XML, sinon comme du YAML. Le nom de fonction défini dans la configuration générée doit correspondre au nom en cours de création. Si create_command se termine avec un code de sortie non nul, l'instruction échoue avec une exception qui inclut ce code de sortie ainsi que la sortie d'erreur standard du driver.

drop_command, lorsqu'elle est présente, est invoquée de la même manière (sans corps de code sur stdin) lors de la suppression de la fonction.

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 exécute le create_command du driver, écrit la configuration générée dans dynamic_user_defined_executable_functions_path, puis le chargeur existant d'UDF exécutables la prend en charge. La fonction peut ensuite être appelée comme n'importe quelle autre fonction.

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

DROP FUNCTION appelle le drop_command du driver (s'il est présent), supprime la configuration dynamique générée ainsi que le répertoire de travail associé à chaque fonction, recharge le chargeur des UDF exécutables et supprime la requête enregistrée.

La requête d’origine est conservée sous la forme d’une instruction ATTACH FUNCTION ... dans le répertoire des objets SQL définis par l’utilisateur, de sorte que la fonction soit conservée après un redémarrage du serveur. Au démarrage, les configurations générées dans dynamic_user_defined_executable_functions_path sont chargées directement sans relancer le driver. Si une instruction ATTACH FUNCTION conservée n’a pas de configuration générée correspondante (par exemple, si le répertoire dynamique a été perdu), le driver est relancé pour la recréer.

• La fonctionnalité est expérimentale et activée via allow_experimental_executable_udf_drivers.
• Les fonctions basées sur des drivers ne sont pas prises en charge avec le stockage répliqué des fonctions définies par l’utilisateur (ON CLUSTER et <user_defined_zookeeper_path>), car seule la requête initiale est répliquée, pas les artefacts générés.
• Le RESTORE d’une fonction basée sur un driver issue d’une sauvegarde conserve la requête, mais ne réexécute pas le driver ; la configuration générée n’est matérialisée que plus tard, lors de la reprise après redémarrage.

Le code source inclut des drivers de démonstration dans programs/server/user_defined_executable_function_drivers_config.d/ qui compilent et exécutent le corps d’une fonction C. Ce sont des exemples et ils ne sont pas installés par les paquets :

• DockerC - compile et exécute le code dans des conteneurs Docker isolés (--network=none --read-only --cap-drop=ALL --security-opt=no-new-privileges, avec en plus des limites de mémoire/CPU/PID), en produisant une UDF executable_pool.
• GVisorC - une variante qui exécute le binaire compilé avec l’environnement d’exécution runsc de gVisor.
• UnsafeC - compile et exécute le code directement sur l’hôte, sans sandbox. Comme son nom l’indique, il ne fournit aucune isolation et est destiné uniquement aux environnements de confiance et aux tests.

Ces drivers d’exemple sont conçus comme point de départ ; examinez et renforcez le mécanisme d’isolation adapté à votre environnement avant de les exposer à des utilisateurs non fiables.

• Fonctions définies par l’utilisateur dans ClickHouse Cloud