Create Wasm TVFs

Syntax

SQL

CREATE [OR REPLACE] FUNCTION function_name 
    { <explicitTypedPred> | <implicitTypedPred> }
    (USING EXPORT '"' <wasmExportName> '"')

explicitTypedPred: 
        <typeSpec> 
        AS WASM 
        (ABI CANONICAL )
        (GROW TO <numBytes>) 
        FROM <contentSrc>

implicitTypedPred: 
        RETURNS TABLE
        AS WASM 
        (ABI CANONICAL )
        (GROW TO <numBytes>)
        FROM <contentSrc> 
        (WITH WIT FROM <contentSrc>)


function_name: <standard function name>
typeSpec: '(' <paramList>? ')' RETURNS TABLE '(' <paramList> ')'
numBytes: [0-9]+
paramList: <param> (',' <param>)*
param: <varName> <dataType>
varName: <standard column name>

contentSrc: BASE64 '"' <base64> '"'               |
            HTTP '"' <url> '"' <cred>? <config>?  |
            LINK <linkName> <connStr>             |
            S3 <s3_config>?                       |
            AZURE <azure_config>?                 |
            GCS <gcs_config>?

linkName: <link connection name>
connStr: <provider-specific path to a resource>
base64: <base64-encoded string>
url: <standard URL form>
cred: CREDENTIALS '"' <credSpec> '"'
credSpec: <json with provider-specific credentials>
config: CONFIG '"' <configSpec> '"'
configSpec: <json with provider-specific configuration>

s3_config:
    S3 { '<bucket-name>' | '<bucket-name/path>' }
      [CONFIG '<configuration_json>']
      CREDENTIALS '<credentials_json>'

azure_config:
    AZURE { '<container-name>' | '<container-name/object-name>' | '<container-name/prefix/object-name>' }
      CREDENTIALS '<credentials_json>'
      [CONFIG '<configuration_json>']

gcs_config:
   GCS { '<bucket-name>' | '<bucket-name/path>' }
     CREDENTIALS '<credentials_json>'
     [CONFIG '<configuration_json>']

Here's an expansion of the syntax for an explicitly-typed Wasm TVF:

SQL

CREATE FUNCTION <name> '(' <paramList> ')'  
    RETURNS TABLE '(' <paramList> ')'
    AS WASM
    (ABI CANONICAL)
    (GROW TO <numBytes>)
    <contentSrc> 
    (USING EXPORT ‘"’ <wasmExportName> ‘"’)

Here's an expansion of the syntax for an implicitly-typed Wasm TVF using Canonical ABI:

SQL

CREATE FUNCTION <name> 
    RETURNS TABLE
    AS WASM 
    (ABI CANONICAL)
    (GROW TO <numBytes>)
    <contentSrc> 
    (WITH WIT <contentSrc>)
    (USING EXPORT ‘"’ <wasmExportName> ‘"’)

Remarks

The Wasm function must return an ARRAY of RECORDS, specified as list<record-type> in the WIT specification.
You can call a Wasm TVF in the FROM clause of a SELECT statement.
The implicitly-typed and explicitly-typed Wasm TVFs default to the Canonical ABI if the ABI clause is omitted in the CREATE FUNCTION statement.
The WIT document specification must be specified.
The GROW TO clause is optional for the Canonical ABI. The default size is 16 MB (256 Wasm pages).

Wasm TVF Sources

SingleStore supports Wasm TVFs created from the following sources in the CREATE FUNCTION statement:

Base-64 encoded string: You can pass the contents of a Wasm module directly to the CREATE FUNCTION statement as an inline base-64 encoded string, see Examples. The maximum size of the CREATE FUNCTION statement can be modified using the max_allowed_packet global variable.
Remote URL: To download the content of a Wasm module from a remote endpoint, you can specify the URL of the endpoint using the HTTP clause. Here's an example:
SQL
```
CREATE FUNCTION wasm_udf AS WASM FROM HTTP 'https://url/to/udf.wasm' ...
```
Optionally, you can specify the CONFIG and CREDENTIALS clauses for the URL. The LINK object is not supported with HTTP, and the credential configuration must be provided inline in the CREATE FUNCTION command.
Cloud Provider: You can download the content of a Wasm module from the following cloud providers: S3, Azure, and GCS. You also can specify the URL and connection credentials using the LINK clause, see Examples.

For examples on using the CONFIG <configSpec> clause, see BACKUP DATABASE.

Note

While not officially supported by SingleStore, you may use the pushwasm tool to upload the .wit and .wasm files to your cluster and create Wasm UDFs, UDAFs, or TVFs. For more information, refer to pushwasm.

Wasm UDF/TVF Naming Convention

You can specify a Wasm UDF/TVF name that is different from the WIT specification in the CREATE FUNCTION statement. When specifying a custom name for the Wasm UDF/TVF, you may use any name that is valid for a database UDF/TVF. This will be the Wasm function's alias in the database. Additionally, specify the real name of the Wasm function, as declared in the WIT file, in the USING EXPORT clause. This name is case-sensitive and can contain hyphens.

For example, consider the following WIT specification:

WIT

run: func(pass1: f32, pass2: f32, pass3: f32) -> f32

The following example specifies a custom name calc_score:

SQL

CREATE FUNCTION calc_score AS WASM
FROM LINK rec 'module/run.wasm'
WITH WIT FROM LINK rec 'module/run.wit'
USING EXPORT "run";

You can use distinct aliases to create multiple UDFs/TVFs from the same Wasm function.

If the USING EXPORT clause is omitted from the CREATE FUNCTION statement:

When using a WIT file, the name of the Wasm UDF/TVF must match the name of the corresponding Wasm function as declared in the WIT IDL, after substituting hyphens with underscores.
When you are not using a WIT file, the name of the Wasm UDF/TVF must match the name of the function exported from the module. This is usually the same function name declared in the source language.

If the USING EXPORT clause is omitted and the Wasm module has functions with the same name but different case, the CREATE FUNCTION statement returns an error. You cannot import functions with matching case-insensitive names from a Wasm module.

Examples

Example 1

The following example creates a Wasm TVF (in Rust) that splits a string and returns the two sub-strings.

Setup VS Code

Install the Dev Containers extension in VS Code.
Open the Command Palette, and select Dev Containers: Open Folder in Container.
Navigate to the directory where you want to build your project, and select Open.
On the Select Features dialog, from the list, select the Rust container.
Use defaults for other configuration items.
After VS Code completes building the project, open a terminal. Install the cargo-wasi subcommand to build the code for wasm32-wasi target. Refer to cargo wasi for more information.
Shell
```
cargo install cargo-wasi
```

Proceed with the example.

Create a Wasm TVF

Initialize a Cargo package in this directory using the following command:
Shell
```
cargo init --vcs none --lib
```

Update the Cargo.toml file with the following configuration:

TOML

[package]
name = "split"
version = "0.1.0"
edition = "2018"

[dependencies]
wit-bindgen-rust = { git = "https://github.com/bytecodealliance/wit-bindgen.git", rev = "60e3c5b41e616fee239304d92128e117dd9be0a7" }

[lib]
crate-type = ["cdylib"]

Create a .wit file with the WIT specification, for example split.wit.

WIT

record subphrase {
  str: string,
  idx: s32
}
split-str: func(phrase: string, delim: string) -> list<subphrase>

Add the following code to the src/lib.rs file:

Rust

wit_bindgen_rust::export!("split.wit");
struct Split;
use crate::split::Subphrase;

impl split::Split for Split {

    fn split_str(phrase: String, delim: String) -> Vec<Subphrase> {
        phrase
            .split(&delim)
            .scan(0, |idx, s| {
                let current = Subphrase {
                    str: s.to_string(),
                    idx: *idx as i32
                };
                *idx += (s.len() + delim.len()) as i32;
                Some(current)
            })
            .collect()
    }
}

Compile the program into a Wasm module using the following command:
Shell
```
cargo wasi build --lib
```
The split.wasm file is created in the target/wasm32-wasi/debug/ directory.
Note
When your code is debugged and ready for production, add the --release flag to the cargo build command instead to enable further optimizations and remove the debug info. For example,
Shell
cargo build --lib --release
The resulting split.wasm file is created in the target/wasm32-wasi/release/ directory.
Upload the split.wasm and split.wit files to your cloud storage account, say an S3 bucket named wasm-modules.

Load the Wasm TVF

Execute the following command in the SingleStore command line:

SQL

CREATE DATABASE wasm_tutorial;
USE wasm_tutorial;
CREATE FUNCTION split_str RETURNS TABLE AS WASM FROM S3 'wasm-modules/split.wasm' 
        CREDENTIALS '{
                "aws_access_key_id": "ASIAZPIKLSJ3HM7FKAUB", 
                "aws_secret_access_key": FwoGZXIvYXdzEL3fv [...]"
        }' 
        CONFIG '{"region": "us-east-1"}'
WITH WIT FROM S3 'wasm-modules/split.wit'
        CREDENTIALS '{
                "aws_access_key_id": "ASIAZPIKLSJ3HM7FKAUB", 
                "aws_secret_access_key": FwoGZXIvYXdzEL3fv [...]"
        }' 
        CONFIG '{"region": "us-east-1"}';

This command imports the split.wit and split.wasm files created earlier in this example. The Wasm TVF is now ready to use. For example,

SQL

SELECT * FROM split_str('wasm_rocks_the_house', '_');

+-------+-----+
| str   | idx |
+-------+-----+
| wasm  |   0 |
| rocks |   5 |
| the   |  11 |
| house |  15 |
+-------+-----+

Example 2

You can use a Wasm TVF in the FROM clause of a query, i.e., you can specify the values from a table on the left of the Wasm TVF in the FROM clause as arguments to the Wasm TVF. Use a comma or the JOIN keyword before the Wasm TVF name in the FROM clause. This feature performs a an implicit lateral join between the expression on the left of the Wasm TVF and the results of the Wasm TVF.

The following example code uses the split_str() Wasm TVF from Example 1 and the strg table:

SQL

SELECT * FROM strg;

+------------+
| Name       |
+------------+
| anna_moore | 
| adam_west  |
| sam_duke   |
+------------+

SQL

SELECT Name, str, idx FROM strg JOIN split_str(Name, '_');

+------------+-------+-----+
| Name       | str   | idx |
+------------+-------+-----+
| anna_moore | anna  | 0   |
| anna_moore | moore | 5   |
| sam_duke   | sam   | 0   |
| sam_duke   | duke  | 4   |
| adam_west  | adam  | 0   |
| adam_west  | west  | 5   |
+------------+-------+-----+

For more examples on using Wasm TVFs, refer to SingleStore Wasm Toolkit.

Create Wasm TVFs

On this page

Syntax

Remarks

Wasm TVF Sources

Wasm UDF/TVF Naming Convention

Examples

Example 1

Setup VS Code

Create a Wasm TVF

Load the Wasm TVF

Example 2

Was this article helpful?

On this page

Was this article helpful?