In order to test the plugin, we need to load some data. I’ve picked a few movies. You can copy the below and paste into your command line:
curl -XPOST localhost:9200/movies/movie/_bulk -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 -H "Content-type: application/json" -XGET localhost:9200/_opendistro/_sql?sql=SELECT%20title%20FROM%20movies
{"took":9,"timed_out":false,"_shards":{"total":5,"successful":5,"skipped":0,"failed":0},"hits":{"total":7,"max_score":1.0,"hits":[{"_index":"movies","_type":"movie","_id":"wDgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"Django Unchained"}},{"_index":"movies","_type":"movie","_id":"ujgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"Star Trek Into Darkness"}},{"_index":"movies","_type":"movie","_id":"vDgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"Rush"}},{"_index":"movies","_type":"movie","_id":"vjgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"The Avengers"}},{"_index":"movies","_type":"movie","_id":"vTgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"Gravity"}},{"_index":"movies","_type":"movie","_id":"vzgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"The Dark Knight Rises"}},{"_index":"movies","_type":"movie","_id":"uzgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"Star Wars"}}]}}
Note that, since I’m using curl, I had to URL-encode the sql statement. Depending on your method for connecting, you might also have to URL-encode. The more reliable method is to POST the SQL request in the body:
$ curl -H "Content-type: application/json" -XPOST localhost:9200/_opendistro/_sql -d'{
"query" : "SELECT title FROM movies"
}'
{"took":3,"timed_out":false,"_shards":{"total":5,"successful":5,"skipped":0,"failed":0},"hits":{"total":7,"max_score":1.0,"hits":[{"_index":"movies","_type":"movie","_id":"wDgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"Django Unchained"}},{"_index":"movies","_type":"movie","_id":"ujgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"Star Trek Into Darkness"}},{"_index":"movies","_type":"movie","_id":"vDgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"Rush"}},{"_index":"movies","_type":"movie","_id":"vjgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"The Avengers"}},{"_index":"movies","_type":"movie","_id":"vTgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"Gravity"}},{"_index":"movies","_type":"movie","_id":"vzgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"The Dark Knight Rises"}},{"_index":"movies","_type":"movie","_id":"uzgvb2kBQ4VucTC3NDK9","_score":1.0,"_source":{"title":"Star Wars"}}]}}
Be sure to take down your instance when you’re done testing, to avoid additional charges.
Conclusion
Congratulations! You have installed Elasticsearch OSS 6.5.4, downloaded and built the Open Distro for Elasticsearch SQL plugin, loaded some data, and run queries in SQL. In future posts, we’ll explore building the other plugins and modifying them. If you’ve got additions, bug fixes, or other great ideas, we welcome all pull requests and commentary.
Have an issue or question? Want to contribute? You can get help and discuss Open Distro for Elasticsearch on our forums. You can file issues here.