【ETL】系列六：核心转换场景-行转列与列转行

2020-01-09 本文已影响0人不爱吃药的小白鼠

前面我们介绍了2大类核心转换场景。

【ETL】系列四：核心转换场景—单表间直转

【ETL】系列五：核心转换场景-字段合并与字段拆分

接下来我们介绍第三类核心转换场景——行转列与列转行。

简单来说，行转列是指横表转竖表，列转行是指竖表转横表。

横表的特点是一个ID对应所有的值信息，以行Key-Value1-Value2-Value3的方式存储；

竖表的特点是每行仅存储该ID的某一个类别字段的值，以行Key-Value的方式存储。

我们这里看下网上常见的行转列（横表转竖表）与列转行（竖表转横表）例子以方便理解。

横表示例

竖表示例

关于横表和竖表的介绍可参考：数据库设计---关于建表的时候选择横表和竖表(纵表)的一点思考

在本文的样例中，依然以金融数据的行转列（横表转竖表）与列转行（竖表转横表）例子来进行实操。

利润表-横表示例

利润表-竖表示例

在以往的数据业务中，由于各种原因，数据仓库中存储的来源表是横表或纵表，在产品端的用到的是纵表或横表，那就需要在数据处理环节，用到行转列或列转行这样的功能进行数据的同步。

首先，我们介绍行转列。

（一）行转列

应用场景：不同的业务数据表，数据存储的方式不同，在不同使用场景下，涉及到不同的数据处理方式，希望将横表的字段及数据作为值抽取出来并同步到竖表的记录行中，比如常见的姓名、科目、分数，一个姓名下所有科目的分数一行存储，映射到目标表之后一个姓名下一个科目的分数一行存储。接下来将以金融数据中利润表的财务科目存储中，来源表是横表的数据同步到目标表是竖表的数据为例进行实操。

业务目标：将来源库A中的表A【com_income1（利润表横表）】的数据推送到目标数据库B中的表B【com_income2（利润表竖表）】，并将来源表-利润表横表如overall_income、main_income、overall_cost等利润表科目字段及值映射到目标表-利润表竖表对应的stand_pro_name（标准科目名称）字段值中。

业务流程分析：

抽取数据：抽取来源库A的数据表A【com_income1（利润表横表）】的数据，可以定义抽取数据范围；

转换数据：将来源表-利润表横表如overall_income、main_income、overall_cost等字段转为目标表-利润表竖表stand_pro_name（标准科目名称）的值，将来源表-利润表横表如overall_income、main_income、overall_cost等字段的数据转为了目标表-利润表竖表cur_amount（本期金额）的值，其他字段直接映射；

加载数据：数据加载到目标库B的数据表B【 com_income2（利润表竖表）】中，目标表已有数据，进行更新；否则，新增数据。

操作步骤：

1、点击左侧的核心对象，选择表输入并把它拖到右侧的编辑区中进行配置。

数据抽取【表输入】插件拖选

选择表输入并把它拖到右侧的编辑区中进行配置，主要是进行自定义来源表的抽取SQL语句。

数据抽取【表输入】插件配置

如截图所示，将来源库表信息进行填写或选择后，进行来源表的抽取SQL的定义，此处限制抽取数据的范围，只处理一个公司的利润表科目数据。

2、选择行转列并把它拖到右侧的编辑区中进行列转行配置。

在这里，需要理解key字段、字段名称、key值、value字段的含义。

1.Key字段：行转列之后，overall_income、main_income、overall_cost等字段会变成一个新的字段的值，Key字段就是这个新字段的名称，该样例中关键字段为stand_pro_name；

2.字段名称：填写横表需要行转列的字段，在这写的字段会从原来的字段名转变为一列数据，这列数据的列名为上面设置的Key字段名，该样例中的字段名称为overall_income、main_income、overall_cost等财务科目名称；

3.Key值：转为列之后key字段的值，该样例中的key值为营业总收入、营业收入、营业总成本等；

4.Value字段：行转列的那些字段是有数据的，这些数据转换为竖表的值，需要给这些数据起个名字，名字需要都一致，该样例中的value字段为cur_amount。

3、选择插入/更新并把它拖到右侧的编辑区中进行相关配置。

数据转换【行转列】插件配置

如截图中的配置，选择完目标数据库和目标数据表之后，用来查询的关键字，选择id，表示这里按照id查询，如果此id存在，则更新数据，若不存在则插入数据；获取目标表字段和来源表的流字段映射关系，排除在上一步【行转列】配置的字段名称。

插入/更新配置完成后，保存转换文件，可点击【运行这个转换】按钮进行本地运行。

数据加载【插入/更新】插件运行

4、转换成功后，也可以检查本地数据库，查看数据是否推送成功，是否进行了行转列。

来源表抽取数据截图

推送目标表后的数据截图

到这里，行转列的转换流程就配置成功了，后续打开.ktr后缀的文件即可。与行转列相对的，就是列转行了。

（二）列转行

应用场景：不同的业务数据表，数据存储的方式不同，希望将竖表的数据抽取作为新的字段同步到目标表中，比如常见的姓名、科目、分数，一个姓名下一个科目的分数一行存储，映射到目标表之后一个姓名下所有科目的分数一行存储。接下来将以金融数据中利润表的财务科目存储中，来源表是竖表的数据同步到目标表是横表的数据为例进行实操。

业务目标：将来源库A中的表A【com_income1（利润表竖表）】的数据推送到目标数据库B中的表B【com_income2（利润表横表）】，并将来源表A的stand_pro_name（标准科目名称）的值映射到目标表B对应科目的字段值中。

业务流程分析：

抽取数据：抽取来源库A的数据表A【com_income1（利润表竖表）】的数据，可以定义抽取数据范围；

转换数据：将来源表A的stand_pro_name（标准科目名称）的值映射到目标表B对应科目的字段值中，目标表B的com_uni_code（公司统一编码）、decl_date（公告日期）、statement_name（报表类型名称）为一组，其他字段直接映射；

加载数据：数据加载到目标库B的数据表B【 com_income2（利润表横表）】中，目标表已有数据，进行更新；否则，新增数据。

操作步骤：

1、点击左侧的核心对象，选择表输入并把它拖到右侧的编辑区中进行配置。

数据抽取【表输入】插件拖选

选择表输入并把它拖到右侧的编辑区中进行配置，主要是进行自定义来源表抽取SQL语句。

数据抽取【表输入】插件配置

如截图所示，将来源库表信息进行填写或选择后，进行来源表的抽取SQL的定义，此处限制抽取数据的范围，只处理一个公司的利润表科目数据。

2、选择列转行并把它拖到右侧的编辑区中进行列转行配置。

数据转换【列转行】插件配置

在这里，需要理解关键字段、分组字段、目标字段、数据字段、关键字值、类型/长度/精度的含义。

1.关键字段：列转行的核心字段，此字段会进行聚合操作，聚合结果作为之后横表的新字段名，该样例中关键字段是stand_pro_name(标准科目名称)，即以这个标准科目名称的值作为横表的新字段名，如转换为【营业总收入】、【营业收入】、【营业总成本】等字段。

2.分组字段：关键字段的数据（营业总收入，营业收入，营业总成本等）有重复，利润表的数据来源于公司发布的财报数据，每个公司每个报告期会发布合并利润表和母公司利润表，即每一个唯一标识的公司com_uni_code（公司统一编码）在decl_date（公告日期）发布合并或母公司利润表statement_name（报表类型名称）就会增加一条记录，其关键字段的数据就会重复一次，其中决定重复的字段就是com_uni_code、decl_date、statement_name，这三个字段就是分组字段。

3.目标字段：关键字段的数据去重后（营业总收入，营业收入，营业总成本等）变成的字段名，这个目标字段也就是转换后目标表的物理名，比如overall_income、main_income、overall_cost等。

4.数据字段：在竖表中，有一个与关键字段一一对应的数据字段，它的字段名填在这，该样例中为cur_amount（本期金额）；

5.关键字值：关键字值是关键字段的数据，关键字段的数据去重后（营业总收入，营业收入，营业总成本等）有多少列，关键字值就有多少个；

6.类型/长度/精度：定义关键字值的数据类型、长度和精度，该样例中利润表科目中的数值是分别为number、18、4。

3、选择插入/更新并把它拖到右侧的编辑区中进行相关配置。