Elasticsearch

1在Docker中启动 Elastic-SQL 命令行
2    /usr/share/elasticsearch/jdk/bin/java -cp elasticsearch-sql-cli-7.9.0.jar org.elasticsearch.xpack.sql.cli.Cli
3    # 发现乱码，加上参数，解决乱码问题
4    /usr/share/elasticsearch/jdk/bin/java -Dfile.encoding=UTF-8 -cp elasticsearch-sql-cli-7.9.0.jar org.elasticsearch.xpack.sql.cli.Cli
5   
6
7kibana中使用 Elastic-SQL
8    # 基本查询
9    POST _sql?format=json
10    {
11      "query":"select * from es_biz_trade_product"
12    }
13    
14    # format 格式化输出
15    ?format=
16    参数有 csv json tsv txt yaml cbor smile 这几种
17    
18    # 转换
19    POST _sql/translate
20    {
21      "query": "SELECT * FROM es_biz_trade_product",
22      "fetch_size": 10
23    }
24    
25    # DSL 过滤
26    POST _sql?format=json
27    {
28      "query": """
29      SELECT * FROM "es_biz_trade_product"
30      """,
31      "filter": {
32        "range": {
33          "original_price": {
34            "gte": 10,
35            "lte": 80
36          }
37        }
38      },
39      "fetch_size": 2
40    }
41    
42    # columnar 
43    POST _sql?format=json
44    {
45      "query": "SELECT * FROM es_biz_trade_product",
46      "fetch_size": 10,
47      "columnar": true
48    }
49    
50    # scroll 分页，带上 scroll=1m 
51    # 第一次查询
52    POST es_biz_trade_product/_search?scroll=1m
53    {
54      "size": 2
55    }
56    # 获取 _scroll_id 中的值，做为 POST 请求参数
57    POST _search/scroll?scroll=1m
58    {
59      "scroll_id" : "FGluY2x1ZGVfY29udGV4dF91dWlkDXF1ZXJ5QW5kRmV0Y2gBFGdWYklNM1FCZzVNR1ZIUlRzTTFEAAAAAAAAjWAWWXFVTEJFUjVUZlM0NHpEaVZHSTZYdw=="
60    }
61    # 用 _scroll_id 发送请求，直到 hits 为空为止
62    
63    # scroll 性能问题
64    1. 默认scroll有排序，可以用 "sort": [ "_doc" ] 
65    POST es_biz_trade_product/_search?scroll=1m
66    {
67      "size": 2,
68      "sort": [ "_doc" ]
69    }
70    2. scroll 占用系统资源，务必用完就关闭。
71    DELETE _search/scroll
72    {
73        "scroll_id": "FGluY2x1ZGVfY29udGV4dF91dWlkDXF1ZXJ5QW5kRmV0Y2gBFElGYk9NM1FCZzVNR1ZIUlQ2TTVFAAAAAAAAjf8WWXFVTEJFUjVUZlM0NHpEaVZHSTZYdw=="
74    }
75    
76    # 加上 fetch_size 就会有 cursor
77    # cursor 分页例子，获取 cursor 值发送请求
78    POST _sql?format=json
79    {
80      "query": "SELECT * FROM es_biz_trade_product",
81      "fetch_size": 2,
82      "columnar": true
83    }
84    
85    POST _sql?format=json
86    {
87      "cursor" : "s9+wAwFaAXN4RkdsdVkyeDFaR1ZmWTI5dWRHVjRkRjkxZFdsa0RYRjFaWEo1UVc1a1JtVjBZMmdCRkhCc1ltVk5NMUZDWnpWTlIxWklVbFJVWXpockFBQUFBQUFBajRVV1dYRlZURUpGVWpWVVpsTTBOSHBFYVZaSFNUWllkdz09/////w8FAWYNY3VycmVudF9wcmljZQENY3VycmVudF9wcmljZQEGZG91YmxlAAAAAWYCaWQBAmlkAQRsb25nAAAAAWYEbmFtZQEEbmFtZQEEdGV4dAAAAAFmDm9yaWdpbmFsX3ByaWNlAQ5vcmlnaW5hbF9wcmljZQEGZG91YmxlAAAAAWYJc2Nyb2xsX2lkAQlzY3JvbGxfaWQBBHRleHQAAAABHw==",
88      "columnar": true
89    }
90    # cursor 用完，也要关闭
91    POST /_sql/close
92    {
93      "cursor": "s9+wAwFaAXN4RkdsdVkyeDFaR1ZmWTI5dWRHVjRkRjkxZFdsa0RYRjFaWEo1UVc1a1JtVjBZMmdCRkhCc1ltVk5NMUZDWnpWTlIxWklVbFJVWXpockFBQUFBQUFBajRVV1dYRlZURUpGVWpWVVpsTTBOSHBFYVZaSFNUWllkdz09/////w8FAWYNY3VycmVudF9wcmljZQENY3VycmVudF9wcmljZQEGZG91YmxlAAAAAWYCaWQBAmlkAQRsb25nAAAAAWYEbmFtZQEEbmFtZQEEdGV4dAAAAAFmDm9yaWdpbmFsX3ByaWNlAQ5vcmlnaW5hbF9wcmljZQEGZG91YmxlAAAAAWYJc2Nyb2xsX2lkAQlzY3JvbGxfaWQBBHRleHQAAAABHw=="
94    }
95    
96    # 参数
97    query 
98    fetch_size
99    filter
100    request_timeout
101    page_timeout
102    time_zone
103    columnar
104    field_multi_value_leniency
105    index_include_frozen
106    params
107    
108    params 参数解析例子
109    POST _sql?format=json
110    {
111      "query": """
112      SELECT * FROM "es_biz_trade_product" where original_price > ?
113      """,
114      "params": [100]
115    }