ES6.3发布之后，带来了很多新特性，https://www.elastic.co/blog/elasticsearch-6-3-0-released
其中最大的亮点，莫过于内置支持SQL模块。虽然它目前支持的SQL语法和功能非常有限，但还是迫不及待的尝试了一下。
试验中，我在单机上安装了3个实例的集群，es版本为最新的6.3.1.
单机es集群安装配置
## 下载和解压
wget https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-6.3.1.tar.gz
tar -xzvf elasticsearch-6.3.1.tar.gz
## 复制和修改目录
mv elasticsearch-6.3.1 es_node1
cp -r es_node1/ es_node2/
cp -r es_node1/ es_node3/
[liuxiaowen@test01v ~/es]$ pwd /home/liuxiaowen/es [liuxiaowen@test01v ~/es]$ ll total 89300 -rw-rw-r-- 1 liuxiaowen liuxiaowen 91429350 Jul 5 21:34 elasticsearch-6.3.1.tar.gz drwxr-xr-x 8 liuxiaowen liuxiaowen 4096 Jun 30 06:04 es_node1 drwxr-xr-x 8 liuxiaowen liuxiaowen 4096 Aug 17 11:04 es_node2 drwxr-xr-x 8 liuxiaowen liuxiaowen 4096 Aug 17 11:04 es_node3
## 创建数据目录
mkdir -p /home/liuxiaowen/es/es_node1/data
mkdir -p /home/liuxiaowen/es/es_node2/data
mkdir -p /home/liuxiaowen/es/es_node3/data
## 修改es_node1配置
cd /home/liuxiaowen/es/es_node1/config
vi?elasticsearch.yml
cluster.name: lxw_es node.name: es_node1 node.master: true network.host: 0.0.0.0 network.bind_host: 0.0.0.0 path.data: /home/liuxiaowen/es/es_node1/data path.logs: /home/liuxiaowen/es/es_node1/logs http.port: 9391 #对外访问 transport.tcp.port: 9301 #各节点通信 discovery.zen.ping.unicast.hosts: ["192.168.1.228:9301","192.168.1.228:9302","192.168.1.228:9303"] #分别为各节点通信端口
## 修改es_node2配置
cluster.name: lxw_es node.name: es_node2 node.master: true network.host: 0.0.0.0 network.bind_host: 0.0.0.0 path.data: /home/liuxiaowen/es/es_node2/data path.logs: /home/liuxiaowen/es/es_node2/logs http.port: 9392 #对外访问 transport.tcp.port: 9302 #各节点通信 discovery.zen.ping.unicast.hosts: ["192.168.1.228:9301","192.168.1.228:9302","192.168.1.228:9303"] #分别为各节点通信端口
## 修改es_node3配置
cluster.name: lxw_es node.name: es_node3 node.master: true network.host: 0.0.0.0 network.bind_host: 0.0.0.0 path.data: /home/liuxiaowen/es/es_node3/data path.logs: /home/liuxiaowen/es/es_node3/logs http.port: 9393 #对外访问 transport.tcp.port: 9303 #各节点通信 discovery.zen.ping.unicast.hosts: ["192.168.1.228:9301","192.168.1.228:9302","192.168.1.228:9303"] #分别为各节点通信端口
## 分别修改三个节点分配内存大小：
cd /home/liuxiaowen/es/es_node[n]/config
vi jvm.options
-Xms3g
-Xmx3g
### 修改系统限制
root用户：
查看限制：ulimit -Hn
修改限制：vi /etc/security/limits.conf
在最后面添加：
liuxiaowen soft nofile 65536
liuxiaowen hard nofile 65536
其中，liuxiaowen是运行es的用户。
退出liuxiaowen用户重新登录，查看限制，已经是65536.
[2]: max virtual memory areas vm.max_map_count [65530] is too low, increase to at least [262144]
root用户：vi /etc/sysctl.conf
添加配置：vm.max_map_count=655360
保存，并执行命令使其生效：sysctl -p
## 分别启动三个节点
cd /home/liuxiaowen/es/es_node[n]/bin
./elasticsearch -d
启动后，可在/home/liuxiaowen/es/es_node[n]/logs下面查看日志：lxw_es.log
## 查看集群状态：
curl -XGET 'http://192.168.1.228:9391/_cluster/health?pretty'
{
"cluster_name" : "lxw_es",
"status" : "green",
"timed_out" : false,
"number_of_nodes" : 3,
"number_of_data_nodes" : 3,
"active_primary_shards" : 5,
"active_shards" : 10,
"relocating_shards" : 0,
"initializing_shards" : 0,
"unassigned_shards" : 0,
"delayed_unassigned_shards" : 0,
"number_of_pending_tasks" : 0,
"number_of_in_flight_fetch" : 0,
"task_max_waiting_in_queue_millis" : 0,
"active_shards_percent_as_number" : 100.0
}
体验SQL模块
## 插入测试数据：
curl -X PUT "192.168.1.228:9391/library/book/_bulk?refresh" -H 'Content-Type: application/json' -d'
{"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命令行：
cd /home/liuxiaowen/es/es_node1/bin
./elasticsearch-sql-cli 192.168.1.228:9391
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
## 支持的SQL语法
DESCRIBE TABLE
SELECT
SHOW COLUMNS
SHOW FUNCTIONS
SHOW TABLES
## 内置函数
sql> SHOW FUNCTIONS;
name | type
----------------+---------------
AVG |AGGREGATE
COUNT |AGGREGATE
MAX |AGGREGATE
MIN |AGGREGATE
SUM |AGGREGATE
STDDEV_POP |AGGREGATE
VAR_POP |AGGREGATE
PERCENTILE |AGGREGATE
PERCENTILE_RANK |AGGREGATE
SUM_OF_SQUARES |AGGREGATE
SKEWNESS |AGGREGATE
KURTOSIS |AGGREGATE
DAY_OF_MONTH |SCALAR
DAY |SCALAR
DOM |SCALAR
DAY_OF_WEEK |SCALAR
DOW |SCALAR
DAY_OF_YEAR |SCALAR
DOY |SCALAR
HOUR_OF_DAY |SCALAR
HOUR |SCALAR
MINUTE_OF_DAY |SCALAR
MINUTE_OF_HOUR |SCALAR
MINUTE |SCALAR
SECOND_OF_MINUTE|SCALAR
SECOND |SCALAR
MONTH_OF_YEAR |SCALAR
MONTH |SCALAR
YEAR |SCALAR
WEEK_OF_YEAR |SCALAR
WEEK |SCALAR
ABS |SCALAR
ACOS |SCALAR
ASIN |SCALAR
ATAN |SCALAR
ATAN2 |SCALAR
CBRT |SCALAR
CEIL |SCALAR
CEILING |SCALAR
COS |SCALAR
COSH |SCALAR
COT |SCALAR
DEGREES |SCALAR
E |SCALAR
EXP |SCALAR
EXPM1 |SCALAR
FLOOR |SCALAR
LOG |SCALAR
LOG10 |SCALAR
MOD |SCALAR
PI |SCALAR
POWER |SCALAR
RADIANS |SCALAR
RANDOM |SCALAR
RAND |SCALAR
ROUND |SCALAR
SIGN |SCALAR
SIGNUM |SCALAR
SIN |SCALAR
SINH |SCALAR
SQRT |SCALAR
TAN |SCALAR
SCORE |SCORE
## SELECT语法支持的很有限
SELECT select_expr [, ...] [ FROM table_name ] [ WHERE condition ] [ GROUP BY grouping_element [, ...] ] [ HAVING condition] [ ORDER BY expression [ ASC | DESC ] [, ...] ] [ LIMIT [ count ] ]
## 除了命令行，也可以使用Rest API来进行SQL查询
curl -X POST "192.168.1.228:9391/_xpack/sql?format=txt" -H 'Content-Type: application/json' -d'
{
"query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
}
'
author | name | page_count | release_date
----------------+---------------+---------------+------------------------
Frank Herbert |Dune |604 |1965-06-01T00:00:00.000Z
James S.A. Corey|Leviathan Wakes|561 |2011-06-02T00:00:00.000Z
Dan Simmons |Hyperion |482 |1989-05-26T00:00:00.000Z
另外还可以使用JDBC来执行SQL查询。
我们使用es，不仅为了快速检索，另外还有一些汇总分析的需求，使用SQL来完成，会更加方便。
如果觉得本博客对您有帮助，请 赞助作者 。
