This documentation contains work-in-progress information for future Elastic Stack and Cloud releases. Use the version selector to view supported release docs. It also contains some Elastic Cloud serverless information. Check out our serverless docs for more details.
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