Predicting & Visualizing Petroleum Production with MindsDB and Tableau
Tags: mindsdb, hacktoberfest, machinelearning, ai
Published: 2022-10-31 20:52:05

## **Introduction**

MindsDB is an open-source machine-learning tool that brings automated machine learning to your database. MindsDB offers predictive capabilities in your database. Tableau lets you visualize your data easily and intuitively. In this tutorial, we will be using MindsDB to predict the hourly electricity demand in the United States and visualize results in Tableau. To complete this tutorial, you are required to have a working MindsDB connection, either locally or via [cloud.mindsdb.com](http://cloud.mindsdb.com/). You can use this [guide](https://docs.mindsdb.com/connect/mindsdb_editor/) to connect to the MindsDB cloud.

## **Data Setup**

### **Connecting the data as a file**

Follow the steps below to upload a file to MindsDB Cloud.

- Log in to your [MindsDB Cloud](https://cloud.mindsdb.com/login) account to open the MindsDB Editor.
- Navigate to `Add data` the section by clicking the `Add data` button located in the top right corner.

![File Upload into MindsDB](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gcm8ab1ezgbn07855jo1.png)
- Choose the Files tab.

![Choose the Files tab](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5khtbnxh35jtncdad2lm.png)
- Choose the `Import File` option.
- Upload a file (`[national_nameplate_capacity.csv](https://www.kaggle.com/datasets/maurosrgiofigueira/fuel-output-capacity-brazil), name a table used to store the file data (here it is `petroleum_production` ), and click the `Save and Continue` button.

![File upload](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uj9ruzwlfcdd2ly79rsv.png)
Once you are done uploading, you can query the data directly with;

```sql
SELECT * FROM files.petroleum_production LIMIT 10;
```

The output would be:

![Table Query Output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iwujhwb4urx5uu486kdi.png)
## Understanding the Dataset

This dataset presents Brazilian petroleum products ouput capacity evolution, from 2011 to 2020 in each Brazilian refinery. Data is displayed in barrels. From the Brazilian National Petroleum Agency.

### Context

- **Refinery** -  They are 19 refinery in this dataset.
- **Year** - The year of this dataset ranges from 2011 to 2020.
- **Nameplate Capacity** - **Nameplate capacity**, also known as the **rated capacity**, **nominal capacity** , **installed capacity**, or **maximum effect**, is the intended full-load sustained output of a facility such as a power station, fuel plant and etc. (Source: [Wikipedia](https://en.wikipedia.org/wiki/Nameplate_capacity))

## **Creating the Predictor**

To being, let’s create a predictor that uses the date to predict the Petroleum Production for refineries. You can learn more about creating a predictor by checking [here](https://docs.mindsdb.com/sql/create/predictor/). You can predict a classification series model using the following syntax

```sql
CREATE PREDICTOR mindsdb.[predictor_name]
FROM [integration_name]
    (SELECT [sequential_column], [partition_column], [other_column], [target_column]
     FROM [table_name])
PREDICT [target_column]
```

- `CREATE PREDICTOR`: Creates a predictor with the name `predictor_name` in the `mindsdb` table.
- `FROM files`: Points to the table containing the data.
- `PREDICT`: Dictates the column to predict.

```sql
CREATE PREDICTOR mindsdb.petroleum_output
FROM files (SELECT refinery, Year, Nameplate_Capacity FROM petroleum_production)
PREDICT Nameplate_Capacity

```

On execution we get:

```
Query successfully completed
```

## **Status of a Predictor**

A predictor may take a couple of minutes for the training to complete. You can monitor the status of the predictor by using this SQL command:

```sql
SELECT status
FROM mindsdb.predictors
WHERE name='petroleum_output';
```

Your output should be:

```sql
+------------+
| status     |
+------------+
| complete   |
+------------+
```

## **Making Predictions**

Now that we have our Prediction Model, we can simply execute some simple SQL query statements to predict the target value based on the feature parameters:

```sql
SELECT Year, Nameplate_Capacity
FROM mindsdb.petroleum_output
JOIN files.petroleum_production
LIMIT 100;
```
![Predictions Query Output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b4wimbc7v24boe5wmkmv.png)
# Connecting MindsDB to Tableau

Tableau lets you visualize your data easily and intuitively. In this tutorial will use Tableau to create visualizations of our predictions.

### **How to Connect**

- First, create a new workbook in Tableau and open the *Connectors* tab in the *Connect to Data* window.
- Click on MySQL

![Connecting MindsDB to Tableau](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ck1dj2z1x344bn2w9lfu.png)

• Input “[cloud.mindsdb.com](http://cloud.mindsdb.com/)” for Server, “3306” for Port, “mindsdb” for Database “your mindsdb cloud email” for Username, “your mindsdb cloud password” for Password, and Sign in.
![Inputting Details for connection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cvmd9jznyzmzcby95elr.png)
Now you are connected and your page should look like this:

![Done connecting](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bqflnwd1j7euqjf2ddd2.png)
### **Visualizing our Data**

Before you can visualize predictions in Tableau, you must first choose a data source. And because the predictions in this article are generated using a SQL statement, you will need to create a custom SQL query in Tableau to generate the data source. To do this:

- First, select the `New Custom SQL`on the left side of the window and use the query below to generate the petroleum production for each year. You can preview the results or directly load the data into Tableau.

```sql
SELECT Year, Nameplate_Capacity
FROM mindsdb.petroleum_output
JOIN files.petroleum_production
```
![Custom SQL Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vzwcwbopq7ie5ibfcqed.png)
- Create an extract of the data under the connection heading at the top right of the window. You do this to facilitate data conversion to the appropriate data type. The extraction should take some time.
- Move to the Sheet tab on the bottom left and right-click on the `Nameplate_Capacity` and `Year` to convert their data types to Number(whole) and Date, respectively. Additionally, when right-clicking on the `Nameplate_Capacity` and `Date`, choose the option to convert it to a continuous measure.

![Changing Data Type](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ljb48fvuvmwjiy3idvqv.png)
![Changing Data Type](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rlhk9a3puwdiyasc8pk8.png)
![Changing Data Type](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w62cuib6sfbvpxqoebjz.png)
- Drag the `Nameplate_Capacity` measure to the row shelf and the `Year` dimension to the column shelf.
![Visualization in Tableau](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uh4jwv0507ksw2ddfskj.png)
- You can also switch the visualization also to line, area and etc.
![Final Visualization](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/37o7ogkkijfp55zspkpl.png)

## What’s Next?

Have fun while trying it out yourself!

- Star the **[MindsDB repository on GitHub](https://github.com/mindsdb/mindsdb)**.
- Sign up for a **[free MindsDB account](https://cloud.mindsdb.com/register?_ga=2.126448781.1875715573.1663498526-358045687.1658244666&_gl=1*1us5k78*_ga*MzU4MDQ1Njg3LjE2NTgyNDQ2NjY.*_ga_7LGFPGV6XV*MTY2MzYwNjcxNi4zNi4xLjE2NjM2MDY3MzUuMC4wLjA)**
- Engage with the MindsDB community on **[Slack](https://mindsdb.com/joincommunity?_ga=2.126448781.1875715573.1663498526-358045687.1658244666&_gl=1*1us5k78*_ga*MzU4MDQ1Njg3LjE2NTgyNDQ2NjY.*_ga_7LGFPGV6XV*MTY2MzYwNjcxNi4zNi4xLjE2NjM2MDY3MzUuMC4wLjA)** or **[GitHub](https://github.com/mindsdb/mindsdb/discussions)** to ask questions and share your ideas and thoughts.

Give a like or a comment if this tutorial was helpful