离线数据分析功能介绍

可能不少用户还不知道，我们上个月中旬就已经对外发布了离线数据分析功能。用户可以用它来做什么，它适用于哪些应用场景，有没有应用实例？若要回答这些问题，首先让我们看看部分用户曾经向我们提出过的一些痛点或需求。为了对用户数据保密，后面提到的应用名字与数据均为虚构。

• 某应用 TestA 发布几个月之后，由于用户量暴涨，存储在我们这里的数据规模急剧上涨。这就导致了一个不得不忽视的问题：他们在应用刚发布的那段时间会每天从我们这里导出数据，自己写程序做数据分析。而当数据规模（几十 GB）越来越大，受限于网络文件下载速度，每日导出数据再自己做分析已经变得越来越困难。因此，他们希望我们能提供数据分析服务，支持 SQL-like 的查询。
• 某应用 TestB 每天都会针对应用数据做一些分析报告，主要目的是知晓应用当前的关键数据指标。按照最初的方案，他们需要每日导出数据，自己写脚本程序处理应用数据。实际上，这样的工作对一个需要快速迭代产品的团队来说颇为繁琐，也比较消耗时间。
• 我们的 工单系统 也是构建在 Leancloud 之上，每一条工单数据都是通过我们的存储组件写入我们的云端数据库。工单系统同时也作为我们的一个示例公开了源代码，从源代码就可以了解到它和其他用户的应用没有本质区别（从数据存储这个角度看）。我们的工程师除了需要非常及时地处理用户提交的工单，也需要了解工单系统的一些统计指标，例如自己最近回复了多少工单、不同平台工单的处理速度等等。如果有一个支持 SQL-like 的查询服务，那么要统计这些指标就会变得很容易。

相信上面的例子对很多用户来讲都不陌生，它揭示的痛点无非是大量的数据无法通过便捷的手段进行分析。考虑到用户的需求，我们开发了支持 SQL-like 查询的离线数据分析服务。用户要做的不过是按照我们的 离线数据分析使用指南 开启离线数据分析服务，输入合法的 SQL 语句即可分析数据。顺便说一下，这个服务后端核心组件为 Spark SQL 与 Parquet。

接下来，就以我们的工单系统为例子，介绍如何利用离线数据分析服务来解决我们日常工作中遇到的一些问题。当然，为了更好地了解工单系统内部的结构，你可以访问 这里 的源代码。

首先让我们看看整个界面：

接下来就让我们针对一些统计指标分析工单系统数据：

• 统计不同平台类型（type）的工单数量，并按大小排序

  select count(*) as count, type from Ticket where type is not null group by type order by count desc

结果如图所示（如果图中文字不够清晰，请点击查看大图）：

• 统计每个工单（Ticket）各有多少条回复（Thread），并按回复数大小排序

  select Ticket.objectId, Ticket.title, count(Thread.content) as count from Ticket inner join Thread where Ticket.objectId=Thread.ticket.objectId group by Thread.content, Ticket.objectId, Ticket.title order by count desc

结果如图所示：

若想知晓 iOS 平台的每条工单各有多少条回复，只需把上面的 SQL 语句做简单修改：

select Ticket.objectId, Ticket.title, count(Thread.content) as `count` from Ticket inner join Thread where Ticket.objectId=Thread.ticket.objectId and Ticket.type='ios' group by Thread.content, Ticket.objectId, Ticket.title order by `count` desc

• 统计所有工程师最近一个多月处理了多少条工单（Ticket），并按处理数量排序。Admin、Thread、Ticket 这三张表参与了 join 计算，其中 Admin 存放了工程师的个人信息，它和回复（Thread）通过 cid 关联。而回复（Thread）与工单（Ticket）则是通过工单的 objectId 来做关联。

注：slackName 其实就是这个工程师的用户名，因为我们还使用 Slack 来实时接收消息。

select count(distinct Ticket.objectId) as `count`, Admin.slackName from Ticket inner join Thread inner join Admin where Thread.ticket.objectId = Ticket.objectId and Admin.cid = Thread.cid and Admin.slackName is not null and unix_timestamp(Ticket.createdAt) > unix_timestamp('2014-12-01T00:00:00.000Z') group by Admin.slackName order by `count` desc

结果如图所示：

• 统计所有工程师总共有多少次回复（Thread），并按回复数排序。同工单数的查询类似，这里也需要三张表做 join。

select count(distinct Thread.objectId) as count, Admin.slackName from Ticket inner join Thread inner join Admin where Thread.ticket.objectId = Ticket.objectId and Admin.cid = Thread.cid and Admin.slackName is not null group by Admin.slackName order by count desc

结果如图所示：

• 统计每天各有多少条回复（Thread）

substr(createdAt, 1, 10) 用来获取回复的日期（yyyy-MM-dd）select count(*) as count, substr(createdAt, 1, 10) as date from Thread group by substr(createdAt, 1, 10) order by date desc

结果如图所示：

考虑到本文篇幅有限，就不再列举其他查询示例。如果您在使用离线数据分析服务的过程中遇到各种各样的问题，都可以通过工单或者邮件向我们反馈，我们会及时处理相关问题。