Admin-Defined Functions

Provides an overview of admin-defined functions.

Self-Service Analytics provides a set of functions that you can use in expressions to build derived fields or custom metrics. However, you may want to use your own functions (for which there is no Self-Service Analytics equivalent) to extend your data analytics in Self-Service Analytics. Examples of this might be functions available within your data store either natively or as user-defined functions. In such situations, you can leverage administrator-defined functions to expose this capability throughout Self-Service Analytics.

Administrator-defined functions allow your organization to create functions at the connector level from SQL strings. These functions can be referenced later in data source configurations that use the connector.

Currently, the following limitations exist for administrator-defined functions.

1. They are only available for connectors that are SQL-based and that support row-level expressions in derived fields and custom metrics.
2. They are only available for functions that perform row-level operations, and not aggregation operations (for example, standard deviation or rank).

Support for this feature by connector is shown in the following table.

Key:Y - Supported; N - Not Supported; N/A - not applicable

Connector	Supported?
Amazon Redshift	Y
Amazon S3	Y
Apache Drill	Y
Apache Phoenix	Y
Apache Phoenix Query Server (QS)	Y
Apache Solr	N
BigQuery	Y
Business Central Jet	Y
Cloudera Impala	Y
Cloudera Search	N
Couchbase	Y
Dremio	N
Dundas BI (Managed)	source-dependent
Elasticsearch 7.0	N
Elasticsearch 8.0	N
File Upload (Upload API)	Y
HDFS	Y
Hive	Y
Jira	N
MemSQL	Y
Microsoft SQL Server	Y
MongoDB	N
MySQL	Y
OpenSearch	N
Oracle	Y
PostgreSQL	Y
Python	N
Real Time Sales	N/A
Salesforce	N
SAP Hana	Y
SAP S/4HANA	Y
SAP IQ	Y
Spark SQL	Y
Snowflake	Y
Teradata	Y
TIBCO DV	Y
Trino	Y
File Upload (Upload API)	Y
Vertica	Y

If a connector supports derived fields, it also supports row-level expressions.

For more information, see:

• Activate Admin-Defined Functions
• Admin-Defined Function JSON Files

Activate Admin-Defined Functions

Activate an admin-defined function in Self-Service Analytics

1. Create a JSON file containing your admin-defined function definitions. This JSON file should reside on the same machine as the connector server. The recommended location for this JSON file is /etc/zoomdata (for example, /etc/zoomdata/edc-impala-functions.json). See Admin-Defined Function JSON Files for more information about creating this JSON file.

2. Update the connector server configuration so the connector knows where to find the file containing the admin-defined functions you have created. Complete these steps:

   1. Locate and edit the properties file for the connector in the /etc/zoomdata directory. For example, the Impala properties file is called edc-impala.properties.

      vi <property-file>
      

   2. Locate and update the functions.template.json.path property in the Functions section of the properties file. Supply the path to the JSON file containing your admin-defined functions in the property. In the following example, the path for the edc-impala-functions.json file is specified.

      functions.template.json.path=/etc/zoomdata/edc-impala-functions.json
      

      All admin-defined functions for a connector should be defined in the same JSON file, so only one path is needed per data source.

   3. Save the properties file.

3. Restart the connector server.

The connector reads and validates the JSON file only at startup. When you restart the connector, it loads the admin-defined function JSON file and will validate the function definitions in the file. Errors are logged if they are found. If the function definitions are valid, the connector starts successfully and writes a list of the loaded admin-defined functions to the log.

Admin-Defined Function JSON Files

Use a single JSON file to define all the admin-defined functions for a single connector. Store this JSON file in the appropriate location for your environment.

• Linux: /etc/zoomdata. As an example, /etc/zoomdata/edc-impala-functions.json.
• Windows: <install-path>/conf-modify. As an example, <install-path>/conf-modify/edc-impala-functions.json.

This topic covers the following information:

• JSON File Structure
• Validating Your JSON File
• Example

JSON File Structure

The basic structure of an admin-defined function in the JSON file is shown below. Each section is described.

{
  "<function>": {
    "template": "<SQL template string>",
    "returnType": {
      "type": "<type>",
      "name": "<name>"
	},
	"arguments": {
      "<arg1-key>": {
         "name": "<arg1-name>",
         "returnType": {
           "type": "<type>",
           "name": "<name>"
         },
         "description": "<arg1-description>"
      },
      "<arg2-key>": {
         "name": "<arg2-name>",
         "returnType": {
           "type": "<type>",
           "name": "<name>"
         },
         "description": "<arg2-description>"
      }
	},
    "description": "<function-description>"
  }
}

function

The function name is the identifier used for the row-level admin-defined function. This name is displayed in the UI. The function name must start with a letter or an underscore and can contain letters, underscores, numbers, and periods.

template

The template defines the SQL string that will be used in the SQL query to a data source. You are fully responsible for the validity of the template string. Self-Service Analytics cannot fully validate it.

The template references arguments defined later in the JSON file. The arguments are surrounded by braces (curly braces) and are replaced by their SQL representations at run time. Self-Service Analytics does validate missing arguments when the connector associated with this JSON file starts.

You can use a backslash (\) as an escape character. It can be used to escape the following three characters: \ { } anywhere in the SQL template string (including inside the argument placeholders).

Here is an example:

"template": "({arg_0}) + ({arg_1}) * interval '1 year'”

returnType

There are three possible returnTypes: simple, generic, and array. The returnType used for the whole function must be simple or generic; the array returnType is not supported for the whole function. However, all three returnTypes are supported in the arguments defined within an admin-defined function.

Each returnType in the JSON provides a type specification and a name.

simple returnTypes

Five simple returnTypes are supported : NUMBER, INTEGER, STRING, DATE, or BOOLEAN. Here is an example of a simple returnType:

"returnType": {
    "type": "simple",
    "name": "DATE"
}

generic returnTypes

A generic returnType is an abstract type that is given a name. Here is an example of a generic returnType:

"returnType": {
    "type": "generic",
    "name": "T"
}

The specified name is the name used for the returnType inference. The only restriction is that the function’s generic returnType name must be the same as one of the argument types. This is required to infer a function returnType based on an argument type.

array returnTypes

An array returnType includes a baseType that can be used only for the last argument. The baseType can only be simple or generic. Here is an example:

"returnType": {
    "type": "array",
    "baseType": {
        "type": "simple",
        "name": "STRING"
    }
}

arguments

The arguments section defines the argument keys and definitions. You do not have to specify an arguments section if there are no arguments. The argument keys are used in the SQL template string at the beginning of the function definition.

An argument definition consists of a name, a returnType, and description. The name and description are used to display the argument in the UI. The returnType is used for argument type validation and function returnType inference. The argument returnType can be simple, generic or array.

description

The description is an optional string you can specify to describe a function or an argument in the JSON file and in the UI.

Validating Your JSON File

You can validate your JSON against a JSON schema file functions-schema.json that Self-Service Analytics provides in the appropriate directory for your environment.

• Linux: /opt/zoomdata/docs/<edc-connector>. For example, the Impala JSON schema is in /opt/zoomdata/docs/edc-impala/functions-schema.json.
• Windows: <install-path>/docs/<edc-connector>. For example, the Impala JSON schema is in <install-path>/docs/edc-impala/functions-schema.json.

Validate your JSON file against the JSON schema

1. Link to the JSON Schema Validator at https://www.jsonschemavalidator.net/.
2. Copy the schema found in the JSON schema file for your connector (see above) to the left side of the JSON Schema Validator screen.
3. Copy your JSON file admin-defined function definitions in the Input JSON section on the right side of the JSON Schema Validator screen.

Any schema errors will be identified immediately on the screen.

Example

In the following JSON file, two admin-defined functions, TEST_ADD and TEST_ADD_YEAR, are defined.

Before using this example, be sure to remove the comments. Comments are not allowed in JSON.

{

  "TEST_ADD": { //Function name
    "template": "{summand_1} + {summand_2}", //SQL template string
    "returnType": { //Return type of the function
	  "type": "simple",
	  "name": "NUMBER"
    },
    "arguments": { // List of arguments
	  "summand_1": { //Argument key used for lookup in SQL template string
	    "name": "Summand 1", //Argument name, will be displayed on Derived Field Editor
	    "returnType": { // Type of argument.
	      "type": "simple",
	      "name": "NUMBER"
        },
	    "description": "An expression evaluated to a numeric value." //Argument description
	  },
	  "summand_2": {
        "name": "Summand 2",
        "returnType": {
          "type": "simple",
          "name": "NUMBER"
        },
        "description": "An expression evaluated to a numeric value."
      }
    },
	"description": "Addition: add two numbers." //Function description
  },
  "TEST_ADD_YEAR": {
    "template": "({0}) + ({1}) * interval '1 year'",
    "returnType": {
      "type": "simple",
      "name": "DATE"
	},
	"arguments": {
      "0": {
         "name": "Date",
         "returnType": {
           "type": "simple",
           "name": "DATE"
         },
         "description": "An expression 1"
      },
      "1": {
         "name": "Year",
         "returnType": {
           "type": "simple",
           "name": "INTEGER"
         },
         "description": "An expression 2"
      }
	},
    "description": "Add year."
  }
}