Getting Started with SQL
editGetting Started with SQL
editTo start using Elasticsearch SQL, create an index with some data to experiment with:
resp = client.bulk( index="library", refresh=True, operations=[ { "index": { "_id": "Leviathan Wakes" } }, { "name": "Leviathan Wakes", "author": "James S.A. Corey", "release_date": "2011-06-02", "page_count": 561 }, { "index": { "_id": "Hyperion" } }, { "name": "Hyperion", "author": "Dan Simmons", "release_date": "1989-05-26", "page_count": 482 }, { "index": { "_id": "Dune" } }, { "name": "Dune", "author": "Frank Herbert", "release_date": "1965-06-01", "page_count": 604 } ], ) print(resp)
response = client.bulk( index: 'library', refresh: true, body: [ { index: { _id: 'Leviathan Wakes' } }, { name: 'Leviathan Wakes', author: 'James S.A. Corey', release_date: '2011-06-02', page_count: 561 }, { index: { _id: 'Hyperion' } }, { name: 'Hyperion', author: 'Dan Simmons', release_date: '1989-05-26', page_count: 482 }, { index: { _id: 'Dune' } }, { name: 'Dune', author: 'Frank Herbert', release_date: '1965-06-01', page_count: 604 } ] ) puts response
const response = await client.bulk({ index: "library", refresh: "true", operations: [ { index: { _id: "Leviathan Wakes", }, }, { name: "Leviathan Wakes", author: "James S.A. Corey", release_date: "2011-06-02", page_count: 561, }, { index: { _id: "Hyperion", }, }, { name: "Hyperion", author: "Dan Simmons", release_date: "1989-05-26", page_count: 482, }, { index: { _id: "Dune", }, }, { name: "Dune", author: "Frank Herbert", release_date: "1965-06-01", page_count: 604, }, ], }); console.log(response);
PUT /library/_bulk?refresh {"index":{"_id": "Leviathan Wakes"}} {"name": "Leviathan Wakes", "author": "James S.A. Corey", "release_date": "2011-06-02", "page_count": 561} {"index":{"_id": "Hyperion"}} {"name": "Hyperion", "author": "Dan Simmons", "release_date": "1989-05-26", "page_count": 482} {"index":{"_id": "Dune"}} {"name": "Dune", "author": "Frank Herbert", "release_date": "1965-06-01", "page_count": 604}
And now you can execute SQL using the SQL search API:
resp = client.sql.query( format="txt", query="SELECT * FROM library WHERE release_date < '2000-01-01'", ) print(resp)
response = client.sql.query( format: 'txt', body: { query: "SELECT * FROM library WHERE release_date < '2000-01-01'" } ) puts response
const response = await client.sql.query({ format: "txt", query: "SELECT * FROM library WHERE release_date < '2000-01-01'", }); console.log(response);
POST /_sql?format=txt { "query": "SELECT * FROM library WHERE release_date < '2000-01-01'" }
Which should return something along the lines of:
author | name | page_count | release_date ---------------+---------------+---------------+------------------------ Dan Simmons |Hyperion |482 |1989-05-26T00:00:00.000Z 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 the Elasticsearch bin
directory:
$ ./bin/elasticsearch-sql-cli
From there you can run the same query:
sql> SELECT * FROM library WHERE release_date < '2000-01-01'; author | name | page_count | release_date ---------------+---------------+---------------+------------------------ Dan Simmons |Hyperion |482 |1989-05-26T00:00:00.000Z Frank Herbert |Dune |604 |1965-06-01T00:00:00.000Z