sql时间格式转换yyyymm_利用FME调用sql语句的方法及注意事项以增量数据坐标转换部署CDC为例...

    FME是一款强大的ETL工具，它具有 “集百家之所长，融百家之所思”的特点。除了不同软件平台的格式互连互转，FME还能做到功能上的一些共通共用，比如我们经常使用的数据库sql语句，FME就能很好的支持并且使用它，把它作为一个齿轮集成到我们整个ETL流程中来。

1.应用背景

    2018年应相关部门要求，全国各地转换了很多国土规划的历史存量数据到CGCS2000坐标系下，然而这种传统的存量一次性整体转换模式不能顺应我们日益增长的数据要求。因为规划设计方面还是主要使用城建坐标为主，而作为数据信息管理和审批等部门还是要求2000坐标系，因此需要我们对增量数据进行自动坐标转换和同步。

2.解决方案

       在此过程中，我们的需求主要有：多格式的支持、自动的坐标转换、增量数据的监测与记录、自动化的同步更新等，以及作为绝密的参数如何保密？

2.1实现思路

2.1.1.如何支持多种数据格式，包含矢量文件、栅格文件、空间数据库、非空间文本excel表等？

       对于多源异构数据的支持，FME是最好的解决方案，作为一个成熟的产品，支持各种我们常用的格式读写和转换。

2.1.2.坐标转换

       FME具有很多现成的转换器功能模块，能够完成各种坐标转换模型的参数计算和投影转换，并且能够从底层进行数据解析，可保证转换的图属无损。

2.1.3.转换参数保密

充分考虑坐标转换参数或控制点对的绝密性，支持通过外部应用程序或加密狗等安全措施存储坐标转换参数和控制点对，并控制授权许可。加密过程的架构设计如下图所示：

具体运行过程如下图所示：

可根据需求自行开发命令行程序，将控制点对或转换参数存储到加密狗的数据加密区，并通过加密狗或许可文件控制命令行程序的使用。

在FME坐标转换模板中调用命令行程序, 命令行程序通过标准控制台输入/输出向FME坐标转换模板动态传递控制点对和转换参数。如命令行程序检测到环境异常或未授权时，则拒绝向FME坐标转换模板传递控制点对和转换参数，FME坐标转换模板将终止执行。下图展示FME模板中调用系统Windows网络信息命令程序并获取输出结果。

如果命令行程序程序仅存储了控制点对信息，那么在FME坐标转换模板中可以使用控制点对实时计算转换参数(包括布尔莎七参数、二维七参数、二维四参数、多项式拟合系数等)，然后在用实时计算的转换参数坐坐标转换操作。下图展示从命令行程序中获取控制点对后实时计算布尔莎七参数进行坐标转换的过程：

上述所有过程和结果都在内存中计算，不在磁盘上做存储，充分保证控制点对和转换参数的安全性。

2.1.4. 数据库增量数据的监测与记录

数据库变化监测采用CDC+FME，以“发布”—“订阅”—“消费”的模式实现目标数据与源数据联动更新。如下图：

(1)CDC机制：捕获数据源变化，并将变化存储到发布空间

(2)FME机制：获取发布空间变化数据并同步实现数据转换到目标数据

CDC(Change DataCapture)是oracle在数据库级别实现的增量抽取解决方案。在一般的ETL过程中，对于增量抽取，无非是在数据上加时间截，全记录比对，关键字段比对，日志分析抽取等几种方法，要么需要修改原表结构，要么需要大量的算法，要么借助第三方的工具实现。Oracle从9i开始引入的CDC特性，使得有机会在数据库层面上直接实现增量抽取功能，在性能方面由于和数据库引擎的直接集成，比第三方工具应该具有一定的优势。

CDC有两个模式：同步和异步。两种模式的实现机制是截然不同的。同步CDC主要是采用触发器记录新增数据，基本能够做到实时增量抽取。而异步CDC则是通过分析已经commit的日志记录来得到增量数据信息，有一定的时间延迟，并且提供了到OracleStreams的接口。同步CDC在企业版或者标准版中都可以使用，异步CDC则只包含在企业版中。注意CDC在9i和10g中有了比较大的改变，异步CDC主要采用了和Streams相同的技术。

CDC中将系统分为两个角色：发布者和订阅者。发布者主要负责捕获增量数据，订阅者则将增量数据传递给实际应用。这些任务都可以通过oracle提供的PL/SQL包实现。

1)CDC捕获数据源的变化并记录到发布空间，通过扩展消费视图供订阅者消费，消费视图记录当前阶段变化(I/D/U)

2)CDC采用“发布”/“订阅”/“消费”模式，捕获到的变化相当于资源发布，需要订阅者来“消费”，如果没有“订阅者”，则不执行捕获，当所有“订阅者”都完成“消费”，发布空间的数据就不在保留。

原理示意图如下：

2.1.5.自动化的同步更新

利用Oracle CDC和FME Server 搭建数据变化监测和自动抽取分发的全自动化处理过程，同时实现分发过程中的坐标转换。

调用CDC(Change DataCapture)机制捕获变化数据，通过fme将变化数据转换为目标数据，技术路线图如下：

2.2FME实现流程

2.2.1部署cdc模板实现

       首先我们可以看下部署的的过程所使用的sql语句，其步骤流程为：1 创建表空间->2创建发布者用户并且附权限->3 创建变化集->4创建变化表 ->5 创建订阅者用户->6 创建订阅在订阅者用户下执行存储过程->7创建订阅视图，在订阅者用户下执行，后来在订阅者用户下通过查询该视图查看变化->8激活订阅在订阅者用户下执行->9刷新订阅视图

       以上过程，我们下面用FME来进行实现，主要用到了SQLExecutor转换器和PythonCaller转换器来写sql语句。

       这里涉及到的数据库连接参数、用户名、密码等参数都可以做成FME参数，方便后面调用，也方便用户运行的时候进行相关设置，如下图：

       使用SQLExecutor转换器写sql非常简单，只需要选择数据库的格式并且设置相应的连接参数和用户名(需要用到sys用户)、密码即可完成，唯一需要注意的就是当sql语句为多行时需要在第一行加上“FME_SQL_DELIMITER ;”这个命令，具体可参考帮助文档，如下图：

       之后我们可以将其它流程涉及到的语句一步步用SQLExecutor转换器来实现，如下图：

       基本上所有的语句都可以用SQLExecutor转换器来实现，除了用“GRANT EXECUTE ONDBMS_CDC_PUBLISH TO” 赋值cdc对象权限时会报错说权限不足，这时候我们需要用PythonCaller转换器来实现权限赋值，如下图：

       这里用到了cx_Oracle这个库，如果是FME2015及之前的版本是默认自带的，如果是FME2016及之后的版本就需要去安装了，最简单的安装方法为直接拷贝15的这个库过去，如下图：

当然能部署也能移除，移除的方法也可以用FME搭建模板来进行，如下图：

2.2.2同步更新和坐标转换模板

       部署好cdc后，如果数据库监控的表的要素发生了变化(增加、删除、更新)，那么相应的变化记录表会记下变化的时间、ID、变化的操作(增加I、删除D、更新UU)，然后每次刷新订阅，都会把最新变化记录放到消费视图中，然后我们可以用featurereader转换器获取这条记录原来的几何和属性，获取后利用转换器进行坐标转换和featurewriter转换器进行同步更新，最后把消费后的视图删掉，下次再刷新则是最新一次的记录了。

       最后我们把同步模板做好后，可以发布到FME Server，配置计划任务来进行定时自动触发，如果更新频率较高可以每几分钟运行一次如下图：

3.小节

       使用FME来进行多源异构数据整合和处理，可以很方便的实现对数据的连接(读写各种格式的业务数据等，统一格式和标准)、变换(空间几何变换、坐标系变换、属性结构变换等)、自动化(自动化的对数据进行处理)。上面我们介绍的功能只是用FME整合数据的冰山一角，实际上利用FME的格式与功能的扩展能力，能够快速灵活搭建出各种我们整合数据所需的功能。