统一SQL-Oracle2PostgreSQL支持分区表
【统一sql】:https://www.light-pg.com/docs/LTSQL/current/index.html
LightDB 统一SQL 是一款基于 Go 开发的 SQL 转换中间件,支持将 Oracle 常用 SQL 语法翻译转换为其他数据库的 SQL 。以主流数据库Oracle的SQL为基准,将SQL语句转换为其他信创数据库(LightDB、PostgreSQL、TDSQL、DM8、OceanBase、openGauss等)的SQL语句。目标是降低业务部门适配信创数据库的成本,让用户尽可能少地修改数据访问层代码,使基于Hibernate、JPA、MyBatis等框架的Java应用程序,基于oci、libmysqlclient、cres开发工具、hsdb的c/c++应用程序以及其它能够调用c函数的程序都能够直接切换到目标信创数据库,实现信创数据库之间的平滑迁移。
前言
Oracle2PostgreSQL需支持查询分区表的解析和改写,需与创建分区表配合(配套命名):分区表名是通过主表名拼接’ 1_prt’再拼接分区名而成,而且只支持查询1个分区;分区表名需支持schema、双引号区分大小写的用法;
Oracle2PostgreSQL需支持创建hash分区表的解析和改写:CREATE TABLE … PARTITION BY HASH (column[,column]) [PARTITIONS hash_partition_quantity];如果不指定哈希分区数则默认创建一个分区;
Oracle2PostgreSQL需支持创建hash、list、range分区表时指定主键、唯一键约束的解析和改写:CREATE TABLE … (…, CONSTRAINT … {PRIMARY KEY|UNIQUE} …) PARTITON BY …;约束只支持列,不支持CHECK和列的嵌套表达式;
查询分区表
转化
oracle语句
SELECT … FROM table_name PARTITION (partition_name);
postgresql语句
SELECT … FROM table_name_1_prt_partition_name;
结果
通过统一sql转化之后的结果:
CREATE TABLE unisql_partition (
unisql_id NUMBER,
unisql_name VARCHAR2(50),
unisql_salary NUMBER(10,2),
unisql_hire_date DATE,
CONSTRAINT unisql_partition_pk PRIMARY KEY (unisql_id, unisql_hire_date)
)
PARTITION BY RANGE (unisql_hire_date) (
PARTITION unisql_partition_p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION unisql_partition_p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (1, 'John', 5000, TO_DATE('2023-01-01','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (2, 'Mary', 6000, TO_DATE( '2023-02-15','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (3, 'Tom', 7000, TO_DATE( '2023-03-20','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (4, 'Alice', 8000, TO_DATE('2023-04-10','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (5, 'Bob', 9000, TO_DATE('2023-05-01','YYYY-MM-DD'));
-- 转换前Oracle SQL:
SELECT unisql_id,unisql_name,unisql_salary, unisql_hire_date FROM unisql_partition PARTITION (unisql_partition_p1);
UNISQL_ID|UNISQL_NAME|UNISQL_SALARY|UNISQL_HIRE_DATE |
---------+-----------+-------------+-----------------------+
1|John | 5000|2023-01-01 00:00:00.000|
2|Mary | 6000|2023-02-15 00:00:00.000|
3|Tom | 7000|2023-03-20 00:00:00.000|
4|Alice | 8000|2023-04-10 00:00:00.000|
5|Bob | 9000|2023-05-01 00:00:00.000|
-- 转换后PostgreSQL SQL:
SELECT unisql_id,unisql_name,unisql_salary,unisql_hire_date FROM unisql_partition_1_prt_unisql_partition_p1
unisql_id|unisql_name|unisql_salary|unisql_hire_date |
---------+-----------+-------------+-----------------------+
1|John | 5000.00|2023-01-01 00:00:00.000|
2|Mary | 6000.00|2023-02-15 00:00:00.000|
3|Tom | 7000.00|2023-03-20 00:00:00.000|
4|Alice | 8000.00|2023-04-10 00:00:00.000|
5|Bob | 9000.00|2023-05-01 00:00:00.000|
create table unisql_partition_by_list_test
(
id varchar2(15) not null,
city varchar2(20),
city_number NUMBER(10,2)
)
partition by list (city)(
partition "P1" values ('beijing'),
partition p2 values ('shanghai'),
partition p3 values ('changsha'),
partition p4 values (default)
);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (1, 'beijing', 5000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (2, 'beijing', 9000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (3, 'beijing', 2000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (4, 'shanghai', 3000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (5, 'hangzhou', 6000);
-- 转换前Oracle SQL:
SELECT id, city, city_number FROM unisql_partition_by_list_test PARTITION ("P1");
ID|CITY |CITY_NUMBER|
--+-------+-----------+
1 |beijing| 5000|
2 |beijing| 9000|
3 |beijing| 2000|
-- 转换后PostgreSQL SQL:
SELECT id, city,city_number FROM "unisql_partition_by_list_test_1_prt_P1"
id|city |city_number|
--+-------+-----------+
1 |beijing| 5000.00|
2 |beijing| 9000.00|
3 |beijing| 2000.00|create table unisql_index_test(col1 int,col2 date);
创建hash分区表的改写
转化
源Oracle:
CREATE TABLE ... PARTITION BY HASH (part_params) PARTITIONS number ;
转到postgresql
CREATE TABLE ... PARTITION BY HASH (part_params);
CREATE REATE TABLE tableName_1_prt_p0 PARTITION OF tableName FOR VALUES WITH (MODULUS number, REMAINDER 0);
CREATE REATE TABLE tableName_1_prt_p1 PARTITION OF tableName FOR VALUES WITH (MODULUS number, REMAINDER 1);
...
CREATE REATE TABLE tableName_1_prt_p1 PARTITION OF tableName FOR VALUES WITH (MODULUS number, REMAINDER number-1);
结果
-- 转换前Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 INT, col2 VARCHAR(50), col3 TIMESTAMP )
PARTITION BY HASH (col1);
-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 bigint,col2 varchar(50),col3 timestamp) PARTITION BY HASH (col1);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p0 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-- 转换前Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 int,col2 varchar(50),col3 timestamp,CONSTRAINT unisql_partition_uk UNIQUE (col1, col2))
PARTITION BY HASH (col1,col2) PARTITIONS 4;
-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 bigint,col2 varchar(50),col3 timestamp,CONSTRAINT unisql_partition_uk UNIQUE(col1, col2)) PARTITION BY HASH (col1,col2);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p0 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p1 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p2 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p3 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 3);
分区表指定主键、唯一键约束的改写
转化
创建分区表时指定主键、唯一键表级约束:CREATE TABLE … (…, CONSTRAINT … {PRIMARY KEY|UNIQUE} …) PARTITON BY …,这里值得是
转化到postgresql时候,直接转化出去,语法没有改变的
结果
-- 转换前Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 int,col2 varchar(50),col3 timestamp,CONSTRAINT unisql_partition_uk UNIQUE (col1, col2))
PARTITION BY HASH (col1,col2) PARTITIONS 4;
-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 bigint,col2 varchar(50),col3 timestamp,CONSTRAINT unisql_partition_uk UNIQUE(col1, col2)) PARTITION BY HASH (col1,col2);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p0 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p1 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p2 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p3 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 3);
总结
统一SQL分区键仅支持列,分区范围支持使用函数、字符串、常数表达,统一SQL会根据表名、分区名在转化库中创建分区表,分区表名为:表名_1_prt_分区名, Oracle 源语句HASH分区未指定分区名和分区数目时,转化到PostgreSQL表分区名默认为p0,指定多个分区数目,转化后的分区名依次递增(p0,p1,p2,…)。Oracle 和 PostgreSQL HASH分区算法不同,所以插入的数据分布到分区表表现也不一致。 分区表的唯一、主键的表级约束,必须与分区字段一致(或包含分区字段),才能保证全局的唯一性,否则无法实现转化,唯一、主键的列级约束的这个限制暂不考虑。
数据库底层HASH算法的不同,所有数据分区到各个分区表也会不同的,这边统一SQL只是提供转化方案,无法保证HASH分区插入数据之后储存的分区一致。
查询分区表,转化到PostgreSQL,这个拼接的分区表名是否有双引号,和源Oracle查询语句的分区名是否有双引号表现一致,不受表名双引号影响。