SQLのはじめに（Getting Started with SQL）

SQLの使い始め

Elasticsearch SQLを使用するには、実験用のデータを含むインデックスを作成します:

Python

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)

Ruby

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

Js

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}

これで、SQL検索APIを使用してSQLを実行できます:

Python

resp = client.sql.query(
   format="txt",
   query="SELECT * FROM library WHERE release_date < '2000-01-01'",
)
print(resp)

Ruby

response = client.sql.query(
  format: 'txt',
  body: {
   query: "SELECT * FROM library WHERE release_date < '2000-01-01'"
  }
)
puts response

Js

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'"
}

これにより、次のような結果が返されるはずです:

テキスト

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

SQL CLIも使用できます。Elasticsearch binディレクトリに含まれているスクリプトを使用して起動します:

Bash

$ ./bin/elasticsearch-sql-cli

そこから同じクエリを実行できます:

Sqlcli

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