Moql-elasticsearch Converters (Translator)

Source: Internet
Author: User

MOQL, in addition to providing syntax converters from MOQL syntax to SQL dialects such as Oracle, SQL Server, DB2, MySQL, PostgreSQL, and so on, currently supports conversion to elasticsearch syntax. The class name of the converter is org.moql.sql.es.ElasticSearchTranslator. Since MOQL is SQL like, this converter can complete the conversion from SQL syntax to Elasticsearch query syntax. Because the semantics of the Elasticsearch query syntax and the SQL syntactic semantics have their own merits, MOQL only completes the translation of the syntax of the semantic intersection. However, this partial conversion is sufficient for developers who are familiar with SQL and want to use Elasticsearch for initial queries. The usage and syntax conversion relationships of this converter are described in detail below.

code example

The use of the Elasticsearch converter is simple and the sample code is as follows:

Public static void main (string[] args) {

String sql = "Select W.country, Max (w.port), Min (w.port) from the Web W Group by W.country";

Try {

Convert SQL statement string to syntax string for Elasticsearch dialect

String es = moqlutils. Translatemoql2dialect (SQL, Sqldialecttype. ELASTICSEARCH);

es = Es.trim ();

Print output converted syntax string

System. out. println (es);

} catch (Moqlexception e) {

E.printstacktrace ();

}

}

The above code executes the following results:

{

"Size": 0,

"Aggs": {

"Condition": {

"Filter": {

"Match_all": {}

},

"Aggs": {

"Group": {

"Terms": {

"Field": "Country"

},

"Aggs": {

"Column1": {

"Max": {

"Field": "Port"

}

},

"Column2": {

"Min": {

"Field": "Port"

}

}

}

}

}

}

}

}

Additional instructions on converters can be found in the article "moql-Converter (Translator)". More examples of SQL to Elasticsearch DSL conversion can be found in the MOQL source Org.moql.core.test.TestElasticSearchTranslator.java file.

Syntax mapping

The MOQL converter does not fully convert the semantics of SQL and Elasticsearch, and the semantic conversion relationship between the two is described in detail below.

Moql

ElasticSearch

Union,intersect,except such as set operation clauses

Not converted Mappings

SELECT clause

When the MOQL statement does not contain the distinct and group clauses, the filter clause is mapped to Elasticsearch. Specifying a specific projection column in the SELECT clause at this point does not work, it is uniformly considered to be the selection of all columns, that is, select *, and when MOQL contains distinct and group clauses, it is mapped to the Aggs of Elasticsearch. The projection column in the SELECT clause should follow the conventions of the SQL syntax in order to convert correctly.

DISTINCT clause

The AGGS clause converted to Elasticsearch.

FROM clause

Do not convert

WHERE clause

(Note: The WHERE clause is converted to the filter clause of Elasticsearch, but when a group clause exists throughout the statement, the filter clause is nested in the AGGS clause)

And, or

Map to and filter and or filter

Not, <> (not equal to)

Map to not filter

= (equals)

Map to term filter

> (greater than), < (less than), >= (greater than or equal), <= (less than equals), between

Map to Range filter

Like

Map to RegExp Filter

Inch

Map to terms Filter

Is

Map to exists filter

Parentheses for changing the priority

Mapping to hierarchical relationships

Additional parts:

The Regex (field, pattern) function, which is used for regular expression matching. The matching pattern is richer than like, and can be found in the relevant documentation for regular expressions. The field parameter represents the fields to be matched, and the pattern parameter represents a matching string.

Map to RegExp Filter

Limit clause

Map to the Size property in Elasticsearch. The MOQL statement contains or does not contain the distinct and group clauses, which are mapped to the size property of the different elasticsearch clauses.

Order clause

When the MOQL statement does not contain the distinct and group clauses, it is mapped to the sort clause of elasticsearch, and when the MOQL statement contains the above clause, it is mapped to the Order property of the AGGS clause in terms.

q* ()

The query clause in Elasticsearch (full-text search) does not have a corresponding syntax in SQL, so this part of the syntax is designed in MOQL for a set of functions prefixed with q followed by elasticsearch corresponding to the query clause name, such as: Qmatch (...). Corresponds to the match query clause. Currently, MOQL only supports a qmatch (field,message) function. field can be either a field name or a string, converted to match query when it is a field name or only one field name in a string, such as: field1, ' field1 ' are field names and strings containing field names, all converted to match query When field is separated by a ', ' in a string that includes multiple field names, it is converted to multi match query, such as: ' Field1,field2 '.

The associated paths for MOQL are as follows:

Project Address: http://sourceforge.net/projects/moql/

Code path: Svn://svn.code.sf.net/p/moql/code/trunk

Moql-elasticsearch Converters (Translator)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.