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](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/bltdcd4935455d73ef6/668336a0ee05f3876c3010e8/img_blog_BP-Building-Query-Lambdas-with-SingleStore-TVF-and-Data-API_new-primary.png?width=736&disable=upscale&auto=webp)
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 requestsimport jsoncredentials = ('admin', '<pwd>') ## username & pw of SingleStoreworkspacehost = '<hostname>'api_endpoint = '/api/v2/query/rows'url = 'https://' + host + api_endpoint# Query + databasequery = "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.