User-defined functions in Cloud

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

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;

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

1   0

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

DROP FUNCTION isBusinessHours

• 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

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

requests>=2.28.0
numpy>=1.23.0

zip is_business_hours.zip main.py requirements.txt

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

zip is_business_hours.zip main.py

SELECT isBusinessHours('2026-03-20 10:00:00'::DateTime), isBusinessHours('2026-03-20 23:00:00'::DateTime);

true    false

​

Manage UDFs with the Cloud API

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.

​

Manage UDFs with Terraform

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

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
}