Sample pipeline: SQL Server to ClickHouse

​

What you will build

​

Before you begin

• A running Striim for ClickHouse service on the Premium plan (see Get started).
• A SQL Server instance reachable from Striim, with SQL Server Agent running, and permission to enable change data capture. The steps below work on SQL Server on a VM or Amazon RDS for SQL Server.
• A ClickHouse service, a database named sales, a Striim user with the required privileges, and a ClickHouse connection profile.

​

Step 1: Prepare SQL Server

CREATE DATABASE salesdb;
GO
USE salesdb;
GO
CREATE SCHEMA sales;
GO
CREATE TABLE sales.orders (
  order_id    INT           NOT NULL PRIMARY KEY,
  customer_id INT           NOT NULL,
  status      VARCHAR(20)   NOT NULL,
  total       DECIMAL(10,2) NOT NULL,
  placed_at   DATETIME2     NOT NULL
);
INSERT INTO sales.orders VALUES
  (1, 101, 'NEW',     120.50, SYSDATETIME()),
  (2, 102, 'NEW',      75.00, SYSDATETIME()),
  (3, 103, 'NEW',     310.25, SYSDATETIME());
GO

USE salesdb;
EXEC sys.sp_cdc_enable_db;

EXEC msdb.dbo.rds_cdc_enable_db 'salesdb';

USE salesdb;
CREATE LOGIN striim WITH PASSWORD = '<strong password>';
CREATE USER striim FOR LOGIN striim;
EXEC sp_addrolemember @rolename = db_owner, @membername = striim;

​

Step 2: Create the automated pipeline

1. In Striim, select Apps > Create an App.
2. Search for and select SQL Server as the source. Select ClickHouse as the target.
3. Select Get Started and choose the automated pipeline (initial load plus CDC).
4. Name the pipeline, for example SqlServerToClickHouse, choose a namespace, and select Next.
5. Enter the SQL Server connection details: host and port (<host>:1433), database name salesdb, and the striim user. Select Next. Striim validates the connection. If validation fails, select Back and correct the settings; you must pass validation to continue.
6. Select the schemas to replicate. Choose sales; do not select system schemas.
7. Select the tables to replicate from each schema. Choose orders.
8. Select your ClickHouse connection profile and set Table Engine to ReplacingMergeTree. This engine suits CDC sources that send a full row image on update.
9. Optionally edit the table mapping. Confirm sales.orders maps to sales.orders in ClickHouse.
10. Review the settings and select Save & Start.

​

Step 3: Verify the initial load in ClickHouse

SHOW CREATE TABLE sales.orders;

SELECT order_id, customer_id, status, total
FROM sales.orders FINAL
WHERE __STRIIM_ISDELETED = 0
ORDER BY order_id;

​

Step 4: Stream changes

USE salesdb;
UPDATE sales.orders SET status = 'SHIPPED' WHERE order_id = 2;
DELETE FROM sales.orders WHERE order_id = 3;
INSERT INTO sales.orders VALUES (4, 104, 'NEW', 42.00, SYSDATETIME());

​

Build the same pipeline with Striim Clara

​

Clean up

• Stop and undeploy the two Striim applications from the Apps page.
• SQL Server keeps capturing change data even after you undeploy the Striim applications with the default reader settings. To stop capturing data for this sample, run sys.sp_cdc_disable_table for sales.orders, or sys.sp_cdc_disable_db if no other tables in the database require CDC. See the SQL Server documentation for details. Alternatively, follow Striim’s Auto Disable Table CDC procedure.
• Drop the sample tables: DROP TABLE sales.orders; in both systems.

​

Next steps

• Try other sources from the supported source list.
• Add in-flight transformations or masking in Flow Designer. See Pipelines in the Striim documentation.
• Build dashboards on the replicated data with ClickHouse visualization integrations.