Operations (ClickHouse)

For ClickHouse via RepoDb.ClickHouse.BulkOperations, every row-load goes through this package’s internal ClickHouseBulkCopy class — a thin adapter over ClickHouse.Driver’s own native Copy.ClickHouseBulkCopy, reshaped to the ColumnMappings-based API the rest of the library expects.

You must call GlobalConfiguration.Setup().UseClickHouse() once at application startup. See Get Started for ClickHouse for more options.

For BulkInsert, rows are written straight to the target table — there is no pseudo/staging table involved, since the only reason one would be needed (identityBehavior: ReturnIdentity) is never supported for ClickHouse.

For BulkMerge, BulkUpdate, BulkDelete and BulkDeleteByKey, a pseudo (staging) table is created for each call. The library writes to it via its internal ClickHouseBulkCopy, then cascades the changes to the original table using the correct SQL statement, then drops the pseudo table.

The data is brought together from the client application into the database server (at one-go). It then gets processed together at the same time.

No Identity Support

ClickHouse has no session-wide scope identity, sequence, or auto-increment mechanism. ClickHouseBulkImportIdentityBehavior.ReturnIdentity always throws NotSupportedException on BulkInsert and BulkMerge — the default KeepIdentity (writing the value exactly as supplied on the entity) is the only supported behavior. Assign key values on the entity yourself before calling any bulk operation.

No Reliable Affected-Row Count

ALTER TABLE ... UPDATE/DELETE are asynchronous mutations in ClickHouse — applied later by background merges, not immediately — and neither they nor a plain INSERT report a reliable “rows affected” figure through ClickHouse.Driver. Because of this, BulkMerge, BulkUpdate, BulkDelete and BulkDeleteByKey all report the number of rows staged into the pseudo table, not a confirmed post-mutation count. BulkInsert reports the number of rows submitted to the driver’s bulk copy.

Pseudo Table Type

The ClickHouseBulkImportPseudoTableType enum lets you choose between a Memory-engine table (session-isolated) and an ordinary MergeTree table for the staging table.

Every value currently resolves to Physical at runtime, including an explicit Memory and Auto’s row-count threshold (5,000 rows). The Memory-engine branch is fully implemented in the SQL builder, but the resolution step that picks between them currently maps every input to Physical unconditionally, until that path is enabled and verified against a live server. Because a physical pseudo table has no per-session isolation, avoid running concurrent bulk operations against the same target table until this is resolved.

Supported Objects

Below are the following objects supported by the bulk operations.

• System.DataTable
• System.Data.Common.DbDataReader
• IEnumerable<T>

The Staging Table Lifecycle

BulkMerge, BulkUpdate, BulkDelete and BulkDeleteByKey stage rows into a per-call pseudo table before running a mutation against it. Every call issues a DROP TABLE IF EXISTS followed by CREATE TABLE pseudo ENGINE = MergeTree ORDER BY tuple() AS SELECT ... FROM table WHERE (1 = 0) (or ENGINE = Memory when that branch is reached) to (re)create the pseudo table, shaped after the real table’s columns — ClickHouse has no CREATE TABLE IF NOT EXISTS ... AS SELECT “replace” form, so the explicit drop always comes first. The pseudo table is dropped again once the operation finishes.

Operation SQL Statements

Once all the data is in the pseudo (staging) table, the correct SQL statement is used to cascade the changes towards the original table.

BulkInsert writes directly into the target table and skips the staging table entirely.

For BulkDelete / BulkDeleteByKey

> ALTER TABLE OriginalTable DELETE
> WHERE (QualifierField1, QualifierField2) IN (SELECT QualifierField1, QualifierField2 FROM PseudoTempTable);

For BulkMerge

> ALTER TABLE OriginalTable UPDATE
>     Field3 = (SELECT S.Field3 FROM PseudoTempTable S WHERE S.QualifierField1 = OriginalTable.QualifierField1 LIMIT 1),
>     Field4 = (SELECT S.Field4 FROM PseudoTempTable S WHERE S.QualifierField1 = OriginalTable.QualifierField1 LIMIT 1)
> WHERE (QualifierField1) IN (SELECT QualifierField1 FROM PseudoTempTable);
>
> INSERT INTO OriginalTable (Field1, Field2, Field3, Field4)
> SELECT S.Field1, S.Field2, S.Field3, S.Field4 FROM PseudoTempTable S
> LEFT JOIN OriginalTable T ON (T.QualifierField1 = S.QualifierField1)
> WHERE T.QualifierField1 IS NULL;

ClickHouse has no native MERGE statement and mutations have no join alias, so BulkMerge always performs the same two-statement translation: an ALTER TABLE ... UPDATE against the rows that match on qualifiers (each updated column pulled via a correlated scalar subquery), followed by an INSERT ... SELECT guarded by a LEFT JOIN ... WHERE ... IS NULL anti-join for the rows that don’t. The anti-join assumes the first qualifier column is never legitimately NULL on a real, matched row.

For BulkUpdate

> ALTER TABLE OriginalTable UPDATE
>     Field3 = (SELECT S.Field3 FROM PseudoTempTable S WHERE S.QualifierField1 = OriginalTable.QualifierField1 LIMIT 1),
>     Field4 = (SELECT S.Field4 FROM PseudoTempTable S WHERE S.QualifierField1 = OriginalTable.QualifierField1 LIMIT 1)
> WHERE (QualifierField1) IN (SELECT QualifierField1 FROM PseudoTempTable);

Unlike BulkMerge, there is no anti-join INSERT branch — staged rows with no matching target row are left as-is, not inserted. If every staged column is also a qualifier (i.e. there is nothing left to update), the operation short-circuits and returns 0 without touching the database.

Special Arguments

The arguments below are available on most operations.

Async Methods

All the provided synchronous operations have an equivalent asynchronous (Async) counterpart.

BulkDelete

using (var connection = new ClickHouseConnection(connectionString))
{
    var people = connection.Query<Person>(e => e.IsActive == false);
    var deletedRows = connection.BulkDelete<Person>(people);
}

Or with qualifiers.

using (var connection = new ClickHouseConnection(connectionString))
{
    var deletedRows = connection.BulkDelete<Person>(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

BulkDeleteByKey

using (var connection = new ClickHouseConnection(connectionString))
{
    var primaryKeys = new object[] { 10045, 10046, 10047 };
    var deletedRows = connection.BulkDeleteByKey("Person", primaryKeys);
}

BulkInsert

using (var connection = new ClickHouseConnection(connectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people);
}

BulkMerge

using (var connection = new ClickHouseConnection(connectionString))
{
    var people = GetPeople(10000);
    var mergedRows = connection.BulkMerge(people);
}

Or with qualifiers.

using (var connection = new ClickHouseConnection(connectionString))
{
    var mergedRows = connection.BulkMerge(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

BulkUpdate

using (var connection = new ClickHouseConnection(connectionString))
{
    var people = GetPeople(10000);
    var updatedRows = connection.BulkUpdate(people);
}

Or with qualifiers.

using (var connection = new ClickHouseConnection(connectionString))
{
    var updatedRows = connection.BulkUpdate(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

Table of contents

• BulkDelete
• BulkDeleteByKey
• BulkInsert
• BulkMerge
• BulkUpdate