阿里云ODPS：大规模离线数据分析处理-技术开发专区

阿里云ODPS：大规模离线数据分析处理

作者：蔡文志汤子楠编辑：王玉圆 2012-02-06 10:02 来源：程序员

　　自动定期分析

　　至此，我们已完成一次数据分析操作：从OTS导入原始数据到ODPS中，进行数据分析操作，再将结果数据导出到OTS供查询。但这个过程是手工完成的，如果希望“十大餐馆”的数据每天更新一次，那么有没有什么办法可以自动完成上面的操作呢?

　　我们将上面的流程整理一下，形成一个SQL命令脚本，保存到CalTopTenRestaurants.sql中：

　　DROP TABLE RestaurantInfo;

　　DROP TABLE RestaurantComment;

　　CREATE TABLE RestaurantInfo AS SELECT * FROM OTS.RestaurantInfo;

　　CREATE TABLE RestaurantComment AS SELECT * FROM OTS.RestaurantComment;

　　CREATE TABLE IF NOT EXISTS TopTenRestaurants (DistrictID INT, RestaurantID INT, Name STRING, Address STRING, PhoneNumber STRING, Location STRING, Category STRING, ScoreStat INT, CostStat INT);

　　INSERT OVERWRITE TABLE TopTenRestaurants SELECT a.DistrictID, a.RestaurantID, a.Name, a.Address, a.PhoneNumber, a.Location, a.Category, b.ScoreStat, b.CostStat FROM RestaurantInfo a JOIN (SELECT DistrictID, RestaurantID, AVG(Score) AS ScoreStat, AVG(Cost) AS CostStat FROM RestaurantComment GROUP BY DistrictID, RestaurantID) b ON a.DistrictID = b. DistrictID AND a.RestaurantID = b.RestaurantID ORDER BY b.ScoreStat DESC LIMIT 10;

　　EXPORT DATA TO OTS TopTenRestaurants FROM TABLE TopTenRestaurants;

　　为了定期执行上面的SQL命令脚本，我们在ODPS中创建一个作业(QUERY)【注：由用户定义的一系列串行执行的SQL命令，保存在ODPS中，用来对ODPS中的表完成某个特定的数据分析操作】：

　　$ odpscmd CREATE QUERY CalTopTenRestaurants FROM

　　./CalTopTenRestaurants.sql;

　　CalTopTenRestaurants这个作业包含了计算“十大餐馆”的全部数据分析操作，我们需要这个作业每天运行一次，在odpscmd中进行如下设置：

　　$ odpscmd SET QUERY CalTopTenRestaurants EXECTIME=00:00:00 EVERY DAY;

　　好了，现在这个作业会从设置的第二天零点开始，每天执行一次。你的前端应用程序可以直接在OTS中访问结果表数据，ODPS会在后台自动完成作业的运行和监控，并会在出现问题时，通过ODPS管理中心向你发出警报。你可以随时查询这个作业的状态，了解它的上次运行时间、总运行次数等信息。

　　$ odpscmd DESCRIBE QUERY TopTenRestaurants;

　　构造复杂的数据挖掘任务

　　利用上面的流程，你可以完成很多简单的数据分析操作，让你的网站能够为客户提供更丰富的服务;但你的数据蕴含的价值远不止如此。下面再来试个稍微复杂一点的例子：你的用户在网站上发表的点评暗示了他/她的消费轨迹和偏好，我们希望通过分析用户的点评数据，为他/她推荐喜爱的餐馆。

　　首先需要做点准备工作。在《借助OTS快速构建LBS服务》一文中没有提及用户信息如何保存，由于后续的计算需要用到用户数据，不妨假设你的用户信息都存放在表4中。

自动定期分析
▲表4 UserInfo表存储用户信息

　　然后我们来分析如何向用户推荐他/她喜欢的餐馆。

　　首先将OTS中的原始数据表导入到ODPS中。

　　DROP TABLE RestaurantInfo; #(1)

　　CREATE TABLE RestaurantInfo AS SELECT * FROM OTS.RestaurantInfo; #(2)

　　DROP TABLE RestaurantComment; #(3)

　　CREATE TABLE RestaurantComment AS SELECT * FROM OTS.RestaurantComment; #(4)

　　DROP TABLE UserInfo; #(5)

　　CREATE TABLE UserInfo AS SELECT * FROM OTS.UserInfo; #(6)

　　在上一节中计算的结果依然有用：首先得到所有餐馆的用户口碑，接下来可以计算每类餐馆中口碑最好的一家。

　　DROP TABLE RestaurantStat; #(7)

　　CREATE TABLE RestaurantStat AS SELECT a.DistrictID, a.RestaurantID, a.Name, a.Address, a.PhoneNumber, a.Location, a.Category, b.ScoreStat, b.CostStat FROM RestaurantInfo a JOIN (SELECT DistrictID, RestaurantID, AVG(Score) AS ScoreStat, AVG(Cost) AS CostStat FROM RestaurantComment GROUP BY DistrictID, RestaurantID) b ON a.DistrictID = b. DistrictID AND a.RestaurantID = b.RestaurantID; #(8)

　　DROP TABLE BestRestaurantInCategory; #(9)

　　CREATE TABLE BestRestaurantInCategory AS SELECT Category, DistrictID, RestaurantID, Name, Address, PhoneNumber, Location, MAX(ScoreStat) AS BestScoreStat, CostStat FROM RestaurantStat GROUP BY Category; #(10)

　　找到每个用户最后发表点评的餐馆，假设他/她刚刚去过那家餐馆，并且比较喜欢那个餐馆所在类别的餐馆;计算这些餐厅所属的类别。

　　DROP TABLE UserLatestCategory; #(11)CREATE TABLE UserLatestCategory AS SELECT a.UserName, b.Category FROM (SELECT DistrictID, RestaurantID, MAX(DateTime) AS LatestDateTime, UserName FROM RestaurantComment GROUP BY UserName) a JOIN RestaurantInfo b ON a.DistrictID = b.DistrictID AND a.RestaurantID = b.RestaurantID; #(12)

　　向用户推荐由#3得到的那类餐馆中口碑最好的一家。

　　#计算为每位用户推荐的餐厅

　　DROP TABLE UserRecommandedRestaurant; #(13)

　　CREATE TABLE UserRecommandedRestaurant AS SELECT a.UserId, a.UserName, b.Category, c.Name, c.Address, c.PhoneNumber, c.Location, c.BestScoreStat, c.CostStat FROM UserInfo a LEFT OUTER JOIN UserLatestCategory b ON a.UserName = b.UserName LEFT OUTER JOIN BestRestaurantInCategory c ON b.Category = c.Category; #(14)

　　将结果表中的数据导出到OTS中供网站应用访问。

　　EXPORT DATA TO OTS UserRecommandedRestaurant FROM TABLE UserRecommandedRestaurant; #(15)

　　我们可以将上述SQL命令保存为一个作业，提交给ODPS服务执行。但这样一个作业中的所有SQL命令是顺序执行的，对于RestaurantComment和UserInfo这类含有千万甚至亿条数据的表而言，执行GROUP BY和MULTI-JOIN操作需要花费大量时间。如果数据模型的复杂度提升，时间还会进一步增加，难以满足数据分析对时效性的要求。有什么好办法吗?通过观察不难发现，上述15个SQL语句之间是有依赖关系的，其依赖关系可以构成一张有向无环图，如图1所示。每个节点的编号与SQL语句后面的编号一一对应。

自动定期分析
▲图1 计算向用户推荐的餐馆的SQL语句的依赖关系

　　ODPS支持按作业的依赖关系并发调度作业，因此，我们根据这15个SQL语句的依赖关系将它们整理成如下作业(冒号前面是作业名称，冒号后面是该作业包含的SQL语句，此处省略了在odpscmd中定义作业的命令)：

　　QUERY GenRestaurantInfo: 1, 2

　　QUERY GenRestaurantComment: 3, 4

　　QUERY GenUserInfo: 5, 6

　　QUERY CalBestRestaurantInCategory: 7, 8, 9, 10

　　QUERY CalUserLatestCategory: 11, 12

　　QUERY CalUserRecommandedRestaurant: 13, 14

　　QUERY Finish: 15

　　定义好作业后，我们可以在ODPS中定义一个工作流【注：由用户定义的作业集合，集合中的作业满足特定的执行依赖关系。利用ODPS的并发调度功能，使用工作流可以更高效地执行多个作业】。这个工作流包括上述全部作业以及它们之间的依赖关系，通过执行这个工作流，可以完成向用户推荐餐馆的全部数据分析操作。

　　$ odpscmd CREATE WORKFLOW CalUserRecommandedRestaurant { GenRestaurantInfo -> CalBestRestaurantInCategory, GenRestaurantInfo -> CalUserLatestCategory, GenRestaurantComment -> CalBestRestaurantInCategory, GenRestaurantComment -> CalUserLatestCategory, CalBestRestaurantInCategory -> CalUserRecommandedRestaurant, CalUserLatestCategory -> CalUserRecommandedRestaurant, GenUserInfo -> CalUserRecommandedRestaurant, CalUserRecommandedRestaurant};

　　工作流CalUserRecommandedRestaurant定义的作业之间的依赖关系如图2所示。

自动定期分析
▲图2 计算向用户推荐餐馆的作业的依赖关系

　　定义好工作流之后，就可以像执行作业一样执行它了。我们现在就来计算为每个用户推荐的最喜欢的餐厅，并将结果表导出到OTS。或者，你也可以为该工作流定义执行计划，这需要综合考虑用户的登录频率和该工作流的执行时间。

　　$ odpscmd EXEC WORKFLOW UserRecommandedRestaurant;

　　后记

　　ODPS拥有出色的数据离线处理能力，并提供了丰富的命令让用户可以灵活地管理海量数据和多个并发计算作业，以帮助用户快速构建复杂的数据分析和数据挖掘应用。借助于数据分片存储和分片计算等分布式数据处理技术，ODPS可以游刃有余地处理TB甚至PB级别的数据分析任务。除了提供丰富的数据导入/导出命令外，ODPS还与阿里云的其他服务进行了深度集成，允许用户轻松地将在其他服务中沉淀的数据导入到ODPS中进行分析和处理，并回流到原服务中供其他应用访问。

　　拥有出色的处理能力仅仅是ODPS的冰山一角，未来我们将会把业务伙伴在ODPS上沉淀的数据开放出来，供更多的组织机构使用，让ODPS成为一个数据分享和数据分析的开放式服务平台，同时满足数据生产者、加工者和消费者等多方面的需求。