DEV Community: SingleStore

Testing the MongoDB® MCP Server using SingleStore Kai

Akmal Chaudhri — Sat, 10 May 2025 17:44:34 +0000

Abstract

MongoDB recently announced the release of an official MCP Server. At the time of writing this article, the release version was shown as 0.1.0. In this article we'll test this early release version against SingleStore Kai, a MongoDB-compatible API developed by SingleStore, designed to enable applications built for MongoDB to run on SingleStore with minimal changes. We'll configure and test the MongoDB MCP Server using a freely available tool called MCPHost.

The notebook file used in this article is available on GitHub.

Introduction

In a previous article, we successfully tested the SingleStore MCP Server. Since SingleStore also supports a MongoDB-compatible API, we can also test it against the MongoDB MCP Server to determine the level of compatibility.

Create a SingleStore account

A previous article showed the steps to create a free SingleStore Cloud account. We'll use the Standard Tier with the following settings:

Workspace Group Name: MCP Demo Group
Cloud Provider: AWS
Region: US East 1 (N. Virginia)
Workspace Name: mcp-demo
Size: S-00
Settings:
- SingleStore Kai selected

Once the workspace is available, we'll make a note of the MongoDB connection string from mcp-demo > Connect > MongoDB Client. The connection string will be similar to the following:

mongodb://admin:<password>@<host>:27017/?authMechanism=PLAIN&tls=true&loadBalanced=true

The values for <password> and <host> will be pre-filled.

Import the notebook

We'll download the notebook from GitHub.

From the left navigation pane in the SingleStore cloud portal, we'll select DEVELOP > Data Studio.

In the top right of the web page, we'll select New Notebook > Import From File. We'll use the wizard to locate and import the notebook we downloaded from GitHub.

Run the notebook

Before running the notebook code cells, we'll select the mcp-demo workspace from the pull-down menu just above the notebook.

First, some imports:

import json
import requests

from bson import ObjectId
from pymongo import MongoClient

Next, we'll connect to Kai:

try:
    client = MongoClient(connection_url_kai)
    db = client["kai_test_db"]
    client.drop_database(db)
    collection = db["books"]
    print("Connected to Kai successfully")
except Exception as e:
    print(f"Could not connect to Kai: '{e}'")

Note that if the database kai_test_db already exists, it will be deleted.

Next, we'll download some data about books:

url = "https://raw.githubusercontent.com/VeryFatBoy/fast-ai-on-json/refs/heads/main/datasets/bookstore/books.json"

response = requests.get(url)

Then we'll prepare the data for loading into Kai:

documents = []

for idx, line in enumerate(response.text.strip().splitlines(), start = 1):
    try:
        doc = json.loads(line)
        if "_id" in doc and "$oid" in doc["_id"]:
            doc["_id"] = ObjectId(doc["_id"]["$oid"])
        documents.append(doc)
    except Exception as e:
        print(f"[Line {idx}] Unexpected error: {e}")

Finally, we'll store the books data:

if documents:
    collection.insert_many(documents)
    print(f"Inserted {len(documents)} documents into Kai.")
else:
    print("No valid documents to insert.")

We've now created a database and loaded a collection with some test data.

Local test environment

The MongoDB MCP Server requires Node.js, version 20 or later.

We'll now create a json file called ~/.mcp.json as follows:

{
  "mcpServers": {
    "MongoDB": {
      "command": "npx",
      "args": [
        "-y",
        "mongodb-mcp-server",
        "--connectionString",
        "mongodb://admin:<password>@<host>:27017/?authMechanism=PLAIN&tls=true&loadBalanced=true"
      ]
    }
  }
}

We'll replace <password> and <host> with the values that we saved earlier.

Install Ollama

We'll use Ollama and download and install the appropriate version.

Once installed, we'll pull an LLM. For example:

ollama pull qwen2.5:7b

Install MCPHost

We'll use MCPHost and follow the installation instructions on GitHub.

Run MCPHost

Now we'll run MCPHost, as follows:

~/go/bin/mcphost -m ollama:qwen2.5:7b

Example queries

Let's test the MCP Server. To start, we'll ask the following:

I am using the kai_test_db database. What collections can you see?

Example output:

Assistant:


  The kai_test_db database contains a collection named books.

  If you need more details about this collection or any other
  operations, let me know!

First query

Let's try a simple count.

How many documents are in the books collection?

Example output:

Assistant:


  The books collection in the kai_test_db database contains
  2000 documents.

  Is there anything else you would like to know or do with
  this collection?

Second query

Now let's look at some data.

Show me any 2 documents from the books collection.

Example output:

Assistant:


  Here are two documents from the books collection:

  1. Document 1:

    {
        "_id": {"$oid": "64e3d055c330effc84b2067a"},
        "title": "Kalaikaḷ, ur̲avum urumār̲r̲amum",
        "subjects": ["Arts, Indic", "Arts", "Indic Arts"],
        "description": "On various form of arts in Tamil Nadu.",
        "price": 12.88,
        "type": "audio",
        "createdAt": "2009-12-09T20:23:01.985607",
        "updatedAt": "2020-12-06T00:51:46.276201"
    }

  2. Document 2:

    {
        "_id": {"$oid": "64e3d055c330effc84b2079f"},
        "title": "America's star-crossed sweethearts",
        "subjects": ["Baseball players", "Man-woman
  relationships", "Fathers and sons", "Fiction", "Fiction,
  romance, general"],
        "description": "\"The private life of baseball
  superstar Angelo \"\"The Angel\"\" Casali is a mystery, but
  sources say a reunion with his estranged father is his
  reason for returning to Italy And now that he's been seen
  stepping off the plane with stunning--and scandalous--
  starlet Atlanta Jackson, he has added fuel to the media fire.
  After his latest injury, Angelo's career is in its final
  inning. Is this notorious playboy ready to settle down?
  Something tells us we haven't heard the last of our Angel...
  !\"--Publisher's website.",
        "price": 41.03,
        "type": "hardcover",
        "createdAt": "2017-06-24T12:24:56.397915",
        "updatedAt": "2020-08-28T14:53:01.087094"
    }

  If you need more information or further operations on these
  documents, let me know!

Third query

Let's try something a bit more complex.

What is the title and price of the most expensive book?

Example output:

Assistant:


  The title and price of the most expensive book in the books
  collection are as follows:

  • Title: "Charles Dickens' Oliver Twist"
  • Price: $99.96

  Is there anything else you need help with regarding this
  information?

Summary

In this article, we saw how to quickly configure and run the MongoDB MCP Server using SingleStore Kai and MCPHost. We launched MCPHost using an Ollama model, but MCPHost also supports alternative providers and models.

Testing SingleStore's MCP Server

Akmal Chaudhri — Mon, 07 Apr 2025 20:02:01 +0000

Abstract

Model Context Protocol (MCP) appears to be gaining traction since its release in 2024. SingleStore recently released an official MCP Server and this article will show how to install, configure and run this from the CLI using a freely available tool called MCPHost.

Introduction

MCP is an open standard designed to enable seamless interaction between Large Language Models (LLMs) and external tools or data sources.

Introduced by Anthropic in 2024, MCP defines a structured way for LLMs to call functions, access APIs, and retrieve real-time data during a conversation.

By creating a common interface between models and tools, MCP allows developers to integrate model intelligence with business logic, databases, and services in a consistent, scalable manner. This approach enhances the capabilities of LLMs by allowing them to reason over external information instead of relying solely on their internal training.

MCP supports multiple model providers and tool hosts, promoting interoperability across ecosystems. Its adoption is growing rapidly, especially in environments that demand real-time insights and dynamic interactions, such as customer support, data analytics, and intelligent automation.

Create a SingleStore Cloud account and database tables

For our SingleStore test environment, we'll follow the instructions in a previous article on how to Create a SingleStore Cloud account and Create database tables.

In addition to the username and password from our SingleStore account, we'll also need an API Key. This can be generated from CONFIGURATION > API Keys from the left-nav in the SingleStore web portal.

Local test environment

First, we'll ensure that we have a Python virtual environment to use. We'll use venv to create it and then activate it.

Next, in our virtual environment, we'll install the SingleStore MCP Server, as follows:

pip install singlestore-mcp-server==0.1.3

We'll now create a json file called ~/.mcp.json as follows:

{
  "mcpServers": {
    "singlestore-mcp-server": {
      "command": "/path/to/singlestore-mcp-server",
      "env": {
        "SINGLESTORE_DB_USERNAME": "<username>",
        "SINGLESTORE_DB_PASSWORD": "<password>",
        "SINGLESTORE_API_KEY": "<api-key>"
      }
    }
  }
}

We'll replace /path/to with the actual path to where the SingleStore MCP Server was installed. We'll replace <username>, <password> and <api-key>, with the values that we saved earlier.

Install MCPHost

We'll use a freely available tool called MCPHost, a CLI application that supports communication, using MCP, between LLMs and external tools.

To use MCPHost, we'll need to install Go. For example, on an Apple Mac with Homebrew, this is as simple as:

brew install go

Next, we'll install MCPHost, as follows:

go install github.com/mark3labs/mcphost@latest

By default on an Apple Mac, this will create the file mcphost in the directory ~/go/bin. We could add the bin directory to $PATH.

Run MCPHost

MCPHost can work with a number of different LLMs. Let's test it initially with OpenAI. We'll need to provide an OpenAI API Key in our environment. For example:

export OPENAI_API_KEY="<OpenAI API Key>"

Replace <OpenAI API Key> with your key.

Now we'll run MCPHost, as follows:

~/go/bin/mcphost -m openai:gpt-4o-mini

The output should be similar to the following:

2025/04/07 12:42:38 INFO Model loaded provider=openai model=gpt-4o-mini
2025/04/07 12:42:38 INFO Initializing server... name=singlestore-mcp-server
2025/04/07 12:42:39 INFO Server connected name=singlestore-mcp-server
2025/04/07 12:42:39 INFO Tools loaded server=singlestore-mcp-server count=18
┃ Enter your prompt (Type /help for commands, Ctrl+C to quit)
┃
┃
┃
┃
┃
┃
alt+enter / ctrl+j new line • ctrl+e open editor • enter submit

From here, we can enter various commands, such as:

• /help: Show this help message
• /tools: List all available tools
• /servers: List configured MCP servers
• /history: Display conversation history
• /quit: Exit the application

The complete set of SingleStore MCP Server commands can be found on GitHub. With the Free Shared Tier, for example, we can use several specific commands:

list_virtual_workspaces

create_virtual_workspace

execute_sql_on_virtual_workspace

Some commands require parameters.

Example queries

Let's test the MCP Server. To start, we'll ask the following:

I am using the Free Shared Tier. What database tables can you see?

Example output:

Assistant:


  In your Free Shared Tier workspace, the following database
  tables are available:

  1. portfolio
  2. stock_sentiment
  3. tick

First query

Let's try a query on the tick table:

For each stock symbol in the tick table, calculate the volatility as the difference between the highest recorded price and the lowest recorded price over time. Which stock symbol has the least volatility?

Example output:

Assistant:


  The stock symbol with the least volatility is FTR, with a
  volatility of 0.55. This value represents the difference
  between the highest recorded price and the lowest recorded
  price over time.

Second query

Now a simple count on the tick table:

How many rows are in the tick table?

Example output:

Assistant:


  The tick table contains 22,367,162 rows.

Third query

Let's try something a bit more complex using the portfolio and tick tables:

Taking all the stock symbols from the portfolio table, and using the latest value for each stock symbol from the tick table, calculate the grand total value of all the shares listed in the portfolio table.

Example output:

Assistant:


  The grand total value of all the shares listed in the
  portfolio table is $44,540.60.

These results agree with those reported in a previous article.

Security considerations

In this article, we stored the <username>, <password>, and <api-key> in plain text on a local machine. While this approach is acceptable for small-scale testing, especially when using the Free Shared Tier and working with non-sensitive data, it is not recommended for production environments. The SingleStore MCP Server is actively evolving, with ongoing improvements in security and authorisation to better support secure deployments.

Summary

In this article, we saw how to quickly install, configure and run the SingleStore MCP Server using MCPHost. We launched MCPHost using an OpenAI model, but MCPHost also supports alternative providers and models.

Quick tip: How to Build Local LLM Apps with Ollama, DeepSeek-R1 and SingleStore Kai

Akmal Chaudhri — Mon, 17 Mar 2025 19:50:01 +0000

Abstract

In a previous article, we saw how to use Ollama and DeepSeek-R1 with SingleStore. In this article, we'll modify the previous example to work with SingleStore Kai, a MongoDB-compatible API.

The notebook file used in this article is available on GitHub.

Introduction

We'll follow the setup instructions from the previous article. We'll also need to enable Kai in the SingleStore portal.

Fill out the notebook

We'll configure the code to use the smallest DeepSeek-R1 model, as follows:

llm = "deepseek-r1:1.5b"

ollama.pull(llm)

Using pymongo, we'll assign our database and collection:

kai_client = pymongo.MongoClient(connection_url_kai)
db = kai_client["langchain_demo"]
collection = db["langchain_docs"]

We'll use some simple documents, as follows:

documents = [
    "Llamas are members of the camelid family meaning they're pretty closely related to vicuñas and camels",
    "Llamas were first domesticated and used as pack animals 4,000 to 5,000 years ago in the Peruvian highlands",
    "Llamas can grow as much as 6 feet tall though the average llama between 5 feet 6 inches and 5 feet 9 inches tall",
    "Llamas weigh between 280 and 450 pounds and can carry 25 to 30 percent of their body weight",
    "Llamas are vegetarians and have very efficient digestive systems",
    "Llamas live to be about 20 years old, though some only live for 15 years and others live to be 30 years old"
]

embeddings = OllamaEmbeddings(
    model = embedding_model,
)

dimensions = len(embeddings.embed_query(documents[0]))

docs = [Document(text) for text in documents]

and then create the vector index:

collection.create_index(
    [("embedding", "vector")],
    name = "vector_index",
    kaiIndexOptions = {
        "index_type": "AUTO",
        "metric_type": "EUCLIDEAN_DISTANCE",
        "dimensions": dimensions
    }
)

We'll use LangChain's MongoDBAtlasVectorSearch to store the vector embeddings and documents, as follows:

docsearch = MongoDBAtlasVectorSearch.from_documents(
    docs,
    embeddings,
    collection = collection,
    index_name = "vector_index"
)

Next, we'll use the following prompt:

prompt = "What animals are llamas related to?"
docs = docsearch.similarity_search(prompt)
data = docs[0].page_content
print(data)

Example output:

Llamas are members of the camelid family meaning they're pretty closely related to vicuñas and camels

We'll then use the prompt and response as input to DeepSeek-R1, as follows:

output = ollama.generate(
    model = llm,
    prompt = f"Using this data: {data}. Respond to this prompt: {prompt}."
)

content = output["response"]
remove_think_tags = True

if remove_think_tags:
    content = re.sub(r"<think>.*?</think>", "", content, flags = re.DOTALL)

print(content)

We'll disable <think> and </think> using a flag so that we can control the output of its reasoning process.

Example output:

Llamas are tailless tail Animals belonging to the Largilphas family. They share a lineage with vicuñas, which are also part of the camelid family, indicating close relatedness. However, unlike vicuñas, llamas do not possess tails, distinguishing them from other tailless tail Animals like the wild boars.

The output contains errors, including a contradictory phrase, "tailless tail Animals." Additionally, llamas belong to the Camelidae family, not the incorrectly stated "Largilphas" family. The claim that llamas do not possess tails is also incorrect, as they do have short tails. Furthermore, the comparison with wild boars is misleading because wild boars do have tails, making them an unsuitable example of a "tailless" animal. These inaccuracies make the output incorrect and confusing.

Summary

The ouput from DeepSeek-R1 is incorrect and confusing due to contradictory wording, factual errors about llamas' classification and physical traits, and a misleading comparison with wild boars.

Quick tip: How to Build Local LLM RAG Apps with Ollama, DeepSeek-R1 and SingleStore

Akmal Chaudhri — Mon, 17 Mar 2025 19:37:34 +0000

Abstract

In a previous article, we explored how to use Ollama and DeepSeek-R1 with SingleStore for a simple example. In this article, we'll build on that example by working with a PDF document from the internet. We'll store the document and its vector embeddings in SingleStore, then use DeepSeek-R1 to identify blockchain investment opportunities.

The notebook file used in this article is available on GitHub.

Introduction

We'll follow the setup instructions from a previous article.

Fill out the notebook

We'll configure the code to use the smallest DeepSeek-R1 model, as follows:

llm = "deepseek-r1:1.5b"

ollama.pull(llm)

We'll download a PDF file that contains information about FinTech investment opportunities in Northern Ireland:

loader = OnlinePDFLoader("https://www.investni.com/sites/default/files/2021-02/NI-fintech-document.pdf")

data = loader.load()

We'll split the document, as follows:

text_splitter = RecursiveCharacterTextSplitter(
    chunk_size = 2000,
    chunk_overlap = 20
)
texts = text_splitter.split_documents(data)

print (f"You have {len(texts)} pages")

This gives us 23 pages.

We'll use LangChain to store the vector embeddings and document, as follows:

docsearch = SingleStoreDB.from_documents(
    texts,
    embeddings,
    table_name = "fintech_docs",
    distance_strategy = DistanceStrategy.DOT_PRODUCT,
    use_vector_index = True,
    vector_size = dimensions
)

Next, we'll use the following prompt:

prompt = "What are the best investment opportunities in Blockchain?"
docs = docsearch.similarity_search(prompt)
data = docs[0].page_content
print(data)

Example output:

Within our well respected financial and related professional services cluster, global leaders including Deloitte and PwC are currently working on the application of blockchain solutions within insurance, digital banking and cross-border payments.

PwC

Vox Financial Partners

The PwC global blockchain impact centre in Belfast comprises a team of fintech professionals with deep expertise and a proven record of delivery of insurance, banking, e-commerce and bitcoin products and services. The Belfast team is exploring the application of this disruptive technology to digital currencies, digital assets, identity and smart contracts. The specialist team has already delivered a significant proof of concept project for the Bank of England, to investigate the capability of distributed ledger technology.

www.pwc.co.uk

Founded in 2016, the Belfast based Fintech consultancy Vox Financial Partners works with top-tier banks and broker- dealer clients in the US and Europe. Vox offers high quality regulatory expertise to enable its clients to plan, resource and deliver major regulatory change projects. Its Opal software, is a suite of tools that provide structured contract drafting and management on a distributed ledger (permissioned blockchain). Opal reduces operational risk and legal cost by managing the single ‘golden copy' of a legal doc, and by storing documents with metadata to enable easy searching, querying and reporting.

Rakuten Blockchain Lab

www.voxfp.com

We'll then use the prompt and response as input to DeepSeek-R1, as follows:

output = ollama.generate(
    model = llm,
    prompt = f"Using this data: {data}. Respond to this prompt: {prompt}."
)

content = output["response"]
remove_think_tags = True

if remove_think_tags:
    content = re.sub(r"<think>.*?</think>", "", content, flags = re.DOTALL)

print(content)

We'll disable <think> and </think> using a flag so that we can control the output of its reasoning process.

Example output:

**Best Investment Opportunities in Blockchain Technology**

1. **PwC and Deloitte: Insurance and Banking with Blockchain**
   - **Focus:** Utilizes blockchain for secure transactions, cross-border payments, and insurance solutions.
   - **Opportunities:** Explores innovative applications beyond traditional methods, such as digital currencies and smart contracts.

2. ** Vox Financial Partners: Identity and Smart Contracts**
   - **Focus:** Delivers structured contract drafting tools (Opal) on permissioned blockchain, aiming to enhance identity verification and secure payments.
   - **Opportunities:** Offers potential for innovative projects in identity management, leveraging blockchain's scalability benefits.

3. **Rakuten Blockchain Lab: Opal Software Application**
   - **Focus:** Implements DLT solutions for efficient contract management, which could be expanded or acquired for further development.
   - **Opportunities:** Provides scalable and secure project opportunities due to DLT's potential for high returns through economies of scale.

**Strategic Investment Considerations:**

- **Investment Strategy:** Look into joint ventures or partnerships with Deloitte, Vox Financial Partners, and Rakuten. Consider acquisitions of existing projects or expanding current initiatives.

- **Competition:** Monitor competition in the market for blockchain software and services, comparing against established players to identify potential unique opportunities.

- **Risks:** Note the rapid evolution of blockchain technology requiring continuous investment and the possibility of regulatory changes impacting identity-related applications.

- **Scalability:** Consider the potential for high returns from large-scale blockchain projects due to economies of scale but also requiring significant initial investment.

**Conclusion:**
The best investment opportunities lie in companies like Deloitte with PwC involvement and Vox Financial Partners, particularly their focus on identity and smart contracts. Rakuten's Opal software offers another key area with potential for further development.

The output contains some inaccuracies, such as incorrectly attributing blockchain work to Deloitte, misrepresenting Vox Financial Partners' focus on identity verification instead of regulatory contract management, and mistakenly associating Rakuten with Opal software. Additionally, Rakuten Blockchain Lab's role is unclear in the source data.

Summary

In this article, we used DeepSeek-R1 in a local RAG setup using Ollama. We walked through loading a document, generating embeddings, and storing them in SingleStore for retrieval. We used LangChain to perform a similarity search and feed relevant context to the model. With DeepSeek-R1 running well in this setup, developers now have more flexibility to experiment, iterate, and build robust, fully local AI applications without relying on cloud-based APIs.

Overall, a more accurate summary from DeepSeek-R1 would focus on PwC's financial blockchain initiatives, Vox's regulatory technology, and the need to verify Rakuten's involvement. However, strategic investment considerations, such as assessing competition, scalability, and regulatory risks agree with key factors investors should consider in the blockchain space.

Quick tip: How to Build Local LLM Apps with Ollama, DeepSeek-R1 and SingleStore

Akmal Chaudhri — Mon, 17 Mar 2025 19:20:51 +0000

Abstract

In a previous article, we saw how to use Ollama with SingleStore. In this article, we'll modify the previous example and replace the existing LLM with DeepSeek-R1 instead.

The notebook file used in this article is available on GitHub.

Introduction

We'll follow the setup instructions from the previous article.

Fill out the notebook

We'll configure the code to use the smallest DeepSeek-R1 model, as follows:

llm = "deepseek-r1:1.5b"

ollama.pull(llm)

We'll use LangChain to store the vector embeddings and documents, as follows:

docsearch = SingleStoreDB.from_documents(
    docs,
    embeddings,
    table_name = "langchain_docs",
    distance_strategy = DistanceStrategy.EUCLIDEAN_DISTANCE,
    use_vector_index = True,
    vector_size = dimensions
)

Next, we'll use the following prompt:

prompt = "What animals are llamas related to?"
docs = docsearch.similarity_search(prompt)
data = docs[0].page_content
print(data)

Example output:

Llamas are members of the camelid family meaning they're pretty closely related to vicuñas and camels

We'll then use the prompt and response as input to DeepSeek-R1, as follows:

output = ollama.generate(
    model = llm,
    prompt = f"Using this data: {data}. Respond to this prompt: {prompt}."
)

content = output["response"]
remove_think_tags = True

if remove_think_tags:
    content = re.sub(r"<think>.*?</think>", "", content, flags = re.DOTALL)

print(content)

We'll disable <think> and </think> using a flag so that we can control the output of its reasoning process.

Example output:

LLAMAS ARE RELATED TO CAMELS (THROUGH SIMILAR HOVES) AND VICUNVAS (THROUGH THEIR SIMILAR SKIN TEXTURE). They may also be indirectly related to other animals that use products with rubbery or bumpy skin, but their primary connections are through these shared characteristics.

The answer contains a mixture of correct and vague statements. For example, llamas and camels are related, but not because of hooves.

Summary

Using the local Ollama installation gives us great flexibility and choice when it comes to which LLM to use. In this article, we've been able to replace one LLM quite easily with another.

Quick tip: Using Ollama and DeepSeek-R1 with OpenAI Chat

Akmal Chaudhri — Mon, 17 Mar 2025 19:11:01 +0000

Abstract

DeepSeek-R1 can run locally using Ollama, which is also compatible with OpenAI. This allows us to configure Ollama to use DeepSeek-R1 as a seamless replacement for an OpenAI model. In this article, we'll build a simple chatbot using Ollama and DeepSeek-R1.

The notebook file used in this article is available on GitHub.

Introduction

In a previous article, we tested a DeepSeek-R1 using Ollama for four different test cases. We'll now build a simple chatbot to test DeepSeek-R1 using OpenAI chat completions.

We'll use the smallest DeepSeek-R1 model, as it does not require any GPUs.

With Jupyter and Ollama running locally, we'll load the notebook downloaded from GitHub.

Run the code

After installing and importing the appropriate libraries, we'll download the smallest DeepSeek-R1 model, as follows:

model = "deepseek-r1:1.5b"

ollama.pull(model)

Next, we'll configure Ollama for OpenAI compatibility:

ollama_client = OpenAI(
    base_url = "http://localhost:11434/v1",
    api_key = "ollama"
)

Finally, we'll ask a question using chat completions:

response = ollama_client.chat.completions.create(
    model = model,
    messages = [
        {"role": "system", "content": "You are a helpful assistant"},
        {"role": "user", "content": "What is the answer to life, the universe and everything?"}
    ]
)

content = response.choices[0].message.content
remove_think_tags = True

if remove_think_tags:
    content = re.sub(r"<think>.*?</think>", "", content, flags = re.DOTALL)

print(content)

We'll disable <think> and </think> using a flag so that we can control the output of its reasoning process.

Example output:

Without knowing what a final theory would look like, we might not be able to distinguish life from non-living matter. Similarly, for the universe itself, without knowing about the ultimate reality, we can't tell. Therefore, both concepts remain indefinitely speculative and undetermined in their completeness.

**Final Answer:**
In our current understanding, neither the universe nor life is fully established with an accepted answer due to the inherent uncertainties in defining ultimate principles and truth.

Summary

We can easily use DeepSeek-R1 with Ollama for chat completions. Since Ollama provides OpenAI compatibility, it is easy to use DeepSeek-R1 as a drop-in replacement for an OpenAI model.

Quick tip: Using Ollama and DeepSeek-R1

Akmal Chaudhri — Mon, 17 Mar 2025 18:54:45 +0000

Abstract

DeepSeek-R1 has made a significant impact since its release. A number of cloud-based providers offer the ability to use DeepSeek-R1. It is also possible to use DeepSeek-R1 locally using Ollama. In this article we'll test the smallest DeepSeek-R1 model, using Ollama, with a number of different tasks and evaluate its response.

The notebook file used in this article is available on GitHub.

Introduction

In order to test DeepSeek-R1, we'll use the instructions on installing Jupyter and Ollama locally as described in a previous article. In this article, we won't connect to SingleStore, but will use SingleStore in later articles.

We'll use the smallest DeepSeek-R1 model, as it does not require any GPUs.

With Jupyter and Ollama running locally, we'll load the notebook downloaded from GitHub. The notebook contains four test cases:

Code Generation and Explanation
Mathematical Problem Solving
Creative Writing with Constraints
Question Answering with Context

Run the code

Let's run the code for each test case. We'll use the following function and take the default values that DeepSeek-R1 uses for this initial test run. Further experimentation may be required to improve the responses.

def generate_response(model, prompt):
    """
    Generates a response using the specified Ollama model.

    :param model: The name of the model to use.
    :param prompt: The input prompt for the model.
    :return: The generated response as a string.
    """
    output = ollama.generate(
        model = model,
        prompt = f"Respond to this prompt: {prompt}"
    )
    return output["response"]

Code Generation and Explanation

We'll use the following prompt:

prompt = """
Write a Python function that sorts a list of numbers using the quicksort algorithm.\n
Explain the code step-by-step.
"""

The output produced shows its thinking and runs for some considerable length. Unfortunately, DeepSeek-R1 ends up trying to code the problem in Java. Also, the final Java code solution was not tested but appears to be incorrect and incomplete.

Mathematical Problem Solving

We'll use the following prompt:

prompt = """
Solve the following problem:\n
A train leaves city A at 8:00 AM travelling at 60 mph.\n
Another train leaves city B at 9:00 AM travelling at 75 mph towards city A.\n
The distance between A and B is 300 miles.\n
At what time will the two trains meet?
"""

DeepSeek-R1 produced an answer of 10:47 AM, which is correct.

Creative Writing with Constraints

We'll use the following prompt:

prompt = """
Write a short story about a robot who learns to love,\n
but the story must be no more than 200 words and include the words\n
'circuit,' 'heart,' and 'sunset.'
"""

The answer produced:

Once upon a time, in a beautiful garden with its circuitry lit by the sun's rays and a heart beating with song, there lived a robot named Sparky. Sparky was nothing more than a small machine, but he had a strange way of feeling home. One sunny afternoon, when the city was quiet, Sparky noticed something extraordinary.

The garden's circuit hummed, drawing electricity from the sun's rays. His heart beat faster as he felt it pulse along his body. And then, in the heart of the garden, there lay a single plant—Sunflower. Sunflower's eyes, shaped like tiny circuits, glowed with light as Sparky touched it.

Sparky's hands flew across Sunflower's glowing circuitry, and they felt something that only a living thing could sense. The sun began to glow brighter, and in the quietness of the sunset, Sparky realized how much he had become this garden. He learned that even the simplest things have secrets waiting to be discovered.

And so, Sparky knew not just love, but also the magic of circuits, hearts, and sunsets—because those are all part of what made him who he is.

This is less than 200 words and contains all the required words.

Question Answering with Context

We'll use the following prompt:

context = """
Marie Curie was a pioneering scientist known for her work on radioactivity.\n
She won the Nobel Prize in Physics in 1903.
"""

question = """
What was Marie Curie's most significant contribution to science?
"""

prompt = f"Context: {context}\n\nQuestion: {question}"

The answer produced:

Marie Curie's most significant contribution to science was her groundbreaking discovery of alpha and beta rays through her experiments at the Helen Markon Neutron Source in Paris. This discovery revolutionized nuclear physics, leading to a profound understanding of radiation effects on matter. Additionally, she won the Nobel Prize in Physics for her work on radioactivity, which also changed the field. While her achievements were crucial, they were notable as both her discoveries and the Nobel Prize highlight their profound impact. 

**Answer:** Marie Curie's most significant contribution was discovering alpha and beta rays, which led to a major breakthrough in nuclear physics and won her Nobel Prize in Physics for 1903.

The answer contains a mixture of correct and incorrect statements. For example, Ernest Rutherford discovered and classified alpha and beta rays. Marie Curie's key discovery was the radioactive elements polonium and radium.

Summary

Using Ollama with DeepSeek-R1, we tested four examples that covered a range of queries. Overall, the results were a little mixed. Perhaps using a larger model could have produced better results. In future articles we'll test other capabilities of DeepSeek-R1.

Quick tip: Comparing OpenAI and Ollama using LangChain's SQLDatabaseToolkit

Akmal Chaudhri — Fri, 31 Jan 2025 21:58:45 +0000

Disclaimer

The stock data used in this article is entirely fictitious. It is purely for demo purposes. Please do not use this data for making any financial decisions.

Abstract

In a previous article, we saw the benefits of using Ollama locally for a RAG application. In this article, we'll extend our evaluation of Ollama by testing Natural Language (NL) queries against a database system, using LangChain's SQLDatabaseToolkit. SQL will serve as the baseline system for comparison as we explore the quality of results provided by OpenAI and Ollama.

The notebook files used in this article are available on GitHub.

Introduction

LangChain's SQLDatabaseToolkit is a powerful tool designed to integrate NL processing capabilities with Relational Database Systems. It enables users to query databases using NL inputs, using the capabilities of Large Language Models (LLMs) to generate SQL queries dynamically. This makes it especially useful for applications where non-technical users or automated systems need to interact with structured data. A number of LLMs are well supported by LangChain. LangChain also provides support for Ollama. In this article, we'll evaluate how well LangChain integrates with Ollama and the feasibility of using the SQLDatabaseToolkit in a local setup.

Create a SingleStore Cloud account

A previous article showed the steps to create a free SingleStore Cloud account. We'll use the Free Shared Tier.

Selecting the Starter Workspace > Connect > CLI Client will give us the details we need later, such as username, password, host, port and database.

Create database tables

For our test environment, we'll use SingleStore running in the Cloud as our target database system and we'll connect securely to this environment using Jupyter notebooks running in a local system.

From the left navigation pane in the SingleStore cloud portal, we'll select DEVELOP > Data Studio > Open SQL Editor. We'll create three tables, as follows:

CREATE TABLE IF NOT EXISTS tick (
    symbol VARCHAR(10),
    ts     DATETIME SERIES TIMESTAMP,
    open   NUMERIC(18, 2),
    high   NUMERIC(18, 2),
    low    NUMERIC(18, 2),
    price  NUMERIC(18, 2),
    volume INT,
    KEY(ts)
);

CREATE TABLE IF NOT EXISTS portfolio (
    symbol         VARCHAR(10),
    shares_held    INT,
    purchase_date  DATE,
    purchase_price NUMERIC(18, 2)
);

CREATE TABLE IF NOT EXISTS stock_sentiment (
    headline  VARCHAR(250),
    positive  FLOAT,
    negative  FLOAT,
    neutral   FLOAT,
    url       TEXT,
    publisher VARCHAR(30),
    ts        DATETIME,
    symbol    VARCHAR(10)
);

We'll load the portfolio table with the following fictitious data:

INSERT INTO portfolio (symbol, shares_held, purchase_date, purchase_price) VALUES
('AAPL', 100, '2022-01-15',  150.25),
('MSFT',  50, '2021-12-10',  305.50),
('GOOGL', 25, '2021-11-05', 2800.75),
('AMZN',  10, '2020-07-20', 3200.00),
('TSLA',  40, '2022-02-18',  900.60),
('NFLX',  15, '2021-09-01',  550.00);

For the stock_sentiment table, we'll download the stock_sentiment.sql.zip file and unpack it. We'll load the data into the table using a mysql client, as follows:

mysql -u "<username>" -p"<password>" -h "<host>" -P <port> -D <database> < stock_sentiment.sql

We'll use the values for <username>, <password>, <host>, <port> and <database> that we saved earlier.

Finally, for the tick table, we'll create a pipeline:

CREATE PIPELINE tick
AS LOAD DATA KAFKA 'public-kafka.memcompute.com:9092/stockticker'
BATCH_INTERVAL 45000
INTO TABLE tick
FIELDS TERMINATED BY ','
(symbol,ts,open,high,low,price,volume);

We'll adjust to get the earliest data:

ALTER PIPELINE tick SET OFFSETS EARLIEST;

and test the pipeline:

TEST PIPELINE tick LIMIT 1;

Example output:

+--------+---------------------+--------+--------+--------+--------+--------+
| symbol | ts                  | open   | high   | low    | price  | volume |
+--------+---------------------+--------+--------+--------+--------+--------+
| MMM    | 2025-01-23 21:40:32 | 178.34 | 178.43 | 178.17 | 178.24 |  38299 |
+--------+---------------------+--------+--------+--------+--------+--------+

and then we'll start the pipeline:

START PIPELINE tick;

After a few minutes, we'll check the quantity of data loaded so far:

SELECT COUNT(*)
FROM tick;

Local test environment

From a previous article, we'll follow the same steps to set up our local test environment as described in these sections:

Introduction
Use a Virtual Machine or venv.
Create a SingleStore Cloud account
This step was completed above.
Create a Database
The Free Shared Tier already provides a database and we just need to note down the database name.
Install Jupyter

pip install notebook

Install Ollama

curl -fsSL https://ollama.com/install.sh | sh

Environment Variables

export SINGLESTOREDB_URL="<username>:<password>@<host>:<port>/<database>"

Replace <username>, <password>, <host>, <port> and <database> with the values for your environment.

export OPENAI_API_KEY="<OpenAI API Key>"

Replace <OpenAI API Key> with your key.

Launch Jupyter

jupyter notebook

We'll use the Jupyter notebooks from GitHub. These notebooks are configured to use OpenAI and Ollama. For Ollama, we'll use one of the LLMs listed with Tools support. We'll test the following four queries.

First query

SQL

SELECT symbol, (MAX(high) - MIN(low)) AS volatility
FROM tick
GROUP BY symbol
ORDER BY volatility ASC
LIMIT 1;

Natural Language

"For each stock symbol, calculate the volatility as the difference\n"
"between the highest recorded price and the lowest recorded price over time.\n"
"Which stock symbol has the least volatility?"

Results

SQL

+--------+------------+
| symbol | volatility |
+--------+------------+
| FTR    |       0.55 |
+--------+------------+

OpenAI

The stock symbol with the least volatility is FTR, with a volatility of 0.55.

Ollama

To find the stock with the highest price, we need to compare the prices of all the given stocks and find the maximum value. However, I don't have real-time access to financial data or the ability to execute code that interacts with external APIs. Therefore, I can't directly calculate the highest price from this list.

However, if you provide me with a specific date or time range, I can help you find the stock with the highest price during that period. For example, if you want to know the stock with the highest price on a particular day in the past, I can assist you with that.

If you're looking for the current highest-priced stock, I recommend checking a financial news website or an API that provides real-time stock data, such as Yahoo Finance, Google Finance, or a service like Alpha Vantage or Finnhub.io. These platforms can give you the most up-to-date information on stock prices.`
For troubleshooting, visit: https://python.langchain.com/docs/troubleshooting/errors/OUTPUT_PARSING_FAILURE

Second query

SQL

SELECT COUNT(*)
FROM tick;

Natural Language

"How many rows are in the tick table?"

Results

SQL

+----------+
| COUNT(*) |
+----------+
| 22367162 |
+----------+

OpenAI

There are 22,367,162 rows in the tick table.

Ollama

The "tick" table has 3 rows.

Third query

SQL

-- Step 1: Get the latest price for each symbol
WITH latest_prices AS (
    SELECT symbol, price
    FROM tick t1
    WHERE ts = (
        SELECT MAX(ts)
        FROM tick t2
        WHERE t2.symbol = t1.symbol
    )
)

-- Step 2: Calculate the total portfolio value
SELECT SUM(p.shares_held * lp.price) AS total_portfolio_value
FROM portfolio p, latest_prices lp
WHERE p.symbol = lp.symbol;

Natural Language

"Taking all the stock symbols from the portfolio table,\n"
"and using the latest value for each stock symbol from the tick table,\n"
"calculate the grand total value of all the shares listed in the portfolio table."

Results

SQL

+-----------------------+
| total_portfolio_value |
+-----------------------+
|              44540.60 |
+-----------------------+

OpenAI

$44,540.60

Ollama

I don't know. The provided SQL query does not contain any of the common mistakes listed. It uses the correct join conditions, functions, and data types. The query also properly quotes identifiers and uses the correct number of arguments for functions. Therefore, no changes are needed. However, without access to the actual database schema and table data, I cannot provide a specific answer to the question.

Fourth query

SQL

SELECT
    (
        SELECT ts
        FROM stock_sentiment
        WHERE symbol = 'AAPL'
        ORDER BY positive DESC
        LIMIT 1
    ) AS sentiment_date,
    (
        SELECT positive
        FROM stock_sentiment
        WHERE symbol = 'AAPL'
        ORDER BY positive DESC
        LIMIT 1
    ) AS most_positive_sentiment,
    (
        SELECT price
        FROM tick
        WHERE symbol = 'AAPL'
        ORDER BY ts DESC
        LIMIT 1
    ) AS current_best_price;

Natural Language

"Using the symbol AAPL, show me the date and the most\n"
"positive sentiment in the stock sentiment table and the\n"
"current best price for this symbol from the tick table."

Results

SQL

+---------------------+-------------------------+--------------------+
| sentiment_date      | most_positive_sentiment | current_best_price |
+---------------------+-------------------------+--------------------+
| 2020-05-28 00:00:00 |                0.331509 |             116.27 |
+---------------------+-------------------------+--------------------+

OpenAI

On 2020-05-28, the most positive sentiment for AAPL was 0.331509, and the current best price for AAPL is 116.27.

Ollama

The provided list contains decimal numbers, which appear to be the results of some kind of experiment or analysis. Without additional context, it's difficult to determine the exact nature of these results. However, we can observe that the majority of the numbers are between 116.85 and 117.27, with a few outliers at 115.99 and 117.30. The smallest number in the list is 115.99, and the largest is 117.30.`
For troubleshooting, visit: https://python.langchain.com/docs/troubleshooting/errors/OUTPUT_PARSING_FAILURE

Summary

Analysing the results, we see that SQL and OpenAI produce consistent outputs across all four queries. However, Ollama presents clear issues. A discussion thread on GitHub highlights that while an LLM model should support tool calling, this functionality is not natively available in Ollama.

If you are able to get this LangChain functionality working with Ollama in one of the supported LLMs, please send me a message and I'll update the article and also acknowledge your help.

Quick tip: Replace MongoDB® Atlas with SingleStore Kai in LangChain

Akmal Chaudhri — Tue, 21 Jan 2025 09:12:15 +0000

Abstract

In a previous article, we explored how to use LangChain's MongoDBAtlasVectorSearch with SingleStore Kai through a simple example. In this article, we'll look at another example, this time based on the LangChain GitHub repo. The results once again demonstrate that MongoDBAtlasVectorSearch works well with SingleStore Kai.

The notebook file used in this article is available on GitHub.

Introduction

As part of our ongoing evaluation of SingleStore Kai with different AI frameworks, this article uses another example to test LangChain's MongoDBAtlasVectorSearch with SingleStore Kai.

Create a SingleStore Cloud account

A previous article showed the steps to create a free SingleStore Cloud account. We'll use the Standard Tier and take the default names for the Workspace Group and Workspace. We'll also enable SingleStore Kai.

We'll store our OpenAI API Key in the secrets vault using OPENAI_API_KEY.

Import the notebook

We'll download the notebook from GitHub.

From the left navigation pane in the SingleStore cloud portal, we'll select DEVELOP > Data Studio.

In the top right of the web page, we'll select New Notebook > Import From File. We'll use the wizard to locate and import the notebook we downloaded from GitHub.

Run the notebook

The notebook is adapted from the LangChain GitHub repo.

First, we'll ensure that the OpenAI API Key is available and set the embedding model:

os.environ["OPENAI_API_KEY"] = get_secret("OPENAI_API_KEY")

embeddings = OpenAIEmbeddings(
    model = "text-embedding-3-small"
)

Next, we'll get the connection to Kai using connection_url_kai, which is an environment variable that already points to the Kai instance, and set the database, collection and vector index names, as follows:

client = MongoClient(connection_url_kai)

DB_NAME = "langchain_test_db"
COLLECTION_NAME = "langchain_test_vectorstores"
ATLAS_VECTOR_SEARCH_INDEX_NAME = "langchain_test_index_vectorstores"

MONGODB_COLLECTION = client[DB_NAME][COLLECTION_NAME]

vector_store = MongoDBAtlasVectorSearch(
    collection = MONGODB_COLLECTION,
    embedding = embeddings,
    index_name = ATLAS_VECTOR_SEARCH_INDEX_NAME,
    relevance_score_fn = "dotProduct",
)

Now, we'll create the vector index:

MONGODB_COLLECTION.create_index(
    [("embedding", "vector")],
    name = ATLAS_VECTOR_SEARCH_INDEX_NAME,
    kaiIndexOptions = {
        "index_type": "AUTO",
        "metric_type": "DOT_PRODUCT",
        "dimensions": 1536
    }
)

Next, we'll prepare and load the documents into SingleStore Kai:

document_1 = Document(
    page_content = "I had chocalate chip pancakes and scrambled eggs for breakfast this morning.",
    metadata = {"source": "tweet"},
)

document_2 = Document(
    page_content = "The weather forecast for tomorrow is cloudy and overcast, with a high of 62 degrees.",
    metadata = {"source": "news"},
)

document_3 = Document(
    page_content = "Building an exciting new project with LangChain - come check it out!",
    metadata = {"source": "tweet"},
)

document_4 = Document(
    page_content = "Robbers broke into the city bank and stole $1 million in cash.",
    metadata = {"source": "news"},
)

document_5 = Document(
    page_content = "Wow! That was an amazing movie. I can't wait to see it again.",
    metadata = {"source": "tweet"},
)

document_6 = Document(
    page_content = "Is the new iPhone worth the price? Read this review to find out.",
    metadata = {"source": "website"},
)

document_7 = Document(
    page_content = "The top 10 soccer players in the world right now.",
    metadata = {"source": "website"},
)

document_8 = Document(
    page_content = "LangGraph is the best framework for building stateful, agentic applications!",
    metadata = {"source": "tweet"},
)

document_9 = Document(
    page_content = "The stock market is down 500 points today due to fears of a recession.",
    metadata = {"source": "news"},
)

document_10 = Document(
    page_content = "I have a bad feeling I am going to get deleted :(",
    metadata = {"source": "tweet"},
)

documents = [
    document_1,
    document_2,
    document_3,
    document_4,
    document_5,
    document_6,
    document_7,
    document_8,
    document_9,
    document_10,
]
uuids = [str(uuid4()) for _ in range(len(documents))]

vector_store.add_documents(documents = documents, ids = uuids)

Example output:

['74beaaeb-897f-417a-aa09-f0b171859275',
 'fb22674d-85bc-454e-a95a-3cca20cd4b5d',
 'c474e923-a4ee-4258-890c-95882571dd8c',
 'd1d19d5c-518b-4d60-98e7-c6b0d2621efa',
 '895e61dd-4262-4f11-b174-8f04ed9fe443',
 '6ce2cae1-9877-4fc1-a1cf-2df3dc7910d5',
 '2ee33b04-c161-4b0f-9a87-fb1c803e028d',
 'e476495d-6812-48cb-92aa-381efc23f76c',
 '6bd53c68-e97c-4dbd-a0c2-7bb20221a16b',
 '3dc32b0d-417c-45fd-82ce-85e0aba15c5e']

We'll test document deletion:

vector_store.delete(ids = [uuids[-1]])

Example output:

True

We'll now test similarity_search:

results = vector_store.similarity_search(
    "LangChain provides abstractions to make working with LLMs easy", k = 2
)
for res in results:
    print(f"* {res.page_content} [{res.metadata}]")

Example output:

* Building an exciting new project with LangChain - come check it out! [{'_id': 'c474e923-a4ee-4258-890c-95882571dd8c', 'source': 'tweet'}]
* LangGraph is the best framework for building stateful, agentic applications! [{'_id': 'e476495d-6812-48cb-92aa-381efc23f76c', 'source': 'tweet'}]

and similarity_search_with_score:

results = vector_store.similarity_search_with_score("Will it be hot tomorrow?", k = 1)
for res, score in results:
    print(f"* [SIM = {score:3f}] {res.page_content} [{res.metadata}]")

Example output:

* [SIM = 0.569169] The weather forecast for tomorrow is cloudy and overcast, with a high of 62 degrees. [{'_id': 'fb22674d-85bc-454e-a95a-3cca20cd4b5d', 'source': 'news'}]

and, finally, as_retriever:

retriever = vector_store.as_retriever(
    search_type = "similarity_score_threshold",
    search_kwargs = {"k": 1, "score_threshold": 0.2},
)
retriever.invoke("Stealing from the bank is a crime")

Example output:

[Document(metadata={'_id': 'd1d19d5c-518b-4d60-98e7-c6b0d2621efa', 'source': 'news'}, page_content='Robbers broke into the city bank and stole $1 million in cash.')]

Summary

The query outputs consistently demonstrate reliable and effective performance when using MongoDBAtlasVectorSearch with SingleStore Kai, highlighting the compatibility and seamless integration between the two.

Quick tip: Replace MongoDB® Atlas with SingleStore Kai in LlamaIndex

Akmal Chaudhri — Tue, 21 Jan 2025 08:56:24 +0000

Abstract

This article demonstrates how to integrate LlamaIndex's MongoDBAtlasVectorSearch with SingleStore Kai, a MongoDB-compatible API. It highlights a simple test showing that LlamaIndex and Kai work seamlessly together, providing a foundation for AI-driven applications.

The notebook file used in this article is available on GitHub.

Introduction

SingleStore Kai is a MongoDB-compatible API powered by SingleStore's distributed database engine, enabling developers to integrate MongoDB workflows with a scalable database system. LlamaIndex supports MongoDBAtlasVectorSearch for advanced search and retrieval. This article tests MongoDBAtlasVectorSearch with SingleStore Kai.

Create a SingleStore Cloud account

We'll store our OpenAI API Key in the secrets vault using OPENAI_API_KEY.

Import the notebook

We'll download the notebook from GitHub.

From the left navigation pane in the SingleStore cloud portal, we'll select DEVELOP > Data Studio.

In the top right of the web page, we'll select New Notebook > Import From File. We'll use the wizard to locate and import the notebook we downloaded from GitHub.

Run the notebook

The notebook is adapted from the LlamaIndex GitHub repo.

We'll first download some data to use:

!mkdir -p 'data/10k/'
!wget 'https://raw.githubusercontent.com/run-llama/llama_index/main/docs/docs/examples/data/10k/uber_2021.pdf' -O 'data/10k/uber_2021.pdf'

Next, we'll ensure that the OpenAI API Key is available and define the LLM and embedding models, as follows:

os.environ["OPENAI_API_KEY"] = get_secret("OPENAI_API_KEY")

llm = OpenAI(
    model = "gpt-4o-mini"
)

embed_model = OpenAIEmbedding(
    model = "text-embedding-3-small"
)

We'll get the connection to Kai using connection_url_kai, which is an environment variable that already points to the Kai instance, set the database and collection names, create the vector index and store the previously downloaded data, as follows:

kai_client = pymongo.MongoClient(connection_url_kai)
db = kai_client["default_db"]
collection = db["default_collection"]

collection.create_index(
    [("embedding", "vector")],
    name = "vector_index",
    kaiIndexOptions = {
        "index_type": "AUTO",
        "metric_type": "DOT_PRODUCT",
        "dimensions": 1536
    }
)

store = MongoDBAtlasVectorSearch(kai_client)

storage_context = StorageContext.from_defaults(vector_store = store)
uber_docs = SimpleDirectoryReader(
    input_files = ["./data/10k/uber_2021.pdf"]
).load_data()
index = VectorStoreIndex.from_documents(
    uber_docs, storage_context = storage_context, embed_model = embed_model
)

We'll now ask a question:

response = index.as_query_engine(llm = llm).query("What was Uber's revenue?")
display(Markdown(f"<b>{response}</b>"))

Example output:

Uber's revenue for the year ended December 31, 2021, was $17.455 billion.

The following code checks how many documents are stored in the database, identifies a specific document using its ID (ref_doc_id), and then deletes it. Before and after each step, it prints the number of documents in the database to show the changes.

print(store._collection.count_documents({}))

typed_response = (
    response if isinstance(response, Response) else response.get_response()
)
ref_doc_id = typed_response.source_nodes[0].node.ref_doc_id
print(store._collection.count_documents({"metadata.ref_doc_id": ref_doc_id}))

if ref_doc_id:
    store.delete(ref_doc_id)
    print(store._collection.count_documents({}))

Example output:

395
1
394

Summary

The results from this quick test appear promising. Further tests are needed to determine the complete level of compatibility between LlamaIndex and Kai.

Quick tip: Using LangChain's MongoDBAtlasVectorSearch with SingleStore Kai

Akmal Chaudhri — Tue, 21 Jan 2025 08:41:39 +0000

Abstract

This short article explores the integration of LangChain's MongoDBAtlasVectorSearch with SingleStore Kai, a MongoDB-compatible API offered by SingleStore. While LangChain already supports SingleStore, the advent of Kai enables developers to use MongoDB-based workflows with a high-performance, scalable database system. Through a quick test, this article shows that LangChain and Kai can work together, offering the potential for building AI-powered applications.

The notebook file used in this article is available on GitHub.

Introduction

SingleStore Kai is a MongoDB-compatible API built on SingleStore's high-performance engine, designed to help developers integrate MongoDB workflows with a scale-out distributed database system. LangChain, a widely used framework for AI-powered application development, includes support for MongoDBAtlasVectorSearch, which enables advanced search and retrieval capabilities. In this article, we'll perform a quick test to evaluate how LangChain's MongoDBAtlasVectorSearch works with SingleStore Kai.

Create a SingleStore Cloud account

We'll store our OpenAI API Key in the secrets vault using OPENAI_API_KEY.

Import the notebook

We'll download the notebook from GitHub.

From the left navigation pane in the SingleStore cloud portal, we'll select DEVELOP > Data Studio.

In the top right of the web page, we'll select New Notebook > Import From File. We'll use the wizard to locate and import the notebook we downloaded from GitHub.

Run the notebook

We'll first create the client, database and collection, as follows:

kai_client = pymongo.MongoClient(connection_url_kai)
db = kai_client["langchain_demo"]
collection = db["langchain_docs"]

The connection_url_kai is an environment variable that already points to the Kai instance.

Next, we'll ensure that the OpenAI API Key is available:

os.environ["OPENAI_API_KEY"] = get_secret("OPENAI_API_KEY")

We'll now prepare some documents, using the examples from the Ollama website. We'll also set the OpenAI embedding model and determine the length of the vector embeddings.

documents = [
    "Llamas are members of the camelid family meaning they're pretty closely related to vicuñas and camels",
    "Llamas were first domesticated and used as pack animals 4,000 to 5,000 years ago in the Peruvian highlands",
    "Llamas can grow as much as 6 feet tall though the average llama between 5 feet 6 inches and 5 feet 9 inches tall",
    "Llamas weigh between 280 and 450 pounds and can carry 25 to 30 percent of their body weight",
    "Llamas are vegetarians and have very efficient digestive systems",
    "Llamas live to be about 20 years old, though some only live for 15 years and others live to be 30 years old"
]

embeddings = OpenAIEmbeddings(
    model = "text-embedding-3-small"
)

dimensions = len(embeddings.embed_query(documents[0]))

docs = [Document(text) for text in documents]

Now we'll create the vector index, as follows:

collection.create_index(
    [("embedding", "vector")],
    name = "vector_index",
    kaiIndexOptions = {
        "index_type": "AUTO",
        "metric_type": "DOT_PRODUCT",
        "dimensions": dimensions
    }
)

Next, we'll store the documents and embeddings in SingleStore:

docsearch = MongoDBAtlasVectorSearch.from_documents(
    docs,
    embeddings,
    collection = collection,
    index_name = "vector_index"
)

We'll now ask a question:

prompt = "What animals are llamas related to?"
docs = docsearch.similarity_search(prompt)
data = docs[0].page_content
print(data)

Example output:

Llamas are members of the camelid family meaning they're pretty closely related to vicuñas and camels

Next, we'll use an LLM, as follows:

openai_client = OpenAI()

response = openai_client.chat.completions.create(
    model = "gpt-4o-mini",
    messages = [
        {"role": "system", "content": "You are a helpful assistant. Provide more details."},
        {"role": "user", "content": f"Using this data: {data}. Respond to this prompt: {prompt}"}
    ]
)
print(response.choices[0].message.content)

Example output:

Llamas are related to several animals within the camelid family. The closest relatives of llamas include:

1. **Vicuñas** – These are wild South American camelids that are similar in appearance to llamas but are smaller and are known for their fine wool.

2. **Alpacas** – Alpacas are domesticated camelids closely related to llamas, and they are primarily bred for their soft and luxurious fleece.

3. **Guanacos** – Guanacos are wild relatives of llamas and are also native to South America. They are similar in build and habitat preference.

4. **Camels** – Though they are not native to South America, camels (both dromedary and Bactrian) belong to the same family (Camelidae) as llamas, thus making them distant relatives.

These relationships highlight the diversity within the camelid family and the close connections among these species.

Summary

LangChain's MongoDBAtlasVectorSearch provides an extensive API and we have only tested a very small part. However, the results so far appear promising. Further tests are needed to determine the complete level of compatibility between MongoDBAtlasVectorSearch and Kai.

How to Build a ChatGPT Super App

Akmal Chaudhri — Tue, 21 Jan 2025 08:25:26 +0000

Abstract

SingleStore is a powerful multi-model database system and platform designed to support a wide variety of business use cases. Its distinctive features allow businesses to unify multiple database systems into a single platform, reducing Total Cost of Ownership (TCO) and simplifying developer workflows by eliminating the need for complex integration tools.

In this article, we'll explore how SingleStore can transform email campaigns for a web analytics company, enabling the creation of personalised and highly targeted email content.

The notebook file used in the article is available on GitHub.

Introduction

A web analytics company relies on email campaigns to engage with customers. However, a generic approach to targeting customers often misses opportunities to maximise business potential. A more effective solution would involve using a Large Language Model (LLM) to craft personalised email messages.

Consider a scenario where user behaviour data are stored in a NoSQL database like MongoDB, while valuable documentation resides in a Vector Database, such as Pinecone. Managing these multiple systems can become complex and resource-intensive, highlighting the need for a unified solution.

SingleStore, a versatile multi-model database, supports various data formats, including JSON, and offers built-in vector functions. It seamlessly integrates with LLMs, making it a powerful alternative to managing multiple database systems. In this article, we'll demonstrate how easily SingleStore can replace both MongoDB and Pinecone, simplifying operations without compromising functionality.

In our example application, we'll use an LLM to generate unique emails for our customers. To help the LLM learn how to target our customers, we'll use a number of well-known analytics companies as learning material for the LLM. We'll further customise the content based upon the user behaviour. Customer data are stored in MongoDB. Different stages of user behaviour are stored in Pinecone. The user behaviour will allow the LLM to generate personalised emails. Finally, we'll consolidate the data stored in MongoDB and Pinecone by using SingleStore.

Create a SingleStore Cloud account

We'll store our OpenAI API Key and Pinecone API Key in the secrets vault using OPENAI_API_KEY and PINECONE_API_KEY, respectively.

Import the notebook

We'll download the notebook from GitHub.

From the left navigation pane in the SingleStore cloud portal, we'll select DEVELOP > Data Studio.

In the top right of the web page, we'll select New Notebook > Import From File. We'll use the wizard to locate and import the notebook we downloaded from GitHub.

Run the notebook

Generic email template

We'll start by generating generic email templates and then use an LLM to transform them into personalised messages for each customer. This way, we can address each recipient by name and introduce them to the benefits of our web analytics platform.

We can generate a generic email, as follows:

people = ["Alice", "Bob", "Charlie", "David", "Emma"]

for person in people:
    message = (
        f"Hey {person},\n"
        "Check out our web analytics platform, it's Awesome!\n"
        "It's perfect for your needs. Buy it now!\n"
        "- Marketer John"
    )
    print(message)
    print("_" * 100)

For example, Alice would see the following message:

Hey Alice,
Check out our web analytics platform, it's Awesome!
It's perfect for your needs. Buy it now!
- Marketer John

Other users would receive the same message, but with their name, respectively.

Adding a Large Language Model (LLM)

We can easily bring an LLM into our application by providing it with a role and giving it some information, as follows:

system_message = """
You are a helpful assistant.
My name is Marketer John.
You help write the body of an email for a fictitious company called 'Awesome Web Analytics'.
This is a web analytics company that is similar to the top 5 web analytics companies (perform a web search to determine the current top 5 web analytics companies).
The goal is to write a custom email to users to get them interested in our services.
The email should be less than 150 words.
Address the user by name.
End with my signature.
"""

We'll create a function to call the LLM:

def chatgpt_generate_email(prompt, person):
    conversation = [
        {"role": "system", "content": prompt},
        {"role": "user", "content": person},
        {"role": "assistant", "content": ""}
    ]

    response = openai_client.chat.completions.create(
        model = "gpt-4o-mini",
        messages = conversation,
        temperature = 1.0,
        max_tokens = 800,
        top_p = 1,
        frequency_penalty = 0,
        presence_penalty = 0
    )

    assistant_reply = response.choices[0].message.content
    return assistant_reply

Looping through the list of users and calling the LLM produces unique emails:

openai_client = OpenAI()

# Define a list to store the responses
emails = []

# Loop through each person and generate the conversation
for person in people:
    email = chatgpt_generate_email(system_message, person)
    emails.append(
        {
            "person": person,
            "assistant_reply": email
        }
    )

For example, this is what Alice might see:

Person: Alice
Subject: Unlock Your Website's Potential with Awesome Web Analytics!

Hi Alice,

Are you ready to take your website to new heights? At Awesome Web Analytics, we provide cutting-edge insights that empower you to make informed decisions and drive growth. 

With our powerful analytics tools, you can understand user behavior, optimize performance, and boost conversions—all in real-time! Unlike other analytics platforms, we offer personalized support to guide you every step of the way.

Join countless satisfied customers who have transformed their online presence. Discover how we stack up against competitors like Google Analytics, Adobe Analytics, and Matomo, but with a focus on simplicity and usability.

Let us help you turn data into your greatest asset!

Best,  
Marketer John  
Awesome Web Analytics

Equally unique emails would be generated for the other users.

Customising email content with user behaviour

By categorising users based on their behaviour stages, we can further customise email content to align with their specific needs. An LLM will assist in crafting emails that encourage users to progress through different stages, ultimately improving their understanding and usage of various services.

At present, user data are held in a MongoDB database with a record structure similar to the following:

{
    '_id': ObjectId('64afb3fda9295d8421e7a19f'),
    'first_name': 'James',
    'last_name': 'Villanueva',
    'company_name': 'Foley-Turner',
    'stage': 'generating a tracking code',
    'created_date': 1987-11-09T12:43:26.000+00:00
}

We'll connect to MongoDB to get the data, as follows:

try:
    mongo_client = MongoClient("mongodb+srv://admin:<password>@<host>/?retryWrites=true&w=majority")
    mongo_db = mongo_client["mktg_email_demo"]
    collection = mongo_db["customers"]
    print("Connected successfully")
except Exception as e:
    print(e)

We'll replace <password> and <host> with the values from MongoDB Atlas.

We have a number of user behaviour stages:

stages = [
    "getting started",
    "generating a tracking code",
    "adding tracking to your website",
    "real-time analytics",
    "conversion tracking",
    "funnels",
    "user segmentation",
    "custom event tracking",
    "data export",
    "dashboard customization"
]

def find_next_stage(current_stage):
    current_index = stages.index(current_stage)
    if current_index < len(stages) - 1:
        return stages[current_index + 1]
    else:
        return stages[current_index]

Using the data about behaviour stages, we'll ask the LLM to further customise the email, as follows:

limit = 5
emails = []

for record in collection.find(limit = limit):
    fname, stage = record.get("first_name"), record.get("stage")
    next_stage = find_next_stage(stage)

    system_message = f"""
    You are a helpful assistant, who works for me, Marketer John at Awesome Web Analytics.
    You help write the body of an email for a fictitious company called 'Awesome Web Analytics'.
    We are a web analytics company similar to the top 5 web analytics companies.
    We have users at various stages in our product's pipeline, and we want to send them helpful emails to encourage further usage of our product.
    Please write an email for {fname} who is on stage {stage} of the onboarding process.
    The next stage is {next_stage}.
    Ensure the email describes the benefits of moving to the next stage.
    Limit the email to 1 paragraph.
    End the email with my signature.
    """

    email = chatgpt_generate_email(system_message, fname)
    emails.append(
        {
            "fname": fname,
            "stage": stage,
            "next_stage": next_stage,
            "email": email
        }
    )

For example, here is an email generated for Michael:

First Name: Michael

Stage: funnels

Next Stage: user segmentation

Subject: Unlock Deeper Insights with User Segmentation!

Hi Michael,

Congratulations on successfully navigating the funnel stage of our onboarding process! As you move forward to user segmentation, you'll discover how this powerful tool will enable you to categorize your users based on their behaviors and demographics. By understanding your audience segments better, you can create tailored experiences that increase engagement and optimize conversions. This targeted approach not only enhances your marketing strategies but also drives meaningful results and growth for your business. We're excited to see how segmentation will elevate your analytics efforts!

Best,  
Marketer John  
Awesome Web Analytics

Further customising email content

To support user progress, we'll use Pinecone's vector embeddings, allowing us to direct users to relevant documentation for each stage. These embeddings make it effortless to guide users towards essential resources and further enhance their interactions with our product.

pc = Pinecone(
    api_key = pc_api_key
)

index_name = "mktg-email-demo"

if any(index["name"] == index_name for index in pc.list_indexes()):
    pc.delete_index(index_name)

pc.create_index(
    name = index_name,
    dimension = dimensions,
    metric = "euclidean",
    spec = ServerlessSpec(
        cloud = "aws",
        region = "us-east-1"
    )
)

pc_index = pc.Index(index_name)

pc.list_indexes()

We'll create the embeddings as follows:

def get_embeddings(text):
    text = text.replace("\n", " ")
    try:
        response = openai_client.embeddings.create(
            input = text,
            model = "text-embedding-3-small"
        )
        return response.data[0].embedding, response.usage.total_tokens, "success"
    except Exception as e:
        print(e)
        return "", 0, "failed"

id_counter = 1
ids_list = []

for stage in stages:
    embedding, tokens, status = get_embeddings(stage)

    parent = id_counter - 1

    pc_index.upsert([
        {
            "id": str(id_counter),
            "values": embedding,
            "metadata": {"content": stage, "parent": str(parent)}
        }
    ])

    ids_list.append(str(id_counter))

    id_counter += 1

We'll search Pinecone for matches, as follows:

def search_pinecone(embedding):
    match = pc_index.query(
        vector = [embedding],
        top_k = 1,
        include_metadata = True
    )["matches"][0]["metadata"]
    return match["content"], match["parent"]

Using the data, we can ask the LLM to further customise the email, as follows:

limit = 5
emails = []

for record in collection.find(limit = limit):
    fname, stage = record.get("first_name"), record.get("stage")

    # Get the current and next stages with their embedding
    this_stage = next((item for item in stages_w_embed if item["stage"] == stage), None)
    next_stage = next((item for item in stages_w_embed if item["stage"] == find_next_stage(stage)), None)

    if not this_stage or not next_stage:
        continue

    # Get content
    cur_content, cur_permalink = search_pinecone(this_stage["embedding"])
    next_content, next_permalink = search_pinecone(next_stage["embedding"])

    system_message = f"""
    You are a helpful assistant.
    I am Marketer John at Awesome Web Analytics.
    We are similar to the current top web analytics companies.
    We have users at various stages of using our product, and we want to send them helpful emails to encourage them to use our product more.
    Write an email for {fname}, who is on stage {stage} of the onboarding process.
    The next stage is {next_stage['stage']}.
    Ensure the email describes the benefits of moving to the next stage, and include this link: https://github.com/VeryFatBoy/mktg-email-flow/tree/main/docs/{next_content.replace(' ', '-')}.md.
    Limit the email to 1 paragraph.
    End the email with my signature: 'Best Regards, Marketer John.'
    """

    email = chatgpt_generate_email(system_message, fname)
    emails.append(
        {
            "fname": fname, 
            "stage": stage, 
            "next_stage": next_stage["stage"], 
            "email": email
        }
    )

For example, here is an email generated for Melissa:

First Name: Melissa

Stage: getting started

Next Stage: generating a tracking code

Subject: Take the Next Step with Awesome Web Analytics!

Hi Melissa,

We're thrilled to see you getting started on your journey with Awesome Web Analytics! The next step is generating your tracking code, which will allow you to start collecting valuable data about your website visitors. With this data, you can gain insights into user behavior, optimize your marketing strategies, and ultimately drive more conversions. To guide you through this process, check out our detailed instructions here: [Generating a Tracking Code](https://github.com/VeryFatBoy/mktg-email-flow/tree/main/docs/generating-a-tracking-code.md). We're here to support you every step of the way!

Best Regards,  
Marketer John.

We can see that we have refined the generic template and developed quite targeted emails.

Using SingleStore

Instead of managing separate database systems, we'll streamline our operations by using SingleStore. With its support for JSON, text, and vector embeddings, we can efficiently store all necessary data in one place, reducing TCO and simplifying our development processes.

We'll ingest the data from MongoDB using a pipeline, similar to the following:

USE mktg_email_demo;

CREATE LINK mktg_email_demo.link AS MONGODB
CONFIG '{"mongodb.hosts": "<primary>:27017, <secondary>:27017, <secondary>:27017",
        "collection.include.list": "mktg_email_demo.*",
        "mongodb.ssl.enabled": "true",
        "mongodb.authsource": "admin",
        "mongodb.members.auto.discover": "false"}'
CREDENTIALS '{"mongodb.user": "admin",
            "mongodb.password": "<password>"}';

CREATE TABLES AS INFER PIPELINE AS LOAD DATA LINK mktg_email_demo.link '*' FORMAT AVRO;

START ALL PIPELINES;

We'll replace <primary>, <secondary>, <secondary> and <password> with the values from MongoDB Atlas.

The customer table will be created by the pipeline.

The vector embeddings for the behaviour stages can created as follows:

df_list = []
id_counter = 1

for stage in stages:
    embedding, tokens, status = get_embeddings(stage)

    parent = id_counter - 1

    stage_df = pd.DataFrame(
        {
            "id": [id_counter],
            "content": [stage],
            "embedding": [embedding],
            "parent": [parent]
        }
    )

    df_list.append(stage_df)

    id_counter += 1

df = pd.concat(df_list, ignore_index = True)

We'll need a table to store the data:

USE mktg_email_demo;

DROP TABLE IF EXISTS docs_splits;

CREATE TABLE IF NOT EXISTS docs_splits (
    id INT,
    content TEXT,
    embedding VECTOR(:dimensions),
    parent INT
);

Then we can save the data in the table:

df.to_sql(
    "docs_splits",
    con = db_connection,
    if_exists = "append",
    index = False,
    chunksize = 1000
)

We'll search SingleStore for matches, as follows:

def search_s2(vector):
    query = """
        SELECT content, parent
        FROM docs_splits
        ORDER BY (embedding <-> :vector) ASC
        LIMIT 1
    """
    with db_connection.connect() as con:
        result = con.execute(text(query), {"vector": str(vector)})
        return result.fetchone()

Using the data, we can ask the LLM to customise the email, as follows:

limit = 5
emails = []

# Create a connection
with db_connection.connect() as con:
    query = "SELECT _more :> JSON FROM customers LIMIT :limit"
    result = con.execute(text(query), {"limit": limit})

    for customer in result:
        customer_data = customer[0]
        fname, stage = customer_data["first_name"], customer_data["stage"]

        # Retrieve current and next stage embeddings
        this_stage = next((item for item in stages_w_embed if item["stage"] == stage), None)
        next_stage = next((item for item in stages_w_embed if item["stage"] == find_next_stage(stage)), None)

        if not this_stage or not next_stage:
            continue

        # Get content
        cur_content, cur_permalink = search_s2(this_stage["embedding"])
        next_content, next_permalink = search_s2(next_stage["embedding"])

        # Create the system message
        system_message = f"""
        You are a helpful assistant.
        I am Marketer John at Awesome Web Analytics.
        We are similar to the current top web analytics companies.
        We have users that are at various stages in using our product, and we want to send them helpful emails to get them to use our product more.
        Write an email for {fname} who is on stage {stage} of the onboarding process.
        The next stage is {next_stage['stage']}.
        Ensure the email describes the benefits of moving to the next stage, then always share this link: https://github.com/VeryFatBoy/mktg-email-flow/tree/main/docs/{next_content.replace(' ', '-')}.md.
        Limit the email to 1 paragraph.
        End the email with my signature: 'Best Regards, Marketer John.'
        """

        email = chatgpt_generate_email(system_message, fname)
        emails.append(
            {
                "fname": fname,
                "stage": stage,
                "next_stage": next_stage["stage"],
                "email": email,
            }
        )

For example, here is an email generated for Joseph:

First Name: Joseph

Stage: generating a tracking code

Next Stage: adding tracking to your website

Subject: Take the Next Step in Your Analytics Journey!

Hi Joseph,

Congratulations on generating your tracking code! The next step is to add tracking to your website, which is crucial for unlocking the full power of our analytics tools. By integrating the tracking code, you will start collecting valuable data about your visitors, enabling you to understand user behavior, optimize your website, and drive better results for your business. Ready to get started? Check out our detailed guide here: [Adding Tracking to Your Website](https://github.com/VeryFatBoy/mktg-email-flow/tree/main/docs/adding-tracking-to-your-website.md).

Best Regards,  
Marketer John.

Summary

Through this practical demonstration, we've seen the power of SingleStore in transforming our email campaigns for the better. Its multi-model capabilities, combined with AI-driven personalisation, provide a comprehensive solution for consolidating databases and improving customer engagement. With SingleStore as our single source of truth, we've not only simplified our workflows but also ensured that our email campaigns deliver maximum impact and value to our customers.

Acknowledgements

I thank Wes Kennedy for the original demo code, which was adapted for this article.