CREATE EXTERNAL FUNCTION

Protocol V2 Metadata

For an external function defined with PROTOCOL V2, SingleStore encodes the parameter type list as an ordered, semicolon-separated list. Each item corresponds to one input parameter in the SQL function definition. The type list uses the canonical SQL type names and omits length, collation, and other type modifiers. For example, a function declared with BIGINT UNSIGNED and VARCHAR(20) COLLATE utf8mb4_bin sends the following type list: BIGINT;VARCHAR.

For a zero-argument external function, SingleStore omits the parameter type metadata because there are no input types to report.

PROTOCOL V2 sends input parameter type metadata for every external function, including non-overloaded functions.

Remote and Managed Services: HTTP Handshake

Remote and managed services use a HTTP POST request. The request body contains the function input data, and headers identify the function, data format, protocol, and, for V2, the input parameter types.

Accept: application/<type>
Content-Type: application/<type>
Content-Length: <length>
User-Agent: SingleStore/<server-version>
s2-ef-name: <functio-name>
s2-ef-version: 1.0

Accept: application/<type>
Content-Type: application/<type>
Content-Length: <length>
User-Agent: SingleStore/<server-version>
s2-ef-name: <function_name>
s2-ef-version: 2.0
s2-ef-input-param-types: BIGINT;VARCHAR;FLOAT

Collocated Services: UNIX-Domain Socket Handshake

Collocated services communicate over a UNIX-domain socket. Input and output data are transferred through shared-memory files, while handshake and batch-control metadata are transferred through the socket. The engine sends two shared-memory file descriptors using ancillary SCM_RIGHTS data.

[8 bytes] protocol_version       // uint64, value 1
[8 bytes] function_name_length   // uint64, N
[N bytes] function_name          // UTF-8 function name
[ancillary data] input_fd, output_fd

[8 bytes] protocol_version       // uint64, value 2
[8 bytes] function_name_length   // uint64, N
[N bytes] function_name          // UTF-8 function name
[ancillary data] input_fd, output_fd
[8 bytes] parameter_types_length // uint64, M
[M bytes] parameter_types_buffer // UTF-8: TYPE1;TYPE2;...

protocol_version:       2
function_name_length:   11
function_name:          PrettyPrint
input_fd, output_fd:    SCM_RIGHTS ancillary data
parameter_types_length: 14
parameter_types_buffer: BIGINT;VARCHAR

Batching of data sent from SingleStore to an external TVF

SingleStore always sends data in batches to an external TVF.

Batching of data sent from SingleStore to an external UDF

External UDFs are rewritten by SingleStore to external TVFs to allow external UDFs to be pushed down to a table scan to support batch processing of inputs.

However, not all external UDFs can be rewritten as TVFs. Only UDFs contained in the projection list (the columns that are selected by a query) or the WHERE clause of a SELECT can be rewritten as external TVFs. Furthermore, these external UDFs must not receive aggregate functions, window functions, or nested SELECT statements as input.

The batch_external_functions engine variable controls the rewrites of external UDFs to external TVFs for batching. The possible values are as follows:

• ALWAYS: Any query, which contains an external UDF that SingleStore cannot convert to an external TVF, will not be executed. External UDFs used in all other types of queries will run and will be rewritten to TVFs.

• AUTO: SingleStore rewrites all external UDFs that it can to external TVFs for batching. Any external UDFs that cannot be rewritten will be called one row at a time.

• NEVER: All external UDFs will be called without batching (i.e., one row at a time).

Send Delimited Batch Sets

Specify the USING DELIMITED BATCHES clause to keep the connection open after all the batches have been processed by SingleStore and accept a final batch from the external function. For example:

CREATE EXTERNAL FUNCTION extFunc(a TEXT)
   RETURNS TEXT AS REMOTE SERVICE 'http://<svchost>/<endpoint>'
   FORMAT ROWDAT_1 USING DELIMITED BATCHES;

By default, once SingleStore processes all the rows in the batch, it sends a final batch of zero size and closes the connection to the external function. However, the external function may sometimes send additional rows after all the rows have been processed. Enabling delimited batch sets allows the external function to acknowledge that all the rows have been received and send additional rows within the last response if required.

Benefits of Batching

• Batching reduces network and CPU utilization.

• Batching does increase memory utilization. However, the benefits of the decrease in network and CPU utilization far outweigh the increase in memory utilization.

• A global variable which assists with batching is external_functions_batch_size. It controls the batch size, in rows, for calls to the external process that services external functions. It has a default size of 512 rows. Most applications should simply use the default, but if the overhead of calling the external process becomes significant, you may wish to increase the batch size.

Tune External Function Workloads

While the default batch size is sufficient for most use cases, if an external function spends significant time waiting for the next batch or there is significant overhead in calling the external process, increase the batch size using the external_functions_batch_size engine variable. A larger batch can reduce the relative overhead of serialization, network transfer, and external process invocation. But, larger batches may also increase the memory usage.

SHOW VARIABLES LIKE 'expected_leaf_core_count';
SELECT @@query_parallelism_per_leaf_core;
SELECT @@sub_to_physical_partition_ratio;

SELECT database_name, num_partitions, num_sub_partitions
FROM information_schema.distributed_databases
WHERE database_name = '<database_name>';

Unsupported Data Types

The following data types are not supported:

To pass an unsupported data type to an external function, explicitly cast it to a supported data type in the query. For example:

SELECT func(CAST(val_date AS BIGINT), CAST(val_decimal AS DOUBLE)) ...

Unsupported types are rejected and the CREATE EXTERNAL FUNCTION statement returns an error.

JSON

If batching is used, the data format is serialized as a JSON array of arrays as follows:

{"data":
    [[<row_id>, <argument 1>, <argument 2>, …],
    [<row_id>, <argument 1>, <argument 2>, …],
    ...
]}

If batching is not used, the data format is serialized as a JSON array containing one row.

{"data":
    [[<row_id>, <argument 1>, <argument 2>, …]]
}

Each JSON sub-array contains the following fields, in order:

• row_id: A row ID, which is an integer greater than or equal to 0. The row_id is generated for the rows sent as input to the external function. The output rows are listed in ascending order. Duplicate row_id values are allowed.

• argument n: Refer to Supported Data Types for the data types accepted by the JSON serialization format.

In the JSON format, SQL NULL and JSON null are both represented as null; an external function cannot distinguish between them. If this distinction matters (e.g., for JSON-typed parameters), use the ROWDAT_1 format. In ROWDAT_1, the NULL indicator is transmitted separately from the value bytes.

ROWDAT_1

ROWDAT_1 is a binary format. It is used for serializing data that is the input to and the output from an external function. The data is in rowstore format which contains one row, or multiple rows if batching is used.

Each row contains the following fields, in order:

• A row_id, which is an integer greater than or equal to 0. For ROWDAT_1 output, row_id values must be strictly increasing. Each row_id must be greater than the previous row_id.

• The remaining fields contain the arguments to the external function. The ROWDAT_1 serialization format encodes the respective values as follows:

  • Integer: An 8-byte signed integer (64-bit).

  • Double: An 8-byte IEEE 754 double (64-bit).

  • String (length-encoded variable-length string): An 8-byte little-endian unsigned integer that specifies the length of the string in bytes, followed by the corresponding number of bytes containing the string data.

  • JSON: Encoded using the same format as strings, with an 8-byte length prefix followed by the JSON text represented as UTF-8 bytes.

The serialization format is little-endian. A single byte denoting whether a value is NULL precedes each value, except the row_id which cannot be NULL, where:

• 1: NULL, indicates that no bytes follow for this field.

• 0: Indicates the following value is not NULL.

Example: Create a HTTP Link Using the CREDENTIALS and CONFIG Clauses

The following example shows how to create an HTTP link, which you can use in a CREATE [OR REPLACE] EXTERNAL FUNCTION statement.

CREATE LINK HTTP_link AS HTTP
CREDENTIALS '{"headers":{"authorization":"Bearer <token>"}}'
CONFIG '{"headers":{"version":"2.3"}}';

Example: Create an HTTP Link Using Only the CREDENTIALS Clause

The following example shows how to create an HTTP link, which you can use in a CREATE [OR REPLACE] EXTERNAL FUNCTION statement.

CREATE LINK HTTP_link AS HTTP
CREDENTIALS '{"headers":{"authorization":"Bearer <token>", "version":"2.3"}}';

Encrypting HTTP Connection Links

To create an encrypted HTTP connection (encrypts the traffic between the database and the remote endpoint), set the following security-specific CONFIG/CREDENTIALS JSON:

{
  "security": {
    "security.protocol": "ssl",
    "ssl.key.location": "/path/to/key.pem",
    "ssl.key.password": "<password_for_private_key>",
    "ssl.certificate.location": "/path/to/cert.pem"
    "ssl.ca.location": "/path/to/ca.pem"
  }
}

Where:

• security.protocol: must be "ssl". No other protocols are supported/implemented.

• ssl.key.location: required. Specifies the path to the private key that will be used during SSL negotiation with a remote server.

• ssl.key.password: optional. Used if ssl.key.location points to a password-protected private key.

• ssl.certificate.location: required. Specifies the path to the certificate with the signature of the public part of the key during the SSL handshake.

• ssl.ca.location: optional. If specified, the SingleStore engine will verify the peer’s certificate against the CA bundle loaded from the provided path.

You can omit the headers object in the JSON when configuring encrypted connections.

CREATE LINK link_1 AS HTTP
CREDENTIALS '{"security":{"ssl.key.location":"<path_to_private_key>/server-key.pem"}}'
CONFIG '{"security":{
  "security.protocol":"ssl",
  "ssl.certificate.location":"<path_to_pem>/server-cert.pem",
  "ssl.ca.location":"/<path_to_ca_bundle>/ca-cert.pem"}}';

CREATE OR REPLACE EXTERNAL FUNCTION 'ef1' (input INT) RETURNS TEXT AS REMOTE SERVICE
  'https://localhost:8123/func1' FORMAT JSON LINK link_1;

SELECT ef1(42);

CREATE LINK link_2 AS HTTP
CREDENTIALS ''
CONFIG '{"security":{"ssl.ca.location":"<path_to_pe>/ca-cert.pem"}}';

Example: An External UDF

Suppose you are writing an external function that implements an elementary grammar checker. The checker scans one paragraph at a time. The checker corrects each paragraph by adding missing periods at the end of sentences and capitalizes the first letter of sentences (if the first letter is lowercase). Also, the checker optionally replaces digits, that are standalone words, in the sentence with the corresponding numbers spelled out. If no corrections to a paragraph are needed, the checker outputs the paragraph, unchanged.

Create the paragraphs table which stores the paragraphs to be checked and whether to check the digits in each paragraph.

CREATE TABLE paragraphs(paragraph TEXT, check_digits BOOL);

The table contains the following data.

+---------------------------------------------------------------+--------------+
| paragraph                                                     | check_digits |
+---------------------------------------------------------------+--------------+
| this paragraph has 1 sentence                                 |            1 |
| this paragraph has 2 sentences. this is the second sentence   |            0 |
| This paragraph has one sentence and doesn't need corrections. |            1 |
+---------------------------------------------------------------+--------------+

Create the definition for an external function that checks a paragraph for grammar mistakes. If 1 is sent as input to the check_digits parameter, then digits are included as part of the grammar check.

CREATE EXTERNAL FUNCTION check_paragraph(paragraph TEXT, check_digits BOOL)
RETURNS TEXT
AS REMOTE SERVICE '<endpoint_URL>'
FORMAT JSON;

Implement the body of the external function and store the implementation on the HTTP server. An example implementation using an AWS Lambda function written in Python follows. Note that the implementation of the helper functions convert_numbers_to_words, capitalize_sentences and add_missing_periods_to_sentences is not shown.

import json

def lambda_handler(event, context):
    request = json.loads(event['body'])
    response = []
    for row_id, paragraph, check_digits in request["data"]:
        if check_digits:
            paragraph = convert_numbers_to_words(paragraph)
        paragraph = capitalize_sentences(paragraph)
        paragraph = add_missing_periods_to_sentences(paragraph)
        response.append([row_id, paragraph])
    return { 'statusCode': 200, 'body': json.dumps({"data": response}) }

Query the paragraphs table and call the external function as part of the query.

SELECT check_paragraph(paragraph, check_digits) as corrected_paragraph,
check_digits FROM paragraphs;

The external UDF is rewritten to an external TVF; the query calls the external function only once with the following input:

{"data":
    [[0, "this paragraph has 1 sentence", 1],
    [1, "this paragraph has 2 sentences. this is the second sentence", 0],
    [2, "This paragraph has one sentence and doesn't need corrections.", 1]]
}

After processing the input data, the external function returns the following data to the calling query. The data contains the corrected paragraphs. Paragraphs not needing any corrections are also included.

{"data":
    [[0, "This paragraph has one sentence."],
    [1, "This paragraph has 2 sentences. This is the second sentence."],
    [2, "This paragraph has one sentence and doesn't need corrections."]]
}

The query returns the following output.

+---------------------------------------------------------------+--------------+
| corrected_paragraph                                           | check_digits |
+---------------------------------------------------------------+--------------+
| This paragraph has one sentence.                              |            1 |
| This paragraph has 2 sentences. This is the second sentence.  |            0 |
| This paragraph has one sentence and doesn't need corrections. |            1 |
+---------------------------------------------------------------+--------------+

CREATE EXTERNAL FUNCTION ext_func()
RETURNS TEXT DEFINER ='root'@'%'
AS REMOTE SERVICE '...' FORMAT JSON;

SHOW FUNCTIONS;

+------------------------+------------------+----------+-------------+-----------------+------+
| Functions_in_func_proc | Function Type    | Definer  | Data Format | Runtime Type    | Link |
+------------------------+------------------+----------+-------------+-----------------+------+
| ext_func               | External User    | root@%   | JSON        | Remote Service  |      |
|                        | Defined Function |          |             |                 |      |
+------------------------+------------------+----------+-------------+-----------------+------+

Example: An External TVF

Suppose you want to read the contents of an external file and store each line in a row in a table.

Create the definition for the external function, which gets the contents of a file that is passed as input.

CREATE EXTERNAL FUNCTION get_file_lines(filename TEXT)
RETURNS TABLE(line TEXT)
AS REMOTE SERVICE '<endpoint_URL>'
FORMAT JSON;

Implement the body of the external function and store the implementation on the HTTP server. The implementation is not shown here.

Query the external function:

SELECT line FROM get_file_lines("simple_file.txt");

When the external function runs, it returns a JSON array of arrays, which contains the data in simple_file.txt:

{"data":
    [[0, "This is the first sentence."],
    [0, "This is the second sentence."],
    [0, "This is the third sentence."]]
}

When the query that calls the external function completes, the query returns the following output.

+------------------------------+
| line                         |
+------------------------------+
| This is the first sentence.  |
| This is the second sentence. |
| This is the third sentence.  |
+------------------------------+

Example: Using Remote Service

The default service type is REMOTE and the endpoint is expected to be the URL of a remote HTTP service. The following example uses an AWS endpoint as the remote service:

CREATE OR REPLACE EXTERNAL FUNCTION uppercase_string(string_col TEXT)
     RETURNS TEXT
     AS REMOTE SERVICE
     'https://fnc.execute-api.us-east-1.amazonaws.com/prod/upcase_string'
     FORMAT JSON;

Example: Using localhost as Remote Service

CREATE EXTERNAL FUNCTION ext_udf2(col_a INT, col_b INT)
     RETURNS TABLE(col_1 TEXT, col_2 BOOL)
     AS REMOTE SERVICE 'http://localhost:3305/cool_table_func'
     FORMAT JSON;

Example: Using Collocated Service

CREATE EXTERNAL FUNCTION lowercase_string(string_col TEXT)
RETURNS TEXT
AS COLLOCATED SERVICE 'tmp/s2conn.sock'
FORMAT ROWDAT_1;

Stage Rows by Reference for Collocated TVFs

When an external table-valued function (TVF) is called, it keeps the input row’s columns available until the external service returns its response. By default, each input row is copied into an intermediate staging area.

Set external_functions_vpi_ref_staging to ON to allow eligible queries to skip this copy. The variable is OFF by default.

SET GLOBAL external_functions_vpi_ref_staging = ON;

Setting a global engine variable requires the SUPER privilege.

This variable participates in the plan cache, when in the default mode, such that queries are recompiled after the value changes. Plans compiled with the variable enabled and disabled can coexist in the plan cache.

A query uses staging by reference only when all of the following conditions are met:

• external_functions_vpi_ref_staging is ON.

• enable_external_functions is ON.

• batch_external_functions is not set to NEVER.

• The query joins exactly one local columnstore table to exactly one external TVF.

• The join is an inner join.

• The external function uses AS COLLOCATED SERVICE and FORMAT ROWDAT_1.

• The external function does not use USING DELIMITED BATCHES.

Queries that do not meet these conditions use the default copy behavior.

Aligning the external function batch size with the columnstore scan batch size can improve performance when staging by reference. For best results, set external_functions_batch_size to 4096:

SET GLOBAL external_functions_batch_size = 4096;

Because external_functions_batch_size is a global setting, evaluate the effect on workloads that use the default copy behavior before changing it cluster-wide.

The benefit comes from reducing data copying. Queries that pass several wide TEXT or VARCHAR columns to the external TVF may see the maximum gains.

Queries that project only the function argument and its result may see little improvement because they already copy very little data. Highly selective filters may also reduce the benefit. Measure a representative query with the variable set to OFF and ON before enabling it for a production workload.

The external_function_staged_by_ref variable is available in JSON EXPLAIN. Use PROFILE and SHOW PROFILE JSON to inspect the runtime batch and row counters.

SET GLOBAL external_functions_vpi_ref_staging = ON;

PROFILE SELECT COUNT(*), SUM(LENGTH(t.description)), e.result
FROM products AS t JOIN classify_tvf(t.name) AS e;

SHOW PROFILE JSON;

The five batching counters are reported on the external function join operator in text and JSON PROFILE output. The external_function_staged_by_ref variable is reported on the external TVF node in the join’s right-input subtree.

Compare external_function_avg_batch_rows with external_functions_batch_size to evaluate batch utilization. Compare external_function_block_limited_batches with external_function_batches to assess how effectively batches use the configured batch size.