Create Wasm UDFs

Syntax

SQL

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

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

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


function_name: <standard function name>
typeSpec: '(' <paramList>? ')' RETURNS <result>
abiSpec: BASIC | CANONICAL
numBytes: [0-9]+
paramList: <param> (',' <param>)*
param: <varName> <dataType>
result: TABLE (<paramList>) | <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 UDF:

SQL

CREATE FUNCTION <name> <typeSpec> 
    AS WASM
    (ABI <abiSpec>)
    (GROW TO <numBytes>)
    <contentSrc> 
    (USING EXPORT ‘"’ <wasmExportName> ‘"’)

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

SQL

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

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

SQL

CREATE FUNCTION <name> 
    AS WASM
    ABI BASIC
    (GROW TO <numBytes>)
    <contentSrc> 
    (USING EXPORT ‘"’ <wasmExportName> ‘"’)

Remarks

When using the Canonical ABI, an implicitly-typed Wasm UDF automatically infers the data type information from the WIT specification.
When using the Basic ABI, an implicitly-typed Wasm UDF infers the data type information from the function's signature.
The explicitly-typed Wasm UDF allows you to specify your own data types and maps them to the corresponding type supported by the current ABI. See Wasm Data Type Coercions for more information.

Warning

Using inappropriate data types may cause the UDF to return unpredictable results or an error.
The implicitly-typed Wasm UDF defaults to the Canonical ABI if the ABI clause is not specified in the CREATE FUNCTION statement.
The explicitly-typed Wasm UDF does not support WIT document specification.
The Basic ABI does not support WIT document specification.
The implicitly-typed Wasm UDF does not support the specification of parameters or return types in a UDF.
The WIT document specification must be specified in an implicitly-typed Wasm UDF that uses Canonical ABI.
The GROW TO clause is optional for both the Canonical ABI and Basic ABI. The default size is 16 MB (256 Wasm pages).

Wasm UDF Sources

SingleStore supports Wasm UDFs 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 workspace 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 - Create a Wasm UDF Using Rust

The following example creates a Wasm UDF that computes the expression x^y in Rust and builds the project in Visual Studio Code using the wasm32-wasi target.. This example imports the Wasm module from an S3 bucket

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 UDF

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 = "powerof"
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 powerof.wit.
WIT
```
power-of: func(base: s32, exp: s32) -> s32
```
This specifies that the power-of function accepts two 32-bit integers and returns a single 32-bit integer.

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

Rust

wit_bindgen_rust::export!("powerof.wit");
struct Powerof;
impl powerof::Powerof for Powerof {

    fn power_of(base: i32, exp: i32) -> i32 {
        let mut res = 1;
        for _i in 0..exp {
            res *= base;
        }
        res
    }
}

Compile the program into a Wasm module using the following command:
Shell
```
cargo wasi build --lib
```
The powerof.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 wasi build --lib --release
The resulting powerof.wasm file is created in the target/wasm32-wasi/release/ directory.
Upload the powerof.wasm and powerof.wit files to your cloud storage account, say an S3 bucket named wasm-modules.

Load the Wasm UDF

Execute the following command in the SingleStore command-line:

SQL

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

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

SQL

SELECT power_of(2,4) AS Result;

+-----------+
| Result    |
+-----------+ 
| 16        |
+-----------+

Example 2 - Create a Wasm UDF Using C

The following example creates a Wasm UDF that computes the expression x^y using the C language. See Setup VS Code in the previous example to build this example in VS Code and include the required dependencies, for example wit-bindgen.

Create a .wit file with the WIT specification, for example power.wit.
WIT
```
power-of: func(base: s32, exp: s32) -> s32
```
This specifies that the power-of function accepts two 32-bit integers as arguments and returns a single 32-bit integer.
Run wit-bindgen on your IDL to generate Wasm bindings.
Shell
```
wit-bindgen c --export power.wit
```

Add the following code to the source file (power.c):

C

int32_t power_of(int32_t base, int32_t exp)
{
    int32_t res = 1;
    for (int32_t i = 0; i < exp; ++i)
    {
        res *= base;
    }
    return res;
}

Compile the program into a Wasm module using the following command:

Shell

clang                            \
    --target=wasm32-unknown-wasi \
    -mexec-model=reactor         \
    -s                           \
    -I.                          \
    -o power.wasm                \
    power.c

This command generates the power.wasm file that contains the Wasm module.

Upload the power.wasm and power.wit files to your cloud storage account, say an S3 bucket named wasm-modules.

Run the following command in SingleStore command-line to load the Wasm module:

SQL

CREATE FUNCTION power_of AS WASM FROM S3 'wasm-modules/power.wasm' 
        CREDENTIALS '{
                "aws_access_key_id": "ASIAZPIKLSJ3HM7FKAUB", 
                "aws_secret_access_key": FwoGZXIvYXdzEL3fv [...]"
        }' 
        CONFIG '{"region": "us-east-1"}'
WITH WIT FROM S3 'wasm-modules/power.wit'
        CREDENTIALS '{
                "aws_access_key_id": "ASIAZPIKLSJ3HM7FKAUB", 
                "aws_secret_access_key": FwoGZXIvYXdzEL3fv [...]"
        }' 
        CONFIG '{"region": "us-east-1"}';

The Wasm module is now ready to use. For example,

SQL

SELECT power_of(4,2) AS Result;

+-----------+
| Result    |
+-----------+ 
| 16        |
+-----------+

Example 3 - Load Wasm Using a Base-64 encoded String

The following example shows how to load a Wasm module created in C, which is encoded to a base-64 encoded string:

Add the following code to the source file, say mult.c:
C
```
int mult(int a,int b) {
  return a * b;
  }
```

Compile the program into a Wasm module using the following command:

Shell

clang                            \ 
    --target=wasm32-unknown-wasi \
    -mexec-model=reactor         \
    -Wl,--export-all             \
    -o mult.wasm mult.c

To encode a Wasm file to a base-64 encoded string, run the following command (in Linux):

Shell

base64 -w 0 mult.wasm

AGFzbQEAAAABCgJgAABgAn9/AX8DBAMAAAEEBQFwAQEBBQMBAAIGKwd/AUGAiAQLfwBBgAgLfwBBgAgLfwBBgAgLfwBBgIgEC38AQQALfwBBAQsHqAELBm1lbW9yeQIAEV9fd2FzbV9jYWxsX2N0b3JzAAALX2luaXRpYWxpemUAARlfX2luZGlyZWN0X2Z1bmN0aW9uX3RhYmxlAQAEbXVsdAACDF9fZHNvX2hhbmRsZQMBCl9fZGF0YV9lbmQDAg1fX2dsb2JhbF9iYXNlAwMLX19oZWFwX2Jhc2UDBA1fX21lbW9yeV9iYXNlAwUMX190YWJsZV9iYXNlAwYKSwMCAAsIABCAgICAAAs9AQZ/I4CAgIAAIQJBECEDIAIgA2shBCAEIAA2AgwgBCABNgIIIAQoAgwhBSAEKAIIIQYgBSAGbCEHIAcPCw==

Copy the base-64 encoded from the standard output (stdout).

Run the following command to load the Wasm module:

SQL

CREATE FUNCTION mult AS WASM ABI BASIC FROM BASE64 'AGFzbQEAAAABCgJgAABgAn9/AX8DBAMAAAEEBQFwAQEBBQMBAAIGKwd/AUGAiAQLfwBBgAgLfwBBgAgLfwBBgAgLfwBBgIgEC38AQQALfwBBAQsHqAELBm1lbW9yeQIAEV9fd2FzbV9jYWxsX2N0b3JzAAALX2luaXRpYWxpemUAARlfX2luZGlyZWN0X2Z1bmN0aW9uX3RhYmxlAQAEbXVsdAACDF9fZHNvX2hhbmRsZQMBCl9fZGF0YV9lbmQDAg1fX2dsb2JhbF9iYXNlAwMLX19oZWFwX2Jhc2UDBA1fX21lbW9yeV9iYXNlAwUMX190YWJsZV9iYXNlAwYKSwMCAAsIABCAgICAAAs9AQZ/I4CAgIAAIQJBECEDIAIgA2shBCAEIAA2AgwgBCABNgIIIAQoAgwhBSAEKAIIIQYgBSAGbCEHIAcPCw==';

The Wasm module is now ready to use. For example,

SQL

SELECT mult(4,2) AS Result;

+-----------+
| Result    |
+-----------+ 
| 8        |
+-----------+

Example 4 - Load Wasm Using LINK

The following example shows how to store the credentials in a LINK object and use it in the CREATE FUNCTION statement:

SQL

CREATE LINK accessLink AS S3
        CREDENTIALS '{
                "aws_access_key_id": "ASIAZPIKLSJ3HM7FKAUB", 
                "aws_secret_access_key": FwoGZXIvYXdzEL3fv [...]"
        }' 
        CONFIG '{"region": "us-east-1"}';

CREATE FUNCTION power_of AS WASM 
        FROM LINK accessLink 'wasm-modules/power.wasm'
        WITH WIT FROM LINK accessLink 'wasm-modules/power.wit';

Example References

For more examples, refer to SingleStore Wasm Toolkit.

Troubleshooting

If a Wasm UDF returns a backtrace indicating an out-of-memory exception, increase the amount of linear memory available. To increase the amount of linear memory available:
- Re-create the UDF with a higher value for GROW TO, and/or
- Increase the value of wasm_max_linear_memory_size global variable.
To list all the Wasm UDFs for the current database, use the SHOW FUNCTIONS command. For Wasm UDFs, the Function Type is User Defined Function and the Runtime Type field is Wasm.
To view the SQL command used to create a Wasm UDF, use the SHOW CREATE FUNCTION command. Since the body of the Wasm module is binary and large, it is omitted in the output. For example,
SQL
```
CREATE OR REPLACE FUNCTION mywasm(input int(11) NOT NULL) RETURNS int(11) NOT NULL AS WASM ABI CANONICAL GROW TO 16777216;
```
To view detailed resource metrics on a command using Wasm UDFs, use the PROFILE command.
SingleStore provides a Rust crate that can act as a debugger. For more information see Wasm Remote Debugger Service.

If a Wasm code generates an exception, use the SHOW WASM BACKTRACE command to view the complete backtrace from the error message. For example,

SQL

SHOW WASM BACKTRACE \G

* 1. row *
Backtrace: wasm trap: wasm `unreachable` instruction executed
wasm backtrace:
0: 0xe00d - <unknown>!abort
1: 0x7aaa - std::sys::wasi::abort_internal::hb387f3c4e8f42ceb
at /rustc/7c4b47696907d64eff5621a64eb3c6e795a9ec77/library/std/src/sys/wasi/mod.rs:85:14
2: 0x9be4 - std::process::abort::hdfad6720ede82433
at /rustc/7c4b47696907d64eff5621a64eb3c6e795a9ec77/library/std/src/process.rs:2059:5
3: 0xa0b9 - rust_oom
at /rustc/7c4b47696907d64eff5621a64eb3c6e795a9ec77/library/std/src/alloc.rs:341:5
-------- output truncated --------

On this page

Syntax

Remarks

Wasm UDF Sources

Wasm UDF/TVF Naming Convention

Examples

Example 1 - Create a Wasm UDF Using Rust

Setup VS Code

Create a Wasm UDF

Load the Wasm UDF

Example 2 - Create a Wasm UDF Using C

Example 3 - Load Wasm Using a Base-64 encoded String

Example 4 - Load Wasm Using LINK

Example References

Troubleshooting

Was this article helpful?

On this page

Was this article helpful?