Greenplum数据导入导出

1.1 GPload 工具

编写 load.yml

verSION: 1.0.0.1

USER: gpadmin

PORT: 5432

GPLOAD:

INPUT:

• SOURCE:

FILE:

• /opt/gpadmin/zhonghong/testload.txt
• COLUMNS:
• msisdn: text
• blank1: text
• blank2: text
• uaText: text
• FORMAT: text
• DELIMITER: '|'

OUTPUT:

• TABLE: ua_in
• MODE: INSERT

SQL:

执行命令： gpload -f load.yml -U gpadmin -d uainfo

1.2 外部表

CREATE EXTERNAL TABLE ext_expenses ( name text,

date date, amount float4, category text, desc text )

LOCATION (‘gpfdist://etlhost:8081/*’,

‘ gpfdist://etlhost1:8081/*’)

FORMAT ‘TEXT’ (DELIMITER ‘,’);

装载数据：

1-insert 方式

INSERT INTO expenses_travel

SELECT * from ext_expenses where category=’travel’;

2-create table … as … 方式

CREATE TABLE expenses AS SELECT * from ext_expenses;

1.3 Copy 命令

Copy 命令装载数据并不是并行装载，而且所装载的数据文件必须放在 master 服务器上，例如：

COPY country FROM ‘/data/gpdb/country_data’

WITH DELIMITER ‘|’ LOG ERRORS INTO err_country

SEGMENT REJECT LIMIT 10 ROWS;