Connecting to AWS Elasticsearch 7.1?

Issue Summary

Connecting to AWS Elasticsearch reporting success on connection test, but fails to load indices or execute queries. Errors arise when refreshing (“Cannot refresh indices”) and when executing any query ("Type ‘bool’ is not iterable) - so it looks like possibly AWS Elasticsearch Service 7.1 is not supported?

Technical details:

  • Redash Version: 8.0.0
  • Browser/OS: chrome, macos
  • How did you install Redash: self hosted, premade ec2 ami
1 Like

Same case here, Elasticsearch also hosted.

Error same.

Tried to capture/decode communication with wireshark, Elasticsearch looks like correct output, no any error while linting, but looks a bit weird, like it contains lot’s of staff but no records.

Here is some more detailed error from redash:

[2020-03-04 11:22:31,194][PID:388][WARNING][ForkPoolWorker-333] task_name=redash.tasks.refresh_schema task_id=df03835a-1353-4d13-a1d9-397722499742 Failed refreshing schema for the data source: XXX
Traceback (most recent call last):
  File "/app/redash/tasks/queries.py", line 261, in refresh_schema
    ds.get_schema(refresh=True)
  File "/app/redash/models/__init__.py", line 155, in get_schema
    schema = sorted(query_runner.get_schema(get_stats=refresh), key=lambda t: t['name'])
  File "/app/redash/query_runner/elasticsearch.py", line 173, in get_schema
    columns.extend(parse_doc(items))
  File "/app/redash/query_runner/elasticsearch.py", line 154, in parse_doc
    for field, description in doc['properties'].items():
TypeError: string indices must be integers

Which ES connector? There are two.

As I have self hosted elastic tried both Kibana and Elasticsearch (not amazon one)

both end up with same result.

trying to run simple queries from this page:

https://redash.io/help/data-sources/querying/elasticsearch

just replacing names of index and fields.

Also found that if I specify wrong index name, correct error message appears that no such index.

Hi,
I tried to connect elasticsearch to redash but while checking for connection…I am getting an error as connection failed.Can you help me to how to connect with elasticsearch.

Thanks,
Thaya

I host on Elastic.co and was able to connect successfully and run a basic query using the Kibana connector and this format of host:
https://.us-west-2.aws.found.io:9243

I had some trouble with the ElasticSearch connector (test connection works, but queries time out), but this worked.

Now, I’m trying to figure out how to run the queries generated by Kibana, but I get the error below. I’m guessing there must be some nested data structures that Redash doesn’t know how to handle? I pretty new to ES so trying learning as I go with it.

Error running query: ‘list’ object has no attribute 'rstrip’

Query:

{"index":"snowplow",
  "version": true,
  "size": 500,
  "sort": [
    {
      "collector_tstamp": {
        "order": "desc",
        "unmapped_type": "boolean"
      }
    }
  ],
  "_source": {
    "excludes": []
  },
  "aggs": {
    "2": {
      "date_histogram": {
        "field": "collector_tstamp",
        "calendar_interval": "1m",
        "time_zone": "America/Los_Angeles",
        "min_doc_count": 1
      }
    }
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {
    "Server Load Time": {
      "script": {
        "source": "doc['contexts_org_w3_performance_timing_1.responseStart'].value - doc['contexts_org_w3_performance_timing_1.connectStart'].value",
        "lang": "painless"
      }
    },
    "Coupon": {
      "script": {
        "source": "if (doc['contexts_com_session_variables_1.coupon.keyword'].size() != 0) {\n    doc['contexts_com_session_variables_1.coupon.keyword'].value\n}",
        "lang": "painless"
      }
    }
  },
  "docvalue_fields": [
    {
      "field": "collector_tstamp",
      "format": "date_time"
    },
    {
      "field": "derived_tstamp",
      "format": "date_time"
    },
    {
      "field": "dvce_created_tstamp",
      "format": "date_time"
    },
    {
      "field": "dvce_sent_tstamp",
      "format": "date_time"
    },
    {
      "field": "etl_tstamp",
      "format": "date_time"
    }
  ],
  "query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "match_all": {}
        },
        {
          "range": {
            "collector_tstamp": {
              "format": "strict_date_optional_time",
              "gte": "2020-05-04T22:43:07.986Z",
              "lte": "2020-05-04T23:43:07.986Z"
            }
          }
        }
      ],
      "should": [],
      "must_not": []
    }
  },
  "highlight": {
    "pre_tags": [
      "@kibana-highlighted-field@"
    ],
    "post_tags": [
      "@/kibana-highlighted-field@"
    ],
    "fields": {
      "*": {}
    },
    "fragment_size": 2147483647
  }
}

Our ES connector is in bad condition. It was a community contribution from several years ago and needs to be rewritten. This process is underway but it’s a low priority for us. If ES is a major part of your data viz needs then Redash might not be the best tool for now.