Greenplum-数据导入导出

原创已于 2022-09-16 19:14:34 修改 · 2.8k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #数据库

于 2022-09-16 18:10:45 首次发布

Greenplum 专栏收录该内容

37 篇文章

订阅专栏

本文详细介绍了Greenplum中COPY命令和外部表在数据导入导出中的应用，包括COPY命令的使用场景、特点以及与COPY的区别，以及外部表如file、gpfdist和PXF协议的定义、示例和优缺点。

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');