SingleStore logo

  • Pricing

LoginL
Start free
SingleStore logo

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In
SingleStore Spaces/Run your first Python UDF
New

Run your first Python UDF

Notebook

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.

In [1]:

1
%%sql
2
DROP TABLE IF EXISTS reviews;
3


4
CREATE TABLE IF NOT EXISTS
5
reviews (
6
    review_id INT PRIMARY KEY,
7
    store_name VARCHAR(255) NOT NULL,
8
    review TEXT NOT NULL
9
);

Insert sample data

In [2]:

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

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.

In [3]:

1
!pip install nltk

In [4]:

1
from singlestoredb.functions import udf
2
import nltk
3
from nltk.sentiment import SentimentIntensityAnalyzer
4


5
nltk.download('vader_lexicon')
6
sia = SentimentIntensityAnalyzer()
7


8
@udf
9
def review_sentiment(review: str) -> str:
10
    print("review:" + review)
11
    scores = sia.polarity_scores(review)
12
    sentiment = (
13
        "Positive" if scores['compound'] > 0.05 else
14
        "Negative" if scores['compound'] < -0.05 else
15
        "Neutral"
16
    )
17
    print("sentiment:" + sentiment)
18
    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.

In [5]:

1
import singlestoredb.apps as apps
2
connection_info = await apps.run_udf_app(replace_existing=True)

List all registered UDFs

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

In [6]:

1
%%sql
2
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!

Details

About this Template

Learn how to connect to create andpublish a python UDF and call it in SQL.

This Notebook can be run in Shared Tier, Standard and Enterprise deployments.

Tags

starternotebookspython

License

This Notebook has been released under the Apache 2.0 open source license.

See Notebook in action

Launch this notebook in SingleStore and start executing queries instantly.