Getting Started with SQL

Getting Started with SQL

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

  1. resp = client.bulk(
  2.     index="library",
  3.     refresh=True,
  4.     operations=[
  5.         {
  6.             "index": {
  7.                 "_id": "Leviathan Wakes"
  8.             }
  9.         },
  10.         {
  11.             "name": "Leviathan Wakes",
  12.             "author": "James S.A. Corey",
  13.             "release_date": "2011-06-02",
  14.             "page_count": 561
  15.         },
  16.         {
  17.             "index": {
  18.                 "_id": "Hyperion"
  19.             }
  20.         },
  21.         {
  22.             "name": "Hyperion",
  23.             "author": "Dan Simmons",
  24.             "release_date": "1989-05-26",
  25.             "page_count": 482
  26.         },
  27.         {
  28.             "index": {
  29.                 "_id": "Dune"
  30.             }
  31.         },
  32.         {
  33.             "name": "Dune",
  34.             "author": "Frank Herbert",
  35.             "release_date": "1965-06-01",
  36.             "page_count": 604
  37.         }
  38.     ],
  39. )
  40. print(resp)
  1. response = client.bulk(
  2.   index: 'library',
  3.   refresh: true,
  4.   body: [
  5.     {
  6.       index: {
  7.         _id: 'Leviathan Wakes'
  8.       }
  9.     },
  10.     {
  11.       name: 'Leviathan Wakes',
  12.       author: 'James S.A. Corey',
  13.       release_date: '2011-06-02',
  14.       page_count: 561
  15.     },
  16.     {
  17.       index: {
  18.         _id: 'Hyperion'
  19.       }
  20.     },
  21.     {
  22.       name: 'Hyperion',
  23.       author: 'Dan Simmons',
  24.       release_date: '1989-05-26',
  25.       page_count: 482
  26.     },
  27.     {
  28.       index: {
  29.         _id: 'Dune'
  30.       }
  31.     },
  32.     {
  33.       name: 'Dune',
  34.       author: 'Frank Herbert',
  35.       release_date: '1965-06-01',
  36.       page_count: 604
  37.     }
  38.   ]
  39. )
  40. puts response
  1. const response = await client.bulk({
  2.   index: "library",
  3.   refresh: "true",
  4.   operations: [
  5.     {
  6.       index: {
  7.         _id: "Leviathan Wakes",
  8.       },
  9.     },
  10.     {
  11.       name: "Leviathan Wakes",
  12.       author: "James S.A. Corey",
  13.       release_date: "2011-06-02",
  14.       page_count: 561,
  15.     },
  16.     {
  17.       index: {
  18.         _id: "Hyperion",
  19.       },
  20.     },
  21.     {
  22.       name: "Hyperion",
  23.       author: "Dan Simmons",
  24.       release_date: "1989-05-26",
  25.       page_count: 482,
  26.     },
  27.     {
  28.       index: {
  29.         _id: "Dune",
  30.       },
  31.     },
  32.     {
  33.       name: "Dune",
  34.       author: "Frank Herbert",
  35.       release_date: "1965-06-01",
  36.       page_count: 604,
  37.     },
  38.   ],
  39. });
  40. console.log(response);
  1. PUT /library/_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 search API:

  1. resp = client.sql.query(
  2.     format="txt",
  3.     query="SELECT * FROM library WHERE release_date < '2000-01-01'",
  4. )
  5. print(resp)
  1. response = client.sql.query(
  2.   format: 'txt',
  3.   body: {
  4.     query: "SELECT * FROM library WHERE release_date < '2000-01-01'"
  5.   }
  6. )
  7. puts response
  1. const response = await client.sql.query({
  2.   format: "txt",
  3.   query: "SELECT * FROM library WHERE release_date < '2000-01-01'",
  4. });
  5. console.log(response);
  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 the Elasticsearch 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