• 2017.4.28 支持 SSL

create table my_index.my_table (

     id keyword,

     name text,

     age long,

     birthday date

);



select * from my_index.my_type;



select count(*) from my_index.my_table group by age;

#Create table



字段参数,ES中分词规则、索引类型、字段格式等高级参数的支持



create table my_table (

	name text (analyzer = ik_max_word),

	dd text (index=no),

	age long (include_in_all=false)

);





对象、嵌套字段支持 as



create table my_index (

	id long,

	name text,

     obj object as (

         first_name text,

         second_name text (analyzer=pinyin)

     )

);





create table my_index (

	id long,

	name text,

   obj nested as (

         first_name text,

         second_name text (analyzer=pinyin)

   )

);





ES索引高级参数支持 with option



create table my_index (

	id long,

	name text

) with option (

	index.number_of_shards=10,

   index.number_of_replicas = 1

);

#Insert/Bulk



单条数据插入

insert into my_index.index (name,age) values ('zhangsan',24);



多条插入

bulk into my_index.index (name,age) values ('zhangsan',24),('lisi',24);





对象数据插入,list,{}Map



insert into my_index.index (ds) values (['zhejiang','hangzhou']);

		

insert into my_index.index (dd) values ({address='zhejiang',postCode='330010'});

#select/Aggregations



select * from my_table.my_index where name like 'john *' and age between 20 and 30 and (hotel = 'hanting' or flight = 'MH4510');



地理位置中心点查询

select * from hz_point where geo_distance({distance='1km',location='30.306378,120.247427'});



地理坐标区域查询

select * from hz_point where geo_bounding_box({location={top_left='31.306378,119.247427',bottom_right='29.285797,122.172329'}});



pipeline统计 move_avg

select count(*) as total, moving_avg({buckets_path=total}) from my_index group by date_histogram({field=timestamp,interval='1h'});

Getting Started



环境要求python >= 2.7



export PYTHONHOME=(%python_path)

export PATH=$PYTHONHOME/bin:$PATH





安装第三方依赖包

pip install -r esql5.egg-info/requires.txt

或python setup.py install



运行esql5服务 

(standalone):

cd esql5

python -m App.app



(with uwsgi)

cd esql5

uwsgi --ini conf/uwsgi.ini





shell终端:

python -m elsh.Command

output{

elasticsearch{

action => "index"

hosts => ["xxxxxx:9200"]

index => "xxxxx"

document_type => "haha"

}

}

output{

elasticsearch{

action => "index"

hosts => ["xxx"]

index => "logstashlog"

template => "xx/http-logstash.json"

template_name => "http-log-logstash"

template_overwrite => true

}

stdout{

codec => rubydebug

}

elasticsearch{

action => "index"

hosts => ["xxx"]

index => "http-log-logstash"

document_type => "haha"

template => "xxx/http-logstash.json"

template_name => "http-log-logstash"

template_overwrite => true

}

stdout{

codec => rubydebug

}

{ 

    "template" : "qmpsearchlog", 

    "order":1,

    "settings" : { "index.refresh_interval" : "60s" }, 

    "mappings" : { 

        "_default_" : { 

            "_all" : { "enabled" : false }, 

            "dynamic_templates" : [{ 

              "message_field" : { 

                "match" : "message", 

                "match_mapping_type" : "string", 

                "mapping" : { "type" : "string", "index" : "not_analyzed" } 

              } 

            }, { 

              "string_fields" : { 

                "match" : "*", 

                "match_mapping_type" : "string", 

                "mapping" : { "type" : "string", "index" : "not_analyzed" } 

              } 

            }], 

            "properties" : { 

                "@timestamp" : { "type" : "date"}, 

                "@version" : { "type" : "integer", "index" : "not_analyzed" }, 

                "path" : { "type" : "string", "index" : "not_analyzed" }, 

				"host" : { "type" : "string", "index" : "not_analyzed" },

                "record_time":{"type":"date","format": "yyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"}, 

                "method":{"type":"string","index" : "not_analyzed"},

                "unionid":{"type":"string","index" : "not_analyzed"},

                "user_name":{"type":"string","index" : "not_analyzed"},

                "query":{"type":"string","index" : "not_analyzed"},

                "ip":{ "type" : "ip"}, 

                "webbrower":{"type":"string","index" : "not_analyzed"},

                "os":{"type":"string","index" : "not_analyzed"},

                "device":{"type":"string","index" : "not_analyzed"},

                "ptype":{"type":"string","index" : "not_analyzed"},

                "serarch_time":{"type":"date","format": "yyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"},

                "have_ok":{"type":"string","index" : "not_analyzed"},

                "legal":{"type":"string","index" : "not_analyzed"}

            } 

        } 

    } 

}

• ElasticSearch参考手册，学习 DSL查询语法，包括查找（query）、过滤（filter）和聚合（aggs）等。
• Logstash参考手册，学习数据导入，包括输入（input）、过滤（filter）和输出（ output）等，主要是filter中如何对复杂文本 进行拆分和类型 转化。
• Kibana参考手册，使用Kibana提供的前端界面对数据进行快速展示，主要是对Visulize 模块的使

• ElasticSearch
• Logstash：Logstash 最佳实践，ELKStack中文指南
• Kibana：ELKStack中文指南

date {

        match => [ "submission_time", "yyyyMMdd" ]

    }

date {

        match => [ "submission_time", "UNIX_MS" ]

        target => "@timestamp"

        }

date {

        match => [ "submission_time", "UNIX_MS" ]

        target => "submission_time"

        }

date {

        match => [ "start_time", "UNIX_MS" ]

        target => "start_time"

        }

date {

        match => [ "end_time", "UNIX_MS" ]

        target => "end_time"

        }

意思是说发生系统swapping的时候ES节点的性能会非常差，也会影响节点的稳定性。所以要不惜一切代价来避免swapping。swapping会导致Java GC的周期延迟从毫秒级恶化到分钟，更严重的是会引起节点响应延迟甚至脱离集群。                                                                      ----如果不了解到底什么是swapping的，可以找点Linux IO章节文章看看

GET 请求 /_nodes?filter_path=**.mlockall

{
  "nodes": {
    "dCH5FCpATRO7D1azyPhsRQ": {
      "process": {
        "mlockall": false
      }
    },
    "GoNfwnNzSwmJy3y1QdfluA": {
      "process": {
        "mlockall": false
      }
    },
    "ijW61kA-SAqnnVHjpTSw2w": {
      "process": {
        "mlockall": false
      }
    },
    "yHl9GUGbS46o4hwKvHpwnQ": {
      "process": {
        "mlockall": false
      }
    }
  }
}

告诉操作系统可以无限制分配内存给一个进程

[2017-04-06T11:51:14,840][INFO ][o.e.b.BootstrapCheck     ] [Portal_ES_Node10_0_36_49] bound or publishing to a non-loopback or non-link-local address, enforcing bootstrap checks
ERROR: bootstrap checks failed
memory locking requested for elasticsearch process but memory is not locked

# allow user 'XXX' mlockall
XXX soft memlock unlimited
XXX hard memlock unlimited

String sql = "select * from index.order where status='SUCCESS' and price > 100 order by nvl(pride, 0) asc routing by 'JD' limit 0, 20";



ElasticSql2DslParser sql2DslParser = new ElasticSql2DslParser();

//解析SQL

ElasticSqlParseResult parseResult = sql2DslParser.parse(sql);

//生成DSL(可用于rest api调用)

String dsl = parseResult.toDsl();



//toRequest方法接收一个clinet对象参数

SearchRequestBuilder searchReq = parseResult.toRequest(esClient);

//执行查询

SearchResponse response = searchReq.execute().actionGet();

{

  "from" : 0,

  "size" : 20,

  "query" : {

    "bool" : {

      "filter" : {

        "bool" : {

          "must" : [ {

            "term" : {

              "status" : "SUCCESS"

            }

          }, {

            "range" : {

              "price" : {

                "from" : 100,

                "to" : null,

                "include_lower" : false,

                "include_upper" : true

              }

            }

          } ]

        }

      }

    }

  },

  "sort" : [ {

    "pride" : {

      "order" : "asc",

      "missing" : 0

    }

  } ]

}

<bean id="elasticDataSource" class="org.elasticsearch.jdbc.api.ElasticSingleConnectionDataSource" destroy-method="destroy">

    <property name="driverClassName" value="org.elasticsearch.jdbc.api.ElasticDriver" />

    <property name="url" value="jdbc:elastic:192.168.0.109:9300/product_cluster" />

</bean>



<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">

    <property name="dataSource" ref="elasticDataSource" />

    <property name="configLocation" value="classpath:sqlMapConfig.xml"/>

</bean>

<sqlMapConfig>

    <settings

            cacheModelsEnabled="true"

            lazyLoadingEnabled="true"

            enhancementEnabled="true"

            maxSessions="64"

            maxTransactions="20"

            maxRequests="128"

            useStatementNamespaces="true"/>



    <sqlMap resource="sqlmap/PRODUCT.xml"/>



</sqlMapConfig>

<sqlMap namespace="PRODUCT">

    <select id="getProductByCodeAndMatchWord" parameterClass="java.util.Map" resultClass="java.lang.String">

        SELECT *

        FROM index.product

        QUERY match(productName, #matchWord#) or prefix(productName, #prefixWord#, 'boost:2.0f')

        WHERE productCode = #productCode#

        AND advicePrice > #advicePrice#

        AND $$buyers.buyerName IN ('china', 'usa')

        ROUTING BY #routingVal#

    </select>

</sqlMap>

@Repository

public class ProductDao {

    @Autowired

    @Qualifier("sqlMapClient")

    private SqlMapClient sqlMapClient;





    public List<Product> getProductByCodeAndMatchWord(String matchWord, String productCode) throws SQLException {

        Map<String, Object> paramMap = Maps.newHashMap();

        paramMap.put("productCode", productCode);

        paramMap.put("advicePrice", 1000);

        paramMap.put("routingVal", "A");

        paramMap.put("matchWord", matchWord);

        paramMap.put("prefixWord", matchWord);

        String responseGson = (String) sqlMapClient.queryForObject("PRODUCT.getProductByCodeAndMatchWord", paramMap);

        

        //反序列化查询结果

        JdbcSearchResponseResolver responseResolver = new JdbcSearchResponseResolver(responseGson);

        JdbcSearchResponse<Product> searchResponse = responseResolver.resolveSearchResponse(Product.class);



        return searchResponse.getDocList();



    }

}

@Test

public void testProductQuery() throws Exception {

    BeanFactory factory = new ClassPathXmlApplicationContext("application-context.xml");

    ProductDao productDao = factory.getBean(ProductDao.class);

    

    List<Product> productList = productDao.getProductByCodeAndMatchWord("iphone 6s", "IP_6S");

    for (Product product : productList) {

        System.out.println(product.getProductName());

    }

}

• Term Suggester
• Phrase Suggester
• Completion Suggester
• Context Suggester

PUT /blogs/
{
  "mappings": {
    "tech": {
      "properties": {
        "body": {
          "type": "text"
        }
      }
    }
  }
}

POST _bulk/?refresh=true
{ "index" : { "_index" : "blogs", "_type" : "tech" } }
{ "body": "Lucene is cool"}
{ "index" : { "_index" : "blogs", "_type" : "tech" } }
{ "body": "Elasticsearch builds on top of lucene"}
{ "index" : { "_index" : "blogs", "_type" : "tech" } }
{ "body": "Elasticsearch rocks"}
{ "index" : { "_index" : "blogs", "_type" : "tech" } }
{ "body": "Elastic is the company behind ELK stack"}
{ "index" : { "_index" : "blogs", "_type" : "tech" } }
{ "body": "elk rocks"}
{ "index" : { "_index" : "blogs", "_type" : "tech" } }
{  "body": "elasticsearch is rock solid"}

POST _analyze
{
  "text": [
    "Lucene is cool",
    "Elasticsearch builds on top of lucene",
    "Elasticsearch rocks",
    "Elastic is the company behind ELK stack",
    "elk rocks",
    "elasticsearch is rock solid"
  ]
}

POST /blogs/_search
{ 
  "suggest": {
    "my-suggestion": {
      "text": "lucne rock",
      "term": {
        "suggest_mode": "missing",
        "field": "body"
      }
    }
  }
}

{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "failed": 0
  },
  "hits": {
    "total": 0,
    "max_score": 0,
    "hits":
  },
  "suggest": {
    "my-suggestion": [
      {
        "text": "lucne",
        "offset": 0,
        "length": 5,
        "options": [
          {
            "text": "lucene",
            "score": 0.8,
            "freq": 2
          }
        ]
      },
      {
        "text": "rock",
        "offset": 6,
        "length": 4,
        "options":
      }
    ]
  }
}

 "suggest": {
    "my-suggestion": [
      {
        "text": "lucne",
        "offset": 0,
        "length": 5,
        "options": [
          {
            "text": "lucene",
            "score": 0.8,
            "freq": 2
          }
        ]
      },
      {
        "text": "rock",
        "offset": 6,
        "length": 4,
        "options": [
          {
            "text": "rocks",
            "score": 0.75,
            "freq": 2
          }
        ]
      }
    ]
  }

POST /blogs/_search
{
  "suggest": {
    "my-suggestion": {
      "text": "lucne and elasticsear rock",
      "phrase": {
        "field": "body",
        "highlight": {
          "pre_tag": "<em>",
          "post_tag": "</em>"
        }
      }
    }
  }
}

"suggest": {
    "my-suggestion": [
      {
        "text": "lucne and elasticsear rock",
        "offset": 0,
        "length": 26,
        "options": [
          {
            "text": "lucene and elasticsearch rock",
            "highlighted": "<em>lucene</em> and <em>elasticsearch</em> rock",
            "score": 0.004993905
          },
          {
            "text": "lucne and elasticsearch rock",
            "highlighted": "lucne and <em>elasticsearch</em> rock",
            "score": 0.0033391973
          },
          {
            "text": "lucene and elasticsear rock",
            "highlighted": "<em>lucene</em> and elasticsear rock",
            "score": 0.0029183894
          }
        ]
      }
    ]
  }

PUT /blogs_completion/
{
  "mappings": {
    "tech": {
      "properties": {
        "body": {
          "type": "completion"
        }
      }
    }
  }
}

POST _bulk/?refresh=true
{ "index" : { "_index" : "blogs_completion", "_type" : "tech" } }
{ "body": "Lucene is cool"}
{ "index" : { "_index" : "blogs_completion", "_type" : "tech" } }
{ "body": "Elasticsearch builds on top of lucene"}
{ "index" : { "_index" : "blogs_completion", "_type" : "tech" } }
{ "body": "Elasticsearch rocks"}
{ "index" : { "_index" : "blogs_completion", "_type" : "tech" } }
{ "body": "Elastic is the company behind ELK stack"}
{ "index" : { "_index" : "blogs_completion", "_type" : "tech" } }
{ "body": "the elk stack rocks"}
{ "index" : { "_index" : "blogs_completion", "_type" : "tech" } }
{ "body": "elasticsearch is rock solid"}

POST blogs_completion/_search?pretty
{ "size": 0,
  "suggest": {
    "blog-suggest": {
      "prefix": "elastic i",
      "completion": {
        "field": "body"
      }
    }
  }
}

"suggest": {
    "blog-suggest": [
      {
        "text": "elastic i",
        "offset": 0,
        "length": 9,
        "options": [
          {
            "text": "Elastic is the company behind ELK stack",
            "_index": "blogs_completion",
            "_type": "tech",
            "_id": "AVrXFyn-cpYmMpGqDdcd",
            "_score": 1,
            "_source": {
              "body": "Elastic is the company behind ELK stack"
            }
          }
        ]
      }
    ]
  }

PUT /blogs_completion/
{
  "mappings": {
    "tech": {
      "properties": {
        "body": {
          "type": "completion",
          "analyzer": "english"
        }
      }
    }
  }
}

POST blogs_completion/_search?pretty
{ "size": 0,
  "suggest": {
    "blog-suggest": {
      "prefix": "elastic i",
      "completion": {
        "field": "body"
      }
    }
  }
}

POST _analyze?analyzer=english
{
  "text": "elasticsearch is rock solid"
}

会发现只有3个token:
{
  "tokens": [
    {
      "token": "elasticsearch",
      "start_offset": 0,
      "end_offset": 13,
      "type": "<ALPHANUM>",
      "position": 0
    },
    {
      "token": "rock",
      "start_offset": 17,
      "end_offset": 21,
      "type": "<ALPHANUM>",
      "position": 2
    },
    {
      "token": "solid",
      "start_offset": 22,
      "end_offset": 27,
      "type": "<ALPHANUM>",
      "position": 3
    }
  ]
}

1. 在用户刚开始输入的过程中，使用Completion Suggester进行关键词前缀匹配，刚开始匹配项会比较多，随着用户输入字符增多，匹配项越来越少。如果用户输入比较精准，可能Completion Suggester的结果已经够好，用户已经可以看到理想的备选项了。 
2. 如果Completion Suggester已经到了零匹配，那么可以猜测是否用户有输入错误，这时候可以尝试一下Phrase Suggester。
3. 如果Phrase Suggester没有找到任何option，开始尝试term Suggester。

• 《Elastic{ON}17 Keynote 回顾》，QQ 群(190605846)，2017-3-17 21:00 PM，回放
• 《What's new in Elasticsearch 5》，QQ 群(190605846)，2017-3-20 21:00 PM，回放
• 《What's new in Logstash 5》，QQ 群(190605846)，2017-3-21 21:00 PM，回放 

• Elastic Workshop，北京，2017-04-10【报名结束】
• Elastic Workshop，上海，2017-04-17【报名结束】
• Elastic Workshop，深圳，2017-04-20【报名结束】
• Elastic Workshop，上海，2017-06-29【报名结束】
• Elastic Workshop，广州，2017-07-04【报名结束】
• Elastic Workshop，深圳，2017-07-06【报名结束】

• Elastic Meetup Shanghai ，上海，2017.5.14， 【报名结束】【日程】
• Elastic Meetup Beijing ，北京，2017.5.21  【报名结束】【日程】【直播回放】
• Elastic Meetup Nanjing，南京，2017.6.10 【报名结束】【日程】【直播回放】
• Elastic Meetup Hangzhou，杭州，2017.6.25 【报名结束​】【日程】【直播回放】
• Elastic Meetup Changsha，长沙，2017.10.28 【报名结束​】【日程】
• Elastic Meetup Wuhan，武汉，2017.11.4 【报名结束​】【日程】
• Elastic Meetup Guangzhou，广州，2017.11.25 【报名结束​】【日程】
• Elastic Meetup Shenzhen，深圳，2017.12.16 【报名结束​】【日程】

• Gopher China，上海，2017.04.15-2017.04.16
• OSC Shanghai ，上海，2017.5.13
• The China-R Conf，北京，2017.5.19-2017.5.21
• OSC Hangzhou，杭州，2017.6.24
• ArchSummit Shenzhen，深圳，2017.7.7-2017.7.8
• OSC Jinan，济南，2017.7.22
• OSC Zhuhai，珠海，2017.8.27
• RubyConf China，杭州，2017.9.16-17
• OSC Chengdu， 成都，2017.9.23
• OSC Chongqing，重庆，2017.9.24
• ArchSummit Beijing，2017.12.8-2017.12.9