/// <summary>
/// Method to use proper database
/// </summary>
/// <param name="database">name of chosen database</param>
public void Use(string database, ColumnarDBClient client)
{
try
{
client.UseDatabase(database);
Console.WriteLine(SuccessfulUse(database));
}
catch (IOException e)
{
Console.WriteLine(e.Message);
}
catch (QueryException e)
{
Console.WriteLine("Query Exception: " + e.Message);
}
catch (Exception e)
{
Console.WriteLine(UnknownException() + e.Message);
}
}
/// <summary>
/// Method to import CSV file
/// </summary>
/// <param name="path">Path to csv file that should be imported</param>
/// <param name="databaseName">Database that is used</param>
/// <param name="blockSize">Block size of table, if not specified default value of database server configuration will be used</param>
/// <param name="hasHeader">Specifies whether input csv has header, if not specified default value is true</param>
/// <param name="columnSeparator">Char representing column separator, if not specified default value will be guessed</param>
/// <param name="batchSize">Number of lines processed in one batch, if not specified default value is 65536</param>
/// <param name="threadsCount">Number of threads for processing csv, if not specified number of cores of the client CPU will be used</param>
/// <param name="encoding">Encoding of csv, if not specified it will be guessed</param>
public void Import(string path, string databaseName,
string tableName = "", int blockSize = 0, bool hasHeader = true, char columnSeparator = '\0', int batchSize = 65536, int threadsCount = 0, Encoding encoding = null)
{
this.databaseName = databaseName;
try
{
// Table name from path or from argument if present
if (tableName == "")
{
tableName = Path.GetFileNameWithoutExtension(path);
}
this.tableName = tableName;
if (encoding == null)
{
encoding = ParserCSV.GuessEncoding(path);
}
if (columnSeparator == '\0')
{
columnSeparator = ParserCSV.GuessSeparator(path, encoding);
}
var types = ParserCSV.GuessTypes(path, hasHeader, columnSeparator, encoding);
streamLength = ParserCSV.GetStreamLength(path);
client.Connect();
client.UseDatabase(databaseName);
CreateTable(databaseName, tableName, types, blockSize);
ParserCSV.Configuration configuration = new ParserCSV.Configuration(batchSize: batchSize, encoding: encoding, columnSeparator: columnSeparator);
if (threadsCount <= 0)
{
// Use more threads if file is bigger than 10MB
if (streamLength > 10000000)
{
threadsCount = Environment.ProcessorCount;
}
else
{
threadsCount = 1;
}
}
Console.WriteLine("Importing started with " + threadsCount + " threads.");
var startTime = DateTime.Now;
long streamThreadLength = streamLength / threadsCount;
long lines = 0;
long errors = 0;
linesImported = new long[threadsCount];
bytesImported = new long[threadsCount];
linesError = new long[threadsCount];
Thread[] threads = new Thread[threadsCount];
Exception[] threadExceptions = new Exception[threadsCount];
// Each thread has its own instance of the Parser (because of different position of read) and ColumnarDBClient (because of concurrent bulk import)
for (int i = 0; i < threadsCount; i++)
{
long start = i * streamThreadLength;
long end = i * streamThreadLength + streamThreadLength + 1;
int index = i;
threadExceptions[index] = null;
threads[index] = new Thread(() =>
{
try
{
this.ParseAndImportBatch(index, path, configuration, types, start, end);
}
catch (Exception ex)
{
threadExceptions[index] = ex;
}
});
threads[index].Start();
//this.ParseAndImportBatch(index, path, configuration, types, start, end);
}
for (int i = 0; i < threadsCount; i++)
{
threads[i].Join();
}
for (int i = 0; i < threadsCount; i++)
{
if (threadExceptions[i] != null)
{
throw threadExceptions[i];
}
}
for (int i = 0; i < threadsCount; i++)
{
lines += linesImported[i];
errors += linesError[i];
}
client.Dispose();
var endTime = DateTime.Now;
Console.WriteLine();
Console.WriteLine("Importing done (imported " + lines + " records, " + errors + " failed, " + (endTime - startTime).TotalSeconds.ToString() + "sec.).");
}
catch (FileNotFoundException)
{
Console.WriteLine(FileNotFound(path));
}
catch (IOException e)
{
Console.WriteLine(e.Message);
}
catch (QueryException e)
{
Console.WriteLine("Query Exception: " + e.Message);
}
catch (ParserCSV.ParserException e)
{
Console.WriteLine("Parser Exception: " + e.Message);
}
catch (Exception e)
{
Console.WriteLine(UnknownException() + e.Message);
}
}
/// <summary>
/// Load benchmark queries from a file, execute them one by one and save results.
/// </summary>
public static int Main(string[] args)
{
bool avgTimePassed = true;
bool correctResultsPassed = true;
Array.Sort(args);
ColumnarDBClient client = new ColumnarDBClient($"Host={ipAddress};Port={port.ToString()};");
client.Connect();
Console.WriteLine("Client has successfully connected to server.");
UseDatabase use = new UseDatabase();
if (File.Exists(resultFilePath))
{
File.Delete(resultFilePath);
}
var resultFile = new System.IO.StreamWriter(resultFilePath);
if (args.GetLength(0) == 0)
{
string[] array = { "-a" };
List <string> tempList = new List <string>(array);
args = tempList.ToArray();
}
resultFile.WriteLine($"Each query was executed {numberOfQueryExec} times and the average time was saved.");
foreach (string arg in args)
{
string queryString;
string queryExptectedString;
if (String.Equals(arg, "-a") || String.Equals(arg, "-b"))
{
// test telco queries:
use.Use(telcoDbName, client);
client.UseDatabase(telcoDbName);
var queryFile = new System.IO.StreamReader(telcoQueriesPath);
Console.WriteLine($"Benchmark queries from file '{telcoQueriesPath}' were loaded.");
while ((queryString = queryFile.ReadLine()) != null)
{
Console.WriteLine($"Executing benchmark query: {queryString}");
resultFile.WriteLine(queryString);
// execute query first time (no cache):
double resultSum = 0;
try
{
client.Query(queryString);
}
catch (Exception e)
{
Console.WriteLine(e);
break;
}
Dictionary <string, float> executionTimes = null;
ColumnarDataTable result = null;
while (((result, executionTimes) = client.GetNextQueryResult()).result != null)
{
resultSum += executionTimes.Values.Sum();
}
resultSum = Math.Round(resultSum);
// save query result to a file:
resultFile.WriteLine((resultSum).ToString() + " (first run)");
Console.WriteLine((resultSum).ToString() + " (first run)");
// execute query N times (used cache):
for (int i = 0; i < numberOfQueryExec; i++)
{
try
{
client.Query(queryString);
}
catch (Exception e)
{
Console.WriteLine(e);
break;
}
while (((result, executionTimes) = client.GetNextQueryResult()).result != null)
{
resultSum += executionTimes.Values.Sum();
}
}
double avgQueryExec = Math.Round(resultSum / numberOfQueryExec);
// save query result to a file:
resultFile.WriteLine(avgQueryExec.ToString() + " (average cached N runs)");
Console.WriteLine(avgQueryExec + " (average cached N runs)");
// check if query execution time is acceptable and save the result:
int queryExpectedExecTime = System.Convert.ToInt32(queryFile.ReadLine());
if (avgQueryExec < queryExpectedExecTime)
{
resultFile.WriteLine($"The query '{queryString}' has passed the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime.ToString()} / {avgQueryExec.ToString()}");
Console.WriteLine($"The query '{queryString}' has passed the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime} / {avgQueryExec}");
}
else
{
resultFile.WriteLine($"The query '{queryString}' has FAILED the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime.ToString()} / {avgQueryExec.ToString()}");
Console.WriteLine($"The query '{queryString} ' has FAILED the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime} / {avgQueryExec}");
avgTimePassed = false;
}
}
queryFile.Close();
}
if (String.Equals(arg, "-a") || String.Equals(arg, "-g"))
{
// test geo queries:
use.Use(geoDbName, client);
client.UseDatabase(geoDbName);
var queryFile = new System.IO.StreamReader(geoQueriesPath);
Console.WriteLine($"Benchmark queries from file '{geoQueriesPath}' were loaded.");
while ((queryString = queryFile.ReadLine()) != null)
{
Console.WriteLine($"Executing benchmark query: {queryString}");
resultFile.WriteLine(queryString);
// execute query first time (no cache):
double resultSum = 0;
try
{
client.Query(queryString);
}
catch (Exception e)
{
Console.WriteLine(e);
break;
}
Dictionary <string, float> executionTimes = null;
ColumnarDataTable result = null;
while (((result, executionTimes) = client.GetNextQueryResult()).result != null)
{
resultSum += executionTimes.Values.Sum();
}
resultSum = Math.Round(resultSum);
// save query result to a file:
resultFile.WriteLine((resultSum).ToString() + " (first run)");
Console.WriteLine((resultSum).ToString() + " (first run)");
// execute query N times (used cache):
for (int i = 0; i < numberOfQueryExec; i++)
{
try
{
client.Query(queryString);
}
catch (Exception e)
{
Console.WriteLine(e);
break;
}
while (((result, executionTimes) = client.GetNextQueryResult()).result != null)
{
resultSum += executionTimes.Values.Sum();
}
}
double avgQueryExec = Math.Round(resultSum / numberOfQueryExec);
// save query result to a file:
resultFile.WriteLine(avgQueryExec.ToString() + " (average cached N runs)");
Console.WriteLine(avgQueryExec + " (average cached N runs)");
// check if query execution time is acceptable and save the result:
int queryExpectedExecTime = System.Convert.ToInt32(queryFile.ReadLine());
if (avgQueryExec < queryExpectedExecTime)
{
resultFile.WriteLine($"The query '{queryString}' has passed the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime.ToString()} / {avgQueryExec.ToString()}");
Console.WriteLine($"The query '{queryString}' has passed the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime} / {avgQueryExec}");
}
else
{
resultFile.WriteLine($"The query '{queryString}' has FAILED the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime.ToString()} / {avgQueryExec.ToString()}");
Console.WriteLine($"The query '{queryString} ' has FAILED the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime} / {avgQueryExec}");
avgTimePassed = false;
}
}
queryFile.Close();
}
if (String.Equals(arg, "-a") || String.Equals(arg, "-t"))
{
// test taxi queries:
use.Use(taxiDbName, client);
client.UseDatabase(taxiDbName);
var queryFile = new System.IO.StreamReader(taxiQueriesPath);
Console.WriteLine($"Benchmark queries from file '{taxiQueriesPath}' were loaded.");
while ((queryString = queryFile.ReadLine()) != null)
{
Console.WriteLine($"Executing benchmark query: {queryString}");
resultFile.WriteLine(queryString);
// execute query first time (no cache):
double resultSum = 0;
client.Query(queryString);
Dictionary <string, float> executionTimes = null;
ColumnarDataTable result = null;
while (((result, executionTimes) = client.GetNextQueryResult()).result != null)
{
resultSum += executionTimes.Values.Sum();
}
resultSum = Math.Round(resultSum);
// save query result to a file:
resultFile.WriteLine((resultSum).ToString() + " (first run)");
Console.WriteLine((resultSum).ToString() + " (first run)");
// execute query N times (used cache):
for (int i = 0; i < numberOfQueryExec; i++)
{
client.Query(queryString);
while (((result, executionTimes) = client.GetNextQueryResult()).result != null)
{
resultSum += executionTimes.Values.Sum();
}
}
// save query result to a file:
resultFile.WriteLine((resultSum / numberOfQueryExec).ToString() + " (average cached N runs)");
Console.WriteLine((resultSum / numberOfQueryExec) + " (average cached N runs)");
}
queryFile.Close();
}
if (String.Equals(arg, "-a") || String.Equals(arg, "--ci"))
{
// test taxi queries:
use.Use(taxiDbName, client);
client.UseDatabase(taxiDbName);
var queryFile = new System.IO.StreamReader(ciQueriesPath);
Console.WriteLine($"Benchmark queries from file '{ciQueriesPath}' were loaded.");
int queryIndex = 1; // indexing from 1, not zero, because we use to call it taxi rides query number 1, not number 0, so it is not confusing
Dictionary <string, IList> columnData = null;
while ((queryString = queryFile.ReadLine()) != null)
{
Console.WriteLine($"Executing benchmark query: {queryString}");
resultFile.WriteLine(queryString);
// execute query first time (no cache):
double resultSum = 0;
try
{
client.Query(queryString);
}
catch (Exception e)
{
Console.WriteLine(e);
break;
}
Dictionary <string, float> executionTimes = null;
ColumnarDataTable result = null;
// read file where the results of a particular query are saved:
var queryExpectedResultFile = new StreamReader($"../../../QikkDB.BenchmarkUtility/{taxiDbName}_testQuery_{queryIndex}.txt");
queryIndex++;
// read the file header
var expectedColumnNames = queryExpectedResultFile.ReadLine().Split('|');
// read expected column data types
var expectedDataTypes = queryExpectedResultFile.ReadLine().Split('|');
Dictionary <string, IList> exptectedColumns = new Dictionary <string, IList>();
bool firstPart = true;
while (((result, executionTimes) = client.GetNextQueryResult()).result != null)
{
// long results are divided into multiple parts
if (firstPart)
{
columnData = result.GetColumnData();
}
else
{
for (int i = 0; i < expectedColumnNames.Length; i++)
{
for (int j = 0; j < result.GetColumnData()[expectedColumnNames[i]].Count; j++)
{
columnData[expectedColumnNames[i]].Add(result.GetColumnData()[expectedColumnNames[i]][j]);
}
}
}
resultSum += executionTimes.Values.Sum();
firstPart = false;
}
for (int i = 0; i < expectedColumnNames.Length; i++)
{
switch (expectedDataTypes[i])
{
case "INT":
exptectedColumns.Add(expectedColumnNames[i], new List <Int32>());
break;
case "LONG":
exptectedColumns.Add(expectedColumnNames[i], new List <Int64>());
break;
case "FLOAT":
exptectedColumns.Add(expectedColumnNames[i], new List <Single>());
break;
case "DOUBLE":
exptectedColumns.Add(expectedColumnNames[i], new List <Double>());
break;
case "STRING":
exptectedColumns.Add(expectedColumnNames[i], new List <String>());
break;
}
}
// read results from a file:
while ((queryExptectedString = queryExpectedResultFile.ReadLine()) != null)
{
var results = queryExptectedString.Split('|');
for (int i = 0; i < expectedColumnNames.Length; i++)
{
switch (expectedDataTypes[i])
{
case "INT":
exptectedColumns[expectedColumnNames[i]].Add(Int32.Parse(results[i]));
break;
case "LONG":
exptectedColumns[expectedColumnNames[i]].Add(Int64.Parse(results[i]));
break;
case "FLOAT":
exptectedColumns[expectedColumnNames[i]].Add(Single.Parse(results[i]));
break;
case "DOUBLE":
var styles = NumberStyles.AllowDecimalPoint;
var provider = CultureInfo.CreateSpecificCulture("en-US");
exptectedColumns[expectedColumnNames[i]].Add(Double.Parse(results[i], styles, provider));
break;
case "STRING":
exptectedColumns[expectedColumnNames[i]].Add(results[i]);
break;
}
}
}
// check if the expected result dictionary is the same as actual query result dictionary:
for (int i = 0; i < expectedColumnNames.Length; i++)
{
try
{
if (exptectedColumns[expectedColumnNames[i]].Count != columnData[expectedColumnNames[i]].Count)
{
resultFile.WriteLine($"The query '{queryString}' has FAILED the correct results test. Expected / Actual count of result entries: {exptectedColumns[expectedColumnNames[i]].Count.ToString()} / {columnData[expectedColumnNames[i]].Count.ToString()}");
Console.WriteLine($"The query '{queryString} ' has FAILED the correct results test. Expected / Actual count of result entries: {exptectedColumns[expectedColumnNames[i]].Count} / {columnData[expectedColumnNames[i]].Count}");
correctResultsPassed = false;
}
else
{
// check each element in result's lists
for (int j = 0; j < exptectedColumns[expectedColumnNames[i]].Count; j++)
{
bool tempCorrectResultsPassed = true;
switch (expectedDataTypes[i])
{
case "INT":
if ((int)exptectedColumns[expectedColumnNames[i]][j] != (int)columnData[expectedColumnNames[i]][j])
{
tempCorrectResultsPassed = false;
}
break;
case "LONG":
if ((long)exptectedColumns[expectedColumnNames[i]][j] != (long)columnData[expectedColumnNames[i]][j])
{
tempCorrectResultsPassed = false;
}
break;
case "FLOAT":
if (Math.Abs((float)exptectedColumns[expectedColumnNames[i]][j] - (float)columnData[expectedColumnNames[i]][j]) > 0.001)
{
tempCorrectResultsPassed = false;
}
break;
case "DOUBLE":
if (Math.Abs((double)exptectedColumns[expectedColumnNames[i]][j] - (double)columnData[expectedColumnNames[i]][j]) > 0.001)
{
tempCorrectResultsPassed = false;
}
break;
case "STRING":
if ((string)exptectedColumns[expectedColumnNames[i]][j] != (string)columnData[expectedColumnNames[i]][j])
{
tempCorrectResultsPassed = false;
}
break;
}
if (!tempCorrectResultsPassed)
{
resultFile.WriteLine($"The query '{queryString}' has FAILED the correct results test. Expected[{expectedColumnNames[i].ToString()}][{j.ToString()}] / Actual[{j.ToString()}] returned value: {exptectedColumns[expectedColumnNames[i]][j].ToString()} / {columnData[expectedColumnNames[i]][j].ToString()}");
Console.WriteLine($"The query '{queryString}' has FAILED the correct results test. Expected[{expectedColumnNames[i].ToString()}][{j}] / Actual[{j}] returned value: {exptectedColumns[expectedColumnNames[i]][j]} / {columnData[expectedColumnNames[i]][j]}");
correctResultsPassed = false;
}
}
}
}
catch (System.Collections.Generic.KeyNotFoundException e)
{
resultFile.WriteLine($"The query '" + queryString + "' has FAILED the correct results test. Expected / Actual returned value: " + exptectedColumns[expectedColumnNames[i]].ToString() + " / System.Collections.Generic.KeyNotFoundException was thrown: " + e.Message);
Console.WriteLine($"The query '" + queryString + "' has FAILED the correct results test. Expected / Actual returned value: " + exptectedColumns[expectedColumnNames[i]] + " / System.Collections.Generic.KeyNotFoundException was thrown: " + e.Message);
correctResultsPassed = false;
}
}
resultSum = Math.Round(resultSum);
// save query result to a file:
resultFile.WriteLine((resultSum).ToString() + " (first run)");
Console.WriteLine((resultSum).ToString() + " (first run)");
// execute query N times (used cache):
for (int i = 0; i < numberOfQueryExec; i++)
{
try
{
client.Query(queryString);
}
catch (Exception e)
{
Console.WriteLine(e);
break;
}
while (((result, executionTimes) = client.GetNextQueryResult()).result != null)
{
resultSum += executionTimes.Values.Sum();
}
}
double avgQueryExec = Math.Round(resultSum / numberOfQueryExec);
// save query result to a file:
resultFile.WriteLine(avgQueryExec.ToString() + " (average cached N runs)");
Console.WriteLine(avgQueryExec + " (average cached N runs)");
// check if query execution time is acceptable and save the result:
int queryExpectedExecTime = System.Convert.ToInt32(queryFile.ReadLine());
if (avgQueryExec < queryExpectedExecTime)
{
resultFile.WriteLine($"The query '{queryString}' has passed the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime.ToString()} / {avgQueryExec.ToString()}");
Console.WriteLine($"The query '{queryString}' has passed the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime} / {avgQueryExec}");
}
else
{
resultFile.WriteLine($"The query '{queryString}' has FAILED the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime.ToString()} / {avgQueryExec.ToString()}");
Console.WriteLine($"The query '{queryString} ' has FAILED the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime} / {avgQueryExec}");
avgTimePassed = false;
}
}
queryFile.Close();
}
if (String.Equals(arg, "-a") || String.Equals(arg, "-s"))
{
// test stcs queries:
use.Use(stcsDbName, client);
client.UseDatabase(stcsDbName);
var queryFile = new System.IO.StreamReader(stcsQueriesPath);
Console.WriteLine($"Benchmark queries from file '{stcsQueriesPath}' were loaded.");
while ((queryString = queryFile.ReadLine()) != null)
{
Console.WriteLine($"Executing benchmark query: {queryString}");
resultFile.WriteLine(queryString);
// execute query first time (no cache):
double resultSum = 0;
try
{
client.Query(queryString);
}
catch (Exception e)
{
Console.WriteLine(e);
break;
}
Dictionary <string, float> executionTimes = null;
ColumnarDataTable result = null;
while (((result, executionTimes) = client.GetNextQueryResult()).result != null)
{
resultSum += executionTimes.Values.Sum();
}
resultSum = Math.Round(resultSum);
// save query result to a file:
resultFile.WriteLine((resultSum).ToString() + " (first run)");
Console.WriteLine((resultSum).ToString() + " (first run)");
// execute query N times (used cache):
for (int i = 0; i < numberOfQueryExec; i++)
{
try
{
client.Query(queryString);
}
catch (Exception e)
{
Console.WriteLine(e);
break;
}
while (((result, executionTimes) = client.GetNextQueryResult()).result != null)
{
resultSum += executionTimes.Values.Sum();
}
}
double avgQueryExec = Math.Round(resultSum / numberOfQueryExec);
// save query result to a file:
resultFile.WriteLine(avgQueryExec.ToString() + " (average cached N runs)");
Console.WriteLine(avgQueryExec + " (average cached N runs)");
// check if query execution time is acceptable and save the result:
int queryExpectedExecTime = System.Convert.ToInt32(queryFile.ReadLine());
if (avgQueryExec < queryExpectedExecTime)
{
resultFile.WriteLine($"The query '{queryString}' has passed the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime.ToString()} / {avgQueryExec.ToString()}");
Console.WriteLine($"The query '{queryString}' has passed the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime} / {avgQueryExec}");
}
else
{
resultFile.WriteLine($"The query '{queryString}' has FAILED the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime.ToString()} / {avgQueryExec.ToString()}");
Console.WriteLine($"The query '{queryString} ' has FAILED the execution time test. Expected / Actual average query execution time: {queryExpectedExecTime} / {avgQueryExec}");
avgTimePassed = false;
}
}
queryFile.Close();
}
}
resultFile.Close();
// return exit code:
if (correctResultsPassed && avgTimePassed)
{
// everything was successful
return(0);
}
if (!correctResultsPassed && avgTimePassed)
{
// query results were not correct, but query has finished in expected time
return(1);
}
if (correctResultsPassed && !avgTimePassed)
{
// query results were corrcet, but query has not finished in expected time
return(2);
}
if (!correctResultsPassed && !avgTimePassed)
{
// neither query results were correct, nor query has finished execution in expected time
return(3);
}
// something else has happend
return(4);
}