START PIPELINE

The START PIPELINE statement starts an existing pipeline in a SingleStore database. An error will occur if the pipeline is already running.

Syntax

START PIPELINE [IF NOT RUNNING] pipeline_name [FOREGROUND [LIMIT count BATCHES] ];
START ALL|FAILED PIPELINES;

Arguments

  • PIPELINE_NAME: the name of the pipeline.

  • count: an integer that represents the maximum number of batches to ingest.

  • IF NOT RUNNING: if included, this statement will start the pipeline if it is not running and do nothing otherwise.

  • ALL: all pipelines will start in the current database in the background. It is equivalent to running START PIPELINE IF NOT RUNNING pipeline_name for every pipeline in the database.

  • FAILED: will restart pipelines that have stopped due to an error.

Remarks

Starting a Pipeline in the Background

  • Start a pipeline in the background by running the START PIPELINE statement without the FOREGROUND clause.

  • After starting the pipeline, the client will not report the number of affected rows or any errors that it encounters. You can find this information in the Pipelines information schema tables.

  • The engine variable pipelines_stop_on_error specifies whether a pipeline started in the background will stop if it encounters an error.

  • Use a background pipeline when you want to ingest data from a data source at regular intervals. You can also use a foreground pipeline for the same purpose, but the foreground pipeline will stop under certain conditions, as explained in the next section.

  • This command causes implicit commits. Refer to COMMIT for more information.

  • Refer to the Permission Matrix for the required permission.

Starting a Pipeline in the Foreground

When a pipeline is started in the foreground, the client will report the number of affected rows or any errors it encounters.

To start a pipeline in the foreground without a batch limit, run START PIPELINE mypipeline FOREGROUND;.

To start a pipeline in the foreground with a batch limit, use the LIMIT clause. For example, to specify a batch limit of ten, run START PIPELINE mypipeline FOREGROUND LIMIT 10 BATCHES;.

This command causes implicit commits. Refer to COMMIT for more information.

A foreground pipeline will stop when any of the following occur:

  • The batch limit is reached (if it is specified).

  • An error occurs.

  • All data from the data source has been loaded.

A foreground pipeline is intended for the following purposes:

  • Testing and debugging.

  • Running a one-time bulk data load.

  • Writing your own scheduler to load data at specific times.

Permissions Needed to Start a Pipeline

To start a pipeline, a user must have the START PIPELINE permission. Pipelines without the required user access will be skipped and see a message similar to the one below:

START ALL PIPELINES;
START PIPELINE test_pipeline;
ERROR 1142 (42000): START PIPELINE command denied to user 'singlestore-4100c00-bd00-0d'@'%' for table 'test_pipeline'

START PIPELINE does not apply to the use of the pipeline's resource pool.

For more information on resource pools, see Setting Resource Limits.

Examples

Example 1: Starting a Pipeline in Background

The following example demonstrates how to start a pipeline with no additional clauses specified:

START PIPELINE mypipeline;
Query OK, 4 rows affected (0.04 sec)

To see if the pipeline is running, run SHOW PIPELINES:

SHOW PIPELINES;
+-------------------+---------+
| Pipelines_in_mydb | State   |
+-------------------+---------+
| mypipeline        | Running |
+-------------------+---------+

Example 2: Starting a Pipeline in the Foreground

The following example demonstrates how to start a pipeline in the foreground, which displays the number of rows written to the destination table:

START PIPELINE mypipeline FOREGROUND;
Query OK, 4 rows affected (1.39 sec)

To see if the pipeline is running, run SHOW PIPELINES:

SHOW PIPELINES;
+-------------------+---------+
| Pipelines_in_mydb | State   |
+-------------------+---------+
| mypipeline        | Running |
+-------------------+---------+

If your pipeline is not in the running state, see the section Starting a Pipeline in the Foregroundfor further details.

Example 3: Starting a Pipeline in the Foreground with a Batch Limit

The following example demonstrates how to start a pipeline in the foreground with a specified limit, which displays the number of rows written to the destination table:

START PIPELINE mypipeline FOREGROUND LIMIT 5 BATCHES;
Query OK, 37 rows affected (0.71 sec)

To see if the pipeline is running, run SHOW PIPELINES:

SHOW PIPELINES;
+-------------------+---------+
| Pipelines_in_mydb | State   |
+-------------------+---------+
| mypipeline        | Running |
+-------------------+---------+

If your pipeline is not in the running state, see the section Starting a Pipeline in the Foreground for further details.

Example 4: Starting All Pipelines

START ALL PIPELINES will start all available pipelines on the current database at once. To verify the pipelines are running use the SHOW PIPELINES command.

START ALL PIPELINES;
Query OK, 0 rows affected (0.01 sec)
SHOW PIPELINES;
+-----------------------+---------+-----------+
| Pipelines_in_examples | State   | Scheduled |
+-----------------------+---------+-----------+
| nfbooks               | Running | False     |
| books                 | Running | False     |
+-----------------------+---------+-----------+

Example 5: Starting a Failed Pipeline

START FAILED PIPELINES will start only pipelines in a failed state. To see what state a pipeline is in, use the following command:

SELECT pipeline_name, batch_state
FROM information_schema.PIPELINES_BATCHES_SUMMARY;
+---------------+-------------+
| pipeline_name | batch_state |
+---------------+-------------+
| nautical      | Succeeded   |
| horror        | Failed      |
+---------------+-------------+

Start the failed pipelines and verify the batch state of the pipelines:

START FAILED PIPELINES;
SELECT pipeline_name, batch_state
FROM information_schema.PIPELINES_BATCHES_SUMMARY;
+---------------+-------------+
| pipeline_name | batch_state |
+---------------+-------------+
| nautical      | Succeeded   |
| horror        | Succeeded   |
+---------------+-------------+

Last modified: September 24, 2024

Was this article helpful?