Repository: mindsdb/mindsdb
Pull request: [ENH] Ollama handler
## Description

This PR adds an integration to Ollama, a popular LLM library that simplifies LLM deployment (macOS only, for now).

It also moves a helper function from the OpenAI handler to the common `integrations/libs/llm_utils.py` file, so that any LLM handler can use it. Moving forward, this file should host all LLM related utility functions, and it may even become its own namespace, we'll see. Due to this, a bit of duplicated code is removed from the PALM integration.

Fixes #7351

## Type of change
- [x] ⚡ New feature (non-breaking change which adds functionality)
- [x] 📄 This change requires a documentation update

## Verification Process

To ensure the changes are working as expected:

 - [ ]   Test Location: Specify the URL or path for testing.
 - [ ]   Verification Steps: Outline the steps or queries needed to validate the change. Include any data, configurations, or actions required to reproduce or see the new functionality.

## Additional Media:

- [ ] I have attached a brief loom video or screenshots showcasing the new functionality or change.

## Checklist:

- [ ] My code follows the style guidelines(PEP 8) of MindsDB.
- [ ] I have appropriately commented on my code, especially in complex areas.
- [ ] Necessary documentation updates are either made or tracked in issues.
- [ ] Relevant unit and integration tests are updated or added.





Problem statement:
[Integration]: Ollama
### Is there an existing integration?

- [X] I have searched the existing integrations.

### Use Case

Local LLMs, fast inference.

### Motivation

Ollama enables fast inferencing for local LLMs like Llama2.

### Implementation

Start with macOS support.

### Anything else?

_No response_

Hints from issue discussion:
We may want to do a set of primitives for LLM fine tuning as part of this integration. E.g. chunking, embedding and all other RAG stuff should be at the base level so that other engines can leverage it.

Commit message: fix instruction

Patch to review:
diff --git a/mindsdb/integrations/handlers/ollama_handler/README.md b/mindsdb/integrations/handlers/ollama_handler/README.md
new file mode 100644
index 00000000000..ea1cf65acd0
--- /dev/null
+++ b/mindsdb/integrations/handlers/ollama_handler/README.md
@@ -0,0 +1,85 @@
+# Ollama handler 
+
+## Briefly describe the ML framework this handler integrates with MindsDB, and how?
+[Ollama](https://ollama.ai/) is a project that enables easy local deployment of Large Language Models (LLMs). All models supported by Ollama are available in MindsDB through this integration.
+
+For now, this integration will only work in MacOS, with Linux and Windows to come later.
+
+Call this handler by
+`USING ENGINE="ollama"`, you can see a full example at the end of this readme.
+
+## Why is this integration useful? What does the ideal predictive use case for this integration look like? When would you definitely not use this integration?
+Locally deployed LLMs can be desirable for a wide variety of reasons. In this case, data privacy, developer feedback-loop speed and inference cost reduction can be powerful reasons to opt for a local LLM.
+
+Ideal predictive use cases, as in other LLM-focused integrations (e.g. OpenAI, Anthropic, Cohere), will be anything involving language understanding and generation, including but not limited to:
+- zero-shot text classification
+- sentiment analysis
+- question answering
+- summarization
+- translation
+
+Some current limitations of local LLMs:
+- overall weaker performance (ranging from "somewhat" to "a lot") than commercial cloud-based LLMs, particularly GPT-4. Please study options carefully and benchmark thoroughly to ensure your LLM is at the right level of performance for your use case before deploying to production.
+- steep entry barrier due to required hardware specs (macOS only, M1 chip or greater, a lot of RAM depending on model size)
+
+## Are models created with this integration fast and scalable, in general?
+Model training is not required, as these are pretrained models. 
+
+Inference is generally fast, however stream generation is not supported at this time in MindsDB, so completions are only returned once the model has finished generating the entire sequence.
+
+## What are the recommended system specifications?
+
+* A macOS machine, M1 chip or greater. 
+* A working Ollama installation. For instructions refer to their [webpage](https://ollama.ai). This step should be really simple.
+* For 7B models, at least 8GB RAM is recommended. 
+* For 13B models, at least 16GB RAM is recommended. 
+* For 70B models, at least 64GB RAM is recommended.
+
+More information [here](https://ollama.ai/library/llama2). Minimum specs can vary depending on the model.
+
+## To what degree can users control the underlying framework by passing parameters via the USING syntax?
+The prompt template can be overridden at prediction time, e.g.:
+
+```sql
+-- example: override template at prediction time
+SELECT text, completion
+FROM my_llama2
+WHERE text = 'hi there!';
+USING 
+prompt_template = 'Answer using exactly five words: {{text}}:';
+```
+
+## Does this integration offer model explainability or insights via the DESCRIBE syntax?
+It replicates the information exposed by the Ollama API, plus a few additional MindsDB-specific fields.
+
+Supported commands are:
+1. `DESCRIBE ollama_model;`
+2. `DESCRIBE ollama_model.model;`
+3. `DESCRIBE ollama_model.features;`
+
+## Does this integration support fine-tuning pre-existing models (i.e. is the update() method implemented)? Are there any caveats?
+Not at this time.
+
+## Any directions for future work in subsequent versions of the handler?
+A few are commented in the code:
+1. add support for overriding modelfile params (e.g. temperature)
+2. add support for storing `context` short conversational memory
+3. actually store all model artifacts in the engine storage, instead of the internal Ollama mechanism. This may require upstream changes, though.
+
+## Please provide a minimal SQL example that uses this ML engine (pointers to integration tests in the PR also valid)
+```sql
+CREATE ML_ENGINE ollama FROM ollama;
+
+CREATE MODEL my_llama2
+PREDICT completion
+USING
+model_name = 'llama2',
+engine = 'ollama';
+
+DESCRIBE my_llama2.model;
+DESCRIBE my_llama2.features;
+
+SELECT text, completion
+FROM my_llama2
+WHERE text = 'hi there!';
+```
\ No newline at end of file
diff --git a/mindsdb/integrations/handlers/ollama_handler/__about__.py b/mindsdb/integrations/handlers/ollama_handler/__about__.py
new file mode 100644
index 00000000000..56170a2b5fb
--- /dev/null
+++ b/mindsdb/integrations/handlers/ollama_handler/__about__.py
@@ -0,0 +1,9 @@
+__title__ = 'MindsDB Ollama handler'
+__package_name__ = 'mindsdb_ollama_handler'
+__version__ = '0.0.1'
+__description__ = "MindsDB handler for Ollama"
+__author__ = 'MindsDB Inc'
+__github__ = 'https://github.com/mindsdb/mindsdb'
+__pypi__ = 'https://pypi.org/project/mindsdb/'
+__license__ = 'GPL-3.0'
+__copyright__ = 'Copyright 2023- mindsdb'
diff --git a/mindsdb/integrations/handlers/ollama_handler/__init__.py b/mindsdb/integrations/handlers/ollama_handler/__init__.py
new file mode 100644
index 00000000000..dd19ac1c834
--- /dev/null
+++ b/mindsdb/integrations/handlers/ollama_handler/__init__.py
@@ -0,0 +1,18 @@
+from mindsdb.integrations.libs.const import HANDLER_TYPE
+
+from .__about__ import __version__ as version, __description__ as description
+try:
+    from .ollama_handler import OllamaHandler as Handler
+    import_error = None
+except Exception as e:
+    Handler = None
+    import_error = e
+
+title = 'Ollama'
+name = 'ollama'
+type = HANDLER_TYPE.ML
+permanent = False
+
+__all__ = [
+    'Handler', 'version', 'name', 'type', 'title', 'description', 'import_error'
+]
diff --git a/mindsdb/integrations/handlers/ollama_handler/ollama_handler.py b/mindsdb/integrations/handlers/ollama_handler/ollama_handler.py
new file mode 100644
index 00000000000..5afb385ed6b
--- /dev/null
+++ b/mindsdb/integrations/handlers/ollama_handler/ollama_handler.py
@@ -0,0 +1,127 @@
+import json
+import requests
+from typing import Dict, Optional
+
+import pandas as pd
+
+from mindsdb.integrations.libs.base import BaseMLEngine
+from mindsdb.integrations.libs.llm_utils import get_completed_prompts
+
+
+class OllamaHandler(BaseMLEngine):
+    name = "ollama"
+    SERVE_URL = 'http://localhost:11434'
+    MODEL_LIST_URL = 'https://registry.ollama.ai/v2/_catalog'
+
+    @staticmethod
+    def create_validation(target, args=None, **kwargs):
+        if 'using' not in args:
+            raise Exception("Ollama engine requires a USING clause! Refer to its documentation for more details.")
+        else:
+            args = args['using']
+
+        # check model version is valid
+        all_models = requests.get(OllamaHandler.MODEL_LIST_URL).json()['repositories']
+        base_models = list(filter(lambda x: 'library/' in x, all_models))
+        valid_models = [m.split('/')[-1] for m in base_models]
+
+        if 'model_name' not in args:
+            raise Exception('`model_name` must be provided in the USING clause.')
+        elif args['model_name'] not in valid_models:
+            raise Exception(f"The model `{args['model_name']}` is not yet supported by Ollama! Please choose one of the following: {valid_models}")  # noqa
+
+        # check ollama service health
+        status = requests.get(OllamaHandler.SERVE_URL + '/api/tags').status_code
+        if status != 200:
+            raise Exception(f"Ollama service is not working (status `{status}`). Please double check it is running and try again.")  # noqa
+
+    def create(self, target: str, df: Optional[pd.DataFrame] = None, args: Optional[Dict] = None) -> None:
+        """ Pull LLM artifacts with Ollama API. """
+        # arg setter
+        args = args['using']
+        args['target'] = target
+        self.model_storage.json_set('args', args)
+
+        # download model
+        # TODO v2: point Ollama to the engine storage folder instead of their default location
+        model_name = args['model_name']
+        # blocking operation, finishes once model has been fully pulled and served
+        requests.post(OllamaHandler.SERVE_URL + '/api/pull', json={'name': model_name})
+
+    def predict(self, df: pd.DataFrame, args: Optional[Dict] = None) -> pd.DataFrame:
+        """
+        Generate text completions with the local LLM.
+            Args:
+                df (pd.DataFrame): The input DataFrame containing data to predict.
+                args (Optional[Dict]): Additional arguments for prediction parameters.
+            Returns:
+                pd.DataFrame: The DataFrame containing row-wise text completions.
+        """
+        # setup
+        pred_args = args.get('predict_params', {})
+        args = self.model_storage.json_get('args')
+        model_name, target_col = args['model_name'], args['target']
+        prompt_template = pred_args.get('prompt_template',
+                                        args.get('prompt_template', 'Answer the following question: {{{{text}}}}'))
+        # TODO v2: add support for overriding modelfile params (e.g. temperature)
+
+        # prepare prompts
+        prompts, empty_prompt_ids = get_completed_prompts(prompt_template, df)
+        df['__mdb_prompt'] = prompts
+
+        # call llm
+        completions = []
+        for i, row in df.iterrows():
+            if i not in empty_prompt_ids:
+                raw_output = requests.post(
+                    OllamaHandler.SERVE_URL + '/api/generate',
+                    json={
+                        'model': args['model_name'],
+                        'prompt': row['__mdb_prompt'],
+                    }
+                )
+                out_tokens = raw_output.content.decode().split('\n')  # stream of output tokens
+
+                tokens = []
+                for o in out_tokens:
+                    if o != '':
+                        # TODO v2: add support for storing `context` short conversational memory
+                        info = json.loads(o)
+                        if 'response' in info:
+                            token = info['response']
+                            tokens.append(token)
+
+                completions.append(''.join(tokens))
+            else:
+                completions.append('')
+
+        # consolidate output
+        data = pd.DataFrame(completions)
+        data.columns = [target_col]
+        return data
+
+    def describe(self, attribute: Optional[str] = None) -> pd.DataFrame:
+        args = self.model_storage.json_get('args')
+        model_name, target_col = args['model_name'], args['target']
+        prompt_template = args.get('prompt_template', 'Answer the following question: {{{{text}}}}')
+
+        if attribute == "features":
+            return pd.DataFrame([[target_col, prompt_template]], columns=['target_column', 'mindsdb_prompt_template'])
+
+        # get model info
+        else:
+            model_info = requests.post(OllamaHandler.SERVE_URL + '/api/show', json={'name': model_name}).json()
+            return pd.DataFrame([[
+                model_name,
+                model_info['license'],
+                model_info['modelfile'],
+                model_info['parameters'],
+                model_info['template'],
+            ]],
+                columns=[
+                    'model_type',
+                    'license',
+                    'modelfile',
+                    'parameters',
+                    'ollama_base_template',
+                ])
diff --git a/mindsdb/integrations/handlers/ollama_handler/requirements.txt b/mindsdb/integrations/handlers/ollama_handler/requirements.txt
new file mode 100644
index 00000000000..fb6c7ed7ec6
--- /dev/null
+++ b/mindsdb/integrations/handlers/ollama_handler/requirements.txt
@@ -0,0 +1 @@
+pandas
diff --git a/mindsdb/integrations/handlers/openai_handler/openai_handler.py b/mindsdb/integrations/handlers/openai_handler/openai_handler.py
index d312321e858..1c98b0836ad 100644
--- a/mindsdb/integrations/handlers/openai_handler/openai_handler.py
+++ b/mindsdb/integrations/handlers/openai_handler/openai_handler.py
@@ -1,5 +1,4 @@
 import os
-import re
 import math
 import json
 import shutil
@@ -20,6 +19,7 @@
 from mindsdb.integrations.handlers.openai_handler.helpers import retry_with_exponential_backoff, \
     truncate_msgs_for_token_limit
 from mindsdb.integrations.utilities.handler_utils import get_api_key
+from mindsdb.integrations.libs.llm_utils import get_completed_prompts
 
 CHAT_MODELS = ('gpt-3.5-turbo', 'gpt-3.5-turbo-16k', 'gpt-4', 'gpt-4-32k')
 
@@ -166,7 +166,7 @@ def predict(self, df, args=None):
                 prompts = list(df[args['question_column']].apply(lambda x: str(x)))
                 empty_prompt_ids = np.where(df[[args['question_column']]].isna().all(axis=1).values)[0]
             elif args.get('prompt_template'):
-                prompts, empty_prompt_ids = self._get_completed_prompts(base_template, df)
+                prompts, empty_prompt_ids = get_completed_prompts(base_template, df)
             else:
                 raise Exception('Image mode needs either `prompt_template` or `question_column`.')
 
@@ -197,7 +197,7 @@ def predict(self, df, args=None):
                 raise Exception(f"Conversational modes are only available for the following models: {', '.join(self.chat_completion_models)}")  # noqa
 
             if args.get('prompt_template', False):
-                prompts, empty_prompt_ids = self._get_completed_prompts(base_template, df)
+                prompts, empty_prompt_ids = get_completed_prompts(base_template, df)
 
             elif args.get('context_column', False):
                 empty_prompt_ids = np.where(df[[args['context_column'],
@@ -622,37 +622,3 @@ def _check_ft_status(model_id):
 
         self.model_storage.json_set('args', args)
         shutil.rmtree(temp_storage_path)
-
-    @staticmethod
-    def _get_completed_prompts(base_template, df):
-        columns = []
-        spans = []
-        matches = list(re.finditer("{{(.*?)}}", base_template))
-
-        assert len(matches) > 0, 'No placeholders found in the prompt, please provide a valid prompt template.'
-
-        first_span = matches[0].start()
-        last_span = matches[-1].end()
-
-        for m in matches:
-            columns.append(m[0].replace('{', '').replace('}', ''))
-            spans.extend((m.start(), m.end()))
-
-        spans = spans[1:-1]  # omit first and last, they are added separately
-        template = [base_template[s:e] for s, e in list(zip(spans, spans[1:]))[::2]]  # take every other to skip placeholders  # noqa
-        template.insert(0, base_template[0:first_span])  # add prompt start
-        template.append(base_template[last_span:])  # add prompt end
-
-        empty_prompt_ids = np.where(df[columns].isna().all(axis=1).values)[0]
-
-        df['__mdb_prompt'] = ''
-        for i in range(len(template)):
-            atom = template[i]
-            if i < len(columns):
-                col = df[columns[i]].replace(to_replace=[None], value='')  # add empty quote if data is missing
-                df['__mdb_prompt'] = df['__mdb_prompt'].apply(lambda x: x + atom) + col.astype("string")
-            else:
-                df['__mdb_prompt'] = df['__mdb_prompt'].apply(lambda x: x + atom)
-        prompts = list(df['__mdb_prompt'])
-
-        return prompts, empty_prompt_ids
diff --git a/mindsdb/integrations/handlers/palm_handler/palm_handler.py b/mindsdb/integrations/handlers/palm_handler/palm_handler.py
index 27568613c04..a91b486a49c 100644
--- a/mindsdb/integrations/handlers/palm_handler/palm_handler.py
+++ b/mindsdb/integrations/handlers/palm_handler/palm_handler.py
@@ -1,5 +1,4 @@
 import textwrap
-import re
 from pydantic import BaseModel, Extra
 
 import google.generativeai as palm
@@ -11,6 +10,7 @@
 from mindsdb.integrations.libs.base import BaseMLEngine
 
 from mindsdb.integrations.utilities.handler_utils import get_api_key
+from mindsdb.integrations.libs.llm_utils import get_completed_prompts
 
 CHAT_MODELS = (
     "models/chat-bison-001",
@@ -217,7 +217,7 @@ def predict(self, df, args=None):
                 )  # noqa
 
             if args_model.prompt_template:
-                prompts, empty_prompt_ids = self._get_completed_prompts(
+                prompts, empty_prompt_ids = get_completed_prompts(
                     base_template, df
                 )
                 if len(prompts) == 0:
@@ -444,46 +444,3 @@ def _tidy(comp):
             completion.extend({"error": str(e)})
 
         return completion
-
-    @staticmethod
-    def _get_completed_prompts(base_template, df):
-        columns = []
-        spans = []
-        matches = list(re.finditer("{{(.*?)}}", base_template))
-
-        assert (
-            len(matches) > 0
-        ), "No placeholders found in the prompt, please provide a valid prompt template."
-
-        first_span = matches[0].start()
-        last_span = matches[-1].end()
-
-        for m in matches:
-            name = m[0].replace("{", "").replace("}", "").strip()
-            columns.append(name)
-            spans.extend((m.start(), m.end()))
-
-        spans = spans[1:-1]  # omit first and last, they are added separately
-        template = [
-            base_template[s:e] for s, e in list(zip(spans, spans[1:]))[::2]
-        ]  # take every other to skip placeholders  # noqa
-        template.insert(0, base_template[0:first_span])  # add prompt start
-        template.append(base_template[last_span:])  # add prompt end
-
-        empty_prompt_ids = np.where(df[columns].isna().all(axis=1).values)[0]
-
-        df["__mdb_prompt"] = ""
-        for i in range(len(template)):
-            atom = template[i]
-            if i < len(columns):
-                col = df[columns[i]].replace(
-                    to_replace=[None], value=""
-                )  # add empty quote if data is missing
-                df["__mdb_prompt"] = df["__mdb_prompt"].apply(
-                    lambda x: x + atom
-                ) + col.astype("string")
-            else:
-                df["__mdb_prompt"] = df["__mdb_prompt"].apply(lambda x: x + atom)
-        prompts = list(df["__mdb_prompt"])
-
-        return prompts, empty_prompt_ids
diff --git a/mindsdb/integrations/libs/llm_utils.py b/mindsdb/integrations/libs/llm_utils.py
new file mode 100644
index 00000000000..3edb11f1d00
--- /dev/null
+++ b/mindsdb/integrations/libs/llm_utils.py
@@ -0,0 +1,41 @@
+import re
+import numpy as np
+
+
+def get_completed_prompts(base_template, df):
+    """
+        Helper method that produces formatted prompts given a template and data in a Pandas DataFrame.
+        It also returns the ID of any empty templates that failed to be filled due to missing data.
+    """
+    columns = []
+    spans = []
+    matches = list(re.finditer("{{(.*?)}}", base_template))
+
+    assert len(matches) > 0, 'No placeholders found in the prompt, please provide a valid prompt template.'
+
+    first_span = matches[0].start()
+    last_span = matches[-1].end()
+
+    for m in matches:
+        columns.append(m[0].replace('{', '').replace('}', ''))
+        spans.extend((m.start(), m.end()))
+
+    spans = spans[1:-1]  # omit first and last, they are added separately
+    template = [base_template[s:e] for s, e in
+                list(zip(spans, spans[1:]))[::2]]  # take every other to skip placeholders  # noqa
+    template.insert(0, base_template[0:first_span])  # add prompt start
+    template.append(base_template[last_span:])  # add prompt end
+
+    empty_prompt_ids = np.where(df[columns].isna().all(axis=1).values)[0]
+
+    df['__mdb_prompt'] = ''
+    for i in range(len(template)):
+        atom = template[i]
+        if i < len(columns):
+            col = df[columns[i]].replace(to_replace=[None], value='')  # add empty quote if data is missing
+            df['__mdb_prompt'] = df['__mdb_prompt'].apply(lambda x: x + atom) + col.astype("string")
+        else:
+            df['__mdb_prompt'] = df['__mdb_prompt'].apply(lambda x: x + atom)
+    prompts = list(df['__mdb_prompt'])
+
+    return prompts, empty_prompt_ids


Code review comments:
diff_hunk: @@ -0,0 +1,127 @@
+import json
+import requests
+from typing import Dict, Optional
+
+import pandas as pd
+
+from mindsdb.integrations.libs.base import BaseMLEngine
+from mindsdb.integrations.libs.llm_utils import get_completed_prompts
+
+
+class OllamaHandler(BaseMLEngine):
+    name = "ollama"
+    SERVE_URL = 'http://localhost:11434'
+    MODEL_LIST_URL = 'https://registry.ollama.ai/v2/_catalog'
+
+    @staticmethod
+    def create_validation(target, args=None, **kwargs):
+        if 'using' not in args:
+            raise Exception("Ollama engine requires a USING clause! Refer to its documentation for more details.")
+        else:
+            args = args['using']
+
+        # check model version is valid
+        all_models = requests.get(OllamaHandler.MODEL_LIST_URL).json()['repositories']
path: mindsdb/integrations/handlers/ollama_handler/ollama_handler.py
text: @user1:
Maybe wrap this in try/except in case of connectivity issues

@author:
Good point!