Bridging High-Speed Databases with Intelligent Experiences

SingleStoreDB is renowned for its ability to handle both transactional (OLTP) and analytical (OLAP) workloads at exceptional speed within a single, distributed SQL database. Its real-time performance makes it a powerful choice for operational databases, data warehouses, and applications demanding low latency. While SingleStore excels at storing, processing, and querying data quickly, the next step is often activating this valuable, fast-moving data to drive sophisticated, AI-powered personalization like real-time recommendations and adaptive search.

How do you leverage the up-to-the-millisecond data in SingleStore – perhaps user profiles, product inventory, or recent interactions – to power AI models that predict user intent right now? How do you train complex machine learning models on your SingleStore data without impacting its core performance or building separate, complex data pipelines? Shaped provides a direct and efficient solution through its compatibility with the MySQL wire protocol used by SingleStore.

Shaped is an AI-native relevance platform designed to connect securely to databases like SingleStore (using the MySQL protocol), ingest data from specified tables, train state-of-the-art ML models, and serve personalized search rankings and recommendations via simple APIs. This post explains the benefits of connecting SingleStore to Shaped and guides you through the integration process.

Why Connect SingleStore to Shaped? Leverage Speed and Data for AI

Connecting your fast SingleStore database directly to Shaped allows you to combine SingleStore's real-time data processing capabilities with Shaped's advanced AI personalization engine, enabling powerful use cases:

• Real-Time Recommendations: Utilize the freshest data available in SingleStore:
  
  • Incorporate Live Operational Data: Generate recommendations based on the most current user status, inventory levels, or pricing stored in SingleStore.
  • React to Recent Interactions: Train models using low-latency interaction data captured in SingleStore tables.
  • Accurate Catalog Awareness: Sync product attributes directly from SingleStore catalog tables, ensuring recommendations reflect the absolute latest information.
• Enhanced Search Personalization: Improve search relevance using reliable, fast data:
  
  • Attribute-Based Filtering: Use precise, up-to-date item attributes synced from SingleStore for powerful filtering via Shaped's APIs.
  • Personalize Based on Current State: Tailor search results based on the most recent user profile information or context available in SingleStore.
• Simplified Data Flow for Fast Data: Eliminate the need for complex ETL or CDC pipelines just to get data out of your high-speed SingleStore database into an ML system. Shaped connects and syncs efficiently.
• Efficient Incremental Updates: Keep models fresh by periodically syncing only new or updated data from SingleStore tables based on a replication key, designed to minimize impact on your high-performance database.
• Offload Heavy ML Compute: Let Shaped handle the computationally intensive tasks of training and serving complex AI models, preserving SingleStore's resources for its core database functions.

How it Works: Connecting via the MySQL Protocol

Shaped leverages SingleStore's compatibility with the MySQL wire protocol for seamless integration.

1. Connection: Shaped connects to your SingleStore cluster using standard MySQL connection parameters (host, port, read-only user credentials).
2. Read-Only Access: You create a dedicated user within SingleStore with SELECT (read) permissions on the necessary tables.
3. Incremental Sync: Shaped uses a replication_key column (like an updated_at timestamp or an auto-incrementing id) that you specify. On scheduled syncs, Shaped queries SingleStore for rows where this key's value is greater than the last recorded value, efficiently fetching only new or updated data.

Connecting SingleStore to Shaped

The setup involves creating a read-only user in SingleStore, ensuring network access (IP allowlisting), and configuring the dataset connection in Shaped using the MYSQL schema type.

Step 1: Prepare SingleStore - Create Read-Only User & Grant Permissions

For security and proper operation, create a dedicated user in SingleStore with only read permissions.

1. Connect to SingleStore: Use the singlestore CLI, a SQL client compatible with MySQL, or the SingleStore portal UI to connect to your database as an administrative user.

2. Create Read-Only User: Execute SQL commands similar to MySQL to create the user and grant permissions. Refer to the official SingleStore User Management documentation for the precise syntax applicable to your version. The concept is similar to the MySQL example:

1 -- Example conceptual commands (Verify exact syntax with SingleStore docs):
2 -- 1. Create a new user (replace 'username' and 'password')
3 --    Specify appropriate host access ('%' allows any, firewall provides security)
4 CREATE USER 'shaped_readonly'@'%' IDENTIFIED BY 'YOUR_SECURE_PASSWORD_HERE!';
5 
6 -- 2. Grant SELECT (read) privileges on the specific database or tables
7 --    Replace 'database_name'. Use `.*` for all tables or specify table names.
8 GRANT SELECT ON `your_database_name`.* TO 'shaped_readonly'@'%';
9 
10 -- OR grant on specific tables:
11 -- GRANT SELECT ON `your_database_name`.`your_table` TO 'shaped_readonly'@'%';
12 
13 -- SingleStore might not require FLUSH PRIVILEGES like older MySQL versions,
14 -- but include if recommended by SingleStore docs for permission changes.
15 -- FLUSH PRIVILEGES;

3. Secure Credentials: Securely store the username (e.g., shaped_readonly) and password.

4. IP Allowlisting / Firewall: Crucial step! Configure the firewall rules for your SingleStore cluster (whether cloud-managed or self-hosted) to allow incoming connections from Shaped's specific IP addresses on the relevant port (default 3306). Contact the Shaped team to obtain the necessary IP addresses.

Step 2: Configure the Shaped Dataset (YAML)

Define the connection details using the MYSQL schema type, pointing to your SingleStore instance.

Create a YAML file (e.g., singlestore_dataset.yaml):

1 name: your_singlestore_dataset_name  # Choose a descriptive name
2 
3 # --- Required Fields ---
4 # Use MYSQL schema_type due to SingleStore's wire compatibility
5 schema_type: MYSQL
6 table: your_table_name  # The specific table in SingleStore to sync from
7 user: shaped_readonly  # The read-only username created in Step 1
8 password: YOUR_SECURE_PASSWORD_HERE!  # The password for the read-only user
9 host: your.singlestore.host.com  # Hostname of your SingleStore cluster endpoint
10 port: 3306  # SingleStore MySQL-compatible port (default is 3306)
11 database: your_database_name  # The database containing the table
12 
13 # The column Shaped uses to track changes for incremental syncs.
14 # MUST be a column that reliably increases over time (e.g., updated_at timestamp,
15 # created_at timestamp, auto-incrementing primary key like 'id').
16 replication_key: updated_at  # Or created_at, id, etc.
17 
18 # --- Optional Fields ---
19 # List specific columns to sync. If omitted, Shaped syncs all columns.
20 # columns: ["user_id", "item_id", "timestamp", "attribute1", "metric1"]
21 
22 # Columns uniquely identifying a row (for deduplication based on replication_key).
23 # unique_keys: ["transaction_id"]  # Example
24 
25 # --- Optional Performance Fields ---
26 # batch_size: 50000  # Default 10000
27 
28 # --- Optional Scheduling ---
29 # Schedule for periodic syncs (Cron format, e.g., "@hourly", "@daily").
30 # Defaults to "@hourly" if omitted.
31 # schedule_interval: "@hourly"
32 # description: "Operational data from SingleStore"

Key Configuration Points:

• schema_type: MYSQL: This is essential for connecting to SingleStore.
• host, port (usually 3306), user, password, database: Ensure these match your SingleStore deployment and the read-only user.
• replication_key: Critical for efficient syncing. Choose a timestamp or auto-incrementing ID suitable for tracking changes in your SingleStore table.
• columns (Optional): Specify only needed columns for efficiency.

Step 3: Create the Dataset in Shaped

Use the Shaped CLI to create the dataset using your YAML configuration file:

1 shaped create-dataset --file singlestore_dataset.yaml

Shaped will validate the configuration, attempt the connection using the MySQL protocol (ensure IP allowlisting is complete!), and begin the initial data sync. Monitor status via the Shaped Dashboard or CLI (shaped view-dataset --dataset-name your_singlestore_dataset_name).

What Happens Next? Syncing, Training, Serving from SingleStore

Once connected:

1. Initial Sync: Shaped performs a full sync of the specified table.
2. Incremental Syncs: On the schedule_interval (default: hourly), Shaped queries SingleStore (using the MySQL protocol) for rows where the replication_key is greater than the last synced value, efficiently fetching only changes.
3. Model Training: Shaped uses the synced data to train its advanced AI models.
4. API Serving: After models are trained, Shaped's APIs serve personalized results derived from your fast SingleStore data.
5. Continuous Updates: Scheduled syncs keep Shaped's models informed by the latest data committed to your SingleStore database.

Conclusion: Combine SingleStore Speed with Shaped AI Intelligence

Your SingleStore database provides a high-performance foundation for your operational data. By leveraging its MySQL wire compatibility, Shaped offers a direct and efficient way to connect this fast data source to a state-of-the-art AI personalization engine. This integration allows you to activate your SingleStore data for sophisticated recommendations and search ranking without complex ETL or impacting your database's core performance. Bridge the gap between real-time data processing and real-time AI-driven relevance with Shaped and SingleStore.

Ready to power intelligent personalization using your SingleStore data?

‍Request a demo of Shaped today to see it in action with your specific use case. Or, start exploring immediately with our free trial sandbox.