JOBS

MindsDB enables you to automate any pipeline using JOBS, which grant you the power to schedule any query at any frequency. Additionally, it introduces the keyword LAST, offering the capability for a JOB to act solely on new data, essentially treating any data source as a stream.

​

Description

The CREATE JOB statement lets you schedule the execution of queries by providing relevant parameters, such as start date, end date, or repetition frequency.

​

Syntax

​

CREATE JOB

Here is the syntax:

CREATE JOB [IF NOT EXISTS] [project_name.]job_name [AS] (
   <statement_1>[; <statement_2>][; ...]
)
[START <date>]
[END <date>]
[EVERY [number] <period>]
[IF (<statement_1>[; <statement_2>][; ...])];

Where:

Further, you can query all jobs and their execution history like this:

SHOW JOBS;
SELECT * FROM [project_name.]jobs WHERE name = 'job_name';
SELECT * FROM log.jobs_history WHERE project = 'mindsdb' AND name = 'job_name';

​

LAST

MindsDB provides a custom LAST keyword that enables you to fetch data inserted after the last time you queried for it. It is a convenient way to select only the newly added data rows when running a job.

Imagine you have the fruit_data table that contains the following:

+-------+-----------+
| id    | name      |
+-------+-----------+
| 1     | apple     |
| 2     | orange    |
+-------+-----------+

When you run the SELECT query with the LAST keyword for the first time, it’ll give an empty output.

SELECT id, name
FROM fruit_data
WHERE id > LAST;

This query returns:

+-------+-----------+
| id    | name      |
+-------+-----------+
| null  | null      |
+-------+-----------+

SELECT id, name
FROM fruit_data
WHERE id > COALESCE(LAST, 1);

SELECT id, name
FROM fruit_data
WHERE id > 1;

SELECT id, name
FROM fruit_data
WHERE id > LAST;

Now imagine you inserted a new record into the fruit_data table:

+-------+-----------+
| id    | name      |
+-------+-----------+
| 1     | apple     |
| 2     | orange    |
| 3     | pear      |
+-------+-----------+

When you run the SELECT query with the LAST keyword again, you’ll get only the newly added record as output.

SELECT id, name
FROM fruit_data
WHERE id > LAST;

This query returns:

+-------+-----------+
| id    | name      |
+-------+-----------+
| 3     | pear      |
+-------+-----------+

From this point on, whenever you add new records into the fruit_data table, it’ll be returned by the next run of the SELECT query with the LAST keyword. And, if you do not add any new records between the query runs, then the output will be null.

If you want to clear context for the LAST keyword in the editor, then run set context = 0 or set context = null.

​

Conditional Jobs

Here is how you can create a conditional job that will execute periodically only if there is new data available:

CREATE JOB conditional_job (

    FINETUNE MODEL model_name
    FROM (     
        SELECT *
        FROM datasource.table_name
        WHERE incremental_column > LAST
    )
)
EVERY 1 min
IF (
    SELECT *
    FROM datasource.table_name
    WHERE incremental_column > LAST
);

The above job will be triggered every minute, but it will execute its task (i.e. finetuning the model) only if there is new data available.

​

Examples

​

Example 1: Retrain a Model

In this example, we create a job in the current project to retrain the home_rentals_model model and insert predictions into the rentals table.

CREATE JOB retrain_model_and_save_predictions (

   RETRAIN mindsdb.home_rentals_model
   USING
      join_learn_process = true;

   INSERT INTO my_integration.rentals (
      SELECT m.rental_price, m.rental_price_explain
      FROM mindsdb.home_rentals_model AS m
      JOIN example_db.demo_data.home_rentals AS d
   )
)
END '2023-04-01 00:00:00'
EVERY 2 days;

The retrain_model_and_save_predictions job starts its execution on the current system date and ends on the 1st of April 2023. The job is executed every 2 days.

​

Example 2: Save Predictions

In this example, the job creates a table named as result_{{START_DATETIME}} and inserts predictions into it.

CREATE JOB save_predictions (

   CREATE TABLE my_integration.`result_{{START_DATETIME}}` (
      SELECT m.rental_price, m.rental_price_explain
      FROM mindsdb.home_rentals_model AS m
      JOIN example_db.demo_data.home_rentals AS d
   )
)
EVERY hour;

The save_predictions job starts its execution on the current system date and repeats every 2 hours until it is manually disabled.

​

Example 3: Drop a Model

In this example, we create a job to drop the home_rentals_model model scheduled on the 1st of April 2023.

CREATE JOB drop_model (

   DROP MODEL mindsdb.home_rentals_model
) 
START '2023-04-01';

This job runs once on the 1st of April 2023.

​

Example 4: Twitter Chatbot

You can easily create a chatbot to respond to tweets using jobs. But before you get to it, you should connect your Twitter account to MindsDB following the instructions here.

Let’s create a job that runs every hour, checks for new tweets, and responds using the OpenAI model.

CREATE JOB mindsdb.gpt4_twitter_job AS (

   -- insert into tweets the output of joining model and new tweets
   INSERT INTO my_twitter_v2.tweets (in_reply_to_tweet_id, text)
      SELECT
         t.id AS in_reply_to_tweet_id,
         r.response AS text
      FROM my_twitter.tweets t
      JOIN mindsdb.snoopstein_model r
         WHERE
            t.query = '(@snoopstein OR @snoop_stein OR #snoopstein OR #snoop_stein) -is:retweet -from:snoop_stein'
         AND t.created_at > LAST
      LIMIT 10
)
EVERY hour;

The SELECT statement joins the data table with the model table to get responses for newly posted tweets, thanks to the LAST keyword. Then, the INSERT INTO statement writes these responses to the tweets table of the my_twitter integration.

​

Additional Configuration

Here is the template of the config.json file that you can pass as a parameter when starting your local MindsDB instance:

"jobs": {
        "disable": true,
        "check_interval": 30
      }

The disable parameter defines whether the scheduler is active (true) or not (false). By default, in the MindsDB Editor, the scheduler is active.

The check_interval parameter defines the interval in seconds between consecutive checks of the scheduler table. By default, in the MindsDB Editor, it is 30 seconds.

You can modify the default configuration in your local MindsDB installation by creating a config.json file and starting MindsDB with this file as a parameter. You can find detailed instructions here.