UDF Function

The system provides the functionality to extend HQL, allowing users to define custom UDF functions. UDF functions are used in the system in the same way as native HQL functions and can be applied to add new columns, metrics, permission filters, etc. Users can add custom functions from the database to the HENGSHI SENSE system, making the work of database administrators accessible to report creators. Users can also extract permission control-related queries into some functions, making it easier for report creators to set permissions.

Custom UDF function creation process:

1. Prepare the UDF function file.
2. Place the UDF function file on the server and specify the server path.
3. Restart the service to use the UDF function.

Below is a detailed example of the UDF function creation process.

Prepare UDF Function

Prepare a custom UDF function and store it in a file with the json suffix. The following are examples of custom functions regex_replace, localtime, and dataorgperm.

[
    {
        "type": "string", // Return type of the function
        "args": [
            {
                "type": "string", // Parameter type
                "placeholder": "s", // Parameter placeholder
                "desc": "Text type parameter" // Parameter description
            },
            {
                "type": "string",
                "placeholder": "regex",
                "desc": "Text type parameter"
            },
            {
                "type": "string",
                "placeholder": "replace",
                "desc": "Text type parameter"
            }
        ],
        "desc": "Regular replacement", // Function description
        "name": "regex_replace", // Function name
        "categories": [ // Function categories
            "string functions",
            "non aggregate functions",
            "new field functions"
        ],
        "presets": {
            "mysql": "regex_replace({0}, {1}, {2})",
            "postgresql": "regex_replace({0}, {1}, {2})"
        }, // Actual implementation of the function, one record per dialect, mysql is the dialect name
        "varArgs": false, // Whether the function's variables are variable
        "isAggregate":false, // Whether the function is an aggregate function
        "isWindow":false // Whether the function is a window function
    },
    {
        "type": "date",
        "args": [
        ],
        "desc": "Current time",
        "name": "localtime",
        "categories": [    //Function categories
            "time functions",
            "non aggregate functions",
            "new field functions"
        ],
        "presets": {
            "mysql": "localtime",
            "postgresql": "localtime"
        },
        "varArgs": true
    }
    {
        "name": "dataorgperm",
        "type": "bool",
        "presets": {
            "postgresql": "exists (select 0 from \"A_IVT_MOVIE\" z where prime_genre like ''%{1}%'' and id = '{{%%_table}}'.{0})"
        },
        "args": [
            {
                "type": "string",
                "placeholder": "literal_unquoted",
                "desc": "Permission organization field"
            },
            {
                "type": "string",
                "placeholder": "literal_unquoted",
                "desc": "User's organization"
            }
        ],
        "desc": "Data organization permission",
        "categories": [
            "other functions"
        ],
        "varArgs": false,
        "isAggregate": false,
        "isWindow": false
    }

]

The return value and parameters of the function support the following types:

• string ：Text type.
• integer：Integer type.
• number ：Number type.
• time ：Time type.
• date ：Date type.
• bool ：Boolean type.
• any ：Any type.

Function categories support the following types:

• string functions ： String functions.
• math functions ： Math functions.
• time functions ： Date functions.
• non aggregate functions： Non-aggregate functions.
• aggregate functions ： Aggregate functions.
• window functions ： Window functions.
• common functions ： Common functions.
• new field functions ： Functions will be listed in the function list of new fields, metrics, permissions, etc.

Parameter placeholders: The value of the placeholder can be set to any value. The following two placeholder values have specific definitions in the HENGSHI SENSE system.

• literal ：Indicates that this parameter must be a constant string.
• literal_unquoted ：Indicates that this parameter is a constant string and does not need to add quotes when concatenating SQL.

Parameter usage: UDF functions support passing parameters using the format '{{%%parameter name}}', where the single quote is an escape symbol to avoid the system treating it as a function parameter placeholder. The _table is a special parameter used to represent the alias of the Dataset it acts on, which is generally used in data connection or Dataset permission settings.

Configure UDF Function Path

After preparing the UDF function file, place it on the server and specify the server path in the configuration file using the configuration item UDF_FILE_PATH.

UDF_FILE_PATH=~/Desktop/udf/udf-test.json

Use UDF Function

After configuring the UDF function path, restart the HENGSHI SENSE service, and you can view and use the defined UDF functions in expressions.

Note

If the UDF function has the same name as a system HQL function, the UDF function will be prioritized, and the function list will display the UDF function.