oracle数据迁移

步骤1:导出dmp文件

  • 假设一个用户为hubble,密码也为hubble,目录为/home/oracle/dump,并且用户拥有读写(read/write)权限。
  • 导出dmp文件(主要是表结构和视图)
expdp hubble/hubble directory=directory_name dumpfile=oracle_example.dmp content=metadata_only logfile=example.log

步骤2:将dmp文件转换为sql格式

impdp hubble/hubble directory=directory_name dumpfile=oracle_example.dmp sqlfile=example_sql.sql TRANSFORM=SEGMENT_ATTRIBUTES:N:table PARTITION_OPTIONS=MERGE

导出后的sql文件,包括了用户所拥有的的权限,建表语句、主键、索引、表之间的主外键关系,视图等一些信息。但是对应到hubble上的话仍然需要修改,注意事项包括:

  • 1.表的主键和表之间的主外键关系在create table语句中创建。

  • 2.视图创建去掉FORCE关键字。

  • 3.oracle数据类型转换成hubble的数据类型。

  • 4.注释语句,索引语句可以继续使用。

步骤3:导出表数据

  • 需要将每个表的数据提取到数据列表文件(.lst)中,编写SQL脚本(spool.sql)来执行此操作:
cat spool.sql
SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET TRIMSPOOL ON
SET WRAP OFF
set linesize 30000
SET RECSEP OFF
SET VERIFY OFF
SET ARRAYSIZE 10000
SET COLSEP '|'
SPOOL '&1'
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
SELECT * from &1;
SPOOL OFF
SET PAGESIZE 24
SET FEEDBACK ON
SET TERMOUT ON
  • 用两张表举例cust_info和trans
create table cust_info(cust_no varchar2(30) primary key,cust_name varchar2(30) ,cust_card_no  varchar(18),age  number);
create table trans (cust_id varchar2(20) primary key,cust_no varchar2(30),trans_date date,trans_aml number(10,2));
  • 要提取数据,登录sqlplus
cd /home/oracle/dump
sqlplus hubble/hubble
  • 传入参数为表名,且表中必须包含数据,不能为空
SQL> @spool cust_info
SQL> @spool trans

退出SQL * Plus:

EXIT
  • 查看是否存在.lst文件
[oracle@zhangdb dump]$ ls *.lst
cust_onfo.lst  trans.lst

步骤4:配置表数据并将其转换为CSV

每个表的数据列表文件都需要转换为CSV并针对hubble进行格式化。我们编写了一个简单的Python脚本(fix-example.py)来执行此操作:

cat fix-example.py
import csv
import string
import sys

for lstfile in sys.argv[1:]:
  filename = lstfile.split(".")[0]

  with open(sys.argv[1]) as f:
    reader = csv.reader(f, delimiter="|")
    with open(filename+".csv", "w") as fo:
      writer = csv.writer(fo)
      for rec in reader:
        writer.writerow(map(string.strip, rec))
  • 执行python脚本,转换csv文件
python fix-example.py cust_info.lst trans.lst
  • 查看是否存在.csv文件
[oracle@zhangdb dump]$ ls *.csv
cust_info.csv  trans.csv

步骤5:Oracle映射到hubble数据类型

  • 使用之前步骤二生成的SQL文件,编写IMPORT TABLE与要导入的表数据的模式匹配的语句。

  • 删除所有特定于Oracle的属性,重新映射所有Oracle数据类型,重构所有CREATE TABLE语句。

数据类型映射

  • 使用下表进行数据类型映射:
Oracle数据类型hubble数据类型
BLOBBYTES
CHAR(n),CHARACTER(n)n <256CHAR(n),CHARACTER(n)
CLOBSTRING
DATEDATE
FLOAT(n)DECIMAL(n)
INTERVAL YEAR(p) TO MONTHVARCHAR, INTERVAL
INTERVAL DAY(p) TO SECOND(s)VARCHAR, INTERVAL
DOUBLEDECIMAL(m,n)
JSONJSON
LONGSTRING
LONG RAWBYTES
NCHAR(n)n <256CHAR(n)
NCHAR(n)n> 255VARCHAR, STRING
NCLOBSTRING
NUMBER(p,0),NUMBER(p)1 <= p <5INT2
NUMBER(p,0),NUMBER(p)5 <= p <9INT4
NUMBER(p,0),NUMBER(p)9 <= p <19INT8
NUMBER(p,0),NUMBER(p)19 <= p <= 38DECIMAL(p)
NUMBER(p,s) s> 0DECIMAL(p,s)
NUMBER, NUMBER(*)DECIMAL
NVARCHAR2(n)VARCHAR(n)
RAW(n)BYTES
TIMESTAMP(p)TIMESTAMP
TIMESTAMP(p) WITH TIME ZONETIMESTAMP WITH TIMEZONE
VARCHAR(n), VARCHAR2(n)VARCHAR(n)
XMLJSON
  • BLOBSCLOBS应将其转换为BYTES,或者STRING大小可变的位置,但建议将值保持在1MB以下,以确保性能。1MB以上的任何内容都将需要重构到对象存储中,并在表中嵌入一个指针来代替对象。
  • JSONXML类型可以转换为JSONB使用任何XMLJSON的转换。在导入hubble之前XML必须将其转换为JSONB
  • 转换时NUMBER(p,0),请考虑NUMBER将Base-10限制的INT类型映射到hubble 类型的Base-10限制,NUMBERS可以转换为DECIMAL

步骤6:数据导入

  • 以上述oracle两张表cust_info和trans为例在hubble数据库中建表,对应字段类型参考步骤五的数据类型映射表
create table  cust_info(cust_no varchar(40) primary key,cust_name varchar(40) ,cust_card_no  varchar(20),age  DECIMAL);
create table trans (cust_id varchar(20) primary key,cust_no varchar(30),trans_date timestamp,trans_aml DECIMAL(10,2));
  • 将csv文件放置集群在可访问的位置
[hubble@poc-hubble01 ~]$ cd /data_shares/cus
[hubble@poc-hubble01 cus]$ ls
cust_info.csv  trans.csv
  • 导入cust_info
IMPORT into cust_info ( cust_no ,cust_name, cust_card_no, age
) CSV DATA ( 'nodelocal://1/cus/cust_info.csv' ) 
WITH 
    DELIMITER = ','
;
        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+--------
  748594745725714433 | succeeded |                  1 |    2 |             0 |   114
root@poc-hubble01:35432/oracle> select * from cust_info;
    cust_no   | cust_name |    cust_card_no    | age
--------------+-----------+--------------------+------
  a2327818434 | 张三      | 130224195405256540 |  19
  a8723518461 | 马超      | 110224199905216541 |  15
(2 rows)
  • 导入trans
IMPORT into trans ( cust_id ,cust_no,trans_date, trans_aml
) CSV DATA ( 'nodelocal://1/cus/trans.csv' ) 
WITH 
    DELIMITER = ','
;
        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+--------
  748595201171456001 | succeeded |                  1 |    2 |             0 |   102
root@poc-hubble01:35432/oracle> select * from trans;
    cust_id    |   cust_no   |     trans_date      | trans_aml
---------------+-------------+---------------------+------------
  saae22324434 | a2343355545 | 2013-02-26 11:07:25 |    123.40
  saae22324489 | a2343355545 | 2013-02-26 12:07:00 |    523.40
(2 rows)

步骤六可参考CSV数据迁移