ClickHouse: .NET (ClickHouse.Client)

ADO.NET over the ClickHouse HTTP interface, with ClickHouseBulkCopy for the load.

The only change

# CLICKHOUSE_CONNECTION
 Host=clickhouse.internal;Port=8443;Protocol=https;Username=app;Database=default
 Host=warehouse.internal;Port=8443;Protocol=https;Username=app;Database=default
# CLICKHOUSE_PASSWORD
 ClickHouse password
 MachWarehouse password

Run

dotnet run

Library: ClickHouse.Client 7.14.0, .NET 10.

Source

The program as it is kept and tested in the Mach Database Warehouse repository; highlighted lines read the connection setting.

Program.cs

// Loads the demo events with ClickHouse.Client and prints revenue per country.
// Plain ClickHouse.Client code: nothing here knows about MachWarehouse.
using System.Globalization;
using ClickHouse.Client.ADO;
using ClickHouse.Client.Copy;

var connectionString = Environment.GetEnvironmentVariable("CLICKHOUSE_CONNECTION")
    ?? "Host=localhost;Port=8443;Protocol=https;Username=default;Database=default";
var password = Environment.GetEnvironmentVariable("CLICKHOUSE_PASSWORD") ?? "";
var dataset = Path.Combine("..", "..", "dataset");

using var connection = new ClickHouseConnection($"{connectionString};Password={password}");
await connection.OpenAsync();

using (var ddl = connection.CreateCommand())
{
    ddl.CommandText = await File.ReadAllTextAsync(Path.Combine(dataset, "clickhouse.sql"));
    await ddl.ExecuteNonQueryAsync();
}

var rows = File.ReadLines(Path.Combine(dataset, "events.csv")).Skip(1).Select(line =>
{
    var f = line.Split(',');
    return new object[]
    {
        DateOnly.Parse(f[0], CultureInfo.InvariantCulture),
        DateTime.Parse(f[1], CultureInfo.InvariantCulture),
        ulong.Parse(f[2], CultureInfo.InvariantCulture),
        f[3],
        f[4],
        decimal.Parse(f[5], CultureInfo.InvariantCulture),
    };
});
using (var bulk = new ClickHouseBulkCopy(connection) { DestinationTableName = "events" })
{
    await bulk.InitAsync();
    await bulk.WriteToServerAsync(rows);
}

using var query = connection.CreateCommand();
query.CommandText = "SELECT country, count() AS events, sum(revenue) AS revenue FROM events GROUP BY country ORDER BY revenue DESC";
using var reader = await query.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine($"{reader.GetString(0)}\t{reader.GetValue(1)}\t{reader.GetValue(2)}");
}

Example.csproj

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="ClickHouse.Client" Version="7.14.0" />
  </ItemGroup>
</Project>