Convert SQL query to Elasticsearch dsl for data visualization

Question

Currently I'm using SQL queries in Elasticsearch but wanted to convert them to Elasticsearch DSL due to some data issues.

My current sql query is

{
    "query": "select \"A\",\"B\" from \"sometable\"  ORDER BY datetime DESC LIMIT 10"
}

which returns these results:

{
    "columns": [
        {
            "name": "A",
            "type": "float"
        },
        {
            "name": "B",
            "type": "float"
        }
    ],
    "rows": [
        [
            81.22833251953125,
            22.495885848999023
        ],
        [
            81.22833251953125,
            22.495885848999023
        ]
    ]
}

But I want the same result using Elasticsearch DSL in this format [[x,y],[x,y]]. I have also tried to translate the same SQL query to Elasticsearch DSL but it returns a different result.

The DSL query I have created so far is below but how we can limit the records for example 100.

{
   "aggs": {
        "documentCounts": {
          "scripted_metric": {
            "init_script": "state.transactions = []",
            "map_script": "state.transactions.add( [doc['A'].value,doc['B'].value] )",
            "combine_script": " return state.transactions"
          }
       }
    }
}

Alessandro S. · Accepted Answer · 2022-01-12 19:43:49Z

1

You could use Apache Calcite which provides an ElasticSearch adapter.

In the documentation you have an example on how to use the CSV adapter: https://calcite.apache.org/docs/tutorial.html, you can easily change the setup files to achieve the same for ES.

At that point you can use sqlline to query ES via SQL statement, Calcite will do the translation to ES QL, and fetch the results.

In order to print the query that is sent to ES, simply change the log level to DEBUG for the class ElasticsearchTable, for instance using this log4j.properties file:

# Set root logger level to DEBUG and its only appender to A1.
log4j.category.org.apache.calcite.adapter.elasticsearch.ElasticsearchTable=DEBUG, A1

# A1 is set to be a ConsoleAppender.
log4j.appender.A1=org.apache.log4j.ConsoleAppender

# A1 uses PatternLayout.
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n

Then you can point to this log4j property file in sqlline script as follows:

export JAVA_OPTS="-Djavax.xml.parsers.DocumentBuilderFactory=com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl -Dlog4j.configuration=file://$YOUR_PATH/log4j.properties"

Now simply run your select statement via sqlline and check for the debug string starting with Elasticsearch Query: in the console.

Here an example of what I get as output:

$ ./sqlline
Building Apache Calcite 1.30.0-SNAPSHOT
sqlline version 1.12.0
sqlline> !connect jdbc:calcite:model=model.json admin admin
log4j:WARN No appenders could be found for logger (org.apache.calcite.adapter.elasticsearch.ElasticsearchSchemaFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
Transaction isolation level TRANSACTION_REPEATABLE_READ is not supported. Default (TRANSACTION_NONE) will be used instead.
0: jdbc:calcite:model=model.json> select * from "zips";
3576 [main] DEBUG org.apache.calcite.adapter.elasticsearch.ElasticsearchTable  - Elasticsearch Query: {"size":5196}
+----------------------------------------------------------------------------------+
|                                       _MAP                                       |
+----------------------------------------------------------------------------------+
| {id=01701, city=FRAMINGHAM, loc=[-71.425486, 42.300665], pop=65046, state=MA}    |
| {id=02154, city=NORTH WALTHAM, loc=[-71.236497, 42.382492], pop=57871, state=MA} |
+----------------------------------------------------------------------------------+
2 rows selected (0.974 seconds)

edited Jan 12, 2022 at 19:43

answered Dec 22, 2021 at 16:55

Alessandro S.

1,04315 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Abdullah Ahsan Over a year ago

Thanks for the reply Alessandro, I will check this.

Abdullah Ahsan Over a year ago

Hi Alessandro, I have set up Apache Calcite and run the query, It shows the required results but where can I see the translated SQL query?

Alessandro S. Over a year ago

Did you run it programmatically or via sqline? If it's the former, can you share your code snippet somewhere?

Abdullah Ahsan Over a year ago

I have run it using sqline.

Abdullah Ahsan Over a year ago

Hi Alessandro Thanks for the comment. I will try it and let you know. Thanks

|

Collectives™ on Stack Overflow

Convert SQL query to Elasticsearch dsl for data visualization

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related