Skip to main content

SingleStoreDB v7.3

Additional CREATE PIPELINE Examples

Pipelines may be created using the SKIP, IGNORE, or REPLACE clauses. These clauses can be used alone or in combination with each other.

SKIP

The SKIP ... ERRORS behavior allows you to specify an error scenario that, when encountered, discards an offending row. See SKIP ALL ERRORS Example for more information.

Examples

CREATE PIPELINE <pipeline_name>  AS LOAD DATA <file_name> SKIP PARSER ERRORS INTO TABLE <table_name>;

CREATE PIPELINE <pipeline_name>  AS LOAD DATA <file_name> SKIP PARSER ERRORS REPLACE INTO TABLE<table_name>;

CREATE PIPELINE <pipeline_name>  AS LOAD DATA <file_name> SKIP CONSTRAINT ERRORS INTO TABLE <table_name>;
 
CREATE PIPELINE <pipeline_name>  AS LOAD DATA <file_name> SKIP ALL ERRORS INTO TABLE <table_name>;

IGNORE

Unlike SKIP ... ERRORS which discards offending rows, IGNORE may change the inserted row’s data to ensure that it adheres to the table schema. See IGNORE Error Handling

Examples

CREATE PIPELINE <pipeline_name> AS LOAD DATA <file_name> IGNORE INTO TABLE <table_name>;

CREATE PIPELINE <pipeline_name> AS LOAD DATA <file_name> IGNORE PARSER ERRORS INTO TABLE <table_name>;

CREATE PIPELINE <pipeline_name> AS LOAD DATA <file_name> IGNORE PARSER ERRORS REPLACE INTO TABLE <table_name>;

REPLACE

If the OR REPLACE clause is provided and a pipeline with pipeline_name already exists, then the CREATE query will alter that pipeline to match the new definition. Its state, including its cursor positions, will be preserved by the CREATE.

REPLACE may also be used with IGNORE and SKIP. However, REPLACE would come after the SKIP or IGNORE clause.

Examples

CREATE PIPELINE <pipeline_name> AS LOAD DATA <file_name> REPLACE INTO TABLE <table_name>;

CREATE PIPELINE <pipeline_name> AS LOAD DATA <file_name> SKIP ALL ERRORS REPLACE INTO TABLE <table_name>;

CREATE PIPELINE <pipeline_name> AS LOAD DATA <file_name> SKIP CONSTRAINT ERRORS INTO TABLE <table_name>;

CREATE PIPELINE p AS LOAD DATA <file_name> SKIP CONSTRAINT ERRORS REPLACE INTO <pipeline_name>;

Important

  • SKIP CONSTRAINT ERRORS  and  SKIP DUPLICATE KEY ERRORS  are unsupported with pipelines into stored procedures.

  • IGNORE, SKIP PARSER ERRORS, and  SKIP ALL ERRORS  are unsupported with non-CSV pipelines.

  • REPLACE, SKIP CONSTRAINT ERRORS, and SKIP DUPLICATE KEY ERRORS are supported with non-CSV pipelines.

In this section: