iamazy / Elasticsearch Sql

Elasticsearch-SQL

Build status

6.0.0 6.0.1 6.1.0 6.1.1 6.1.2 6.1.3
6.1.4 6.2.0 6.2.1 6.2.2 6.2.3 6.2.4
6.3.0 6.3.1 6.3.2 6.4.0 6.4.1 6.4.2
6.4.3 6.5.0 6.5.1 6.5.2 6.5.3 6.5.4
6.6.0 6.6.1 6.6.2 6.7.0 6.7.1 6.7.2
6.8.0 6.8.1 6.8.2 7.0.0 7.0.1 7.1.0
7.1.1 7.2.0 7.2.1 7.3.0 7.3.1 7.3.2
7.4.0 7.4.1 7.4.2 7.5.0 7.5.1 7.5.2
7.6.0 7.6.1 7.6.2 7.7.0 7.7.1 7.8.0
7.8.1 7.9.0 7.9.1 7.9.2 7.9.3 7.10.0
7.10.1 7.10.2 7.11.0 7.11.1 7.11.2

Description

rewrite elasticsearch-sql2 with antlr4, support jdbc

Changelog

Changelog

Maven

<dependency>
    <groupId>io.github.iamazy.elasticsearch.dsl</groupId>
    <artifactId>elasticsearch-sql-all</artifactId>
    <version>7.11.2</version>
</dependency>

或者

<dependencies>
    <dependency>
        <groupId>io.github.iamazy.elasticsearch.dsl</groupId>
        <artifactId>elasticsearch-sql-core</artifactId>
        <version>7.11.2</version>
    </dependency>
    <dependency>
        <groupId>io.github.iamazy.elasticsearch.dsl</groupId>
        <artifactId>elasticsearch-sql-jdbc</artifactId>
        <version>7.11.2</version>
    </dependency>
</dependencies>

Plugin(isql)

Installing

Elasticsearch {7.x}

./bin/elasticsearch-plugin install https://github.com/iamazy/elasticsearch-sql/releases/download/{isql-version}/elasticsearch-sql-plugin-{elasticsearch-version}.zip

Usage

1. query dataset with sql

POST _isql
{
    "sql":"select * from fruit"
}

2. parse sql into elasticsearch dsl

POST _isql/_explain
{
    "sql":"select * from fruit"
}

Wiki

elasticsearch-sql-wiki

Features

1. Based on antlr4

customize grammer of elasticsearch sql
support analyse the walk of sql ast and the relation of tokens

Ast

select name from student aggregate by terms(name,1)>(terms(aa,2),[apple,cardinality(ip),terms(aaa,1)>(terms(cc,10)>(terms(hh,3
)))]) limit 2,5

Relation of Tokens

2. Based on elasticsearch java rest high level client

support for request from third-party http component
cross-language
support for parsing sql into elasticsearch dsl
support x-pack
no need for request pool

3. Integrte into elasticsearch(isql)

Features

• [x] SQL Select
• [x] SQL Where
• [x] SQL Order by (Asc & Desc)
• [x] SQL Group by
• [x] ES Aggregate by
• [x] SQL And & Or
• [x] SQL In
• [x] SQL Between And
• [x] SQL Is
• [x] SQL Not
• [x] SQL Null
• [ ] SQL Nvl
• [x] SQL Max
• [x] SQL Min
• [x] SQL Sum
• [x] SQL Avg
• [x] SQL > & < & >= & <=
• [ ] ES Explain
• [x] ES FullText
• [x] ES Match
• [x] ES MultiMatch
• [x] ES QueryString
• [ ] ES SimpleQueryString
• [x] ES HasParent
• [x] ES HasChild
• [x] ES Join
• [x] ES Script
• [x] ES Fuzzy
• [x] ES Prefix
• [x] ES Regex
• [x] ES Term
• [x] ES Wildcard
• [x] ES Routing
• [x] ES Nested Query
• [x] ES Nested Aggregation
• [x] ES Include & Exclude
• [x] ES From
• [x] ES Size
• [x] ES Range(Number,Date)
• [x] ES MatchAll
• [x] ES MatchPhrase
• [x] ES MatchPhrasePrefix
• [x] ES DeleteByQuery
• [x] ES Cardinality
• [x] ES TopHits
• [x] ES Nested
• [x] ES GeoDistance
• [x] ES GeoBoundingBox
• [x] ES GeoPolygon
• [x] ES GeoShape
• [x] ES GeoJsonShape
• [x] ES SubAggregation
• [ ] ES Scroll Id
• [x] ES Highlighter
• [ ] ES Boosting
• [x] ES Function Score
• [x] ES Disjunction Max (DisMax)
• [x] SQL Like
• [x] SQL Desc
• [x] ES Reindex
• [x] ES Track Total Hits
• [x] SQL Update
• [x] ES Update By Query
• [x] SQL Delete
• [x] ES DeleteByQuery
• [x] SQL Insert
• [x] Java Jdbc

Todo

• [ ] SQL Having
• [ ] SQL Customise Function
• [ ] ES Analysis
• [ ] ES Boosting
• [ ] ...

Examples

1. select,include,exclude,from,where,in,and,or,has_parent,geo_distance,limit

select name,^h!age,h!gender from student where ((a in (1,2,3,4)) and has_parent(apple,bb~='fruit')) and c=1 and (coordinate = [40.0,30.0] and distance = '1km' or t='bb') limit 2,5

generate dsl

{
  "from" : 2,
  "size" : 5,
  "query" : {
    "bool" : {
      "must" : [ {
        "terms" : {
          "a" : [ "1", "2", "3", "4" ],
          "boost" : 1.0
        }
      }, {
        "has_parent" : {
          "query" : {
            "bool" : {
              "must" : [ {
                "match" : {
                  "bb" : {
                    "query" : "'fruit'",
                    "operator" : "OR",
                    "prefix_length" : 0,
                    "max_expansions" : 50,
                    "fuzzy_transpositions" : true,
                    "lenient" : false,
                    "zero_terms_query" : "NONE",
                    "auto_generate_synonyms_phrase_query" : true,
                    "boost" : 1.0
                  }
                }
              } ],
              "adjust_pure_negative" : true,
              "minimum_should_match" : "1",
              "boost" : 1.0
            }
          },
          "parent_type" : "apple",
          "score" : true,
          "ignore_unmapped" : false,
          "boost" : 1.0
        }
      }, {
        "term" : {
          "c" : {
            "value" : "1",
            "boost" : 1.0
          }
        }
      } ],
      "should" : [ {
        "geo_distance" : {
          "coordinate" : [ 30.0, 40.0 ],
          "distance" : 1000.0,
          "distance_type" : "arc",
          "validation_method" : "STRICT",
          "ignore_unmapped" : false,
          "boost" : 1.0
        }
      }, {
        "term" : {
          "t" : {
            "value" : "'bb'",
            "boost" : 1.0
          }
        }
      } ],
      "adjust_pure_negative" : true,
      "minimum_should_match" : "1",
      "boost" : 1.0
    }
  },
  "_source" : {
    "includes" : [ "name", "gender" ],
    "excludes" : [ "age" ]
  }
}

2. nested,query_string,match(~==)

select name from student where (([class1, age>1 and [class1.class2, name='hhha']] and c=1) or b~=='hhhhh') and query by 'apppple' limit 2,5

generate dsl

{
  "from" : 2,
  "size" : 5,
  "query" : {
    "bool" : {
      "must" : [ {
        "query_string" : {
          "query" : "apppple",
          "fields" : [ ],
          "type" : "best_fields",
          "default_operator" : "or",
          "max_determinized_states" : 10000,
          "enable_position_increments" : true,
          "fuzziness" : "AUTO",
          "fuzzy_prefix_length" : 0,
          "fuzzy_max_expansions" : 50,
          "phrase_slop" : 0,
          "escape" : false,
          "auto_generate_synonyms_phrase_query" : true,
          "fuzzy_transpositions" : true,
          "boost" : 1.0
        }
      } ],
      "should" : [ {
        "bool" : {
          "must" : [ {
            "nested" : {
              "query" : {
                "bool" : {
                  "must" : [ {
                    "range" : {
                      "age" : {
                        "from" : "1",
                        "to" : null,
                        "include_lower" : false,
                        "include_upper" : true,
                        "boost" : 1.0
                      }
                    }
                  }, {
                    "nested" : {
                      "query" : {
                        "bool" : {
                          "must" : [ {
                            "term" : {
                              "name" : {
                                "value" : "'hhha'",
                                "boost" : 1.0
                              }
                            }
                          } ],
                          "adjust_pure_negative" : true,
                          "minimum_should_match" : "1",
                          "boost" : 1.0
                        }
                      },
                      "path" : "class1.class2",
                      "ignore_unmapped" : false,
                      "score_mode" : "avg",
                      "boost" : 1.0
                    }
                  } ],
                  "adjust_pure_negative" : true,
                  "minimum_should_match" : "1",
                  "boost" : 1.0
                }
              },
              "path" : "class1",
              "ignore_unmapped" : false,
              "score_mode" : "avg",
              "boost" : 1.0
            }
          }, {
            "term" : {
              "c" : {
                "value" : "1",
                "boost" : 1.0
              }
            }
          } ],
          "adjust_pure_negative" : true,
          "boost" : 1.0
        }
      }, {
        "match_phrase" : {
          "b" : {
            "query" : "'hhhhh'",
            "slop" : 0,
            "zero_terms_query" : "NONE",
            "boost" : 1.0
          }
        }
      } ],
      "adjust_pure_negative" : true,
      "minimum_should_match" : "1",
      "boost" : 1.0
    }
  },
  "_source" : {
    "includes" : [ "name" ],
    "excludes" : [ ]
  }
}

3. aggregate by

select name from student aggregate by terms(name,1)>(terms(aa,2),terms(bb,3)>(terms(cc,4))),terms(age,10)>(terms(weight,10))

generate dsl

{
  "from" : 0,
  "size" : 15,
  "query" : {
    "match_all" : {
      "boost" : 1.0
    }
  },
  "_source" : {
    "includes" : [ "name" ],
    "excludes" : [ ]
  },
  "aggregations" : {
    "name" : {
      "terms" : {
        "size" : 1,
        "shard_size" : 2,
        "min_doc_count" : 1,
        "shard_min_doc_count" : 1,
        "show_term_doc_count_error" : false,
        "order" : [ {
          "_count" : "desc"
        }, {
          "_key" : "asc"
        } ]
      },
      "aggregations" : {
        "aa" : {
          "terms" : {
            "size" : 2,
            "shard_size" : 4,
            "min_doc_count" : 1,
            "shard_min_doc_count" : 1,
            "show_term_doc_count_error" : false,
            "order" : [ {
              "_count" : "desc"
            }, {
              "_key" : "asc"
            } ]
          }
        },
        "bb" : {
          "terms" : {
            "size" : 3,
            "shard_size" : 6,
            "min_doc_count" : 1,
            "shard_min_doc_count" : 1,
            "show_term_doc_count_error" : false,
            "order" : [ {
              "_count" : "desc"
            }, {
              "_key" : "asc"
            } ]
          },
          "aggregations" : {
            "cc" : {
              "terms" : {
                "size" : 4,
                "shard_size" : 8,
                "min_doc_count" : 1,
                "shard_min_doc_count" : 1,
                "show_term_doc_count_error" : false,
                "order" : [ {
                  "_count" : "desc"
                }, {
                  "_key" : "asc"
                } ]
              }
            }
          }
        }
      }
    },
    "age" : {
      "terms" : {
        "size" : 10,
        "shard_size" : 20,
        "min_doc_count" : 1,
        "shard_min_doc_count" : 1,
        "show_term_doc_count_error" : false,
        "order" : [ {
          "_count" : "desc"
        }, {
          "_key" : "asc"
        } ]
      },
      "aggregations" : {
        "weight" : {
          "terms" : {
            "size" : 10,
            "shard_size" : 20,
            "min_doc_count" : 1,
            "shard_min_doc_count" : 1,
            "show_term_doc_count_error" : false,
            "order" : [ {
              "_count" : "desc"
            }, {
              "_key" : "asc"
            } ]
          }
        }
      }
    }
  }
}

4. nested aggregation,subAggregation(~)

select name from student aggregate by terms(name,1)>(terms(aa,2),[apple,cardinality(ip),terms(aaa,1)>(terms(bb,1),terms(cc,10)>(terms(hh,3),avg(age)),terms(vv,1))]) limit 2,5

generate dsl

{
  "from" : 2,
  "size" : 5,
  "query" : {
    "match_all" : {
      "boost" : 1.0
    }
  },
  "_source" : {
    "includes" : [ "name" ],
    "excludes" : [ ]
  },
  "aggregations" : {
    "name" : {
      "terms" : {
        "size" : 1,
        "shard_size" : 2,
        "min_doc_count" : 1,
        "shard_min_doc_count" : 1,
        "show_term_doc_count_error" : false,
        "order" : [ {
          "_count" : "desc"
        }, {
          "_key" : "asc"
        } ]
      },
      "aggregations" : {
        "aa" : {
          "terms" : {
            "size" : 2,
            "shard_size" : 4,
            "min_doc_count" : 1,
            "shard_min_doc_count" : 1,
            "show_term_doc_count_error" : false,
            "order" : [ {
              "_count" : "desc"
            }, {
              "_key" : "asc"
            } ]
          }
        },
        "nested_apple" : {
          "nested" : {
            "path" : "apple"
          },
          "aggregations" : {
            "ip_cardinality" : {
              "cardinality" : {
                "field" : "ip"
              }
            },
            "aaa" : {
              "terms" : {
                "size" : 1,
                "shard_size" : 2,
                "min_doc_count" : 1,
                "shard_min_doc_count" : 1,
                "show_term_doc_count_error" : false,
                "order" : [ {
                  "_count" : "desc"
                }, {
                  "_key" : "asc"
                } ]
              },
              "aggregations" : {
                "bb" : {
                  "terms" : {
                    "size" : 1,
                    "shard_size" : 2,
                    "min_doc_count" : 1,
                    "shard_min_doc_count" : 1,
                    "show_term_doc_count_error" : false,
                    "order" : [ {
                      "_count" : "desc"
                    }, {
                      "_key" : "asc"
                    } ]
                  }
                },
                "cc" : {
                  "terms" : {
                    "size" : 10,
                    "shard_size" : 20,
                    "min_doc_count" : 1,
                    "shard_min_doc_count" : 1,
                    "show_term_doc_count_error" : false,
                    "order" : [ {
                      "_count" : "desc"
                    }, {
                      "_key" : "asc"
                    } ]
                  },
                  "aggregations" : {
                    "hh" : {
                      "terms" : {
                        "size" : 3,
                        "shard_size" : 6,
                        "min_doc_count" : 1,
                        "shard_min_doc_count" : 1,
                        "show_term_doc_count_error" : false,
                        "order" : [ {
                          "_count" : "desc"
                        }, {
                          "_key" : "asc"
                        } ]
                      }
                    },
                    "age_avg" : {
                      "avg" : {
                        "field" : "age"
                      }
                    }
                  }
                },
                "vv" : {
                  "terms" : {
                    "size" : 1,
                    "shard_size" : 2,
                    "min_doc_count" : 1,
                    "shard_min_doc_count" : 1,
                    "show_term_doc_count_error" : false,
                    "order" : [ {
                      "_count" : "desc"
                    }, {
                      "_key" : "asc"
                    } ]
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}