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