ELK环境搭建及es使用sql to dsl 插件

77 阅读 0 评论 51 点赞

我是靠谱客的博主开朗季节，这篇文章主要介绍ELK环境搭建及es使用sql to dsl 插件，现在分享给大家，希望可以做个参考。

依赖

关于java JDK 依赖最好使用1.8JDK
jar :mysql-connector-java-5.1.43-bin.jar

配置文件 xxxx.conf

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
input {
  jdbc {
    jdbc_driver_library => "D:/wll-soft/ELK/logstash-5.5.2/mysql-connector-java-5.1.43-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://xxxxxx:3306/xxxx"
    jdbc_validate_connection => true
    jdbc_paging_enabled => true
    jdbc_page_size => 500
    jdbc_user => "root"
    jdbc_password => "root"
    statement => "select * from table  WHERE id > :sql_last_value"
  }
}

output {
    elasticsearch {
        hosts => ["127.0.0.1:9200"]
        index => "indexName"
    }
    stdout { codec => rubydebug }
}

启动报错(win)

Cannot locate Java installation, specified by JAVA_HOME:

复制代码

1
 set JAVA_HOME=/XXX/XXX

Your application used more memory than the safety cap of 1G.
手动修改配置文件logstash/bin/setup.*

复制代码

1
  set LS_HEAP_SIZE=3g

sql To DSL

github
导入数据

复制代码

1
    logstash.bat -f xxxxx.conf

使用插件

复制代码

1
elasticsearch-plugin.bat install es-sql-5.5.0.zip

PHP demo
下载curl扩展

复制代码

1
2
3
composer require culr/curl

composer require elasticsearch/elasticsearch

test.php

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
require_once 'vendor/autoload.php';
require_once 'Elasticsearch.php';

$es = new Elasticsearch();

$curl = $es->getCURL();

$baseUri = $es->getURI();

$baseSQL = 'select * from news_info_shadow limit 1';

$sql = isset($argv[1]) ? $argv[1] : $baseSQL;

if (is_string($sql) && '' != $sql) {
    $result = $curl->get($baseUri . urlencode($sql));
    print_r(json_decode($result->response, 1)['hits']['hits']);
}

Elasticsearch.php

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<?php

/**
 * Created by PhpStorm.
 * User: wang.liangliang
 * Date: 2017/9/1
 * Time: 15:58
 */
class Elasticsearch
{
    private $curl;

    private $uri;

    private $version;

    private $sql;


    public function __construct()
    {
        $this->uri = 'http://localhost:9200/_sql?sql=';
        $this->curl = new CurlCurl();
        $this->version = '1.0';
    }

    public function getCURL()
    {
        return $this->curl;
    }

    public function getURI()
    {
        return $this->uri;
    }

    public function setSQL($sql)
    {
        $this->sql = $sql;
    }
}