微信搜索lxw1234bigdata | 邀请体验:数阅–数据管理、OLAP分析与可视化平台 | 赞助作者:赞助作者

单机安装配置ElasticSearch6.3集群,体验es-sql

ElasticSearch lxw1234@qq.com 18894℃ 1评论

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来完成,会更加方便。

如果觉得本博客对您有帮助,请 赞助作者

转载请注明:lxw的大数据田地 » 单机安装配置ElasticSearch6.3集群,体验es-sql

喜欢 (33)
分享 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(1)个小伙伴在吐槽
  1. 学习了
    无名2019-06-13 21:36 回复