【前言】:前4年中,参与过的数据迁移工作并不频繁,而且使用的方法也很传统,大多数重复性的工作都是手动对csv源文件在Excel上做导入前的处理,大体包括:
#1. 数据mapping(有时需要将原对象拆表满足CRM数据结构)
#2. 统一数据类型
#3. 数据清洗,如去重 / 调整数据格式(譬如,将phone中的area code与phone分离)
#4. 数据默认值填充
#5. 数据ownership的对应
这类工作看起来很简单(就是把旧系统数据转存在新系统中),因此在实施中很容易被低估。
而且一旦给定窗口期内源数据发生变化,或者客户临时变更数据合同内容,还需在Excel上重复数遍上述操作才能最终把变化按新要求对应进新系统。以此来看,传统方式对实施团队的关注细节能力有较高的要求,同时也对数据所有人对结果的监测也提高了要求(往往客户监测点比较简单:其一是数据量,其二是抽查10条相关联的数据作为导入前后的比对)。
在一年前的SF线下学习交流会上,就听说过ETL工具,直到最近刚好又碰上数据迁移的工作机会,借着留粤过年的闲暇时间,做下ETL工具选型调研再合适不过了。
【传统方式定义】:
这里首先定义下何为“传统方式”?
就是利用诸如Data Import Wizard / Data Loader / Salesforce Inspector将数据从本地的csv/Excel源文件导进Salesforce。
【主流ETL工具】:
市场上有很多受欢迎的企业版本的ETL工具,如:
- Informatica(Power Center)
当然也有些够用的开源免费版本的ETL工具,如:
- Power Query for Excel (最受益的功能是将JSON数据格式转成Excel表格格式,同时也支持Web Link的爬虫处理)
- Power BI for desktop (该工具支持线下做数据的Transform / Load -> 构建数据关系 -> 交互式预览的数据可视化,并最终可通过Cloud Service发布到Portal与同事分享,同事支持Mobile预览)
- Pentaho (也叫Kettle,比较稳定的是v7.1版本,新版本PDI v9.1大致有1.8GB,比较重要的概念是Transform和Job - 以业务与工作流为导向)
- Talend (官方社区很强大)
- Data Loader CLI (除支持将本地csv数据导入SF外,还支持直连MySQL写查询并导入SF)
其中导入Date时,需要将日期格式化为YYYY-MM-DDThh:mm:ssZ,以timezone: Asia/Shanghai为例,可以通过excel提前格式化为YYYY-MM-DDT08:mm:ssZ来避免相差一天的问题,DateTime不受影响。
上面提及的工具都是亲自实过的,由于Pentaho动不动就未响应,很占内存,后面就转向Talend这个方向了,因此本文将着重介绍Talend的实战。
【落地项目】:将房源图片信息同步到关联对象 - 一键运行,多国家数据迁移无缝切换
话题1:如何在父子job间共享上下文?即如果父job选的是DEV环境,如何确保子job也是按照DEV上下文的变量来执行作业。
#1. 使用动态作业(一个tRunJob组件通过迭代jobName来执行多个子job)时:
#2. 不使用动态作业(通过使用多个tRunJob组件来执行多个子Job)时:
话题2:tFixedFlowInput与tFlowToInterate组件实现便利效果
((String)globalMap.get("row1.jobName"))
话题3:性能调优 - 目标是将10001条记录的obj name填充到no__c字段,调优前更新了7k记录后就报错:java.net.SocketTimeoutException: Read timed out
话题4:如果数据中含有很多图片导致update超时或者内存不足 - 建议将tMap好的结果输出到Excel再手动导入:
【技术边界调研】:
- 从Excel中读取含有code参考表的数据并同步到SF
- Salesforce间的Junction对象数据迁移 (sixdeep -> sp21)
- JSON解析 (Xpath / JsonPath / JsonPath without loop)
{ "store": { "name": "Sunshine Department Store", "address": "Wangfujing Street", "goods": { "book": [ { "category": "Reference", "title": "Sayings of the Century", "author": "Nigel Rees", "price": 8.88 }, { "category": "Fiction", "title": "Sword of Honour", "author": "Evelyn Waugh", "price": 12.66 } ], "bicycle": { "type": "GIANT OCR2600", "color": "white", "price": 276 } } } }
- Excel与MySQL间数据迁移
5. 数据处理 (mysql id list转value list -> sf value list转id list)
假如mysql中的数据如上图,我们想把teacher表及ref表导入到sf,同时把skill_id和lang_id字符串数组替换成sf的id list,如何实现呢?
假如我们并不想在sf中做数据处理,如在sf中替换external id list为sf id list,我们可以使用tReplaceList组件来曲线救国 (项目实战4中如果能直接在tMap中写复杂的代码来处理id list的切换多好呀!!!)。
【ETL优点】:
- 对于需要手动重复性操作的步骤自动化成工作流
- 数据集成低代码化,拖拽即可完成
- 方便过程追溯与任务审查
【常见问题与解决方案】:
Q1. Job被锁后无法编辑属性及移动至poc文件夹,如何解锁?
A1. 按下图关闭job workspace
Q2. 如何使用sql builder写查询语句?
A2. 元数据 -> 数据库连接 -> MySQL_xx0.1 -> 右键编辑查询
Q3. 如何使用tLogRow组件打印日志信息?
A3. 拖拽tLogRow,基本设置选择“表”
Q4. 如何监控导入流?
A4. 如图:
【下一步计划】:
p1. 如何提取并转换tMap组件中row.column中数组的值?目前是通过SQL在navicat中将数据处理好再导入
p2. 如何提取tMap组件中row.column中json属性的值?
p3. 如何使用自定义code处理数据?
【不同工具导入DateTime对比】:
Talend目前测试F1是支持的。