Skip to content

Custom Functions

Hydrolix provides the ability to create user-defined functions.

Custom Functions⚓︎

Hydrolix custom functions are created using the Create function endpoint and are scoped to specific projects to avoid namespace collision.

Custom function configuration follows a similar method as can be found here - https://clickhouse.com/docs/sql-reference/statements/create/function.

Define Functions⚓︎

To specify your function with the API you should define it as follows:

1
2
3
4
5
6
7
8
POST https://hostname.hydrolix.live/config/v1/orgs/{org_id}/projects/{project_id}/functions/
Authorization: Bearer {{access_token}}
Content-Type: application/json

{
     "sql": "(x, k, b) -> k*x + b;",
     "name": "linear_equation"
}

Alternatively, you can define multiple functions with one API call:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
POST https://hostname.hydrolix.live/config/v1/orgs/{org_id}/projects/{project_id}/functions/bulk_function/
Authorization: Bearer {{access_token}}
Content-Type: application/json

[
    {
        "name": "sample_function1",
        "description": "sample function 1",
        "sql": "SELECT * FROM sample_project.sample_table_1 FORMAT JSON"
    },
    {
        "name": "sample_function2",
        "description": "sample function 2",
        "sql": "SELECT * FROM sample_project.sample_table_2 FORMAT JSON"
    }
]

The SQL is just the function definition

The sql section of the function config corresponds to the expression which immediately follows: CREATE FUNCTION <function name> AS ...

Name Definition
sql The function's specification
name The name of the function

Validation of function SQL⚓︎

When you save this resource, Hydrolix validates the SQL you provide by passing it through hdx_verify_sql, a ClickHouse table function that parses and analyzes the semantics of the query without executing it.

hdx_verify_sql checks for:

  • Syntax errors such as unclosed strings, unbalanced parentheses, missing keywords, or malformed statements
  • References to non-existent tables or databases
  • Type errors, for example arithmetic on incompatible types

Validation doesn't catch:

  • References to non-existent functions
  • Undefined variables
  • Whether referenced tables contain data
  • Query performance or cost
  • Access control or row/column-level permissions
  • Runtime errors

If the query service is temporarily unavailable, validation is skipped and the save is allowed to proceed.

Example Custom Function Usage⚓︎

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT
    number,
    {{projectname}}_linear_equation(number, 2, 1)
FROM numbers(3)

┌─number─┬─plus(multiply(2, number), 1)─┐
│      0 │                            1 │
│      1 │                            3 │
│      2 │                            5 │
└────────┴──────────────────────────────┘