# CREATE EXTERNAL FUNCTION The `CREATE EXTERNAL FUNCTION` command creates an external function, which is a function that calls code that is executed outside of a SingleStore database. The `CREATE 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 ```sql CREATE [OR REPLACE] EXTERNAL FUNCTION [.]function_name ([]) RETURNS [DEFINER = CURRENT_USER] AS [COLLOCATED | REMOTE] SERVICE "" FORMAT [USING DELIMITED BATCHES] [LINK [database_name.]] [PROTOCOL { V1 | V2 }] parameter_list: [ [...n]] [, ...] data_type_modifier: DEFAULT | NOT NULL | NULL | COLLATE collation_name ``` ## Return Type * `return_type`: A scalar value or a `TABLE` value. If a `TABLE` value, includes a list of ` ` values such as `TABLE(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 `` and `` together by replacing `` with `.` instead of defaulting to the current context database. For example, you can write the following: ```sql CREATE EXTERNAL FUNCTION db.some_func(a int) ... ``` * `parameter_list`: A list of function parameters in the ` []` format. Refer to [Supported Data Types](https://docs.singlestore.com/#section-id235658355629718.md) for the data types accepted by the function parameters. * `data_format`: One of the following: `JSON` or `ROWDAT_1`. For more information, refer to [Data Formats](https://docs.singlestore.com/#section-idm4551069385585632686506106818.md). * `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. Refer to [Configuring and Using Connection Links](https://docs.singlestore.com/db/v10/security/authentication/configuring-and-using-connection-links.md) for more details. * `DEFINER = current_user`: (Optional) When specified, a special flag is stored in the metadata table which indicates if a different user is required to run the function. * `AS [COLLOCATED | REMOTE] SERVICE`: Specifies where and how the external service runs. The default service type is `REMOTE`. Refer to [AS REMOTE SERVICE](https://docs.singlestore.com/#section-id23585514465644.md) and [AS COLLOCATED SERVICE](https://docs.singlestore.com/#section-id235855152290954.md) for more information. ## 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: * ```sql SELECT example_external_udf(100,'foo'); ``` * ```sql SELECT * FROM t WHERE example_external_udf(100,'foo') = 'bar'; ``` * ```sql 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: * ```sql SELECT * FROM example_external_tvf(100,'foo');  ``` * ```sql SELECT * FROM t, example_external_tvf(100,'foo') AS e WHERE t.id = e.id; ``` * SingleStore does not send keep-alive probes or enable connection pooling for remote external functions. ## Enable External Functions External functions must be enabled before they can be used. To enable external functions, set the `enable_external_functions` engine variable to either of the following as required: | Value | Description | | ----------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `ON` | Enables the use of all external functions. | | `ALLOWLIST` | Enables the use of all the external functions in a specified list. When`enable_external_functions`is set to`ALLOWLIST`, you must configure the`external_functions_allowlist`engine variable and specify the names of external functions to enable (allow). 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 functions named`lowercase`and`uppercase`located at`http://host2.example.com`, set the engine variable`external_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`. ## Handshake Protocol The `CREATE EXTERNAL FUNCTION` statement supports an optional protocol clause: `PROTOCOL {V1 | V2}`. `PROTOCOL V1` is the default and remains backward compatible with existing external services. `PROTOCOL V2` adds the input parameter type list to the handshake. Use `PROTOCOL V2` when an external service supports overloaded functions and needs to distinguish between function signatures.  If `PROTOCOL` is omitted, SingleStore uses `PROTOCOL V1`. Existing external functions therefore continue to use the legacy handshake unless they are recreated or replaced with `PROTOCOL V2`. The `USING DELIMITED BATCHES` clause is independent of the handshake version. When it is used, the external service must implement the final zero-size batch and the response behavior. | Protocol | Behavior | Use Case | | -------- | -------------------------------------------------------------------- | --------------------------------------------------------------------------------- | | V1 | Sends the original handshake metadata without input parameter types. | Use with existing services that do not understand`V2`metadata. | | V2 | Extends`V1`with the ordered input parameter type list. | Use when the external service must distinguish overloaded functions by signature. | The `EXTERNAL_PROTOCOL_VERSION` column in the `ROUTINES` information schema view shows the protocol version of an existing external function. Alternatively, run the `SHOW CREATE FUNCTION` command to view the protocol version. A `V2` definition includes `PROTOCOL V2`. Whereas, a `V1` definition may omit the clause because `PROTOCOL V1` is the default. ## 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. ## Protocol V1 HTTP Request Headers The default, `PROTOCOL V1`, request contains the following metadata headers: ``` Accept: application/ Content-Type: application/ Content-Length: User-Agent: SingleStore/ s2-ef-name: s2-ef-version: 1.0 ``` where, * `` is `json` for `JSON` format and `octet-stream` for `ROWDAT_1` format. * `Content-Length` is the length of the request body.  * `s2-ef-name` and `s2-ef-version` are custom HTTP headers that specify the external function name and handshake version, respectively. Connection links can add user-configured headers, but they cannot override reserved headers such as `s2-ef-*`, `Accept`, `Content-Type`, `Content-Length`, or `User-Agent`. ## Protocol V2 HTTP Request Headers `PROTOCOL V2` extends the `PROTOCOL V1` headers to include the input parameter types. For example: ``` Accept: application/ Content-Type: application/ Content-Length: User-Agent: SingleStore/ s2-ef-name: s2-ef-version: 2.0 s2-ef-input-param-types: BIGINT;VARCHAR;FLOAT ``` Note that `s2-ef-version` is `2.0` for `PROTOCOL V2`. For a zero-argument external function, SingleStore omits the parameter type metadata because there are no input types to report. The current implementation validates the constructed Protocol V2 parameter-type header at create time for remote and managed services. The maximum header buffer is 1100 bytes; a serialized header at or above this limit is rejected, and the error advises reducing the number of input parameters. ## 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. ## Protocol V1 Wire Format Protocol V1 sends the following fields during connection initialization: ``` [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 ``` The two file descriptors identify the input and output shared-memory files. After the handshake, the engine sends the input batch length as a 64-bit integer. The service returns two 64-bit integers: status code and output length. Status `200` indicates success; another status indicates an error. ## Protocol V2 Wire Format Protocol `V2` preserves the `V1` fields and appends the parameter-type metadata after the function name and file descriptors: ``` [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;... ``` For example: ``` 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 ``` The additional parameter-type fields are sent during connection initialization and are not repeated for every batch on the connection. For a zero-argument external function, SingleStore sends `parameter_types_length: 0` and omits the `parameter_types_buffer` because there are no input types to report. ## Overloaded External Functions External functions support overloading. You can define multiple external UDFs or TVFs with the same name when they have different input parameter lists. Refer to [Overloaded Functions and Stored Procedures](https://docs.singlestore.com/db/v10/reference/sql-reference/procedural-sql-reference/overloaded-functions-and-stored-procedures.md) for information on enabling overloaded functions, supported data types in function signature of overloaded functions, and other requirements. When a query calls an overloaded external function, the database resolves the function by using the function name and the supplied input parameters, following the same overloading framework used for regular UDFs and stored procedures. To create an overloaded external function, specify `PROTOCOL V2` in the function definition. For example: ```sql CREATE OR REPLACE EXTERNAL FUNCTION format_value(value BIGINT) RETURNS TEXT AS REMOTE SERVICE 'https://example.com/functions/format-value' FORMAT JSON PROTOCOL V2; CREATE OR REPLACE EXTERNAL FUNCTION format_value(value VARCHAR(16)) RETURNS TEXT AS REMOTE SERVICE 'https://example.com/functions/format-value' FORMAT JSON PROTOCOL V2; ``` When the external function uses `PROTOCOL V2`, the handshake includes the input parameter types. This enables the external service to distinguish between different function signatures and identify which overloaded implementation the database is calling. External function overloading follows the same resolution model as overloaded regular UDFs and stored procedures. Refer to [Overloaded Function and Procedure Resolution](https://docs.singlestore.com/db/v10/reference/sql-reference/procedural-sql-reference/overloaded-functions-and-stored-procedures/#section-id235451816069914.md) for more information. ## Supported Data Types External functions support the following data types: | Category | Data Type | | -------------- | ------------------------------------------------------------------------------------------------------------------------- | | Integer | `TINYINT`,`SMALLINT`,`MEDIUMINT`,`INT`,`BIGINT`,`BOOL` | | Floating-point | `FLOAT`,`DOUBLE` | | String | `VARCHAR`,`TEXT`,`CHAR`,`BLOB`,`LONGTEXT`,`MEDIUMTEXT`,`TINYTEXT`,`LONGBLOB`,`MEDIUMBLOB`,`TINYBLOB`,`BINARY`,`VARBINARY` | | JSON | `JSON` | ## Unsupported Data Types The following data types are not supported: | Category | Data Type | | ------------- | ---------------------------------------------------------------------------------- | | Number | `DECIMAL`,`NUMERIC``UNSIGNED`integer types (e.g.,`INT UNSIGNED`,`BIGINT UNSIGNED`) | | Time and Date | `DATE`,`TIME`,`DATETIME`,`TIMESTAMP`,`YEAR` | | Other Types | `GEOMETRY`,`BIT`,`SET`,`ENUM` | To pass an unsupported data type to an external function, explicitly cast it to a supported data type in the query. For example: ```sql 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. ## Data Formats External functions support two data formats, `JSON` and `ROWDAT_1`. Specify the format to use in a `CREATE [OR REPLACE] EXTERNAL FUNCTION` statement. The selected format specifies how the input row(s) sent to the external function and output row(s) returned by the external function are serialized. ## JSON If batching is used, the data format is serialized as a JSON array of arrays as follows: ```json {"data": [[, , , …], [, , , …], ... ]} ``` If batching is not used, the data format is serialized as a JSON array containing one row. ```sql {"data": [[, , , …]] } ``` 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](https://docs.singlestore.com/#section-id235658355629718.md) 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. > **📝 Note**: Because a single row or multiple rows (a batch) may be sent as input to an external function, the function must be written to handle both the cases. ## 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`. > **📝 Note**: Because a single row or multiple rows (a batch) may be sent as input to an external function, the function must be written to handle both the cases. ## AS REMOTE SERVICE Use AS `REMOTE SERVICE ''` to create an external function that is accessible through a remotely hosted HTTP service, where `service_endpoint` specifies the URL of the remote service endpoint. SingleStore sends the function input as an HTTP request and expects an HTTP response. > **📝 Note**: At high call rates, workloads that issue one HTTP request per SQL statement for remote external functions can accumulate TCP `TIME_WAIT` sockets. Following are the specifics of the HTTP request and response format. **HTTP Request Method** * `POST`: A standard HTTP `POST` request is used. **Standard HTTP Request Headers** * `accept/content-type`: * `application/json`, or * `application/octet-stream` * `user-agent: SingleStore/` **Custom HTTP Request Headers** * `s2-ef-version`: Handshake protocol version. * `s2-ef-name`: Name of the external function. **HTTP Response** When a remote function runs successfully, it must return a HTTP response with status code `200`. A HTTP response with status code other than `200` is treated as an error, and the response body contains the error message. Refer to [Handshake Protocol](https://docs.singlestore.com/#section-id235836148550881.md) for information on handshake protocol and wire format. Refer to [Data Formats](https://docs.singlestore.com/#section-idm4551069385585632686506106818.md) for information on supported data formats. ## AS COLLOCATED SERVICE Use the `AS COLLOCATED SERVICE ''` option when the external service runs locally on each SingleStore node that may run the function, where `socket_path` specifies the filesystem path to a Unix domain socket on which the collocated service listens. SingleStore connects to the Unix domain socket, while input and output data are sent via shared memory files. The engine creates two in-memory files via `memfd_create`, both sealed with `F_SEAL_SHRINK`, which allows the files to 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. Use the `external_functions_service_buffer_mb` engine variable to configure the maximum size of the memory-mapped region used to communicate between the SingleStore engine and collocated services. Refer to [Handshake Protocol](https://docs.singlestore.com/#section-id235836148550881.md) for information on handshake protocol and wire format. For each input batch, the input and output are written to the corresponding shared file in either the `ROWDAT_1` or `JSON` format. Each new batch overwrites the data from the previous batch. Refer to [Data Formats](https://docs.singlestore.com/#section-idm4551069385585632686506106818.md) for information on supported data formats. The status code conforms to the HTTP standard. A status code of `200` indicates a successful request. Any status code other than `200` indicates that an error occurred. Before calling an external function, start the collocated service and configure it to listen on the Unix domain socket specified in the function definition. Run one listener process on each SingleStore node that can run the function, including aggregator and leaf nodes. Ensure that the `memsqld` operating-system user has permission to connect to the Unix domain socket. Refer to [Example: SingleStore Collocated Service Using Binary Protocol](https://github.com/singlestore-labs/collocated-service-using-binary-protocol-example) for an example. ## Batching of Data Sent from SingleStore to an External Function A batch contains multiple rows of data (as defined in the external function declaration). A `row_id` is added to each row of data in the batch. ## 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: ```sql CREATE EXTERNAL FUNCTION extFunc(a TEXT) RETURNS TEXT AS REMOTE SERVICE 'http:///' 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. ## Implement Delimited Batch Sets in External Functions When `USING DELIMITED BATCHES` is specified in `CREATE EXTERNAL FUNCTION` statements, SingleStore appends the `batchdelim=true` parameter to the function name in the URI, which indicates that delimited batch sets are enabled for this function. External functions can check this suffix to confirm if the function supports delimited batches. If delimited batch sets are enabled for an external function, SingleStore waits for a response after sending the final zero-size batch. Hence, the external function must send a response after receiving the last batch of zero size as follows: * If there are no additional rows to send, the external function must respond with a HTTP status message and a response size of 0. * If additional rows exist, the `row_id` of the rows sent in the response must match the `row_id` of the original rows in the target SingleStore table. SingleStore matches the rows in the last batch with the corresponding rows in the table using the `row_id`. When `USING DELIMITED BATCHES` is specified, SingleStore does not send keep-alive probes or create a connection pool across queries; it only changes the final empty-batch acknowledgment behavior within a query. ## 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. ## Verify Batching Before increasing the batch size, confirm that the query is using batched execution: * SingleStore always sends data to an external TVF in batches. * An external UDF can be rewritten to an external TVF only for supported query shapes. With `batch_external_functions = AUTO`, SingleStore rewrites external UDFs when possible. An external UDF that cannot be rewritten is called one row at a time. * With `batch_external_functions = NEVER`, external UDFs are called without batching. If the external function is called one row at a time, increasing the batch size does not improve performance. Review the query shape and `batch_external_functions`. ## Verify Flexible Parallelism For large analytic workloads, flexible parallelism allows multiple cores on a leaf node to process portions of a database partition in parallel. Check the configuration and query plan before changing query parallelism. For example: ```sql 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 = ''; ``` A `query_parallelism_per_leaf_core` value of `0` disables flexible parallelism. Values greater than `0` enable it when the query and database layout support it. The default value of `1` allows a parallel operation to use all the available CPU cores on each leaf node, but the actual parallelism depends on the query shape. Use `EXPLAIN` or `PROFILE` to verify the result. The `parallelism_level` in the `EXPLAIN` output indicates whether an operator uses `partition`, `sub_partition`, or `segment` parallelism. Queries on rowstore tables and query shapes that do not support flexible parallelism may not use it even when `query_parallelism_per_leaf_core` is non-zero. For information on supported flexible-parallelism modes and database requirements, refer to [Flexible Parallelism](https://docs.singlestore.com/db/v10/introduction/distributed-architecture/flexible-parallelism.md). ## Tune Batch Size SingleStore recommends performing the following actions, in order, to find the optimal batch size: 1. Establish a baseline with the current batch size and record the query time, external service processing time, CPU and memory usage, and the time spent waiting for input. 2. Test a batch with a larger size, for example 4096 rows, to evaluate its impact on performance and resource usage. Increase the batch size using `external_functions_batch_size`. 3. If the workload benefits from the large batch size, test with a larger batch size. Keep the value that provides the best performance. **Note**: Increasing the batch size beyond an optimal value can have diminishing returns. Avoid increasing the batch size indefinitely. 4. Reevaluate the performance using representative data volumes and concurrency levels. A batch size that performs well for one workload may not be optimal for another. ## SingleStore Transaction Semantics Are Not Enforced Inside of External Functions > **⚠️ Warning**: Any update to state made by an external function is not part of the calling SingleStore transaction. If the calling transaction rolls back, it does 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](https://docs.singlestore.com/db/v10/security/authentication/configuring-and-using-connection-links.md) 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. ```sql CREATE LINK HTTP_link AS HTTP CREDENTIALS '{"headers":{"authorization":"Bearer "}}' 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. ```sql CREATE LINK HTTP_link AS HTTP CREDENTIALS '{"headers":{"authorization":"Bearer ", "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": "", "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. ## Example: Encrypt Traffic Between Database and Remote Endpoint ```sql CREATE LINK link_1 AS HTTP CREDENTIALS '{"security":{"ssl.key.location":"/server-key.pem"}}' CONFIG '{"security":{ "security.protocol":"ssl", "ssl.certificate.location":"/server-cert.pem", "ssl.ca.location":"//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 SingleStore 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. ```sql CREATE LINK link_2 AS HTTP CREDENTIALS '' CONFIG '{"security":{"ssl.ca.location":"/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. ```sql 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. ```sql CREATE EXTERNAL FUNCTION check_paragraph(paragraph TEXT, check_digits BOOL) RETURNS TEXT AS REMOTE SERVICE '' 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. ```python 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. ```sql 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 ```sql 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: ```sql SHOW FUNCTIONS; ``` ```output +------------------------+------------------+----------+-------------+-----------------+------+ | 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. ```sql CREATE EXTERNAL FUNCTION get_file_lines(filename TEXT) RETURNS TABLE(line TEXT) AS REMOTE SERVICE '' 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: ```sql 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. ```sql +------------------------------+ | 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: ```sql 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 ```sql 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 ```sql 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. ```sql 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: ```sql 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. ```sql 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. | Field | Description | | ----------------------------------------- | ---------------------------------------------------------------------------------------------- | | `external_function_staged_by_ref` | Indicates whether the query uses staging by reference. This field appears in JSON output only. | | `external_function_batches` | The number of batches sent to the external service. | | `external_function_rows` | The number of input rows sent to the external service. | | `external_function_max_batch_rows` | The number of rows in the largest batch. | | `external_function_avg_batch_rows` | The average number of rows per batch. | | `external_function_block_limited_batches` | The number of batches sent before reaching`external_functions_batch_size`. | 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. *** Modified at: October 30, 2026 Source: [/db/v10/reference/sql-reference/procedural-sql-reference/create-or-replace-external-function/](https://docs.singlestore.com/db/v10/reference/sql-reference/procedural-sql-reference/create-or-replace-external-function/) (An index of the documentation is available at /llms.txt)