Oracle 数据迁移到Hubble数据库

Oracle 数据迁移

Oracle映射到hubble数据类型

使用之前创建的SQL文件，编写IMPORT TABLE与要导入的表数据的模式匹配的语句。
删除所有特定于Oracle的属性，重新映射所有Oracle数据类型，重构所有CREATE TABLE语句以包括主键。
使用下表进行数据类型映射：

Oracle数据类型	hubble数据类型
BLOB	BYTES 1个
CHAR(n)，CHARACTER(n)n <256	CHAR(n)，CHARACTER(n)
CLOB	STRING 1个
DATE	DATE
FLOAT(n)	DECIMAL(n)
INTERVAL YEAR(p) TO MONTH	VARCHAR， INTERVAL
INTERVAL DAY(p) TO SECOND(s)	VARCHAR， INTERVAL
JSON	JSON 2
LONG	STRING
LONG RAW	BYTES
NCHAR(n)n <256	CHAR(n)
NCHAR(n)n> 255	VARCHAR， STRING
NCLOB	STRING
NUMBER(p,0)，NUMBER(p)1 <= p <5	INT2 3
NUMBER(p,0)，NUMBER(p)5 <= p <9	INT4 3
NUMBER(p,0)，NUMBER(p)9 <= p <19	INT8 3
NUMBER(p,0)，NUMBER(p)19 <= p <= 38	DECIMAL(p)
NUMBER(p,s) s> 0	DECIMAL(p,s)
NUMBER， NUMBER(*)	DECIMAL
NVARCHAR2(n)	VARCHAR(n)
RAW(n)	BYTES
TIMESTAMP(p)	TIMESTAMP
TIMESTAMP(p) WITH TIME ZONE	TIMESTAMP WITH TIMEZONE
VARCHAR(n)， VARCHAR2(n)	VARCHAR(n)
XML	JSON 2

BLOBS，CLOBS应将其转换为BYTES，或者STRING大小可变的位置，但建议将值保持在1 MB以下，以确保性能。1 MB以上的任何内容都将需要重构到对象存储中，并在表中嵌入一个指针来代替对象。
JSON，XML类型可以转换为JSONB使用任何XML到JSON的转换。在导入hubble之前XML必须将其转换为JSONB。
转换时NUMBER(p,0)，请考虑NUMBER将Base-10限制的INT类型映射到hubble 类型的Base-10限制。（可选）NUMBERS可以转换为DECIMAL。

导出Oracle模式

expdp user/password directory=datapump dumpfile=oracle_example.dmp content=metadata_only logfile=example.log

将Oracle模式转换为SQL

impdp user/password directory=datapump dumpfile=oracle_example.dmp sqlfile=example_sql.sql TRANSFORM=SEGMENT_ATTRIBUTES:N:table PARTITION_OPTIONS=MERGE

导出表数据

您需要将每个表的数据提取到数据列表文件（.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'
SPOOL '&1'
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';  # 适用于Hubble的日期格式
SELECT * from &1;
SPOOL OFF

SET PAGESIZE 24
SET FEEDBACK ON
SET TERMOUT ON

要提取数据，我们为SQL * Plus中的每个表运行了脚本：

$ sqlplus user/password
@spool CUSTOMERS 
@spool ADDRESSES  
@spool CARD_DETAILS  
@spool WAREHOUSES  
@spool ORDER_ITEMS  
@spool ORDERS  
@spool INVENTORIES  
@spool PRODUCT_INFORMATION  
@spool LOGON  
@spool PRODUCT_DESCRIPTIONS  
@spool ORDERENTRY_METADATA

.lst为每个表创建一个带有前导和尾随空格的数据列表文件（）。

退出SQL * Plus：

>EXIT

配置表数据并将其转换为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))

python3 fix.py CUSTOMERS.lst ADDRESSES.lst CARD_DETAILS.lst WAREHOUSES.lst ORDER_ITEMS.lst ORDERS.lst INVENTORIES.lst PRODUCT_INFORMATION.lst LOGON.lst PRODUCT_DESCRIPTIONS.lst ORDERENTRY_METADATA.lst

CSV数据导入

CSV格式数据准备

您将需要为每个表导出一个CSV文件，并具有以下要求：

文件必须为有效的CSV格式，但请注意，分隔符必须为单个字符。要使用逗号以外的其他字符（例如制表符），请使用delimiter option设置自定义分隔符。
文件必须为UTF-8编码。
如果字段中出现以下字符之一，则该字段必须用双引号引起来：

 分隔符（,默认情况下）
  双引号（"） 
  换行符（\n）
  回车（\r）

4.如果使用双引号将字段括起来，则必须在字段内部出现双引号，然后在其前面加上另一个双引号，以对其进行转义。例如：”aaa”,”b””bb”,”ccc”

5.如果列是类型BYTES，则它可以是有效的UTF-8字符串，也可以是以开头的十六进制编码的字节常量\x。例如，一个字段，其值应是字节1，2将被写为\x0102。

将数据文件放置在集群可访问到的位置

URL必须使用以下格式:

[scheme]://[host]/[path]?[parameters]

类型	schema	host	参数	示例
http	http	主机地址	N/A	http://localhost:8080/mydatest.sql
NFS/Local	nodelocal	节点ID或为空	N/A	nodelocal:///path/mydatest,nodelocal://2/path/mydatest

hubble库中不存在的表的数据迁移

IMPORT TABLE employees (    
emp_no INT PRIMARY KEY,
birth_date DATE NOT NULL,
first_name STRING NOT NULL,
last_name STRING NOT NULL, 
 gender STRING NOT NULL,
 hire_date DATE NOT NULL ) CSV DATA ('http://localhost:3000/employees.csv.gz');
例如:
root@tytest16:26257/hubble_demo> IMPORT TABLE employees (
                              ->     emp_no INT PRIMARY KEY,
                              ->     birth_date DATE NOT NULL, 
                             ->     first_name STRING NOT NULL,
                              ->     last_name STRING NOT NULL,
                              ->     gender STRING NOT NULL,
                              ->     hire_date DATE NOT NULL
                              ->   ) CSV DATA ('http://localhost:3000/employees.csv.gz');
job_id             | status |fraction_completed| rows |index_entries| system_records | bytes    +-----------------+--------+------------------+------+-------------+---------------+--------+  
535422568764833793|succeeded|                 1 |300024|            0 |              0 | 12134341

(1 row)
Time: 3.078573146s

hubble库中已存在表的数据迁移

IMPORT INTO employees (emp_no, birth_date,first_name,last_name,gender,hire_date) CSV DATA ('http://localhost:3000/employees.csv.gz')

1） 配置选项

以下选项可用于IMPORT … CSV：

2）列定界符

该delimiter选项用于设置Unicode字符，该字符标记每列的结尾。默认值：,。

用法示例：

IMPORT TABLE employees (
emp_no INT PRIMARY KEY,
birth_date DATE NOT NULL,
first_name STRING NOT NULL,
last_name STRING NOT NULL,
gender STRING NOT NULL,
hire_date DATE NOT NULL
)  CSV DATA ('http://localhost:3000/employees.csv.gz') WITH delimiter = e'\t';

root@tytest16:26257/hubble_demo> IMPORT INTO employees (emp_no, birth_date,first_name,last_name,gender,hire_date) CSV DATA ('http://localhost:3000/employees.csv.gz');
        job_id       |  status   | fraction_completed |  rows  | index_entries | system_records |  bytes    +--------------------+-----------+--------------------+--------+---------------+----------------+----------+  535428382456872961 | succeeded |                  1 | 300024 |             0 |              0 | 12134341
(1 row)
Time: 5.768633441s

3）注释语法

comment选项确定哪个Unicode字符标记数据中要跳过的行。

用法示例：

IMPORT TABLE employees (
emp_no INT PRIMARY KEY,
 birth_date DATE NOT NULL,
first_name STRING NOT NULL,
last_name STRING NOT NULL,
gender STRING NOT NULL,
hire_date DATE NOT NULL
)  CSV DATA ('http://localhost:3000/employees.csv.gz') WITH  comment = '#';

4） 跳过标题行

skip选项确定导入文件时要跳过的标题行数。

用法示例：

IMPORT TABLE employees (
emp_no INT PRIMARY KEY,
birth_date DATE NOT NULL,
first_name STRING NOT NULL,
last_name STRING NOT NULL,
gender STRING NOT NULL,
hire_date DATE NOT NULL
)  CSV DATA   ('http://localhost:3000/employees.csv.gz') WITH skip = '2';

5）空字符串

nullif选项定义应将哪个字符串转换为NULL。

用法示例：

IMPORT TABLE employees (
emp_no INT PRIMARY KEY,
birth_date DATE NOT NULL,
first_name STRING NOT NULL,
last_name STRING NOT NULL,
gender STRING NOT NULL,
hire_date DATE NOT NULL  
)  CSV DATA ('http://localhost:3000/employees.csv.gz') WITH nullif = '';

6）文件压缩格式

compress选项用于指定需导入的csv文件压缩格式，默认不使用压缩格式

可选的压缩格式包括：gzip，bzip，none

IMPORT TABLE employees (
emp_no INT PRIMARY KEY,
birth_date DATE NOT NULL,
first_name STRING NOT NULL,
last_name STRING NOT NULL,
gender STRING NOT NULL,
hire_date DATE NOT NULL
)  CSV DATA   ('http://localhost:3000/employees.csv.gz') WITH compress = 'gzip';

SQL性能最佳实践

多行DML最佳做法

使用多行DML替代多个单行DML

对于INSERT，UPSERT和DELETE语句，单个多行DML比多个单行DML快。尽可能的使用多行DML来取代多个单行DML。

使用TRUNCATE而不是DELETE来删除表中的所有行

TRUNCATE语句通过删除表并重新创建相同名称的新表来删除表中的所有行。比使用DELETE要好很多，DELETE需要执行多个事务以删除所有行。但是，对于较小的表（比如行数少于1000），使用不带WHERE子句的DELETE语句会比使用TRUNCATE的性能更好。

批量插入最佳做法

使用多行INSERT语句对现有表进行批量插入

将数据批量插入到现有表中，请在一个多行INSERT语句中批处理多行，并且不要在事务中包括INSERT语句。通过监视不同批次大小（10行，100行，1000行）的性能，通过实验确定适合您的应用程序的最佳批次大小。也可以使用IMPORT INTO语句将CSV数据批量插入到现有表中。

使用IMPORT而不是INSERT在新表中批量插入

要将数据批量插入到新表中，IMPORT语句的性能要优于INSERT。

批量删除最佳做法

Hubble依靠多版本并发控制（MVCC）处理并发请求，同时确保强大的一致性。因此，删除行时不会立即将其从磁盘中删除。该行的MVCC值将一直保留，直到经过适用的区域配置垃圾收集时间为止。默认情况下，此期限为25小时。

如果使用默认设置，DELETE语句的每次迭代都必须扫描过去25小时内先前标记为删除的所有行。所以如果尝试在同一25小时内删除1,000行10次，则第10条命令将必须扫描先前标记为删除的9,000行。

如果您需要以固定的时间迭代地删除行，则可以更改区域配置改为一个较低的值（例如5分钟，然后每个间隔运行一次DELETE语句。但建议完成大量删除操作后，恢复为默认值。

重构应用程序SQL

迁移过程的最后一个阶段是更改应用程序的事务行为和SQL方言。

1）事务，锁定和并发控制

Oracle和Hubble都支持多语句事务，这些事务是原子的并保证ACID语义。但是，Hubble在可序列化的隔离模式下运行，而Oracle默认将其读取为提交，当事务两次读取数据时，它可以创建不可重复的读取和幻像读取。

关于锁，Hubble利用轻量级闩锁来序列化并发事务中对公共密钥的访问。 Oracle和Hubble事务控制流只有一些细微的差别。

由于Hubble不允许可序列化的异常，因此事务可能会遇到死锁或读/写争用。在相同键的并发期间，这可能会发生。这些可以通过自动重试或客户端干预技术来解决。

2）SQL方言

Hubble是与Postgres方言兼容的ANSI SQL，它使您可以使用本机驱动程序将应用程序和ORM连接到Hubble。

如果要迁移到Hubble数据库上，需要重构Oracle SQL和不符合ANSI SQL-92的函数。参见附录一。

Oracle需要使用DUAL表，因为Oracle需要SELECT … FROM。在Hubble中，应删除所有对DUAL表的引用。

Hubble支持NESTLOOP、HASH，MERGE和LOOKUP连接。 Oracle对LEFT和RIGHT连接使用+运算符，但是Hubble使用ANSI连接语法。

3）序列(Sequences)

Hubble中的序列不需要触发即可自我增加；将序列放在表DDL中：

> CREATE TABLE customer_list (
    id INT PRIMARY KEY DEFAULT nextval('customer_seq'),
    customer string,
    address string
  );

4）系统时间

Hubble不支持SYSDATE；但是，它支持以下日期和时间：

> SELECT Transaction_timestamp(), clock_timestamp();
> SELECT current_timestamp
> SELECT now();

Oracle 数据迁移

Oracle映射到hubble数据类型

导出Oracle模式

将Oracle模式转换为SQL

导出表数据

配置表数据并将其转换为CSV

CSV数据导入

CSV格式数据准备

将数据文件放置在集群可访问到的位置

hubble库中不存在的表的数据迁移

hubble库中已存在表的数据迁移

SQL性能最佳实践

多行DML最佳做法

使用TRUNCATE而不是DELETE来删除表中的所有行

批量插入最佳做法

使用IMPORT而不是INSERT在新表中批量插入

批量删除最佳做法

重构应用程序SQL

解决方案

MySQL数据迁移到Hubble数据库

解决方案

历史数据查询解决方案

Oracle 数据迁移到Hubble数据库

Oracle 数据迁移

Oracle映射到hubble数据类型

导出Oracle模式

将Oracle模式转换为SQL

导出表数据

配置表数据并将其转换为CSV

CSV数据导入

CSV格式数据准备

将数据文件放置在集群可访问到的位置

hubble库中不存在的表的数据迁移

hubble库中已存在表的数据迁移

SQL性能最佳实践

多行DML最佳做法

使用TRUNCATE而不是DELETE来删除表中的所有行

批量插入最佳做法

使用IMPORT而不是INSERT在新表中批量插入

批量删除最佳做法

重构应用程序SQL

相关文章

解决方案

MySQL数据迁移到Hubble数据库

解决方案

历史数据查询解决方案