MySQL到Greenplum迁移分析
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自带的一个并行文件服务,原理如下图:

MySQL到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’) fromattext’(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