1. 环境准备
1.1. 集群环境
阿里云ElasticSearch V6.7.0
1.2. 数据准备
1.2.1. 索引创建
PUT /products_info { "mappings": { "products": { "properties": { "productName": { "type": "text", "analyzer": "ik_smart" }, "annual_rate":{ "type":"keyword" }, "describe": { "type": "text", "analyzer": "ik_smart" }, "product_age":{ "type":"integer" } } } } }
1.2.2. 数据导入
POST /products_info/products/_bulk {"index":{}} {"productName":"理财产品A","annual_rate":"3.2200%","describe":"180天定期理财，最低20000起投，收益稳定，可以自助选择消息推送","product_age":0} {"index":{}} {"productName":"理财产品B","annual_rate":"3.1100%","describe":"90天定投产品，最低10000起投，每天收益到账消息推送","product_age":2} {"index":{}} {"productName":"理财产品C","annual_rate":"3.3500%","describe":"270天定投产品，最低40000起投，每天收益立即到账消息推送","product_age":2} {"index":{}} {"productName":"理财产品D","annual_rate":"3.1200%","describe":"90天定投产品，最低12000起投，每天收益到账消息推送","product_age":1} {"index":{}} {"productName":"理财产品E","annual_rate":"3.0100%","describe":"30天定投产品推荐，最低8000起投，每天收益会消息推送","product_age":5} {"index":{}} {"productName":"理财产品F","annual_rate":"2.7500%","describe":"热门短期产品，3天短期，无须任何手续费用，最低500起投，通过短信提示获取收益消息","product_age":11}
1.3. 自定义/开源插件安装流程
- 参考阿里云官方文档：https://help.aliyun.com/zh/es/user-guide/upload-and-install-a-custom-plug-in?spm=a2c4g.11186623.0.i8
- 在集群详细信息配置界面依次点击如下按钮后，集群将处于生效中的变更状态，变更完成后即可使用
- 配置与管理
- 插件配置
- 自定义插件列表
- 上传
- 上传本地插件zip压缩包文件
- 上传安装
2. SQL插件
2.1. _sql summary
插件名称：NLPchina/elasticsearch-sql
参考开源地址：https://github.com/NLPchina/elasticsearch-sql/releases/tag/6.7.0.0
2.2. 使用与测试
版本不同GET请求的路径也有变更，目前我们使用的6.7.0版本需使用/_sql及/_sql/_explain
2.2.1. _sql查询
返回结果与DSL查询返回形式一致
GET _sql { "sql":"select * from products_info where product_age > 0 limit 1,10" }
2.2.2. _sql/_explain转换DSL
GET _sql/_explain { "sql":"select * from products_info where product_age > 0 limit 1,10" }
{ "from": 1, "size": 10, "query": { "bool": { "filter": [ { "bool": { "must": [ { "range": { "product_age": { "from": 0, "to": null, "include_lower": false, "include_upper": true, "boost": 1.0 } } } ], "adjust_pure_negative": true, "boost": 1.0 } } ], "adjust_pure_negative": true, "boost": 1.0 } } }
2.2.3. select tpye
在不同业务使用场景中，历史业务逻辑存在对{index}/{_type}的使用，该开源插件支持识别并查询该场景下数据
GET _sql { "sql":"select * from products_info/products where product_age > 0 limit 1,10" }
2.3. 对比aliyun-sql
2.3.1. 安装与开启
aliyun-sql默认安装在阿里云ElasticSearch集群插件中，需要通过如下命令开启使用，否则会报异常：
{ "error": { "root_cause": [ { "type": "access_denied_exception", "reason": "sql plugin is disabled!" } ], "type": "access_denied_exception", "reason": "sql plugin is disabled!" }, "status": 500 }
PUT _cluster/settings { "transient": { "aliyun.sql.enabled": true } }
2.3.2. _alisql查询
- GET请求路径不同插件有所不同，如果冲突的话需要按需卸载或者disable
- body请求体的key不同，_alisql为"query"
GET /_alisql { "query":"select * from products_info where product_age > 0 limit 1,10" }
2.3.3. _alisql/transalte转换DSL
GET /_alisql/translate { "query":"select * from products_info where product_age > 0 limit 1,10" }
2.3.4. 不支持/_type的用法..?
- 部分用户从自建/其他云厂商迁移到阿里云ElasticSearch，从_sql切换到_alisql，使用select type的语法暂不支持
GET /_alisql/translate { "query":"select * from products_info/products where product_age > 0 limit 1,10" }
{ "error": { "root_cause": [ { "type": "sql_parse_exception", "reason": "Encountered \" \"/\" \"/ \"\" at line 1, column 28.\nWas expecting:\n <EOF> \n " } ], "type": "sql_parse_exception", "reason": "Encountered \" \"/\" \"/ \"\" at line 1, column 28.\nWas expecting:\n <EOF> \n ", "caused_by": { "type": "parse_exception", "reason": "Encountered \" \"/\" \"/ \"\" at line 1, column 28.\nWas expecting:\n <EOF> \n " } }, "status": 500 }
- 部分同学调整语法后发现可以正常运行，表现在替换"/"为"//"。但发现查询结果不符合预期。
GET /_alisql/translate { "query":"select * from products_info//products where product_age > 0 limit 1,10" }
- 经过查看SQL转换成的DSL可以看出，alisql应该是将"//"视为了注释，后面的where查询，limit翻页，均没有在DSL中有所体现
- alisql在官网文档中查看并未开源，使用教程中也并未提到相关的语法，如有相关语句修改后正常执行的方案请各位大佬私聊指导
2.3.5. 不支持索引名中含有"-"..?
- 创建索引，索引名中含有"-"
PUT /products_info-1 { "mappings": { "products": { "properties": { "productName": { "type": "text", "analyzer": "ik_smart" }, "annual_rate":{ "type":"keyword" }, "describe": { "type": "text", "analyzer": "ik_smart" }, "product_age":{ "type":"integer" } } } } }
- 使用开源插件_sql和_alisql查询，_alisql异常如下
{ "error": { "root_cause": [ { "type": "sql_parse_exception", "reason": "Encountered \" \"-\" \"- \"\" at line 1, column 28.\nWas expecting:\n <EOF> \n " } ], "type": "sql_parse_exception", "reason": "Encountered \" \"-\" \"- \"\" at line 1, column 28.\nWas expecting:\n <EOF> \n ", "caused_by": { "type": "parse_exception", "reason": "Encountered \" \"-\" \"- \"\" at line 1, column 28.\nWas expecting:\n <EOF> \n " } }, "status": 500 }
- alisql在官网文档中查看并未开源，使用教程中也并未提到相关的语法，如有相关语句修改后正常执行的方案请各位大佬私聊指导
3. 繁体/简体转换插件
3.1. summary
插件名称：medcl/elasticsearch-analysis-stconvert
参考开源地址：https://github.com/medcl/elasticsearch-analysis-stconvert/releases/tag/v6.7.0
3.2. 使用与测试
我们使用analyze的API对该analysis的插件进行使用测试。
analyze的API参考：https://www.elastic.co/guide/en/elasticsearch/reference/6.7/indices-analyze.html#indices-analyze。
- 该插件提供的analyzer、tokenizer、filter（原：token-filter）、char-filter默认为简体转为繁体，即Traditional->Simplified
GET /_analyze { "text": ["我爱北京天安门Yeah"] , "tokenizer": "stconvert" }
- 需求为繁体转换为简体，将默认的convert_type从「s2t」修改为「t2s」，并在filter中转换为小写
GET /_analyze { "text": ["我愛北京天安門Yeah"] ,"tokenizer": { "type":"stconvert" ,"convert_type" : "t2s" } ,"filter": ["lowercase"] }
- 也可以使用指定索引analyzer的方式自定义为繁体转简体。设置convert索引的analyzer为test_convert，并在stconvert的基础上指定convert_type为t2s。
DELETE /convert PUT /convert { "settings": { "analysis": { "analyzer": { "test_convert":{ "type":"stconvert", "convert_type":"t2s" } } } } } GET /convert/_analyze { "text": ["我愛北京天安門Yeah"] ,"analyzer": "test_convert" }
