Alerting Dashboard for Tesla's Stock Price with QuestDB and Grafana
Tags: database, tutorial, devops
Published: 2021-03-11 14:12:26

# Introduction
There are many reasons why reacting to time series data is useful, and usually, the quicker you can respond to changes in this data, the better. The best tool for this job is easily a time series database, a type of database designed to write and read large amounts of measurements that change over time.

In this tutorial, you will learn how to read data from a REST API and stream it to QuestDB, an open-source time-series database. We will use Grafana to visualize the data and alerting to notify Slack on changes that interest us. We use Python to fetch data from the API and stream it to QuestDB and you can easily customize the scripts to check different stocks or even APIs.

# Configuration

## Prerequisites

Before getting started with the tutorial, you will need the following things:

* **[Docker desktop](https://www.docker.com/products/docker-desktop)** - we have created a  [GitHub repository](https://github.com/questdb/questdb-slack-grafana-alerts)  that will enable you to run Grafana and QuestDB in a Docker container. The project README also documents setup steps for Grafana, QuestDB, and Python.

* **[IexFinance Account](https://pypi.org/project/iexfinance/)** - we will use the IexFinance API for polling stock prices, note that a free account on IexFinance has a limit of 50,000 API calls per month.

* **[Slack workspace](https://slack.com/intl/en-au/help/articles/206845317-Create-a-Slack-workspace)** - to deliver alerts about Stock prices from Grafana, you'd need a Slack workspace with the ability to create incoming webhooks.

## Deploy QuestDB & Grafana containers using Docker
Firstly  [clone the repository from GitHub](https://github.com/bsmth/questdb-slack-grafana-alerts) :

```sh 
git clone git@github.com:questdb/questdb-slack-grafana-alerts.git
``` 

Running `docker-compose up` will bring up two containers that are networked together; Grafana is running on `localhost:3000` and QuestDB has a web console available on `localhost:9000` as well as a port open on `8812`, which can accept Postgres protocol.

To check if your QuestDB and Grafana containers are working, please visit the aforementioned URLs. Alternatively, you can check the status using `docker-compose ps` on the command line, which should show you the following output:

![Screen Shot 2021-03-06 at 12.25.45 am.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615470951236/b4kGHVu3w.png)

Running `docker-compose` will also provide Grafana with the default connection credentials to use the Postgres authentication. This means you can use QuestDB as a default data source in Grafana right away without manual configuration steps.

## Install Python Libraries

All the Python libraries required for this tutorial are listed in the requirements.txt file. Install the requirements using pip:

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

## Ingest mock data into QuestDB

We need some data in QuestDB to create some visualizations and alerts. We can use the IexFinance API to fetch stock prices and  [an additional script to generate dummy data](https://github.com/questdb/questdb-slack-grafana-alerts/blob/main/python/mock_stock_data_example.py). The IexFinance API has a cap of 50,000 requests per month in the free account, so our mock script can generate random prices so that we don't max out our trial during testing. To start ingesting mock data into QuestDB, run the script:

```sh 
cd python
python mock_stock_data_example.py
```

The script will automatically create a table `stock_prices`, and it will start ingesting mock data into this table that contains three columns:

1. **stock**-listed name of the stock, e.g., TSLA for Tesla. QuestDB has an optimized data type,  [symbol](https://questdb.io/docs/concept/symbol/) , for text columns that have repetitive values. Read more about that in QuestDB's official documentation.
2. **stockPrice** - price of the stock in USD in double.
3. **createdDateTime**- timestamp at which stockPrice was ingested in QuestDB.

In the following screenshot, you can see that the data ingested in QuestDB:

![Screen Shot 2021-03-06 at 1.52.21 am.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615470630096/UW2Qtu-3f.png)

## Configure the IexFinance API
Once you have tested the ingestion, you can start using the API with real data. Using this API, you can query stock prices in real-time. As mentioned earlier, there is a cap of 50000 free API calls per month, so make sure you don't cross that limit while on the free plan. To configure IexFinance API, follow these steps:
1. Create a free account on IexFinance.
2. Create an API token.
3. Press `Reveal Secret Token` and copy the `SECRET` token.
4. Create a new file .env in the ./python folder.
5. Paste the token in the .env file in the format →`IEX_TOKEN=Skwf93hD`.

![Screen Shot 2021-03-06 at 2.01.49 am.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615470552909/bJbS9Jdu-.png)

## Configure a Slack Incoming Webhook

Next, we need to create a Slack webhook for sending alert messages from Grafana:

1. Go to  [https://api.slack.com/apps?new_app=1](https://api.slack.com/apps?new_app=1)  
2. Name your Slack app *QuestDB Stock Price Alerts*
3. In Features and functionality choose incoming webhooks
4. Activate incoming webhooks and click `Add New Webhook` to Workspace
5. Select the channel to allow the app to post to and click `Allow`
6. Copy the Webhook URL which is in the following format → `https://hooks.slack.com/services/T123/B0123/2Fb...`

## Create a Notification channel in Grafana
Go to `localhost:3000` in your browser. To enable connectivity between Grafana and Slack for alerting, click `Add Channel` in the `Alerting > Notification channels` section as shown below:

![Screen Shot 2021-03-06 at 12.19.37 am.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615471205236/Nyx2BdCAJ.png)

Paste the Slack Incoming Webhook URL in the Url field while creating a new notification channel as shown below:

![Screen Shot 2021-03-06 at 12.21.27 am.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615471128657/nkB-_Zy51.png)

You can quickly test if the webhook is working fine by pressing the Test button on the screen above. This will trigger a notification from Grafana to be published on Slack. You can see an example notification below:

![Screen Shot 2021-03-06 at 12.22.52 am.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615471229464/N-XdTX4mm.png)

## Create a Grafana Panel & Setup the Alert

 [Set up a Grafana panel](https://grafana.com/docs/grafana/latest/panels/add-a-panel/)  that hosts the real-time graph of TSLA stock price using the following steps:
1. Navigate to **+** `Create and select Dashboard`
2. Click **+** `Add new panel`
3. In the panel, click the pencil icon or click `Edit SQL` and paste the following example query:

```sql
SELECT createdDatetime time, 
       round(avg(stockPrice),2) avgPrice
FROM (stock_prices timestamp(createdDatetime))
WHERE stock = 'TSLA'
SAMPLE BY 5s;
```

![Screen Shot 2021-03-06 at 3.46.12 am.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615471273761/L7Ka3Df7g.png)

After creating the Grafana panel with the query shown in the image above. Save the dashboard. To create an alert on `TSLA` stock price, perform the following steps:

1. Edit the panel in the dashboard.
2. Go to the `Alert` tab and name the alert Tesla Stock Price alert.
3. Set *Evaluate every 10 seconds for 30 seconds* (Evaluate every signifies how often the scheduler will evaluate the alert rule and for specifies how long the query needs to violate the thresholds before triggering alert notifications).
4. Set the conditions to *WHEN min() OF query(5-second Avg. of TSLA, 30s, now() IS BELOW 762*. In other words, the conditions for alerting are met if the minimum value of the query named 5-second Avg. of TSLA is below 762 in the last 30 seconds.
5. In the No Data & Error Handling section, use the defaults.
6. In `Notifications → Send` to, select the `notification channel` that we set up earlier named Stock Price Alerts.
7. Add the message The 5-second bucketed average of TSLA stock price has gone below 762 in the last 30 seconds.
8. Save the Panel.

You can see the steps in action below:

![Screen Shot 2021-03-06 at 4.20.30 am.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615471404569/uPzmyDQQr.png)

Once the condition is met, Grafana will trigger an alert and send a notification to Slack. The notification will be something like the screenshot below:

![Screen Shot 2021-03-06 at 4.33.08 am.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615471439748/QuzWtmV0_.png)

To understand the alert status changes more deeply, you can visit the State history. It will show you the timeline of transition from one status to another. You can see an example of state history below:

![Screen Shot 2021-03-06 at 4.38.14 am.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615471461149/V4YBQn576.png)

To learn more about building dashboards for time series data with Grafana, there's  [another tutorial on QuestDB's website](https://questdb.io/tutorial/2020/10/19/grafana/)  with a link to example data to try out more features in detail.

# Conclusion
In this tutorial, you learned how Grafana could confluence with QuestDB using the PostgreSQL endpoint. Using the data ingested from an API into QuestDB, you learned how to visualize that data in a Grafana dashboard and set up alerts based on some predefined conditions. You also learned how to publish alert messages to external tools like Slack. For more information on any of the topics covered in this tutorial, please visit QuestDB's official documentation.