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.1limit

MySQL：

MySQL

LIMIT offset, limit

1

LIMIToffset,limit

or

MySQL

LIMIT limit OFFSET offset

1

LIMITlimitOFFSEToffset

Greenplum：

MySQL

LIMIT limit OFFSET offset

1

LIMITlimitOFFSEToffset

2.2replace

MySQL：

MySQL

REPLACE [INTO] tbl_name [(col_name,...)]

{VALUES | VALUE} ({expr | DEFAULT},...),(...),...

1

2

3

REPLACE[INTO]tbl_name[(col_name,...)]

{VALUES|VALUE}({expr|DEFAULT},...),(...),...

Greenplum：

不支持该语法，需要使用函数实现，例：

MySQL

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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

CREATE FUNCTIONreplace()RETURNSvoidAS$$

BEGIN

IF EXISTS(SELECT*FROMphonebookWHEREname='john doe')THEN

UPDATEphonebook

SETextension='1234'WHEREname='john doe';

ELSE

INSERTINTOphonebookVALUES('john doe','1234');

END IF;

RETURN;

END;

$$LANGUAGEplpgsql;

2.3insert into … on duplicate key update

MySQL：

MySQL

INSERT [INTO] tbl_name [(col_name,...)]

{VALUES | VALUE} ({expr | DEFAULT},...),(...),...

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] ... ]

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：

不支持该语法，需要使用函数实现，例：

MySQL

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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

CREATE FUNCTIONreplace()RETURNSvoidAS$$

BEGIN

IF EXISTS(SELECT*FROMphonebookWHEREname='john doe')THEN

UPDATEphonebook

SETextension='1234'WHEREname='john doe';

ELSE

INSERTINTOphonebookVALUES('john doe','1234');

END IF;

RETURN;

END;

$$LANGUAGEplpgsql;

2.4select … into outfile

MySQL：

MySQL

SELECT ...

INTO OUTFILE 'path/file_name'

1

2

3

SELECT ...

INTO OUTFILE'path/file_name'

Greenplum：

MySQL

COPY ( SELECT ... )

TO 'path/file_name'

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：

MySQL

call proc_name()

1

callproc_name()

Greenplum：

Greenplum并无存储过程，使用函数代替，所以执行：

MySQL

select proc_name()

1

selectproc_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.1COPY命令

COPY需要经过master，仅建议在小数据量时使用。无法并行导入，在大量数据导入时效率很低，不过多介绍。

例：COPY tablea FROM ‘/data/tablea_data’;

4.2使用外部表

外部表以及4.3中的gpload都需要使用gpfdist服务。

gpfdist是Greenplum自带的一个并行文件服务，原理如下图：

gpfdist为每个segment提供并行读写数据文件的服务。

1、先启动gpfdist服务，例：

MySQL

gpfdist -d /data0/data -p 8123 -l /home/greenplum/gpfdist.log &

1

gpfdist-d/data0/data-p8123-l/home/greenplum/gpfdist.log&

-d 指定数据目录 -p指定服务端口 -l 指定日志文件

将数据文件放入该目录下

2、创建外部表，例：

MySQL

create external table lzk.a(a int,b varchar(50)) location (‘gpfdist:localhost:8123/a.txt’) fromat ‘text’(delimiter ‘,’)

1

createexternaltablelzk.a(aint,bvarchar(50))location(‘gpfdist:localhost:8123/a.txt’)fromat‘text’(delimiter‘,’)

从外部表导入数据，例：

MySQL

create table t as select * from a;

1

createtabletasselect*froma;

或者先创建，后导入：

MySQL

create table t (a int,b varchar(50)) distributed by (a);

insert into t select * from a;

1

2

3

createtablet(aint,bvarchar(50))distributedby(a);

insertintotselect*froma;

4.3gpload

通过配置yaml控制文件来进行数据导入，同样依赖gpfdist服务。

例：

1、编辑a.yml文件

MySQL

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

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、进行导入：

MySQL

gpload -f a.yml -l a.log

1

gpload-fa.yml-la.log