SharpJuice.ClickHouse 1.1.0

Writing .Net objects to ClickHouse

Octonica.ClickHouseClient extension for easily writing objects to ClickHouse using bulk insert and ArrayPool for high performance and low memory allocation.

Works over either ClickHouse driver — see Registration.

Registration

There are two packages, one per driver:

Only the registration differs — For<T>(), AddColumn, AddNestedColumn, ArrayJoin and ITableWriter<T> are the same types from SharpJuice.ClickHouse.

    using SharpJuice.Clickhouse;

    ClickHouseConnectionSettings connectionSettings = ...;

    services.AddSingleton<IClickHouseConnectionFactory>(new ClickHouseConnectionFactory(connectionSettings));
    services.AddSingleton<ITableWriterBuilder, TableWriterBuilder>();	
    services.AddSingleton<ClickHouseRepository>();	

    using SharpJuice.Clickhouse.Driver;

    var settings = new ClickHouse.Driver.ADO.ClickHouseClientSettings("Host=localhost;Port=8123;Username=default");

    // the factory owns a ClickHouseClient, so let the container dispose it
    services.AddSingleton<IClickHouseConnectionFactory>(sp => new ClickHouseConnectionFactory(settings));
    services.AddSingleton<SharpJuice.Clickhouse.ITableWriterBuilder, TableWriterBuilder>();
    services.AddSingleton<ClickHouseRepository>();

For ClickHouse servers below 25.x set JsonReadMode = JsonReadMode.None and JsonWriteMode = JsonWriteMode.None in ClickHouseClientSettings.

Flat object

    public sealed class ClickHouseRepository 
    {
        private readonly ITableWriter<Order> _tableWriter;
        
        public ClickHouseRepository(ITableWriterBuilder tableWriterBuilder)
        {
            _tableWriter = tableWriterBuilder
                .For<Order>("table_name")
                .AddColumn("order_id", a => a.OrderId)
                .AddColumn("user_id", a => a.UserId)
                .AddColumn("created_at", a => a.CreatedAt)                
		.Build();
        }

        public async Task Add(IReadOnlyCollection<Order> orders, CancellationToken token)
        {
	    ...
            await _tableWriter.Insert(orders, token);
	    ...
        }
    }

Nested objects

    public sealed class ClickHouseRepository 
    {
        private readonly ITableWriter<Order> _tableWriter;
        
        public ClickHouseRepository(ITableWriterBuilder tableWriterBuilder)
        {
            _tableWriter = tableWriterBuilder
                .For<Order>("table_name")
                .AddColumn("order_id", a => a.OrderId)
                .AddColumn("date", a => a.Date)
                .AddNestedColumn("item", x => x.Items, c => c
                    .AddColumn("id", x => x.Id)
                    .AddColumn("quantity", x => x.Quantity)
                    .AddColumn("name", x => x.Name)
                    .AddColumn("price", x => x.Price))
                .AddNestedColumn(x => x.Discounts, c => c
                    .AddColumn("discount.id", x => x.Id)
                    .AddColumn("discount.name", x => x.Name)
                    .AddColumn("discount.value", x => x.Value))                
                .Build();          
        }

        public async Task Add(IReadOnlyCollection<Order> orders, CancellationToken token)
        {
	    ...
            await _tableWriter.Insert(orders, token);
	    ...
        }
    }

Array join (only one ArrayJoin per writer)

    public sealed class ClickHouseRepository 
    {
        private readonly ITableWriter<Order> _tableWriter;
        
        public ClickHouseRepository(ITableWriterBuilder tableWriterBuilder)
        {
            _tableWriter = tableWriterBuilder
                .For<Order>("table_name")
                .AddColumn("order_id", a => a.OrderId)
                .AddColumn("date", a => a.Date)
                .ArrayJoin(a => a.Items, c => c
                    .AddColumn("item_id", x => x.Id)
                    .AddColumn("item_quantity", x => x.Quantity)
                    .AddColumn("item_name", x => x.Name)
                    .AddColumn("item_price", x => x.Price))
		    .Build();
        }

        public async Task Add(IReadOnlyCollection<Order> orders, CancellationToken token)
        {
	    ...
            await _tableWriter.Insert(orders, token);
	    ...
        }
    }

Performance and memory allocation benchmark (.net 10)

Benchmark source code

All methods insert the same objects (4 scalar columns + 3 Nested groups) into the same table. Apple M3 Pro, ClickHouse 24.3 in local Docker.

Summary — Nested data, 30000 rows:

Full results below. Columns: Nested — via AddNestedColumn, flat arrays — array columns built by hand (.ToArray() in lambdas). InsertBinaryAsync takes object[] per row, ColumnWriter takes a dictionary of column arrays — both without SharpJuice.

Thanks to @deniskuzmin and @LegaNoga