[Elasticsearch]学会在Elasticsearch中使用SQL

1.概述

Overview

Elasticsearch SQL提供了功能强大又轻量的可以使用SQL与Elasticsearch交互的方式.

Elasticsearch SQL aims to provide a powerful yet lightweight SQL interface to Elasticsearch.

简介

Introduction

Elasticsearch SQL是属于X-Pack提供的功能组件，可以让我们近实时的对Elasticsearch执行类似SQL的查询.无论是使用REST API还是使用的命令行、JDBC或者其它客户端链接的Elasticsearch，都可以使用SQL执行搜索统计操作，就像Elasticsearch原生就支持SQL一样.可以把Elasticsearch SQL理解为一个翻译器，就是它可以把你写的SQL翻译为Elasticsearch的查询语言去执行.这样无论你是熟悉SQL还是熟悉Elasticsearch的查询语言都可以使用Elasticsearch近实时的读取和处理数据.

Elasticsearch SQL is an X-Pack component that allows SQL-like queries to be executed in real-time against Elasticsearch. Whether using the REST interface, command-line or JDBC, any client can use SQL to search and aggregate data natively inside Elasticsearch. One can think of Elasticsearch SQL as a translator, one that understands both SQL and Elasticsearch and makes it easy to read and process data in real-time, at scale by leveraging Elasticsearch capabilities.

为什么要使用Elasticsearch SQL?

Why Elasticsearch SQL?

原生集成

Native integration

Elasticsearch SQL是为Elasticsearch特别定制的,每个查询都可以根据底层数据存储方式位置做出相应优化从而提高查询性能.

Elasticsearch SQL is built from the ground up for Elasticsearch. Each and every query is efficiently executed against the relevant nodes according to the underlying storage.

不需要额外投入

No external parts

使用Elasticsearch SQL不需要额外添加硬件、软件、特别处理,因为Ealsticsearch SQL是运行在集群内部的.

No need for additional hardware, processes, runtimes or libraries to query Elasticsearch; Elasticsearch SQL eliminates extra moving parts by running inside the Elasticsearch cluster.

轻量高性能

Lightweight and efficient

Elasticsearch SQL并不是对Elasticsearch和它查询功能的二次封装抽象，相反Elasticsearch也是很热情的去支持使用SQL以同样声明性、简洁的方式进行近实时的全文检索.

Elasticsearch SQL does not abstract Elasticsearch and its search capabilities - on the contrary, it embraces and exposes SQL to allow proper full-text search, in real-time, in the same declarative, succinct fashion.

2.开始使用SQL

Getting Started with SQL

为了尝试使用SQL,新建一个索引并添加点数据用于演示:

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

PUT /library/book/_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}

创建好演示数据就可以使用REST API进行SQL查询了, 就像这样:

And now you can execute SQL using the SQL REST API right away:

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

也可以使用SQL命令行工具进行SQL查询，可以在x-pack的bin目录中找到并启动它:

You can also use the SQL CLI. There is a script to start it shipped in x-pack’s bin directory:

$ ./bin/elasticsearch-sql-cli

启动后，可以像这样执行跟上面一样的SQL:

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

3.约定和术语

Conventions and Terminology

理解某些词真正表达的意思有助于更好的理解文档内容，对有些词大家理解的意思可能不一样，这主要取决于我们的知识背景不同对SQL、Elasticsearch的熟悉程度不同.

For clarity, it is important to establish the meaning behind certain words as, the same wording might convey different meanings to different readers depending on one’s familiarity with SQL versus Elasticsearch.

一般来说，Elasticsearch SQL就像它的名字一样是为Elasticsearch提供SQL交互支持，所以会优先使用SQL的术语和约定，但毕竟底层的支持引擎是Elasticsearch难免有些地方不能很好的兼容，可能会出现一些特定的术语。这也没办法不是，毕竟需要有所取舍，但我们会尽量少的引入新的术语和约定.

As a general rule, Elasticsearch SQL as the name indicates provides a SQL interface to Elasticsearch. As such, it follows the SQL terminology and conventions first, whenever possible. However the backing engine itself is Elasticsearch for which Elasticsearch SQL was purposely created hence why features or concepts that are not available, or cannot be mapped correctly, in SQL appear in Elasticsearch SQL. Last but not least, Elasticsearch SQL tries to obey the principle of least surprise, though as all things in the world, everything is relative.

持续更新中...欢迎关注