Greenplum主要有两种数据的导入导出方式,一种是通过继承PostgreSQL的COPY命令,另一种是使用外部表的方式。
COPY命令一般是针对小数据量场景,而大数据量的场景则通常使用外部表来实现。
COPY命令
特点:适用数据量较小的场景,串行方式,数据通过Master进行处理。
以下示例演示通过COPY命令进行导入与导出。
COPY导入
=# create table test(a int, b int, c text) distributed randomly;
CREATE TABLE
=# \COPY test FROM '/tmp/file1' DELIMITER ',';
COPY 2
=# select * from test;
a | b | c
---+---+-----
2 | 2 | bbb
1 | 1 | aaa
(2 rows)
实际上,COPY命令有几种不同的形式:
- 使用copy命令从文件导入,如COPY test FROM ‘/tmp/file0’ DELIMITER ‘|’;
- 使用\copy命令从文件导入,如\COPY test FROM ‘/tmp/file1’ DELIMITER ‘,’;
- 从STDIN导入,如copy test from stdin;
\COPY与COPY是完全不同的,COPY命令需要在Master节点上执行,而\COPY命令与COPY FROM STDIN命令则是将数据输送到Master服务,是不需要在Master节点上运行的。
COPY导出
=# \COPY test to '/tmp/file100';
COPY 2
[gpadmin@xduat01 ~]$ cat /tmp/file100
1 1 aaa
2 2 bbb
使用COPY命令导出,可以是导出一张完整的表,也可以是指定一个查询语句导出部分数据。
外部表
特点:针对大规模并行导入或导出数据,目前支持多种外部表协议,如file协议、gpfdist、pxf等。
file协议
定义:通过 URI 来指向操作系统的文件,URI 由主机名、文件所属的路径和文件名组成,不需要指定端口号,因为访问的是各个计算节点的本地文件。
示例:
[gpadmin@xduat01 tmp]$ cat /tmp/file1
1,1,aaa
2,2,bbb
=# CREATE EXTERNAL TABLE test_ext (a int, b int, c text) LOCATION('file://xduat01/tmp/file1') FORMAT 'text' (delimiter E'\x2c');
CREATE EXTERNAL TABLE
=# \d+ test_ext
External table "public.test_ext"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
a | integer | | plain | |
b | integer | | plain | |
c | text | | extended | |
Type: readable
Encoding: UTF8
Format type: text
Format options: delimiter ',' null '\N' escape ''
External options: {}
External location: file://xduat01/tmp/file1
Execute on: all segments
=# select * from test_ext;
a | b | c
---+---+-----
1 | 1 | aaa
2 | 2 | bbb
(2 rows)
注:一个外部表在定义时可以指定多个URI,但URI的个数不能大于节点上Primary的个数,否则将会报错。
gpfdist协议
定义:通过 URI 来指向 gpfdist 服务的相对路径的文件,数据库的所有Primary 都必须能够访问 qbfdist 服务。
gpfdist服务的命令,在数据库集群的每台主机上都存在,在$GPHOME/bin 目录下,source 了 GP 的 path 文件之后,就可以直接使用该命令。
如果要提升多个外部表的并发导入能力,可以启动多个 gpfdist 服务供不同的外部表访问。
gpfdist还支持数据转换功能,通过在 URI 中使用 transform 参数来指定。
gpfdist 服务,允许一个外部表,同时使用一台 gpfdist 文件服务器的多个网络端口,以提升数据访问的性能。
使用gpfdist的步骤如下:
1 启动gpfdist服务
以下示例是在一个节点上使用不同的端口来启动两个pgfdist服务
nohup gpfdist -p 8081 -d /../ &
nohup gpfdist -p 8082 -d /../ &
2 创建外部表
=# CREATE EXTERNAL TABLE test_ext1 (a int, b int, c text) LOCATION('gpfdist://xduat01:8081/tmp/file1') FORMAT 'text' (delimiter E'\x2c');
CREATE EXTERNAL TABLE
=# CREATE EXTERNAL TABLE test_ext2 (a int, b int, c text) LOCATION('gpfdist://xduat01:8082/tmp/file2') FORMAT 'text' (delimiter E'\x2c');
CREATE EXTERNAL TABLE
=# select * from test_ext1;
a | b | c
---+---+-----
1 | 1 | aaa
2 | 2 | bbb
(2 rows)
=# select * from test_ext2;
a | b | c
---+---+-----
3 | 3 | ccc
4 | 4 | ddd
5 | 5 | eee
(3 rows)
=# \d+ test_ext1;
External table "public.test_ext1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
a | integer | | plain | |
b | integer | | plain | |
c | text | | extended | |
Type: readable
Encoding: UTF8
Format type: text
Format options: delimiter ',' null '\N' escape ''
External options: {}
External location: gpfdist://xduat01:8081/tmp/file1
Execute on: all segments
=# \d+ test_ext2;
External table "public.test_ext2"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
a | integer | | plain | |
b | integer | | plain | |
c | text | | extended | |
Type: readable
Encoding: UTF8
Format type: text
Format options: delimiter ',' null '\N' escape ''
External options: {}
External location: gpfdist://xduat01:8082/tmp/file2
Execute on: all segments
PXF协议
定义:PXF是Greenplum的平台扩展框架,通过将外部数据源映射成GP的外部表,便利Greenplum能够并行、高吞吐量以及联合查询外部数据源的数据。该扩展插件是基于Apache HAWQ的PXF。
PXF的使用步骤如下:
1 安装pxf.so
在数据库安装目录检查是否已经包含pxf.so的lib包编译,如果不存在lib文件需要上传pxf.so文件,目录为/usr/local/gp/lib/psotgresql/pxf.so
2 上传pxf安装包并配置
准备好下载的pxf.tar.gz并解压到/usr/local/gp目录下,对新加入的pxf安装包添加文件配置
echo 'export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.242.b08-1.h5.ky10.aarch64/jre' >> .bashrc
echo 'export PXF_CONF=/usr/local/gp/pxf/conf' >> .bashrc
echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/gp/pxf/lib' >> .bash_profile
echo 'export PATH=$HOME/.local/bin:$HOME/bin:$GPHOME/bin:$GPHOME/pxf/bin:$PATH' >>.bash_profile
3 新建jdbc配置文件
在/usr/local/gp/pxf/servers下新建文件夹connora并创建jdbc-site.xml文件,内容如下
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<property>
<name>jdbc.driver</name>
<value>oracle.jdbc.driver.OracleDriver</value>
</property>
<property>
<name>jdbc.url</name>
<value>jdbc:oracle:thin:@xx.xx.xx.xx:1521:sjfxdb</value>
</property>
<property>
<name>jdbc.user</name>
<value>icl</value>
</property>
<property>
<name>jdbc.password</name>
<value>icl123</value>
</property>
</configuration>
4 初始化pxf
pxf init
如果为集群方式需要使用
pxf cluster init
5 启动pxf
pxf start
如果为集群方式需要使用
pxf cluster start
6 pxf sql以及control文件
如果编译的数据库版本不包含pxf的包,需要手动上传到服务器上,需要把pxf的sql以及control文件上传到文件夹
将以下文件放置到/usr/local/gp/share/postgresql/extension下
pxf–1.0–2.0.sql
pxf–1.0.sql
pxf–2.0.sql
pxf.control
pxfinvalid.sql
pxf.sql
7 创建支持pxf协议
CREATE EXTENSION pxf;
8 创建外部表
create EXTERNAL table C_AG_ACCT_INFO
(
etl_dat VARCHAR(10),
legp_cd VARCHAR(10),
agr_id VARCHAR(50),
agr_mdf VARCHAR(50),
src_sys_cust_nbr VARCHAR(50),
cbs_cust_nbr VARCHAR(50),
cust_typ VARCHAR(10),
act_nbr VARCHAR(50),
sub_act_nbr VARCHAR(50),
ibs_obs_id VARCHAR(10),
int_act_id VARCHAR(10),
。。。
)
LOCATION ('pxf://icl.C_AG_ACCT_INFO?PROFILE=Jdbc&SERVER=connora')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');