AWS redshift->hdb pg(Greenplum),DDL语法转换规则

简介: postgresql greenplum ddl

背景
redshift 和 Greenplum都是源自PostgreSQL的MPP数据库:

https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases

语法上有一些差异,从redshift切换到greenplum(或阿里云hdb pg)时,DDL需要做一定的转换。

redshift create table
https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html

CREATE [ [LOCAL ] { TEMPORARY | TEMP } ] TABLE
[ IF NOT EXISTS ] table_name
( { column_name data_type [column_attributes] [ column_constraints ]
| table_constraints
| LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }
[, ... ] )
[ BACKUP { YES | NO } ] -- gpdb 不支持,(指定是否自动快照备份)
[table_attribute]
where column_attributes are:

[ DEFAULT default_expr ]
[ IDENTITY ( seed, step ) ] -- gpdb 不支持该语法,使用SEQUENCE+DEFAULT代替
[ ENCODE encoding ] -- 指定列压缩算法,gpdb 不支持该语法,使用全局压缩算法代替
[ DISTKEY ] -- gpdb 不支持该语法,但是redshift只支持一列作为分布键,GPDB支持多列作为分布键,gpdb使用distributed by(colname1, ...)代替
[ SORTKEY ] -- gpdb 不支持,阿里云hdb pg支持,语法参考后面的转换规则。
and column_constraints are:

[ { NOT NULL | NULL } ]
[ { UNIQUE | PRIMARY KEY } ]
[ REFERENCES reftable [ ( refcolumn ) ] ]
and table_constraints are:

[ UNIQUE ( column_name [, ... ] ) ]
[ PRIMARY KEY ( column_name [, ... ] ) ]
[ FOREIGN KEY (column_name [, ... ] ) REFERENCES reftable [ ( refcolumn ) ]
and table_attributes are:

[ DISTSTYLE { EVEN | KEY | ALL } ] -- gpdb 不支持该语法,even使用distributed randomly代替,key使用distributed by (colname1,...) 代替,ALL不支持(维度表,所有SEGMENT都有一份)。
[ DISTKEY ( column_name ) ] -- gpdb 不支持该语法,但是redshift只支持一列作为分布键,GPDB支持多列作为分布键,gpdb使用distributed by(colname1, ...)代替
[ [COMPOUND | INTERLEAVED ] SORTKEY ( column_name [, ...] ) ] -- gpdb 不支持,阿里云HDB PG支持sortkey,语法参考后面的转换规则
greenplum(阿里云hdb pg) create table
https://gpdb.docs.pivotal.io/43300/ref_guide/sql_commands/CREATE_TABLE.html

Synopsis

CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name (
[ { column_name data_type [ DEFAULT default_expr ]
[column_constraint [ ... ]
[ ENCODING ( storage_directive [,...] ) ]
]
| table_constraint
| LIKE other_table [{INCLUDING | EXCLUDING}

                  {DEFAULTS | CONSTRAINTS}] ...}  

[, ... ] ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter=value [, ... ] )
[ ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP} ]
[ TABLESPACE tablespace ]
[ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
[ PARTITION BY partition_type (column)

   [ SUBPARTITION BY partition_type (column) ]   
      [ SUBPARTITION TEMPLATE ( template_spec ) ]  
   [...]  
( partition_spec )   
    | [ SUBPARTITION BY partition_type (column) ]  
      [...]  
( partition_spec  
  [ ( subpartition_spec  
       [(...)]   
     ) ]   
)  

where column_constraint is:

[CONSTRAINT constraint_name]
NOT NULL | NULL
| UNIQUE [USING INDEX TABLESPACE tablespace]

        [WITH ( FILLFACTOR = value )]  

| PRIMARY KEY [USING INDEX TABLESPACE tablespace]

             [WITH ( FILLFACTOR = value )]  

| CHECK ( expression )
| REFERENCES table_name [ ( column_name [, ... ] ) ]

        [ key_match_type ]  
        [ key_action ]  

where storage_directive for a column is:

COMPRESSTYPE={ZLIB | QUICKLZ | RLE_TYPE | NONE}

[COMPRESSLEVEL={0-9} ]  
[BLOCKSIZE={8192-2097152} ]  

where storage_parameter for the table is:

APPENDONLY={TRUE|FALSE}
BLOCKSIZE={8192-2097152}
ORIENTATION={COLUMN|ROW}
CHECKSUM={TRUE|FALSE}
COMPRESSTYPE={ZLIB|QUICKLZ|RLE_TYPE|NONE}
COMPRESSLEVEL={0-9}
FILLFACTOR={10-100}
OIDS[=TRUE|FALSE]
and table_constraint is:

[CONSTRAINT constraint_name]
UNIQUE ( column_name [, ... ] )

      [USING INDEX TABLESPACE tablespace]   
      [WITH ( FILLFACTOR=value )]   

| PRIMARY KEY ( column_name [, ... ] )

             [USING INDEX TABLESPACE tablespace]   
             [WITH ( FILLFACTOR=value )]   

| CHECK ( expression )
| FOREIGN KEY ( column_name [, ... ] )

        REFERENCES table_name [ ( column_name [, ... ] ) ]  
        [ key_match_type ]  
        [ key_action ]  
        [ key_checking_mode ]  

where key_match_type is:

MATCH FULL  

| SIMPLE
where key_action is:

ON DELETE   

| ON UPDATE
| NO ACTION
| RESTRICT
| CASCADE
| SET NULL
| SET DEFAULT
where key_checking_mode is:

DEFERRABLE  

| NOT DEFERRABLE
| INITIALLY DEFERRED
| INITIALLY IMMEDIATE
where partition_type is:

LIST  

| RANGE
where partition_specification is:

partition_element [, ...]
and partition_element is:

DEFAULT PARTITION name
| [PARTITION name] VALUES (list_value [,...] )
| [PARTITION name]

 START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]  
 [ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]  
 [ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]  

| [PARTITION name]

 END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]  
 [ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]  

[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[ TABLESPACE tablespace ]
where subpartition_spec or template_spec is:

subpartition_element [, ...]
and subpartition_element is:

DEFAULT SUBPARTITION name
| [SUBPARTITION name] VALUES (list_value [,...] )
| [SUBPARTITION name]

 START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]  
 [ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]  
 [ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]  

| [SUBPARTITION name]

 END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]  
 [ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]  

[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[ TABLESPACE tablespace ]
where storage_parameter for a partition is:

APPENDONLY={TRUE|FALSE}
BLOCKSIZE={8192-2097152}
ORIENTATION={COLUMN|ROW}
CHECKSUM={TRUE|FALSE}
COMPRESSTYPE={ZLIB|QUICKLZ|RLE_TYPE|NONE}
COMPRESSLEVEL={1-9}
FILLFACTOR={10-100}
OIDS[=TRUE|FALSE]
阿里云 hdb pg sortkey 语法
https://help.aliyun.com/knowledge_detail/59195.html

[ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
[ SORTKEY (column, [ ... ] )]
例子

create table test(date text, time text, open float, high float, low float, volume int) with(APPENDONLY=true,ORIENTATION=column) sortkey (volume);

ALTER [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name SET SORTKEY (column, [ ... ] )
转换规则
1、去除完全不支持的语法

[ BACKUP { YES | NO } ] -- gpdb 不支持,(指定是否自动快照备份)
2、转换不支持但兼容的语法

where column_attributes are:

2.1、

[ IDENTITY ( seed, step ) ] -- gpdb 不支持该语法,使用SEQUENCE+DEFAULT代替
去掉并转换为:

create sequence seq1 start with seed increment by step;
create table test (
id int default nextval('seq1')
);
例如

postgres=# create sequence seq1 start with 100 increment by 2;
CREATE SEQUENCE
postgres=# select nextval('seq1');

nextval

 100  

(1 row)

postgres=# select nextval('seq1');

nextval

 102  

(1 row)
2.2、

[ ENCODE encoding ] -- 指定列压缩算法,gpdb 不支持该语法,使用全局压缩算法代替

BYTEDICT
DELTA
DELTA32K
LZO
MOSTLY8
MOSTLY16
MOSTLY32
RAW (no compression)
RUNLENGTH
TEXT255
TEXT32K
ZSTD
去掉并转换为:

create table test (id int)
with (COMPRESSTYPE={ZLIB|QUICKLZ|RLE_TYPE|NONE}) -- 选择一个压缩算法
;
2.3、

[ DISTKEY ] -- gpdb 不支持该语法,但是redshift只支持一列作为分布键,GPDB支持多列作为分布键,gpdb使用distributed by(colname1, ...)代替
去掉并转换为:

create table test (id int, info text)
distributed by (id); -- 分布列名与DISTKEY对应列名一致
2.4、

[ SORTKEY ] -- gpdb 不支持,阿里云hdb pg支持,语法参考后面的转换规则。
去掉并转换为:

create table test(date text, time text, open float, high float, low float, volume int)
with(APPENDONLY=true,ORIENTATION=column)
sortkey (volume); -- 这里指定sortkey

ALTER [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name
SET SORTKEY (column, [ ... ] ); -- 这里指定sortkey
如果设置了sort key,在数据导入完成后,执行如下SQL,(堵塞DDL,DML,DSL,重新对数据排序)

VACUUM SORT ONLY [tablename]
and table_attributes are:

2.5、

[ DISTSTYLE { EVEN | KEY | ALL } ] -- gpdb 不支持该语法,even使用distributed randomly代替,key使用distributed by (colname1,...) 代替,ALL不支持(维度表,所有SEGMENT都有一份)。
去掉并转换为:

distributed randomly

distributed by (colname1, ...)
2.6、

[ DISTKEY ( column_name ) ] -- gpdb 不支持该语法,但是redshift只支持一列作为分布键,GPDB支持多列作为分布键,gpdb使用distributed by(colname1, ...)代替
去掉并转换为:

distributed by(colname1, ...)
2.7、

[ [COMPOUND | INTERLEAVED ] SORTKEY ( column_name [, ...] ) ] -- gpdb 不支持,阿里云HDB PG支持sortkey,语法参考后面的转换规则
同2.4。

COMPOUND表示完全按用户指定的字段排序,类似PG的CLUSTER。

INTERLEAVED表示按维度排序,任意列的顺序都是公平的,类似多维空间聚集存放。

参考
https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html

https://gpdb.docs.pivotal.io/43300/ref_guide/sql_commands/CREATE_TABLE.html

https://help.aliyun.com/knowledge_detail/59195.html
转自阿里云德哥

目录
相关文章
|
大数据 OLAP 数据库
阿里大数据——数据库总结——ADS分析型数据库
阿里大数据——数据库总结——ADS分析型数据库自制脑图
1105 1
阿里大数据——数据库总结——ADS分析型数据库
|
弹性计算 tengine 负载均衡
云原生 - 负载均衡(SLB)配置 HTTPS 访问设置
云原生 - 负载均衡(SLB)配置 HTTPS 访问设置
2910 0
云原生 - 负载均衡(SLB)配置 HTTPS 访问设置
|
Ubuntu Linux Python
Tkinter错误笔记(一):tkinter.Button在linux下出现乱码
在Linux系统中,使用Tkinter库时可能会遇到中文显示乱码的问题,这通常是由于字体支持问题导致的,可以通过更换支持中文的字体来解决。
1447 0
Tkinter错误笔记(一):tkinter.Button在linux下出现乱码
|
存储 Kubernetes 调度
如何驱逐某个节点上到某些名称空间的pod到其他节点
在 Kubernetes (k8s) 中,驱逐某个节点上特定命名空间的 Pod 到其他节点可以通过以下步骤实现: ### 步骤一:找到要驱逐的 Pod 首先,你需要找到位于特定命名空间并且运行在目标节点上的 Pod。你可以使用 `kubectl get pods` 命令并指定 `-o wide` 和 `--namespace` 参数来获取这些信息。 ```bash kubectl get pods -o wide --namespace=<your-namespace> ``` 此命令将返回指定命名空间中的所有 Pod,并显示它们的详细信息,包括所在的节点名称。 ### 步骤二:标记
1783 4
CPU的工作原理基于其内部结构,通过执行指令来完成各种任务
CPU的工作原理基于其内部结构,通过执行指令来完成各种任务
798 3
|
运维 监控 持续交付
微服务架构解析:跨越传统架构的技术革命
微服务架构(Microservices Architecture)是一种软件架构风格,它将一个大型的单体应用拆分为多个小而独立的服务,每个服务都可以独立开发、部署和扩展。
4094 37
微服务架构解析:跨越传统架构的技术革命
|
小程序
微信小程序wx.previewImage实现图片预览
微信小程序wx.previewImage实现图片预览
2212 0
|
数据可视化 API 开发者
R1类模型推理能力评测手把手实战
随着DeepSeek-R1模型的广泛应用,越来越多的开发者开始尝试复现类似的模型,以提升其推理能力。
1648 2
|
SQL Java 数据库连接
数据库迁移不再难:Flyway 与 Liquibase 大比拼,哪个才是你的真命天子?
【9月更文挑战第3天】数据库迁移在软件开发中至关重要,尤其在使用 ORM 框架如 Hibernate 时。为确保部署时能顺利应用最新的数据库变更,开发者常使用自动化工具。Flyway 和 Liquibase 是当前流行的两种选择,均能有效管理数据库版本控制。Flyway 采用 SQL 脚本表示变更,简单易用;Liquibase 支持多种脚本格式,功能更强大,适合复杂项目。本文将对比这两种工具的特点,并通过示例展示各自的优缺点,帮助开发者根据项目需求做出合适的选择。
3810 1
|
监控 Ubuntu Linux
使用`dbus-monitor`命令深入了解DBus通信
`dbus-monitor`是Linux下用于观察DBus消息的命令行工具,有助于理解和调试应用程序间的通信。安装该工具可通过发行版的包管理器,如`apt-get`、`dnf`或`pacman`。基本用法是运行`dbus-monitor`监控系统总线,使用`--session`监控会话总线,配合过滤器如`sender`、`interface`等精细化查看特定消息。当面临DBus相关问题时,此工具能辅助排查和诊断。