public async Task ShouldInsertIntoTableWithLotsOfColumns()
{
if (!connection.SupportedFeatures.HasFlag(FeatureFlags.SupportsInlineQuery))
{
Assert.Inconclusive("This test is only valid for versions which support 'inline' POST body query");
}
var tblName = "test.bulk_long_columns";
var columnCount = 3900;
//Generating create tbl statement with a lot of columns
var query = $"CREATE TABLE IF NOT EXISTS {tblName}(\n";
var columns = Enumerable.Range(1, columnCount)
.Select(x => $" some_loooooooooooooonnnnnnnnnnnngggggggg_column_name_{x} Int32");
query += string.Join(",\n", columns);
query += ")\n ENGINE = MergeTree()\n ORDER BY (some_loooooooooooooonnnnnnnnnnnngggggggg_column_name_1)";
//Create tbl in db
await connection.ExecuteStatementAsync(query);
var bulkCopy = new ClickHouseBulkCopy(connection)
{
DestinationTableName = tblName
};
var rowToInsert = new[] { Enumerable.Range(1, columnCount).Select(x => (object)x).ToArray() };
await bulkCopy.WriteToServerAsync(rowToInsert);
}
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());
reader.AssertHasFieldCount(1);
var data = reader.GetValue(0);
Assert.AreEqual(insertedValue, data);
}
public void SaveLogPosition(InformationSystemsBase system, FileInfo logFileInfo, EventLogPosition position)
{
using (ClickHouseBulkCopy bulkCopyInterface = new ClickHouseBulkCopy(_connection)
{
DestinationTableName = "LogFiles",
BatchSize = 100000
})
{
long logFileNewId = GetLogFileInfoNewId(system);
IEnumerable <object[]> values = new List <object[]>()
{
new object[]
{
system.Name,
logFileNewId,
logFileInfo.Name,
logFileInfo.CreationTimeUtc,
logFileInfo.LastWriteTimeUtc,
position.EventNumber,
position.CurrentFileReferences.Replace("\\", "\\\\"),
position.CurrentFileData.Replace("\\", "\\\\"),
position.StreamPosition ?? 0
}
}.AsEnumerable();
var bulkResult = bulkCopyInterface.WriteToServerAsync(values);
bulkResult.Wait();
}
}
public async Task ShouldExecuteSingleValueInsertViaBulkCopy(string clickHouseType, object insertedValue)
{
var targetTable = "test." + SanitizeTableName($"bulk_single_{clickHouseType}");
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,
MaxDegreeOfParallelism = 2,
BatchSize = 100
};
await bulkCopy.WriteToServerAsync(Enumerable.Repeat(new[] { insertedValue }, 1));
Assert.AreEqual(1, bulkCopy.RowsWritten);
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, "Original and actually inserted values differ");
}
public async Task ExceptionOnInsertIntoMissmachedTypedColumn()
{
var targetTable = $"test.exception_table";
await connection.ExecuteStatementAsync($"TRUNCATE TABLE IF EXISTS {targetTable}");
await connection.ExecuteStatementAsync($"CREATE TABLE IF NOT EXISTS {targetTable} (value1 Nullable(UInt8), value2 Nullable(Float32), value3 Nullable(Int8)) ENGINE TinyLog");
using var bulkCopy = new ClickHouseBulkCopy(connection)
{
DestinationTableName = targetTable,
};
var data = Enumerable.Repeat(new object[] { 5, 5 }, 5).ToList();
data.Add(new object[] { 50, "a5555" });
data.AddRange(Enumerable.Repeat(new object[] { 5, 5 }, 5));
bulkCopy.OnBulkCopyException += (blk, row_data) =>
{
Assert.AreEqual(50, row_data[0]);
};
try
{
await bulkCopy.WriteToServerAsync(data, new[] { "value1, value2" });
}
catch (Exception expMsg) {
Console.WriteLine(expMsg.Message);
}
}
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);
}
protected override int RawExecuteAffrows()
{
var affrows = 0;
Exception exception = null;
Aop.CurdBeforeEventArgs before = null;
if (_source.Count > 1)
{
try
{
before = new Aop.CurdBeforeEventArgs(_table.Type, _table, Aop.CurdType.Insert, null, _params);
_orm.Aop.CurdBeforeHandler?.Invoke(this, before);
using var bulkCopyInterface = new ClickHouseBulkCopy(_orm.Ado.MasterPool.Get().Value as ClickHouseConnection)
{
DestinationTableName = _table.DbName,
BatchSize = _source.Count
};
var data = ToDataTable();
bulkCopyInterface.WriteToServerAsync(data, default).Wait();
return(affrows);
}
catch (Exception ex)
{
exception = ex;
throw ex;
}
finally
{
var after = new Aop.CurdAfterEventArgs(before, exception, affrows);
_orm.Aop.CurdAfterHandler?.Invoke(this, after);
}
}
else
{
var sql = this.ToSql();
before = new Aop.CurdBeforeEventArgs(_table.Type, _table, Aop.CurdType.Insert, sql, _params);
_orm.Aop.CurdBeforeHandler?.Invoke(this, before);
try
{
affrows = _orm.Ado.ExecuteNonQuery(_connection, null, CommandType.Text, sql, _commandTimeout, _params);
}
catch (Exception ex)
{
exception = ex;
throw ex;
}
finally
{
var after = new Aop.CurdAfterEventArgs(before, exception, affrows);
_orm.Aop.CurdAfterHandler?.Invoke(this, after);
}
return(affrows);
}
}
/// <inheritdoc />
public async Task WriteBatch(IReadOnlyCollection <string> columns, List <object[]> values, string tableName)
{
await using var connection = GetConnection();
using var command = new ClickHouseBulkCopy(connection)
{
MaxDegreeOfParallelism = Options.MaxDegreeOfParallelism,
BatchSize = Options.EventsFlushCount,
DestinationTableName = connection.Database + "." + tableName
};
await command.WriteToServerAsync(values, columns);
}
public async Task <long> BulkInsert <T>(string tableName, List <T> bulk)
{
using var bulkCopyInterface = new ClickHouseBulkCopy(_clickHouseConnection)
{
DestinationTableName = tableName,
BatchSize = bulk.Count
};
var(items, columns) = _commandFormatter.BulkInsert(bulk);
await bulkCopyInterface.WriteToServerAsync(items, columns);
return(bulkCopyInterface.RowsWritten);
}
public async Task WriteEventLogDataAsync(List <EventLogItem> entities,
CancellationToken cancellationToken = default)
{
await CreateConnectionAsync(cancellationToken);
using var copy = new ClickHouseBulkCopy(_connection)
{
DestinationTableName = TableName,
BatchSize = entities.Count
};
var data = entities.Select(item => new object[]
{
item.FileName ?? "",
item.EndPosition,
item.LgfEndPosition,
item.Id,
item.DateTime,
item.TransactionStatus ?? "",
item.TransactionDateTime == DateTime.MinValue ? new DateTime(1970, 1, 1) : item.TransactionDateTime,
item.TransactionNumber,
item.UserUuid ?? "",
item.User ?? "",
item.Computer ?? "",
item.Application ?? "",
item.Connection,
item.Event ?? "",
item.Severity ?? "",
item.Comment ?? "",
item.MetadataUuid ?? "",
item.Metadata ?? "",
item.Data ?? "",
item.DataPresentation ?? "",
item.Server ?? "",
item.MainPort,
item.AddPort,
item.Session
}).AsEnumerable();
try
{
await copy.WriteToServerAsync(data, cancellationToken);
}
catch (Exception ex)
{
_logger?.LogError(ex, $"Failed to write data to {_databaseName}");
throw;
}
_logger?.LogDebug($"{entities.Count} items were being written to {_databaseName}");
}
public override async Task <BenchmarkResult> Run()
{
var count = Convert.ToInt32(Duration.TotalSeconds * 5000000.0);
Console.WriteLine("Preparing data");
var values = Enumerable.Range(0, count).Select(i => new object[] { (long)i }).ToList();
Console.WriteLine("Running benchmark");
var targetDatabase = "benchmark";
var targetTable = $"{targetDatabase}.bulk_insert_test";
var stopwatch = new Stopwatch();
// Create database and table for benchmark
var targetConnections = GetConnections(16);
var targetConnection = targetConnections.First();
await targetConnection.ExecuteStatementAsync($"CREATE DATABASE IF NOT EXISTS {targetDatabase}");
await targetConnection.ExecuteStatementAsync($"TRUNCATE TABLE IF EXISTS {targetTable}");
await targetConnection.ExecuteStatementAsync($"CREATE TABLE IF NOT EXISTS {targetTable} (col1 Int64) ENGINE Memory");
targetConnection.ChangeDatabase(targetDatabase);
using var bulkCopyInterface = new ClickHouseBulkCopy(targetConnections)
{
DestinationTableName = targetTable,
BatchSize = 1000000
};
stopwatch.Start();
await bulkCopyInterface.WriteToServerAsync(values);
stopwatch.Stop();
// Verify we've written expected number of rows
//Assert.AreEqual(count, bulkCopyInterface.RowsWritten);
//Assert.AreEqual(count, Convert.ToInt32(await targetConnection.ExecuteScalarAsync($"SELECT COUNT(*) FROM {targetTable}")));
// Clear table after benchmark
await targetConnection.ExecuteStatementAsync($"TRUNCATE TABLE IF EXISTS {targetTable}");
var rps = (long)count * 1000 / stopwatch.ElapsedMilliseconds;
return(new BenchmarkResult {
Duration = stopwatch.Elapsed, RowsCount = Convert.ToUInt64(count), DataSize = Convert.ToUInt64(count) * sizeof(long)
});
}
public async Task SaveRowsData(List <XEventData> eventData)
{
using (ClickHouseBulkCopy bulkCopyInterface = new ClickHouseBulkCopy(_connection)
{
DestinationTableName = "XEventData",
BatchSize = 100000
})
{
var values = eventData.Select(i => new object[]
{
i.FileName,
i.EventNumber,
i.Timestamp.DateTime,
i.EventName,
i.UUID.ToString(),
i.Username ?? string.Empty,
i.UsernameNT ?? string.Empty,
i.UsernameSessionNT ?? string.Empty,
i.SessionId ?? 0,
i.PlanHandle ?? string.Empty,
i.IsSystem == null ? 0 : ((bool)i.IsSystem ? 1 : 0),
i.ExecutionPlanGuid?.ToString() ?? string.Empty,
i.DatabaseName ?? string.Empty,
i.DatabaseId ?? 0,
i.NumaNodeId ?? 0,
i.CpuId ?? 0,
i.ProcessId ?? 0,
i.SQLText ?? string.Empty,
i.SQLTextHash ?? string.Empty,
i.ClientAppName ?? string.Empty,
i.ClientHostname ?? string.Empty,
i.ClientId ?? 0,
i.QueryHash ?? string.Empty,
i.ServerInstanceName ?? string.Empty,
i.ServerPrincipalName ?? string.Empty,
i.ServerPrincipalId ?? 0,
i.CpuTime ?? 0,
i.Duration ?? 0,
i.PhysicalReads ?? 0,
i.LogicalReads ?? 0,
i.Writes ?? 0,
i.RowCount ?? 0,
i.GetActionsAsJSON(),
i.GetFieldsAsJSON()
}).AsEnumerable();
await bulkCopyInterface.WriteToServerAsync(values);
}
}
public async Task ShouldExecuteInsertWithLessColumns()
{
var targetTable = $"test.multiple_columns";
await connection.ExecuteStatementAsync($"TRUNCATE TABLE IF EXISTS {targetTable}");
await connection.ExecuteStatementAsync($"CREATE TABLE IF NOT EXISTS {targetTable} (value1 Nullable(UInt8), value2 Nullable(Float32), value3 Nullable(Int8)) ENGINE TinyLog");
using var bulkCopy = new ClickHouseBulkCopy(connection)
{
DestinationTableName = targetTable,
};
await bulkCopy.WriteToServerAsync(Enumerable.Repeat(new object[] { 5 }, 5), new[] { "value2" }, CancellationToken.None);
using var reader = await connection.ExecuteReaderAsync($"SELECT * from {targetTable}");
}
public void SaveRowsData(InformationSystemsBase system, List <RowData> rowsData)
{
if (rowsData == null || rowsData.Count == 0)
{
return;
}
using (ClickHouseBulkCopy bulkCopyInterface = new ClickHouseBulkCopy(_connection)
{
DestinationTableName = "RowsData",
BatchSize = 100000
})
{
var values = rowsData.Select(i => new object[]
{
system.Name,
i.RowId,
i.Period,
Severities.GetPresentationByName(i.Severity.ToString()),
i.ConnectId ?? 0,
i.Session ?? 0,
TransactionStatuses.GetPresentationByName(i.TransactionStatus.ToString()),
(i.TransactionDate == null || i.TransactionDate < _minDateTime ? _minDateTime : i.TransactionDate),
i.TransactionId ?? 0,
i.User?.Name ?? string.Empty,
i.User?.Uuid.ToString() ?? _emptyGuidAsString,
i.Computer?.Name ?? string.Empty,
Applications.GetPresentationByName(i.Application?.Name ?? string.Empty),
Events.GetPresentationByName(i.Event?.Name ?? string.Empty),
i.Comment ?? string.Empty,
i.Metadata?.Name ?? string.Empty,
i.Metadata?.Uuid.ToString() ?? _emptyGuidAsString,
i.Data ?? string.Empty,
(i.DataUuid ?? string.Empty).NormalizeShortUUID(),
i.DataPresentation ?? string.Empty,
i.WorkServer?.Name ?? string.Empty,
i.PrimaryPort?.Name ?? string.Empty,
i.SecondaryPort?.Name ?? string.Empty
}).AsEnumerable();
_extendedActions?.BeforeSaveData(system, rowsData, ref values);
var bulkResult = bulkCopyInterface.WriteToServerAsync(values);
bulkResult.Wait();
}
}
public async Task ShouldExecuteInsertWithBacktickedColumns()
{
var targetTable = $"test.backticked_columns";
await connection.ExecuteStatementAsync($"TRUNCATE TABLE IF EXISTS {targetTable}");
await connection.ExecuteStatementAsync($"CREATE TABLE IF NOT EXISTS {targetTable} (`field.id` Nullable(UInt8), `@value` Nullable(UInt8)) ENGINE TinyLog");
using var bulkCopy = new ClickHouseBulkCopy(connection)
{
DestinationTableName = targetTable,
};
await bulkCopy.WriteToServerAsync(Enumerable.Repeat(new object[] { 5, 5 }, 5), new[] { "`field.id`, `@value`" });
using var reader = await connection.ExecuteReaderAsync($"SELECT * FROM {targetTable}");
}
public async Task ShouldExecuteBulkInsertWithComplexColumnName(string columnName)
{
var targetTable = "test." + SanitizeTableName($"bulk_complex_{columnName}");
await connection.ExecuteStatementAsync($"TRUNCATE TABLE IF EXISTS {targetTable}");
await connection.ExecuteStatementAsync($"CREATE TABLE IF NOT EXISTS {targetTable} (`{columnName.Replace("`", "\\`")}` Int32) ENGINE TinyLog");
using var bulkCopy = new ClickHouseBulkCopy(connection)
{
DestinationTableName = targetTable,
MaxDegreeOfParallelism = 2,
BatchSize = 100
};
await bulkCopy.WriteToServerAsync(Enumerable.Repeat(new[] { (object)1 }, 1), CancellationToken.None);
Assert.AreEqual(1, bulkCopy.RowsWritten);
}
public async Task ShouldExecuteBulkInsertIntoSimpleAggregatedFunctionColumn()
{
var targetTable = "test." + SanitizeTableName($"bulk_simple_aggregated_function");
await connection.ExecuteStatementAsync($"TRUNCATE TABLE IF EXISTS {targetTable}");
await connection.ExecuteStatementAsync($"CREATE TABLE IF NOT EXISTS {targetTable} (value SimpleAggregateFunction(anyLast,Nullable(Float64))) ENGINE TinyLog");
using var bulkCopy = new ClickHouseBulkCopy(connection)
{
DestinationTableName = targetTable,
MaxDegreeOfParallelism = 2,
BatchSize = 100
};
await bulkCopy.WriteToServerAsync(Enumerable.Repeat(new[] { (object)1 }, 1), CancellationToken.None);
Assert.AreEqual(1, bulkCopy.RowsWritten);
// Verify we can read back
Assert.AreEqual(1, await connection.ExecuteScalarAsync($"SELECT value FROM {targetTable}"));
}
public async Task ShouldExecuteSingleValueInsertViaBulkCopy(string clickHouseType, object insertedValue)
{
var targetTable = SanitizeTableName($"test.b_{clickHouseType}");
var connection = connections.First();
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(connections)
{
DestinationTableName = targetTable,
BatchSize = 1000000
};
await bulkCopy.WriteToServerAsync(Enumerable.Repeat(new[] { insertedValue }, 10000));
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);
}
async protected override Task <int> RawExecuteAffrowsAsync(CancellationToken cancellationToken = default)
{
var affrows = 0;
Exception exception = null;
Aop.CurdBeforeEventArgs before = null;
if (_source.Count > 1)
{
try
{
before = new Aop.CurdBeforeEventArgs(_table.Type, _table, Aop.CurdType.Insert, null, _params);
_orm.Aop.CurdBeforeHandler?.Invoke(this, before);
using (var conn = await _orm.Ado.MasterPool.GetAsync())
{
using var bulkCopyInterface = new ClickHouseBulkCopy(conn.Value as ClickHouseConnection)
{
DestinationTableName = _table.DbName,
BatchSize = _source.Count
};
var data = ToDataTable();
await bulkCopyInterface.WriteToServerAsync(data, default);
}
return(affrows);
}
catch (Exception ex)
{
exception = ex;
throw ex;
}
finally
{
var after = new Aop.CurdAfterEventArgs(before, exception, affrows);
_orm.Aop.CurdAfterHandler?.Invoke(this, after);
}
}
return(await base.RawExecuteAffrowsAsync(cancellationToken));
}
public async Task SaveLogPositions(ExtendedEventsLogBase xEventsLog, List <ExtendedEventsPosition> positions)
{
List <object[]> positionsForInsert = new List <object[]>();
foreach (var positionItem in positions)
{
FileInfo logFileInfo = new FileInfo(positionItem.CurrentFileData);
long itemNumber = positions.IndexOf(positionItem) + 1;
positionsForInsert.Add(new object[]
{
xEventsLog.Name,
DateTime.UtcNow.Ticks + itemNumber,
logFileInfo.Name,
DateTime.UtcNow,
logFileInfo.CreationTimeUtc,
logFileInfo.LastWriteTimeUtc,
positionItem.EventNumber,
positionItem.EventUUID,
positionItem.EventPeriod,
positionItem.CurrentFileData.Replace("\\", "\\\\"),
});
}
if (positionsForInsert.Count > 0)
{
using (ClickHouseBulkCopy bulkCopyInterface = new ClickHouseBulkCopy(_connection)
{
DestinationTableName = "LogFiles",
BatchSize = 100000
})
{
await bulkCopyInterface.WriteToServerAsync(positionsForInsert);
}
}
}
public async Task SaveRowsData(Dictionary <LogBufferItemKey, LogBufferItem> sourceDataFromBuffer)
{
List <object[]> rowsForInsert = new List <object[]>();
List <object[]> positionsForInsert = new List <object[]>();
Dictionary <string, LastRowsInfoByLogFile> maxPeriodByDirectories = new Dictionary <string, LastRowsInfoByLogFile>();
var dataFromBuffer = sourceDataFromBuffer
.OrderBy(i => i.Key.Period)
.ThenBy(i => i.Value.LogPosition.EventNumber)
.ToList();
long itemNumber = 0;
foreach (var dataItem in dataFromBuffer)
{
itemNumber++;
FileInfo logFileInfo = new FileInfo(dataItem.Key.LogFile);
DateTime eventPeriodUtc;
if (dataItem.Value.LogPosition.EventPeriod != null)
{
DateTime periodServer = dataItem.Value.LogPosition.EventPeriod.Value.LocalDateTime;
DateTime periodLocal = TimeZoneInfo.ConvertTime(periodServer, TimeZoneInfo.Local, dataItem.Key.Settings.TimeZone);
eventPeriodUtc = TimeZoneInfo.ConvertTimeToUtc(periodLocal, dataItem.Key.Settings.TimeZone);
}
else
{
eventPeriodUtc = DateTime.MinValue;
}
positionsForInsert.Add(new object[]
{
dataItem.Key.Settings.XEventsLog.Name,
DateTime.UtcNow.Ticks + itemNumber,
logFileInfo.Name,
DateTime.UtcNow,
logFileInfo.CreationTimeUtc,
logFileInfo.LastWriteTimeUtc,
dataItem.Value.LogPosition.EventNumber,
dataItem.Value.LogPosition.EventUUID,
eventPeriodUtc,
dataItem.Value.LogPosition.FinishReadFile
});
foreach (var rowData in dataItem.Value.LogRows)
{
DateTime periodServer = rowData.Value.Timestamp.LocalDateTime;
DateTime periodLocal = TimeZoneInfo.ConvertTime(periodServer, TimeZoneInfo.Local, dataItem.Key.Settings.TimeZone);
DateTime periodUtc = TimeZoneInfo.ConvertTimeToUtc(periodLocal, dataItem.Key.Settings.TimeZone);
if (!maxPeriodByDirectories.TryGetValue(logFileInfo.FullName, out LastRowsInfoByLogFile lastInfo))
{
if (logFileInfo.Directory != null)
{
GetRowsDataMaxPeriodAndId(
dataItem.Key.Settings.XEventsLog,
logFileInfo.Name,
periodUtc,
out var maxPeriod,
out var maxId
);
lastInfo = new LastRowsInfoByLogFile(maxPeriod, maxId);
maxPeriodByDirectories.Add(logFileInfo.FullName, lastInfo);
}
}
bool existByPeriod = lastInfo.MaxPeriod > ClickHouseHelpers.MinDateTimeValue &&
periodUtc.Truncate(TimeSpan.FromSeconds(1)) <= lastInfo.MaxPeriod;
bool existById = lastInfo.MaxId > 0 &&
rowData.Value.Id <= lastInfo.MaxId;
if (existByPeriod && existById)
{
continue;
}
var eventItem = rowData.Value;
rowsForInsert.Add(new object[]
{
dataItem.Key.Settings.XEventsLog.Name,
logFileInfo.Name,
eventItem.EventNumber,
periodUtc,
periodLocal,
eventItem.EventName,
eventItem.UUID.ToString(),
eventItem.Username ?? string.Empty,
eventItem.UsernameNT ?? string.Empty,
eventItem.UsernameSessionNT ?? string.Empty,
eventItem.SessionId ?? 0,
eventItem.PlanHandle ?? string.Empty,
eventItem.IsSystem == null ? 0 : ((bool)eventItem.IsSystem ? 1 : 0),
eventItem.ExecutionPlanGuid?.ToString() ?? string.Empty,
eventItem.DatabaseName ?? string.Empty,
eventItem.DatabaseId ?? 0,
eventItem.NumaNodeId ?? 0,
eventItem.CpuId ?? 0,
eventItem.ProcessId ?? 0,
eventItem.SQLText ?? string.Empty,
eventItem.SQLTextHash ?? string.Empty,
eventItem.ClientAppName ?? string.Empty,
eventItem.ClientHostname ?? string.Empty,
eventItem.ClientId ?? 0,
eventItem.QueryHash ?? string.Empty,
eventItem.ServerInstanceName ?? string.Empty,
eventItem.ServerPrincipalName ?? string.Empty,
eventItem.ServerPrincipalId ?? 0,
eventItem.CpuTime ?? 0,
eventItem.Duration ?? 0,
eventItem.PhysicalReads ?? 0,
eventItem.LogicalReads ?? 0,
eventItem.Writes ?? 0,
eventItem.RowCount ?? 0,
eventItem.GetActionsAsJSON(),
eventItem.GetFieldsAsJSON()
});
}
}
if (rowsForInsert.Count > 0)
{
using (ClickHouseBulkCopy bulkCopyInterface = new ClickHouseBulkCopy(_connection)
{
DestinationTableName = "XEventData",
BatchSize = 100000,
MaxDegreeOfParallelism = 4
})
{
await bulkCopyInterface.WriteToServerAsync(rowsForInsert);
rowsForInsert.Clear();
}
}
if (positionsForInsert.Count > 0)
{
using (ClickHouseBulkCopy bulkCopyInterface = new ClickHouseBulkCopy(_connection)
{
DestinationTableName = "LogFiles",
BatchSize = 100000
})
{
await bulkCopyInterface.WriteToServerAsync(positionsForInsert);
}
}
}
public async Task SaveRowsData(ExtendedEventsLogBase xEventsLog,
IDictionary <string, List <XEventData> > xEventsData,
Dictionary <string, LastRowsInfoByLogFile> maxPeriodByFiles = null)
{
if (maxPeriodByFiles == null)
{
maxPeriodByFiles = new Dictionary <string, LastRowsInfoByLogFile>();
}
List <object[]> rowsForInsert = new List <object[]>();
foreach (var eventInfo in xEventsData)
{
FileInfo logFileInfo = new FileInfo(eventInfo.Key);
foreach (var eventItem in eventInfo.Value)
{
DateTime periodServer = eventItem.Timestamp.LocalDateTime;
DateTime periodUtc = TimeZoneInfo.ConvertTimeToUtc(periodServer, TimeZoneInfo.Local);
DateTime periodLocal = periodServer;
if (!maxPeriodByFiles.TryGetValue(logFileInfo.Name, out LastRowsInfoByLogFile lastInfo))
{
if (logFileInfo.Directory != null)
{
GetRowsDataMaxPeriodAndId(
xEventsLog,
logFileInfo.Name,
periodUtc,
out var maxPeriod,
out var maxId
);
lastInfo = new LastRowsInfoByLogFile(maxPeriod, maxId);
maxPeriodByFiles.Add(logFileInfo.Name, lastInfo);
}
}
bool existByPeriod = lastInfo.MaxPeriod > ClickHouseHelpers.MinDateTimeValue &&
periodUtc.Truncate(TimeSpan.FromSeconds(1)) <= lastInfo.MaxPeriod;
bool existById = lastInfo.MaxId > 0 &&
eventItem.Id <= lastInfo.MaxId;
if (existByPeriod && existById)
{
continue;
}
if (logFileInfo.Directory != null)
{
rowsForInsert.Add(new object[]
{
xEventsLog.Name,
logFileInfo.Name,
eventItem.EventNumber,
periodUtc,
periodLocal,
eventItem.EventName,
eventItem.UUID.ToString(),
eventItem.Username ?? string.Empty,
eventItem.UsernameNT ?? string.Empty,
eventItem.UsernameSessionNT ?? string.Empty,
eventItem.SessionId ?? 0,
eventItem.PlanHandle ?? string.Empty,
eventItem.IsSystem == null ? 0 : ((bool)eventItem.IsSystem ? 1 : 0),
eventItem.ExecutionPlanGuid?.ToString() ?? string.Empty,
eventItem.DatabaseName ?? string.Empty,
eventItem.DatabaseId ?? 0,
eventItem.NumaNodeId ?? 0,
eventItem.CpuId ?? 0,
eventItem.ProcessId ?? 0,
eventItem.SQLText ?? string.Empty,
eventItem.SQLTextHash ?? string.Empty,
eventItem.ClientAppName ?? string.Empty,
eventItem.ClientHostname ?? string.Empty,
eventItem.ClientId ?? 0,
eventItem.QueryHash ?? string.Empty,
eventItem.ServerInstanceName ?? string.Empty,
eventItem.ServerPrincipalName ?? string.Empty,
eventItem.ServerPrincipalId ?? 0,
eventItem.CpuTime ?? 0,
eventItem.Duration ?? 0,
eventItem.PhysicalReads ?? 0,
eventItem.LogicalReads ?? 0,
eventItem.Writes ?? 0,
eventItem.RowCount ?? 0,
eventItem.GetActionsAsJSON(),
eventItem.GetFieldsAsJSON()
});
}
}
}
if (rowsForInsert.Count == 0)
{
return;
}
using (ClickHouseBulkCopy bulkCopyInterface = new ClickHouseBulkCopy(_connection)
{
DestinationTableName = "XEventData",
BatchSize = 100000,
MaxDegreeOfParallelism = 4
})
{
await bulkCopyInterface.WriteToServerAsync(rowsForInsert);
rowsForInsert.Clear();
}
}
