Lab 5 : SQL Plugin

Lets load some movie data to run SQL statements. Copy the below and paste into your command line and type in the new admin password at the prompt.

curl -k -XPOST https://localhost:9200/movies/movie/_bulk -u admin -H "Content-type: application/json" -d '
{"index" : { } }
{"title": "Star Trek Into Darkness", "directors": ["J.J. Abrams"], "genres": ["Action", "Adventure", "Sci-Fi"], "actors": ["Chris Pine", "Zachary Quinto", "Zoe Saldana"], "id": "tt1408101"}
{"index" : { } }
{"title": "Star Wars", "directors": ["George Lucas"], "genres": ["Action", "Adventure", "Fantasy", "Sci-Fi"], "actors": ["Mark Hamill", "Harrison Ford", "Carrie Fisher"], "id": "tt0076759"}
{"index" : { } }
{"title": "Rush", "directors": ["Ron Howard"], "genres": ["Action", "Biography", "Drama", "Sport"], "actors": ["Daniel Br\u00fchl", "Chris Hemsworth", "Olivia Wilde"], "id": "tt1979320"}
{"index" : { } }
{"title": "Gravity", "directors": ["Alfonso Cuar\u00f3n"], "genres": ["Drama", "Sci-Fi", "Thriller"], "actors": ["Sandra Bullock", "George Clooney", "Ed Harris"], "id": "tt1454468"}
{"index" : { } }
{"title": "The Avengers", "directors": ["Joss Whedon"], "genres": ["Action", "Fantasy"], "actors": ["Robert Downey Jr.", "Chris Evans", "Scarlett Johansson"], "id": "tt0848228"}
{"index" : { } }
{"title": "The Dark Knight Rises", "directors": ["Christopher Nolan"], "genres": ["Action", "Crime", "Thriller"], "actors": ["Christian Bale", "Tom Hardy", "Anne Hathaway"], "id": "tt1345836"}
{"index" : { } }
{"directors": ["Quentin Tarantino"], "genres": ["Adventure", "Drama", "Western"], "title": "Django Unchained", "actors": ["Jamie Foxx", "Christoph Waltz", "Leonardo DiCaprio"], "id": "tt1853728"}
'

You can send SQL commands to Elasticsearch using the simple (URL-based) query API to the _opendistro/_sql endpoint:

curl -k -H "Content-type: application/json" -u admin -XGET https://localhost:9200/_opendistro/_sql?sql=SELECT%20title%20FROM%20movies

You can also use Kibana Dev Tools to run the SQL queries. Some examples below

GET _opendistro/_sql
{
  "query": "SELECT title FROM movies"
}

Output in CSV

GET _opendistro/_sql?format=csv
{
  "query": "SELECT title FROM movies WHERE genres='Action'"
}

Elasticsearch query DSL from SQL query

GET _opendistro/_sql/_explain
{
  "query": "SELECT title FROM movies WHERE genres='Action'"
}