> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8c05c8a2.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 galaxyOnClick = eventName => () => {
  try {
    if (typeof window !== "undefined" && window.galaxy && eventName) {
      window.galaxy.track(eventName, {
        interaction: "click"
      });
    }
  } catch (e) {}
};

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}>
                <Icon />
                <span>Beta</span>
            </a>;
  }
  return <div className="betaBadge">
            <Icon />
            <span>
                Beta feature. 
                <u>
                    <a href="/docs/beta-and-experimental-features#beta-features">
                        Learn more.
                    </a>
                </u>
            </span>
        </div>;
};

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/regular-functions-index).

In ClickHouse Cloud, there are two ways to create user-defined functions:

1. Using SQL
2. Using the UI and your own code (public 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>

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>
    <h3 id="create-python-file">
      Create the Python file
    </h3>

    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>
    <h3 id="bundle-dependencies">
      Bundle dependencies and local files
    </h3>

    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>
    <h3 id="create-udf-via-ui">
      Create a UDF via the UI
    </h3>

    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>
    <h3 id="test-your-udf">
      Test your UDF
    </h3>

    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>
    <h3 id="create-new-version">
      Create a new version
    </h3>

    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>