Using the Open Distro SQL Plug-in to Compile Queries

For Elasticsearch 6.5.4 and later versions, Open Distro for Elasticsearch SQL lets you write queries in SQL rather than in the Elasticsearch query domain-specific language (DSL).

If you are already familiar with SQL and do not want to learn query DSL, this feature is a great option.

Basic Operations

  • Kibana (recommended)

    • Log in to Kibana and send requests using request parameters or request body to _opendistro/_sqlURI in the Dev Tools page.

      POST _opendistro/_sql
      {
        "query": "SELECT * FROM my-index LIMIT 50"
      }
      
    • By default, the result is returned in the JSON structure. If you want the result to be returned in the CSV format, run the following command:

      POST _opendistro/_sql?format=csv
      {
        "query": "SELECT * FROM my-index LIMIT 50"
      }
      

      When data is returned in the CSV format, each row corresponds to a document and each column corresponds to a field.

  • cURL commands

    You can also run cURL commands in ECS to execute SQL statements.

    curl -XPOST https://localhost:9200/_opendistro/_sql -u username:password -k -d '{"query": "SELECT * FROM kibana_sample_data_flights LIMIT 10"}' -H 'Content-Type: application/json'
    

Supported Operations

Open Distro for Elasticsearch supports the following SQL operations: statements, conditions, aggregations, include and exclude fields, common functions, joins, and show.

  • Statements

    Table 1 Statements

    Statement

    Example

    Select

    SELECT * FROM my-index

    Delete

    DELETE FROM my-index WHERE _id=1

    Where

    SELECT * FROM my-index WHERE ['field']='value'

    Order by

    SELECT * FROM my-index ORDER BY _id asc

    Group by

    SELECT * FROM my-index GROUP BY range(age, 20,30,39)

    Limit

    SELECT * FROM my-index LIMIT 50 (default is 200)

    Union

    SELECT * FROM my-index1 UNION SELECT * FROM my-index2

    Minus

    SELECT * FROM my-index1 MINUS SELECT * FROM my-index2

    Note

    As with any complex query, large UNION and MINUS statements can strain or even crash your cluster.

  • Conditions

    Table 2 Conditions

    Condition

    Example

    Like

    SELECT * FROM my-index WHERE name LIKE 'j%'

    And

    SELECT * FROM my-index WHERE name LIKE 'j%' AND age > 21

    Or

    SELECT * FROM my-index WHERE name LIKE 'j%' OR age > 21

    Count distinct

    SELECT count(distinct age) FROM my-index

    In

    SELECT * FROM my-index WHERE name IN ('alejandro', 'carolina')

    Not

    SELECT * FROM my-index WHERE name NOT IN ('jane')

    Between

    SELECT * FROM my-index WHERE age BETWEEN 20 AND 30

    Aliases

    SELECT avg(age) AS Average_Age FROM my-index

    Date

    SELECT * FROM my-index WHERE birthday='1990-11-15'

    Null

    SELECT * FROM my-index WHERE name IS NULL

  • Aggregations

    Table 3 Aggregations

    Aggregation

    Example

    avg()

    SELECT avg(age) FROM my-index

    count()

    SELECT count(age) FROM my-index

    max()

    SELECT max(age) AS Highest_Age FROM my-index

    min()

    SELECT min(age) AS Lowest_Age FROM my-index

    sum()

    SELECT sum(age) AS Age_Sum FROM my-index

  • Include and exclude fields

    Table 4 Include and exclude fields

    Pattern

    Example

    include()

    SELECT include('a*'), exclude('age') FROM my-index

    exclude()

    SELECT exclude('*name') FROM my-index

  • Functions

    Table 5 Functions

    Function

    Example

    floor

    SELECT floor(number) AS Rounded_Down FROM my-index

    trim

    SELECT trim(name) FROM my-index

    log

    SELECT log(number) FROM my-index

    log10

    SELECT log10(number) FROM my-index

    substring

    SELECT substring(name, 2,5) FROM my-index

    round

    SELECT round(number) FROM my-index

    sqrt

    SELECT sqrt(number) FROM my-index

    concat_ws

    SELECT concat_ws(' ', age, height) AS combined FROM my-index

    /

    SELECT number / 100 FROM my-index

    %

    SELECT number % 100 FROM my-index

    date_format

    SELECT date_format(date, 'Y') FROM my-index

    Note

    You must enable fielddata in the document mapping for most string functions to work properly.

  • Joins

    Table 6 Joins

    Join

    Example

    Inner join

    SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s JOIN school sc ON sc.name = s.school_name WHERE s.age > 20

    Left outer join

    SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s LEFT JOIN school sc ON sc.name = s.school_name

    Cross join

    SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s CROSS JOIN school sc

    For details about the restrictions, see Joins.

  • Show

    Show commands display indexes and mappings that match an index pattern. You can use * or % for wildcards.

    Table 7 Show

    Show

    Example

    Show tables like

    SHOW TABLES LIKE logs-*

Joins

Open Distro for Elasticsearch SQL supports inner joins, left outer joins and cross joins. Joins have the following constraints:

  • You can only join two indexes.

  • You must use an alias for an index (for example, people p).

  • In an ON clause, you can only use the AND conditions.

  • In a WHERE statement, do not combine trees that contain multiple indexes. For example, the following statement will work:

    WHERE (a.type1 > 3 OR a.type1 < 0) AND (b.type2 > 4 OR b.type2 < -1)
    

    The following statement will not work:

    WHERE (a.type1 > 3 OR b.type2 < 0) AND (a.type1 > 4 OR b.type2 < -1)
    
  • You cannot use GROUP BY or ORDER BY to obtain results.

  • LIMIT with OFFSET (for example, LIMIT 25 OFFSET 25) is not supported.

JDBC Driver

The Java Database Connectivity (JDBC) driver allows you to integrate Open Distro for Elasticsearch with your business intelligence (BI) applications.

For details about how to download and use JAR files, see GitHub Repositories.