ElasticSearch學習總結4（sql操作ES）

可以直接使用sql語句操作es資料庫，比如查詢people索引庫

也可以輸出json格式

也可以輸出csv格式

使用接口調用工具(ApiPost)，也可以請求到結果：

在.NetCore程式中使用sql查詢資料

using Newtonsoft.Json;
using System;
using System.Data;
using System.IO;
using System.Net;
using System.Text;

namespace ElasticSearch
{
    public class SendHttp
    {
        public static string url = "http://localhost:9200/_xpack/sql?format=csv";

        public static DataTable GetDataBySql(string sql)
        {
            DataTable dataTable = new DataTable();
            try
            {
                //擷取資料
                string jsonstr = Post(url, new QueryParam() { query = sql });

                //按行拆分
                var lines = jsonstr.Split("\r\n");

                //擷取表頭字段
                foreach (string item in lines[0].Split(","))
                {
                    //可以做類型轉換
                    dataTable.Columns.Add(item, typeof(System.String));
                }

                //表資料
                for (int i = 1; i < lines.Length - 1; i++)
                {
                    var filedvalue = lines[i].Split(",");
                    var row = dataTable.NewRow();
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        row[j] = filedvalue[j];
                    }
                    dataTable.Rows.Add(row);
                }
            }
            catch (Exception)
            {

                throw;
            }
            return dataTable;
        }


        public static string Post(string url, QueryParam queryParam)
        {
            HttpWebRequest request = null;
            try
            {
                request = (HttpWebRequest)WebRequest.Create(url);
                var data = Encoding.UTF8.GetBytes(JsonConvert.SerializeObject(queryParam));
                request.Method = "POST";
                request.ContentType = "application/json;charset=UTF-8";
                request.Timeout = 90000;

                //設定入參
                using (var stream = request.GetRequestStream())
                {
                    stream.Write(data, 0, data.Length);
                }

                //發送請求
                var response = (HttpWebResponse)request.GetResponse();

                //讀取出參
                using (var resStream = response.GetResponseStream())
                {
                    using (var reader = new StreamReader(resStream, Encoding.UTF8))
                    {
                        return reader.ReadToEnd();
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                //釋放連結
                if (request != null)
                {
                    request.Abort();
                }
            }
        }

    }


    public class QueryParam
    {
        public string query { get; set; }
    }
}
           

main方法：

class Program
    {
        static void Main(string[] args)
        {
            //使用sql查詢資料
            var data = SendHttp.GetDataBySql("select * from people");

            Console.ReadLine();
        }
    }
           

執行結果：

這樣就把資料放到一個DataTable容器裡面了。