祎休个人页面-阿里云开发者社区

Amazon Redshift数据迁移到MaxCompute

Amazon Redshift数据迁移到MaxCompute Amazon Redshift 中的数据迁移到MaxCompute中经常需要先卸载到S3中，再到阿里云对象存储OSS中，大数据计算服务MaxCompute然后再通过外部表的方式直接读取OSS中的数据。如下示意图：前提条件本文以SQL Workbench/J工具来连接Reshift进行案例演示，其中用了Reshift官方的Query editor发现经常报一些奇怪的错误。建议使用SQL Workbench/J。下载Amazon Redshift JDBC驱动程序，推荐4.2 https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/1.2.16.1027/RedshiftJDBC42-1.2.16.1027.jar 在SQL Workbench/J中新建Drivers，选择下载的驱动程序jar，并填写Classname为 com.amazon.redshift.jdbc42.Driver。配置新连接，选择新建的Driver，并复制JDBC url地址、数据库用户名和密码并勾选Autocommit。如果在配置过程中发现一只connection time out，需要在ecs的vpc安全组中配置安全策略。具体详见：https://docs.aws.amazon.com/zh_cn/redshift/latest/gsg/rs-gsg-authorize-cluster-access.html Amazon Redshift数据预览方式一：在AWS指定的query editor中进行数据预览，如下所示：方式二：使用Workbench/J进行数据预览，如下图所示：具体Workbench/J的下载和配置详见：https://docs.aws.amazon.com/zh_cn/redshift/latest/mgmt/connecting-using-workbench.html（下图为JDBC驱动下载和JDBC URL查看页面）卸载数据到Amazon S3 在卸载数据到S3之前一定要确保IAM权足够，否则如果您在运行 COPY、UNLOAD 或 CREATE LIBRARY 命令时收到错误消息 S3ServiceException: Access Denied，则您的集群对于 Amazon S3 没有适当的访问权限。如下：创建 IAM 角色以允许 Amazon Redshift 集群访问 S3服务 step1：进入https://console.aws.amazon.com/iam/home#/roles，创建role。 step2：选择Redshift服务，并选择Redshift-Customizable step3：搜索策略S3，找到AmazonS3FullAccess，点击下一步。 step4：命名角色为redshiftunload。 step5：打开刚定义的role并复制角色ARN。（unload命令会用到） step6：进入Redshift集群，打开管理IAM角色 step7：选择刚定义的redshiftunload角色并应用更改。执行unload命令卸载数据以管道分隔符导出数据以默认管道符号（|）的方式将数据卸载到对应的S3存储桶中，并以venue_为前缀进行存储，如下： unload ('select * from venue') to 's3://aws2oss/venue_' iam_role '<新建的redshiftunload角色对应的ARN>'; --parallel off; --连续卸载，UNLOAD 将一次写入一个文件，每个文件的大小最多为 6.2 GB 执行效果图如下：进入Amazon S3对应的存储桶中可以查看到有两份文件，且以venue_为前缀的，可以打开文件查看下数据。数据如下，以管道字符（|)分隔：以指标符导出数据要将相同的结果集卸载到制表符分隔的文件中，请发出下面的命令： unload ('select * from venue') to 's3://aws2oss/venue_' iam_role '<新建的redshiftunload角色对应的ARN>' delimiter as '\t'; 打开文件可以预览到数据文件如下： ----为了MaxCompute更方便的读取数据，我们采用以逗号(,)分隔-- unload ('select * from venue') to 's3://aws2oss/venue_' iam_role '<新建的redshiftunload角色对应的ARN>' delimiter as ',' NULL AS '0'; 更多关于unload的命令说明详见：https://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_UNLOAD.html Amazon S3无缝切换到OSS 在线迁移工具只支持同一个国家的数据源，针对不同国家数据源迁移建议用户采用OSS迁移工具，自己部署迁移服务并且购买专线来完成，详见：https://help.aliyun.com/document_detail/56990.html OSS提供了S3 API的兼容性，可以让您的数据从AWS S3无缝迁移到阿里云OSS上。从AWS S3迁移到OSS后，您仍然可以使用S3 API访问OSS。更多可以详见S3迁移教程。背景信息 ① 执行在线迁移任务过程中，读取Amazon S3数据会产生公网流出流量费，该费用由Amazon方收取。② 在线迁移默认不支持跨境迁移数据，若有跨境数据迁移需求需要提交工单来申请配置任务的权限。准备工作 Amazon S3前提工作接下来以RAM子账号来演示Amazon S3数据迁移到Aliyun OSS上。预估迁移数据，进入管控台中确认S3中有的存储量与文件数量。创建迁移密钥，进入AWS IAM页面中创建用户并赋予AmazonS3ReadOnlyAccess权限。添加用户-->访问类型（编程访问，AK信息）-->赋予AmazonS3ReadOnlyAccess权限-->记录AK信息。 step1：进入IAM，选择添加用户。![image.png] step2：新增用户并勾选创建AK。 step3：选择直接附加现有策略，并赋予AmazonS3ReadOnlyAccess权限。 step4：记录AK信息，在数据迁移中会用到。 Aliyun OSS前提工作阿里云OSS相关操作，新创建bucket：创建RAM子账号并授予OSS bucket的读写权限和在线迁移管理权限。迁移实施迁移会占用源端和目的端的网络资源；迁移需要检查源端和目的端文件，如果存在文件名相同且源端的最后更新时间少于目的端，会进行覆盖。进入阿里云数据在线迁移控制台：https://mgw.console.aliyun.com/?spm=a2c4g.11186623.2.11.10fe1e02iYSAhv#/job?_k=6w2hbo，并以《Aliyun OSS前提工作》中新建的子账号登录。进入数据迁移服务-数据地址-数据类型（其他），如下：【创建源地址：】具体配置项说明详见：https://help.aliyun.com/document_detail/95159.html【创建目标地址：】具体配置项说明详见：https://help.aliyun.com/document_detail/95159.html 创建迁移任务从左侧tab页面中找到迁移任务，并进入页面，点击创建迁移任务。![image.png] ---->OSS中的数据如下： MaxCompute直接加载OSS数据授权在查询OSS上数据之前，需要对将OSS的数据相关权限赋给MaxCompute的访问账号，授权详见授权文档。MaxCompute需要直接访问OSS的数据，前提需要将OSS的数据相关权限赋给MaxCompute的访问账号，您可通过以下方式授予权限：当MaxCompute和OSS的owner是同一个账号时，可以直接登录阿里云账号后，点击此处完成一键授权。若MaxCompute和OSS不是同一个账号，此处需由OSS账号登录进行授权，详见文档。创建外部表在DataWorks中创建外部表，如下图所示：创建MaxCompute外部表DDL语句： CREATE EXTERNAL TABLE IF NOT EXISTS venue_external ( VENUEID bigint, VENUENAME string, VENUECITY string, VENUESTATE string, VENUESEATS bigint ) STORED BY 'com.aliyun.odps.CsvStorageHandler' -- (1) WITH SERDEPROPERTIES ( 'odps.properties.rolearn'='acs:ram::*****:role/aliyunodpsdefaultrole' ) -- (2) LOCATION 'oss://oss-cn-shanghai-internal.aliyuncs.com/redshift2odps/s3/'; -- (3)(4) com.aliyun.odps.CsvStorageHandler是内置的处理CSV格式文件的StorageHandler，它定义了如何读写CSV文件。您只需指明这个名字，相关逻辑已经由系统实现。如果用户在数据卸载到S3时候自定义了其他分隔符那么，MaxCompute也支持自定义分隔符的Handler，详见：https://help.aliyun.com/document_detail/45389.html 可以直接查询返回结果：select * from venue_external limit 10; DataWorks上执行的结果如下图所示：创建内部表固化数据如果后续还需要做复杂的查询且数据量特别大的情况下，建议将外部表转换为内部表，具体示意如下：create table if not exists venue as select * from venue_external;

21分钟教会你分析MaxCompute账单

21分钟教会你分析MaxCompute账单背景阿里云大计算服务MaxCompute是一款商业化的大数据分析平台，其计算资源有预付费和后付费两种计费方式。并且产品每天按照project为维度进行计量计费（账单基本情况下会第二天6点前产出）。本文使用的为云上客户真实数据，故在下文中的截图都mask掉了。关于MaxCompute计量计费说明，详见官方文档：但是通常情况下，我们在数据开发阶段或者在上线前夕会发下账单有波动（通常情况下为增大），其实用户首先可以通过自助的方式来分析账单波动情况，再倒逼自己的作业进行优化。阿里云费用中心就是一个很好的通道，阿里云所有商业化收费的产品都可以在其中下载费用明细。获取账单信息通常您需要使用主账号查看账单详情。如果您需要使用子账号查看账单信息，请首先参考费用中心RAM配置策略行子账号授权。 step1：使用主账号或者被授权的RAM子账号来登录阿里云管控台。step2：右上角进入费用中心。 step3：在费用中心-消费记录-消费明细中，选择产品和账单日期。包年包月中的后付费是指项目开通包年包月计算计费模式后，还产生的存储、下载对应的费用（存储、下载费用只有后付费）。 step4：为了方便批量分析数据，我们选择下载使用记录csv文件在本地分析。下载csv文件如下，可以在本地打开进行分析。 --csv的表头项目编号,计量信息编号,数据分类,存储（Byte）,结束时间,SQL/交互式分析,读取量（Byte）,SQL复杂度,公网上行流量（Byte）,公网下行流量（Byte）,MR/Spark作业计算（Core*Second）,SQL读取量_访问OTS（Byte）,SQL读取量_访问OSS（Byte）,开始时间,计算资源规格,DataWorks调度任务ID 上传账单明细至MaxCompute 使用记录明细字段解释：项目编号：当前账号下或子账号对应的主账号的MaxCompute project列表。计量信息编号：其中会包含存储、计算、上传和下载的计费信息编号，SQL为instanceid，上传和下载为Tunnel sessionid。数据分类：Storage（存储）、ComputationSql（计算）、UploadIn（内网上传）、UploadEx（外网上传）、DownloadIn（内网下载）、DownloadEx（外网下载）、spark（计算）、LightningQuery（计算）。按照计费规则其中只有红色为实际计费项目。开始时间/结束时间：按照实际作业执行时间进行计量，只有Storage是按照每个小时取一次数据。存储（Byte）：每小时读取的存储量单位为Byte。 SQL 读取量（Byte）：SQL计算项，每一次SQL执行时SQL的input数据量，单位为Byte。 SQL 复杂度（Byte）：每次执行SQL的复杂度，为SQL计费因子之一。公网上行流量（Byte），公网下行流量（Byte）：分别为公网上传和下载的数据量，单位Byte。 MR作业计算（CoreSecond）：MR作业的计算时单位为coresecond，需要转换为计算时hour。 SQL读取量_访问OTS（Byte）,SQL读取量_访问OSS（Byte）：外部表实施收费后的读取数据量，单位Byte。计算资源规格：暂时为空。 DataWorks调度任务ID：通过DataWorks调度执行的节点ID，可以通过ID在运维中心中定位到具体某个节点。 ① 确认CSV文件数据，尤其是列分隔符等（推荐使用UE）。数据以逗号分隔，且单元格值都带有双引号。 ② 数据预处理：替换掉文档所有双引号，以方便使用Tunnel等上传工具。替换为不用填写。直接点击全部替换。 ③ 创建MaxCompute表，存储下载的消费明细。 DROP TABLE IF EXISTS maxcomputefee ; CREATE TABLE IF NOT EXISTS maxcomputefee ( projectid STRING COMMENT '项目编号' ,feeid STRING COMMENT '计费信息编号' ,type STRING COMMENT '数据分类，包括Storage、ComputationSQL、DownloadEx等' ,storage BIGINT COMMENT '存储量' ,endtime DATETIME COMMENT '结束时间' ,computationsqlinput BIGINT COMMENT '输入数据量' ,computationsqlcomplexity DOUBLE COMMENT 'sql复杂度' ,uploadex BIGINT COMMENT '公网上行流量Byte' ,download BIGINT COMMENT '公网下行流量Byte' ,cu_usage DOUBLE COMMENT 'MR计算时*second' ,input_ots BIGINT COMMENT '访问OTS的数据输入量' ,input_oss BIGINT COMMENT '访问OSS的数据输入量' ,starttime DATETIME COMMENT '开始时间' ,guige string COMMENT '计算资源规格' ,nodeid string COMMENT '对应DataWorks节点ID' ) ; ④ Tunnel上传数据，具体Tunnel的配置详见官方文档。odps@ sz_mc>tunnel upload /Users/yangyi/Desktop/ODPS_2019-01-12_2019-01-14.csv maxcomputefee -c "UTF-8" -h "true" -dfp "yyyy-MM-dd HH:mm:ss"; 当然用户也可以通过DataWorks数据导入的功能来进行，具体详见操作步骤。 ⑤ 验证数据。通过SQL分析账单数据 1、分析SQL费用云上客户使用MaxCompute，95%的用户通过SQL即可满足需求，SQL也在消费成长中占据了绝大部分。 SQL费用=一次SQL计算费用 = 计算输入数据量 × SQL复杂度 × 0.3元/GB --分析SQL消费，按照SQL进行排行 SELECT to_char(endtime,'yyyymmdd') as ds,feeid as instanceid ,projectid ,computationsqlcomplexity --复杂度 ,SUM((computationsqlinput / 1024 / 1024 / 1024)) as computationsqlinput --数据输入量GB ,SUM((computationsqlinput / 1024 / 1024 / 1024)) * computationsqlcomplexity * 0.3 AS sqlmoney FROM maxcomputefee WHERE TYPE = 'ComputationSql' AND to_char(endtime,'yyyymmdd') >= '20190112' GROUP BY to_char(endtime,'yyyymmdd'),feeid ,projectid ,computationsqlcomplexity ORDER BY sqlmoney DESC LIMIT 10000 ; --查询结果-- 根据此段SQL执行结果可以得到如下结论：大作业可以优化的点：**是否可以减小数据读取量、降低复杂度来优化费用成本。也可以按照ds字段（按照天）进行汇总，分析某个时间段内的SQL消费金额走势。比如利用本地excle或云上QuickBI等工具绘制折线图等方式，更直观的反应作业的趋势。拿到具体的instanceid，在console或者DataWorks脚本中进行wait instanceid;查看具体作业和SQL。随即在浏览器中打开logview的url地址（关于logview的介绍详见官方文档）：从logview中获取DataWorks节点名称：在logview中打开SourceXML可以查看到具体执行信息，如SKYNET_NODENAME表示DataWorks的节点名称（当然只有被调度系统执行的作业才有值，临时查询为空，如下图所示）。拿到节点名称可以快速的在DataWorks找到该节点进行优化或查看责任人。 2、分析作业增长趋势一般情况下费用的增长背后其实是作业量的暴涨，可能是重复执行，也可能是调度属性配置的不是很合理。 --分析作业增长趋势 SELECT TO_CHAR(endtime,'yyyymmdd') AS ds ,projectid ,COUNT(*) AS tasknum FROM maxcomputefee WHERE TYPE = 'ComputationSql' AND TO_CHAR(endtime,'yyyymmdd') >= '20190112' GROUP BY TO_CHAR(endtime,'yyyymmdd') ,projectid ORDER BY tasknum DESC LIMIT 10000 ; --执行结果-- 从执行结果可以看出来12-14日提交到MaxCompute且执行成功的作业数的波动趋势。 3、分析存储费用存储费用的计费规则相对来说比较复杂，因为下载到的明细是每个小时取一次数据。按照MaxCompute存储计费规则，会整体24小时求和然后平均之后的值再阶梯收费。具体详见官网。 --分析存储费用 SELECT t.ds ,t.projectid ,t.storage ,CASE WHEN t.storage < 0.5 THEN 0.01 WHEN t.storage >= 0.5 AND t.storage <= 100 THEN t.storage*0.0192 WHEN t.storage > 100 AND t.storage <= 1024 THEN (100*0.0192+(t.storage-100)*0.0096) WHEN t.storage > 1024 AND t.storage <= 10240 THEN (100*0.0192+(1024-100)*0.0096+(t.storage-1024)*0.0084) WHEN t.storage > 10240 AND t.storage <= 102400 THEN (100*0.0192+(1024-100)*0.0096+(10240-1024)*0.0084+(t.storage-10240)*0.0072) WHEN t.storage > 102400 AND t.storage <= 1048576 THEN (100*0.0192+(1024-100)*0.0096+(10240-1024)*0.0084+(102400-10240)*0.0072+(t.storage-102400)*0.006) END storage_fee FROM ( SELECT to_char(starttime,'yyyymmdd') as ds ,projectid ,SUM(storage/1024/1024/1024)/24 AS storage FROM maxcomputefee WHERE TYPE = 'Storage' and to_char(starttime,'yyyymmdd') >= '20190112' GROUP BY to_char(starttime,'yyyymmdd') ,projectid ) t ORDER BY storage_fee DESC ; --执行结果-- 根据计算结果可以分析得出结论：存储在13日为最高有一个增长的过程，但是在14日是有降低。存储优化，建议表设置生命周期，删除长期不使用的临时表等。 4、分析下载费用对于公网或者跨Region的数据下载，MaxCompute将按照下载的数据大小进行计费。计费公式为：一次下载费用=下载数据量*0.8元/GB。 --分析下载消费明细 SELECT TO_CHAR(starttime,'yyyymmdd') AS ds ,projectid ,SUM((download/1024/1024/1024)*0.8) AS download_fee FROM maxcomputefee WHERE type = 'DownloadEx' AND TO_CHAR(starttime,'yyyymmdd') >= '20190112' GROUP BY TO_CHAR(starttime,'yyyymmdd') ,projectid ORDER BY download_fee DESC ; 按照执行结果也可以分析出某个时间段内的下载费用走势。另外可以通过tunnel show history查看具体历史信息，具体命令详见官方文档。以下几种计算作业与SQL类似，按照官方计费文档编写SQL即可。 5、分析MapReduce作业消费 MR任务当日计算费用=当日总计算时*0.46元 --分析MR作业消费 SELECT TO_CHAR(starttime,'yyyymmdd') AS ds ,projectid ,(cu_usage/3600)*0.46 AS mr_fee FROM maxcomputefee WHERE type = 'MapReduce' AND TO_CHAR(starttime,'yyyymmdd') >= '20190112' GROUP BY TO_CHAR(starttime,'yyyymmdd') ,projectid ,cu_usage ORDER BY mr_fee DESC ; 6、分析外部表作业（OTS和OSS） SQL外部表功能计费规则：一次SQL计算费用=计算输入数据量×SQL复杂度×0.03元/GB --分析OTS外部表SQL作业消费 SELECT TO_CHAR(starttime,'yyyymmdd') AS ds ,projectid ,(input_ots/1024/1024/1024)*1*0.03 AS ots_fee FROM maxcomputefee WHERE type = 'ComputationSql' AND TO_CHAR(starttime,'yyyymmdd') >= '20190112' GROUP BY TO_CHAR(starttime,'yyyymmdd') ,projectid ,input_ots ORDER BY ots_fee DESC ; --分析OSS外部表SQL作业消费 SELECT TO_CHAR(starttime,'yyyymmdd') AS ds ,projectid ,(input_oss/1024/1024/1024)*1*0.03 AS ots_fee FROM maxcomputefee WHERE type = 'ComputationSql' AND TO_CHAR(starttime,'yyyymmdd') >= '20190112' GROUP BY TO_CHAR(starttime,'yyyymmdd') ,projectid ,input_oss ORDER BY ots_fee DESC ; 7、分析Lightning查询费用一次Lightning查询费用 = 查询输入数据量 * 单价（0.03元/GB） SELECT to_char(endtime,'yyyymmdd') as ds,feeid as instanceid ,projectid ,computationsqlcomplexity ,SUM((computationsqlinput / 1024 / 1024 / 1024)) as computationsqlinput ,SUM((computationsqlinput / 1024 / 1024 / 1024)) * computationsqlcomplexity * 0.03 AS sqlmoney FROM maxcomputefee WHERE TYPE = 'LightningQuery' --AND to_char(endtime,'yyyymmdd') >= '20190112' GROUP BY to_char(endtime,'yyyymmdd'),feeid ,projectid ,computationsqlcomplexity ORDER BY sqlmoney DESC LIMIT 10000 ; 8、分析Spark计算费用 Spark任务当日计算费用 = 当日总计算时 * 0.66元（人民币） --分析MR作业消费 SELECT TO_CHAR(starttime,'yyyymmdd') AS ds ,projectid ,(cu_usage/3600)*0.66 AS mr_fee FROM maxcomputefee WHERE type = 'spark' AND TO_CHAR(starttime,'yyyymmdd') >= '20190112' GROUP BY TO_CHAR(starttime,'yyyymmdd') ,projectid ,cu_usage ORDER BY mr_fee DESC ; 总结 MaxCompute产品消费的增长（暴涨）往往背后都是由于作业量的大幅度提升，要优化自己的费用成本，首选要知道自己SQL等作业中存在什么问题，要优化具体哪一个SQL。本文期望能够给予大家一些帮助。更多关于费用成本优化的文章可以详见，云栖社区《帮助企业做好MaxCompute大数据平台成本优化的最佳实践》和《众安保险如果优化自己的MaxCompute费用成本实践》。

祎休个人页面-阿里云开发者社区

个人介绍

擅长的技术

在MaxCompute中配置Policy策略遇到结果不一致的问题

通过DataWorks数据集成归档日志服务数据至MaxCompute进行离线分析

Amazon Redshift数据迁移到MaxCompute

使用split_size优化的ODPS SQL的场景

21分钟教会你分析MaxCompute账单

在MaxCompute中配置Policy策略遇到结果不一致的问题

通过DataWorks数据集成归档日志服务数据至MaxCompute进行离线分析

Amazon Redshift数据迁移到MaxCompute

使用split_size优化的ODPS SQL的场景

21分钟教会你分析MaxCompute账单

在DataWorks中实现指定UDF只能被指定账户访问

MaxCompute/DataWorks权限问题排查建议

开箱即用的安全方案：MaxCompute数据安全方案介绍

基于MaxCompute构建企业用户画像（用户标签的制作）

基于MaxCompute的拉链表设计

PyODPS 0.7.15版本发布

北京云栖大会workshop：《数据处理：数据建模与加工》篇

北京Workshop准备条件：《云数据·大计算：快速搭建互联网在线运营分析平台》

北京：《云数据·大计算：快速搭建互联网在线运营分析平台》Workshop-入口

[使用初学者]如何快速玩转阿里云大数据计算服务MaxCompute

【干货索引】阿里云大数据计算服务MaxCompute与生态系统的融合

阿里云与Apache Flink商业公司DataArtisans于2017杭州云栖大会达成战略合作并发布

[干货]作为大数据入门者，你不得不知道的2017杭州云栖大会

2017云栖大会·杭州峰会：《在线用户行为分析：基于流式计算的数据处理及应用》之《数据可视化：构建实时动态运营数据分析大屏》篇

2017云栖大会·杭州峰会：《在线用户行为分析：基于流式计算的数据处理及应用》之《流数据处理：通过StreamSQL分析视频日志》篇