写点什么

大数据 ELK(十七):Elasticsearch SQL 订单统计分析案例

作者:Lansonli
  • 2022 年 10 月 08 日
    广东
  • 本文字数:3678 字

    阅读完需:约 12 分钟

大数据ELK(十七):Elasticsearch SQL 订单统计分析案例

订单统计分析案例


一、案例介绍

有以下数据集:



我们需要基于按数据,使用 Elasticsearch 中的聚合统计功能,实现一些指标统计。

二、创建索引

PUT /order_idx/{    "mappings": {        "properties": {            "id": {                "type": "keyword",                "store": true            },            "status": {                "type": "keyword",                "store": true            },            "pay_money": {                "type": "double",                "store": true            },            "payway": {                "type": "byte",                "store": true            },            "userid": {                "type": "keyword",                "store": true            },            "operation_date": {                "type": "date",                "format": "yyyy-MM-dd HH:mm:ss",                "store": true            },            "category": {                "type": "keyword",                "store": true            }        }    }}
复制代码

三、导入测试数据

  • 上传资料中的 order_data.json 数据文件到 Linux

  • 使用 bulk 进行批量导入命令


curl -H "Content-Type: application/json" -XPOST "node1:9200/order_idx/_bulk?pretty&refresh" --data-binary "@order_data.json"
复制代码

四、统计不同支付方式的的订单数量

1、​​​​​​​使用 JSON DSL 的方式来实现

这种方式就是用 Elasticsearch 原生支持的基于 JSON 的 DSL 方式来实现聚合统计。


GET /order_idx/_search{    "size": 0,    "aggs": {        "group_by_state": {            "terms": {                "field": "payway"            }        }    }}
复制代码


统计结果:


    "aggregations": {        "group_by_state": {            "doc_count_error_upper_bound": 0,            "sum_other_doc_count": 0,            "buckets": [                {                    "key": 2,                    "doc_count": 1496                },                {                    "key": 1,                    "doc_count": 1438                },                {                    "key": 3,                    "doc_count": 1183                },                {                    "key": 0,                    "doc_count": 883                }            ]        }    }
复制代码


这种方式分析起来比较麻烦,如果将来我们都是写这种方式来分析数据,简直是无法忍受。所以,Elasticsearch 想要进军 OLAP 领域,是一定要支持 SQL,能够使用 SQL 方式来进行统计和分析的

2、​​​​​​​基于 Elasticsearch SQL 方式实现

GET /_sql?format=txt{    "query": "select payway, count(*) as order_cnt from order_idx group by payway"}
复制代码


这种方式要更加直观、简洁

​​​​​​​五、基于 JDBC 方式统计不同方式的订单数量

Elasticsearch 中还提供了基于 JDBC 的方式来访问数据。我们可以像操作 MySQL 一样操作 Elasticsearch。使用步骤如下:


1、在 pom.xml 中添加以下镜像仓库


<repositories>  <repository>    <id>elastic.co</id>    <url>https://artifacts.elastic.co/maven</url>  </repository></repositories>
复制代码


2、导入 Elasticsearch JDBC 驱动 Maven 依赖


<dependency>  <groupId>org.elasticsearch.plugin</groupId>  <artifactId>x-pack-sql-jdbc</artifactId>  <version>7.6.1</version></dependency>
复制代码


3、驱动


org.elasticsearch.xpack.sql.jdbc.EsDriver


4、JDBC URL


jdbc:es:// http:// host:port


5、开启 X-pack 高阶功能试用,如果不开启试用,会报如下错误


current license is non-compliant for [jdbc]
复制代码


在 node1 节点上执行:


curl http://node1:9200/_license/start_trial?acknowledge=true -X POST{"acknowledged":true,"trial_was_started":true,"type":"trial"}
复制代码


试用期为 30 天


参考代码:


/** * 基于JDBC访问Elasticsearch */public class ElasticJdbc {
public static void main(String[] args) throws Exception { Class.forName("org.elasticsearch.xpack.sql.jdbc.EsDriver");
Connection connection = DriverManager.getConnection("jdbc:es://http://node1:9200"); PreparedStatement ps = connection.prepareStatement("select payway, count(*) as order_cnt from order_idx group by payway"); ResultSet resultSet = ps.executeQuery();
while(resultSet.next()) { int payway = resultSet.getInt("payway"); int order_cnt = resultSet.getInt("order_cnt"); System.out.println("支付方式: " + payway + " 订单数量: " + order_cnt); }
resultSet.close(); ps.close(); connection.close(); }}
复制代码


注意:如果在 IDEA 中无法下载依赖,请参考以下操作:


在 Idea 的 File -->settings 中,设置 Maven 的 importing 和 Runner 参数,忽略证书检查即可。(Eclipse 下解决原理类似,设置 maven 运行时参数),并尝试手动执行 Maven compile 执行编译。


具体参数:-Dmaven.multiModuleProjectDirectory=$MAVEN_HOME -Dmaven.wagon.http.ssl.insecure=true -Dmaven.wagon.http.ssl.allowall=true -Dmaven.wagon.http.ssl.ignore.validity.dates=true



六、统计不同支付方式订单数,并按照订单数量倒序排序

GET /_sql?format=txt{    "query": "select payway, count(*) as order_cnt from order_idx group by payway order by order_cnt desc"
复制代码

七、​​​​​​​只统计「已付款」状态的不同支付方式的订单数量

GET /_sql?format=txt{    "query": "select payway, count(*) as order_cnt from order_idx where status = '已付款' group by payway order by order_cnt desc"}
复制代码

八、统计不同状态的订单总额、不同支付方式最高、最低订单金额

统计不同状态的订单总额、不同支付方式最高、最低订单金额


GET /_sql?format=txt{    "query": "select userid, count(1) as cnt, sum(pay_money) as total_money from order_idx group by userid"}
复制代码

九、​​​​​​​Elasticsearch SQL 目前的一些限制

目前 Elasticsearch SQL 还存在一些限制。例如:不支持 JOIN、不支持较复杂的子查询。所以,有一些相对复杂一些的功能,还得借助于 DSL 方式来实现。

十、​​​​​​​常见问题处理

1、​​​​​​​elasticsearch.keystore  AccessDeniedException

Exception in thread "main" org.elasticsearch.bootstrap.BootstrapException: java.nio.file.AccessDeniedException: /export/server/es/elasticsearch-7.6.1/config/elasticsearch.keystoreLikely root cause: java.nio.file.AccessDeniedException: /export/server/es/elasticsearch-7.6.1/config/elasticsearch.keystore        at java.base/sun.nio.fs.UnixException.translateToIOException(UnixException.java:90)        at java.base/sun.nio.fs.UnixException.rethrowAsIOException(UnixException.java:111)        at java.base/sun.nio.fs.UnixException.rethrowAsIOException(UnixException.java:116)        at java.base/sun.nio.fs.UnixFileSystemProvider.newByteChannel(UnixFileSystemProvider.java:219)        at java.base/java.nio.file.Files.newByteChannel(Files.java:374)        at java.base/java.nio.file.Files.newByteChannel(Files.java:425)        at org.apache.lucene.store.SimpleFSDirectory.openInput(SimpleFSDirectory.java:77)        at org.elasticsearch.common.settings.KeyStoreWrapper.load(KeyStoreWrapper.java:219)        at org.elasticsearch.bootstrap.Bootstrap.loadSecureSettings(Bootstrap.java:234)        at org.elasticsearch.bootstrap.Bootstrap.init(Bootstrap.java:305)        at org.elasticsearch.bootstrap.Elasticsearch.init(Elasticsearch.java:170)        at org.elasticsearch.bootstrap.Elasticsearch.execute(Elasticsearch.java:161)        at org.elasticsearch.cli.EnvironmentAwareCommand.execute(EnvironmentAwareCommand.java:86)        at org.elasticsearch.cli.Command.mainWithoutErrorHandling(Command.java:125)        at org.elasticsearch.cli.Command.main(Command.java:90)        at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:126)        at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:92)
复制代码


解决方案:


将/export/server/es/elasticsearch-7.6.1/config/elasticsearch.keystore owner 设置为 lanson


chown lanson /export/server/es/elasticsearch-7.6.1/config/elasticsearch.keystore
复制代码


发布于: 2022 年 10 月 08 日阅读数: 23
用户头像

Lansonli

关注

微信公众号:三帮大数据 2022.07.12 加入

CSDN大数据领域博客专家,华为云享专家、阿里云专家博主、腾云先锋(TDP)核心成员、51CTO专家博主,全网六万多粉丝,知名互联网公司大数据高级开发工程师

评论

发布
暂无评论
大数据ELK(十七):Elasticsearch SQL 订单统计分析案例_10月月更_Lansonli_InfoQ写作社区