|
关于ETL
ETL 是 Extract(抽取)、Transform(转换)、Load(加载) 的缩写,简单说就是:
把数据从多个来源取出来 → 清洗、加工成你需要的格式 → 再放到目标表或报表中使用。
什么时候会用到ETL?
- 数据来自不同系统(Excel、数据库、API),格式不统一
- 字段中包含乱码、空值、多余符号、日期格式混乱
- 需要从一段文本中提取关键信息(如地址、性别、产品规格)
- 需要对敏感信息进行脱敏(如手机号、身份证号)
- 希望用拖拽方式完成数据处理,而不是写复杂SQL或脚本
本文适用于什么场景?
本文档不讲解理论概念,完全面向实际工作痛点,适合以下场景:
- 你是业务人员、数据分析师、运营,经常被“脏数据”困扰
- 你不想每次都求IT写SQL,想自己快速完成数据清洗与转换
- 你需要处理:日期格式乱七八糟、文本中提取特征、手机号 / 身份证脱敏等
本文所有示例均基于 SmartBI 自助ETL实现,采用可视化、拖拽式操作,适合非技术背景人员快速上手。
SmartBI 的自助ETL是一种可视化、拖拽式的数据处理工具。它不需要编写复杂的SQL代码或编程语言,只需通过简单的鼠标拖拽、连接和配置,就能像“搭积木”一样构建数据流程。
无论你是要将日期数据进行多种转换、过滤数据,还是进行复杂的数据关联计算,自助ETL都能帮你轻松完成,让你从“数据搬运工”转变为“数据分析师”。
为了帮助大家快速上手自助ETL,本文档摒弃了枯燥的功能点罗列,完全从实际业务场景出发。我们将通过几个贴近工作的真实案例,手把手带大家体验如何利用自助ETL解决日常工作中的数据处理难题。
希望通过这些实例,能让你感受到:“ETL”这个技术词汇并不复杂,数据处理可以很简单, 再也不用求人,自己动手,简单SQL处理,数据立等可取。
实例一:日期类型转换
1.1业务场景与痛点
在日常的数据分析工作中,日期是我们最常用的分析维度之一。然而,由于数据来源不同(比如从不同业务系统导出、手工填写的Excel、第三方下载的数据),我们经常会遇到日期格式“五花八门”的情况:
格式混乱: 有的列是“2024-01-01”,有的列是“2024/01/01”,还有的是“2024.01.01”。
文本型日期: 日期看起来像是日期,但实际上在数据库中是以文本字符串形式存储的(例如“20240101”),导致无法直接进行时间序列计算或按年月筛选。
不规范的录入: 甚至可能出现“2024.1.1”或“1/1/2024”这种中西方混用的格式。
如果带着这些“脏数据”直接做分析,轻则导致图表无法正常显示,重则造成统计结果错误(例如按月份排序时出现乱序)。以前,我们可能需要手动修改Excel,或者再次提交给IT部门处理,耗时又费力。
在本实例中,我们将利用SmartBI自助ETL强大的数据类型转换与清洗功能,解决上述难题。
1.2功能实现
1.2.1 数值20240101转换为日期2024-01-01
派生列实现 to_date(cast([数值字段] AS STRING),'yyyyMMdd')

效果如下图

注意事项:
- 格式字符串必须匹配:'yyyyMMdd' 对应 20240101 这种格式
- 整数范围:确保整数在有效日期范围内(如 20240101 有效,但 20241301 无效)
- NULL 处理:如果整数列为 NULL,转换结果也为 NULL
- 性能:这个转换在 Spark 中执行效率较高,适合大规模数据处理
1.2.2 “2024-01-01”“2024/01/01”“2024.01.01”,统一转换为日期
派生列实现 TO_DATE(REGEXP_REPLACE([待处理字符串字段], '[./]', '-'), 'yyyy-MM-dd')

效果如下图

注意事项
- 正则表达式注意转义:点号在正则中是特殊字符
- 格式必须匹配:yyyy-MM-dd 要求月和日必须有前导零
- 处理 NULL 和空值:使用 COALESCE 或 TRY_TO_DATE
- 性能优化:大数据量时考虑使用 REPLACE 替代正则
- 数据清洗:先 TRIM() 去除空格
- 格式安全:使用 TRY_TO_DATE 避免转换失败报错
实例二:特征数据提取
2.1业务场景与痛点
在实际业务中,除了规范录入的结构化数据,我们经常需要面对大量的文本信息。这些文本里往往藏着关键的业务特征,但因其非结构化的特性,很难直接用于统计分析。
常见的场景包括:
客户信息表: “地址”字段里混杂着省市县信息,想单独统计“哪个城市的客户最多”,却无从下手。
产品描述: “产品名称”或“规格型号”中包含品牌、尺寸、颜色等特征(例如“Apple iPhone 14 128GB 紫色”),但这些特征都挤在一个单元格里,无法分别统计。
业务备注: 备注字段里记录了“退货原因”或“投诉类型”,但这些关键信息被淹没在长篇的文字描述中。
传统做法是人工肉眼识别、复制粘贴提取,或者用复杂的Excel公式(如LEFT、RIGHT、FIND)反复嵌套,不仅效率低下,还容易出错。当数据量变大时,这种方式几乎寸步难行。
在本实例中,我们将利用SmartBI自助ETL的文本处理能力,从杂乱无章的文本字段中智能提取关键特征。
2.2功能实现
2.2.1 性别判断
现有一个字符串字段,其中包含的数据为"余小姐居住在北京"、"陈先生在上海工作"等这样较为复杂的文本,现要判断文本中角色的性别。
派生列实现
CASE WHEN [文本字段] LIKE '%小姐%' THEN '女' WHEN [文本字段] LIKE '%先生%' THEN '男' ELSE '未登记' END

效果如下图

注意事项
- NULL 值:返回'未登记',无法区分,可以单独处理 NULL 情况
- 匹配优先级:顺序影响结果,更具体的规则放在前面
- 关键词不全:漏判"女士"等 增加更多关键词
实例三:关键数据脱敏
3.1业务场景与痛点
随着数据安全法规(如《数据安全法》、《个人信息保护法》)的逐步完善,企业对敏感信息的保护要求越来越高。在日常的数据分析与分享过程中,我们经常面临这样的两难困境:
既要共享,又要安全: 业务部门需要真实数据进行分析测试,但原始数据中包含客户姓名、手机号、身份证号、银行账号等个人隐私信息,直接分发存在极大的泄露风险。
开发测试环境需求: 系统开发或测试时,需要模拟真实数据环境,但又不能直接将生产库中的敏感数据暴露给开发人员或第三方合作伙伴。
数据导出管控: 业务人员导出数据用于汇报或外部协作时,需要对敏感字段进行模糊化处理,防止信息外泄。
过去,数据脱敏往往需要IT部门编写复杂的脚本或依赖专门的脱敏工具,流程长、响应慢。当业务人员急需一份“可用但不可识别”的数据时,往往只能等待。
在本实例中,我们将利用SmartBI自助ETL的数据脱敏与数据安全功能,实现敏感信息的自动化处理。
3.2功能实现
3.2.1 保留前后几位,中间替换为*
派生列实现 CONCAT(LEFT([待脱敏字段], 3), '****', RIGHT([待脱敏字段], 4))

效果如下图

注意事项
- 数据类型:数字类型无法直接截取,先用 CAST 转为字符串
- 长度校验:字段长度不足7位,增加长度判断和降级逻辑
- NULL处理:NULL 导致结果也为 NULL,单独处理 NULL 值
- 业务语义:规则与字段类型不匹配,根据字段类型选择合适的规则
最后还是来个任务,巩固下,顺带领点麦豆——>点击领取任务 |