.net ElasticSearch-Sql 扩展类【原创】

官方提供的是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 > 0)
            {
                var first = result.aggregations.First();
                //groupby 统计方式
                if (first.Value != null && first.Value.buckets != null && first.Value.buckets.Count > 0)
                {
                    DataTable dataTable = new DataTable("es-groupby");
                    //以第一列为准,填充列
                    {
                        var t = first.Value.buckets[0];
                        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 > 0)
            {
                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 车江毅