Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

User-defined functions in Cloud

View as MarkdownEdit this pageReport an issueSet up docs MCP

User-defined functions (UDF) allow users to extend the behavior of ClickHouse beyond what is offered by over a thousand different out-of-box functions.

In ClickHouse Cloud, there are several ways to create and manage user-defined functions:

  1. Using SQL
  2. Using the UI and your own code (public beta)
  3. Using the Cloud API (beta)
  4. Using Terraform (beta)

SQL user-defined functions

SQL UDFs can be created using the CREATE FUNCTION statement from a lambda expression.

In this example we’ll create a simple executable user-defined function, isBusinessHours. The function will check if a certain timestamp falls inside of regular business hours and return true if it does, otherwise false.

  1. Login to Cloud Console and open the SQL console
  2. Write the following SQL query to create the isBusinessHours function:
CREATE FUNCTION isBusinessHours AS (ts) ->
toDayOfWeek(ts) BETWEEN 1 AND 5
AND toHour(ts) BETWEEN 9 AND 17;
  1. Run the following below to test your newly created UDF:
SELECT isBusinessHours('2026-03-20 10:00:00'::DateTime), isBusinessHours('2026-03-20 23:00:00'::DateTime);

You should get back the result:

1   0
  1. You can use the DROP FUNCTION command to remove the UDF you just created:
DROP FUNCTION isBusinessHours

This means:

  • Session-level settings (set via SET statement) are not propagated to UDF execution context
  • User profile settings are not inherited by UDFs
  • Query-level settings do not apply within UDF execution

User-defined functions created via UI

Beta feature

ClickHouse Cloud offers a UI configuration experience for creating user-defined functions.

In this example we’ll create the same simple executable user-defined function isBusinessHours that checks if a certain timestamp falls inside of regular business hours. Previously we created it using SQL, but this time we will create it using Python and configure it via the UI.

Create the Python file

Create a new file main.py locally:

cat > main.py << 'EOF'
import sys

from datetime import datetime

for line in sys.stdin:
    ts = datetime.fromisoformat(line.strip())
    result = 1 if (0 <= ts.weekday() <= 4 and 9 <= ts.hour <= 17) else 0
    print(result)
    sys.stdout.flush()
EOF

If your Python script imports third-party packages, list them in a requirements.txt file and ClickHouse Cloud installs them for you. You can instead bundle dependencies directly in the ZIP, but then you must include cached packages for both CPU architectures, so requirements.txt is simpler. For example:

requests>=2.28.0
numpy>=1.23.0

Bundle dependencies and local files

To include dependency packages and any additional local files (such as wheel files, configuration files, or data files), place them in the same directory as your main.py and requirements.txt. When you create the ZIP archive, include all files:

zip is_business_hours.zip main.py requirements.txt

You can reference the local bundled path base directory in your Python code using os.path.dirname(os.path.abspath(__file__)). This returns the absolute path to the directory where your main.py is located within the ZIP archive, allowing you to access other bundled files:

import os

# Get the base directory of the bundled files
base_dir = os.path.dirname(os.path.abspath(__file__))
config_path = os.path.join(base_dir, 'config.json')

This is useful when you need to:

  • Access configuration files bundled with your UDF
  • Load wheel packages for custom dependencies
  • Reference additional scripts or data files

Now compress the file into a ZIP archive:

zip is_business_hours.zip main.py

Create a UDF via the UI

  1. From the Cloud console homepage, click on the name of your organization in the bottom-left menu.
  2. Select User-defined functions from the menu.
  3. On the user-defined functions page, click Set up a UDF. A configuration panel opens on the right side of the screen.
  4. Enter a function name. For this example, use isBusinessHours.
  5. Select a function type, either Executable pool or Executable:
    • Executable pool: A pool of persistent processes is maintained, and a process is taken from the pool for reads.
    • Executable: The script runs on every query.
  6. For this example, use the default settings. For a full list of configuration parameters, see Executable user-defined functions.
  7. Click Browse File to upload the .zip file created at the start of this tutorial.
  8. Add a new argument. For this example, add an argument timestamp with type DateTime.
  9. Select a return type. For this example, select Bool.
  10. Click Create UDF. A dialog displays the current build status.
    • If there are any problems, the status changes to error.
    • Otherwise, the status progresses from building to provisioning. Your service must be awake to complete provisioning. If your service is idle, click Wake Up Service in the UDF details panel next to the service name.
    • Once complete, the status changes to deployed.

Test your UDF

  1. return back to the home page of the SQL Console by clicking Settings - return to your service view from the top left corner of the page
  2. click SQL Console in the left hand menu
  3. write the following query:
SELECT isBusinessHours('2026-03-20 10:00:00'::DateTime), isBusinessHours('2026-03-20 23:00:00'::DateTime);

You should see the result:

true    false

Create a new version

To change a UDF’s code, create a new version. The Edit panel only manages which services a UDF is assigned to; uploading a file there won’t replace the deployed code.

  1. From the Cloud console homepage, click on the name of your organization in the bottom-left menu.
  2. Select User-defined functions from the menu.
  3. Select the three dots under Actions for the isBusinessHours UDF, click Create new version
  4. Upload a zip with the modified code, or change settings and then click Create new version

You have successfully added your first user-defined function via the UI, confirmed it runs and seen how to create a new version of it if needed.

Manage UDFs with the Cloud API

Beta feature

Everything available in the UI is also available programmatically through the ClickHouse Cloud API. The UDF endpoints let you script the full lifecycle of a UDF: uploading source archives, creating functions and versions, attaching them to services, and cleaning them up.

The typical workflow to create and deploy a UDF via the API is:

  1. Create an upload URL to receive a presigned application/zip upload URL, then upload your ZIP archive to it. Each upload ID may be used for only one create or version attempt; request a new upload URL when retrying.
  2. Create the UDF from the uploaded archive, specifying the function name, runtime, arguments, and return type.
  3. Attach the UDF to a service. When the version is omitted, the latest ready version is attached. The service must be running; idle services can be woken up first.

The full set of endpoints:

Endpoint Description
Create UDF upload URL Creates an org-scoped presigned application/zip upload URL
Create UDF Creates a new UDF from an uploaded archive
List UDFs Returns the latest version of each UDF in the organization
Get UDF Returns the latest version of a UDF
Delete UDF Deletes every version of a UDF and detaches it from all services
Create UDF version Consumes a source archive, assigns a version, and starts the UDF build
List UDF versions Returns all versions of a UDF
Delete UDF version Deletes a UDF version that is not attached to any service
Attach UDF to service Attaches one UDF version to a service, replacing the current version when necessary
List UDF attachments Returns the current service attachments for a UDF
Get UDF attachment Returns the current attachment of a UDF to one service
Detach UDF from service Detaches a UDF from a service

See the UDF API reference for request and response schemas.

Manage UDFs with Terraform

Beta feature

The official ClickHouse Terraform provider includes two resources for managing UDFs as Infrastructure as Code:

  • clickhouse_udf manages the function itself. It takes a ZIP archive with the function source code and publishes a new version whenever the archive hash changes, waiting for the build to complete.
  • clickhouse_udf_attachment attaches a UDF version to a service. A service holds at most one version of a function at a time. You can pin a fixed version number, or reference clickhouse_udf.<name>.version to automatically roll services forward to the latest version.

For example, to deploy the isBusinessHours UDF from the earlier example with Terraform:

resource "clickhouse_udf" "is_business_hours" {
  function_name = "isBusinessHours"
  runtime       = "python3.11"
  type          = "executable_pool"
  return_type   = "Bool"

  arguments = [
    { name = "timestamp", type = "DateTime" },
  ]

  source_archive_path = "${path.module}/is_business_hours.zip"
  source_archive_hash = filebase64sha256("${path.module}/is_business_hours.zip")
}

resource "clickhouse_udf_attachment" "production" {
  function_name = clickhouse_udf.is_business_hours.function_name
  service_id    = var.service_id
  version       = clickhouse_udf.is_business_hours.version
}

Attaching only succeeds for versions that are ready, and can take several minutes; idle services are woken up automatically. Deleting a clickhouse_udf resource removes all versions of the function and detaches it from all services.

Navigation