1 数据类型对比
| MySQL | PostgreSQL | comments | |
| 数值类型 | TINYINT | SMALLINT | gp中无zerofill属性及unsigned类型，所以为了数据不越界需使用大一精度的数据类型匹配 |
| SMALLINT | SMALLINT | ||
| MEDIUMINT | INTEGER | ||
| INT|INTEGER | INTEGER | ||
| BIGINT | BIGINT | ||
| TINYINT UNSIGNED | SMALLINT | ||
| SMALLINT UNSIGNED | INTEGER | ||
| MEDIUMINT UNSIGNED | INTEGER | ||
| INT UNSIGNED | BIGINT | ||
| BIGINT UNSIGNED | NUMERIC(20) | ||
| BIT | BIT | ||
| FLOAT | REAL | ||
| FLOAT UNSIGNED | DOUBLE PRECISION | ||
| DOUBLE|REAL|DOUBLE PRECISION | DOUBLE PRECISION | ||
| DECIMAL|DEC|NUMERIC|FIXED | NUMERIC | ||
| 字符类型 | CHAR | CHARACTER|CHAR | |
| VARCHAR | CHARACTER VARYING|VARCHAR | ||
| TINYTEXT | TEXT | ||
| TEXT | TEXT | ||
| MEDIUMTEXT | TEXT | ||
| LONGTEXT | TEXT | ||
| BINARY|CHAR BYTE | BYTEA | ||
| VARBINARY | BYTEA | ||
| TINYBLOB | BYTEA | ||
| BLOB | BYTEA | ||
| MEDIUMBLOB | BYTEA | ||
| LONGBLOB | BYTEA | ||
| 时间类型 | DATE | DATE | |
| TIME | TIME | ||
| YEAR | 无 | ||
| DATETIME | TIMESTAMP | ||
| TIMESTAMP | TIMESTAMP | ||
| 其他类型 | BOOL|BOOLEAN | BOOLEAN | |
| ENUM | CREATE TYPE … AS ENUM | ||
| SET | 无 |
2 语法对比
2.1 limit
MySQL：
|
1 |
LIMIT offset, limit |
or
|
1 |
LIMIT limit OFFSET offset |
Greenplum：
|
1 |
LIMIT limit OFFSET offset |
2.2 replace
MySQL：
|
1 2 3 |
REPLACE [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... |
Greenplum：
不支持该语法，需要使用函数实现，例：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE FUNCTION replace() RETURNS void AS $$ BEGIN IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' ) THEN UPDATE phonebook SET extension = '1234' WHERE name = 'john doe'; ELSE INSERT INTO phonebook VALUES( 'john doe', '1234' ); END IF; RETURN; END; $$ LANGUAGE plpgsql; |
2.3 insert into … on duplicate key update
MySQL：
|
1 2 3 4 5 6 7 8 9 |
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] |
Greenplum：
不支持该语法，需要使用函数实现，例：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE FUNCTION replace() RETURNS void AS $$ BEGIN IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' ) THEN UPDATE phonebook SET extension = '1234' WHERE name = 'john doe'; ELSE INSERT INTO phonebook VALUES( 'john doe', '1234' ); END IF; RETURN; END; $$ LANGUAGE plpgsql; |
2.4 select … into outfile
MySQL：
|
1 2 3 |
SELECT ... INTO OUTFILE 'path/file_name' |
Greenplum：
|
1 2 3 |
COPY ( SELECT ... ) TO 'path/file_name' |
2.5 自增列
MySQL：
列加auto_increment属性，例:create table a(id int auto_increment primary key)
获取当前值:select last_insert_id()
Greenplum：
字段类型使用serial,例:create table a(id serial primary key)
获取当前值:select currval(‘a_id_seq’)
2.6 注释
MySQL：
使用#或–
Greenplum：
使用–
2.7 执行存储过程
MySQL：
|
1 |
call proc_name() |
Greenplum：
Greenplum并无存储过程，使用函数代替，所以执行：
|
1 |
select proc_name() |
3 常用函数对比
3.1 时间函数
3.1.1 时间转字符串
MySQL：date_format（）
例：select date_format(now(),’%Y%m%d%H%i%s’)
Greenplum：to_char（）
例：select to_char(now(), ‘YYYYMMDDHH24MISS’)
3.1.2 字符串转时间
MySQL：str_to_date()
例：select str_to_date(‘20171120′,’%Y%m%d%H%i%s’)
Greenplum：to_date(),to_timestamp()
例：select to_date(‘20171120’, ‘YYYYMMDD’)
select to_date(‘20171120’, ‘YYYYMMDDHH24MISS’)
3.1.3 时间计算
MySQL：date_add()
例：select date_add(now(), interval 2 day)
Greenplum：直接计算
例：select now() + interval ‘2 day’
3.2 字符函数
3.2.1 空字符串处理
MySQL：ifnull
例：select ifnull(null,‘default’)
Greenplum：coalesce
例：select coalesce(null,‘default’)
3.2.2 字符串拼接
MySQL：concat()
例：select concat(‘abc’,‘def’)
Greenplum：||
例：select ‘abc’||’def’
4 数据迁移
Greenplum数据导入3种方式：
4.1 COPY命令
COPY需要经过master，仅建议在小数据量时使用。无法并行导入，在大量数据导入时效率很低，不过多介绍。
例：COPY tablea FROM ‘/data/tablea_data’;
4.2 使用外部表
外部表以及4.3中的gpload都需要使用gpfdist服务。
gpfdist是Greenplum自带的一个并行文件服务，原理如下图：
gpfdist为每个segment提供并行读写数据文件的服务。
1、先启动gpfdist服务，例：
|
1 |
gpfdist -d /data0/data -p 8123 -l /home/greenplum/gpfdist.log & |
-d 指定数据目录 -p指定服务端口 -l 指定日志文件
将数据文件放入该目录下
2、创建外部表，例：
|
1 |
create external table lzk.a(a int,b varchar(50)) location (‘gpfdist:localhost:8123/a.txt’) fromat ‘text’(delimiter ‘,’) |
- 从外部表导入数据，例：
|
1 |
create table t as select * from a; |
或者先创建，后导入：
|
1 2 3 |
create table t (a int,b varchar(50)) distributed by (a); insert into t select * from a; |
4.3 gpload
通过配置yaml控制文件来进行数据导入，同样依赖gpfdist服务。
例：
1、编辑a.yml文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
VERSION: 1.0.0.1 DATABASE: lzk USER: greenplum HOST: localhost PORT: 5432 GPLOAD: INPUT: - SOURCE: LOCAL_HOSTNAME: - localhost PORT: 8234 FILE: - /data0/data/a.txt - COLUMNS: - a: integer - b: text - FORMAT: text - DELIMITER: ',' - ERROR_LIMIT: 25 - LOG_ERRORS: true OUTPUT: - TABLE: public.a - MODE: INSERT |
2、进行导入：
|
1 |
gpload -f a.yml -l a.log |
