CREATE [OR REPLACE] EXTERNAL FUNCTION
Creates or replaces an external function, which is a function that calls code that is executed outside of a SingleStore database.
A CREATE [OR REPLACE] EXTERNAL FUNCTION
statement does not specify the function body.
This topic uses the following terms:
user-defined function (UDF): A function that returns a scalar value.
table-valued function (TVF): A function that returns a
TABLE
value (a rowset).
An external function may be either a UDF or TVF.
Syntax
CREATE [OR REPLACE] EXTERNAL FUNCTION [<database_name>.]function_name ([<parameter_list>])
RETURNS <return_type>
DEFINER = CURRENT_USER
AS REMOTE SERVICE "<service_endpoint>"
FORMAT <data_format> [LINK [database_name.]<connection_link_name>];
parameter_list: <variable_name> <data_type> [<data_type_modifier> [...n]] [, ...]
data_type_modifier:
DEFAULT <default_value> | NOT NULL | NULL | COLLATE collation_name
Return Type
return_type: A scalar value or a
TABLE
value. If aTABLE
value, includes a list of<column name> <data_type>
values such asTABLE(a TEXT, b INT)
.
Arguments
function_name:
The name of the function. Function names must be unique within a database, and cannot be duplicates of names for other tables, views, UDFs, TVFs, user-defined aggregate functions (UDAFs), or stored procedures.
You can also specify
<database_name>
and<function_name>
together by replacing<function_name>
with<database_name>.<function_name>
instead of defaulting to the current context database. For example, you can write the following:CREATE EXTERNAL FUNCTION db.some_func(a int) ...
data_format: One of the following:
JSON
orROWDAT_1
. For more details, see the section Data Formats, below.connection_link_name: The name of a connection link, which is a secure link that stores connection details (credentials and configurations) for an HTTP connection. See Configuring and Using Connection Links for more details.
DEFINER = current_user
is an optional parameter and when used a special flag is stored in the metadata table which indicates if a different ser is required to run the function.
Remarks
Note
SingleStore does not start, stop, or otherwise maintain the service that hosts the external function.
A call to an external UDF can be made anywhere in a SQL statement where an expression is allowed. For example:
SELECT example_external_udf(100,'foo');
SELECT * FROM t WHERE example_external_udf(100,'foo') = 'bar';
SELECT a, COUNT(*) FROM t GROUP BY a HAVING a > example_external_udf(100,'foo');
A call to an external TVF can be made anywhere in a SQL statement where a table reference is allowed, including joining to a table. For example:
SELECT * FROM example_external_tvf(100,'foo');
SELECT * FROM t, example_external_tvf(100,'foo') AS e WHERE t.id = e.id;
Enabling External Functions
External functions must be enabled before they can be used.
To enable the use of all external functions, set the engine variable
enable_external_functions
toON
.To enable the use of all external functions in a list that you specify, set
enable_external_functions
toALLOWLIST
and theexternal_functions_allowlist
engine variable to the names of the external functions that you want to enable. You can use wildcards in the names.For example, to enable the use of any external function located at
http://host1.example.com
and the functionslowercase
anduppercase
located athttp://host2.example.com
, set the engine variableexternal_functions_allowlist
to:"{'endpoints' : ['http://host1.example.com/*', 'http://host2.example.com/uppercase','http://host2.example.com/lowercase']}"
By default, enable_external_functions
is set to OFF
.
Batching of Data Sent from SingleStore to an External Function
A batch contains multiple rows of data and a row id.
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. This is done 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 value of the engine variable batch_external_function
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 will rewrite 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).
The 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.
Data Formats
External functions support two data formats, JSON
and ROWDAT_1
. You specify which format to use when you write a CREATE [OR REPLACE] EXTERNAL FUNCTION
statement. The format you select specifies how the input row(s) that are sent to the external function are serialized and how the output row(s) that are returned from the external function are serialized.
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 of one array. The one array contains the row.
{"data": [[<row id>, <argument 1>, <argument 2>, …]] ]}
Following is a description of the fields, in order, of the JSON sub-array:
<row id>
: A row id, which is an integer greater than or equal to0
. The row id is generated for the rows sent as input to the external function. The output rows have to use matching row ids. The row id in a given row must be larger than the row id in the previous row. Multiple rows may contain the same row id.<argument n>
: The serialization format supports three types:integer
double
variable-length string
All other types are serialized as strings.
Note
Because a single row or multiple rows (a batch) may be sent as input to an external function, the function should be written to handle both cases.
ROWDAT_1
ROWDAT_1
is a SingleStore binary format. It is used for serializing data that is the input to and the output from an external function. The data is in a rowstore format which contains one row, or multiple rows if batching is used.
Following is a description of the fields, in order, in each row:
A row id, which is an integer greater than or equal to
0
. The row id in a given row must be larger than the row id in the previous row. Multiple rows may contain the same row id.The remaining fields contain the arguments to the external function. The serialization format supports three types:
64-bit signed integer
64-bit double (IEEE 754)
length-encoded variable-length string
The serialization format is little-endian. A single byte denoting whether a value is
NULL
(1:NULL
, 0: not-NULL
) precedes all type values (except the row id which cannot beNULL
).All other types are serialized as string.
Note
Because a single row or multiple rows (a batch) may be sent as input to an external function, the function should be written to handle both cases.
CREATE [OR REPLACE] EXTERNAL FUNCTION ... AS REMOTE ...
Use the AS REMOTE
option to create an external function, which is a function that is accessible through an HTTP service. Following are the specifics of the HTTP request and response format.
HTTP request
Request method
A standard HTTP POST request is used.
Standard HTTP request headers
The following standard HTTP headers are used:
accept/content-type
:application/json
orapplication/octet-stream
user-agent
:SingleStore/<S2 version>
Custom HTTP request headers
The following custom HTTP headers are used:
s2-ef-version
: <data format version>s2-ef-name
: <name of the external function>
HTTP response
When a remote function is successful, it returns a HTTP response with status code 200
. A HTTP response with status code other than 200
is treated as error, and the response body contains the error message.
SingleStore Transaction Semantics Are Not Enforced Inside of External Functions
Caution
Any update to state made by an external function is not part of the calling SingleStore transaction. If the calling transaction rolls back, that will not roll back updates made by an external function.
For example, suppose an application runs a SQL query which calls an external function. The function runs and increments a variable v
held in an external process by 1
, from 100
to 101
, and returns v
. After the function completes, but before the transaction commits, the application running the transaction calls ROLLBACK
. The state of the variable v
in the external process remains at 101
; it is not rolled back to 100
.
Using HTTP Connection Links
An HTTP connection link can be used when creating an external function. Using a connection link is more secure than specifying credentials and other sensitive connection details directly in the command. Examples are provided at the end of this topic.
HTTP connections have the following limitations:
Only external functions can use them.
They cannot contain the following header keys:
s2-ef-...
,Accept
,Connection
,Content-Length
,Content-Type
,Upgrade
,User-Agent
.
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 ifssl.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 SingleStoreDB 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.
Example: Encrypt Traffic Between Database and Remote Endpoint
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);
Example: Create a Basic Encrypted HTTP Link Using Only the CONFIG Clause
The following example shows how to create a basic encrypted HTTP link using only the CA bundle path in the security
settings in the CONFIG clause. You can create an HTTP link using only the CA bundle path if the authentication of the SingleStoreDB engine is not required, and only encryption and remote certificate verification are needed. You do not need to specify "security.protocol":"ssl"
in this case.
CREATE LINK link_2 AS HTTP CREDENTIALS '' CONFIG '{"security":{"ssl.ca.location":"<path_to_pe>/ca-cert.pem"}}';
Examples
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 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 | +----------------------------------------------------------------+--------------+
Example: External UDF using DEFINER
CREATE EXTERNAL FUNCTION ext_func() RETURNS TEXT DEFINER ='root'@'%' AS REMOTE SERVICE '...' FORMAT JSON;
To view a list of the functions and see the definer used:
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. | +------------------------------+
Implementing High-Performance Parallel External Functions
Fast, large-batch processing can be accomplished, when in an on-premises configuration, by implementing high-performance parallel external functions. This is done by configuring every node in your SingleStore system to act as a parallel, multi-threaded application server.
By using this approach, rows are batched when sent to a UDF, cross-process calls are not done for every row, and data is not sent across the network. This allows for very fast per-row processing speed. Also, this scales the amount of CPU used for external function computation proportionally to the sizes of your cluster. This avoids potential CPU bottlenecks that could occur if all calls are directed to an external URL (depending on how your networking and the application service to run functions are designed). Other advantages are: any code can be utilized and legacy code and DLLs can be run without needing to recompile the DLLs.
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;
Using Collocated Service
When the COLLOCATED option is specified, commands are sent via Unix domain socket while input/output is sent via shared memory files.
For each collocated external function, the engine will first connect to the Unix domain socket listened to by the collocated service. First, the engine will send the version of the protocol (64-bit little-endian int, the current version is 1). Then the length-embedded function name (the length is also 64-bit little-endian int). The engine will create two in-memory files via memfd_create
, both sealed with F_SEAL_SHRINK
so the files can only grow in size but not shrink and thus is safe to mmap
. The input/output files use sendmsg to transmit to the Unix domain socket as ancillary data.
For each input batch, the input and output will be written to their corresponding shared files using either ROWDAT_1 or JSON format (similar to remote service). Each new batch is going to overwrite the data from the previous batch. The engine will send 64-bit int as the length of the input to the Unix domain socket. The collocated service will send two 64-bit int as the status code and output length to the Unix domain socket. The status code follows HTTP and 200 indicates success. Any code other than 200 indicates an error.
Example:
CREATE EXTERNAL FUNCTION lowercase_string(string_col TEXT) RETURNS TEXT AS COLLOCATED SERVICE '/usr/bin/fd' FORMAT ROWDAT_1;