Quick tip: Analysing Stock Tick Data in SingleStoreDB using LangChain and OpenAI's Whisper
Tags: singlestoredb, langchain, openai, vectordatabase
Published: 2023-09-14 00:56:32

## 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
This article will show how to connect a Kafka broker, streaming example stock tick data, to SingleStoreDB. We'll then query the data using English sentences through LangChain, which provides a basic question-and-answer capability for the tick data. We'll build a Python application, through several design iterations, to use [OpenAI's Whisper](https://github.com/openai/whisper) to ask questions through speech and use speech synthesis to reply.

The notebook file, SQL and Python code are available on [GitHub](https://github.com/VeryFatBoy/openai-whisper).

## Introduction
The ability to ask questions about a database system using natural language is familiar. However, it has become much easier to implement with modern tools like LangChain and OpenAI's Whisper. In this article, we'll see how.

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/singlestore/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps to create a free SingleStoreDB Cloud account. We'll use the following settings:

- **Workspace Group Name:** Whisper Demo Group
- **Cloud Provider:** AWS
- **Region:** US East 1 (N. Virginia)
- **Workspace Name:** whisper-demo
- **Size:** S-00
- **Settings:**
  - None selected

Once the workspace is available, we'll make a note of our **password** and **host**. The host will be available from **Whisper Demo Group > Overview > Workspaces > whisper-demo > Connect > SQL IDE > Host**. We'll need this information later for a Python application. We'll temporarily allow access from anywhere by configuring the firewall under **Whisper Demo Group > Firewall**.

## Create a Database and Tables
From the left navigation pane, we'll select **Develop > SQL Editor** to create a `timeseries_db` database, `tick` and `stock_sentiment` tables, as follows:

```sql
CREATE DATABASE IF NOT EXISTS timeseries_db;

USE timeseries_db;

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 stock_sentiment (
        headline  VARCHAR(250),
        positive  FLOAT,
        negative  FLOAT,
        neutral   FLOAT,
        url       TEXT,
        publisher VARCHAR(30),
        ts        DATETIME,
        symbol    VARCHAR(10)
);
```

## Create a Pipeline
Pipelines allow us to create streaming ingest feeds from various sources, such as Kafka, S3 and HDFS, using a single command. With pipelines, we can also perform ETL operations.

For our use case, we'll create a simple pipeline in SingleStoreDB as follows:

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

We'll control the rate of data ingestion using the `BATCH_INTERVAL`. Initially, we'll set this to 2500 milliseconds.

We'll configure the pipeline to start from the earliest offset, as follows:

```sql
ALTER PIPELINE tick SET OFFSETS EARLIEST;
```

and we'll test the pipeline before we start running it, as follows:

```sql
TEST PIPELINE tick LIMIT 1;
```

The output should be similar to the following:

```
+--------+---------------------+-------+-------+-------+-------+--------+
| symbol | ts                  | open  | high  | low   | price | volume |
+--------+---------------------+-------+-------+-------+-------+--------+
| AIV    | 2023-09-05 06:47:53 | 44.89 | 44.89 | 44.88 | 44.89 |    719 |
+--------+---------------------+-------+-------+-------+-------+--------+
```

> Remember, this is fictitious data

We'll now start the pipeline:

```sql
START PIPELINE tick;
```

## Load Stock Sentiment Data
We'll now load the data into the `stock_sentiment` table. The data are derived from [Kaggle](https://www.kaggle.com/datasets/miguelaenlle/massive-stock-news-analysis-db-for-nlpbacktests). First, we'll unpack the zip file that contains the SQL file. Next, we'll load the SQL file into SingleStoreDB Cloud using a MySQL client, as follows:

```shell
mysql -u admin -p<password> -h <host> timeseries_db < /path/to/stock_sentiment.sql
```

The `<password>` and `<host>` being replaced with the values we obtained from SingleStoreDB Cloud earlier. The `/path/to/` replaced with the actual path to where the SQL file is located.

## Load the Notebook
We'll use the notebook file available on [GitHub](https://github.com/VeryFatBoy/openai-whisper/blob/main/notebook/fintech.ipynb). From the left navigation pane in SingleStoreDB Cloud, we'll select **Notebooks**. In the top right of the web page will be **New Notebook** with a pulldown that has two options:

1. New Notebook
2. Import From File

We'll select the second option, locate the notebook file we downloaded from GitHub and load it into SingleStoreDB Cloud.

## Run the Notebook
We'll start by selecting the **Connection** (**whisper-demo**) and **Database** (**timeseries_db**) using the drop-down menus above the notebook.

### Analysing Time Series Data
Part 1 of the notebook contains a set of Time Series operations on the data in the `tick` table. These operations were described in greater detail in a [previous article](https://medium.com/@VeryFatBoy/using-singlestore-as-a-time-series-database-6517a1a36a4e).

### LangChain OnlinePDFLoader
Part 2 of the notebook loads the contents of a PDF document and vector embeddings into a table called `fintech_docs`. These operations were described in greater detail in a [previous article](https://dev.to/singlestore/quick-tip-singlestoredb-integration-with-langchain-51po). Replace `<PDF document URL>` with the hyperlink of your chosen FinTech document:

```python
from langchain.document_loaders import OnlinePDFLoader

loader = OnlinePDFLoader("<PDF document URL>")

data = loader.load()
```

The Fully Qualified Domain Name (FQDN) where the PDF file is located must be added to the firewall by selecting the **Edit Firewall** option in the top right.

We'll use ChatGPT to answer questions on the PDF file, such as:

```
"What are the best investment opportunities in Blockchain?"
```

### LangChain SQL Agent
Part 3 of the notebook contains LangChain agent operations on the data in the `tick` and `stock_sentiment` tables. This will be the main focus of this article.

First, we'll install some libraries:

```shell
!pip install langchain --quiet
!pip install langchain-openai --quiet
!pip install openai --quiet
```

Now, we'll enter our `OpenAI API Key`:

```python
import os
import getpass

os.environ["OPENAI_API_KEY"] = getpass.getpass("OpenAI API Key:")
```

Next, we'll need some imports:

```python
from langchain_openai import OpenAI
from langchain.agents import create_sql_agent
from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain.sql_database import SQLDatabase
```

We'll configure the LangChain toolkit and agent, as follows:

```python
db = SQLDatabase.from_uri(connection_url)

llm = OpenAI(temperature = 0, verbose = False)

toolkit = SQLDatabaseToolkit(db = db, llm = llm)

agent_executor = create_sql_agent(
    llm = OpenAI(temperature = 0),
    toolkit = toolkit,
    max_iterations = 15,
    max_execution_time = 60,
    top_k = 3,
    verbose = False
)
```

We'll now test the code using an example query, as follows:

```python
agent_executor.invoke(
    "From the tick table, which stock symbol saw the least volatility in share trading in the dataset?",
    return_only_outputs = True
)["output"]
```

Here is some example output:

```
'The stock symbol with the least volatility in share trading in the dataset is FTR.'
```

We'll make this a little more interactive using the following code:

```python
query = input("Please enter your question:")
agent_executor.invoke(
    query,
    return_only_outputs = True
)["output"]
```

We'll test this with an example query:

```
Using the symbol A, what is the most positive sentiment in the stock_sentiment table and the current best price for this symbol from the tick table?
```

Here is some example output:

```
'The most positive sentiment for symbol A is 0.9576 and the current best price is 46.43.'
```

To see the chain output, we'll set `verbose` to `True`, as follows:

```python
agent_executor = create_sql_agent(
    llm = OpenAI(temperature = 0),
    toolkit = toolkit,
    max_iterations = 15,
    max_execution_time = 60,
    top_k = 3,
    verbose = True
)
```

An example of chain output from another database was shown in a [previous article](https://medium.com/@VeryFatBoy/quick-tip-using-langchains-sqldatabasetoolkit-with-singlestoredb-a2106d3260f5).

## Bonus: Build a Visual Python Application
We'll now build a simple Python application that uses OpenAI's Whisper to ask questions about the database system. An [excellent article](https://medium.com/codingthesmartway-com-blog/voice-to-text-made-easy-implementing-a-python-app-with-openais-whisper-speech-to-text-api-e8f415a5f737) inspired this application.

### Install the Required Software
For this article, this is the software that was required in a clean install of Ubuntu 22.04.2 running in a VMware Fusion Virtual Machine:

```shell
sudo apt install ffmpeg
sudo apt install libespeak1
sudo apt install portaudio19-dev
sudo apt install python3-tk
sudo apt install python3-pil python3-pil.imagetk
```

as well as the following packages:

```
langchain
langchain-openai
matplotlib
openai
openai-whisper
pyaudio
pymysql
pyttsx3
wave
```

These can be found in the `requirements.txt` file on GitHub. Run the file as follows:

```shell
pip install -r requirements.txt
```

`openai-whisper` may take a while to install.

We'll need to provide an `OpenAI API Key` in our environment. For example:

```shell
export OPENAI_API_KEY="<OpenAI API Key>"
```

Replace `<OpenAI API Key>` with your key.

In each of the following applications, we have the following code:

```python
s2_password = "<password>"
s2_host = "<host>"
```

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

### First Iteration
Let's start with a simple visual Python application using [Tkinter](https://docs.python.org/3/library/tkinter.html). We'll also add voice recognition using OpenAI's Whisper. The application enables up to 20 seconds of recorded speech. It can be run as follows:

```shell
python3 record-transcribe.py
```

Example output is shown in Figure 1.

![Figure 1. First Iteration.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r1sfph45924x7tkmnomy.png)

<center>Figure 1. First Iteration.</center>

### Second Iteration
In the next iteration, we'll add an Audio Waveform. We'll run the program as follows:

```shell
python3 record-transcribe-visualise.py
```

Example output is shown in Figure 2.

![Figure 2. Second Iteration.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cg6ltzc801tvsdd052cs.png)

<center>Figure 2. Second Iteration.</center>

### Third Iteration
In the third and final iteration, we'll remove a text-based response by the application and replace it with speech synthesis. We'll run the program as follows:

```shell
python3 record-transcribe-visualise-speak.py
```

Example output is shown in Figure 3.

![Figure 3. Third Iteration.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wjremg3np2vean7lkzlp.png)

<center>Figure 3. Third Iteration.</center>

In the code for all three iterations, we can call the OpenAI Whisper API instead of using the local Whisper installation. To do this, we'd uncomment these lines of code in the `transcribe_audio` function:

```python
            # transcript = openai.Audio.transcribe(
            #     model = "whisper-1",
            #     file = audio_file,
            #     language = "en"
            # )
```

and comment out this line of code:

```python
            transcript = model.transcribe(filename)
```

However, calling the OpenAI Whisper API would incur additional costs. The local Whisper installation already provides excellent results.

## Summary
In this article, we've seen that without creating vector embeddings, we've been able to access our data quite effectively using LangChain. However, our queries need to be more focused, and we need to understand the database schema before asking questions. Integrating a speech capability enables our applications to be more widely used.