July 1, 2024 in Product

Building Query Lambdas with SingleStore's TVF and Data API

Stephen Kallianos

Senior Manager, SE Americas

Ben Paul

Cloud Solutions Engineer

In this blog post, we'll demonstrate how to create parameterized queries (similar to Rockset's Query Lambdas) using SingleStore's Table-Valued Functions and the Data API. This allows us to manage and execute complex queries efficiently within SingleStore.

Building Query Lambdas with SingleStore's TVF and Data API

Step 1. Setting up SingleStore

Before creating TVFs, we need to set up our SingleStore database and tables.

Create a SingleStore database

CREATE DATABASE my_database;

Create a table in SingleStore

USE my_database;
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255),
    created_at TIMESTAMP
);

Insert sample data

INSERT INTO users (user_id, username, email, created_at) VALUES
(1, 'john_doe', 'john@example.com', NOW()),
(2, 'jane_doe', 'jane@example.com', NOW());

Step 2. Creating a Table-Valued Function (TVF) in SingleStore

Table-Valued Functions (TVFs) allow us to create reusable, parameterized queries in SingleStore. A TVF is a callable routine that accepts input parameters, executes a single SELECT statement in the function body, and returns a single table-type value (similar to a view).

Create a TVF to query user data

Let’s create a simple TVF, which selects one row from the users table, dynamically filtering on the user_id column.

CREATE FUNCTION get_user_by_id(ins_user_id INT)
RETURNS TABLE AS RETURN SELECT * FROM users WHERE user_id = ins_user_id;

Step 3. Accessing the TVF via SingleStore Data API

SingleStore's Data API allows us to execute queries and functions via HTTP requests.

Execute the TVF using the Data API

curl -X POST 'https:/</hostname>/api/v2/query/rows' \
-u 'admin:<pwd> \
-H 'Content-Type: application/json' \
-d '{
  "sql": "SELECT * FROM get_user_by_id(?)",
  "database": "my_database",
  "args": [2]}'

Step 4. Automating data ingestion and query execution

To automate data ingestion and query execution, you can create scripts or use ETL tools that interact with SingleStore's Data API.

Python script to execute TVF

import requests
import json

credentials = ('admin', '<pwd>') ## username & pw of SingleStore 
workspace
host = '<hostname>'
api_endpoint = '/api/v2/query/rows'
url = 'https://' + host + api_endpoint

# Query + database
query = "SELECT * FROM get_user_by_id(?)"

sql_statement = {
    'sql': query,
    'args': [2],
    'database':'my_database'
}

resp = requests.post(url,json=sql_statement,auth=credentials)
print(resp.json()['results'][0]['rows'])

Conclusion

In this blog post, we demonstrated how to create parameterized queries using SingleStore's Table-Valued Functions (TVF) and execute them via the Data API. This approach allows us to achieve similar functionality to Rockset's Query Lambdas within the SingleStore ecosystem. By leveraging TVFs and the Data API, we can efficiently manage and execute complex queries, providing a powerful solution for data management and analysis.

If you’re looking for a Rockset alternative (or any database), feel free to schedule time with a member of the SingleStore team here.

Product Engineering