Skip to content
SPSync SPSync SPSync Docs
Back to spsync.io

Connect SPSync to ClickHouse

This guide covers ClickHouse Cloud and ClickHouse you run yourself. SPSync uses one destination kind, clickhouse, and the same connection string shape for both.

Before you start

Section titled “Before you start”
  • ClickHouse, reachable from the machine that runs SPSync.
  • Nothing to install on that machine. SPSync includes the client libraries.
  • Rights to create a database and a user.

ClickHouse has no schemas. Every table sits directly in one database. SPSync turns dataset_name into a table-name prefix instead, joined by ___, so a list table ks_tasks with dataset_name=sync becomes sync___ks_tasks. Several replications can share one database that way.

Create the database and a least-privilege user

Section titled “Create the database and a least-privilege user”

The script is from the ClickHouse documentation and has not been run here as written.

CREATE DATABASE spsync;
CREATE USER spsync_loader IDENTIFIED BY 'use-a-long-random-password';
GRANT ALL ON spsync.* TO spsync_loader;

ALL on the one database covers creating tables, inserting, deleting and altering. SPSync needs the alter and delete rights because it issues soft-delete and upsert statements as ClickHouse mutations. On ClickHouse Cloud, create the user in the console or with the same statements.

Allow SPSync to reach it

Section titled “Allow SPSync to reach it”

SPSync uses two ports on the same host:

PortUsed for
9440Native protocol with TLS (9000 without TLS). Queries.
8443HTTPS interface. Loading data, soft deletes and Test connection.

Those are the ClickHouse Cloud defaults. A self-hosted server listens for HTTP on 8123 by default, not 8443, so give SPSync the port with http_port (below).

  • ClickHouse Cloud: under the service’s Security settings, add the address SPSync connects from to the IP access list.
  • Self-hosted: allow both ports in the host firewall and security group, and set the server’s listen_host so it accepts outside connections.

Encrypt the connection

Section titled “Encrypt the connection”

Add secure=1 to the connection string. It turns on TLS for both ports and matches ClickHouse Cloud and any self-hosted server that terminates TLS. A local test server without TLS uses secure=0 and port 9000.

clickhouse://spsync_loader:PASSWORD@HOST:9440/spsync?secure=1

For a self-hosted server whose HTTP port is not 8443, add http_port:

clickhouse://spsync_loader:PASSWORD@HOST:9440/spsync?secure=1&http_port=8123

Authentication SPSync supports today

Section titled “Authentication SPSync supports today”

A user name and password in the connection string. SPSync accepts nothing else for ClickHouse. URL-encode any @, : or / in the password.

Connect from SPSync

Section titled “Connect from SPSync”

  • Desktop app: open the Destination screen, choose ClickHouse, paste the string into Connection string and choose Test connection.

  • CLI:

    Terminal window
    export SPSYNC_CLICKHOUSE_DSN='clickhouse://spsync_loader:PASSWORD@HOST:9440/spsync?secure=1'
    spsync export-config --output replication.yaml \
      --site-url https://contoso.sharepoint.com/sites/x --list Projects \
      --pipeline-name spsync_dev --prefix dev \
      --destination-kind clickhouse --connection-string-env SPSYNC_CLICKHOUSE_DSN \
      --dataset-name sync --client-id <entra-app-client-id>

  • AI assistant: with the admin MCP server connected, ask for the destination requirements, then let the assistant create the destination. You enter the string on the secure entry page.

What Test connection and doctor check

Section titled “What Test connection and doctor check”

Test connection connects over the HTTP port (http_port, or 8443 when not given) and runs SELECT 1. spsync doctor reports the destination probe as destination-connectivity. A pass proves the host, the HTTP port, TLS, the user name and password and the database. It does not open the native port 9440, which loading and queries use, so a firewall that allows only the HTTP port passes the test and fails the first run. It also does not prove the user can create tables.

Common errors

Section titled “Common errors”
What you seeWhat it means and the fix
Connection timed out on 9440 or 8443A firewall, security group or the Cloud IP access list is blocking a port. Open both.
Authentication failed: password is incorrectWrong user or password, or the user cannot connect from SPSync’s address.
Test connection passes, the run cannot connectThe native port 9440 is blocked, or secure does not match the port (9440 needs secure=1, 9000 needs secure=0).
Connects to 8443 on a self-hosted server and failsThe HTTP interface is on 8123. Add &http_port=8123.
Not enough privileges or ACCESS_DENIEDThe user lacks rights on the database. Run the grant above.
Database ... does not existCreate the database named in the path of the connection string.