SharpJuice.ClickHouse 1.0.7

Writing .Net objects to ClickHouse

👁 NuGet

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

Registration

 ClickHouseConnectionSettings connectionSettings = ...;

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

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 8)

Benchmark source code

Thanks to @deniskuzmin and @LegaNoga