Octonica.ClickHouseClient

ClickHouse allows passing arbitrary tables along with a query. In ClickHouseClient, these are managed via the TableProviders collection on a ClickHouseCommand.

To implement a custom table, implement the IClickHouseTableProvider interface. The library provides a default implementation, ClickHouseTableProvider, which allows you to build tables in a columnar format similar to ClickHouseColumnWriter.

How to use ClickHouseTableProvider:

1. Instantiate ClickHouseTableProvider with a table name and the expected row count.
2. Use .AddColumn(name, data) to add columns. Column settings (like ClickHouseDbType or IsNullable) can be configured on the returned column object.
3. Add the provider to cmd.TableProviders.
4. Reference the table name directly in your SQL query without special syntax.

using var connection = new ClickHouseConnection(connectionStr);
connection.Open();

var cmd = connection.CreateCommand("SELECT ptable.id, ptable.user, ptable.ip FROM ptable");

var users = new[] {"user1", "user2", "admin1", "admin2"};
var ips = new[] {"1.1.1.1", "2.2.2.2", "127.0.0.1", "::ffff:192.0.2.1"};

var pTableProvider = new ClickHouseTableProvider("ptable", users.Length);
pTableProvider.AddColumn("id", Enumerable.Range(1, users.Length));
pTableProvider.AddColumn("user", users);

// Configure column settings
var ipColumn = pTableProvider.AddColumn("ip", ips);
ipColumn.ClickHouseDbType = ClickHouseDbType.IpV6;
ipColumn.IsNullable = false;

cmd.TableProviders.Add(pTableProvider);

using var reader = cmd.ExecuteReader();
while(reader.Read())
{
  // Reading the data
}

You can use a ClickHouseTableProvider to efficiently pass a set of values for use in an IN clause in your query.

using var connection = new ClickHouseConnection(connectionStr);
connection.Open();

// Reference the table name 'param_table' directly in the IN clause
var cmd = connection.CreateCommand("SELECT toInt32(number) FROM numbers(100000) WHERE number IN param_table");

var tableProvider = new ClickHouseTableProvider("param_table", 100);
tableProvider.AddColumn(Enumerable.Range(500, 100)); // Providing 100 values

cmd.TableProviders.Add(tableProvider);

using var reader = cmd.ExecuteReader();
while(reader.Read())
{
  // Reading only rows where number is in the provided range
}

using var connection = new ClickHouseConnection(connectionStr);
connection.Open();

var cmd = connection.CreateCommand("SELECT toInt32(number) FROM numbers(100000) WHERE number IN param_table");

var tableProvider = new ClickHouseTableProvider("param_table", 100);
tableProvider.AddColumn(Enumerable.Range(500, int.MaxValue / 2));

cmd.TableProviders.Add(tableProvider);

using var reader = cmd.ExecuteReader();
while(reader.Read())
{
  // Reading only 100 rows
}

If your INSERT query specifies column names (e.g., INSERT INTO table(col1, col2) VALUES), you can write data using an IReadOnlyDictionary<string, object?>. This allows you to map data to columns by their name rather than their position in an array.

1. Create the writer with a query like INSERT INTO table(col1, col2) VALUES.
2. Create a Dictionary<string, object?> where keys are the column names.
3. Call WriteTable or WriteTableAsync passing the dictionary and the rowCount.

avait using var connection = new ClickHouseConnection(connectionStr);
await connection.OpenAsync();

// Specify column names in the query
await using var writer = connection.CreateColumnWriter("INSERT INTO some_table(id, dt, val) VALUES");

var id = new List<Guid>();
var dt = new List<DateTime>();
var val = new List<decimal>();
// ... fill lists ...

var columns = new Dictionary<string, object?>
{
	["id"] = id,
	["dt"] = dt,
	["val"] = val
};

var rowCount = id.Count;
await writer.WriteTableAsync(columns, rowCount, CancellationToken.None);

For high-performance bulk data loading, use the proprietary ClickHouseColumnWriter API. This is accessed via ClickHouseConnection.CreateColumnWriterAsync(). It allows you to write data in a columnar format, which is significantly more efficient for ClickHouse than standard row-based inserts.

To use it, pass an array of columns (where each element is a collection representing a column) to WriteTableAsync.

using var conn = new ClickHouseConnection("Host=127.0.0.1");
conn.Open();
using var cmd = conn.CreateCommand("CREATE TABLE IF NOT EXISTS table_with_two_fields(id Int32, name String) engine Memory");
await cmd.ExecuteNonQueryAsync();

// generate values
List<int> ids = Enumerable.Range(1, 10_000).ToList();
List<string> names = ids.Select(i => $"Name #{i}").ToList();

// insert data
await using (var writer = await conn.CreateColumnWriterAsync("insert into table_with_two_fields(id, name) values", default))
{
	await writer.WriteTableAsync(new object[] { ids, names }, ids.Count, default);
}