ETL功能介绍(筛选、连接、汇总、合并)
ETL属于数据集成的增强能力,ETL代表Extract(提取)、Transform(转换)、Load(加载),它涵盖了将数据从一个数据源提取出来,经过各种处理和转换,最后加载到另一个数据源的全过程。在数据集成中,除来源和目的地节点外,我们还提供了四个功能性节点,下面将对其进行详细介绍。
数据源
此节点用于配置读取数据,在一个同步任务中可以包含多个数据来源节点,例如在多表连接、数据合并节点中。
数据目的地
此节点用于配置写入数据,每个同步任务中只能存在一个数据目的地节点。
筛选过滤
使用该节点后,系统将在数据同步过程中对数据进行过滤,之同步过滤后的数据。任意节点之间均可添加「筛选过滤」节点,筛选条件可以为多个条件或条件组。
添加筛选过滤节点
配置筛选条件
多表连接
使用该节点后,可提供异构数据库之间的多表连接能力,同时支持HAP和外部数据库之间的连接。在查询中将多个表按照一定条件关联起来,从而获取更完整的数据集。
多表连接可以有效解决企业各业务系统之间数据不互通、缺乏关联性的问题,使数据成为真正有价值的资源。
更全面的数据:多表连接允许将分散的数据整合在一起,形成更完整、更全面的数据集,以提供更丰富、更详细的数据信息。
多维度的数据:通过将多个表关联起来设置不同关联关系,可以从不同维度和角度对数据进行深入分析。
使用步骤
1. 点击任意数据同步任务进入画布,鼠标停留在两个节点之间点击「+」添加“多表连接”节点
2. 添加后,会出现两个节点,读取数据源 和 多表连接
读取数据源:用于选择需要连接的数据源,可以是工作表也可以是外部数据库
多表连接:用于配置连接方式、连接条件
3. 配置多表连接
需要先选择数据源,选择后回到多表连接节点配置相应的连接条件
4. 如果需要连接更多的表,还可以在多表连接后增加新的“多表连接”节点
三种连接方式
内连接: 左右互查,只合并命中条目
内连接是一种 一 一映射关系,两张表都有的才能显示出来。
左连接: 左查右,合并全部
左连接是左边表的所有数据都有显示出来,右边的表数据只显示共同有的那部分,没有对应的部分显示为NULL。
右连接: 右查左,合并全部
和左连接相反
多表连接的限制
- 可在任意节点间,增加“多表连接”;
- 同一数据源同一数据库同一张表,不支持再次添加关联;
- 字段配置中,两个表的字段一起判断不可重复,可通过重命名解决;
- 字段配置中,如果是数据库主键列必选,如果是工作表rowid列必选。
场景示例
比如当前有三张工作表,学生表、数学成绩表、语文成绩表,现需要在一张表中显示同一个人的不同科目成绩信息。我们就可以使用 多表连接 来将三张表连接到一起并写入到一张表中。
1、准备数据
2、创建同步任务
3、配置数据源和目的地
4、创建方式选择为 “同步时需要对数据进行处理”
因为已经确定使用ETL能力中的多表连接,因此直接进入画布编辑。
PS:如果我们创建的是纯粹的同步数据的任务,可以点击“仅同步数据”
5、添加多表连接节点
6、配置多表连接的数据源
添加多表连接后,会出现两个节点。
数据源: 这里我们来配置第二个表语文成绩表,其中成绩字段,我们为了区分来源,这里重命名为语文成绩
多表连接: 按照左连接将学生表 和 语文成绩表 连接起来,因为学生表是包含了所有学生,但是语文成绩表不一定包含所有学生,因此使用左连接,以保证学生的数据完全同步
配置连接条件:可以配置多个条件,这里我们仅使用姓名
编辑字段:在这里会看到两张表的字段,这是勾选的的字段,就是下一个节点可以用的字段,在下一个环节我们不需要语文成绩的姓名,因此这里可以取消勾选。
7、连接数学成绩表
在任意节点之间都可以新增ETL节点,这里我们新增第二个多表连接,用来连接数学成绩
这里的配置可以参考语文成绩节点
连接方式: 选择左连接
连接条件: 这里左侧可以选择到上一个多表连接的字段,这里尽量选择学生表的姓名,因为学生表的数据是完整的。
编辑字段: 数学成绩的姓名字段也是没必要的,因此这里也可以取消勾选
8、配置写入目的地
以上已经配置好了数据源 学生表、语文成绩表、数学成绩表,现在需要来配置这些数据写入的目的地表了。
在目的地表,提供了新建工作表 或者 选择已有表,如果有可以匹配的表就选择已有表就好了,这里以新建工作表举例
新建表名称: 创建在数据库或者应用中的名字
主键字段: 在字段名后侧显示 钥匙 的字段均为主键字段,由于同步过程中主键字段需要一直存在,因此这里也是必选,目的地配置的映射字段随意即可。
9、发布同步任务并查看效果
分类汇总
分类汇总主要用于数据分析中,从结果来看可以有效识别数据中的变化、异常从而监测和反馈问题。
常用于:
数据分析和决策制定: 分类汇总允许将大量的原始数据转化为有意义的信息,从而帮助更好地理解数据,做出有根据的决策;
报表生成: 根据分类汇总的结果,可以很方便的生成报表和可视化的数据;
监测和反馈: 根据分类汇总的结果,有助于监测业务指标和性能指标(配合工作流),并及时提供反馈,以便及时采取行动。
使用步骤
点击任意数据同步任务进入画布,鼠标停留在两个节点之间点击「+」添加“分类汇总”节点
配置分类和汇总字段
- 分类字段:可以选择任意字段
- 汇总字段:根据不同字段类型支持不同的函数
- 文本类型:支持最大值、最小值的函数
- 数值类型(包含日期):支持求和、平均值、最大值、最小值的函数
- 计数:仅支持行的计数(*)
分类汇总的限制
只有在分类字段和汇总统计字段中添加的字段,才会作为输出参数传递给下一个节点;
汇总统计字段中的字段必须选择函数。
场景示例
以多表连接的结果为例,来做一次分类汇总,我们在多表连接的结果中增加一列班级,最后我们要做的是按照班级分组,看下不同班级中的语文和数学的最高分、平均分以及最低分。
准备数据
新增分类汇总节点
配置分类汇总节点
- 分类字段:班级
- 汇总统计字段:选择语文成绩和数学成绩,分别选择最大值、最小值、平均值的计算方式
配置目的地节点
新增一张表,用来存储汇总后的数据
更新发布同步任务,查看最终结果
结果可见,已经按照班级分组好了所有数据,同时也根据不同的函数计算出来了相应的值。
数据合并
此节点可以将两个表的结果合并成一个结果集,并支持六种集合运算。
在画布中叠加使用「数据合并」节点,最终可达到多表数据合并的目的,大大简化了多表分开查询数据再统计结果的繁琐配置,提高统计效率。数据集成的任务流程也更易于修改,并能容纳更多表的合并运算。
使用步骤
点击任意数据同步任务进入画布,鼠标停留在两个节点之间点击「+」添加“数据合并”节点
添加后,会出现两个节点,读取数据源 和 数据合并
◦ 读取数据源: 用于选择需要连接的数据源,可以是工作表也可以是外部数据库
◦ 数据合并: 用于配置合并运算方式
支持的六种集合运算:
- 去重合并(UNION):合并时去掉两个表中重复的行记录,保留唯一记录;
- 全部合并(UNION ALL):合并时保留两个表中所有的行记录;
- 相交并去重(INTERSECT):只有在两个表中都存在的记录,才会被保留,并去除重复记录;
- 相交 (INTERSECT ALL):只有在两个表中都存在的记录,才会被保留,不会去除重复记录;
- 排除并去重(EXCEPT):只有在左表中存在,但在右表中不存在的记录会被保留,并去除重复记录;
- 排除(EXCEPT ALL):只有在左表中存在,但在右表中不存在的记录会被保留,不会去除重复记录。
数据合并的限制
- 字段配置只有相同名称,相同类型才会合并列。
场景示例
以分类汇总案例的结果表为例,现在需要与分校的数据进行合并,分校的数据存储在另外一套系统中,因此需要通过读取数据库来合并数据。
准备数据
分类汇总的结果:
分校的数据(来源数据库):
添加数据合并节点
配置数据合并
字段配置
目的地新建表
更新发布,查看最终结果