Python UDFs

Create a Python UDF

Python UDFs can be created in Shared notebook only.

To create a new Python UDF, perform the following steps:

1. In the left navigation, select Editor > Shared.

2. Create or open a shared notebook.

3. Select Publish (on the top right).

New Python UDF

After selecting Publish, a new dialog box appears.

Publish Settings

Select Next.

Select Publish to publish the notebook as Python UDF. Once the Python UDF is published, call the function using SQL Editor.

Example Notebook

The following notebook shows how to publish your first Python UDF:

run-your-first-python-udf.ipynb

ExpandTry it now

SingleStore Notebooks

Run your first Python UDF

Note

This notebook can be run on a Free Starter Workspace. To create a Free Starter Workspace navigate to Start using the left nav. You can also use your existing Standard or Premium workspace with this Notebook.

This feature is currently in Private Preview. Please reach out support@singlestore.com to confirm if this feature can be enabled in your org.

This Jupyter notebook will help you build your first Python UDF using Notebooks, registering it with your database and calling it as part of SQL query.

Create some simple tables

This setup establishes a basic relational structure to store some reviews for restaurants. Ensure you have selected a database.

SQL

Copy

%%sql
DROP TABLE IF EXISTS reviews;

CREATE TABLE IF NOT EXISTS
reviews (
    review_id INT PRIMARY KEY,
    store_name VARCHAR(255) NOT NULL,
    review TEXT NOT NULL
);

Insert sample data

SQL

Copy

%%sql INSERT into reviews (review_id, store_name, review) values
("1", "Single Pizza", "The staff were very respectful and made thoughtful suggestions. I will definitely go again. 10/10!"),
("2", "Single Pizza", "The food was absolutely amazing and the service was fantastic!"),
("3", "Single Pizza", "The experience was terrible. The food was cold and the waiter was rude."),
("4", "Single Pizza", "I loved the ambiance and the desserts were out of this world!"),
("5", "Single Pizza", "Not worth the price. I expected more based on the reviews");

Importing Logging module to enable live logs

Python

Copy

import logging

Define Python UDF functions

Next, we will be Python UDF function using the @udf annotation. We will be using the VADER model of nltk library to perform sentiment analysis on the review text.

Python

Copy

!pip install nltk

Python

Copy

from singlestoredb.functions import udf
import nltk
from nltk.sentiment import SentimentIntensityAnalyzer

nltk.download('vader_lexicon')
sia = SentimentIntensityAnalyzer()

@udf
def review_sentiment(review: str) -> str:
    logging.info(f"Processing review: {review}")

    scores = sia.polarity_scores(review)
    logging.info(f"Sentiment scores: {scores}")

    sentiment = (
        "Positive" if scores['compound'] > 0.05 else
        "Negative" if scores['compound'] < -0.05 else
        "Neutral"
    )
    logging.info(f"Sentiment: {sentiment}")

    return sentiment

Start the Python UDF server

This will start the server as well as register all the functions annotated with @udf as external user defined functions on your selected database.

Python

Copy

import singlestoredb.apps as apps
connection_info = await apps.run_udf_app()

List all registered UDFs

In interactive notebooks, the udf function will be suffixed with _test to differentiate it from the published version

SQL

Copy

%%sql
SHOW functions

Call the UDF from SQL

You will now be able to run queries like

SELECT review_id, store_name, review, review_sentiment_test(review) from reviews order by review_id;

from the SQL editor or any other SQL client.

Try it out by opening another notebook, selecting the current Database and running this query in a new cell.

Publish Python UDF

After validating the Python UDF interactively, you can publish it and access it like

%%sql
SELECT review_id, store_name, review, review_sentiment(review) from reviews order by review_id

enriching your data exploration experience seamlessly!

View an Existing Python UDF

To view an existing Python UDF, select the Python UDF from the Name column. The following actions can be performed for a Python UDF from this page:

• View Live Logs

• Update

• Delete

Update an Existing Python UDF

To update an existing Python UDF, select the ellipsis in the Actions column of the Python UDF, and select Update.

Delete an Existing Python UDF

To delete an existing Python UDF, select the ellipsis in the Actions column of the Python UDF, and select Delete.

Scalar Python UDFs

Scalar Python UDFs are defined with standard Python type annotations, such as int, float, or str. When called from the database, the Python UDF server receives a batch of rows, but the Python UDF itself is invoked once for each individual row of data. This is useful in complex logic with individual records.

The following example demonstrates a scalar Python UDF:

from singlestoredb.functions import udf
import singlestoredb.apps as apps

@udf
async def multiply(x: float, y: float) -> float:
    return x * y

# Start Python UDF server
connection_info = await apps.run_udf_app()
print("UDF server running. Connection info:", connection_info)

This creates the following external function:

CREATE OR REPLACE EXTERNAL FUNCTION `multiply`(
    `x` DOUBLE NOT NULL,
    `y` DOUBLE NOT NULL
)
RETURNS DOUBLE NOT NULL
DEFINER = '<function_owner>'@'%'
AS MANAGED SERVICE '<managed_service_url>'
FORMAT ROWDAT_1;

Use async def for improved cancellation handling.

Invoke the scalar Python UDF using the following command:

SELECT multiply(5.0, 10.0) AS result;

+-----------------------+
| result                |
+-----------------------+
| 50.0                  |
+-----------------------+

Vectorized Python UDFs

Vectorized Python UDFs are defined with vector type annotations, such as numpy.ndarray, pandas.Series, polars.Series, or pyarrow.Array. The Python UDF is called only once for each batch of rows received from the SingleStore database. The entire batch is converted into vectorized inputs, where each column of data corresponds to a single vector object passed as a function parameter. This is useful in high-performance numerical processing.

The following example demonstrates a vectorized Python UDF.

import numpy as np
import numpy.typing as npt
from singlestoredb.functions import udf

@udf
async def vec_multiply(
      x: npt.NDArray[np.float64],
      y: npt.NDArray[np.float64]
    ) -> npt.NDArray[np.float64]:
    return x * y

# Start Python UDF server

import singlestoredb.apps as apps
connection_info = await apps.run_udf_app()

This creates the following external function:

CREATE OR REPLACE EXTERNAL FUNCTION `vec_multiply`(
    `x` DOUBLE NOT NULL,
    `y` DOUBLE NOT NULL
)
RETURNS DOUBLE NOT NULL
DEFINER = '<function_owner>'@'%'
AS MANAGED SERVICE '<managed_service_url>'
FORMAT ROWDAT_1;

Invoke the vectorized Python UDF using the following command:

SELECT vec_multiply(vec_col1, vec_col2)AS result;

+-------------------+
| result            |
+-------------------+
| [4.0, 10.0, 18.0] |
+-------------------+

The function processes all rows in a batch simultaneously. Each row receives its own scalar result, for example, if col1 = 2.0 and col2 = 5.0 for a given row, the result for that row is 10.0.

Scalar Python TVFs

Scalar Python TVFs are defined in the same way as scalar Python UDFs, except that a scalar TVF uses a Table annotation to indicate that the function returns a table. The function must also return the final result wrapped in a Table object.

The following example demonstrates a scalar Python TVF:

from singlestoredb.functions import udf, Table

@udf
async def number_stats(n: int) -> Table[list[int], list[int], list[float]]:
    numbers = list(range(1, n + 1))
    squares = [x ** 2 for x in numbers]
    roots = [round(x ** 0.5, 2) for x in numbers]
    return Table(numbers, squares, roots)

# Start Python UDF server
import singlestoredb.apps as apps
connection_info = await apps.run_udf_app()

This creates the following external function:

CREATE OR REPLACE EXTERNAL FUNCTION `number_stats`(
    `n` BIGINT NOT NULL
)
RETURNS TABLE(
    `numbers` BIGINT NOT NULL,
    `squares` BIGINT NOT NULL,
    `roots` DOUBLE NOT NULL
)
DEFINER = '<function_owner>'@'%'
AS MANAGED SERVICE '<managed_service_url>'
FORMAT ROWDAT_1;

Invoke the scalar Python TVF using the following command:

SELECT * FROM number_stats([5]);

+---------+---------+-------+
| numbers | squares | roots |
+---------+---------+-------+
| 1       | 1       | 1.00  |
| 2       | 4       | 1.41  |
| 3       | 9       | 1.73  |
| 4       | 16      | 2.00  |
| 5       | 25      | 2.24  |
+---------+---------+-------+

SingleStore automatically generates generic column names if no names are associated with the return fields (numbers, squares, and roots in this example). Explicitly name result columns using overrides or schema classes. Refer to Overriding Parameters and Return Value Types for related information.

Vectorized Python TVFs

Vectorized Python TVFs return a table of results. Unlike Python UDFs, which are invoked for each row in a query, a TVF is invoked once with a set of parameters and returns multiple rows and columns. In a vectorized Python TVF, each returned vector represents a column in the output table. The length of each vector determines the number of rows returned.

The following example demonstrates a vectorized Python TVF:

import numpy as np
import numpy.typing as npt
from singlestoredb.functions import udf, Table

@udf
async def vec_table_function(
    n: npt.NDArray[np.int_],
) -> Table[npt.NDArray[np.int_], npt.NDArray[np.float64], npt.NDArray[np.str_]]:
    x = np.array([10] * n[0], dtype=np.int_)
    y = np.array([10.0] * n[0], dtype=np.float64)
    z = np.array(['ten'] * n[0], dtype=np.str_)

    # Returns a tuple of vectors (each column of the output)
    return Table(x, y, z)

# Start Python UDF server

import singlestoredb.apps as apps
connection_info = await apps.run_udf_app()

This creates the following external function:

CREATE OR REPLACE EXTERNAL FUNCTION `vec_table_function`(
    `n` BIGINT NOT NULL
)
RETURNS TABLE(
    `x` BIGINT NOT NULL,
    `y` DOUBLE NOT NULL,
    `z` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
)
DEFINER = '<function_owner>'@'%'
AS MANAGED SERVICE '<managed_service_url>'
FORMAT ROWDAT_1;

Invoke the vectorized Python TVF using the following command:

SELECT * FROM vec_table_function([1]);

+----+------+-----+
| x  | y    | z   |
+----+------+-----+
| 10 | 10.0 | ten |
+----+------+-----+

SingleStore automatically generates generic column names if no names are associated with the return fields (x, y, z). Explicitly name result columns using overrides or schema classes. Refer to Overriding Parameters and Return Value Types for related information.