金融豆豆
探索云世界
金融豆豆
暂无个人介绍
提交了问题
2018-04-04
Join是MaxCompute中最基本的语法，但由于数据量和倾斜问题，非常容易出现性能问题。一般情况下，join产生的问题有两大类： 数据倾斜问题：join会将key相同的数据分发到同一个instance上处理，如果某个key上的数据量特别多则会导致该instance处理时间比其他instance处理时间长，这就是我们常说的数据倾斜，这也是join计算性能问题的罪魁祸首； 数据量问题：关联的两表基本没有热点问题，但两个表数据量都非常大同样会影响性能，比如记录数达几十亿条，如商品表、库存表等； 虽然MaxCompute中提供了一些通用的优化算法，但从业务角度解决性能问题往往更精确，更有效。对于MaxCompute sql优化，在云栖社区上已经有比较多的经验积累，本文主要对join产生的性能问题以及解法做些总结。 不同数据类型key关联 例子 浏览IPV日志以商品id关联商品表，假设日志表的商品id字段是string类型，商品表中的商品id是bigint类型，那么在关联中，关联key会全部转换成double类型进行比较，设想由于埋点问题日志表中的商品id存在很多非数值的脏数据，那么转换成double后值都变为NULL或者截取前面的数值，关联时就会产生数据倾斜问题，更严重的会造成数据错误。 解法 关联时手工进行数据格式转换，在这种情况下一般将bigint类型key转换成string类型。 select a.* from ipv_log_table a left outer join item_table b on a.item_id = cast(b.item_id as string) 思考下，假如反过来将string类型转换成bigint、假如IPV日志表中的商品id大部分为无效值（比如0）、又假如IPV日志表中没有无效值但是有热点key会有什么问题呢？下面的例子会解答这些问题。 小表join大表 Join中存在小表，一般这个小表在100M以内，可以用mapjoin，避免分发引起的长尾。拿上面的例子来说，假如商品表数据量只有几万条记录（这里只是打个比方，现实业务中商品表一般都是非常庞大的），但是IPV日志表中的商品id 80%值为0的无效值，且记录数有几十亿，如果采用上述SQL写法，数据倾斜是显而易见的，但利用mapjoin可以有效解决这个问题： select /*+ MAPJOIN(b) */a.* from ipv_log_table a left outer join item_table b on a.item_id = cast(b.item_id as string) mapjoin原理 把小表广播传递到所有的Join Task Instance上面，然后直接和大表做Hash Join，简单的说就是将join操作提前到map端，而非reduce端。 mapjoin使用注意点 小表在left outer join 时只能是右表， right outer join 时只能是左表， inner join 时无限制，full outer join不支持mapjoin； mapjoin最多只支持8张小表，否则会报语法错误； mapjoin所有小表内存限制不能超过2GB，默认为512M； mapjoin支持小表为子查询； mapjoin支持不等值连接或者使用or连接多个条件； 大表join大表存在无效值 在小表join大表时我们已经了解到通过mapjoin将小表全部加载到map端可以解决倾斜问题，但假如‘小表‘不够小，mapjoin失效的时候该怎么办呢？同样以本文第一个场景为例，IPV日志表中80%商品id都为无效值0（目前MaxCompute底层已经针对NULL值进行优化，已经不存在倾斜问题了），这时关联十几亿量级商品表那就是个灾难。 解法1-分而治之： 我们可以事先知道无效值是不可能关联出结果的，而且完全不需要参与关联，所以可以将无效值与有效值数据分开处理： select a.visitor_id ,b.seller_id from ( select from ipv_log_table where item_id > 0 ) a left outer join item_table b on a.item_id = b.item_id union all select a.visitor_id ,cast(null as bigint) seller_id from ipv_log_table where item_id = 0 解法2-随机值打散： 我们也可以以随机值代替NULL值作为join的key，这样就从原来一个reduce来处理倾斜数据变成多reduce并行处理，因为无效值不参与关联，即使分发到不同reduce，也不会影响最终计算结果： select a.visitor_id ,b.seller_id from ipv_log_table a left outer join item_table b on if(a.item_id > 0, cast(a.item_id as string), concat('rand',cast(rand() as string))) = cast(b.item_id as string) 解法3-转化为mapjoin： 虽然商品表有十几亿条记录，不能直接通过mapjoin来处理，但在实际业务中，我们知道一天内用户访问的商品数是有限的，在业务中尤为明显，基于此我们可以通过一些处理转换成mapjoin： select /*+ MAPJOIN(b) */ a.visitor_id ,b.seller_id from ipv_log_table a left outer join ( select /*+ MAPJOIN(log) */ itm.seller_id ,itm.item_id from ( select item_id from ipv_log_table where item_id > 0 group by item_id ) log join item_table itm on log.item_id = itm.item_id ) b on a.item_id = b.item_id 解法对比 解法1和解法2是通用解决方案，对于解法1，日志表被读取两次，而解法2中只需读取一次，另外任务数解法2也是少于解法1的，所以总的来看解法2是优于解法1的。解法3是基于一定的假设，随着业务发展或者某些特殊情况下假设可能失效（比如一些爬虫日志，可造成访问商品数接近全量），这会导致mapjoin失效，所以在使用过程中要根据具体情况来评估。 一个古老的例子 最后要讲一个古老的优化case，虽然历史比较久远，目前已没有相关问题，但优化思路值得借鉴。情况是这样的，历史上并存过两套商品维表，一份主键是字符串id，新的商品表也就是目前在使用的主键是数字id，字符串id和数字id做了映射，存在商品表中的两个字段中，所以在使用中需要分别过滤数字id、字符串id然后分别和商品表关联，最后union起来得到最终结果。 思考下如果换成下面的优化思路是不是更优呢？ select ... from ipv_log_table a join ( select auction_id as auction_id from auctions union all select auction_string_id as auction_id from auctions where auction_string_id is not null ) b on a.auction_id = b.auction_id 答案是肯定的，可以看到优化后商品表读取从2次降为1次，IPV日志表同样，另外MR作业数也从2个变为1个。 总结 对于MaxCompute sql优化最有效的方式是从业务的角度切入，并能够将MaxCompute sql转化为mapreduce程序来解读。本文针对join中各种场景优化都做了一些梳理，现实情况很可能是上述多场景的组合，这时候就需要灵活运用相应的优化方法，举一反三。 作者：宋智
提交了问题
2017-10-26
提交了问题
2017-10-11
提交了问题
2017-09-05
提交了问题
2017-09-05
提交了问题
2017-08-14
阿里云大数据实验室时阿里云开发的一站式大数据教学实践和科研创新平台，提供创业创新大赛平台，为各行业用户提供简单易用的大数据真实环境，让数据价值触手可及。在阿里云大数据实验室中集成了MaxCompute。 作为一名初次使用MaxCompute的用户，我体会颇深。MaxCompute 开箱即用，拥有集成化的操作界面，你不必关心集群搭建、配置和运维工作。仅需简单的点击鼠标，几步操作，就可以在MaxCompute中上传数据，分析数据并得到分析结果。 作为一种快速、完全托管的 TB/PB 级数据仓库解决方案，MaxCompute不仅为我们提供了传统的命令行操作，而且提供了丰富的web端操作界面。对于数据开发，测试，发布，数据流，数据权限管理都非常好用，支持python，java的udf，对于复杂的逻辑查询也支持传统的MapReduce，同时也支持多种机器学习算法，非常好用。 MaxCompute为我们提供了统一的项目管理。实际开发中各个团队都有自己的项目,自己管理自己的项目, 通过项目隔离,有效的防止数据和任务被其他团队修改和删除等问题。除非是pro项目任务出错,否则不会影响到其他业务线的任务,最大程度降低各业务间的影响。 同时，大数据开发套件和 MaxCompute关系紧密，大数据开发套件为 MaxCompute 提供了一站式的数据同步，任务开发，数据工作流开发，数据管理和数据运维等功能。 当需要处理的数据变得非常多，并且数据发展到足够复杂的时候，这些数据往往需要用不同的模式进行处理，除此之外，当作业出现问题的时候也需要有足够好的工具进行分析和管理。这些其实都是对于开发者而言需要面对的问题，与此同时阿里云为我们提供了DATA IDE环境来解决上述的问题。 在进行数据分析的过程中DATA IDE可以支持SQL的语法以及语法的高亮，同时实现代码补全等功能。开发者即使对于SQL的语法并不熟悉，但是根据DATA IDE给出的关键词提示也可以轻松的进行操作。另外在DATA IDE中可以集成元数据，从而在开发人员进行SQL语句编写时，给予表格、字段以及函数的提示。 在任务执行过程中，我们通过IDE可以看到作业的执行状态以及详细信息，即便是作业出现问题IDE也可以很方便地对于问题进行定位，帮助开发者发现作业中哪一个是瓶颈。 MaxCompute还为我们提供了一个基于云计算的灵活的轻量级的自助 BI 工具服务平台-----Quick BI。 Quick BI可以支持多种数据源的添加，以便于我们对不同的数据源进行实时在线分析。 Quick BI为我们提供了丰富的可视化图表，根据行业的不同需求我们可以随意选取，以便于给决策曾提供更清晰准确的报表。而其基于 WEB 页面的工作环境，拖拽式、类似于 EXCEL 的操作方式，一键导入、实时分析，可以灵活切换数据分析的视角，更是锦上添花。 对于完成后的报表图表，可以以接口方式将制作的报表嵌入到第三方软件，从而方便自己的分析查询。 总之，无论是用来学习，还是用来工作MaxCompute都是一个不错的选择。时代在进步，科技在发展，大数据的脚步已经离我们越来越近，更多是知识需要我们去学习，去实践，赶快行动起来加入其中吧。 文章来源-北京青苔数据 阿里巴巴大数据-玩家社区 https://yq.aliyun.com/teams/6/ ---阿里大数据博文，问答，社群，实践，有朋自远方来，不亦说乎……
在维护客户基于MaxCompute搭建的数据仓库时，我们遇到过一些问题，踩过一些坑，同时积累了一些经验，也初步形成了一套操作流程规范，在这里与大家以Tip的形式与大家分享一下。 Tip1.避免同步视图 同步的源数据要避免使用视图，在客户的生产环境上曾经出现过这样的情况：由于生成视图的存储过程优化不好，同步视图在同步任务发起请求后很久没有生成出来，导致同步任务及后续的ETL挂起达数小时之久，所以后续和数据提供方接洽，将数据源从视图换为表，保证在同步之前同步表里的内容已经更新。 在测试环境下，通过修改数据集成中的同步任务实现数据源从视图切换成表。再确认不同的数据源的表数据结构完全一致之后，修改如下同步脚本中的table值为新的表名。 由于在同步脚本里truncate字段为true，所以再次同步数据时该分区内的旧数据会被擦除，在修改之后可以直接通过补数据节点得到新数据源里的数据，同时该节点之后的节点也需要重跑，确保所有任务数据的正确性、一致性。 核对数据也是保证修改前后数据正确性、一致性的办法之一。核对主要是与数据提供方核对数据。这里以验证销量数据为例，双方核对的标准如下 客户编码Code 客户姓名Name 年月 BILL_DATE 数据行数 销售数量PROD_QUANTITY 销售金额 PROD_AMOUNT 如果连续一周与数据提供方的数据一致，那么可以认为数据源修改成功 Tip2.Maxcompute数据同步到AnalyticDB AnalyticDB常常作为MaxCompute与QuickBI之前的数据加速层，那么MaxCompute到AnalyticDB的数据同步就是必须的。按照通常的数据集成操作来配置，我们可以建立一个数据同步任务，但是当我们尝试运行这个同步任务时就会发现任务运行失败。 起初在授权策略、网络方面查错，但是依旧没有解决问题。最终了解到MaxCompute到AnalyticDB的数据同步必须进行下面的操作： 由于使用的公有云，要将 garuda_build@aliyun.com 和 garuda_data@aliyun.com两个公用云账号添加到MaxCompute的工程中： ADD USER ALIYUN$garuda_build@aliyun.com; ADD USER ALIYUN$ garuda_data@aliyun.com 在添加账号之后需要将Describe,Select权限赋给这两个账号： GRANT Describe,Select ON TABLE rpt_outlet_report_daily_april TO USER ALIYUN$garuda_build@aliyun.com; GRANT Describe,Select ON TABLE rpt_outlet_report_daily_april TO USER ALIYUN$garuda_data@aliyun.com; 之后再运行这个同步任务，发现同步成功！ Tip3.数据删除 数据提供方对上个月的历史数据进行了修改，删除了部分不符合业务逻辑的数据，为保证数据一致性，我们也需要将这部分历史数据删除。由于数据同步是增量的，每天只同步变更日期大于{bdp.system.bizdate}的数据，并用这部分数据替换掉全量数据里的旧数据。数据更新的示例代码如下，其中dwd_dummy_data为全量基础表，存放全量数据；ods_dummy_data为增量同步表，存放今天的增量数据，ds为分区列 由于数据提供方已经没有不符合业务逻辑的数据，那么每日的同步数据一定是符合业务逻辑的，所以并不用做数据删除。那么需要删除的数据就在全量数据中，即示例中的dwd_dummy_data表中。从示例代码可以看出数据更新只使用到分区为两天前的全量数据，所以更新所有分区的的全量数据是没有必要的，但是同时也说明了数据删除操作必须在数据更新任务触发时间前完成，否则完成删除操作的数据全量数据并不会用于新一轮的数据更新。 在明确了这些注意点之后，我们在测试环境中对要进行操作的分区的数据进行备份，方便回滚。由于MaxCompute没有update操作，我们通过insert overwrite来实现数据的删除，下面以删除dwd_dummy_data表中col3小于1000的数据为例，实现的逻辑如下 注意在where中限定分区列ds，否则将会插入所有分区中符合条件的数据，产生重复数据。 同样的，数据删除之后也需要和数据提供方核对数据。一旦数据差异较大时，且问题无法短时定位时要及时回滚数据，即将备份的数据重新插入到操作的分区，同时保留操作时的脚本，用于和数据提供方核查数据删除逻辑。当确定没有修改ETL脚本时，重跑对应业务日期的任务也可以实现数据回滚。 在确定删除逻辑正确，数据可以核对上之后，就可以对生产环境上的数据进行操作。操作时需要通知客户操作的开始时间、预计的操作时长、操作的表、操作的逻辑，如果可能造成影响比较大，也要将可能的影响告知客户。在客户邮件同意之后方可进行操作。同样的，在测试环境操作时的一切准则在生产环境同样要遵守，在连续数据核对一周之后，如果数据没有差异那么操作完成，相关的操作、操作日期、操作人员需要记录下来。 Tip4.ETL脚本修改 ETL脚本修改的准则与数据删除进本类似，但要记得将修改前的脚本备份。另外，对脚本尽量小的改动。举例来说，比如要A字段的值由其他字段依据某些逻辑生成，后面改动时需要将A字段为特定值的数据过滤掉，我们可以选择在生成逻辑里面进行修改，但是这样可能对原来的逻辑产生影响，于是可以在不显著影响执行效率的情况下，考虑再加一层select查询，同时用where限定条件过滤掉数据。 修改完成之后，在脚本的头部注释上添加Modify信息，例如下图： 文章来源-五叶草 阿里巴巴大数据-玩家社区 https://yq.aliyun.com/teams/6/ ---阿里大数据博文，问答，社群，实践，有朋自远方来，不亦说乎……
发表了文章
2018-03-09
发表了文章
2017-07-31
发表了文章
2017-07-31
回答了问题
2019-07-17
语法问题，改为：add user ALIYUN$xx@aliyun.com
提交了问题
2018-04-04
提交了问题
2017-10-26
提交了问题
2017-10-11
提交了问题
2017-09-05
提交了问题
2017-09-05
回答了问题
2019-07-17
round(DOUBLE 字段/DOUBLE 字段)的时候也会出现此类问题，应该是round参数设置问题
提交了问题
2017-08-14
回答了问题
2019-07-17
