工具环境说明
操作系统:Windows10业务数据库:MySQL8.0.17数仓数据库:Oracle 12c Enterprise Edition Release 12.2.0.1.0设计工具:Excel 2016 power query设计过程说明
1、创建表 首先,我们在Excel里创建两个表(必须是Ctrl+T创建的表,点击表内容时你可以在菜单栏里看到表格工具) 表1当中我们复制MySQL生成的建表语句表2当中我们存储需要创建的表的名称 2、加载表然后我们点击表1任意位置,点击数据菜单栏,点击从表格,将表1的数据加载到query当中 3、清除前后空格接下来,我们首先将sql语句中的字段名称分离出来。先利用Power Query操作窗口里转换菜单里的格式中的修整(可以清除所选列的单元格前后空格)工具将单元格前后空格清除掉。这样可以保证表字段名称都在最左侧。 4、分离字段名称要想分离出字段名称,我们可以使用转换-拆分列-按分隔符完成此操作。因为在SQL里,字段名称跟后面的内容都是空格分隔的。
选择按分隔符拆分列时,请记得选择分隔符为“空格”,拆分位置为“最左侧的分隔符”,高级选项中默认就行。 分离出字段名称之后再通过修整-按字符分列,将数据类型分离出来。 5、分离字段空值设置分离出数据类型之后,后面的内容是是否空值以及字段名别称的设置了。
根据观察,字段空值类型有null和not null两种,所以我们不能再使用上面的空格拆分列了。
我们还是先把单元格的前后空格进行修整,确保null、not null位于单元格开头。
我们需要首先将 null进行字符替换,替换为一个 唯一 字符,用来进行拆分 列使用。当然具体选择哪个字符来进行拆分列,大家可以随意即可,逗号、句号、分号、斜杠、反斜杠等都可。只要能保证拆分列能正常完成即可。 选择转换-替换值,查找值为null,替换为null/ 但是你会发现,完成这个操作之后,并没有看到结果如我们所愿,null被替换被null/我们仔细观察query编辑器数据区域上面的编辑栏中的公式,可以发现在公式中的第二个参数它的值是null, 这里它会把null当做为系统的空值来看待,而不是我们想要替换的null字符值。= Table.ReplaceValue(去除的文本2,null,"null/",Replacer.ReplaceValue,{"列1.1"})
那么我们只需要把null
改成"null
"
即可!
= Table.ReplaceValue(去除的文本2,"null ","null/",Replacer.ReplaceText,{"列1.2.2"})
ps:这里我直接把null后面的空格一起替换了,免得一会还要做修整操作。不过这里怎么操作都行。替换null之后,我们再利用按字符拆分列(这里我是用字符“/”)将null拆分出来
6、终结建表语句
在MySQL的建表语句中,字段别名是直接comment "字段别名"的形式放在了字段设置里。而oracle的字段别名的语句是在建表语句之外,单独通过建表语句之后的comment on语句来指定每个字段的别名。
-- Mysql建表语句create table table_name ( filed1 varchar(20) not null comment '主键' primary key, filed2 int null comment '字段2', filed3 varchar(20) null comment '字段3' )-- Oracle建表语句create table table_name ( field1 varchar2(20) not null primary key, filed2 int null, filed3 varchar2(20) null)/comment on column table_name.field1 is '主键'/comment on column table_name.field2 is '字段2'/comment on column table_name.field3 is '字段3'
因此在清洗字段别名之前我们需要先给每个字段的语句来个终结,因此,我们需要再把"null"替换为"null,"。当然,简单点,我们可以直接把刚才null替换值的那一步进行修改。把"null "直接替换为"null,/",这样我们拆分列之后,null后面就直接带上了逗号。
= Table.ReplaceValue(去除的文本2,"null ","null,/",Replacer.ReplaceText,{"列1.2.2"})
7、修改字段名别名的设置
因为Oracle别名设置语句的特殊,我们需要先把comment拆分出来。
下一步,我们需要将表名关联过来。新建自定义列,让它值为1 同样的,我们把表名的那个表2,通过Excel的菜单栏-数据-从表格加载到query编辑器中。同样给它添加一列自定义列,值为1。 然后选择查询中的表1,选择合并查询-合并查询,如图设置表1和表2的关联字段,选择联接种类为左外部。 点击确定之后, 会在 原来的 表中产生 新的一列,其中的值都是Table。点击单元格(不是点击Table),我们可以在下方看到这个Table中的数据。 点击这列名称右侧的符号,如下图所示,进行展开操作。我们只选择tablename列进行展开 展开之后,我们可以看到生成了新的一列-"tablename"。 由于Oracle的comment语句使用"/"来进行语句分割,因此我们首先将原来字段别名之后的","替换为"/"。我使用"',"来替换,是因为主键的逗号其实在下一行上了,我们无需多余给它一个逗号结束。 替换完成之后,我们新建自定义列(在菜单添加列-自定义列),列中我们输入以下公式。# 我们使用query的if then else 语句if [列1.1] <> "comment" then [列1.2.2.2.1] & " on column " & [tablename] & "."&[列1.1]& " is " & [列1.2.2.2.2]else "/ " & [列1.1] & " on table " & [tablename] & " is " & [列1.2.1]
点击确定我们就可以将comment 语句生成了
8、字段类型替换
因为MySQL和Oracle的字段类型不完全一致,我们需要点击转换中的替换值进行数据类型替换。
具体的类型转换大家可以网上搜索一下,大致如下所示
MySQL Data Type | Oracle Data Type |
---|---|
BIGINT | NUMBER(19, 0) |
BLOB | BLOB, RAW |
CHAR | CHAR |
DATE | DATE |
DATETIME | DATE |
DECIMAL | FLOAT (24) |
DOUBLE | FLOAT (24) |
FLOAT | NUMBER |
INT | NUMBER(10, 0) |
INTEGER | NUMBER(10, 0) |
LONGBLOB | BLOB, RAW |
LONGTEXT | CLOB, RAW |
NUMERIC | NUMBER |
SMALLINT | NUMBER(5, 0) |
TEXT | VARCHAR2, CLOB |
TIME | DATE |
TIMESTAMP | DATE |
TINYINT | NUMBER(3, 0) |
TINYTEXT | VARCHAR2 |
VARCHAR | VARCHAR2, CLOB |
数据类型替换之后我们将多余的列删除掉。选中要删除的列点击delete或者选中删除的列点击主页菜单中的删除列。
9、上载清洗结果完成清洗过程之后,我们点击主页菜单的关闭并上载将清洗结果加载到Excel当中。
上载之后我们就可以直接复制前3列数据到navicat、datagrip等工具中进行建表操作了。当然,直接复制出来可能会有错误,需要稍作修改即可。主要是最后一个字段后面的逗号和primary key前面的逗号。 10、不断修正精进清洗过程 要想让这个清洗结果更加准确,我们需要使用多个表的建表语句来进行测试,不断调整其中的清洗过程,比如默认值的设置,特殊数据类型的替换等等。 有了这个工具,以后我们只需要复制MySQL的DDL语句,然后点击刷新就能得到oracle的DDL语句了。再也不用挨个去修改了,大大提升了效率减少了工作量。 Happy!!!一劳永逸!