Elasticsearch

The recommended connector library for Elasticsearch is elasticsearch-dbapi.

The connection string for Elasticsearch looks like this:

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

Using HTTPS

  1. 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 Superset’s row limit on config

  1. ROW_LIMIT = 10000

You can query multiple indices on SQL Lab for example

  1. SELECT timestamp, agent FROM "logstash"

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

  1. POST /_aliases
  2. {
  3. "actions" : [
  4. { "add" : { "index" : "logstash-**", "alias" : "logstash_all" } }
  5. ]
  6. }

Then register your table with the alias name logstash_all

Time zone

By default, Superset 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:

  1. {
  2. "connect_args": {
  3. "time_zone": "Asia/Shanghai"
  4. }
  5. }

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 superset will use the DATETIME_PARSE function for conversion.

Disable SSL Verification

To disable SSL verification, add the following to the SQLALCHEMY URI field:

  1. elasticsearch+https://{user}:{password}@{host}:9200/?verify_certs=False