Elasticsearch

Elasticsearch

The recommended connector library for Elasticsearch is elasticsearch-dbapi.

The connection string for Elasticsearch looks like this:

elasticsearch+http://{user}:{password}@{host}:9200/

Using HTTPS

elasticsearch+https://{user}:{password}@{host}:9200/

Elasticsearch as a default limit of 10000 rows, so you can increase this limit on your cluster or set Feris’s row limit on config

ROW_LIMIT = 10000

You can query multiple indices on SQL Lab for example

SELECT timestamp, agent FROM "logstash"

But, to use visualizations for multiple indices you need to create an alias index on your cluster

POST /_aliases
{
    "actions" : [
        { "add" : { "index" : "logstash-**", "alias" : "logstash_all" } }
    ]
}

Then register your table with the alias name logstasg_all

Time zone

By default, StreamZero uses UTC time zone for elasticsearch query. If you need to specify a time zone, please edit your Database and enter the settings of your specified time zone in the Other > ENGINE PARAMETERS:

{
    "connect_args": {
        "time_zone": "Asia/Shanghai"
    }
}

Another issue to note about the time zone problem is that before elasticsearch7.8, if you want to convert a string into a DATETIME object, you need to use the CAST function,but this function does not support our time_zone setting. So it is recommended to upgrade to the version after elasticsearch7.8. After elasticsearch7.8, you can use the DATETIME_PARSE function to solve this problem. The DATETIME_PARSE function is to support our time_zone setting, and here you need to fill in your elasticsearch version number in the Other > VERSION setting. the StreamZero will use the DATETIME_PARSE function for conversion.