Getting Started with SQL

To start using Elasticsearch SQL, create an index with some data to experiment with:

  1. PUT /library/book/_bulk?refresh
  2. {"index":{"_id": "Leviathan Wakes"}}
  3. {"name": "Leviathan Wakes", "author": "James S.A. Corey", "release_date": "2011-06-02", "page_count": 561}
  4. {"index":{"_id": "Hyperion"}}
  5. {"name": "Hyperion", "author": "Dan Simmons", "release_date": "1989-05-26", "page_count": 482}
  6. {"index":{"_id": "Dune"}}
  7. {"name": "Dune", "author": "Frank Herbert", "release_date": "1965-06-01", "page_count": 604}

And now you can execute SQL using the SQL REST API right away:

  1. POST /_sql?format=txt
  2. {
  3.   "query": "SELECT * FROM library WHERE release_date < '2000-01-01'"
  4. }

Which should return something along the lines of:

  1.     author     |     name      |  page_count   | release_date
  2. ---------------+---------------+---------------+------------------------
  3. Dan Simmons    |Hyperion       |482            |1989-05-26T00:00:00.000Z
  4. Frank Herbert  |Dune           |604            |1965-06-01T00:00:00.000Z

You can also use the SQL CLI. There is a script to start it shipped in x-pack’s bin directory:

  1. $ ./bin/elasticsearch-sql-cli

From there you can run the same query:

  1. sql> SELECT * FROM library WHERE release_date < '2000-01-01';
  2.     author     |     name      |  page_count   | release_date
  3. ---------------+---------------+---------------+------------------------
  4. Dan Simmons    |Hyperion       |482            |1989-05-26T00:00:00.000Z
  5. Frank Herbert  |Dune           |604            |1965-06-01T00:00:00.000Z