CREATE JOB Statement

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

With the following syntax, you can create jobs, delete jobs, and query for jobs and their history.

Creating a Job

Here is the syntax:

CREATE JOB [project_name.]job_name [AS] (
   <mindsdb_sql_query_1>[; <mindsdb_sql_query_2>]
)
[START <date>]
[END <date>]
[EVERY [number] <period>];

Where:

Expression	Description
`[project_name.]job_name`	Name of the job preceded by an optional project name where the job is to be created.
`<mindsdb_sql_query_1>[; <mindsdb_sql_query_2>]`	One or more queries separated by `;` to be executed by the job.
`[START <date>]`	Optional. The date when the job starts its periodical or one-time execution. If not set, it is the current system date.
`[END <date>]`	Optional. The date when the job ends its periodical or one-time execution. If it is not set (and the repetition rules are set), then the job repeats forever.
`[EVERY [number] <period>]`	Optional. The repeatition rules for the job. If not set, the job runs once, not considering the end date value. If the `number` value is not set, it defaults to 1.

Available <date> formats

Here are the supported <date> formats:

'%Y-%m-%d %H:%M:%S'
'%Y-%m-%d'

Please note that the default time zone is UTC.

Available <period> values

And the supported <period> values:

minute / minutes / min
hour / hours
day / days
week / weeks
month / months

Deleting a Job

Here is the syntax for deleting a job:

DROP JOB [project_name.]job_name;

The project_name value is optional. The job_name value indicates the job to be deleted.

Let’s look at some examples:

DROP JOB my_project.retrain_and_save_job;

Here we drop the retrain_and_save_job that resides in the my_project project.

And another example:

DROP JOB create_table_job;

Here we drop the create_table_job job that resides in the current project.

To learn more about projects in MindsDB, visit our docs here.

Querying Jobs

Here is how we can view all jobs in the current project:

SELECT *
FROM jobs;

On execution, we get:

+------------------------------------+---------+----------------------------+----------------------------+----------------------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| NAME                               | PROJECT | START_AT                   | END_AT                     | NEXT_RUN_AT                | SCHEDULE_STR  | QUERY                                                                                                                                                                                                                                   |
+------------------------------------+---------+----------------------------+----------------------------+----------------------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| drop_model                         | mindsdb | 2023-04-01 00:00:00.000000 | [NULL]                     | 2023-04-01 00:00:00.000000 | [NULL]        | DROP MODEL mindsdb.home_rentals_model                                                                                                                                                                                                   |
| retrain_model_and_save_predictions | mindsdb | 2023-02-15 19:19:43.210122 | 2023-04-01 00:00:00.000000 | 2023-02-15 19:19:43.210122 | every 2 days  | 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) |
| save_predictions                   | mindsdb | 2023-02-15 19:19:48.545580 | [NULL]                     | 2023-02-15 19:19:48.545580 | every hour    | 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)                                                |
+------------------------------------+---------+----------------------------+----------------------------+----------------------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

We can also view jobs from a defined project:

SELECT *
FROM my_project.jobs;

Or from all projects at once:

SELECT *
FROM information_schema.jobs;

Querying Jobs History

You can query the history of jobs similar to querying for jobs.

Here is how we can view all jobs history in the current project:

SELECT *
FROM jobs_history;

On execution, we get:

+------------------------------------+---------+----------------------------+----------------------------+----------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| NAME                               | PROJECT | START_AT                   | END_AT                     | NEXT_RUN_AT                | ERROR  | QUERY                                                                                                                                                                                                                                   |
+------------------------------------+---------+----------------------------+----------------------------+----------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| retrain_model_and_save_predictions | mindsdb | 2023-02-15 19:19:43.210122 | 2023-04-01 00:00:00.000000 | 2023-02-15 19:19:43.210122 | [NULL] | 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) |
| save_predictions                   | mindsdb | 2023-02-15 19:19:48.545580 | [NULL]                     | 2023-02-15 19:19:48.545580 | [NULL] | 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)                                                |
+------------------------------------+---------+----------------------------+----------------------------+----------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Please note that the drop_model job is not in the jobs_history table because it didn’t start yet.

We can also view jobs history from a defined project:

SELECT *
FROM my_project.jobs_history;

Or from all projects at once:

SELECT *
FROM information_schema.jobs_history;

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 Cloud 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 Cloud 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.

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;

Please note that the join_learn_process parameter in the USING clause of the RETRAIN statement ensures that the retraining process completes before inserting predictions into a table. In general, this parameter is used to prevent several retrain processes from running simultaneously.

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;

Please note that the uniqueness of the created table name is ensured here by using the {{START_DATETIME}} variable that is replaced at runtime by the date and time of the current run.

You can use the following variables for this purpose:

PREVIOUS_START_DATETIME is replaced by date and time of the previous run of this job.
START_DATETIME is replaced by date and time of the current job run.
START_DATE is replaced by date of the current job run.

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.

Follow the tutorial on how to create a Twitter chatbot to learn the details.

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

CREATE JOB auto_respond AS (

    INSERT INTO my_twitter.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.twitter_response_model r 
        WHERE t.query = '(mindsdb OR #mindsdb) -is:retweet -is:reply' 
        AND t.created_at > "{{PREVIOUS_START_DATETIME}}"
        LIMIT 2
)
EVERY hour;

The SELECT statement joins the data table with the model table to get responses for tweets posted after the {{PREVIOUS_START_DATETIME}} timestamp. Then, the INSERT INTO statement writes these responses to the tweets table of the my_twitter integration.

To learn more about OpenAI integration with MindsDB, visit our docs here.

​Description

​Syntax

​Creating a Job

​Deleting a Job

​Querying Jobs

​Querying Jobs History

​Additional Configuration

​Example 1: Retrain a Model

​Example 2: Save Predictions

​Example 3: Drop a Model

​Example 4: Twitter Chatbot