public async Task ShouldExecuteParameterizedCompareWithTypeDetection(string exampleExpression, string clickHouseType, object value)
{
if (clickHouseType.StartsWith("DateTime64") || clickHouseType == "Date")
{
Assert.Pass("Automatic type detection does not work for " + clickHouseType);
}
if (clickHouseType.StartsWith("Enum"))
{
clickHouseType = "String";
}
using var command = connection.CreateCommand();
command.CommandText = $"SELECT {exampleExpression} as expected, {{var:{clickHouseType}}} as actual, expected = actual as equals";
command.AddParameter("var", value);
var result = (await command.ExecuteReaderAsync()).GetEnsureSingleRow();
Assert.AreEqual(result[0], result[1]);
if (value is null || value is DBNull)
{
Assert.IsInstanceOf <DBNull>(result[2]);
}
//else
//{
// Assert.AreEqual(1, result[2], $"Equality check in ClickHouse failed: {result[0]} {result[1]}");
//}
}
public static void CreateCHTable(List <string> headers, List <string> types, string tableName)
{
string createTableStr = "CREATE TABLE " + tableName + "( ";
for (int i = 0; i < headers.Count; i++)
{
createTableStr += headers[i] + " " + types[i] + ", ";
}
createTableStr = createTableStr.Substring(0, createTableStr.Length - 2);
createTableStr += " ) ENGINE = Log;";
ClickHouseCommand comm = con.CreateCommand();
comm.CommandText = createTableStr;
try
{
comm.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
public (Dictionary <long, Dictionary <string, dynamic> >, List <string>) LogActionsPerHour()
{
using ClickHouseCommand cmd = _conn.CreateCommand("SELECT * FROM log_actions_per_hour;");
using IDataReader reader = cmd.ExecuteReader();
var actions = new List <string>();
Dictionary <long, Dictionary <string, dynamic> > result = new Dictionary <long, Dictionary <string, dynamic> >();
do
{
while (reader.Read())
{
DateTime hour = DateTime.ParseExact(reader.GetString(0), "yyyy-MM-dd H:mm", CultureInfo.InvariantCulture);
var action = reader.GetString(1);
long c = reader.GetInt64(2);
if (!result.ContainsKey(hour.Ticks))
{
result[hour.Ticks] = new Dictionary <string, dynamic>
{
{ "time", hour }
};
}
if (!actions.Contains(action))
{
actions.Add(action);
}
result[hour.Ticks][action] = c;
}
} while (reader.NextResult());
return(result, actions);
}
public void Preview(string table)
{
WaitScreen.Splash();
int limit = int.Parse(Limit.Text);
using (ClickHouseCommand comm = con.CreateCommand($"SELECT * FROM { table } LIMIT { limit }"))
{
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);
CHBox.AppendText(result[i].ToString() + ", ");
}
CHBox.AppendText(Environment.NewLine);
result.Clear();
}
} while (reader.NextResult());
}
}
}
protected virtual void InitializeTable()
{
var conn = new ClickHouseConnection(_connectionString);
conn.Open();
var command = conn.CreateCommand();
command.CommandText = $"CREATE DATABASE IF NOT EXISTS {_database};";
command.ExecuteNonQuery();
if (Options.DropTable)
{
var sql = $"DROP TABLE IF EXISTS {_database}.{_table};";
Logger.Warning(sql);
command = conn.CreateCommand();
command.CommandText = sql;
command.ExecuteNonQuery();
}
command = conn.CreateCommand();
command.CommandText = GenerateCreateTableSql();
command.ExecuteNonQuery();
}
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";
}
}
public DateTime GetRowsDataMaxPeriod(InformationSystemsBase system)
{
DateTime output = DateTime.MinValue;
using (var command = _connection.CreateCommand())
{
command.CommandText =
@"SELECT
MAX(Period) AS MaxPeriod
FROM RowsData AS RD
WHERE InformationSystem = {InformationSystem:String} ";
command.Parameters.Add(new ClickHouseDbParameter
{
ParameterName = "InformationSystem",
Value = system.Name
});
using (var cmdReader = command.ExecuteReader())
{
if (cmdReader.Read())
{
output = cmdReader.GetDateTime(0);
}
}
}
return(output);
}
public void ClickHouseInsertWeb(List <RedisWebEnum> web)
{
ClickHouseCommand command = ClickHouseconnection.CreateCommand();
command.CommandText = "INSERT INTO earthlink (user, type, value) VALUES @bulk";
command.AddParameter("bulk", web);
command.ExecuteNonQuery();
}
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 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 Select1(string table)
{
List <string> columns = null;
using (ClickHouseCommand comm = con.CreateCommand($"SELECT * FROM system.columns where table = '{table}'"))
{
using (var reader = comm.ExecuteReader())
{
columns = GetData(reader).OfType <string>().ToList();
}
}
}
public void GetTableList()
{
List <object> tableList = null;
using (ClickHouseCommand comm = con.CreateCommand("SELECT name FROM system.tables"))
{
using (var reader = comm.ExecuteReader())
{
tableList = GetData(reader);
CHTablesComboBox.ItemsSource = tableList;
}
}
}
/// <summary>
/// 该方法用于编辑数据库
/// </summary>
public void EditDatabase(string table0)
{
WinFromEditTable myform = new WinFromEditTable(table0);
if (myform.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string table1 = myform.table1;
Setting._currentdb = table1;
string str_sql = $"rename table {table0} TO {table1}";
_mycon.CreateCommand(str_sql).ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show($"数据表 {table1} 改名已成功!");
}
}
public async Task ShouldExecuteParameterizedSelectWithExplicitType(string exampleExpression, string clickHouseType, object value)
{
if (clickHouseType.StartsWith("Enum"))
{
clickHouseType = "String";
}
using var command = connection.CreateCommand();
command.CommandText = $"SELECT {{var:{clickHouseType}}} as res";
command.AddParameter("var", clickHouseType, value);
var result = (await command.ExecuteReaderAsync()).GetEnsureSingleRow().Single();
Assert.AreEqual(value, result);
}
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);
}
/// <summary>
///
/// </summary>
/// <param name="columns"></param>
/// <returns></returns>
public async Task InsertOneAsync(IReadOnlyDictionary <string, object> columns, CancellationToken token = default)
{
_logger.Write("[InsertOne] Executing");
using var command = _connection.CreateCommand(_configuration.InsertOneScript);
foreach (var column in columns)
{
command.Parameters.AddWithValue(column.Key, column.Value);
}
using (_watcher.Watch(TimeWatcher.Operation.InsertOne))
{
await command.ExecuteNonQueryAsync(token);
}
_logger.Write("[InsertOne] Executed");
}
public static void Backup(string table)
{
string command = $"SELECT * FROM { table }",
path = $@"D:\{ DateTime.Now.Date.ToString("d") }-{ table }.txt";
using (ClickHouseCommand comm = con.CreateCommand(command))
{
using (var reader = comm.ExecuteReader())
{
do
{
using (StreamWriter file = new StreamWriter(path, true))
{
while (reader.Read())
{
string line = "";
for (var i = 0; i < reader.FieldCount; i++)
{
var val = reader.GetValue(i);
line += $"{ val },";
}
line = line.Substring(0, line.Length - 1);
file.WriteLine(line);
}
}
} while (reader.NextResult());
}
}
}
public ClickHouseContext(string connectionSettings, IExtendedActions extendedActions)
{
_extendedActions = extendedActions;
CheckDatabaseSettings(connectionSettings);
_connection = new ClickHouseConnection(connectionSettings);
_connection.Open();
var cmdDDL = _connection.CreateCommand();
var onModelCreatingParameters = new OnDatabaseModelConfiguringParameters()
{
Query_CreateTable_LogFiles = Resources.Query_CreateTable_LogFiles,
Query_CreateTable_RowsData = Resources.Query_CreateTable_RowsData
};
_extendedActions?.OnDatabaseModelConfiguring(this, onModelCreatingParameters);
cmdDDL.CommandText = onModelCreatingParameters.Query_CreateTable_RowsData
.Replace("{TemplateFields}", string.Empty);
cmdDDL.ExecuteNonQuery();
cmdDDL.CommandText = onModelCreatingParameters.Query_CreateTable_LogFiles
.Replace("{TemplateFields}", string.Empty);
cmdDDL.ExecuteNonQuery();
}
public void Setup()
{
_connectionSettings = ConnectionSettingsHelper.GetConnectionSettings();
using (var connection = new ClickHouseConnection(_connectionSettings))
{
connection.Open();
var cmd = connection.CreateCommand("DROP TABLE IF EXISTS ColumnWriterInsertBenchmarks");
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE ColumnWriterInsertBenchmarks(id UUID, str Nullable(String), dt DateTime, val Decimal64(4)) ENGINE = Memory";
cmd.ExecuteNonQuery();
}
_id = new List <Guid>(Rows);
_str = new List <string?>(Rows);
_dt = new List <DateTime>(Rows);
_val = new List <decimal>(Rows);
const string strSource =
"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.";
var now = DateTime.Now;
var rnd = new Random(2306);
for (int i = 0; i < Rows; i++)
{
_id.Add(Guid.NewGuid());
_str.Add(i % strSource.Length == 0 ? null : strSource.Substring(0, i % strSource.Length));
_dt.Add(now.AddSeconds(i));
_val.Add(Math.Round((decimal)(rnd.NextDouble() - 0.5) * 100_000, 4));
}
}
/// <summary>
/// 查询数据到datatable
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable GetDataFromTable(string sql)
{
DataTable mydt = new DataTable();
object[][] kk = null;
kk = mydb.ExecuteSelectCommand(sql, true);
var reader = _mycon.CreateCommand(sql).ExecuteReader();
for (int i = 0; i < reader.FieldCount; i++)
{
mydt.Columns.Add(reader[i].ToString());
}
while (reader.Read())
{
}
for (int i = 0; i < kk[0].Length; i++)
{
mydt.Columns.Add(kk[0][i].ToString());
}
DataRow dr = null;
for (int i = 1; i < kk.Length; i++)
{
dr = mydt.Rows.Add();
for (int j = 0; j < kk[i].Length; j++)
{
dr[j] = kk[i][j];
}
//mydt.ImportRow(dr);
}
return(mydt);
}
protected AbstractConnectionTestFixture()
{
connection = TestUtilities.GetTestClickHouseConnection();
using var command = connection.CreateCommand();
command.CommandText = "CREATE DATABASE IF NOT EXISTS test;";
command.ExecuteScalar();
}
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);
}
public override void FillTableSchema(DataTable table, string selectCommand, CommandParameterCollection parameters)
{
ClickHouseConnection clickHouseConnection = GetConnection() as ClickHouseConnection;
try
{
OpenConnection(clickHouseConnection);
selectCommand = PrepareSelectCommand(selectCommand, table.TableName, clickHouseConnection);
/*To reduce size of traffic and size of answer from ClickHouse server.
* Because FillSchema doesn't work in this ADO.NET library.
* LIMIT 0 gets an empy set, but we still have list of desired columns
* Prorably can be a better way.
*/
selectCommand += " LIMIT 0";
ClickHouseCommand clickHouseCommand = clickHouseConnection.CreateCommand();
foreach (CommandParameter p in parameters)
{
selectCommand = selectCommand.Replace($"@{p.Name}", $"{{{p.Name}:{(ClickHouseTypeCode)p.DataType}}}");
clickHouseCommand.AddParameter(p.Name, ((ClickHouseTypeCode)p.DataType).ToString(), p.Value);
}
clickHouseCommand.CommandText = selectCommand;
using (ClickHouseDataReader reader = clickHouseCommand.ExecuteReader() as ClickHouseDataReader)
{
var clms = GetColumns(reader);
table.Columns.AddRange(clms.ToArray());
}
}
finally
{
DisposeConnection(clickHouseConnection);
}
}
private async Task CreateEventLogItemsDatabaseAsync(CancellationToken cancellationToken = default)
{
var commandDbText = $@"CREATE DATABASE IF NOT EXISTS {_databaseName}";
await using var cmdDb = _connection.CreateCommand();
cmdDb.CommandText = commandDbText;
await cmdDb.ExecuteNonQueryAsync(cancellationToken);
await _connection.ChangeDatabaseAsync(_databaseName, cancellationToken);
var commandText =
$@"CREATE TABLE IF NOT EXISTS {TableName}
(
FileName LowCardinality(String),
EndPosition Int64 Codec(DoubleDelta, LZ4),
LgfEndPosition Int64 Codec(DoubleDelta, LZ4),
Id Int64 Codec(DoubleDelta, LZ4),
DateTime DateTime('UTC') Codec(Delta, LZ4),
TransactionStatus LowCardinality(String),
TransactionDate DateTime('UTC') Codec(Delta, LZ4),
TransactionNumber Int64 Codec(DoubleDelta, LZ4),
UserUuid LowCardinality(String),
User LowCardinality(String),
Computer LowCardinality(String),
Application LowCardinality(String),
Connection Int64 Codec(DoubleDelta, LZ4),
Event LowCardinality(String),
Severity LowCardinality(String),
Comment String Codec(ZSTD),
MetadataUuid String Codec(ZSTD),
Metadata LowCardinality(String),
Data String Codec(ZSTD),
DataPresentation String Codec(ZSTD),
Server LowCardinality(String),
MainPort Int32 Codec(DoubleDelta, LZ4),
AddPort Int32 Codec(DoubleDelta, LZ4),
Session Int64 Codec(DoubleDelta, LZ4)
)
engine = MergeTree()
PARTITION BY (toYYYYMM(DateTime))
ORDER BY (DateTime, EndPosition)
SETTINGS index_granularity = 8192;";
await using var cmd = _connection.CreateCommand();
cmd.CommandText = commandText;
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
public static void CreateTables()
{
List <String> commands = new List <string>();
commands.Add("DROP TABLE IF EXISTS default.phone_numbers;");
commands.Add("DROP TABLE IF EXISTS default.calls;");
commands.Add("DROP TABLE IF EXISTS default.relation;");
commands.Add("CREATE TABLE phone_numbers (" +
"id Int32, " +
"ph_num Int32, " +
"mon Int32, " +
"tue Int32, " +
"wed Int32, " +
"thu Int32, " +
"fri Int32, " +
"sat Int32, " +
"sun Int32, " +
"typ Int32, " +
"week Int32, " +
"weekend Int32" +
") ENGINE = MergeTree () " +
"ORDER BY (id)");
commands.Add("CREATE TABLE calls (" +
"phone_number Int32, " +
"contact_number Int32, " +
"start DateTime, " +
"end DateTime " +
") ENGINE = MergeTree () " +
"ORDER BY (phone_number)");
commands.Add("CREATE TABLE relation (" +
"id_num Int32, " +
"id_con Int32, " +
"average_time Int32" +
") ENGINE = MergeTree() " +
"ORDER BY id_num");
for (int i = 0; i < commands.Count; i++)
{
ClickHouseCommand comm = con.CreateCommand(commands[i]);
comm.ExecuteNonQuery();
}
FillDB();
Console.WriteLine("Streaming will start automatically.");
BulkToCH();
}
public void Execute(string command)
{
using var connection = new ClickHouseConnection(new ClickHouseConnectionSettings(_connectionString));
connection.Open();
using var cmd = connection.CreateCommand();
cmd.CommandText = command;
cmd.ExecuteNonQuery();
}
public void IterationSetup()
{
connection = OpenConnection();
IterationCleanup();
var cmd = connection.CreateCommand($"CREATE TABLE {TableName}(id Int32, str Nullable(String)) ENGINE=Memory");
cmd.ExecuteNonQuery();
}
public async Task WriteLastPositionAsync(string folder, string file, long position)
{
var cmd = _connection.CreateCommand();
var lastPosition = await GetLastPositionAsync(folder, file);
// ReSharper disable once ConvertIfStatementToConditionalTernaryExpression
if (lastPosition == 0)
{
cmd.CommandText = $"INSERT INTO LastPositions (Folder, File, Position) VALUES ('{folder}', '{file}', {position})";
}
else
{
cmd.CommandText = $"ALTER TABLE LastPositions UPDATE Position = {position} WHERE Folder = '{folder}' AND File = '{file}'";
}
await cmd.ExecuteNonQueryAsync();
}
private void ExportToClickHouse(EventLogExportSettings eventLogSettings)
{
Console.WriteLine(_settings.ConnectionString);
ClickHouseHelpers.DropDatabaseIfExist(_settings.ConnectionString);
EventLogOnClickHouse target = new EventLogOnClickHouse(_settings.ConnectionString, eventLogSettings.Portion);
target.SetInformationSystem(new InformationSystemsBase()
{
Name = eventLogSettings.InforamtionSystemName,
Description = eventLogSettings.InforamtionSystemDescription
});
ExportHelper.ExportToTargetStorage(eventLogSettings, target);
long rowsInDB;
using (var connection = new ClickHouseConnection(_settings.ConnectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText =
@"SELECT
COUNT(*) CNT
FROM RowsData rd
WHERE InformationSystem = {InformationSystem:String}";
cmd.Parameters.Add(new ClickHouseDbParameter
{
ParameterName = "InformationSystem",
Value = eventLogSettings.InforamtionSystemName
});
using (var cmdReader = cmd.ExecuteReader())
{
if (cmdReader.Read())
{
rowsInDB = Convert.ToInt64(cmdReader.GetValue(0));
}
else
{
rowsInDB = 0;
}
}
}
}
long rowsInSourceFiles;
using (EventLogReader reader = EventLogReader.CreateReader(eventLogSettings.EventLogPath))
rowsInSourceFiles = reader.Count();
Assert.NotEqual(0, rowsInSourceFiles);
Assert.NotEqual(0, rowsInDB);
Assert.Equal(rowsInSourceFiles, rowsInDB);
}
public void GlobalSetup()
{
GlobalCleanup();
var cmd = connection.CreateCommand($"CREATE TABLE {TableName}(id Int32, str Nullable(String)) ENGINE=Memory");
cmd.ExecuteNonQuery();
idEnumerable = Enumerable.Range(0, valuesCount);
strEnumerable = idEnumerable.Select(x => x.ToString());
//Fill Table
using (var writer = connection.CreateColumnWriter($"INSERT INTO {TableName}(id, str) VALUES"))
{
var writeObject = new object[] { idEnumerable, strEnumerable };
writer.WriteTable(writeObject, valuesCount);
writer.EndWrite();
}
}
