public void Exec()
{
Stopwatch stopwatch = Stopwatch.StartNew(); //creates and start the instance of Stopwatch
WaitScreen.Splash();
string command = CHBox.Text;
using (ClickHouseCommand comm = con.CreateCommand(command))
{
try
{
comm.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Response.Content = "Wrong command.";
return;
}
stopwatch.Stop();
var elapsedBulkMs = stopwatch.ElapsedMilliseconds;
Logs xd = new DBUplader.Logs();
xd.AddLog("--------------------------------------------");
xd.AddLog("Execute ClickHouse Command");
xd.AddLog(command);
xd.AddLog("Execute time : " + elapsedBulkMs + "ms");
Response.Content = "OK. " + elapsedBulkMs + "ms";
}
}
private void MigrateCHToMDB_Click(object sender, RoutedEventArgs e)
{
WaitScreen.Splash();
string tableName = CHTablesComboBox.SelectedItem.ToString();
Stopwatch stopwatch = Stopwatch.StartNew(); //creates and start the instance of Stopwatch
CHConnection(() => InsertToMDB(GetTableColumns(tableName), tableName));
stopwatch.Stop();
var elapsedBulkMs = stopwatch.ElapsedMilliseconds;
int records = CountMDB(tableName);
Console.WriteLine("time : " + stopwatch.ElapsedMilliseconds);
Timer.Content = "Migration time : " + elapsedBulkMs + "ms";
NumberOfRecordsCH.Content = "Records inserted : " + records;
Logs xd = new DBUplader.Logs();
xd.AddLog("--------------------------------------------");
xd.AddLog("Migrate ClickHouse to MongoDB");
xd.AddLog(" Data name : " + tableName);
xd.AddLog(" Bulk time : " + elapsedBulkMs + "ms");
xd.AddLog("Records inserted : " + records);
}
public void Exec1()
{
Stopwatch stopwatch = Stopwatch.StartNew(); //creates and start the instance of Stopwatch
WaitScreen.Splash();
string command = CHBox.Text;
using (ClickHouseCommand comm = con.CreateCommand(command))
{
try
{
using (var reader = comm.ExecuteReader())
{
List <object> result = new List <object>();
do
{
while (reader.Read())
{
for (var i = 0; i < reader.FieldCount; i++)
{
var val = reader.GetValue(i);
result.Add(val);
Resp.AppendText(result[i].ToString() + ", ");
}
Resp.AppendText(Environment.NewLine);
result.Clear();
}
} while (reader.NextResult());
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Response.Content = "Wrong command.";
return;
}
stopwatch.Stop();
var elapsedBulkMs = stopwatch.ElapsedMilliseconds;
Logs xd = new DBUplader.Logs();
xd.AddLog("--------------------------------------------");
xd.AddLog("Execute ClickHouse Command");
xd.AddLog(command);
xd.AddLog("Execute time : " + elapsedBulkMs + "ms");
Response.Content = "OK. " + elapsedBulkMs + "ms";
}
using (ClickHouseCommand comm = con.CreateCommand(command))
{
}
}
private void MigrateMDBToCH_Click(object sender, RoutedEventArgs e)
{
WaitScreen.Splash();
string tableName = MDBTablesComboBox.SelectedItem.ToString();
Stopwatch stopwatch = Stopwatch.StartNew();
CSVToClickHouse.CHConn(() => GetData(tableName));
stopwatch.Stop();
var elapsedBulkMs = stopwatch.ElapsedMilliseconds;
Timer.Content = "Migration time : " + elapsedBulkMs + "ms";
NumberOfRecords.Content = "Records : " + (lineCount);
Logs xd = new DBUplader.Logs();
xd.AddLog("--------------------------------------------");
xd.AddLog("Migrate MongoDB to ClickHouse");
xd.AddLog(" Data name : " + tableName);
xd.AddLog(" Bulk time : " + elapsedBulkMs + "ms");
xd.AddLog("Records inserted : " + lineCount);
}
private void ReadCSV3(string filePath, string tableName)
{
Stopwatch stopwatch = Stopwatch.StartNew();
List <object> result = new List <object>();
result.Clear();
int maxBulk = 300000;
int k = 0;
var lineCount = File.ReadLines(filePath).Count();
const Int32 BufferSize = 128;
try
{
using (var fileStream1 = File.OpenRead(filePath))
Console.WriteLine(filePath);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
WrongFilePath();
return;
}
List <string> types = null;
using (var fileStream = File.OpenRead(filePath))
using (var streamReader = new StreamReader(fileStream, Encoding.UTF8, true, BufferSize))
{
String line;
while ((line = streamReader.ReadLine()) != null)
{
char[] spearator = { ',' };
if (headerList.Count > 0)
{
string[] strlist = line.Split(spearator, StringSplitOptions.None);
if (types == null)
{
types = CheckType(strlist);
}
object[] x = new object[types.Count];
for (int i = 0; i < types.Count; i++)
{
x[i] = GetValue(types[i], strlist[i]);
}
result.Add(x);
}
else
{
String[] strlist = line.Split(spearator, StringSplitOptions.None);
foreach (String s in strlist)
{
headerList.Add(s);
}
for (int i = 0; i < headerList.Count; i++)
{
Console.Write(headerList[i] + " ");
}
Console.WriteLine();
}
k++;
if (maxBulk == k)
{
CreateCHTable(headerList, types, tableName);
InsertValues(result, tableName, headerList);
result.Clear();
k = 0;
}
}
}
var first = result.First();
CreateCHTable(headerList, types, tableName);
InsertValues(result, tableName, headerList);
stopwatch.Stop();
var elapsedBulkMs = stopwatch.ElapsedMilliseconds;
Timer.Content = "Time : " + elapsedBulkMs + "ms";
int records = lineCount - 1;
NumberOfRecordsCH.Content = "Records inserted : " + records;
Logs xd = new DBUplader.Logs();
xd.AddLog("--------------------------------------------");
xd.AddLog("CSV to ClickHouse");
xd.AddLog(" Data name : " + tableName);
xd.AddLog(" Bulk time : " + elapsedBulkMs + "ms");
xd.AddLog("Records inserted : " + records);
}
private void UploadCSVToMongoDB()
{
var watchBulk = System.Diagnostics.Stopwatch.StartNew();
string filePath = CSVPathTextBox.Text;
string tableName = TableNameTextBox.Text;
try
{
using (var fileStream1 = File.OpenRead(filePath));
}
catch (Exception e)
{
Console.WriteLine(e.Message);
WrongFilePath();
return;
}
var collection = new MongoClient("mongodb://localhost:27017").GetDatabase("test")
.GetCollection <BsonDocument>(tableName);
var lineCount = File.ReadLines(filePath).Count();
List <string> headerList = new List <string>();
List <string> valueList = new List <string>();
//List<object> valueList = new List<object>();
var newDocs = new List <BsonDocument>();
BsonDocument[] doc = new BsonDocument[lineCount];
int wrongLines = 0;
int i = 0;
int maxBulk = 1000;
const Int32 BufferSize = 128;
using (var fileStream = File.OpenRead(filePath))
using (var streamReader = new StreamReader(fileStream, Encoding.UTF8, true, BufferSize))
{
String line;
while ((line = streamReader.ReadLine()) != null)
{
char[] spearator = { ',' };
String[] strlist = line.Split(spearator, StringSplitOptions.None);
if (headerList.Count > 0)
{
if (i == maxBulk)
{
collection.InsertMany(newDocs);
newDocs.Clear();
i = 0;
}
foreach (String s in strlist)
{
valueList.Add(s);
}
doc[i] = new BsonDocument
{
};
for (int j = 0; j < headerList.Count; j++)
{
try
{
int o = 0;
float f = 0F;
if (int.TryParse(valueList[j], out o))
{
o = int.Parse(valueList[j]);
doc[i].Add(new BsonElement(headerList[j], o));
}
else if (float.TryParse(valueList[j], out f))
{
f = float.Parse(valueList[j]);
doc[i].Add(new BsonElement(headerList[j], f));
}
else
{
doc[i].Add(new BsonElement(headerList[j], valueList[j]));
}
}
catch (Exception ex)
{
doc[i].Clear();
Console.WriteLine(ex.Message);
wrongLines++;
break;
}
}
if (doc[i].Count() > 0)
{
newDocs.Add(doc[i]);
}
valueList.Clear();
i++;
}
else
{
foreach (String s in strlist)
{
headerList.Add(s);
}
}
}
}
if (i < maxBulk)
{
collection.InsertMany(newDocs);
}
int records = lineCount - 1 - wrongLines;
NumberOfRecords.Content = "Records inserted : " + records;
doc = new BsonDocument[0];
WrongLinesNumber.Content = "Number of incorrect lines in CSV file : " + wrongLines;
wrongLines = 0;
GC.Collect();
watchBulk.Stop();
var elapsedBulkMs = watchBulk.ElapsedMilliseconds;
BsonToMongoTime.Content = "Bulk Bson data time : " + elapsedBulkMs + "ms";
Logs xd = new DBUplader.Logs();
xd.AddLog("--------------------------------------------");
xd.AddLog("CSV to MongoDB");
xd.AddLog(" Data name : " + tableName);
xd.AddLog(" Bulk time : " + elapsedBulkMs + "ms");
xd.AddLog("Records inserted : " + records);
}
private void Exec(string table, string filter)
{
Stopwatch stopwatch = Stopwatch.StartNew(); //creates and start the instance of Stopwatch
WaitScreen.Splash();
string command = MDBBox.Text;
var server = new MongoClient("mongodb://localhost:27017");
var database = server.GetDatabase("test");
var coll = database.GetCollection <BsonDocument>(table);
//var list = coll.Find(command);
//var list = coll.Find(new BsonDocument()).Limit(5).ToList();
//filter = "{ price: '480'}";
//var list = coll.Find("{},{\"name\":1}).limit(100)").ToList();
try
{
int lim = int.Parse(Limit.Text);
string typ = Type.Text;
List <BsonDocument> list = new List <BsonDocument>();
if (lim > 0 && typ == "Find")
{
list = coll.Find(filter).Limit(lim).ToList();
}
else if (lim <= 0 && typ == "Find")
{
list = coll.Find(filter).ToList();
}
else if (lim > 0 && typ == "Group")
{
list = coll.Aggregate().Group(filter).Limit(lim).ToList();
}
else if (lim <= 0 && typ == "Group")
{
list = coll.Aggregate().Group(filter).ToList();
}
for (int i = 0; i < list.Count; i++)
{
Resp.AppendText(list[i].ToString() + (Environment.NewLine));
}
stopwatch.Stop();
var elapsedBulkMs = stopwatch.ElapsedMilliseconds;
Logs xd = new DBUplader.Logs();
xd.AddLog("--------------------------------------------");
xd.AddLog("Execute MongoDB Command");
xd.AddLog(command);
xd.AddLog($"On table { table }");
xd.AddLog($"Execute time : { elapsedBulkMs } ms");
Response.Content = $"OK. { elapsedBulkMs } ms";
}
catch
{
Response.Content = "Wrong command.";
}
//var comm = new JsonCommand<BsonDocument>(command);
//database.RunCommand(comm);
//db.getCollection('asd').find({ })
}
