IMPORTANT: No additional bug fixes or documentation updates will be released for this version. For the latest information, see the current release documentation.

« Overview Conventions and Terminology »

› ›

Getting Started with SQL

edit

Getting Started with SQL

edit

To 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

« Overview Conventions and Terminology »