Funções definidas pelo usuário (UDFs)

O ClickHouse oferece suporte a vários tipos de funções definidas pelo usuário (UDFs):

• UDFs executáveis iniciam um programa ou script externo (Python, Bash etc.) e enviam blocos de dados para ele via STDIN / STDOUT. Use-as para integrar código ou ferramentas existentes sem recompilar o ClickHouse. Elas têm maior sobrecarga por chamada em comparação com opções executadas no mesmo processo e são mais indicadas para lógicas mais pesadas ou quando é necessário um runtime diferente.
• UDFs SQL são definidas com CREATE FUNCTION exclusivamente em SQL. Elas são inline/expandidas no plano da consulta (sem separação de processo), o que as torna leves e ideais para reutilizar lógica de expressão ou simplificar colunas calculadas complexas.
• UDFs WebAssembly experimentais executam código compilado em WebAssembly dentro de um sandbox no processo do servidor. Elas oferecem menor sobrecarga por chamada do que executáveis externos, com melhor isolamento do que extensões nativas, o que as torna adequadas para algoritmos personalizados escritos em linguagens que podem gerar WASM (por exemplo, C/C++/Rust).
• UDFs executáveis experimentais baseadas em driver permitem que um "driver" fornecido pelo operador transforme um trecho de código fornecido em CREATE FUNCTION ... ENGINE = DriverName(...) AS '...' em uma UDF executável no momento da criação da função (por exemplo, por meio de compilação). Elas se baseiam em UDFs executáveis e exigem configuração do driver no lado do servidor.

O ClickHouse pode chamar qualquer programa executável externo ou script para processar dados.

A configuração das funções executáveis definidas pelo usuário pode estar em um ou mais arquivos XML. O caminho para a configuração é especificado no parâmetro user_defined_executable_functions_config.

A configuração de uma função contém as seguintes definições:

O comando deve ler os argumentos de STDIN e enviar o resultado para STDOUT. O comando deve processar os argumentos iterativamente. Ou seja, após processar um fragmento de argumentos, ele deve aguardar o próximo fragmento.

Crie test_function_sum manualmente, definindo execute_direct como 0, usando configuração 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 │
└─────────────────────────┘

Neste exemplo, criamos uma UDF que lê um valor de STDIN e o retorna como uma string.

Crie test_function usando configuração em 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

Crie um arquivo de script test_function.py na pasta user_scripts (/var/lib/clickhouse/user_scripts/test_function.py com as configurações de caminho padrão).

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

Crie test_function_sum_json com argumentos nomeados e o formato JSONEachRow usando configuração 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

Crie o arquivo de script test_function_sum_json.py na pasta user_scripts (/var/lib/clickhouse/user_scripts/test_function_sum_json.py com o caminho padrão).

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

Funções executáveis definidas pelo usuário podem receber parâmetros constantes configurados na configuração command (isso funciona apenas para funções definidas pelo usuário do tipo executable). Isso também exige a opção execute_direct para evitar vulnerabilidades de expansão de argumentos do 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}

Crie o arquivo de script test_function_parameter_python.py dentro da pasta user_scripts (/var/lib/clickhouse/user_scripts/test_function_parameter_python.py com as configurações de caminho padrão).

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

Neste exemplo, criamos um 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

Crie o script test_shell.sh dentro da pasta user_scripts (/var/lib/clickhouse/user_scripts/test_shell.sh com o caminho padrão).

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

Algumas funções podem gerar uma exceção se os dados forem inválidos. Nesse caso, a consulta é cancelada e uma mensagem de erro é retornada ao cliente. No processamento distribuído, quando ocorre uma exceção em um dos servidores, os demais servidores também tentam interromper a consulta.

Em quase todas as linguagens de programação, para determinados operadores, um dos argumentos pode não ser avaliado. Normalmente, esses operadores são &&, || e ?:. No ClickHouse, os argumentos de funções (operadores) são sempre avaliados. Isso acontece porque partes inteiras de colunas são avaliadas de uma só vez, em vez de cada linha ser calculada separadamente.

No processamento distribuído de consultas, o máximo possível de etapas do processamento da consulta é executado em servidores remotos, e as etapas restantes (a mesclagem dos resultados intermediários e tudo o que vem depois) são executadas no servidor solicitante.

Isso significa que as funções podem ser executadas em servidores diferentes. Por exemplo, na consulta SELECT f(sum(g(x))) FROM distributed_table GROUP BY h(y),

• se uma distributed_table tiver pelo menos dois shards, as funções 'g' e 'h' serão executadas em servidores remotos, e a função 'f' será executada no servidor solicitante.
• se uma distributed_table tiver apenas um shard, todas as funções 'f', 'g' e 'h' serão executadas no servidor desse shard.

O resultado de uma função geralmente não depende do servidor em que ela é executada. No entanto, às vezes isso é importante. Por exemplo, funções que trabalham com dicionários usam o dicionário existente no servidor em que estão sendo executadas. Outro exemplo é a função hostName, que retorna o nome do servidor em que está sendo executada para permitir o GROUP BY por servidores em uma consulta SELECT.

Se uma função em uma consulta for executada no servidor solicitante, mas você precisar executá-la em servidores remotos, poderá encapsulá-la em uma função agregada 'any' ou adicioná-la a uma chave no GROUP BY.

Funções personalizadas com base em expressões lambda podem ser criadas usando a instrução CREATE FUNCTION. Para excluir essas funções, use a instrução DROP FUNCTION.

As funções definidas pelo usuário em WebAssembly (WASM UDFs) permitem executar código personalizado compilado em WebAssembly dentro do processo do servidor ClickHouse.

Ative o suporte experimental a WebAssembly na configuração do ClickHouse:

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

Insira seu módulo WASM compilado na tabela do sistema:

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

Crie uma função usando seu módulo WASM:

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

Use a função nas suas consultas:

SELECT my_function(10, 20);

Consulte a documentação de Funções definidas pelo usuário em WebAssembly para mais detalhes.

Um driver é um adaptador fornecido pelo operador que transforma um trecho de código do usuário em uma UDF executável. Quando uma função é criada com ENGINE = DriverName(...), o ClickHouse executa o create_command do driver, passando a assinatura da função e o corpo do código; o driver compila ou processa esse corpo de outra forma e gera uma configuração de UDF executável, que o ClickHouse então armazena e carrega.

Isso permite que os administradores ofereçam aos usuários uma forma segura e restrita de definir funções em uma linguagem arbitrária (por exemplo, C compilado dentro de um contêiner em sandbox) sem dar a eles acesso aos arquivos de configuração ou ao sistema de arquivos do servidor. O conjunto de drivers disponíveis é controlado inteiramente pelo operador.

UDFs executáveis baseadas em driver ficam desabilitadas por padrão. Para habilitá-las:

1. Defina o controle experimental na configuração do servidor:

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

2. Aponte user_defined_executable_function_drivers_config para um ou mais arquivos de configuração de driver (com suporte a glob) e, opcionalmente, defina dynamic_user_defined_executable_functions_path, o diretório em que as configurações geradas de UDFs executáveis são armazenadas:

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

O registro de drivers é carregado na inicialização do servidor e atualizado em SYSTEM RELOAD CONFIG, portanto os drivers podem ser adicionados, alterados ou removidos sem reiniciar o servidor.

Um driver é descrito por um arquivo XML (ou YAML) com um elemento <driver> no nível superior. Os campos a seguir são compatíveis:

Exemplo de configuração do 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>

Quando CREATE FUNCTION é executado, create_command é invocado com as variáveis env configuradas e os seguintes argumentos:

• --name <function_name>
• --return <return_type> (se uma cláusula RETURNS estiver presente)
• --args <signature> (se uma cláusula ARGUMENTS estiver presente), em que a assinatura é a lista de argumentos declarados, por exemplo x UInt8, y DateTime
• --<key> <value> para cada argumento de engine declarado fornecido em ENGINE = DriverName(key = value)

O corpo do código do usuário (o texto após AS) é enviado para a entrada padrão do comando. O comando deve imprimir a configuração de uma UDF executável em sua saída padrão. O formato é detectado automaticamente: uma saída que começa com < é tratada como XML; caso contrário, como YAML. O nome da função definido na configuração gerada deve corresponder ao nome que está sendo criado. Se create_command encerrar com um status diferente de zero, a instrução falhará com uma exceção que inclui o código de saída e o erro padrão do driver.

drop_command, quando presente, é invocado da mesma forma (sem um corpo de código no stdin) quando a função é removida.

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

O ClickHouse executa o create_command do driver, grava a configuração gerada em dynamic_user_defined_executable_functions_path e o carregador existente de UDF executáveis a carrega. A função pode então ser chamada como qualquer outra função.

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

DROP FUNCTION invoca o drop_command do driver (se houver), remove a configuração dinâmica gerada e o diretório de trabalho de cada função, recarrega o carregador de UDF executável e remove a consulta persistida.

A consulta que deu origem é persistida como uma instrução ATTACH FUNCTION ... no diretório de objetos SQL definidos pelo usuário, para que a função sobreviva à reinicialização do servidor. Na inicialização, as configurações geradas em dynamic_user_defined_executable_functions_path são carregadas diretamente, sem executar novamente o driver. Se uma instrução ATTACH FUNCTION persistida não tiver uma configuração gerada correspondente (por exemplo, se o diretório dinâmico tiver sido perdido), o driver será executado novamente para recriá-la.

• A funcionalidade é experimental e fica condicionada a allow_experimental_executable_udf_drivers.
• Funções baseadas em driver não têm suporte com armazenamento replicado de funções definidas pelo usuário (ON CLUSTER e <user_defined_zookeeper_path>), porque apenas a consulta que deu origem é replicada, não os artefatos gerados.
• O RESTORE de uma função baseada em driver a partir de backup preserva a consulta, mas não executa novamente o driver; a configuração gerada é materializada posteriormente pela recuperação na reinicialização.

A árvore de código-fonte inclui drivers de prova de conceito em programs/server/user_defined_executable_function_drivers_config.d/ que compilam e executam um corpo de função em C. Eles são exemplos e não são instalados pelos pacotes:

• DockerC - compila e executa o código dentro de contêineres Docker em sandbox (--network=none --read-only --cap-drop=ALL --security-opt=no-new-privileges, além de limites de memória/CPU/PID), gerando uma UDF executable_pool.
• GVisorC - uma variante que executa o binário compilado no runtime runsc do gVisor.
• UnsafeC - compila e executa o código diretamente no host, sem sandbox. Como o nome indica, não oferece isolamento e se destina apenas a ambientes confiáveis e testes.

Esses drivers de exemplo servem como ponto de partida; revise e reforce o sandboxing para o seu ambiente antes de expô-los a usuários não confiáveis.

• Funções definidas pelo usuário no ClickHouse Cloud