官方提供的是java sdk，并支持jdbc方式的查询结果输出;但是却没有.net sdk的支持。
开发 ElasticSearch-Sql 第三方开源项目的.net sdk,未来集成入bsf框架。（已使用，但未进行大范围使用测试，欢迎交流并抛砖引玉）
参考ElasticSearch-Sql 开源地址:https://github.com/NLPchina/elasticsearch-sql
1）支持将查询结果转换成datatable形式，便于界面绑定和数据导出等。
2）代码简单易懂，便于改进并提高稳定性和性能。（拷贝立即使用）
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
/*by 车江毅 欢迎交流，一起完善之。.net 开源，因你更美好*/
namespace BSF.ElasticSearch
{
public class EsSqlProvider
{
private string url;
private HttpClient client = new HttpClient();
public EsSqlProvider(string ip,int port)
{
this.url = string.Format("http://{0}:{1}/_sql",ip,port);
} public EsSqlProvider(string url)
{
this.url = url.TrimEnd('/')+"/_sql";
} public Result Post(string sql)
{
//var http = new BSF.Api.HttpProvider();
//var dic = new Dictionary<string, string>();
//dic.Add("",sql); var content = new StringContent(sql);
content.Headers.ContentType = new MediaTypeHeaderValue("application/json");
var json = PostBase(content); return new BSF.Serialization.JsonProvider().Deserialize<Result>(json); } public DataTable Query(string sql)
{
var result = Post(sql);
if (result.aggregations != null && result.aggregations.Count > )
{
var first = result.aggregations.First();
//groupby 统计方式
if (first.Value != null && first.Value.buckets != null && first.Value.buckets.Count > )
{
DataTable dataTable = new DataTable("es-groupby");
//以第一列为准,填充列
{
var t = first.Value.buckets[];
dataTable.Columns.Add(new DataColumn(first.Key, ColumnType((GetValue((object)t["key"])).GetType())));
foreach (var c in t)
{
if (c.Key != "doc_count"&&c.Key!= "key")
{ dataTable.Columns.Add(new DataColumn(c.Key, (GetValue(c.Value)).GetType())); }
}
}
//填充值
foreach (var b in first.Value.buckets)
{
DataRow dataRow = dataTable.NewRow();
dataRow[first.Key] = b["key"];
foreach (var c in b)
{
if(dataTable.Columns.Contains(c.Key))
dataRow[c.Key] = GetValue(c.Value);
}
dataTable.Rows.Add(dataRow);
}
return dataTable; }
else if (first.Value != null&& first.Value.value!=null)
{
DataTable dataTable = new DataTable("es-aggregations");
//常规统计方式
foreach (var o in result.aggregations)
{
dataTable.Columns.Add(new DataColumn(o.Key, ColumnType((GetValue((object)o.Value)).GetType())));
}
DataRow dataRow = dataTable.NewRow();
foreach (var o in result.aggregations)
{
if (dataTable.Columns.Contains(o.Key))
dataRow[o.Key] = GetValue(o.Value);
}
dataTable.Rows.Add(dataRow);
return dataTable;
}
}
else if (result.sources != null && result.sources.Count > )
{
DataTable dataTable = new DataTable("es-sources");
var first = result.sources.First();
foreach (var item in ((Dictionary<string,dynamic>)first))
{
dataTable.Columns.Add(new DataColumn(item.Key, ColumnType((GetValue((Object)item.Value)).GetType())));
}
foreach (dynamic m in result.sources)
{
DataRow dataRow = dataTable.NewRow();
foreach (var item in ((Dictionary<string, dynamic>)m))
{
if (dataTable.Columns.Contains(item.Key))
dataRow[item.Key] = GetValue(item.Value);
}
dataTable.Rows.Add(dataRow);
}
return dataTable;
}
return null;
} private Type ColumnType(Type type)
{
if (type.IsArray)
{
return typeof(string);
}
else
return type;
} private Object GetValue(Object value)
{
if (value != null && value is Array)
return new BSF.Serialization.JsonProvider().Serializer(value);
if (value != null && (value.GetType().IsValueType || value is String))
return value;
if (value != null && value is Dictionary<string, dynamic>)
{
var valuet = (Dictionary<string, dynamic>)value;
if( valuet.ContainsKey("value_as_string"))
return GetValue(valuet["value_as_string"]);
if (valuet.ContainsKey("value"))
return GetValue(valuet["value"]);
}
if (value != null && value.GetType().GetProperty("value")!=null)
{
var p= value.GetType().GetProperty("value_as_string");
if (p!=null&&p.GetValue(value)!=null)
return p.GetValue(value);
p = value.GetType().GetProperty("value");
if (p != null && p.GetValue(value) != null)
return p.GetValue(value);
}
return new BSF.Serialization.JsonProvider().Serializer(value);
} private Object GetPropertyValue(Object obj, string property)
{
var p = obj.GetType().GetProperty(property);
if (p != null)
return p.GetValue(obj);
return null;
} private string PostBase(HttpContent content)
{
//此处未来需要添加HttpClient连接池,复用连接
//using (var client = new HttpClient())
//{
var result = client.PostAsync(url, content).Result;
string resultContent = result.Content.ReadAsStringAsync().Result;
return resultContent;
//}
} public class Result{ public Hits hits { get; set; }
public Dictionary<string, aggregation> aggregations {get;set;} public List<dynamic> sources { get {
List<dynamic> rs = new List<dynamic>();
foreach (var h in hits.hits)
{
rs.Add(h._source);
}
return rs;
} } public class aggvalue
{
public dynamic value { get; set; }
public string value_as_string { get; set; }
}
public class aggregation: aggvalue
{
public List<Dictionary<string, dynamic>> buckets { get; set; }
}
public class Hits {
public int total { get; set; }
public List<hit> hits { get; set; }
}
public class hit {
public string _index { get; set; }
public string _type { get; set; }
public string _id { get; set; } public string _score { get; set; } public dynamic _source { get; set; }
} }
}
}
欢迎交流，为了更好的完善之，开源QQ群: .net 开源基础服务 238543768
by 车江毅