使用外部表关联MySQL数据到Oracle

因为业务需要,有个临时的活动需要DBA来支持一些数据业务,问题来了,需要从MySQL端同步一部分数据到Oracle端,然后从Oracle端匹配查到相应的数据返回给MySQL,至于原因,也是不同的业务系统,不同的权限分配,还没法做到一个应用端去读取这些信息,而且也有安全的考虑,大体就是两部分的数据也是互相补充,但又彼此独立,是一个全集和子集的关系。
这个流程本来从开发的角度来看似乎是一头雾水,所以交给他们来规划就容易出现问题,最后沟通后的流程是下面的形式。
下面这个图左边是Oracle的环境,右边是MySQL的环境,两个环境的表中都存在一个共同的字段就是序列号serial_no,而且MySQL段的序列号是Oracle端的子集,两者是存在一一映射关系的。

现在的问题是MySQL端可以提供uid,但是无法得知cn_number,因为这部分信息在Oracle端。Oracle端又没有uid的概念,所以需要MySQL端提供serial_no来映射才可以。
所以一来二去,得到的流程就是需要5个步骤。
首先开发部门提供需要的uid(1),然后MySQL端抽取后把文件同步到Oracle端(2),然后在Oracle端进行关联查询,得到一个uid和cn_number的组合(3),然后同步到MySQL端,
最后MySQL端得到这部分数据,最终开发的需求就完成了。

很快就得到了MySQL端同步过来的数据,是个本文文件,内容如下:
687914 | 0d6c3956-d53d-4e14-9fba-cb73cec661e6
694786 | 41159bb3-970b-4b6b-9c5d-46e1f3d388be
746010 | 011d632d-149a-4e3d-ad00-dcae53f60825
1226533| 78dd80d3-6ad0-4bd5-aa1d-843c32b7ddab
1399846| 3dcc5982-bcc7-4cbf-9f99-b5a51b932b1d
1400221| 4fc505eb-20a6-451c-8674-5667e33167e7
因为推送过来的表的数据可能会有变化的,但是每次都去更新表的数据还是有些繁琐,一个方便的办法就是外部表了。
CREATE TABLE passport_ext
      (uid    number,
       serial_no  varchar2(100))
    ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_dir
      ACCESS PARAMETERS
        (
        FIELDS TERMINATED BY '|'        
        )
      LOCATION ('mysql_uid_serial_no.lst')
     );
所以一旦MySQL端推送文件过来了,我只需要替换文本文件即可,然后就不用反复做数据的导入了。
直接拿过来关联即可。
但是创建外部表的时候老师抛错,最后发现uid是保留字,用下面的例子来验证。
>  CREATE TABLE passport_ext (uid varchar2(10));
 CREATE TABLE passport_ext (uid varchar2(10))
                                          *
ERROR at line 1:
ORA-00904: : invalid identifier
>  CREATE TABLE passport_ext (uidd varchar2(10));
Table created.
其实后面经过老猫指点,还是可以用"uid"来代替的,这个用法就跟MySQL里面的反引号类似了。
create table test1(`int` int);
Query OK, 0 rows affected (0.00 sec)
不过这个时候还是要注意。下面的输出结果,其实如果用双引号,还是不规范的,而且需要应用端去修改,这样就是一个隐藏的雷。多谢怀总指点。
SQL> create table test("uid" number);
Table created.
SQL> select uid,UID,"uid" from test;
UID UID uid
---------- ---------- ----------
0 0 1
所以在数据导入之后还是最好把字段名改过来,我就直接改成了uidd,因为字段名表关联没有强制要求uid这个列名。
关于保留字可以通过下面的方式来查询
SQL> select * from v$reserved_words where keyword='UID';
外部表加载了之后,关联的时候发现竟然没有匹配的数据,最后发现还是得trim一下数据
select t1.uidd ,t2.cn_number from passport_ext t1,passport t2 where trim(t1.serial_no)=t2.seriao_no;
通过这种方式就得到了一个数据清单,可以再次推送给MySQL端了。
这个案例还是很简单的,但是把这个过程做了多步的分解,可以看出在数据迁移中还是有很多的潜在因素需要考虑。

时间: 2024-09-29 09:46:11

使用外部表关联MySQL数据到Oracle的相关文章

sql 数据库 如何使用update set 解决多表关联修改数据

问题描述 sql 数据库 如何使用update set 解决多表关联修改数据 A表: stuid testid 1 2 3 4 5 1 1 2 1 3 1 1 2 2 2 3 2 B表: stuid testid 1 2 3 4 5 1 1 A B C D A 2 1 B B C A A 3 1 C D A A B 1 2 C C D D D 2 2 B A C A A 3 2 D A D C D C表: testid stid daan fs 1 1 A 3 1 2 B 3 1 3 C 3 1

mysql中多表关联删除数据时同时删除关联数据

category(栏目信息表)和news(新闻数据表). category中的id(栏目编号)字段作为该表的主键(primary key).唯一标识了一个栏目的信息. news 中的id字段作为该表的主键(primary key).唯一标识了一个栏目的信息. category_id(栏目编号)字段与category表的id字段相关联. 1.SQL删除语句  代码如下 复制代码 sql = "delete category,news from category left join news on

mysql表关联只取关联表中最近一条数据

问题描述 mysql表关联只取关联表中最近一条数据 表A{id,userId,logTime} 表B{id,userId,departmentId,updateTime} 表A为数据记录 表B相当于历史记录表(userId在updateTime时间之前的departmentId) 现在表A与表B关联 但是只能让表B中符合条件的最近一记录关联上 (也是updateTime在logTime之前并且最近的一条) 我是这么做的 SELECT A.id,B.userId,B.departmentId FR

ORACLE外部表总结

外部表介绍   ORACLE外部表用来存取数据库以外的文本文 件(Text File)或ORACLE专属格式文件.因此,建立外部表时不会产生段.区.数据块等存储结构,只有与表相关的定义放在数据字典中.外部表,顾名思义,存 储在数据库外面的表.当存取时才能从ORACLE专属格式文件中取得数据,外部表仅供查询,不能对外部表的内容进行修改(INSERT.UPDATE. DELETE操作).不能对外部表建立索引.因为创建索引就意味着要存在对应的索引记录.而外部表其实在没有存储在数据库中.故在外部是无法建

Oracle中创建临时表、外部表和分区表实例

临时表 在Oracle中,临时表是"静态"的,它与普通的数据表一样只需要一次创建,其结构从创建到删除的整个期间都是有效的.相对于其他类型的表,临时表只有在用户实际向表中添加数据时,才会为其分配空间,并且分配的空间来自临时表空间.这就避免了与永久对象的数据争用存储空间. 创建临时表的语法如下: CREATE GLOBAL TEMPORARY TABLE table_name( column_name data_type,[column_name data_type,...] )ON CO

Oracle 外部表

--================= -- Oracle 外部表 --=================       外部表只能在Oracle 9i 之后来使用.简单地说,外部表,是指不存在于数据库中的表.通过向Oracle提供描述外部表的元数据,我们 可以把一个操作系统文件当成一个只读的数据库表,就像这些数据存储在一个普通数据库表中一样来进行访问.外部表是对数据库表的延伸.   一.外部表的特性     位于文件系统之中,按一定格式分割,如文本文件或者其他类型的表可以作为外部表.     对

使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)

--================================================ -- 使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG) --================================================       Oracle 告警日志时DBA维护数据库经常需要关注的一部分内容.然而告警日志以文本文件,按时间的先后顺序不断累积的形式来存储,久而 久之,势必造成告警日志的过大,难于维护和查找相关的信息.使用外表表方式来

海量数据迁移之外部表并行抽取

在10g开始的新特性中,外部表是一个不容忽视的好工具.对于大型项目中海量数据使用sqlloader是一种全新的方式,不过很明显,sqlloader的可扩展性更强,但是基于oracle平台的数据迁移来说,外部表的性能也不错.对于数据迁移来说也是一个很好的方案. 使用外部表来做数据迁移,可以"动态"加载数据,能够很方便的从数据库中加载数据,对于数据校验来说就显得很有优势了,而对于sqlloader来说,可能得等到数据加载的时候才知道是不是有问题,如果对于数据的准确性要求极高,可以使用外部表

Oracle中利用数据泵导出查询结果(二) 外部表的卸载功能

还是上一篇中的测试环境: SQL> CREATE TABLE T1 2  (ID NUMBER, NAME VARCHAR2(30)); Table created. SQL> INSERT INTO T1 2  SELECT ROWNUM, TNAME 3  FROM TAB; 66 rows created. SQL> CREATE TABLE T2 2  (ID NUMBER, NAME VARCHAR2(30)); Table created. SQL> INSERT IN