Forem: Chandre Van Der Westhuizen

How to build your Developer Portfolio with MindsDB: The symbiotic relationship between developers and Opensource in 2024.

Chandre Van Der Westhuizen — Thu, 23 May 2024 15:07:17 +0000

What can you as a developer do to showcase your potential and expertise? One of the many challenges developers face is finding employment as most companies don’t recruit developers who do not have working experience.

Employers need to see proof of work that developers are capable and efficient. However, where does that leave freshly graduated students who have the qualifications but no experience and those who have been in the workforce but struggle with finding employment? Well Opensource has changed the game and is leading developers into a new era where they can gain experience working with different projects and enhance their skill set.

Open Source is a great way to hone in your developer skills and simultaneously build up a proof of work portfolio regardless of being employed or unemployed by contributing to projects. Developers over the world seek for projects that they can contribute to, especially GitHub users who look for ways to fill up those gray boxes with green on their GitHub profile 😉

From beginners to advanced coders that seek challenges, MindsDB offers developers a wide range of ways that they can contribute to opensource while building your Developer Portfolio.

What is MindsDB?

MindsDB is the platform for customizing AI from enterprise data, enabling smarter organizations. It allows developers to build AI/ML applications fast. MindsDB integrates with numerous data sources, including databases, vector stores, and applications, and popular AI/ML frameworks, including AutoML and LLMs. In summary, MindsDB brings data and AI together, enabling intuitive implementation and automation of AI systems.

You can gain experience on working with the following:

Automated Fine-Tuning
AI Agents
AI-Powered Data Retrieval
Data Enrichment
Predictive Analytics
In-Database Machine Learning
AI Workflow Automation

How to build your developer portfolio

In today’s time, it is essential for developers to have their own GitHub profile that they can use to contribute to an open-source project. As MindsDB is Open Source, you will have no issue with utilizing GitHub to hone your skills. MindsDB has its own GitHub repository where issues are listed for developers to solve. From no-code, low-code, and code technical contributions, developers can choose at what level they want to contribute according to their capabilities.

We will explore no-code, low-code to code contributions to help build your portfolio.

No-Code, Low-Code:
This is for every type of developer on any level of skill, meaning you will be able to do this with little to nothing experience as it involves no-code to low-code skills, which includes SQL.

Reporting bugs and fixing typo’s.

This kind of contribution is for beginners looking for a start in contributing to open source. You can report any bugs found in MindsDB’s platform on their github repository issues page as well as fix any typos on their documentation. This showcases your eye for detail and skill to find bugs for an application, something that is very critical for a company looking to enhance their product.

Technical writing

This involves writing a blog or creating a video tutorial on how to use MindsDB.
Here are a few ways you can contribute with writing:

Write a README file - for existing handlers- MindsDB handlers require a clear, comprehensive and user friendly guide on how to contribute to or use the handler
Write a Blog tutorial - Write about how to use the MindsDB product with it’s different features.
Write MindsDB Documentation - Contribute to MindsDB’s documentation and enhance the quality.

Technical writing skills and content creation are a great way to display your skill in being able to convey information and guides that help others understand a product. This is important for companies to have affable documentation that makes it easy for users to understand and trust using their platforms.

QA/UX Testing

With MindsDB, you can QA test their platform with their integrations and user interface and report on any issues found or provide feedback as to what is working. This involves running SQL syntax that performs tasks and tests whether the code as well as the platform with its handler are functional. This improves the quality of the product. Companies find this as an important skill set as it enhances user experience and adds value, as their product/release cannot go to market without it being signed off by QA.

High Code

This involves using the developers coding skills. Your skillset can include Python to build data, AI/ML and application integrations.

Fix Bugs/Issues

Developers are able to check for issues to fix on MindsDB’s Github Issues Page. The issues are marked with labels that indicate what you can work on, which you can find here. Fixing bugs showcases that you are a problem solver and capable of resolving issues. Companies find this capability very valuable as it has an impact on the quality of their product and user experience.

Building Integrations

Developers can showcase their Python skills by building integrations with existing Data handlers, AI/ML handlers and, Applications. You can also enhance existing handlers with building features for integrations that have not been implemented yet. This contribution demonstrates that developers are capable of assisting companies in Python-related tasks, especially in building integrations that will assist with improving the quality of their product by elevating systems to work together efficiently.

Build AI/ML apps

This is where people can exhibit their full potential as a developer. MindsDB allows developers to build AI/ML applications fast and also gives apps access to a wide range of data sources, LLMs, and other applications which enhances the capabilities of their applications. With MindsDB’s Python SDK and JavaScript SDK, you are able to build applications to your heart’s content with MindsDB as the back-end. Companies seek those who are able to build applications as creating one for their own company can increase brand value, generate more sales and revenue, and benefit customer experience which leads to better customer retention. You as a developer might also want to build an application that is fit to profit from and create your own business around.

Conclusion

Developers and Open Source are in symbiosis- just as contributors improve a project, they also are provided the opportunity to demonstrate their skills and creativity. As much as it is enjoyable to create cool trendy projects, developers would be wise to use it as an opportunity to design meaningful projects that can be applied to real-world use cases with impact.

MindsDB is a great way for you to interact with different data sources, model management, LLMs, AI integrations, and Automation. All these types of contributions can serve as proof of work for your developer portfolio, showcasing that you are a prudent developer capable of applying your skills and gaining experience.

For information on how to contribute to MindsDB, check out this Contribution guide.
If you have any questions or would like to share your thoughts, you can join MindsDB’s vibrant community of developers and AI enthusiasts on Slack. Explore their Github Repository and feel free to give it a star!

Twitter: @Chan_vdw
Github: chandrevdw31

AI-Powered Selection of Asset Management Companies using MindsDB and LlamaIndex

Chandre Van Der Westhuizen — Mon, 18 Sep 2023 14:21:40 +0000

Investors make use of Asset Management companies to efficiently manage their assets on their behalf without having any concerns, therefore choosing the correct company is very important which means investors need to query what they want to know in order to make a decision. Asset Management companies want to make sure that they can appeal to investors by providing them with information through their websites. Finding an asset management company that suits your needs and convincing investors to choose your asset management company to assist can be daunting for both parties. AI can alleviate this for both the investor and the asset management company by making sure the investor's questions are answered and the asset management company can answer questions without human interaction.

Asset Management companies rely on their website to provide information about their investment and financial services so that investors are willing to choose them, but how does an investor simplify the process of obtaining answers about an asset management company? This can be resolved with MindsDB's integration with LlamaIndex by creating a Q&A model.

MindsDB is an AI Automation platform for building AI/ML powered features and applications. It works by connecting any data source with any AI/ML model or framework and automating how real-time data flows between them. MindsDB is integrated with LlamaIndex, which makes use of its data framework for connecting custom data sources to large language models. LlamaIndex data ingestion allows you to connect to data sources like PDF’s, webpages, etc., provides data indexing and a query interface that takes input prompts from your data and provides knowledge-augmented responses, thus making it easy to Q&A over documents and webpages.

For this case, let's explore how to create a Q&A model for the Asset Management company Blackrock that can help investors obtain information seamlessly. This can be done using LlamaIndex, OpenAI as the LLM, and Blackrock’s About-Us webpage.

Blackrock is an asset management company that provides services to investors for investment, advisory and risk management solutions and makes a great example of how creating a Q&A model with MindsDB’s integration with LlamaIndex can be beneficial.

How to create a Q&A model using MindsDB and LlamaIndex.

MindsDB has a great SQL Editor in it's Cloud GUI for you to use. If you would like to sign up for a demo Cloud account, you can do so here.

To create a model, you will have to create the ML engine using the CREATE ML_ENGINE syntax in the SQL Editor.

CREATE ML_ENGINE engine_name
FROM llama_index

You can provide the name llamaindex to the ML_ENGINE.

On execution, you get:

We will make use of a file that has been uploaded into the GUI. If you would like to know how to upload a file, you can check the documentation here.. You can find a sample dataset here.

To upload the file, navigate to the 'Add Icon' on the MindsDB Gui and Upload File.

You can ensure that the data has been uploaded by selecting the file.

Run the following syntax:

SELECT * FROM files.about_blackrock LIMIT 10;

On execution, you get:

Once you have created the engine and successfully uploaded your file, you can create the model with the CREATE MODEL syntax.

CREATE MODEL model_name
FROM datasource
    (SELECT * FROM table_name)
PREDICT column_to_be_predicted
USING
  engine = 'llamaindex',
  index_class = 'vector_store_index',
  reader = 'reader_type',
  source_url_link = 'source_url_link',
  input_column = 'column_name',
  openai_api_key = 'your_api_key';

To ensure that the model is created based on the LlamaIndex engine, the USING clause is used to include the engine which is the ml_engine LlamaIndex, index_class is used to define the type of index class used, reader to define the type of reader for the data scrapping, source_url_link defines the URL link used to get the answers from, input_column defines the column used to prompt the model, and openai_api_key is defined with your OpenAI key to gain access to OpenAI models.

CREATE MODEL qa_blackrock
FROM files
    (SELECT * FROM about_blackrock)
PREDICT Answers
USING
  engine = 'llamaindex',
  index_class = 'GPTVectorStoreIndex',
  reader = 'DFReader',
  source_url_link = 'https://www.blackrock.com/za/individual/about-us',
  input_column = 'Questions',
  openai_api_key = 'your_api_key';

On execution, you get:

Where:

Expression	Description
`qa_blackrock`	The name provided to the model.
`engine`	The engine used, which is Llamaindex.
`qa_blackrock`	The name provided of the model.
`engine`	The engine name - here, LlamaIndex.
`index_class`	The vector store index used.
`reader`	The reader that reads the data/webpage.
`input_column`	prompt provided as a question to the model.
`openai_api_key`	API key of the LLM OpenAI.
`source_url_link`	The webpage link.
`input_column`	The column that stores prompt to the model.
`openai_api_key`	OpenAI API key.

The status of the model can be verified that it has trained successfully into a complete status by using the DESCRIBE syntax.

DESCRIBE qa_blackrock

On execution, you get:

Querying the Q&A model

The Q&A model has been successfully created and can now be prompted with questions with the SELECT statement.

SELECT Questions,Answers
FROM mindsdb.qa_blackrock
WHERE Questions = 'What is the best long term investment with minimal risks for private investors';

On execution, you get:

The answer provided is:

The best long-term investment with minimal risks for private investors is a diversified portfolio of low-cost index funds. These funds typically track a broad market index, such as the S&P 500, and provide exposure to a wide variety of publicly traded companies with minimal risk. Blackrock is one of the largest asset management companies in the world, and offers a range of index funds, exchange-traded funds, and other investment products for private investors.

You can also query batch predictions using the JOIN clause.

SELECT a.Questions,b.Answers
FROM mindsdb.qa_blackrock as b
JOIN files.about_blackrock as a;

On execution, you get:

The Q&A model has successfully retrieved information from the webpage and answered the questions prompted

How can Investors and Asset Management companies incorporate a Q&A model?

Not only can the investors create this Q&A model for themself for decision-making about multiple asset management companies, but also the asset management company can use it to make sure that possible clients are equipped to have their questions answered.

Asset Management companies can embed this Q&A model into their webpage which allows a reader/investor to prompt the model questions about the given webpage, therefore sparing time of having to read through the whole webpage and allowing investors to obtain the information they are looking for.

AI Workflows with MindsDB

Being able to have users come to your website and QA your webpage is great, but being notified about the questions your webpage is receiving is even better.

An AI workflow can be created by sending the questions prompting the Q&A model as slack notifications in an organization's Slack channel using MindsDB's application integration with Slack.

Connecting Slack to MindsDB can be done in quick easy steps:

Connecting an existing Slack Application to MindsDB OR
Create a new Slack application to connect to MindsDB

Slack-SDK is used to implement this handler, which is a Python Library offering a package for each of Slack’s APIs. As we are sending notifications to Slack,the Slack app token for authentication will be used to set up a Slack application to receive notifications. You can follow Method 1 in this guide to see how you can set up a Slack app and a Bot User OAuth Token.

Connecting Slack to MindsDB

Once you have created a Slack app or have an existing one, you can go ahead and connect the Slack application to MindsDB.

The CREATE DATABASE syntax would be used to establish a connection between Slack and MindsDB using the Bot User OAuth Token.

CREATE DATABASE slack_app
WITH
  ENGINE = 'slack',
  PARAMETERS = {
    "token": "xoxb-your_token"
    };

On execution, you get:

Query successfull

Setting up an AI Workflow.

As this use case is for a Q&A model being embedded into a website, the model's data source will be adjusted.

The question that will prompt the model will first be saved into a database, and then the model will be trained and provided an answer on the webpage.

Instead of the model, that needs to be embedded into the website, which syntax is this:

CREATE MODEL qa_blackrock
FROM files
    (SELECT * FROM about_blackrock)
PREDICT Answers
USING
  engine = 'blackrock_qa',
  index_class = 'GPTVectorStoreIndex',
  reader = 'DFReader',
  source_url_link = 'https://www.blackrock.com/za/individual/about-us',
  input_column = 'Questions',
  openai_api_key = 'your_api_key';

The syntax will rather look like this:

  CREATE MODEL qa_blackrock
FROM database
    (SELECT * FROM schema.table)
PREDICT Answers
USING
  engine = 'blackrock_qa',
  index_class = 'GPTVectorStoreIndex',
  reader = 'DFReader',
  source_url_link = 'https://www.blackrock.com/za/individual/about-us',
  input_column = 'Questions',
  openai_api_key = 'your_api_key';

For the sake of this example,we will continue to use files as the datasource.

Here is how to send messages to a Slack channel:

INSERT INTO slack_app.channels (channel, text)
VALUES("llama_test", "Here are questions from potential investors");

On execution, you get:

In the Slack channel you will see the message notification:

Now you can set up a job to receive notifications each time your webpage gets prompted a question. The CREATE JOB syntax will be used.

CREATE JOB investor_questions (

RETRAIN qa_blackrock
 USING
join_learn_process = true;

INSERT INTO slack_app.channels (channel, text)
VALUES("llama_test", "Here are questions from potential investors");

INSERT INTO slack_app.channels(channel, text)
  SELECT "llama_test" AS channel,
    concat('Question: ', a.Questions, chr(10), 'Answer: ', b.Answers) as text
  FROM mindsdb.qa_blackrock as b
  JOIN files.about_blackrock as a
  LIMIT 3;
)
EVERY 5 minutes;

You will receive notifications every 5 minutes if the Q&A model is prompted:

You have successfully set up a Q&A model that can be used to be embedded into your webpage and also have an AI workflow that sends notifications to your slack channel each time your model is prompted.

This creates an overview of the kind of information that investors are looking for. It can help you see what services you lack and can implement into your business model. You can take these questions and enhance your webpage’s information. Thus improving the quality of your webpage even further. With the power of Large Language Models, MindsDB makes it possible to serve your potential interested investors, improving the consumer experience and leveraging the power of AI workflows to improve the quality of your company’s presentation as a website.

You check out MindsDB by signing up for a demo account. If you would like to learn more you can visit MindsDB's Documentation. If you want to contribute to MindsDB, visit their Github repository and if you like it give it a star. MindsDB has a vibrant Slack Community and amazing team that provides technical support, if you would like to join you can sign up here.

Twitter:Chan_vdw
Github: chandrevdw31

How Developers should take advantage of MindsDB's Integration with OpenAI Chat GPT-3

Chandre Van Der Westhuizen — Wed, 15 Feb 2023 15:22:28 +0000

As a developer, you look for new ways to minimize the amount of work you have to do in order to get your desired results. Traditional ML development pipelines and methods can be helpful, but you are searching for faster ways to subsume NLP capabilities that require less manual development work. So how do you harness the full capabilities of NLP? By using MindsDB's integration with OpenAI Chat GPT-3 which allows you as a developer to unlock the power of the large pre-trained language models from OpenAI effectively.

Developers face the challenge of creating models that can answer questions with or without context and completing general prompts, and with the capacity of the NLP models it can be done smoothly.MindsDB's integration with OpenAI Chat GPT-3 and databases allows you to use the power of SQL to effortlessly extract insights from text data.

Here are a few ways you can leverage the NLP Capabilities with MindsDB:

You will now be able to create models that can help your clients in the e-commerce,social media,customer service industry by using sentiment analysis to detect the emotion behind their customers’ reviews or requests for a product or service easily.
Being able to create models that can answer questions can help alleviate the heavy workload the customer service industry experiences and assist e-commerce companies' websites to have a point of communication where their customers could ask questions and immediately receive answers. You can also create models for educational purposes that allow users to ask questions about learning material and receive an answer without having to engage with a teacher/lecturer.
Using text summarization cuts down the time spent trying to read paragraphs of information and provides the main points that are important and relevant to the person who needs to receive the information.

MindsDB was ranked 2nd Product of the Day on Product Hunt for their product launch for the MindsDB- OpenAI Chat GPT-3 integration

With the In-Database and NLP capabilities,MindsDB provides a great opportunity for developers to simplify their own workload in delivering services to their clients. You can check out the MindsDB integration with OpenAI Chat GPT-3's documentation about NLP here and check out their examples.

If you would like to find out more about MindsDB, you can visit their website.Feel free to bookmark MindsDB's Github repository and give them a star.Engage with the MindsDB community on Slack or GitHub to ask questions and share your ideas and thoughts.

Twitter : Chandre Van Der Westhuizen
Github page : Chandrevdw31

MindsDB Hacktoberfest2022: Win a Cash Prize

Chandre Van Der Westhuizen — Mon, 17 Oct 2022 19:24:13 +0000

Looking for an Opensource Github Repository to contribute to?

MindsDB Hacktoberfest provides opportunities for everyone to contribute and stand a chance to win a cash prize.

For this year's MindsDB Hacktoberfest there are 3 categories to participate in, one is submitting an idea for MindsDB Integrations. This is a great and easy way for you to participate in Hacktoberfest!

How to enter and win a cash prize:

Share a well-described idea of a new MindsDB integration on MindsDB's Github Repository.
Invite people you know to upvote your idea.
The top 3 authors, whose ideas will get the highest number of upvotes, are the winners. If multiple ideas have the same number of votes, the first one posted wins.
You can submit as many ideas as you like. However, we will not sum up upvotes from multiple issues from a single author. We’ll take just one that has the highest number of votes.

The top 3 authors will receive a cash prize:

The first place gets $500 cash prize, plus MindsDB t-shirt, plus audio gadget.
The second place gets $300 cash prize, plus MindsDB t-shirt, plus audio gadget.
The third place gets $200 cash prize, plus MindsDB t-shirt, plus audio gadget.

See Rules

Rewards will be paid as one-time GitHub sponsorship. Each winner can choose one of the gadgets (JBL Earbuds or Rugged Outdoor Speaker)

We will announce the winners on our Community Slack during the first week of November.

Hacktoberfest 2022 with MindsDB

Chandre Van Der Westhuizen — Fri, 07 Oct 2022 15:42:47 +0000

It’s time to Hack!

Join MindsDB’s Hacktoberfestivities by making Open Source Contributions and get a chance of winning a Razer Blade 15 Laptop, cool audio gadgets, T-shirts and a cash prize.

There are several categories of open issues that are ideal for first-time developers, low-code, and no-code contributors for you to check out on MindsDBs page.

Categories:

Integration ideas
Share a well-described idea of a new MindsDB integration. The authors of top upvoted ideas will get a T-shirt, cool audio gadgets, and a cash prize:

1st place: $500
2nd place: $300
3rd place: $200

Code,Low & No-code
Make as many contributions of your choice to win a high-end Razer Blade 15 Laptop or one of 10 cool audio gadgets:

Contribute to Docs
Share Social Media Posts
Report and/or fix a Bug
Make a QA/UX Test
Develop an Architectural Diagram
Write a Tutorial
Submit a UX Design
Build a Data-Source Integration
Make a Video Tutorial
Build ML Framework Integration

Video Tutorials
Make as many contributions of your choice to win a high-end Razer Blade 15 Laptop or one of 10 cool audio gadgets! Make video tutorials of using MindsDB and invite everyone to watch them on YouTube. The authors of the most popular videos will get a T-shirt, cool audio gadgets, and a cash prize::

1st place: $1000
2nd place: $500
3rd place: $300

For every 10 points, you will receive one entry into the draw for the awesome Razer Blade 15 Laptop powered by the top NVIDIA GeForce RTX 3080 Ti GPU, 14-core Intel i9 CPU and more.

You can also win one of the 10 gadgets of your choice:
JBL Earbuds or Rugged Outdoor Speaker.

To participate, you need to have your GitHub PRs merged and complete the form with your details.

Please complete the form below to participate in the prize draw. You should complete the form just once with all your contributions listed. You will be able to edit your responses after submission. The form can be found here.

Entries will close at midnight (PST) Monday, 31st of October with the winner announced on the first week of November.

Predicting Employee Productivity using Machine Learning with MindsDB

Chandre Van Der Westhuizen — Thu, 21 Jul 2022 09:31:00 +0000

Community Author: Chandre Van Der Westhuizen

The garment industry is an essential exemplar of industrial globalization. It is of utmost importance for the decision makers in the garment industry to analyze,track and predict the productivity performance of employees as the companies rely on the production and delivery performance of their factory workers to satisfy the massive global demands for garment products. MindsDB's in-database machine learning capabilities provides the convenience of predicting productivity to assist employers with employee management.

This tutorial will explore how employers can make predictions on the productivity of employees producing garments using a machine learning regression predictive model. You will get to learn how to connect your database directly to MindsDB, create and train models and make predictions.

Pre-requisites

Access to MindsDB Cloud or local deployment via docker or pip installation.
Dataset: You can find the dataset on Kaggle

Connecting your Database to MindsDB.

The first step will be to establish a database connection via MindsDB's GUI which has a SQL Editor that makes it easy to execute queries. MindsDB enables connections to your favorite databases, data warehouses and data lakes via the CREATE DATABASE syntax.

First, we need to connect MindsDB to the database where the Employee producing garments data is stored:

Access MindsDB GUI on either cloud or the URL 127.0.0.1:47334/
On the default page, select the button Add Data or alternatively select the plug icon on the left sidebar.
The 'Select your data source' page will populate for you to choose your database type. MindsDB has a list of databases that can be chosen. For this tutorial we will be selecting the postgres database button.
Once you have selected the database type,the page will automatically navigate to the SQL Editor where the syntax to create a database connection will automatically populate for you to enter the required parameters.

The required parameters are:

CREATE DATABASE display_name --- display name for database.
WITH ENGINE = "postgres", --- name of the mindsdb handler
PARAMETERS = {
- "user": " ", --- Your database user.
- "password": " ", --- Your password.
- "host": " ", --- host, it can be an ip or an url.
- "port": "5432", --- common port is 5432.
- "database": " " --- The name of your database *optional. }

Select the Run button or Shift+Enter to execute the syntax. Once the Database connection is created the console will display a message 'Query successfully completed'.

Please note that some database connections require running a Ngrok tunnel to establish a connection.
Run the ngrok command in a terminal:
ngrok tcp [db-port]
for example,if your port number is 5433 you will see a similar output:
Session Status                online
Account                       myaccount (Plan: Free)
Version                       2.3.40
Region                        United States (us)
Web Interface                 http://127.0.0.1:4040
Forwarding                    tcp://6.tcp.ngrok.io:14789 -> localhost:5433
The forwarded address information will be required when connecting to MindsDB's GUI. Select and copy the 'Forwarding' information, in this case it is 6.tcp.ngrok.io:14789, where 6.tcp.ngrok.io will be used for the host parameter and 14789 as the port number.

Once the database integration is successful we can query the table from the database to ensure the data pulls through on MindsDB.

You can run queries directly on your database. The below is an example of the syntax used:

SELECT * 
FROM example_db.datasource.table_name
LIMIT 10;

We will run the query to preview the data:

SELECT * 
FROM mindsdb_predictions.data.garments
LIMIT 10;

MindsDB also has a functionality to Upload a file and SELECT data from the file to create a machine learning model.

Understanding the data

+----------+----------+------------+----------+------+-----------------------+-------+--------+-----------+-----------+-----------+----------+--------------------+---------------+---------------------+
| date     | quarter  | department | day      | team | targeted_productivity | smv   | wip    | over_time | incentive | idle_time | idle_men | no_of_style_change | no_of_workers | actual_productivity |
+----------+----------+------------+----------+------+-----------------------+-------+--------+-----------+-----------+-----------+----------+--------------------+---------------+---------------------+
| 1/1/2015 | Quarter1 | sweing     | Thursday |   12 |                   0.8 | 11.41 |  968.0 |      3660 |        50 |       0.0 |        0 |                  0 |          30.5 |         0.800570492 |
| 1/1/2015 | Quarter1 | sweing     | Thursday |    6 |                   0.8 |  25.9 | 1170.0 |      1920 |        50 |       0.0 |        0 |                  0 |          56.0 |         0.800381944 |
| 1/1/2015 | Quarter1 | sweing     | Thursday |    7 |                   0.8 |  25.9 |  984.0 |      6720 |        38 |       0.0 |        0 |                  0 |          56.0 |            0.800125 |
| 1/1/2015 | Quarter1 | sweing     | Thursday |    3 |                  0.75 | 28.08 |  795.0 |      6900 |        45 |       0.0 |        0 |                  0 |          57.5 |         0.753683478 |
| 1/1/2015 | Quarter1 | sweing     | Thursday |    2 |                  0.75 | 19.87 |  733.0 |      6000 |        34 |       0.0 |        0 |                  0 |          55.0 |         0.753097531 |
+----------+----------+------------+----------+------+-----------------------+-------+--------+-----------+-----------+-----------+----------+--------------------+---------------+---------------------+

Where:

Column	Description	Data Type	Usage
`date`	Date in MM-DD-YYYY	`integer`	Feature
`quarter`	A portion of the month. A month was divided into four quarters	`character varying`	Feature
`department`	Associated department with the instance	`integer`	Feature
`day`	Day of the Week	`character varying`	Feature
`team`	Associated team number with the instance	`integer`	Feature
`targeted_productivity`	Targeted productivity set by the Authority for each team for each day.	`integer`	Feature
`smv`	Standard Minute Value, it is the allocated time for a task	`integer`	Feature
`wip`	Work in progress. Includes the number of unfinished items for products	`integer`	Feature
`over_time`	Represents the amount of overtime by each team in minutes	`integer`	Feature
`incentive`	Represents the amount of financial incentive (in BDT) that enables or motivates a particular course of action.	`integer`	Feature
`idle_time`	The amount of time when the production was interrupted due to several reasons	`integer`	Feature
`idle_men`	The number of workers who were idle due to production interruption	`integer`	Feature
`no_of_style_change`	Number of changes in the style of a particular product	`integer`	Feature
`no_of_workers`	Number of workers in each team	`integer`	Feature
`actual_productivity`	The actual % of productivity that was delivered by the workers. It ranges from 0-1	`integer`	Label

A label is the thing we're predicting—the y variable in simple linear regression.
A feature is an input variable—the x variable in simple linear regression.

Creating a Regression Machine Learning Predictive model.

To make predictions, we will create a machine learning model using the CREATE PREDICTOR statement and make a Regression prediction.
In the syntax we will specify which columns/ features to use for training and what column the model should learn to predict as our target/label. In this case we will select all the data to train with and use the column 'Target' as our target variable/label that we want to predict.

In the SQL Editor, the below syntax will be executed:

CREATE PREDICTOR garments_productivity
FROM mindsdb_predictions
(SELECT * FROM data.garments)
PREDICT actual_productivity;

Select the Run button or Shift+Enter to execute the syntax. If there are no issues with creating the query you will receive the message in the console 'Query successfully completed'.

The model can take a while to train. The status of the model's training can be checked with the below syntax:

SELECT * FROM mindsdb.predictors WHERE name='garments_productivity'

As soon as the model is created the status should show training.Once the model is finished training,the status will show complete. Below you can see that the model has an accuracy of 84%.

The Predictor Status Must be 'complete' Before Making a Prediction

Making a Regression Prediction

When the status of the predictor shows complete, we can go ahead and make a prediction. Predictions are made using the SELECT statement by querying the predictor as if it is a table. We will provide the features with parameters to get a result.

The below syntax will be used to make a prediction:

SELECT actual_productivity, actual_productivity_explain FROM garments_productivity WHERE date='1/1/2015' AND quarter='Quarter1' AND department='sweing' AND day='Thursday' 
AND team=8 AND targeted_productivity=0.8 AND smv=26.16 AND wip=1108 
AND over_time=7080 AND incentive=98 AND idle_time=0 AND idle_men=0 
AND no_of_style_change=0 AND no_of_workers=59;

Run the above syntax and you will see the below results:

The actual productivity has a value from 0-1.The model predicted that with these parameters provided the result for the productivity of the employees will be 0.93 and by using Target_explain we can see the confidence levels which shows that the confidence_lower_bound is 0.8556 and confidence_higher_bound is 1. This means that the garment company can expect to have the productivity of the employees to be between 0.8556 and 1.

Making Batch Predictions.

The model can also make batch predictions using the JOIN clause:

SELECT a.actual_productivity as productivity, b.actual_productivity as predicted_productivity, 
a.date, a.quarter, a.department, a.day,
a.team, a.targeted_productivity, a.smv, a.wip,
a.over_time, a.incentive, a.idle_time, a.idle_men,
a.no_of_style_change, a.no_of_workers
FROM mindsdb_predictions.data.garments as a
JOIN mindsdb.garments_productivity as b limit 5;

Results:

+--------------+------------------------+----------+----------+------------+----------+------+-----------------------+-------+--------+-----------+-----------+-----------+----------+--------------------+---------------+
| productivity | predicted_productivity | date     | quarter  | department | day      | team | targeted_productivity | smv   | wip    | over_time | incentive | idle_time | idle_men | no_of_style_change | no_of_workers |
+--------------+------------------------+----------+----------+------------+----------+------+-----------------------+-------+--------+-----------+-----------+-----------+----------+--------------------+---------------+
| 0.800570492  | 0.7820790218694679     | 1/1/2015 | Quarter1 | sweing     | Thursday | 12   | 0.8                   | 11.41 | 968.0  | 3660      | 50        | 0.0       | 0        | 0                  | 30.5          |
| 0.800381944  | 0.8169200021797305     | 1/1/2015 | Quarter1 | sweing     | Thursday | 6    | 0.8                   | 25.9  | 1170.0 | 1920      | 50        | 0.0       | 0        | 0                  | 56.0          |
| 0.800125     | 0.7619499385051534     | 1/1/2015 | Quarter1 | sweing     | Thursday | 7    | 0.8                   | 25.9  | 984.0  | 6720      | 38        | 0.0       | 0        | 0                  | 56.0          |
| 0.753683478  | 0.762949004815915      | 1/1/2015 | Quarter1 | sweing     | Thursday | 3    | 0.75                  | 28.08 | 795.0  | 6900      | 45        | 0.0       | 0        | 0                  | 57.5          |
| 0.753097531  | 0.752386052621824      | 1/1/2015 | Quarter1 | sweing     | Thursday | 2    | 0.75                  | 19.87 | 733.0  | 6000      | 34        | 0.0       | 0        | 0                  | 55.0          |
+--------------+------------------------+----------+----------+------------+----------+------+-----------------------+-------+--------+-----------+-----------+-----------+----------+--------------------+---------------+

Want to try it out for yourself? Sign up for a free MindsDB account and join our community!
Engage with MindsDB community on Slack or Github to ask questions, share and express ideas and thoughts!

Make sure to check out the official website of MindsDB. For more check out other tutorials and MindsDB documentation.

MindsDB: Predicting Supply Chain Demand with Machine Learning using SQL

Chandre Van Der Westhuizen — Mon, 27 Jun 2022 18:16:52 +0000

Community Author: Chandre Van Der Westhuizen

The Supply Chain industry requires forecasting to plan for demand. This is critical to ensure that requirements for demand are met so that the business can run smoothly and keep shortages to a minimum. MindsDB's machine learning models can help predict the demand to uphold a company's service delivery.

This tutorial will be exploring a Brazilian logistics company's dataset that has been collected during 60 days and predict the number of total orders. We will create and train a Regression predictive model and make predictions.

Pre-requisites

Access to MindsDB Cloud or local deployment via docker or pip installation.
Dataset: You can find the Supply chain and Demand dataset on the UCI Machine Learning Repository.

Connecting your Database to MindsDB.

To establish a database connection we will access MindsDB's GUI. MindsDB has a SQL Editor on Cloud and local via the URL 127.0.0.1:47334/. MindsDB enables connections to your favorite databases, data warehouses, data lakes, via the CREATE DATABASE syntax.

First, we need to connect MindsDB to the database where the Supply Chain data is stored:

Access MindsDB GUI on either cloud or the URL 127.0.0.1:47334/
On the default page, select the button Add Data or alternatively select the plug icon on the left sidebar.
The 'Select your data source' page will populate for you to choose your database type. For this tutorial we will be selecting the postgres database button.

Once you have selected the database type,the page will automatically navigate to the SQL Editor where the syntax to create a database connection will automatically populate for you to enter the required parameters.

The required parameters are:

CREATE DATABASE display_name --- display name for database.
WITH ENGINE = "postgres", --- name of the mindsdb handler
PARAMETERS = {
- "user": " ", --- Your database user.
- "password": " ", --- Your password.
- "host": " ", --- host, it can be an ip or an url.
- "port": "5432", --- common port is 5432.
- "database": " " --- The name of your database *optional. }

Select the Run button or Shift+Enter to execute the syntax. Once the Database connection is created the console will display a message 'Query successfully completed'.

Please note that some database connections require running a Ngrok tunnel to establish a connection.
Run the ngrok command in a terminal:
ngrok tcp [db-port]
for example,if your port number is 5433 you will see a similar output:
Session Status                online
Account                       myaccount (Plan: Free)
Version                       2.3.40
Region                        United States (us)
Web Interface                 http://127.0.0.1:4040
Forwarding                    tcp://6.tcp.ngrok.io:14789 -> localhost:5433
The forwarded address information will be required when connecting to MindsDB's GUI. Select and copy the 'Forwarding' information, in this case it is 6.tcp.ngrok.io:14789, where 6.tcp.ngrok.io will be used for the host parameter and 14789 as the port number.

Once the database integration is successful we can query the table from the database to ensure the data pulls through on MindsDB.

You can run queries directly on your database. The below is an example of the syntax used:

SELECT * 
FROM example_db.datasource.table_name
LIMIT 10;

We will run the query to preview the data:

SELECT * 
FROM mindsdb_predictions.data.demand
LIMIT 10;

Understanding the data.

+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+---------+
| Week_of_the_month | Day_of_the_week | Non_urgent_order | Urgent_order | Order_type_A | Order_type_B | Order_type_C | Fiscal_sector_orders | Orders_from_the_traffic_controller_sector | Banking_orders1 | Banking_orders2 | Banking_orders_3 | Target  |
+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+---------+
|                 2 |               2 |          171.297 |      127.667 |       41.542 |      113.294 |      162.284 |               18.156 |                                     49971 |           33703 |           69054 |            18423 |  317.12 |
|                 2 |               3 |           90.532 |      113.526 |       37.679 |       56.618 |       116.22 |                6.459 |                                     48534 |           19646 |           16411 |            20257 | 210.517 |
|                 2 |               4 |          110.925 |        96.36 |       30.792 |       50.704 |      125.868 |                 79.0 |                                     52042 |            8773 |           47522 |            24966 | 207.364 |
|                 2 |               5 |          144.124 |      118.919 |       43.304 |       66.371 |      153.368 |                  0.0 |                                     46573 |           33597 |           48269 |            20973 | 263.043 |
|                 2 |               6 |          119.379 |       113.87 |       38.584 |       85.961 |      124.413 |               15.709 |                                     35033 |           26278 |           56665 |            18502 | 248.958 |
+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+---------+

Where:

Column	Description	Data Type	Usage
`Week_of_the_month`	The how manyth week of the month it is `[1,2,3,4,5]`	`integer`	Feature
`Day_of_the_week`	What day of the week it is, Monday to Friday `[1,2,3,4,5,6]`	`integer`	Feature
`Non_urgent_order`	Orders that are not urgent/emergency.	`integer`	Feature
`Urgent_order`	Orders that are an emergency to deliver.	`integer`	Feature
`Order_type_A`	Orders categorized under type A.	`integer`	Feature
`Order_type_B`	Orders categorized under type B.	`integer`	Feature
`Order_type_C`	Orders categorized under type A.	`integer`	Feature
`Fiscal_sector_orders`	Orders that are for the Fiscal sector.	`integer`	Feature
`Orders_from_the_traffic_controller_sector`	Orders from the traffic controller sector	`integer`	Feature
`Banking_orders1`	Orders categorized under Banking_orders1.	`integer`	Feature
`Banking_orders2`	Orders categorized under Banking_orders2.	`integer`	Feature
`Banking_orders3`	Orders categorized under Banking_orders3.	`integer`	Feature
`Target`	The total orders in demand.	`integer`	Label

A label is the thing we're predicting—the y variable in simple linear regression.
A feature is an input variable—the x variable in simple linear regression.

Creating a Machine Learning Predictive model.

In the SQL Editor, the below syntax will be executed:

CREATE PREDICTOR supply_demand
FROM mindsdb_predictions
(SELECT * FROM data.demand)
PREDICT Target;

Select the Run button or Shift+Enter to execute the syntax. If there are no issues with creating the query you will receive the message in the console 'Query successfully completed'.

The model can take a while to train. The status of the model's training can be checked with the below syntax:

SELECT * FROM mindsdb.predictors WHERE name='supply_demand'

Once the model is finished training,the status will show complete. Below you can see that the model has an accuracy of 99%.

The Predictor Status Must be 'complete' Before Making a Prediction

Making a Prediction

The below syntax will be used to make a prediction:

SELECT Target, Target_explain FROM supply_demand WHERE Week_of_the_month=1 AND Day_of_the_week=5 
AND Non_urgent_order=128.633 AND Urgent_order=96.042
AND Order_type_A=38.058 AND Order_type_B=56.037 AND Order_type_C=130.580 
AND Fiscal_sector_orders=0 AND Orders_from_the_traffic_controller_sector=40419 
AND Banking_orders1=21399 AND Banking_orders2=89461 AND Banking_orders_3=7679;

Run the above syntax and you will see the below results:

The model predicted that with these parameters provided the result for the total orders that will be demanded is 235.725 and by using Target_explain we can see the confidence levels which shows that the confidence_lower_bound is 223.9888 and confidence_higher_bound is 247.463. This lays out that the logistics company should plan for a demand of 224-248 orders.

Making Batch Predictions.

The model can also make batch predictions using the JOIN clause:

SELECT a.Target as total_orders, b.Target as predicted_orders,  a.Week_of_the_month, a.Day_of_the_week 
,a.Non_urgent_order, a.Urgent_order, a.Order_type_A, a.Order_type_B, a.Order_type_C, 
a.Fiscal_sector_orders, 
a.Orders_from_the_traffic_controller_sector, a.Banking_orders1, a.Banking_orders2, a.Banking_orders_3
FROM mindsdb_predictions.data.demand as a
JOIN mindsdb.supply_demand as b limit 5;

+--------------+--------------------+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+
| total_orders | predicted_orders   | Week_of_the_month | Day_of_the_week | Non_urgent_order | Urgent_order | Order_type_A | Order_type_B | Order_type_C | Fiscal_sector_orders | Orders_from_the_traffic_controller_sector | Banking_orders1 | Banking_orders2 | Banking_orders_3 |
+--------------+--------------------+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+
| 317.12       | 322.1392172468534  | 2                 | 2               | 171.297          | 127.667      | 41.542       | 113.294      | 162.284      | 18.156               | 49971                                     | 33703           | 69054           | 18423            |
| 210.517      | 206.24671425495697 | 2                 | 3               | 90.532           | 113.526      | 37.679       | 56.618       | 116.22       | 6.459                | 48534                                     | 19646           | 16411           | 20257            |
| 207.364      | 202.6728809487407  | 2                 | 4               | 110.925          | 96.36        | 30.792       | 50.704       | 125.868      | 79.0                 | 52042                                     | 8773            | 47522           | 24966            |
| 263.043      | 261.41091237022783 | 2                 | 5               | 144.124          | 118.919      | 43.304       | 66.371       | 153.368      | 0.0                  | 46573                                     | 33597           | 48269           | 20973            |
| 248.958      | 250.0993082475708  | 2                 | 6               | 119.379          | 113.87       | 38.584       | 85.961       | 124.413      | 15.709               | 35033                                     | 26278           | 56665           | 18502            |
+--------------+--------------------+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+

Want to try it out for yourself? Sign up for a free MindsDB account and join our community!
Engage with MindsDB community on Slack or Github to ask questions, share and express ideas and thoughts!

Make sure to check out the official website of MindsDB. For more check out other tutorials and MindsDB documentation.

MindsDB: Your introduction to creating machine learning predictive models.

Chandre Van Der Westhuizen — Wed, 19 Jan 2022 13:25:23 +0000

Community Author: Chandre Tosca Van Der Westhuizen

Making Data Science Easier-

ML Classification has gotten easier with MindsDB. Has the data science world been missing out?

Many think AI/machine learning is complicated. Turns out not all frameworks are that complex.

MindsDB's Classification model makes it easy to predict outcomes and can add value to many industries. We will focus on something as simple as mushrooms to showcase the ingenuity of this In-Database Machine Learning platform.

Mushrooms

Dataset: Mushrooms

Mushrooms are a fleshy sporocarp of fungi which can either be edible or poisonous. It's usage dates back centuries with ancient Greek, Chinese and African cultures. They can have high nutritional value and medicinal properties which provides great health benefits. On the other side,some of these fungi can be toxic and consuming the wrong mushroom can have deadly consequences. It is important for industries across the world, like the food and health sector, to identify which type of mushrooms are edible and which are poisonous.

We will explore how MindsDB's machine learning predictive model can make it easier classifying mushrooms and predicting which is safe to consume and which can make you ill.

Pre-requisites

For this tutorial, ensure you have access to docker Docker. A docker-compose.yml file will be provided to get you started quickly.

To ensure you can complete all the steps, make sure you have access to the following tools:

A MindsDB instance. Check out the installation guide for Docker or PyPi. You can also use MindsDB Cloud.
Downloaded the dataset. You can get it from Kaggle
Optional: Access to ngrok. You can check the installation details at the ngrok website.
Optional- Recommended : Visual Studio Code

Docker

Create a new project directory named e.g. mindsdb-tutorial
Inside your project directory: 2.1 Create a new file named docker-compose.yml.

Open the docker-compose.yml file with any text-editor, copy the following and save.


version: '3.5'

services:
  postgres:
    container_name: mindsdb_postgres_mushrooms
    image: postgres
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: changeme
      POSTGRES_DB: Mushrooms
      PGDATA: /data/postgres
    volumes:
    # persist data storage in a docker container
      - mindsdb_postgres_mushrooms:/data/postgres
    ports:
      - "5433:5432"
    network_mode: bridge
    # restart: none


volumes:
    mindsdb_postgres_mushrooms:

This compose stack features both a Postgres database running on ports 5433 and 5432 respectively. We will install Python libraries and use python code in a Jupyter Notebook to create a Postgress database and load data into it in Visual Studio.
For the database to connect to MindsDB we will use a tunneling program ngrok to allow the remote database connection without exposing your public IP.

Data will be loaded from a csv file using the import tool.
Docker Volumes are used for persistent storage of data.

To run, open a terminal in the project folder and run docker-compose up -d .
If you do not have the Docker images locally, it will first download them from docker hub and might take.

Creating a database

The goal of creating a database in Visual Studio is to simplify the method used. We have two options- to create a python file with the relevant code in it or create a Jupyter Notebook. For the purpose of this exercise we are going to use Jupyter Notebook which will help execute the code.

We will use the libraries sqlalchemy and pandas to create the database and load data into tables.

To install the required Python libraries In a terminal, run

pip3 install sqlalchemy pandas

Please change the pip command if required for your system i.e. pip or python3 -m pip

To install psycopg, run

pip3 install psycopg

In Visual studio, create a new file with the extension '.ipynb' to create a Jupyter Notebook. Alternatively you can open a text editor and save the following code as a python file.


from sqlalchemy import create_engine
import pandas as pd
# engine = create_engine('sqlite:///college.db', echo = True)
username='postgres'
password='changeme'
db='Mushrooms'
tablename='mushrooms'

# database connection
engine = create_engine(
    f"postgresql+psycopg2://{username}:{password}@localhost:5433/{db}",
    isolation_level="SERIALIZABLE",
)

file_url = './mushrooms.csv'
df = pd.read_csv(file_url)

df.columns

Drop rows with nul values and and store data to table.


df.dropna(inplace=True)

df.to_sql(tablename,con=engine, if_exists='replace', index=False)

If you are using a python file,open a terminal and execute python3 to run the code. If you are using Jupytor Notebook you can run the code by selecting the play buttons next to the cell blocks. Select python3 when prompted.

The dataset consists of the following information:

Attribute Information: (classes: edible=e, poisonous=p)

*cap-shape: bell=b,conical=c,convex=x,flat=f, knobbed=k,sunken=s
*cap-surface: fibrous=f,grooves=g,scaly=y,smooth=s
*cap-color: brown=n,buff=b,cinnamon=c,gray=g,green=r,pink=p,purple=u,red=e,white=w,yellow=y
*bruises: bruises=t,no=f
*odor: almond=a,anise=l,creosote=c,fishy=y,foul=f,musty=m,none=n,pungent=p,spicy=s
*gill-attachment: attached=a,descending=d,free=f,notched=n
*gill-spacing: close=c,crowded=w,distant=d
*gill-size: broad=b,narrow=n
*gill-color: black=k,brown=n,buff=b,chocolate=h,gray=g, green=r,orange=o,pink=p,purple=u,red=e,white=w,yellow=y
*stalk-shape: enlarging=e,tapering=t
*stalk-root: bulbous=b,club=c,cup=u,equal=e,rhizomorphs=z,rooted=r,missing=?
*stalk-surface-above-ring: fibrous=f,scaly=y,silky=k,smooth=s
*stalk-surface-below-ring: fibrous=f,scaly=y,silky=k,smooth=s
*stalk-color-above-ring: brown=n,buff=b,cinnamon=c,gray=g,orange=o,pink=p,red=e,white=w,yellow=y
*stalk-color-below-ring: brown=n,buff=b,cinnamon=c,gray=g,orange=o,pink=p,red=e,white=w,yellow=y
*veil-type: partial=p,universal=u
*veil-color: brown=n,orange=o,white=w,yellow=y
*ring-number: none=n,one=o,two=t
*ring-type: cobwebby=c,evanescent=e,flaring=f,large=l,none=n,pendant=p,sheathing=s,zone=z
*spore-print-color: black=k,brown=n,buff=b,chocolate=h,green=r,orange=o,purple=u,white=w,yellow=y
*population: abundant=a,clustered=c,numerous=n,scattered=s,several=v,solitary=y
*habitat: grasses=g,leaves=l,meadows=m,paths=p,urban=u,waste=w,woods=d

Accessing the Postgres Database

To access the postgres database, add the extension Postgres Management Tool by Chris Kolkman v1.1.19 in Visual studio.

A shortcut is to select Ctrl+Shift+X and search for the management tool.

Connecting to the database

On the sidebar of visual studio, select the icon for the Postgress explorer.
Select the + button by the option POSTGRESQL EXPLORER:POSTGRESQL to add a database connection.
Add the hostname of the database. >For this example, 127.0.0.1 was used.
Enter the PostgreSQL user to authenticate. >For this example it is postgres.
Enter the password of the PostgreSQL user.
Add the port number. >For this example 5433 was used.
Select the type of connection to use- use Secure Connection or Standard Connection. >For this example we used Standard Connection.
Add the name of the Database to connect to. >For this example Mushrooms was used.
Add the connection name.

You can test the database by navigating to the Postgres Management Tool, selecting the hostname and database dropdowns , then right click on the table name and choose between the select options to return the values.

Connecting to MindsDB Cloud

We will now connect our database to MindsDB Cloud.

First, we will run the ngrok command to establish a connection. Open a terminal and run the following command.

ngrok tcp [db-port]

For this example the port number used is 5433.

You should see a similar output:

Session Status                online
Account                       myaccount (Plan: Free)
Version                       2.3.40
Region                        United States (us)
Web Interface                 http://127.0.0.1:4040
Forwarding                    tcp://6.tcp.ngrok.io:14789 -> localhost:5433

The forwarded address information will be required when connecting to MindsDB's GUI. Select and copy the 'Forwarding' information, in this case it is 6.tcp.ngrok.io:14789, and keep this information for later.

For the next steps we will log into the MindsDB cloud interface. MindsDB Cloud is perfect if you are unable to install MindsDB on your device. If you are not registered yet, feel free to follow the below guide. If you have already registered, skip the next steps to connect your database.

MindsDB Studio Cloud Set Up

You can visit this link and follow the steps in registering a MindsDB Cloud account.

On the landing page, navigate to the left corner of the screen and select ADD DATABASE. Enter the required details.

Too see an example, click here.

Click Connect, you should now see your Postgres database connection in the main screen.

You are now done with connecting MindsDB to your database!

Create a predictor

Now we are ready to create our own predictor. We will start by using the MySQL API to connect to MindsDB and with a single SQL command create a predictor.

The predictor we will create will be trained to determine if a mushroom is edible or poisonous.

Using the following command, you will connect through the MySQL API to MindsDB.

Please note that the username and password to use will be the credentials you registered your MindsDB account with.

mysql -h cloud.mindsdb.com --port 3306 -u cloudusername@mail.com -p

If you are successfully connected, make sure you connect to MindsDB's database.

USE mindsdb;

Use the following query to create a predictor that will predict the target_class for the specific field parameters.

mysql>CREATE PREDICTOR mushroom_predictor
FROM Mushrooms (
    SELECT * FROM "mushrooms"
) as mushrooms_data
PREDICT class;

After creating the predictor you should see a similar output:

Query OK, 0 rows affected (12.18 sec)

The predictor was created successfully and has started training. To check the status of the model, use the below query.

mysql> SELECT * FROM mindsdb.predictors WHERE name='mushroom_predictor';

After the predictor has finished training, you will see a similar output. Note that MindsDB does model testing for you automatically, so you will immediately see if the predictor is accurate enough.

+--------------------+----------+----------+---------+---------------+-----------------+-------+-------------------+------------------+
| name               | status   | accuracy | predict | update_status | mindsdb_version | error | select_data_query | training_options |
+--------------------+----------+----------+---------+---------------+-----------------+-------+-------------------+------------------+
| mushroom_predictor | complete | 1.0      | class   | up_to_date    | 2.61.0          | NULL  |                   |                  |
+--------------------+----------+----------+---------+---------------+-----------------+-------+-------------------+------------------+
1 row in set (0.61 sec)

The predictor has completed its training, indicated by the status column, and shows the accuracy of the model.
You can revisit training new predictors to increase accuracy by changing the query to better suit the dataset i.e. omitting certain columns etc.

Good job! We have successfully created and trained a predictive model ✨

Make predictions

In this section you will learn how to make predictions using your trained model.
Now we will use the trained model to make predictions using a SQL query

Use the following query using mock data with the predictor.

SELECT class
FROM mindsdb.mushroom_predictor
WHERE when_data='{"cap-shape": "x","cap-surface": "s","cap-color": "n","bruises": "t","odor": "p","gill-attachment": "f","gill-spacing": "c","gill-size": "n","gill-color": "k","stalk-shape": "e","stalk-root": "e","stalk-surface-above-ring": "s","stalk-surface-below-ring": "s","stalk-color-above-ring": "w","stalk-color-below-ring": "w","veil-type": "p","veil-color": "w","ring-number": "o","ring-type": "p","spore-print-color": "k","population": "s","habitat": "u"}'\G

The result:

*************************** 1. row ***************************
class: p
1 row in set (1.19 sec)

We have successfully created and trained a model that can predict if a model is edible or poisonous.

Want to try it out for yourself? Sign up for a free MindsDB account and join our community!
Engage with MindsDB community on Slack or Github to ask questions, share and express ideas and thoughts!

For more check out other tutorials and MindsDB documentation.

MindsDB: Creating machine learning predictive models using SQL.

Chandre Van Der Westhuizen — Wed, 19 Jan 2022 12:26:44 +0000

How AI can help identify diabetic risk.

Dataset:Diabetes Data

Communtiy Author: Chandre Tosca Van Der Westhuizen

Diabetes is a metabolic disease that causes high blood sugar and if left untreated can damage your nerves, eyes, kidneys and other organs. It is known as a silent killer, as recent studies have shown that by the year 2040 the world's diabetic patients will reach 642 million. The need to analyse vast medical data to assist in the diagnoses, treatment and management of illnesses is increasing for the medical community. With the rapid development of machine learning, it has been applied to many aspects of medical health and is transforming the health care system.

The vitality to intelligently transform information into valuable knowledge through machine learning has become more present in biosciences. With the use of predictive models, MindsDB can assist in classifying diabetic and non-diabetic patients or those who pose a high risk. This is just a small showcase on how MindsDB's machine learning will be able to assist in vastly enhancing the reach of illnesses, thereby making it more efficient and can revolutionize businesses and most importantly the health care system.

In this tutorial we will be exploring how we can use a machine learning model to classify negative and positive cases for diabetes.
MindsDB allows you to train your model from CSV data directly, however for this tutorial you will:

Create & setup a new database.
Load sample data into a table using the PGAdmin GUI Tool.
Allow connections to the database using Ngrok.
Setup a MindsDB connection to your local instance.
Create a predictor using SQL.

Pre-requisites

For this tutorial, Docker is highly recommended. A docker-compose.yml file will be provided to get you started quickly.

To ensure you can complete all the steps, make sure you have access to the following tools:

A MindsDB instance. Check out the installation guide for Docker or PyPi. You can also use MindsDB Cloud.
Optional: A PostgreSQL Database. You can install it locally or through Docker.
Downloaded the dataset. You can get it from here
Access to PGAdmin4 (provided with the docker-compose file).
Optional: Access to ngrok. You can check the installation details at the ngrok website.

Docker

Create a new project directory named e.g. mindsdb-tutorial
Inside your project directory:

2.1 Create a new file named docker-compose.yml.

2.2 Create another directory named data.

2.3 Download the CSV datafile and store it in your directory

Open the docker-compose.yml file with any text-editor, copy the following and save.


version: '3.5'

services:
  postgres:
    container_name: postgres_container
    image: postgres
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-changeme}
      PGDATA: /data/postgres
    volumes:
       - postgres:/data/postgres
    ports:
      - "5432:5432"
    network_mode: bridge
    restart: unless-stopped

  pgadmin:
    container_name: pgadmin_container
    image: dpage/pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL:-pgadmin4@pgadmin.org}
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD:-admin}
      PGADMIN_CONFIG_SERVER_MODE: 'False'
    volumes:
       - pgadmin:/var/lib/pgadmin
       # Mounts local dir to docker container
       - ./data:/home

    ports:
      - "5050:80"
    network_mode: bridge
    restart: unless-stopped

volumes:
    postgres:
    pgadmin:

This compose stack features both a Postgres database with PGAdmin4 running on ports 5432 and 5050 respectively.
For the database to connect to MindsDB we will use a tunneling program Ngrok to allow the remote database connection without exposing your public IP.

Data will be loaded from a csv file using the import tool.
Docker Volumes are used for persistent storage of data.

To run, open a terminal in the project folder and run docker-compose up -d .
If you do not have the Docker images locally, it will first download them from docker hub and might take.

Setup the database

After docker has started all the services
In this section, you will create a Postgres database and a table into which you will then load the dataset.

First, connect to your Postgres instance. There are cli options like pgcli.

We will use a gui manager: pgAdmin

The required details are specified within the compose file. Remember to change the username & password if you have a different one set up in.

Steps

Open your browser and enter localhost:5050 in the Navigation bar.
Setup or enter a master password.
Create a new server connection by navigating to the right side of the page and right click on 'Server', select 'Create' and enter the required details.

3.1 For the purpose of the exercise, under the General Tab the Name entered is MindsDB and the host name/address is localhost under the Connection tab.
To create a new database, select the dropdown of the Server and navigate to 'Databases', right click on it and select 'Create' and then 'Databases'.Enter the required details.

4.1 For this exercise, the Database was given the name DIABETES_DATA under the General tab.
Select the dropdown of the database created, navigate to 'Schema' and right click on it to select 'Create' and then 'Schema'. Enter the required details.
Select the dropdown of the schema created and navigate to 'Tables' to right click on it and select 'Query Tool'. This will allow you to add a table with SQL code.The below code was used to create the table.

CREATE TABLE Diabetes (
    Number_of_times_pregnant INT ,
    Plasma_glucose_concentration DECIMAL(6,3) ,
    Diastolic_blood_pressure DECIMAL(6,3) ,
    Triceps_skin_fold_thickness DECIMAL(6,3) ,
    Two_Hour_serum_insulin DECIMAL(6,3) ,
    Body_mass_index DECIMAL(6,3) ,
    Diabetes_pedigree_function DECIMAL(6,3) ,
    Age INT ,
    Class varchar(100)
);

7. Importing The Data

Before you import the data, please delete the first row as it is a table header. To import rows into your table, right click on the table name and select Import/Export.

Under the Options tab, ensure the slider is set to Import.
Select your file name and ensure that the delimiters are set.

8. Query the data

By making a select query, the data that has been loaded can be verified.

Navigate to the table name to right click and select 'SCRIPTS', then choose 'SELECT scripts'.
Run the below query and see the results returned.

    SELECT * FROM "DIABETES_DATA".diabetes;

You can use the below query structure to show your database

    SELECT * FROM "Schema_name".table_name;

Congratulations! You have completed setting up a Postgres database.

Connect your database to MindsDB GUI

Now we are ready to connect your database to MindsDB's GUI. For this exercise, we will be connecting to MindsDB Cloud as it is much more convenient for most users to use.

To make our database available to MindsDB, we will run ngrok as our database instance is local.

The following command can be run in docker or a terminal on your device to set up a ngrok tunnel.

ngrok tcp [db-port]

For this example the port number used is 5432.

You should see a similar output:

Session Status                online
Account                       myaccount (Plan: Free)
Version                       2.3.40
Region                        United States (us)
Web Interface                 http://127.0.0.1:4040
Forwarding                    tcp://x.tcp.ngrok.io:15076 -> localhost:5432

The information required is by the forwarded address, next to 'Forwarding' select and copy x.tcp.ngrok.io:15076 Once you have copied the information, you can add it to the information requested by the MindsDB GUI which we will get to in a moment.

MindsDB Studio Cloud Set Up

You can visit this link and follow the steps in registering a MindsDB Cloud account.

On the landing page, navigate to the left corner of the screen and select ADD DATABASE. Enter the required details.

Click Connect, you should now see your Postgres database connection in the main screen.

You are now done with connecting MindsDB to your database! 🚀

Create a predictor

Now we are ready to create our own predictor! We will start by using the MySQL API to connect to MindsDB and with a single SQL command create a predictor.

The predictor we will create will be trained to determine negative and positive cases for diabetes. Predictors are great machine learning models when working with large datasets and optimal for determining classifications.

Using the following command, you will connect through the MySQL API to MindsDB.

Please note that the username and password to use will be the credentials you registered your MindsDB account with.

mysql -h cloud.mindsdb.com --port 3306 -u cloudusername@mail.com -p

If you are successfully connected, make sure you connect to MindsDB's database.

USE mindsdb;

Use the following query to create a predictor that will predict the class (positive or negative) for the specific field parameters.

CREATE PREDICTOR diabetes_predictor
FROM Diabetes (
    SELECT * FROM "DIABETES_DATA".diabetes
) PREDICT class;

After creating the predictor you should see a similar output:

Query OK, 0 rows affected (8.09 sec)

The predictor was created successfully and has started training. To check the status of the model, use the below query.

SELECT * FROM mindsdb.predictors WHERE name='diabetes_predictor';

After the predictor has finished training, you will see a similar output. Note that MindsDB does model testing for you automatically, so you will immediately see if the predictor is accurate enough.

mysql> SELECT * FROM mindsdb.predictors WHERE name='diabetes_predictor';
+--------------------+----------+--------------------+---------+---------------+-----------------+-------+-------------------+------------------+
| name               | status   | accuracy           | predict | update_status | mindsdb_version | error | select_data_query | training_options |
+--------------------+----------+--------------------+---------+---------------+-----------------+-------+-------------------+------------------+
| diabetes_predictor | complete | 0.6546310832025117 | class   | up_to_date    | 2.61.0          | NULL  |                   |                  |
+--------------------+----------+--------------------+---------+---------------+-----------------+-------+-------------------+------------------+
1 row in set (0.57 sec)

Good job! We have successfully created and trained a predictive model ✨

Make predictions

In this section you will learn how to make predictions using your trained model.
Now we will use the trained model to make predictions using a SQL query

Use the following query using mock data with the predictor.

SELECT class
FROM mindsdb.diabetes_predictor
WHERE when_data='{"number_of_times_pregnant": 0, "plasma_glucose_concentration": 135.0, "diastolic_blood_pressure": 65.0, "triceps_skin_fold_thickness": 30, "two_Hour_serum_insulin": 0, "body_mass_index": 23.5, "diabetes_pedigree_function": 0.366, "age": 31}'\G

The result:

class: negative
1 row in set (0.32 sec)

*************************** 1. row ***************************

Viola! We have successfully created and trained a model and make our own prediction. How easy and amazing is MindsDB? 🎉

Want to try it out for yourself? Sign up for a free MindsDB account and join our community!
Engage with MindsDB community on Slack or Github to ask questions, share and express ideas and thoughts!

For more check out other tutorials and MindsDB documentation.