Using SQL to Search for Data in Elasticsearch

SQL Usage Example

• Use SQL to search for data on Kibana (recommended).

  Log in to Kibana and, on the Dev Tools page, send requests to _opendistro/_sql using request parameters or the request body.

  For example, run the following command to retrieve 50 records from the my-index index:

  1
  2
  3
  4POST _opendistro/_sql
  {
    "query": "SELECT * FROM my-index LIMIT 50"
  }
  
  

  By default, the query result is returned in JSON format. To return data in CSV format, set the format parameter in the command as follows:

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

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

• Run cURL commands on an ECS to execute SQL statements to search for data.

  For example, run the following command to retrieve 10 records from the kibana_sample_data_flights index.

  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'
  
  

  localhost indicates the cluster access address. username and password indicate the username and password for accessing the security-mode cluster, respectively.

Supported SQL Operations

• 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 ('Bob', 'David')
  Not	SELECT * FROM my-index WHERE name NOT IN ('Bob')
  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 constraints, 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

• You can only join two indexes.

• You must use an alias for each 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