Query Elasticsearch

While most of the White Label Data documentation refers to placing SQL in queries, it is also possible to query Elasticsearch using a JSON-formatted query. First, you must Create an Elasticsearch Connection. To add a query to a visualization, you create a <query> section in a Layer file and write your search in JSON format. Then, you add a step to the pipeline corresponding to that query. An Elasticsearch query configuration looks as follows:

Query:

<query name="myquery">
{
  "_source": {
    "excludes": []
  },
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "timestamp": {
              "gte": 1559153067954,
              "lte": 1559239467954,
              "format": "epoch_millis"
            }
          }
        }
      ],
      "filter": [],
      "should": [],
      "must_not": [
        {
          "match_phrase": {
            "FlightDelayMin": {
              "query": 0
            }
          }
        }
      ]
    }
  }
}
</query>

Pipeline:

{
    "steps" : [
        {
            "action" : "elasticsearch",
            "query_name": "myquery",
            "index": "my-elasticsearch-index",
            "resultspath": [ "hits", "hits" ]
        }
    ]
}

Options

Option Value(s) Description
action snowflake_query Specifies that it is a Snowflake query.
index A string The name of the Elasticsearch index to use for the query.
resultspath A list of strings The path to the results in the response. White Label Data will walk the JSON response in order of the list to find the array of results, which will then be placed in a DataFrame.
query_name A string The name of the query specified in a <query> tag within the combined Layer.
output_dataframe A string The name to use when creating a new DataFrame. This allows you to have multiple DataFrames in the pipeline context and to map columns from multiple queries to a single visualization. If no name is specified, the DataFrame will be named after the query name.
connection A string Optional. The name of the connection specified in appconfig.json. This is only needed if there are two connections of the same type. For example, if you have two Snowflake connections, you need to specify which one to use for this Snowflake query pipeline step.
shared true or false Indicates whether the query is shared and available to multiple visualizations. See Shared Queries.