Spoon图形操作
Kettle集群的基础原理:
一、 Kettle的导入导出
一般都是在.ktr
工程文件中的
1.1 csv、txt(02、03)
csv、txt文件输入
${Internal.Entry.Current.Directory}/
"CSV文件输入"步骤和与之类似的“固定宽度文件输入”步骤其实都是“文本文件输入”的简化版,都不适合一次处理多个文件
所以本质上在kettle里也是把csv当成txt格式进行转换的(跟pandas很像嘛)
csv、txt文件输出
1.2 xlsx(Excel)(04)
xlsx输入常见的操作
本质上就是变化了
以及:
xlsx输出常见操作(03)
1.3 XML(05)
XML输出方案对比:
XML输入方案对比:
1.4 json(06)
输入方案对比:
输出方案对比:
1.5 SQL
1.5.1 MySQL基础语法
因为涉及到数据库的操作都是转换为SQL语句进行操作的,虽然spoon非常友好的帮我们设置好了自动生成SQL语句,但是咱们至少得看得懂不是。
1.、创建数据库
创建数据库的官方标准语法为:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
其中{}中的内容为多选一,[]中的内容可带可不带,后续若有此类符号也是同等意思。
一般工作中常常这样创建数据库:
CREATE DATABASE IF NOT EXISTS `test_db` DEFAULT CHARACTER SET utf8;
想要查看数据库的创建语句,可以这样查看:
mysql> show create database test_db;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test_db | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
2、修改数据库
一般情况下很少去修改数据库,官方给出的标准语法为:
ALTER {DATABASE | SCHEMA} [db_name]
alter_specification ...
alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
特殊情况下我们可能会修改数据库的字符集,这时候我们可以这样书写:
ALTER DATABASE `test_db` DEFAULT CHARACTER SET utf8mb4;
3、删除数据库
删除数据库可要小心啊!千万不要删库跑路哦。此类需求一般极少,不过我们也要会呀,连库都不会删岂不是很没面子~~ 还是看下官方文档语法:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
相比创建和更改来讲,删除就显得简单粗暴很多。所有我们操作的时候要格外小心,删除前建议做下备份。
比如我们要删除test_db
库,我们可以这样写:
DROP DATABASE IF EXISTS `test_db`;
删除之后我们再执行show database就看不到test_db库了。
4、创建表
CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];
[表定义选项]的格式为:
<列名1> <类型1> [,…] <列名n> <类型n>
对于临时表的创建及分区表的创建选项,日常学习及工作中用的不多,这里就不多介绍,下面列举出一个基础的创建表的语句:
CREATE TABLE `user_info` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`user_id` bigint(11) NOT NULL COMMENT '用户id',
`username` varchar(45) NOT NULL COMMENT '真实姓名',
`email` varchar(30) NOT NULL COMMENT '用户邮箱',
`nickname` varchar(45) NOT NULL COMMENT '昵称',
`birthday` date NOT NULL COMMENT '生日',
`sex` tinyint(4) DEFAULT '0' COMMENT '性别',
`short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己,最多50个汉字',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_user_id` (`user_id`),
KEY `idx_username`(`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表'
5、修改表
修改表可选选项同样是很多的,参考上面创建表的选项,这里就不一一列出了。
alter table用于更改表的结构,例如,可以添加或删除列,创建或删除索引,更改现有列的类型,或重命名列或表本身。还可以更改表的存储引擎或表注释。下面介绍下几个常用的修改表的示例:
修改表选项
# 修改表的存储引擎
ALTER TABLE t1 ENGINE = InnoDB;
# 修改表的自增值
ALTER TABLE t1 AUTO_INCREMENT = 13;
# 修改表的字符集
ALTER TABLE t1 CHARACTER SET = utf8;
# 添加(或更改)表注释:
ALTER TABLE t1 COMMENT = 'New table comment';
# 修改表名称
ALTER TABLE t1 RENAME t2;
字段(列)操作
# 增加字段
# ALTER TABLE <表名> ADD COLUMN <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名]
ALTER TABLE t1 ADD COLUMN col1 INT FIRST;
# 删除字段
ALTER TABLE t1 DROP COLUMN col1;
# 修改字段类型
ALTER TABLE t1 MODIFY col1 VARCHAR(30);
# 更改字段名称
ALTER TABLE t1 CHANGE col1 col2 VARCHAR(30);
索引操作
# 添加索引
alter table t1 add index index_name (column_list) ;
alter table t1 add unique (column_list) ;
alter table t1 add primary key (column_list) ;
# 删除索引
alter table t1 drop index index_name ;
alter table t1 drop primary key ;
6、截断表
截断表即truncate table,也可理解为清空表,从逻辑上讲,TRUNCATE TABLE类似于DELETE一个表的所有行,但它绕过了删除数据的DML方法,因此它不能回滚。
truncate语法很简单,官方文档示例:
TRUNCATE [TABLE] tbl_name
7、删除表
删除表官方给出的参考语法为:
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
1.5.2 Spoon中的数据库操作(08)
咱们来拆分各个任务的需求,并选择合适的方法对其进行实现:
任务1 从student表读取数据,输出到student2表
注意:student表和student2表字段名不同
本质上就是输入表并对字段重命名:
输入:
输出:
任务2 将身高在179以上,成绩在80分以上的学生记录从student表中导入到student3
本质上是条件输出,在输入的时候加一个过滤条件即可:
任务3 将身高在?以上,成绩在?分以上的学生记录从student表中导入到student4
- ? 从前一个步骤获取
- student4表插入前自动清空
任务4 将身高在变量myheight以上,成绩在变量myscore分以上的学生记录从student表中导入到student5 - myheight,myscore是作为命名参数变量设置在转换中
- student5表插入前自动清空
任务5 将student1表中的数据导入student6中
注意: student6 有ID主键约束
这里Mark一下怎么创建主键表:
DROP TABLE IF EXISTS student6;
CREATE TABLE student6
(
ID INT PRIMARY KEY
, NAME VARCHAR(45)
, SEX VARCHAR(45)
, GRADE VARCHAR(45)
, AGE VARCHAR(45)
, SCORE INT
, HEIGHT VARCHAR(45)
, PHONE VARCHAR(45)
)
;
1.6 CDC操作(Change Data Capture)(09、10)
CDC(Change Data Capture),我们称其为捕捉数据改变的一种操作(我自己是觉得跟日志没啥区别啦),不过由于数据库中一些敏感的操作需要被记录(比如有人尝试删库跑路hhhhh),所以我们需要Capture it!并且及时汇报,记录!
我的教材由于涉嫌凑字数的原因真的是巨能扯,其实汇集起来,CDC的操作无外就是用不同的方法来捕捉数据变化或者是操作变化:
操作之前先说一些数据库SQL如何进行表更新操作(也就是在spoon中如何insert、updata):
1.6.1 基于时间戳的CDC
最后更新日志时间:
1.6.2 基于自增序列的CDC数据导入
1.6.3 基于触发器的CDC
关键词:CREATE TRIGGER
# 创建操作日志表
-- -----------------------------------------------
-- 创建CDC操作记录表
-- 该表用于保存每一条数据被执行了什么操作和这些操作的同步状态
-- SID : 一条记录的主键(学号)
-- optype : 这条记录被执行了什么操作('I'-INSERT,'U'-UPDATE,'D'-DELETE)
-- opflag : 这条记录的操作是否被同步("未处理","已完成")
-- -----------------------------------------------
DROP TABLE IF EXISTS cdc_opt_log;
create table cdc_opt_log
(
SID int PRIMARY KEY,
optype char(1),
opflag char(6)
);
-- -----------------------------------------------
-- 创建INSERT触发器
-- 在studentinfo表上发生INSERT操作后记录下被插入的数据的主键
-- -----------------------------------------------
DROP TRIGGER IF EXISTS tri_insert_student;
DELIMITER $$ # 改变行分隔符
CREATE TRIGGER tri_insert_student AFTER INSERT ON studentinfo FOR EACH ROW
begin
IF (SELECT 1 FROM cdc_opt_log WHERE SID=new.ID) THEN
UPDATE cdc_opt_log SET optype='I',opflag='未处理' WHERE SID=new.ID;
ELSE
INSERT INTO cdc_opt_log(SID, optype, opflag) VALUES (new.ID, 'I', '未处理');
END IF;
end
$$
DELIMITER ;
-- -----------------------------------------------
-- 创建UPDATE触发器
-- 在studentinfo表上发生UPDATE操作后记录下被修改的数据的主键
-- -----------------------------------------------
DROP TRIGGER IF EXISTS tri_update_student;
DELIMITER //
CREATE TRIGGER tri_update_student AFTER UPDATE ON studentinfo FOR EACH ROW
begin
IF (SELECT 1 FROM cdc_opt_log WHERE SID=new.ID) THEN
UPDATE cdc_opt_log SET optype='U',opflag='未处理' WHERE SID=new.ID;
ELSE
INSERT INTO cdc_opt_log(SID, optype, opflag) VALUES (new.ID, 'U', '未处理');
END IF;
end
//
DELIMITER ;
-- -----------------------------------------------
-- 创建DELETE触发器
-- 在studentinfo表上发生DELETE操作后记录下被删除的数据的主键
-- -----------------------------------------------
DROP TRIGGER IF EXISTS tri_delete_student;
DELIMITER //
CREATE TRIGGER tri_delete_student AFTER DELETE ON studentinfo FOR EACH ROW
begin
IF (SELECT 1 FROM cdc_opt_log WHERE SID=old.ID) THEN
UPDATE cdc_opt_log SET optype='D',opflag='未处理' WHERE SID=old.ID;
ELSE
INSERT INTO cdc_opt_log(SID, optype, opflag) VALUES (old.ID, 'D', '未处理');
END IF;
end
//
DELIMITER ;
设计转换:
当然这里使用表输入,是为了让我们后续的操作(为了测试是否能够成功记录),被成功捕捉且流程清晰。
为了节省内存,我们甚至还可以直接记录变化后的数据,对变化数据进行冷备份(相当于叠乐高了)(parallal)
也可以通过二值分流,一个转换同时记录两个操作 (filiter)
二、数据清理
尝试整理了一下数据清洗的一些常见操作,但实际上,与数据科学中常用的一些操作没什么区别,增删改查,不过咱们的Spoon友好的将我们常用的一些函数方法进行了总结,写好了函数,将作业分为俩个部分,步骤(Steps)和跳(Hops),咱们只需要拿来点点点就能完成ETL操作,算是对写不来代码的人很友好了。
对应Kettle中的Step(步骤)组件:
2.1 字符串清理
2.1.1 正则表达式
普通字符
普通字符包括没有显式指定为元字符的所有可打印和不可打印字符。这包括所有大写和小写字母、所有数字、所有标点符号和一些其他符号。
字符 | 描述 |
---|---|
[ABC] | 匹配 […] 中的所有字符,例如 [aeiou] 匹配字符串 “google runoob taobao” 中所有的 e o u a 字母。![]() |
[^ABC] | 匹配除了 […] 中字符的所有字符,例如 [^aeiou] 匹配字符串 “google runoob taobao” 中除了 e o u a 字母的所有字母。![]() |
[A-Z] | [A-Z] 表示一个区间,匹配所有大写字母,[a-z] 表示所有小写字母。![]() |
. | 匹配除换行符(\n、\r)之外的任何单个字符,相等于 [^\n\r]。![]() |
[\s\S] | 匹配所有。\s 是匹配所有空白符,包括换行,\S 非空白符,不包括换行。![]() |
\w | 匹配字母、数字、下划线。等价于 [A-Za-z0-9_]![]() |
非打印字符
非打印字符也可以是正则表达式的组成部分。下表列出了表示非打印字符的转义序列:
字符 | 描述 |
---|---|
\cx | 匹配由x指明的控制字符。例如, \cM 匹配一个 Control-M 或回车符。x 的值必须为 A-Z 或 a-z 之一。否则,将 c 视为一个原义的 ‘c’ 字符。 |
\f | 匹配一个换页符。等价于 \x0c 和 \cL。 |
\n | 匹配一个换行符。等价于 \x0a 和 \cJ。 |
\r | 匹配一个回车符。等价于 \x0d 和 \cM。 |
\s | 匹配任何空白字符,包括空格、制表符、换页符等等。等价于 [ \f\n\r\t\v]。注意 Unicode 正则表达式会匹配全角空格符。 |
\S | 匹配任何非空白字符。等价于 [^ \f\n\r\t\v]。 |
\t | 匹配一个制表符。等价于 \x09 和 \cI。 |
\v | 匹配一个垂直制表符。等价于 \x0b 和 \cK。 |
特殊字符
所谓特殊字符,就是一些有特殊含义的字符,如上面说的 runoo*b 中的 *,简单的说就是表示任何字符串的意思。如果要查找字符串中的 * 符号,则需要对 * 进行转义,即在其前加一个 \,runo*ob 匹配字符串 runo*ob。
许多元字符要求在试图匹配它们时特别对待。若要匹配这些特殊字符,必须首先使字符"转义",即,将反斜杠字符\ 放在它们前面。下表列出了正则表达式中的特殊字符:
特别字符 | 描述 |
---|---|
$ | 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性,则 $ 也匹配 ‘\n’ 或 ‘\r’。要匹配 $ 字符本身,请使用 $。 |
( ) | 标记一个子表达式的开始和结束位置。子表达式可以获取供以后使用。要匹配这些字符,请使用 ( 和 )。 |
* | 匹配前面的子表达式零次或多次。要匹配 * 字符,请使用 *。 |
+ | 匹配前面的子表达式一次或多次。要匹配 + 字符,请使用 +。 |
. | 匹配除换行符 \n 之外的任何单字符。要匹配 . ,请使用 . 。 |
[ | 标记一个中括号表达式的开始。要匹配 [,请使用 [。 |
? | 匹配前面的子表达式零次或一次,或指明一个非贪婪限定符。要匹配 ? 字符,请使用 ?。 |
\ | 将下一个字符标记为或特殊字符、或原义字符、或向后引用、或八进制转义符。例如, ‘n’ 匹配字符 ‘n’。’\n’ 匹配换行符。序列 ‘\’ 匹配 “”,而 ‘(’ 则匹配 “(”。 |
^ | 匹配输入字符串的开始位置,除非在方括号表达式中使用,当该符号在方括号表达式中使用时,表示不接受该方括号表达式中的字符集合。要匹配 ^ 字符本身,请使用 ^。 |
{ | 标记限定符表达式的开始。要匹配 {,请使用 {。 |
| | 指明两项之间的一个选择。要匹配 |,请使用 |。 |
限定符
限定符用来指定正则表达式的一个给定组件必须要出现多少次才能满足匹配。有 * 或 + 或 ? 或 {n} 或 {n,} 或 {n,m} 共6种。
正则表达式的限定符有:
字符 | 描述 |
---|---|
* | 匹配前面的子表达式零次或多次。例如,zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。 |
+ | 匹配前面的子表达式一次或多次。例如,‘zo+’ 能匹配 “zo” 以及 “zoo”,但不能匹配 “z”。+ 等价于 {1,}。 |
? | 匹配前面的子表达式零次或一次。例如,“do(es)?” 可以匹配 “do” 、 “does” 中的 “does” 、 “doxy” 中的 “do” 。? 等价于 {0,1}。 |
{n} | n 是一个非负整数。匹配确定的 n 次。例如,‘o{2}’ 不能匹配 “Bob” 中的 ‘o’,但是能匹配 “food” 中的两个 o。 |
{n,} | n 是一个非负整数。至少匹配n 次。例如,‘o{2,}’ 不能匹配 “Bob” 中的 ‘o’,但能匹配 “foooood” 中的所有 o。‘o{1,}’ 等价于 ‘o+’。‘o{0,}’ 则等价于 ‘o*’。 |
{n,m} | m 和 n 均为非负整数,其中n <= m。最少匹配 n 次且最多匹配 m 次。例如,“o{1,3}” 将匹配 “fooooood” 中的前三个 o。‘o{0,1}’ 等价于 ‘o?’。请注意在逗号和两个数之间不能有空格。 |
Kettle中的正则表达式
2.1.2 字符串操作(12)
主要用到的三个组件,字符串操作(String Operation)、字符串替换、剪切字符串
其中字符串操作算是最整合的了,
字符串替换
如果要用空串进行替换,则需要设置[设置为空串]
剪切字符串
2.2 字段清理(13)
主要用的就是三个部分,字段拆合、字段选择(其余几个好像基本上就是三个组件的精细版)。
拆分字段
字段选择(重命名、字段选取、手动排序、删除、更改属性)
字段连接
2.3 数据校验
2.3.1 外部参照表校验数据(15)
在某些场合中,我们无法直接从当前表中识别出数据的问题,往往需要访问一些外部的一些参照数据来修正错误。例如输入的客户表有城市跟区域码,我们外界对这些往往是由有规定对应规则,所以为了判断数据的错误,我们往往就需要引入这些外部规则(也叫参照表)。
一般分为几步:
Step1:输入数据
Step2:查询数据(合并参照表和待校验表的交集)
Step3:计算相似度
Step4:输出
这里我们将采用一个流程的形式来具体展示参照表校验的整个过程:
感觉参照数据表本身不是很难,怎样定义匹配错位,也就是相似度不高的错误修正才是比较麻烦的,例如这里的流程,在相似度小于1大于0.65(这里是过滤步骤的条件)情况下,更多应该考虑到的是输入过程中出现了“笔误”,于是我们应该修改的是Cityname而不是之前的Code,因为这里即使是修改了code仍然是之前那个Cityname,没有任何意义。
因为这是一个项目流程,下面我们具体来看一下参照表的具体用法思路:
- 通过把参照表写入值映射的方式进行流查询:
- 流查询参照表
- 合并后查询参照表
- 不合并分流查询
- 用Switch/case进行分发参照表
要么分发参照表,要么分发数据流查询,换来换去就是这两个操作。
2.3.2 内部规则校验(16)
主要包含三个方面的规则校验
其中数据检验包含的功能:
- 类型校验
- 取值校验
- 空值校验- 枚举校验 (离散值)
- 范围校验 (最大最小值区间)
- 长度校验 (字符串最大最小字符数区间)
- 格式校验 (正则表达式 (默认使用字符串))
输出的校验结果:
然后可以通过过滤或者Switch/case的方法对各个问题进行纠错:
- 枚举校验 (离散值)
2.4 数据排序、去重(17)
数据去重主要有三种情况:
- 去除重复的数据
- 去除不完全重复的数据
- 去除完全重复的数据
第一种情况就是广义上的去重处理:
第二种去除不完全重复数据:
其中模糊匹配的设计方案:
第三种去除完全重复的数据:
三种方法本质上感觉是没有什么区别的。。。。方法都类似。
2.5 空值过滤
主要操作就是数据校验里的东西
2.6 JavaScript脚本(18)
没时间写了,我们期末不考,有空再写。
三、作业流程设计(20、21)
一个作业包括一个或多个作业项,这些作业项以某种顺序来执行。
作业执行顺序由作业项之间的跳(Hop)和每个作业项的执行结果来决定。
作业的组成
生成的主要文件为.kjb
(ketlle job)
作业(Job):
- 定时调度(重复执行)
- ->Start
- 设置全局变量
- 设置变量
- 搭建运行环境
- 脚本(SQL,shell,etc.), 文件操作,,
- 调用和执行转换(ktr)
- 子作业,子转换
- 监控和通知
- 日志,邮件
Kettle的转换原理,由Steps于Hops组成。生成的文件主要为.ktr
(kettle transform)
比较标准的作业流程(针对我们期末而言):
3.1 设置变量
方法1通过配置文件.properties
设置变量
方法2直接通过手动输入设置
3.2 执行SQL脚本
四、项目设计(平时作业、期中考试)
4.1 期中考试(11)
4.2 第二次作业(19)
4.3 19年期末测试及补考(22)
该文章的附件,也就是各个标题里的数字内容见我的资源里的kettleproject。