Create Wasm TVFs
You can create Wasm TVFs using the CREATE FUNCTION
statement.
Note
SingleStoreDB supports only the Canonical ABI with Wasm TVFs because the return type is TABLE
.
Syntax
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:
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:
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
ofRECORDS
, specified aslist<record-type>
in the WIT specification.You can call a Wasm TVF in the
FROM
clause of aSELECT
statement.The implicitly-typed and explicitly-typed Wasm TVFs default to the Canonical ABI if the
ABI
clause is omitted in theCREATE FUNCTION
statement.The WIT document specification must be specified.
The
GROW TO
clause is optional for the Canonical ABI. The default size is16 MB
(256 Wasm pages).
Wasm TVF Sources
SingleStoreDB 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 theCREATE FUNCTION
statement can be modified using themax_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:CREATE FUNCTION wasm_udf AS WASM FROM HTTP 'https://url/to/udf.wasm' ...
Optionally, you can specify the
CONFIG
andCREDENTIALS
clauses for the URL. TheLINK
object is not supported withHTTP
, and the credential configuration must be provided inline in theCREATE 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 SingleStoreDB, you may use the pushwasm
tool to upload the .wit
and .wasm
files to your cluster and create Wasm UDFs. For more information, see 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:
run: func(pass1: f32, pass2: f32, pass3: f32) -> f32
The following example specifies a custom name calc_score:
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 forwasm32-wasi
target. Refer to cargo wasi for more information.cargo install cargo-wasi
Proceed with the example.
Create a Wasm TVF
Initialize a Cargo package in this directory using the following command:
cargo init --vcs none --lib
Update the Cargo.toml file with the following configuration:
[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.
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:
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:
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 thecargo build
command instead to enable further optimizations and remove the debug info. For example,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:
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,
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:
SELECT * FROM strg; **** +------------+ | Name | +------------+ | anna_moore | | adam_west | | sam_duke | +------------+
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.