greenplum--数据导入和导出

insert

使用insert语句只适合加载少量的数据。

insert into tablename values (val1,val2,...);
或
insert into (...) select ... from tabname

copy

copy命令可以将文件导入和导出，在gp中数据需要通过master节点，无法实现各个segment节点并行高效数据导入和导出。
使用copy命令的语法如下：

--\h command :可以获取命令的语法
postgres=# \h copy  
Command:     COPY
Description: copy data between a file and a table
Syntax:
--将文件数据导入表，数据是追加到表中的
COPY table [(column [, ...])] FROM {'file' | STDIN}
     [ [WITH] 
       [OIDS]  
       [HEADER]  
       [DELIMITER [ AS ] 'delimiter']  
       [NULL [ AS ] 'null string']
       [ESCAPE [ AS ] 'escape' | 'OFF']
       [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
       [CSV [QUOTE [ AS ] 'quote'] 
            [FORCE NOT NULL column [, ...]]
       [FILL MISSING FIELDS]
     [ [LOG ERRORS INTO error_table] [KEEP] 
       SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]
 
 --将表中数据导入到文件中
COPY {table [(column [, ...])] | (query)} TO {'file' | STDOUT}
      [ [WITH] 
        [OIDS]
        [HEADER]
        [DELIMITER [ AS ] 'delimiter']
        [NULL [ AS ] 'null string']
        [ESCAPE [ AS ] 'escape' | 'OFF']
        [CSV [QUOTE [ AS ] 'quote'] 
             [FORCE QUOTE column [, ...]] ]

外部表

greenplum支持数据的并发加载，gpfdist是并发加载的工具，使用的是外部表。
gpfdist的实现架构图如下：

gpfdist的工作流程：

1. 启动gpfdist，并在Master上建表，定义外部表的原数据信息。
2. 将外部表插入到一张gp的物理表中，开始导入数据。
3. Segment根据建表时定义的gpfdist url个数，启动相同的并发到gpfdist获取数据。
4. Segment连接到gpfdist，gpfdist接收数据时开始读取文件，顺序读物文件，然后将文件拆分成多个块，随机抛给Segment。

外部表是一张表的数据指向数据库之外的数据文件。可以对一个外部表执行正常的DML操作，读取数据时，数据库就从数据文件中加载数据。

外部表分为可读和可写外部表，可读外部表用于数据装载、可写外部表用于数据卸载。

创建外部表的语法：

CREATE [READABLE] EXTERNAL TABLE table_name 
     ( column_name data_type [, ...] | LIKE other_table )
      LOCATION ('file://seghost[:port]/path/file' [, ...])
        | ('gpfdist://filehost[:port]/file_pattern[#transform]'
        | ('gpfdists://filehost[:port]/file_pattern[#transform]'
            [, ...])
        | ('gphdfs://hdfs_host[:port]/path/file')
      FORMAT 'TEXT' 
            [( [HEADER]
               [DELIMITER [AS] 'delimiter' | 'OFF']
               [NULL [AS] 'null string']
               [ESCAPE [AS] 'escape' | 'OFF']
               [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
               [FILL MISSING FIELDS] )]
           | 'CSV'
            [( [HEADER]
               [QUOTE [AS] 'quote'] 
               [DELIMITER [AS] 'delimiter']
               [NULL [AS] 'null string']
               [FORCE NOT NULL column [, ...]]
               [ESCAPE [AS] 'escape']
               [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
               [FILL MISSING FIELDS] )]
           | 'CUSTOM' (Formatter=<formatter specifications>)
     [ ENCODING 'encoding' ]
     [ [LOG ERRORS INTO error_table] SEGMENT REJECT LIMIT count 
       [ROWS | PERCENT] ]
 ---------------------------------------------------------------      
CREATE [READABLE] EXTERNAL WEB TABLE table_name 
     ( column_name data_type [, ...] | LIKE other_table )
      LOCATION ('http://webhost[:port]/path/file' [, ...])
    | EXECUTE 'command' [ON ALL 
                          | MASTER
                          | number_of_segments 
                          | HOST ['segment_hostname'] 
                          | SEGMENT segment_id ]
      FORMAT 'TEXT' 
            [( [HEADER]
               [DELIMITER [AS] 'delimiter' | 'OFF']
               [NULL [AS] 'null string']
               [ESCAPE [AS] 'escape' | 'OFF']
               [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
               [FILL MISSING FIELDS] )]
           | 'CSV'
            [( [HEADER]
               [QUOTE [AS] 'quote'] 
               [DELIMITER [AS] 'delimiter']
               [NULL [AS] 'null string']
               [FORCE NOT NULL column [, ...]]
               [ESCAPE [AS] 'escape']
               [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
               [FILL MISSING FIELDS] )]
           | 'CUSTOM' (Formatter=<formatter specifications>)
     [ ENCODING 'encoding' ]
     [ [LOG ERRORS INTO error_table] SEGMENT REJECT LIMIT count 
       [ROWS | PERCENT] ]
------------------------------------------------------------------
CREATE WRITABLE EXTERNAL TABLE table_name
    ( column_name data_type [, ...] | LIKE other_table )
     LOCATION('gpfdist://outputhost[:port]/filename[#transform]'
      | ('gpfdists://outputhost[:port]/file_pattern[#transform]'
          [, ...])
      | ('gphdfs://hdfs_host[:port]/path')
      FORMAT 'TEXT' 
               [( [DELIMITER [AS] 'delimiter']

外部表的优势在于支持从各个segment上并发地通过gpfdist导入数据，效率高。

实例

可写外部表（卸载数据）

1. 启动gpfdist服务

[gpadmin@mdw ~]$ gpfdist -p 8888 -d /home/gpadmin/master/ 
2020-02-25 10:43:37 36451 INFO Before opening listening sockets - following listening sockets are available:
2020-02-25 10:43:37 36451 INFO IPV6 socket: [::]:8888
2020-02-25 10:43:37 36451 INFO IPV4 socket: 0.0.0.0:8888
2020-02-25 10:43:37 36451 INFO Trying to open listening socket:
2020-02-25 10:43:37 36451 INFO IPV6 socket: [::]:8888
2020-02-25 10:43:37 36451 INFO Opening listening socket succeeded
2020-02-25 10:43:37 36451 INFO Trying to open listening socket:
2020-02-25 10:43:37 36451 INFO IPV4 socket: 0.0.0.0:8888
Serving HTTP on port 8888, directory /home/gpadmin/master

2. 编写可写外部表

/*创建可写外部表*/
drop EXTERNAL table if exists student_f_et;
create writable external table student_f_et(
 id varchar(4000)
 ,sex varchar(4000)
 ,age varchar(4000)
 ,grade varchar(4000)
)
LOCATION ('gpfdist://192.168.190.144:8888/sutdent.f.dat')   --数据文件的地址
FORMAT 'text' (DELIMITER e'\x0f' null '' ESCAPE 'off')   --数据文件的格式
ENCODING 'UTF8'   --数据文件的编码
;
/*插入表中数据到可写外部表*/
insert into student_f_et (
  id
  ,sex
  ,age
  ,grade
)
select
  id
  ,sex
  ,age
  ,grade
from student
;
/*删除表*/
drop external table if exists student_f_et;

3. 查看是否成功

2020-02-25 10:55:37 36451 INFO remove sessions
2020-02-25 10:55:37 36451 INFO free session 1582583557-0000000197.0.0.0:/home/gpadmin/master/sutdent.f.dat

存在该数据文件，即创建可写外部表成功。

[gpadmin@mdw ~]$ cd /home/gpadmin/master/
[gpadmin@mdw master]$ ll
total 4
-rw-------. 1 gpadmin gpadmin 84 Feb 25 10:55 sutdent.f.dat

可读外部表（装载数据）

--创建可读外部表
drop EXTERNAL table if exists student_r_et;
create  external table student_r_et(
 id int
 ,sex varchar(2)
 ,age int
 ,grade varchar(20)
)
LOCATION ('gpfdist://192.168.190.144:8888/sutdent.f.dat')   --数据文件的地址
FORMAT 'text' (DELIMITER e'\x0f' null '' ESCAPE 'off')   --数据文件的格式
ENCODING 'UTF8'   --数据文件的编码
;

select * from student_r_et;

这样就可以将sutdent.f.dat中的数据装入student_r_et表中了。