public static void CHConnection(Action myMethodName)
{
WaitScreen.Splash();
try
{
con = new ClickHouseConnection(str);
con.Open();
Console.WriteLine("DBConnected");
myMethodName();
}
catch (ClickHouseException err)
{
Console.WriteLine(err);
}
finally
{
if (con != null)
{
con.Close();
}
}
}
public void CreateTableTest()
{
int res1;
int res2;
int res3;
using (ClickHouseConnection con = new ClickHouseConnection(settings))
{
con.Open();
ClickHouseCommand cmd = con.CreateCommand();
cmd.CommandText = "CREATE DATABASE IF NOT EXISTS `test`;";
res1 = cmd.ExecuteNonQuery();
cmd.CommandText = "DROP TABLE IF EXISTS test.temp_table;";
res2 = cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS test.temp_table ( OrderID Int32, Subject String, Date Date, Summ Decimal(15,2)) ENGINE = SummingMergeTree(Summ) PARTITION BY toYYYYMM(Date) ORDER BY (OrderID,Subject,Date);";
res3 = cmd.ExecuteNonQuery();
con.Close();
}
Assert.Equal(res1 + res2 + res3, 0);
}
static void Main(string[] args)
{
var conn = new ClickHouseConnection("Compress=True;CheckCompressedHash=False;Compressor=lz4;Host=192.168.10.110;Port=9000;Database=geekbuying;User=default;Password=;");
conn.Open();
CreateTable(conn);
try
{
var user = new TestUser()
{
ResisterDate = DateTime.Now, ResisterTime = DateTime.Now, Age = 18, Name = "张三2"
};
var user2 = new TestUser()
{
ResisterDate = DateTime.Now, ResisterTime = DateTime.Now, Age = 18, Name = "张三"
};
// conn.Insert(user);
Console.WriteLine("Insert single success.");
conn.InsertBulk(new List <TestUser>()
{
user, user2
});
Console.WriteLine("Insert multi success.");
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
conn.Close();
}
public void FillListFromDatabaseTest()
{
List <SummClass> list = new List <SummClass>();
list.Add(new SummClass(1, "sub1", new DateTime(2019, 1, 15), 100));
list.Add(new SummClass(1, "sub2", new DateTime(2019, 1, 15), 450));
list.Add(new SummClass(1, "sub3", new DateTime(2019, 1, 15), 305));
list.Add(new SummClass(2, "sub1", new DateTime(2019, 1, 15), 520));
list.Add(new SummClass(2, "sub2", new DateTime(2019, 1, 15), 530));
list.Add(new SummClass(2, "sub4", new DateTime(2019, 1, 15), 530));
list.Add(new SummClass(3, "sub2", new DateTime(2019, 1, 15), 60));
list.Add(new SummClass(3, "sub3", new DateTime(2019, 1, 15), 560));
List <SummClass> listFromDB = new List <SummClass>();
using (ClickHouseConnection con = new ClickHouseConnection(settings))
{
con.Open();
ClickHouseCommand cmd = con.CreateCommand();
cmd.CommandText = "CREATE TEMPORARY TABLE temp (OrderID Int32, Subject String, Date Date, Summ Decimal(15,2)) ENGINE = Memory()";
cmd.ExecuteNonQuery();
var cmdInsert = con.CreateCommand("insert into temp values @bulk");
cmdInsert.Parameters.Add(new ClickHouseParameter {
ParameterName = "bulk", Value = list
});
cmdInsert.ExecuteNonQuery();
var reader = con.CreateCommand($"select OrderID,Subject,Date, toDecimal64(SUM(Summ),2) from temp group by OrderID,Subject,Date").ExecuteReader();
do
{
while (reader.Read())
{
SummClass sc = new SummClass();
sc.OrderID = (Int32)reader.GetInt32(0);
sc.Subject = (String)reader.GetString(1);
sc.Date = (DateTime)reader.GetDateTime(2);
sc.Summ = (Decimal)reader.GetDecimal(3);
listFromDB.Add(sc);
}
}while (reader.NextResult());
cmd.CommandText = "drop table temp";
cmd.ExecuteNonQuery();
con.Close();
}
Assert.Equal(listFromDB.Count, list.Count);
}
public void Close()
{
if (_connection == null || _connection.State != ConnectionState.Open)
{
throw new InvalidOperationException("Can't close connections that is not opened.");
}
_connection.Close();
_ownsConnection = false;
}
public static void SummingTest()
{
ClickHouseConnectionSettings set = new ClickHouseConnectionSettings();
set.Host = "localhost";
set.Port = 9000;
set.Compress = true;
set.User = "******";
set.Password = "";
using (ClickHouseConnection con = new ClickHouseConnection(set))
{
con.Open();
ClickHouseCommand cmd = con.CreateCommand();
cmd.CommandText = "CREATE DATABASE IF NOT EXISTS `test`;";
Console.WriteLine(cmd.ExecuteNonQuery());
cmd.CommandText = "DROP TABLE IF EXISTS test.sum_table;";
Console.WriteLine(cmd.ExecuteNonQuery());
cmd.CommandText = "CREATE TABLE IF NOT EXISTS test.sum_table ( OrderID Int16, SubjectID Int16, Subject String, EventDate Date, Summ Decimal(13,2)) ENGINE = SummingMergeTree(Summ) PARTITION BY toYYYYMM(EventDate) ORDER BY (OrderID,SubjectID,Subject,EventDate);";
Console.WriteLine(cmd.ExecuteNonQuery());
cmd.CommandText = "insert into test.sum_table values (1,1,'sub1','2021-01-01',1000)"; //(1,1,'sub1', '2020-01-01', 100.0)
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into test.sum_table values (2,2,'sub2','2022-01-01',2000)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into test.sum_table values (3,3,'sub3','2023-01-01',3000)";
cmd.ExecuteNonQuery();
var reader = con.CreateCommand("select * from test.sum_table").ExecuteReader();
PrintData(reader);
//select OrderID, SubjectID,Subject,EventDate, sum(Summ) from test.sum_table group by OrderID, SubjectID,Subject,EventDate
cmd.CommandText = "insert into test.sum_table select OrderID, SubjectID,Subject,EventDate, -1*(Summ) from test.sum_table where OrderID = 1"; //(1,1,'sub1', '2020-01-01', 100.0)
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into test.sum_table select OrderID, SubjectID,Subject,EventDate, -1*(Summ) from test.sum_table where OrderID = 2"; //(1,1,'sub1', '2020-01-01', 100.0)
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into test.sum_table select OrderID, SubjectID,Subject,EventDate, -1*(Summ) from test.sum_table where OrderID = 3"; //(1,1,'sub1', '2020-01-01', 100.0)
cmd.ExecuteNonQuery();
cmd.CommandText = "optimize table test.sum_table final";
cmd.ExecuteNonQuery();
reader = con.CreateCommand("select * from test.sum_table").ExecuteReader();
PrintData(reader);
con.Close();
}
}
public void Dispose()
{
if (_connection != null)
{
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
}
_connection.Dispose();
_connection = null;
}
}
public void SelectWhereSummNotNull()
{
List <SummClass> list = new List <SummClass>();
list.Add(new SummClass(1, "sub1", new DateTime(2019, 1, 15), 100));
list.Add(new SummClass(1, "sub1", new DateTime(2019, 1, 15), -100));
list.Add(new SummClass(2, "sub3", new DateTime(2019, 1, 15), 305));
list.Add(new SummClass(2, "sub1", new DateTime(2019, 1, 15), -520));
List <SummClass> listFromDB = new List <SummClass>();
using (ClickHouseConnection con = new ClickHouseConnection(settings))
{
con.Open();
ClickHouseCommand cmd = con.CreateCommand();
cmd.CommandText = "CREATE TEMPORARY TABLE temp (OrderID Int32, Subject String, Date Date, Summ Decimal(15,2)) ENGINE = Memory()";
cmd.ExecuteNonQuery();
var cmdInsert = con.CreateCommand("insert into temp values @bulk");
cmdInsert.Parameters.Add(new ClickHouseParameter {
ParameterName = "bulk", Value = list
});
cmdInsert.ExecuteNonQuery();
cmd.CommandText = $"insert into test.temp_table select OrderID,Subject,Date,toDecimal64(SUM(Summ),2) from temp group by OrderID,Subject,Date having SUM(Summ) != 0";
cmd.ExecuteNonQuery();
var reader = con.CreateCommand($"select OrderID,Subject,Date, toDecimal64(SUM(Summ),2) from test.temp_table group by OrderID,Subject,Date").ExecuteReader();
do
{
while (reader.Read())
{
listFromDB.Add(new SummClass((Int32)reader.GetInt32(0), (String)reader.GetString(1),
(DateTime)reader.GetDateTime(2), (Decimal)reader.GetDecimal(3)));
}
}while (reader.NextResult());
cmd.CommandText = "drop table temp";
cmd.ExecuteNonQuery();
con.Close();
}
Assert.Equal(2, listFromDB.Count);
}
public void ConnectionTest()
{
int res;
using (ClickHouseConnection con = new ClickHouseConnection(settings))
{
con.Open();
ClickHouseCommand cmd = con.CreateCommand();
cmd.CommandText = "select 1";
res = cmd.ExecuteNonQuery();
con.Close();
}
Assert.Equal(res, 0);
}
public ClickHouseDatabase(Configuration config)
{
_watcher = new TimeWatcher();
Writer = new Writer(NAME.ToLower() + ".data", 3);
_watcher.ReceivedRange += async(_, e) => await Writer.WriteAsync((int)e.Key, e.Value);
_logger = new Logger(NAME);
_configuration = config;
_connection = new ClickHouseConnection(_configuration.ConnectionString);
_connection.Open();
_connection.StateChange += (_, state) =>
{
if (state.CurrentState == ConnectionState.Broken)
{
_connection.Close();
_connection.Open();
}
};
}
public static void CHConn(Action action)
{
try
{
con = new ClickHouseConnection(str);
con.Open();
Console.WriteLine("DBConnected");
action();
}
catch (ClickHouseException err)
{
Console.WriteLine(err);
}
finally
{
if (con != null)
{
con.Close();
}
}
}
public void InsertDataIntoTempTableTest()
{
int res = 0;
List <SummClass> list = new List <SummClass>();
list.Add(new SummClass(1, "sub1", new DateTime(2019, 1, 15), 100));
list.Add(new SummClass(1, "sub2", new DateTime(2019, 1, 15), 450));
list.Add(new SummClass(1, "sub3", new DateTime(2019, 1, 15), 305));
list.Add(new SummClass(2, "sub1", new DateTime(2019, 1, 15), 520));
list.Add(new SummClass(2, "sub2", new DateTime(2019, 1, 15), 530));
list.Add(new SummClass(2, "sub4", new DateTime(2019, 1, 15), 530));
list.Add(new SummClass(3, "sub2", new DateTime(2019, 1, 15), 60));
list.Add(new SummClass(3, "sub3", new DateTime(2019, 1, 15), 560));
using (ClickHouseConnection con = new ClickHouseConnection(settings))
{
con.Open();
ClickHouseCommand cmd = con.CreateCommand();
cmd.CommandText = "CREATE TEMPORARY TABLE temp (OrderID Int32, Subject String, Date Date, Summ Decimal(15,2)) ENGINE = Memory()";
res += cmd.ExecuteNonQuery();
var cmdInsert = con.CreateCommand("insert into temp values @bulk");
cmdInsert.Parameters.Add(new ClickHouseParameter {
ParameterName = "bulk", Value = list
});
res += cmdInsert.ExecuteNonQuery();
cmd.CommandText = "drop table temp";
res += cmd.ExecuteNonQuery();
con.Close();
}
Assert.Equal(0, res);
}
public static void UpdateOrderData(List <SummClass> list, Int32 OrderID)
{
using (ClickHouseConnection con = new ClickHouseConnection(CreateConnectionSettings()))
{
con.Open();
ClickHouseCommand cmd = con.CreateCommand();
cmd.CommandText = "CREATE TEMPORARY TABLE temp (OrderID Int32, Subject String, Date Date, Summ Decimal(15,2)) ENGINE = Memory()";
Console.WriteLine(cmd.ExecuteNonQuery());
// insert into temp table reverse records
cmd.CommandText = $"insert into temp select OrderID,Subject,Date, -1*SUM(Summ) from test.temp_table where OrderID ={OrderID} group by OrderID,Subject,Date";
cmd.ExecuteNonQuery();
//insert into temp table new list of records
var cmdInsert = con.CreateCommand("insert into temp values @bulk");
cmdInsert.Parameters.Add(new ClickHouseParameter {
ParameterName = "bulk", Value = list
});
cmdInsert.ExecuteNonQuery();
// insert everything from temp to test.temp_table
cmd.CommandText = "insert into test.temp_table select * from temp";
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table temp";
cmd.ExecuteNonQuery();
// cmd.CommandText = "optimize table test.temp_table final";
// cmd.ExecuteNonQuery();
con.Close();
}
}
public static void CreateTable()
{
using (ClickHouseConnection con = new ClickHouseConnection(CreateConnectionSettings()))
{
con.Open();
//var cmd = con.CreateCommand();
ClickHouseCommand cmd = con.CreateCommand();
cmd.CommandText = "CREATE DATABASE IF NOT EXISTS `test`;";
Console.WriteLine(cmd.ExecuteNonQuery());
cmd.CommandText = "DROP TABLE IF EXISTS test.temp_table;";
Console.WriteLine(cmd.ExecuteNonQuery());
cmd.CommandText = "CREATE TABLE IF NOT EXISTS test.temp_table ( OrderID Int32, Subject String, Date Date, Summ Decimal(15,2)) ENGINE = SummingMergeTree(Summ) PARTITION BY toYYYYMM(Date) ORDER BY (OrderID,Subject,Date);";
Console.WriteLine(cmd.ExecuteNonQuery());
con.Close();
}
}
public static void firstTest()
{
ClickHouseConnectionSettings set = new ClickHouseConnectionSettings();
set.Database = "default";
set.Host = "localhost";
set.Port = 9000;
// set.SocketTimeout = 60000000;
set.Compress = true;
set.User = "******";
set.Password = "";
List <Hits> list = new List <Hits>();
//using (ClickHouseConnection con = new ClickHouseConnection("Host=127.0.0.1;Port=9000;User=default;Password=default;Compress=True;CheckCompressedHash=False;SocketTimeout=60000000;Compressor=lz4"))
using (ClickHouseConnection con = new ClickHouseConnection(set))
{
con.Open();
//var cmd = con.CreateCommand();
ClickHouseCommand cmd = new ClickHouseCommand();
cmd.Connection = con;
cmd.CommandTimeout = 300;
// cmd.CommandText = "CREATE DATABASE IF NOT EXISTS `firstdb`;";
// cmd.CommandText = "SELECT WatchID from default.hits_100m_obfuscated limit 10";
// Console.WriteLine(cmd.ExecuteNonQuery());
// cmd.CommandText = "DROP TABLE IF EXISTS firstdb.firsttbl;";
// Console.WriteLine(cmd.ExecuteNonQuery());
//
// cmd.CommandText = "CREATE TABLE IF NOT EXISTS firstdb.firsttbl ( EventDate Date, ColId UInt64, ParamOrderID String ) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (ColId);";
// Console.WriteLine(cmd.ExecuteNonQuery());
// con.CreateCommand(
// "INSERT INTO firstdb.firsttbl (EventDate, ColId, ParamOrderID) VALUES ('2019-01-01', 1, 'str1')").ExecuteNonQuery();
// con.CreateCommand(
// "INSERT INTO firstdb.firsttbl (EventDate, ColId, ParamOrderID) VALUES ('2019-01-01', 2, 'str2')").ExecuteNonQuery();
// con.CreateCommand(
// "INSERT INTO firstdb.firsttbl (EventDate, ColId, ParamOrderID) VALUES ('2019-01-01', 3, 'str3')").ExecuteNonQuery();
// cmd.CommandText = "SELECT * from firstdb.firsttbl;";
var reader = con.CreateCommand("SELECT WatchID, JavaEnable, Title, EventTime, EventDate from default.hits_100m_obfuscated limit 10").ExecuteReader();
//var reader = con.CreateCommand("SELECT JavaEnable, Title from default.hits_100m_obfuscated limit 10").ExecuteReader();
// PrintData(reader);
//for(int i = 0; i < reader.FieldCount; i++)
// while (reader.Read())
// int i = 0;
do
{
// Console.WriteLine(reader.GetName(i));
while (reader.Read())
{
/*
* Console.WriteLine($"WatchID = {reader.GetString(0)}");
* Console.WriteLine($"JavaEnable = {reader.GetInt16(1)}");
* Console.WriteLine($"Title = {reader.GetString(2)}");
* Console.WriteLine($"EventTime = {reader.GetDateTime(3)}");
* Console.WriteLine($"EventDate = {reader.GetDateTime(4)}");
*/
Hits hit = new Hits();
hit.WatchID = (String)reader.GetString(0);
hit.JavaEnable = reader.GetInt16(1);
hit.Title = reader.GetString(2);
hit.EventTime = reader.GetDateTime(3);
hit.EventDate = reader.GetDateTime(4);
list.Add(hit);
// var me = reader.GetInt16(1);
}
//
// object[] obj = new object[10];
//var rere = reader.Read();
//var me = ((ClickHouseDataReader)reader).GetString(2);
//i++;
}while (reader.NextResult());
// PrintData(reader);
// foreach (var el in list)
// {
// Console.WriteLine(el.WatchID + "\t"+ el.JavaEnable+"\t"+ el.Title+"\t"+el.EventTime+"\t"+ el.EventDate);
// }
ClickHouseConnectionSettings set2 = new ClickHouseConnectionSettings();
// set2.Database = "tutorail";
set2.Host = "localhost";
set2.Port = 9000;
// set.SocketTimeout = 60000000;
set2.Compress = true;
set2.User = "******";
set2.Password = "";
using (ClickHouseConnection con2 = new ClickHouseConnection(set2))
{
con2.Open();
//var cmd = con.CreateCommand();
ClickHouseCommand cmd2 = con2.CreateCommand();
cmd2.CommandText = "CREATE DATABASE IF NOT EXISTS `test`;";
Console.WriteLine(cmd2.ExecuteNonQuery());
cmd2.CommandText = "DROP TABLE IF EXISTS test.test_table;";
Console.WriteLine(cmd2.ExecuteNonQuery());
cmd2.CommandText = "CREATE TABLE IF NOT EXISTS test.test_table ( WatchID String, JavaEnable Int16, Title String, EventTime DateTime, EventDate Date) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (WatchID);";
Console.WriteLine(cmd2.ExecuteNonQuery());
var cmdInsert = con2.CreateCommand("insert into test.test_table (WatchID, JavaEnable, Title, EventTime, EventDate) values @bulk");
cmdInsert.Parameters.Add(new ClickHouseParameter {
ParameterName = "bulk", Value = list
});
cmdInsert.ExecuteNonQuery();
con2.Close();
}
}
}
public static bool CheckOrderData(List <SummClass> list, Int32 OrderID)
{
List <SummClass> listFromDB = new List <SummClass>();
using (ClickHouseConnection con = new ClickHouseConnection(CreateConnectionSettings()))
{
con.Open();
//var cmd = con.CreateCommand();
//cmd.CommandText = "select OrderID,Subject,Date,-1*SUM(Summ) from test.temp_table group by OrderID,Subject,Date";
//Console.WriteLine(cmd.ExecuteNonQuery());
var reader = con.CreateCommand($"select OrderID,Subject,Date, toDecimal64(SUM(Summ),2) from test.temp_table where OrderID = {OrderID.ToString()} group by OrderID,Subject,Date having SUM(Summ) != 0").ExecuteReader();
do
{
while (reader.Read())
{
SummClass sc = new SummClass();
sc.OrderID = (Int32)reader.GetInt32(0);
sc.Subject = (String)reader.GetString(1);
sc.Date = (DateTime)reader.GetDateTime(2);
sc.Summ = (Decimal)reader.GetDecimal(3);
listFromDB.Add(sc);
}
}while (reader.NextResult());
con.Close();
}
List <SummClass> groupedList = new List <SummClass>();
for (int i = 0; i < list.Count; i++)
{
var el = groupedList.Where(x => x.OrderID == list.ElementAt(i).OrderID &&
x.Subject == list.ElementAt(i).Subject &&
x.Date == list.ElementAt(i).Date).Select(x => x);
if (el.Count() == 0)
{
groupedList.Add(new SummClass {
OrderID = list.ElementAt(i).OrderID,
Subject = list.ElementAt(i).Subject, Date = list.ElementAt(i).Date,
Summ = list.ElementAt(i).Summ
});
}
else
if (el.Count() > 1)
{
throw new Exception("smth goes wrong");
}
else
{
el.ElementAt(0).Summ += list.ElementAt(i).Summ;
}
}
// if(groupedList.Count() != listFromDB.Count())
// return false;
// else
foreach (var el1 in listFromDB)
{
foreach (var el2 in groupedList)
{
if (el1.OrderID == el2.OrderID && el1.Subject == el2.Subject &&
el1.Date == el2.Date)
{
if (el1.Summ != el2.Summ)
{
return(false);
}
}
}
}
return(true);
}
