> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-trino-dialect.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# User-defined functions in Cloud

> Add your own executable Python functions in Cloud

export const BetaBadge = ({link, galaxyTrack, galaxyEvent}) => {
  if (link) {
    return <a href={link} target="_blank" rel="noopener noreferrer" className="betaBadge" onClick={galaxyTrack && galaxyEvent ? galaxyOnClick(galaxyEvent) : undefined}>
                <span>Beta</span>
            </a>;
  }
  return <a href="https://clickhouse.com/docs/reference/settings/beta-and-experimental-features#beta-features" className="betaBadge">
            <span>Beta feature</span>
        </a>;
};

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](/reference/functions/regular-functions/overview).

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](#manage-udfs-with-the-cloud-api) (beta)
4. Using [Terraform](#manage-udfs-with-terraform) (beta)

<h2 id="sql-udfs">
  SQL user-defined functions
</h2>

SQL UDFs can be created using the [`CREATE FUNCTION`](/reference/statements/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:

```sql theme={null}
CREATE FUNCTION isBusinessHours AS (ts) ->
toDayOfWeek(ts) BETWEEN 1 AND 5
AND toHour(ts) BETWEEN 9 AND 17;
```

3. Run the following below to test your newly created UDF:

```sql theme={null}
SELECT isBusinessHours('2026-03-20 10:00:00'::DateTime), isBusinessHours('2026-03-20 23:00:00'::DateTime);
```

You should get back the result:

```response theme={null}
1   0
```

4. You can use the `DROP FUNCTION` command to remove the UDF you just created:

```sql theme={null}
DROP FUNCTION isBusinessHours
```

<Warning>
  **Important**

  UDFs in ClickHouse Cloud **do not inherit user-level settings**. They execute with default system settings.
</Warning>

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

<h2 id="ui-udfs">
  User-defined functions created via UI
</h2>

<BetaBadge />

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.

<Steps>
  <Step title="Create the Python file" id="create-python-file">
    Create a new file `main.py` locally:

    ```python theme={null}
    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:

    ```text theme={null}
    requests>=2.28.0
    numpy>=1.23.0
    ```

    <Note>
      ClickHouse Cloud expects to find `main.py` in the zip file you will upload via the UI in the next step.
      If you name the file something else you will encounter an error.
    </Note>
  </Step>

  <Step title="Bundle dependencies and local files" id="bundle-dependencies">
    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:

    ```bash theme={null}
    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:

    ```python theme={null}
    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:

    ```bash theme={null}
    zip is_business_hours.zip main.py
    ```

    <Warning>
      **Symlinks are not allowed**

      ClickHouse Cloud rejects UDF archives that contain symbolic links. Make sure your ZIP bundle contains only regular files and directories — uploads with symlinks will fail validation.
    </Warning>
  </Step>

  <Step title="Create a UDF via the UI" id="create-udf-via-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](/reference/functions/regular-functions/udf#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**.
  </Step>

  <Step title="Test your UDF" id="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:

    ```sql theme={null}
    SELECT isBusinessHours('2026-03-20 10:00:00'::DateTime), isBusinessHours('2026-03-20 23:00:00'::DateTime);
    ```

    You should see the result:

    ```response theme={null}
    true    false
    ```
  </Step>

  <Step title="Create a new version" id="create-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.
  </Step>
</Steps>

<h2 id="manage-udfs-with-the-cloud-api">
  Manage UDFs with the Cloud API
</h2>

<BetaBadge />

Everything available in the UI is also available programmatically through the [ClickHouse Cloud API](/products/cloud/features/admin-features/api/api-overview).
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.

<Note>
  These endpoints are in beta and the API contract may change.
</Note>

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

1. [Create an upload URL](/products/cloud/api-reference/udf/udf-upload-session-create) 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](/products/cloud/api-reference/udf/udf-create) from the uploaded archive, specifying the function name, runtime, arguments, and return type.
3. [Attach the UDF to a service](/products/cloud/api-reference/udf/udf-attach). 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](/products/cloud/api-reference/udf/udf-upload-session-create) | Creates an org-scoped presigned `application/zip` upload URL                        |
| [Create UDF](/products/cloud/api-reference/udf/udf-create)                           | Creates a new UDF from an uploaded archive                                          |
| [List UDFs](/products/cloud/api-reference/udf/udf-list)                              | Returns the latest version of each UDF in the organization                          |
| [Get UDF](/products/cloud/api-reference/udf/udf-get)                                 | Returns the latest version of a UDF                                                 |
| [Delete UDF](/products/cloud/api-reference/udf/udf-delete)                           | Deletes every version of a UDF and detaches it from all services                    |
| [Create UDF version](/products/cloud/api-reference/udf/udf-version-create)           | Consumes a source archive, assigns a version, and starts the UDF build              |
| [List UDF versions](/products/cloud/api-reference/udf/udf-version-list)              | Returns all versions of a UDF                                                       |
| [Delete UDF version](/products/cloud/api-reference/udf/udf-version-delete)           | Deletes a UDF version that is not attached to any service                           |
| [Attach UDF to service](/products/cloud/api-reference/udf/udf-attach)                | Attaches one UDF version to a service, replacing the current version when necessary |
| [List UDF attachments](/products/cloud/api-reference/udf/udf-attachment-list)        | Returns the current service attachments for a UDF                                   |
| [Get UDF attachment](/products/cloud/api-reference/udf/udf-attachment-get)           | Returns the current attachment of a UDF to one service                              |
| [Detach UDF from service](/products/cloud/api-reference/udf/udf-detach)              | Detaches a UDF from a service                                                       |

See the [UDF API reference](/products/cloud/api-reference/udf/udf-create) for request and response schemas.

<h2 id="manage-udfs-with-terraform">
  Manage UDFs with Terraform
</h2>

<BetaBadge />

The official [ClickHouse Terraform provider](https://registry.terraform.io/providers/ClickHouse/clickhouse/latest/docs) includes two resources for managing UDFs as Infrastructure as Code:

* [`clickhouse_udf`](https://github.com/ClickHouse/terraform-provider-clickhouse/blob/main/docs/resources/udf.md) 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`](https://github.com/ClickHouse/terraform-provider-clickhouse/blob/main/docs/resources/udf_attachment.md) 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.

<Note>
  These resources are available in provider version 3.24.0 and later. They are in beta and their behavior may change in future provider versions.
</Note>

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

```terraform theme={null}
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.