Fonctions définies par l’utilisateur (UDFs)

Documentation sur les fonctions définies par l’utilisateur (UDFs)

​

UDFs Fonctions définies par l’utilisateur

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

• Executable UDFs 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).

​

Fonctions exécutables définies par l’utilisateur

Cette fonctionnalité est disponible en aperçu privé dans ClickHouse Cloud. Veuillez contacter ClickHouse Support à l’adresse https://clickhouse.cloud/support pour y accéder.

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 :

Paramètre	Description	Obligatoire	Valeur par défaut
name	Nom de la fonction	Oui	-
command	Nom du script à exécuter, ou commande si execute_direct vaut false	Oui	-
argument	Description d’un argument avec son type et, éventuellement, son name. Chaque argument est décrit dans un paramètre distinct. Indiquer un nom est nécessaire si les noms d’arguments font partie de la sérialisation pour un format de fonction définie par l’utilisateur tel que Native ou JSONEachRow	Oui	c + argument_number
format	Format dans lequel les arguments sont transmis à la commande. La sortie de la commande doit également utiliser ce même format	Oui	-
return_type	Type de la valeur renvoyée	Oui	-
return_name	Nom de la valeur renvoyée. Indiquer un nom de retour est nécessaire si ce nom fait partie de la sérialisation pour un format de fonction définie par l’utilisateur tel que Native ou JSONEachRow	Facultatif	result
type	Type d’exécutable. Si type est défini sur executable, une seule commande est lancée. S’il est défini sur executable_pool, un pool de commandes est créé	Oui	-
max_command_execution_time	Temps d’exécution maximal, en secondes, pour le traitement d’un bloc de données. Ce paramètre s’applique uniquement aux commandes executable_pool	Facultatif	10
command_termination_timeout	Délai, en secondes, pendant lequel une commande doit se terminer après la fermeture de son pipe. Au-delà, SIGTERM est envoyé au processus qui exécute la commande	Facultatif	10
command_read_timeout	Délai d’attente pour la lecture des données depuis le stdout de la commande, en millisecondes	Facultatif	10000
command_write_timeout	Délai d’attente pour l’écriture des données vers le stdin de la commande, en millisecondes	Facultatif	10000
pool_size	Taille du pool de commandes	Facultatif	16
send_chunk_header	Indique s’il faut envoyer le nombre de lignes avant d’envoyer un fragment de données au processus	Facultatif	false
execute_direct	Si execute_direct = 1, command est recherchée dans le dossier user_scripts spécifié par user_scripts_path. Des arguments de script supplémentaires peuvent être indiqués en les séparant par des espaces. Exemple : script_name arg1 arg2. Si execute_direct = 0, command est transmise comme argument à bin/sh -c	Facultatif	1
lifetime	Intervalle de rechargement d’une fonction, en secondes. S’il est défini sur 0, la fonction n’est pas rechargée	Facultatif	0
deterministic	Indique si la fonction est déterministe (renvoie le même résultat pour la même entrée)	Facultatif	false
stderr_reaction	Façon de gérer la sortie stderr de la commande. Valeurs : none (ignorer), log (journaliser immédiatement toute la sortie stderr), log_first (journaliser les 4 premiers KiB après la fin), log_last (journaliser les 4 derniers KiB après la fin), throw (lever immédiatement une exception à la moindre sortie sur stderr). Lors de l’utilisation de log_first ou log_last avec un code de sortie non nul, le contenu de stderr est inclus dans le message d’exception	Facultatif	log_last
check_exit_code	Si true, ClickHouse vérifie le code de sortie de la commande. Un code de sortie non nul provoque une exception	Facultatif	true

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.

​

Fonctions exécutables définies par l’utilisateur

​

Exemples

​

UDF à partir d’un script intégré

Créez manuellement test_function_sum en définissant execute_direct sur 0, à l’aide d’une configuration XML ou YAML.

XML

Fichier test_function.xml (/etc/clickhouse-server/test_function.xml avec la configuration de chemin par défaut).

/etc/clickhouse-server/test_function.xml

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

YAML

Fichier test_function.yaml (/etc/clickhouse-server/test_function.yaml avec la configuration de chemin par défaut).

/etc/clickhouse-server/test_function.yaml

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

Query

SELECT test_function_sum(2, 2);

Result

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

​

UDF à partir d’un script Python

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.

XML

Fichier test_function.xml (/etc/clickhouse-server/test_function.xml avec le chemin par défaut).

/etc/clickhouse-server/test_function.xml

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

YAML

Fichier test_function.yaml (/etc/clickhouse-server/test_function.yaml avec le chemin par défaut).

/etc/clickhouse-server/test_function.yaml

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

Query

SELECT test_function_python(toUInt64(2));

Result

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

​

Lire deux valeurs à partir de STDIN et renvoyer leur somme sous forme d’objet JSON

Créez test_function_sum_json avec des arguments nommés et le format JSONEachRow à l’aide d’une configuration XML ou YAML.

XML

Fichier test_function.xml (/etc/clickhouse-server/test_function.xml avec les paramètres de chemin par défaut).

/etc/clickhouse-server/test_function.xml

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

YAML

Fichier test_function.yaml (/etc/clickhouse-server/test_function.yaml avec les paramètres de chemin par défaut).

/etc/clickhouse-server/test_function.yaml

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

Query

SELECT test_function_sum_json(2, 2);

Result

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

​

Utiliser des paramètres dans le paramètre command

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.

XML

Fichier test_function_parameter_python.xml (/etc/clickhouse-server/test_function_parameter_python.xml avec les chemins par défaut).

/etc/clickhouse-server/test_function_parameter_python.xml

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

YAML

Fichier test_function_parameter_python.yaml (/etc/clickhouse-server/test_function_parameter_python.yaml avec les chemins par défaut).

/etc/clickhouse-server/test_function_parameter_python.yaml

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

Query

SELECT test_function_parameter_python(1)(2);

Result

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

​

UDF à partir d’un script shell

Dans cet exemple, nous créons un script shell qui multiplie chaque valeur par 2.

XML

Fichier test_function_shell.xml (/etc/clickhouse-server/test_function_shell.xml si vous utilisez les chemins par défaut).

/etc/clickhouse-server/test_function_shell.xml

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

YAML

Fichier test_function_shell.yaml (/etc/clickhouse-server/test_function_shell.yaml si vous utilisez les chemins par défaut).

/etc/clickhouse-server/test_function_shell.yaml

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

/var/lib/clickhouse/user_scripts/test_shell.sh

#!/bin/bash

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

Query

SELECT test_shell(number) FROM numbers(10);

Result

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

​

Gestion des erreurs

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.

​

Évaluation des expressions des arguments

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.

​

Exécution des fonctions pour le traitement distribué des requêtes

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.

​

SQL User Defined Functions

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.

​

WebAssembly User Defined Functions

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

​

Démarrage rapide

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

​

Informations complémentaires

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

​

Contenu connexe

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