private ClickHouseConnection GetConnection(
string cstr = "Compress=False;BufferSize=32768;SocketTimeout=10000;CheckCompressedHash=False;Compressor=lz4;Host=file-server;Port=9000;Database=default;User=test;Password=123")
{
var settings = new ClickHouseConnectionSettings(cstr);
var cnn = new ClickHouseConnection(settings);
cnn.Open();
return(cnn);
}
static private ClickHouseConnection GetConnection(string cstr = "Compress=True;CheckCompressedHash=False;Compressor=lz4;Host=192.168.1.120;Port=9000;Database=stock18;User=default;")
//(string cstr = "Compress=True;CheckCompressedHash=False;Compressor=lz4;Host=ch-test.flippingbook.com;Port=9000;Database=default;User=andreya;Password=123")
{
var settings = new ClickHouseConnectionSettings(cstr);
var cnn = new ClickHouseConnection(settings);
cnn.Open();
return(cnn);
}
public static ClickHouseConnection GetConnection(
string cstr = "Compress=False;BufferSize=32768;SocketTimeout=10000;CheckCompressedHash=False;Encrypt=False;Compressor=lz4;Host=ch-test.flippingbook.com;Port=9000;Database=default;User=andreya;Password=123")
{
var settings = new ClickHouseConnectionSettings(cstr);
var cnn = new ClickHouseConnection(settings);
cnn.Open();
return(cnn);
}
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();
}
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);
}
private ClickHouseConnection GetConnection(string host, int port)
{
var connectionString =
$"Compress=True;CheckCompressedHash=False;Compressor=lz4;Host={host};Port={port};Database=default;User=default;Password=;ConnectionTimeout=10000;SocketTimeout=10000; DataTransferTimeout=10000; MaxExecutionTime=10000;";
var settings = new ClickHouseConnectionSettings(connectionString);
var cnn = new ClickHouseConnection(settings);
cnn.Open();
return(cnn);
}
public void TruncateTable(ClickHouseTable table, bool ifExists = false)
{
string cluster = CreateParameters.NodeMode == ClickHouseNodeMode.Replicated ? CreateParameters.Cluster : null;
using (ClickHouseConnection conn = new ClickHouseConnection(ConnectionSettings))
{
conn.Open();
table.Truncate(conn, db: ConnectionSettings.Database, ifExists: ifExists, cluster: cluster);
}
}
public void DropDatabase(bool ifExists = false)
{
string cluster = CreateParameters.NodeMode == ClickHouseNodeMode.Replicated ? CreateParameters.Cluster : null;
using (ClickHouseConnection conn = new ClickHouseConnection(ConnectionSettings.GetConnectionWithoutDb()))
{
conn.Open();
conn.Execute(DatabaseCommandText.DropDatabase(db: ConnectionSettings.Database, ifExists: ifExists, cluster: cluster));
}
}
public static string GetClusterName(this ClickHouseConnectionSettings settings)
{
string commandText = $"SELECT cluster FROM system.clusters where is_local = 1 and host_name = '{settings.Host}' LIMIT 1";
using (ClickHouseConnection conn = new ClickHouseConnection(settings.GetConnectionWithoutDb()))
{
conn.Open();
return(conn.Scalar <string>(commandText));
}
}
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 Open()
{
if (_ownsConnection)
{
Close();
}
_connection = _connectionFactory.CreateConnection(_connectionSettings);
_connection.Open();
_ownsConnection = true;
}
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 void ShouldUnwrapConnectionOpenExceptions()
{
var sb = new ClickHouseConnectionStringBuilder {
Host = "none.example.com"
};
using var conn = new ClickHouseConnection(sb);
var exception = Assert.Throws <SocketException>(() => conn.Open());
Assert.Equal(SocketError.HostNotFound, exception.SocketErrorCode);
}
public static bool CheckConnection(this ClickHouseConnectionSettings settings)
{
try
{
using (ClickHouseConnection conn = new ClickHouseConnection(settings))
{
conn.Open();
return(true);
}
}
catch { return(false); }
}
protected virtual ClickHouseConnection CreateClickHouseConnection(string database = null)
{
string connectStr = $"Compress=True;CheckCompressedHash=False;Compressor=lz4;Host={_arguments.Host};Port={_arguments.Port};Database=system;User={_arguments.User};Password={_arguments.Password}";
var settings = new ClickHouseConnectionSettings(connectStr);
var cnn = new ClickHouseConnection(settings);
cnn.Open();
if (!string.IsNullOrWhiteSpace(database))
{
cnn.Execute($"USE {database};");
}
return(cnn);
}
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 ClickHouseConnection CreateConnection()
{
if (_clickHouseConnection == null)
{
var settings = new ClickHouseConnectionSettings(_connectionString);
var cnn = new ClickHouseConnection(settings);
if (cnn.State != ConnectionState.Open)
{
cnn.Open();
}
_clickHouseConnection = cnn;
}
return(_clickHouseConnection);
}
public ClickHouseContext(string connectionSettings)
{
CheckDatabaseSettings(connectionSettings);
_connection = new ClickHouseConnection(connectionSettings);
_connection.Open();
var cmdDDL = _connection.CreateCommand();
cmdDDL.CommandText = Resources.Query_CreateTable_RowsData;
cmdDDL.ExecuteNonQuery();
cmdDDL.CommandText = Resources.Query_CreateTable_LogFiles;
cmdDDL.ExecuteNonQuery();
}
public ClickHouseContext(string connectionSettings)
{
ClickHouseHelpers.CreateDatabaseIfNotExist(connectionSettings);
_connection = new ClickHouseConnection(connectionSettings);
_connection.Open();
var cmdDDL = _connection.CreateCommand();
cmdDDL.CommandText = Resources.Query_CreateTable_XEventData;
cmdDDL.ExecuteNonQuery();
cmdDDL.CommandText = Resources.Query_CreateTable_LogFiles;
cmdDDL.ExecuteNonQuery();
}
public void CreateAllViews(bool ifNotExists = false, bool dropIfExists = false)
{
if (Schema.Views.Count == 0)
{
return;
}
using (ClickHouseConnection conn = new ClickHouseConnection(ConnectionSettings))
{
conn.Open();
if (dropIfExists)
{
Schema.Views.ForEach(view => view.Drop(conn, ConnectionSettings.Database, ifExists: true));
}
Schema.Views.ForEach(view => view.Create(conn, ConnectionSettings.Database, ifNotExists: ifNotExists));
}
}
public void CreateAllDictionaries(bool ifNotExists = false, bool dropIfExists = false)
{
if (Schema.Dictionaries.Count == 0)
{
return;
}
using (ClickHouseConnection conn = new ClickHouseConnection(ConnectionSettings))
{
conn.Open();
if (dropIfExists)
{
Schema.Dictionaries.ForEach(dic => dic.Drop(conn, db: ConnectionSettings.Database, ifExists: true));
}
Schema.Dictionaries.ForEach(dic => dic.Create(conn, db: ConnectionSettings.Database, ifNotExists: ifNotExists));
}
}
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 void Flush(IEnumerable <TColumnFormatter> buff)
{
if (buff.Any())
{
using var connection = new ClickHouseConnection(new ClickHouseConnectionSettings(_connectionString));
connection.Open();
using var cmd = connection.CreateCommand();
cmd.CommandText = _table.Insert;
cmd.Parameters.Add(new ClickHouseParameter
{
ParameterName = "bulk",
Value = buff
});
cmd.ExecuteNonQuery();
}
}
private static void ExecNonReaderQuery(string connectionSettings, string commandTest)
{
string databaseName = GetDatabaseName(connectionSettings, out var paramName, out var paramValue);
if (databaseName != null)
{
string connectionStringDefault = connectionSettings.Replace(
$"{paramName}={paramValue}",
$"Database=default"
);
using (var defaultConnection = new ClickHouseConnection(connectionStringDefault))
{
defaultConnection.Open();
var cmdDefault = defaultConnection.CreateCommand();
cmdDefault.CommandText = commandTest;
cmdDefault.ExecuteNonQuery();
}
}
}
static void Main(string[] args)
{
var settings = new ClickHouseConnectionSettings("Compress=False;Compressor=lz4;Host=34.197.206.226;Port=9000;Database=default;User=andreya;Password=123");
var cnn = new ClickHouseConnection(settings);
cnn.Open();
using (var reader = cnn.CreateCommand("SELECT * FROM test_data").ExecuteReader())
{
do
{
Console.Write("Fields: ");
for (var i = 0; i < reader.FieldCount; i++)
{
Console.Write("{0}:{1} ", reader.GetName(i), reader.GetDataTypeName(i));
}
Console.WriteLine();
while (reader.Read())
{
for (var i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i));
Console.Write(", ");
}
Console.WriteLine();
}
Console.WriteLine();
} while (reader.NextResult());
}
var cmd = cnn.CreateCommand("INSERT INTO test_data (date,time,user_id,email,data)values @bulk;");
cmd.Parameters.Add(new ClickHouseParameter
{
DbType = DbType.Object,
ParameterName = "bulk",
Value = new[]
{
new object[] { DateTime.Now, DateTime.Now, 1ul, "*****@*****.**", "dsdsds" },
new object[] { DateTime.Now.AddHours(-1), DateTime.Now.AddHours(-1), 2ul, "*****@*****.**", "qwqwqwq" },
}
});
cmd.ExecuteNonQuery();
}
public static void DropDatabaseIfExist(string connectionSettings)
{
var connectionParams = GetConnectionParams(connectionSettings);
var databaseParam = connectionParams.FirstOrDefault(e => e.Key.ToUpper() == "DATABASE");
string databaseName = databaseParam.Value;
if (databaseName != null)
{
string connectionStringDefault = connectionSettings.Replace(
$"{databaseParam.Key}={databaseParam.Value}",
$"Database=default"
);
using (var defaultConnection = new ClickHouseConnection(connectionStringDefault))
{
defaultConnection.Open();
var cmdDefault = defaultConnection.CreateCommand();
cmdDefault.CommandText = $"DROP DATABASE IF EXISTS {databaseName}";
cmdDefault.ExecuteNonQuery();
}
}
}
public static IEnumerable <string> GetDatabaseList(this ClickHouseConnectionSettings settings)
{
string commandText = "SELECT name FROM system.databases";
ProxyLog.Info(commandText);
using (ClickHouseConnection conn = new ClickHouseConnection(settings))
{
conn.Open();
using (ClickHouseCommand cmd = new ClickHouseCommand(conn, commandText))
{
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
yield return(reader.GetString(0));
}
ProxyLog.Info($"{reader.RecordsAffected} row(s)");
}
}
}
}
public static void CHConnection(Action myMethodName)
{
try
{
con = new ClickHouseConnection(str);
con.Open();
myMethodName();
}
catch (ClickHouseException err)
{
Console.WriteLine(err);
}
finally
{
if (con != null)
{
con.Close();
}
}
}
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();
}
}
}
private bool TryApplyMigrations(ClickHouseNode node, Assembly assembly)
{
try
{
using (var connection = new ClickHouseConnection(node.ConnectionSettings))
{
_logger.LogInformation($"Applying migrations for the node {node.Name}");
connection.Open();
ClickHouseDbProvider.CreateMigrationTableIfNotExists(connection,
_migrationSettings.MigrationsTableName);
var migrations = _locator.Locate(assembly);
var appliedMigrations =
ClickHouseDbProvider.GetAppliedMigrations(connection, _migrationSettings.MigrationsTableName);
var notAppliedMigrations = GetNotAppliedMigrations(migrations.ToList(), appliedMigrations).ToList();
_logger.LogInformation(
$"Not applied migrations:{Environment.NewLine}{string.Join(Environment.NewLine, notAppliedMigrations.Select(m => m.ToString()))}");
var success = TryApplyMigrations(notAppliedMigrations, node, connection);
if (success)
{
_logger.LogInformation($"All migrations for the node {node.Name} successfully applied");
}
else
{
_logger.LogError($"Migrations for {node.Name} were not applied");
}
return(success);
}
}
catch (Exception e)
{
_logger.LogError(e, "Migration applying error.");
return(false);
}
}