Skip to main content

Pipelines

When the primary purpose of an application is to move or copy data from a source to a target, we call that a "pipeline" application. For an introduction to the subject, see What is a Data Pipeline. A Striim for ClickHouse application typically consists of a source to read data from, optional in-flight processing of data, and a target to write the data to.

S4CHintroDiagram.png

Supported sources and targets for Striim for ClickHouse

The following sources are supported by Striim for ClickHouse:

  • Amazon S3

  • Azure Cosmos DB for MongoDB

  • Azure Data Lake

  • BigQuery

  • Cobol Copy Book files

  • CollectD files

  • Cosmos DB

  • Crunchy Data

  • files (see Parsers)

    • Apache access logs

    • Avro

    • binary

    • Cobol Copy Book

    • CollectD

    • delimited

    • free-form

    • HL7v2

    • JSON

    • Netflow

    • name-value pairs

    • Parquet

    • Striim logs

    • XML

  • Google Ads

  • Google Cloud Storage

  • HDFS

  • HTTP

  • HubSpot

  • Intercom

  • Jira

  • JMS

  • JMX

  • Kafka

  • MapR file system

  • MariaDB (Database Reader using JDBC for initial load, MariaDB Reader using CDC for continuous replication)

  • Microsoft Dynamics 365

  • Microsoft Dynamics 365 Business Central

  • MongoDB

  • MQTT

  • MySQL (JDatabase Reader using DBC for initial load, MySQL Reader using CDC for continuous replication)

  • Neon

  • OPCUA

  • Oracle (Database Reader using JDBC for initial load, Oracle Reader using CDC for continuous replication)

  • PostgreSQL (Database Reader using JDBC for initial load, PostfreSQL Reader using CDC for continuous replication)

  • Salesforce

  • Salesforce CDC (Salesforce Reader for initial load, Salesforce CDC Reader for continuous replication)

  • Salesforce Pardot

  • Salesforce Platform Event: does not support initial schema creation, tables must be created in target before running app

  • Salesforce PushTopic: does not support initial schema creation, tables must be created in target before running app

  • ServiceNow

  • Snowflake

  • Spanner

  • SQL Server (Database Reader using JDBC for initial load, MS SQL Reader using CDC for continuous replication)

  • Stripe

  • TCP

  • UDP

  • Windows Event Log

  • Yugabyte (Database Reader using JDBC for initial load, YugagyteDB Reader using CDC for continuous replication)

  • Zendesk

The following targets are supported:

  • ClickHouse

  • files (using File Writer) for development and debugging purposes

Mapping and filtering

The simplest pipeline applications simply replicate the data from the source tables to target tables with the same names, column names, and data types.

If your source is BigQuery, MariaDB, MySQL, Oracle, PostgreSQL, Snowflake, SQL Server, or YugabyteDB and you use an automated pipeline or any other approach that supports initial schema creation, data types will be mapped as detailed in Data type support & mapping for schema conversion & evolution. For other sources, see their individual data type mapping documentation under Sources.

If your requirements are more complex, see the following:

Schema evolution

For some CDC sources, Striim can capture DDL changes and replicate those changes to ClickHouse target tabkes, or take other actions, such as quiescing or halting the application, For more information, see Handling schema evolution:

Initial load versus continuous replication

Typically, setting up a data pipeline occurs in two phases.

The first step is the initial load, copying all existing data from the source to the target to create an initial, point-in-time copy of the source dataset, a process that is also called "historical sync" or "initial snapshot."

Depending on the amount and complexity of data in the source tables, initial load may take minutes, hours, days, or weeks.

Once the initial load is complete, you will start the Striim CDC application to pick up where the initial load left off. Or, if using an automated pipeline wizard or a source with automated mode, the transition from initial load to CDC will happen automatically.

How to build a pipeline in Striim for ClickHouse

Striim for ClickHouse offers several ways to build a pipeline. They differ in how much of the configuration Striim handles for you, and in how the initial load and continuous replication are carried out (see Initial load versus continuous replication).

In Striim for ClickHouse, you can build a pipeline in the following ways:

  • Striim Clara AI: Describe the pipeline you want in natural language. Clara guides you through the configuration and generates the application for you to review and approve before it makes any changes. Clara builds initial load or continuous replication applications; to build a pipeline that does both, use an automated pipeline wizard. See Build your first application.

  • Automated pipeline wizards: A wizard guides you through configuring the source and target, then creates two linked applications: one that performs the initial load of existing data, and one that continuously replicates subsequent changes using change data capture (CDC). Striim hands over from the first application to the second automatically. See Building a SQL Server-to-ClickHouse pipeline using an automated pipeline wizard.

  • Automated Mode: For sources that support it, such as Salesforce, the wizard creates a single application that performs the initial load and then automatically transitions to continuous replication. See Building a Salesforce-to-ClickHouse pipeline using Automated Mode.

  • Flow Designer: Build the application yourself, component by component, on a visual canvas. Flow Designer gives you full control over the pipeline, for example to add continuous queries (CQs) that transform or type the data, or to read from sources such as files in cloud storage. See Building a GCS-to-ClickHouse pipeline using Flow Designer.

Building a SQL Server-to-ClickHouse pipeline using an automated pipeline wizard

This topic describes how to use an automated pipeline wizard in Striim for ClickHouse to build a pipeline that replicates data from an on-premises SQL Server database to ClickHouse. The wizard creates two linked applications: one performs the initial load of existing source data to ClickHouse (in most cases, reading the source using JDBC), and the other continuously applies subsequent changes to the source, read using change data capture (CDC). Striim hands over from the first application to the second automatically. Recovery is enabled automatically for both applications, except for the initial load of Salesforce sources.

The steps are similar for all source and target combinations. As you work through the wizard, inline help next to each dialog describes the settings in detail.

  1. Select Apps > Create an App.

    AppsCreateAnApp.png
    CreateAnApp.png
  2. Search for and select your source.

    SelectSQLServer.png
  3. Click Get Started. (With some sources, at this point, you could select initial load only or change data capture only instead of automated pipeline.)

    GetStarted.png
  4. Name the pipeline and specify the namespace in which to create it, then click Next.

    NameYourAutomatedPipeline.png
  5. Specify the properties for the source as described in the inline help, then click Next.

    Configure_SQL_Server_Source_with_inline_help.png
  6. If validation is successful, the wizard will automatically go on to the next step. If validation fails, click Back and make whatever corrections are required. You must resolve all issues and validate successfully before going forward in the wizard.

    500_ILCDC_validate_source.png
  7. Select the schemas to replicate in the target (do not select system schemas), then click Next.

    500_ILCDC_select_schemas.png
  8. After Striim has validated and fetched the data, it will automatically go on to the next step.

    500_ILCDC_fetch_schemas.png
  9. Select the tables to be replicated to ClickHouse (separately for each schema), then click Next.

    500_ILCDC_select_tables.png
  10. Select or create a ClickHouse connection profile (see Create a ClickHouse Connection Profile) and select the desired Table Engine (see Choosing a table engine), then click Next.

    ConfigureClickHouse.png
  11. Optionally, edit the table mapping, then click Next.

    EditTableMapping.png
  12. Review the settings to make sure they are correct, then click Save & Start.

    Review.png
  13. Striim will create your automated pipeline.

    SettingUpYourAutomatedPipeline.png

Once Striim has created the automated pipeline, it will run the initial load app and display its status.

SQLServerToClickHouseStartingCDC.png

When initial load is complete, Striim will quiesce the initial load app.

SQLServerToClickHouseILQuiescing.png

Next Striim will start the CDC app and display its status.

SQLServerToClickHouseCDCRunning.png

As records are read from the source and written to ClickHouse, the counts will be updated.

SQLServerToClickHouseFirstEvent.png

If during either phase of the pipeline Striim encounters a problem, it will halt and offer recommendations on what to do to resolve it.

AutomatedWizardInitialLoadError.png

For more information, see Using automated pipeline wizards.

Building a Salesforce-to-ClickHouse pipeline using Automated Mode

This topic describes how to use the Automated Mode wizard in Striim for ClickHouse to build a Striim application that replicates data from Salesforce to ClickHouse. In Automated Mode, Striim performs an initial load of the selected Salesforce objects and then automatically transitions to continuous incremental replication, without requiring a separate application or manual intervention.

Application components

Component

Role

Salesforce Reader (Automated Mode)

Authenticates to Salesforce, performs the initial load of the selected objects, and then continuously replicates ongoing changes.

ClickHouse Writer

Writes both the initial load and the ongoing changes to the target ClickHouse tables, optionally creating the schema automatically.

Sample object

This topic uses the standard Salesforce Opportunity object as the example, replicated into a salesforce.Opportunity table in ClickHouse. Substitute your own objects as needed.

Prerequisites

Salesforce Reader initial setup

Complete the Salesforce Reader initial setup, described in detail in Salesforce initial load — Salesforce Reader initial setup. In brief:

  • Create a Salesforce Connected App or External Client App.

  • Obtain the Consumer Key and Consumer Secret from the app.

  • Generate a Security Token for the Salesforce user account.

  • Create a reusable Salesforce connection profile in Striim ahead of time, which simplifies configuration in the wizard.

ClickHouse connection profile

Create a connection profile for ClickHouse in Striim (Manage Striim > Connection Profiles) before starting the wizard, or create one inline while configuring the target.

ClickHouse target table

You do not need to create the target table in advance. Automated Mode can create the schema on the target automatically when you select that option in Step 3. If your organization's ClickHouse permissions do not allow Striim to create tables, either create the target table in advance and grant the connection user the CREATE TABLE permission, or clear the automatic creation option and provide the mapping manually in Step 8.

Step 1: Start the wizard and select the source

  1. From the Striim for ClickHouse home page, go to Apps > Create An App.

  2. Under Select a Source, search for and select Salesforce.

  3. Click Get Started.

    salesforce-to-clickhouse_3.png

Step 2: Name the application

  1. Enter a Name for the application (for example, SalesforceToClickHouse).

  2. Select a Namespace. See Using namespaces for what a namespace is and how it is used to organize and control access to applications.

  3. Click Next.

Step 3: Configure the Salesforce source

  1. Turn on Use Connection Profile and select your Salesforce connection profile. Alternatively, leave it off and configure the connection manually with an API endpoint and an authentication method. See Salesforce initial load — Salesforce Reader initial setup for each available authentication method (Auth Token, User Credentials, Client Credentials, and JWT Token) and how to configure it manually.

  2. Leave Create schemas on the target before moving the data selected, so that Striim creates the schema and tables in ClickHouse automatically for all selected objects.

  3. Click Next.

    salesforce-to-clickhouse_5.png

Step 4: Validate the source connection

The wizard automatically checks connectivity to Salesforce and fetches the metadata. If all checks pass, it advances automatically. If any check fails, click Back to correct the configuration.

Step 5: Select the Salesforce objects to replicate

  1. Use the search field to locate the objects you want to replicate (for this example, Opportunity).

  2. Select the checkbox next to each object to include.

  3. Click Next.

    salesforce-to-clickhouse_7.png

Step 6: Configure ClickHouse as the target

  1. Select your ClickHouse connection profile.

  2. Choose the ClickHouse table engine to use for writing continuous changes. For a comparison of the engines, see "Choosing a table engine" in Create a ClickHouse Writer application.

  3. Click Next, or continue to the table definition on the same screen.

    salesforce-to-clickhouse_8.png

Step 7: Set the write mode and schema evolution

  1. Optionally, click Define Tables to specify the sorting key, partitioning key, and other clauses for each target table.

  2. Choose how continuous changes are written:

    • Write continuous changes directly (Merge)

    • Write continuous changes as audit records (Append)

  3. Choose how Striim responds to schema changes in the source, for example, Automatically propagate schema changes to the target (Process).

  4. Click Next.

    salesforce-to-clickhouse_9.png

Step 8: (Optional) Adjust the table mapping

The wizard generates a default mapping, for example, mapping all selected Salesforce objects into a salesforce schema in ClickHouse. If the default does not match your target naming conventions, click Edit to revise it, or Reset to restore the default.

Step 9: Validate the target connection

The wizard checks connectivity and permissions on the ClickHouse target. As with the source validation, it advances automatically if the checks pass.

Step 10: Review the configuration

  1. Review the summary of the Source Configuration, Objects to be synced to ClickHouse, and Target Configuration. Use the edit icons to go back and adjust any section, or click Download to save the configuration summary.

  2. Click Save & Start to deploy and start the application immediately, or Save & Exit to deploy it later.

    salesforce-to-clickhouse_12.png

Step 11: Application deployment

Striim displays a progress screen while it creates the application and starts the pipeline.

Step 12: Monitor the running pipeline

After deployment, the view shows the source and target components along with live metrics: Total Input, Total Output, Input Rate, Output Rate, App Rate, and Max LEE (maximum end-to-end lag).

salesforce-to-clickhouse_14.png

The Table Summary view shows, for each replicated object, the source and target table names, the records read from Salesforce, the records written to ClickHouse, and a breakdown of inserts, updates, deletes, primary key updates, and DDL operations.

salesforce-to-clickhouse_15.png

Step 13: Verify the data in ClickHouse

As a final check, query the ClickHouse target table directly:

SELECT count(*) AS total_rows
FROM salesforce.Opportunity;

Building a GCS-to-ClickHouse pipeline using Flow Designer

This topic describes how to use Flow Designer to build a Striim application that reads order data from a CSV file in Google Cloud Storage (GCS) and writes it to a ClickHouse table.

Application components

The application is built from four Striim components, each configured in its own step:

Component

Role

Configured in

GCS Reader

Reads the CSV files from the Google Cloud Storage bucket.

Step 2

DSV Parser

Parses the delimited (CSV) file structure into individual fields.

Step 3

Continuous query (CQ)

Converts the parsed fields into named, typed values.

Step 4

ClickHouse Writer

Writes the typed events to the target ClickHouse table.

Step 5

Sample data

The sample data used in this topic is an e-commerce orders feed with the following columns:

Column

Description

order_id

Unique order identifier

customer_id

Unique customer identifier

order_timestamp

Date and time the order was placed

product_sku

Product SKU code

product_name

Product display name

category

Product category

quantity

Units ordered

unit_price

Price per unit

discount_percent

Discount applied, as a percentage

currency

Currency code

total_amount

Final order total after discount

sales_channel

Web, Mobile App, or Marketplace

region

Sales region

payment_method

Payment method used

shipping_status

Current shipping status

Prerequisites

GCS Reader account setup

Before creating the source, complete the GCS Reader initial setup: create a GCP service account with storage.objects.list and storage.objects.get permissions on the source bucket, and download its JSON key file. See Create a GCS Reader application, which references the required initial setup steps, and GCS Reader for the credential and permission requirements in detail.

ClickHouse target table

Create the target database and table in ClickHouse before configuring the target. For the sample data, you can use the following statement:

CREATE TABLE Orders_GCS.orders
(
    order_id          String,
    customer_id       String,
    order_timestamp   DateTime,
    product_sku       String,
    product_name      String,
    category          String,
    quantity          UInt32,
    unit_price        Decimal(10,2),
    discount_percent  UInt8,
    currency          String,
    total_amount      Decimal(10,2),
    sales_channel     String,
    region            String,
    payment_method    String,
    shipping_status   String
)
ENGINE = MergeTree
ORDER BY order_id;
ClickHouse connection profile

Create a connection profile for ClickHouse in Striim (Manage Striim > Connection Profiles) before configuring the target, or create one inline while configuring ClickHouse Writer in Step 5.

Step 1: Create the application

  1. Go to the Apps page and click Create New.

  2. Select Start with Flow Designer.

    gcs-to-clickhouse_1.png
  3. Enter a Name for the application (for example, OrdersGCSToClickHouse) and a Namespace. See Using namespaces for what a namespace is and how it is used to organize and control access to applications.

  4. Click Save. Flow Designer opens with an empty canvas.

Step 2: Add the GCS Reader source

  1. In the component search field, enter GCS Reader and select it from the Sources palette.

  2. Drag it onto the canvas.

  3. In the properties panel, set the following properties:

    Property

    Value

    Name

    OrdersGCSSource

    Bucket Name

    meridian-retail-orders-raw

    Folder Name

    Your folder path, for example orders/ecommerce/2026/09/18/

    Include Subfolders

    false (unless your files are nested deeper)

    Object Filter

    *.csv

    Note

    GCS Reader's Object Filter supports only the * wildcard character. If your CSV file names vary, keep the filter broad (*.csv) rather than trying to match an exact pattern.

    Project Id

    Your GCP project ID

    Service Account Key

    The path to your service account JSON key file

    Polling Interval

    5000

    Use Streaming

    true

    Parser

    DSV Parser (see Step 3)

  4. Leave other properties at their defaults.

  5. Click Save.

    gcs-to-clickhouse_2.png

Step 3: Configure the DSV Parser

GCS Reader transfers raw file bytes and cannot interpret the table or column layout of the files it reads. A parser is required to interpret that structure into individual fields; without one, downstream components cannot receive usable data. Because the sample data is comma-delimited with a header row, this application uses DSV Parser (delimiter-separated values). See Striim concepts for how readers and parsers work together, and DSV Parser for its supported properties and reader combinations.

  1. In the Parser section of the same properties panel, which appears after you save the source, select DSV Parser.

  2. Set the following properties:

    Property

    Value

    Header

    true

    Column Delimiter

    ,

    Trim Quote

    true

  3. For New Output, enter OrdersRawStream.

  4. Click Save.

    gcs-to-clickhouse_3.png

OrdersRawStream now carries each row as a WAEvent with an untyped data[] array. The column values are all strings, positioned by index in the order they appear in the CSV file (data[0] is order_id, data[1] is customer_id, and so on). The next step converts them to typed values before the data reaches ClickHouse Writer.

Step 4: Write the CQ to type the data

ClickHouse Writer, like most database targets, requires a typed input stream: one where each field has a name and a data type, rather than a positional array of strings. GCS Reader's parsed output does not carry this typing on its own, so a continuous query (CQ) is used to bridge the two.

  1. Click the OrdersRawStream stream icon, click the + that appears, and select Connect next CQ component.

  2. Name the CQ OrdersCQ.

  3. In the Query field, enter the following, replacing OrdersRawStream if you named the source's output differently:

    SELECT
        data[0]             AS order_id,
        data[1]             AS customer_id,
        TO_DATE(data[2])    AS order_timestamp,
        data[3]             AS product_sku,
        data[4]             AS product_name,
        data[5]             AS category,
        TO_INT(data[6])     AS quantity,
        TO_DOUBLE(data[7])  AS unit_price,
        TO_INT(data[8])     AS discount_percent,
        data[9]             AS currency,
        TO_DOUBLE(data[10]) AS total_amount,
        data[11]            AS sales_channel,
        data[12]            AS region,
        data[13]            AS payment_method,
        data[14]            AS shipping_status
    FROM OrdersRawStream;

    Each AS <field name> assigns a name to that position in the incoming data[] array, and each TO_* function converts the field from its original string representation into a typed value:

    • TO_DATE(data[2]) converts the order_timestamp string into a date and time value.

    • TO_INT is used for the whole-number fields quantity and discount_percent.

    • TO_DOUBLE is used for the decimal fields unit_price and total_amount.

    • Fields without a TO_* function remain strings, which is their natural type.

  4. For Output, select New Output and enter OrdersTypedStream.

  5. Click Save.

Note

Selecting New Output creates the underlying type and stream. It is the Flow Designer equivalent of writing CREATE TYPE and CREATE STREAM ... OF in TQL. Striim infers each field's name and data type from the AS aliases and TO_* conversions in your query, so you do not need to declare the type separately.

Step 5: Add the ClickHouse Writer target

  1. Click the OrdersTypedStream stream icon, click +, and select Connect next Target component.

  2. In the Adapter drop-down in the right-hand panel, search for ClickHouse Writer and select it.

  3. Set the following properties:

    Property

    Value

    Name

    OrdersClickHouseTarget

    Connection Profile Name

    Your ClickHouse connection profile

    Tables

    Your target database and table, for example Orders_GCS.orders

    Table Engine

    MergeTree

    Mode

    APPENDONLY

    Upload Policy

    eventcount:10000,interval:30s

    CDDL Action

    Process

  4. Click Save.

    gcs-to-clickhouse_5.png
    gcs-to-clickhouse_6.png

Step 6: Save, deploy, and start

  1. Click Save at the top of Flow Designer to save the application.

  2. Click Deploy and confirm the deployment group.

  3. After the application is deployed, click Start App, or select Start from the application's action menu.

    gcs-to-clickhouse_7.png

When the application is running, event counts appear above each component, showing throughput per second.

Step 7: Verify the data movement

To verify that the rows reached the ClickHouse target table, run a row count against it:

SELECT count(*) AS total_rows
FROM Orders_GCS.orders;

Monitoring your pipeline

You may monitor your pipeline using any of the options discussed in Monitoring.

Setting up alerts for your pipeline

System alerts for potential problems are automatically enabled. You may also create custom alerts. For more information. (see Alerting in Striim).Sending alerts about servers and applications

Scaling up for better performance

When a single reader can not keep up with the data being added to your source, create multiple readers. Use the Tables property to distribute tables among the readers:

  • Assign each table to only one reader.

  • When tables are related (by primary or foreign key) or to ensure transaction integrity among a set of tables, assign them all to the same reader.

  • When dividing tables among readers, distribute them according to how busy they are rather than simply by the number of tables. For example, if one table generates 50% of the entries in the CDC log, you might assign it and any related tables to one reader and all the other tables to another.

The following is a simple example of how you could use two Oracle Readers, with one reading a very busy table and the other reading the rest of the tables in the same schema:

CREATE SOURCE OracleSource1 USING OracleReader ( 
  FetchSize: 1,
  Compression: false,
  Username: 'myname',
  Password: '7ip2lhUSP0o=',
  ConnectionURL: '198.51.100.15:1521:orcl',
  ReaderType: 'LogMiner',
  Tables: 'MYSCHEMA.VERYBUSYTABLE'
) 
OUTPUT TO OracleSourcre_ChangeDataStream;

CREATE SOURCE OracleSource2 USING OracleReader ( 
  FetchSize: 1,
  CommittedTransactions: true,
  Compression: false,
  Username: 'myname',
  Password: '7ip2lhUSP0o=',
  ConnectionURL: '198.51.100.15:1521:orcl',
  ReaderType: 'LogMiner',
  Tables: 'MYSCHEMA.%',
  ExcludedTables: 'MYSCHEMA.VERYBUSYTABLE'
) 
OUTPUT TO OracleSourcre_ChangeDataStream;

When a single ClickHouse Writer instance can not keep up with the data it is receiving from the source (that is, when it is backpressured), use the Parallel Threads property to create additional instances and Striim will automatically distribute data among them (see Creating multiple writer instances (parallel threads)).