Building Query Lambdas with SingleStore's TVF and Data API

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.

Step 1. Setting up SingleStore

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

Create a SingleStore database

1CREATE DATABASE my_database;

Create a table in SingleStore

1USE my_database;2CREATE TABLE users (3    user_id INT PRIMARY KEY,4    username VARCHAR(255),5    email VARCHAR(255),6    created_at TIMESTAMP7);

Insert sample data

1INSERT INTO users (user_id, username, email, created_at) VALUES2(1, 'john_doe', 'john@example.com', NOW()),3(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.

1CREATE FUNCTION get_user_by_id(ins_user_id INT)2RETURNS 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

1curl -X POST 'https:/</hostname>/api/v2/query/rows' \2-u 'admin:<pwd> \3-H 'Content-Type: application/json' \4-d '{5  "sql": "SELECT * FROM get_user_by_id(?)",6  "database": "my_database",7  "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

1import requests2import json3
4credentials = ('admin', '<pwd>') ## username & pw of SingleStore 5workspace6host = '<hostname>'7api_endpoint = '/api/v2/query/rows'8url = 'https://' + host + api_endpoint9
10# Query + database11query = "SELECT * FROM get_user_by_id(?)"12
13sql_statement = {14    'sql': query,15    'args': [2],16    'database':'my_database'17}18
19resp = requests.post(url,json=sql_statement,auth=credentials)20print(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.