Sign inTry Free

Data Shaping with Pipelines

On this page

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.

Some common data shaping operations that can be performed are:

  • Lookups from other SingleStore tables (in addition to the destination table(s))

  • Normalizing data

  • Denormalizing data

  • Adding computed columns

  • Filtering data (excluding specific columns or records)

  • Mapping data values from the data source to new values

  • Splitting records from the data source into multiple destination tables

  • Adding surrogate keys

Data modifications made during shaping are not written back to the data source, unless done explicitly in a transform (SingleStore Self-Managed only).

Ways to specify data shaping logic:

  • In a CREATE PIPELINE statement.

  • In a stored procedure that is called from the pipeline.

  • In a transform that is called from the pipeline.

Methods for Data Shaping with Pipelines

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

In a CREATE PIPELINE statement

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.

CREATE PIPELINE uses the LOAD DATA clause to perform data shaping. See examples in the LOAD DATA topic.

Pipeline Stored Procedure

Medium

More Difficult

Pros: Transactional guarantees; cons of specifying data shaping logic directly in your CREATE PIPELINE statement don’t apply. Cons: Limited to the capabilities of the PSQL language; cannot use third-party libraries.

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 Helios; 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 stdout, only (refer to the guide in the next column for details).

See the guide Writing a Transform to Use with a Pipeline

Last modified: October 23, 2023

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK