clickhouse数据库，http请求访问，支持参数化
public class ClickHouseClient
{
private static readonly HttpClient _httpClient = new HttpClient();
private readonly string _clickHouseUrl;
private readonly string _username;
private readonly string _password;
private string _database;
private static readonly string _searchFormat = " FORMAT JSON";
public ClickHouseClient()
{
_clickHouseUrl = "url";
_username = "user_name";
_password = "password";
if (string.IsNullOrWhiteSpace(_clickHouseUrl) || string.IsNullOrWhiteSpace(_username) || string.IsNullOrWhiteSpace(_password))
{
System.Text.StringBuilder str = new System.Text.StringBuilder();
str.Append("ClickHouse配置缺失，CloudStorage:ClickHouse");
if (string.IsNullOrWhiteSpace(_clickHouseUrl))
{
str.Append("节点EndPoint，");
}
if (string.IsNullOrWhiteSpace(_username))
{
str.Append("节点UId，");
}
if (string.IsNullOrWhiteSpace(_password))
{
str.Append("节点Password，");
}
throw new Exception($"{str.ToString().TrimEnd('，')}未配置");
}
_database = "default";
SetAuthorizationHeader();
}
/// <summary>
/// 设置查询数据库
/// </summary>
/// <param name="database"></param>
public void SetDatabase(string database)
{
_database = database;
SetAuthorizationHeader();
}
/// <summary>
/// 单个查询，参数化格式：sql语句中：{id:String}，赋值：{"id","666"}
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public List<T> ExecuteQuery<T>(string query, Dictionary<string, string> parameters = null) where T : class
{
try
{
var response = SendRequest($"{query} {_searchFormat}", parameters);
var responseString = response.Content.ReadAsStringAsync().Result;
var data = System.Text.Json.JsonSerializer.Deserialize<ClickHouseBaseModel<T>>(responseString);
return data?.data;
}
catch (HttpRequestException)
{
throw;
}
}
/// <summary>
/// 单条插入，参数化格式：sql语句中：{id:String}，赋值：{"id","666"}
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
public void ExecuteNonQuery(string query, Dictionary<string, string> parameters = null)
{
try
{
SendRequest(query, parameters);
}
catch (HttpRequestException)
{
throw;
}
}
/// <summary>
/// 设置访问信息
/// </summary>
private void SetAuthorizationHeader()
{
_httpClient.DefaultRequestHeaders.Clear();
_httpClient.DefaultRequestHeaders.Add("X-ClickHouse-User", _username);
_httpClient.DefaultRequestHeaders.Add("X-ClickHouse-Key", _password);
_httpClient.DefaultRequestHeaders.Add("X-ClickHouse-Database", _database);
}
/// <summary>
/// 进行访问
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private HttpResponseMessage SendRequest(string query, Dictionary<string, string> parameters = null)
{
var formData = new MultipartFormDataContent
{
{ new StringContent(query), "query" }
};
if (parameters != null)
{
foreach (var param in parameters)
{
string paramKey = param.Key;
if (!param.Key.StartsWith("param_"))
{
paramKey = $"param_{paramKey}";
}
formData.Add(new StringContent(param.Value), paramKey);
}
}
var response = _httpClient.PostAsync(_clickHouseUrl, formData).Result;
response.EnsureSuccessStatusCode();
return response;
}
}