Moql-elasticsearch Converters (Translator)

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)