Data Shaping with Pipelines
After data is extracted from a SingleStore Pipeline’s data source, it can be optionally shaped (modified). After shaping, the pipeline loads the data into one or more destination tables in SingleStore.
Data modifications made during shaping are not written back to the data source, unless done explicitly in a transform (see the third data shaping method below).
There are three ways to specify data shaping logic:
- In a
- In a stored procedure that is called from the pipeline.
- In a transform that is called from the pipeline.
The details of each data shaping method are explained in the following table.
|Data Shaping Method||Amount of Customization Logic Allowed||Ease of Use||Comments||Examples|
||Low||Easiest||Pros: Generally, runs the fastest of the three data shaping methods; transactional guarantees. Cons: Limited to SQL WHERE CLAUSE to perform filtering and SQL expressions to perform other shaping logic; can only load data into one target table; cannot perform lookups in other tables; a single row in the data source cannot generate multiple rows in the target table.||
|Pipeline Stored Procedure||Medium||More Difficult||Pros: Transactional guarantees; cons of specifying data shaping logic directly in your
||See examples in CREATE PIPELINE … INTO PROCEDURE|
|Transform||High||Most Difficult||Pros: Can use any nearly any programming language and leverage third-party libraries. Cons: Not available in SingleStore Managed Service; runs slower than stored procedures for most use cases; deployment could be challenging; cannot insert into multiple tables; transactional guarantees apply to data written to
||See the guide Writing a Transform.|