Elasticsearch SQL

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.

    GET _opendistro/_sql?sql=select * from my-index limit 50

    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

  

  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

  

  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 constraints and limitations, see Joins.

• Show

  Show commands display indices 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 indices.

• 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 indices. 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.