public async Task ShouldInsertIntoNestedTableViaBulk()
{
using var bulkCopy = new ClickHouseBulkCopy(connection)
{
DestinationTableName = Table,
};
var row1 = new object[] { 1, new[] { 1, 2, 3 }, new[] { "v1", "v2", "v3" } };
var row2 = new object[] { 2, new[] { 4, 5, 6 }, new[] { "v4", "v5", "v6" } };
await bulkCopy.WriteToServerAsync(new[] { row1, row2 }, CancellationToken.None);
using var reader = await connection.ExecuteReaderAsync("SELECT * FROM test.nested ORDER BY id ASC");
Assert.IsTrue(reader.Read());
var values = reader.GetFieldValues();
Assert.AreEqual(1, values[0]);
CollectionAssert.AreEquivalent(new[] { 1, 2, 3 }, values[1] as IEnumerable);
CollectionAssert.AreEquivalent(new[] { "v1", "v2", "v3" }, values[2] as IEnumerable);
Assert.IsTrue(reader.Read());
values = reader.GetFieldValues();
Assert.AreEqual(2, values[0]);
CollectionAssert.AreEquivalent(new[] { 4, 5, 6 }, values[1] as IEnumerable);
CollectionAssert.AreEquivalent(new[] { "v4", "v5", "v6" }, values[2] as IEnumerable);
}
public async Task ShouldExecuteSingleValueInsertViaBulkCopy(string clickHouseType, object insertedValue)
{
var targetTable = $"temp.b_{clickHouseType}";
targetTable = targetTable
.Replace("(", null)
.Replace(")", null)
.Replace(",", null)
.Replace(" ", null)
.Replace("'", null)
.Replace("/", null);
clickHouseType = clickHouseType.Replace("Enum", "Enum('a' = 1, 'b' = 2)");
await connection.ExecuteStatementAsync($"TRUNCATE TABLE IF EXISTS {targetTable}");
await connection.ExecuteStatementAsync($"CREATE TABLE IF NOT EXISTS {targetTable} (value {clickHouseType}) ENGINE Memory");
using var bulkCopy = new ClickHouseBulkCopy(connection)
{
DestinationTableName = targetTable,
};
await bulkCopy.WriteToServerAsync(Enumerable.Repeat(new[] { insertedValue }, 1));
using var reader = await connection.ExecuteReaderAsync($"SELECT * from {targetTable}");
Assert.IsTrue(reader.Read(), "Cannot read inserted data");
reader.AssertHasFieldCount(1);
var data = reader.GetValue(0);
Assert.AreEqual(insertedValue, data);
}
public async Task <object> ShouldExecuteSimpleSelectQuery(string sql)
{
using var reader = await connection.ExecuteReaderAsync(sql);
reader.AssertHasFieldCount(1);
var result = reader.GetEnsureSingleRow().Single();
return(result);
}
static async Task Main(string[] args)
{
try
{
using var connection = new ClickHouseConnection(GetConnectionString(args));
await connection.ExecuteStatementAsync("CREATE DATABASE IF NOT EXISTS test");
await connection.ExecuteStatementAsync("TRUNCATE TABLE IF EXISTS test.dotnet_test");
await connection.ExecuteStatementAsync("CREATE TABLE IF NOT EXISTS test.dotnet_test (`age` Int32, `name` String) Engine = Memory");
using var command = connection.CreateCommand();
command.AddParameter("name", "Linus Torvalds");
command.AddParameter("age", 51);
command.CommandText = "INSERT INTO test.dotnet_test VALUES({age:Int32}, {name:String})";
await command.ExecuteNonQueryAsync();
using var result1 = await connection.ExecuteReaderAsync("SELECT * FROM test.dotnet_test");
while (result1.Read())
{
var values = new object[result1.FieldCount];
result1.GetValues(values);
foreach (var row in values)
{
Console.WriteLine(row);
}
}
using var result2 = await connection.ExecuteReaderAsync(selectSql);
while (result2.Read())
{
var values = new object[result2.FieldCount];
result2.GetValues(values);
foreach (var row in values)
{
Console.WriteLine(row);
}
}
}
catch (Exception e)
{
Console.Error.WriteLine(e);
Environment.ExitCode = 1;
}
}
public async Task WriteToServerAsync(IEnumerable <object[]> rows, IReadOnlyCollection <string> columns, CancellationToken token)
{
if (rows is null)
{
throw new ArgumentNullException(nameof(rows));
}
if (string.IsNullOrWhiteSpace(DestinationTableName))
{
throw new InvalidOperationException(Resources.DestinationTableNotSetMessage);
}
ClickHouseType[] columnTypes = null;
string[] columnNames = columns?.ToArray();
using (var reader = (ClickHouseDataReader)await connection.ExecuteReaderAsync($"SELECT {GetColumnsExpression(columns)} FROM {DestinationTableName} LIMIT 0"))
{
columnTypes = reader.GetClickHouseColumnTypes();
columnNames ??= reader.GetColumnNames();
}
for (int i = 0; i < columnNames.Length; i++)
{
columnNames[i] = columnNames[i].EncloseColumnName();
}
var tasks = new Task[MaxDegreeOfParallelism];
for (var i = 0; i < tasks.Length; i++)
{
tasks[i] = Task.CompletedTask;
}
foreach (var batch in rows.Batch(BatchSize))
{
token.ThrowIfCancellationRequested();
while (true)
{
var completedTaskIndex = Array.FindIndex(tasks, t => t.IsCompleted);
if (completedTaskIndex >= 0)
{
// propagate exception if one happens
// 'await' instead of 'Wait()' to avoid dealing with AggregateException
await tasks[completedTaskIndex].ConfigureAwait(false);
var task = PushBatch(batch, columnTypes, columnNames, token);
tasks[completedTaskIndex] = task;
break; // while (true); go to next batch
}
else
{
await Task.WhenAny(tasks).ConfigureAwait(false);
}
}
}
await Task.WhenAll(tasks).ConfigureAwait(false);
}
public async Task WriteToServerAsync(IEnumerable <object[]> rows, IReadOnlyCollection <string> columns, CancellationToken token)
{
if (rows is null)
{
throw new ArgumentNullException(nameof(rows));
}
if (string.IsNullOrWhiteSpace(DestinationTableName))
{
throw new InvalidOperationException(Resources.DestinationTableNotSetMessage);
}
ClickHouseType[] columnTypes = null;
using (var reader = (ClickHouseDataReader)await connection.ExecuteReaderAsync($"SELECT {GetColumnsExpression(columns)} FROM {DestinationTableName} LIMIT 0"))
{
columnTypes = reader.GetClickHouseColumnTypes();
}
var tasks = new Task[MaxDegreeOfParallelism];
for (var i = 0; i < tasks.Length; i++)
{
tasks[i] = Task.CompletedTask;
}
foreach (var batch in rows.Batch(BatchSize))
{
token.ThrowIfCancellationRequested();
while (true)
{
var completedTaskIndex = Array.FindIndex(tasks, t => t.Status == TaskStatus.RanToCompletion || t.Status == TaskStatus.Faulted || t.Status == TaskStatus.Canceled);
if (completedTaskIndex >= 0)
{
await tasks[completedTaskIndex].ConfigureAwait(false); // to receive exception if one happens
var task = PushBatch(batch, columnTypes, token);
tasks[completedTaskIndex] = task;
break;
}
else
{
await Task.WhenAny(tasks).ConfigureAwait(false);
}
}
}
await Task.WhenAll(tasks).ConfigureAwait(false);
}
