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); }