greenplum 外部表 oracle,Greenplum外部表操作

好好说gg 戈壁风

于 2021-04-04 08:51:08 发布

阅读量650

点赞数

文章标签： greenplum 外部表 oracle

本文将首先测试使用外部表的方式进行加载和卸载数据。

外部表-external tables，分为只读readable和只写writable两种类别，而每种又可以分为常规regular和web两种类型。在外部表可以并行的查询、关联或者排序等等，视图也是可以基于外部表创建的，只是只读外部表只能select，只写外部表只能insert。

只读的常规regular和web外部表的区别是前者可以重读而后者无法重读。

和oracle当中的外部表类似，但是比oracle提供了更多中的协议方式，oracle当中只有平面文件file这种协议，而gp中提供了以下几种连接协议方式供外部表使用：gpfdist、gpfdists、file、gphdfs，gpfdists只是gpfdist的加密升级版，而且在使用上还有一些限制和配置负责，因此不多介绍。

首先介绍的是gpfdist协议的外部表，这个程序在master安装完后自带就存在的，在$GPHOME/bin目录下，在需要的时候手工启动，当然你也可以直接将这程序copy到别的机器上然后启动就可以充当外部表的服务器了。

7.1.0. gpfdist

[gpadmin@o564gtser1 gpfdist]$ ls /usr/local/greenplum-db/bin/gpfdist -l

-rwxr-xr-x 1 gpadmin gpadmin 819994 01-30 06:31 /usr/local/greenplum-db/bin/gpfdist

几个常用的参数：

-d 指定目录

-p 指定端口

-l 指定日志文件

[gpadmin@o564gtser1 gpfdist]$ pwd

/home/gpadmin/gpfdist

[gpadmin@o564gtser1 gpfdist]$ gpfdist -d ./ -p 8001 -l ./gpfdist.log &

[1] 4382

[gpadmin@o564gtser1 gpfdist]$ [2013-04-30 21:46:21] [WRN gpfdist.c:2049] Creating the socket failed

Serving HTTP on port 8001, directory /home/gpadmin/gpfdist

【

测试gpfdist服饰是否开启:wget http://10.10.2.6:8001/e1.txt

8001端口后面的目录默认是从gpfdist启动的目录开始的不是从 / 根目录开始

netstat -anp | grep 8001{查看端口监听状况}

正常显示：tcp 0 0 :::8001 :::* LISTEN 64186/gpfdist

gpadmin用户在操作时不能操作root用户的文件夹和文件，用ll查看文件夹和文件属于哪个用户；

chown -R gpadmin:gpadmin 文件名；修改文件的用户和用户组 -R递归

】

[gpadmin@o564gtser1 gpfdist]$ jobs

[1]+ Running gpfdist -d ./ -p 8001 -l ./gpfdist.log &

上面我手工启动了一个gpfdist服务器，目录是/home/gpadmin/gpfdist，端口8001，日志文件是/home/gpadmin/gpfdist/gpfdist.log，将下来我们将生产一个文本文件e1.txt，并手工输入一些记录，然后我们从数据库中创建一个外部表读取这些记录。

[gpadmin@o564gtser1 gpfdist]$ cat e1.txt

1001 'gtlions' 18

1002 'keven' 19

1003 'keyte' 32

1004 'leon.lee' 35

[gpadmin@o564gtser1 ~]$ psql -U gtlions gtlions

psql (8.2.15)

Type "help" for help.

--创建外部表e1

gtlions=# create external table e1 (id smallint,name varchar(30),age smallint) location ('gpfdist://o564gtser1:8001/e1.txt') format 'text';

CREATE EXTERNAL TABLE

--读取外部表的数据

gtlions=# select * from e1;

id | name | age

------+------------+-----

1001 | 'gtlions' | 18

1002 | 'keven' | 19

1003 | 'keyte' | 32

1004 | 'leon.lee' | 35

(4 rows)

--我们发现name字段貌似存在了一点问题：多了引号，实际上在平面文件中并不需要特别的加引号，因为我们在定义表结构的时候已经指明的字段类型，我们修改下外部记录，重新读取下就ok了：

gtlions=# select * from e1;

id | name | age

------+----------+-----

1001 | gtlions | 18

1002 | keven | 19

1003 | keyte | 32

1004 | leon.lee | 35

(4 rows)

我们增加一个文件看看如何同时读取外部表，我直接cp了e1.txt：

[gpadmin@o564gtser1 gpfdist]$ cp e1.txt e1_a.txt

[gpadmin@o564gtser1 gpfdist]$ cat e*.txt

1001 gtlions 18

1002 keven 19

1003 keyte 32

1004 leon.lee 35

1001 gtlions 18

1002 keven 19

1003 keyte 32

1004 leon.lee 35

由于外部表无法重新定义位置和格式信息，因此我们只能新加一个表：

gtlions=# create external table e1_1(id smallint,name varchar(20),age smallint) location('gpfdist://o564gtser1:8001/e1*.txt') format 'text';

CREATE EXTERNAL TABLE

gtlions=# select * from e1_1;

id | name | age

------+----------+-----

1001 | gtlions | 18

1002 | keven | 19

1003 | keyte | 32

1004 | leon.lee | 35

1001 | gtlions | 18

1002 | keven | 19

1003 | keyte | 32

1004 | leon.lee | 35

(8 rows)

这样我们可以看到外部表是支持C通配符的，接下里我们看下如何处理不同格式的记录，因为我们上面指定的是text，默认的text用的是tab分隔符，我们将尝试使用别的分隔符，比如|、空格、逗号、星号等等来分割的记录，还有空值的情况。

首先我们看下空值的情况如何处理，来看下这样的情况：

[gpadmin@o564gtser1 gpfdist]$ cat e1.txt

1001 gtlions 18

1002 19

1003 keyte 32

leon.lee 35

第2行和第4行分别有空值的列，我们要怎么处理这个情况下表呢？我们先试试默认的情况下会是怎么读取的：

gtlions=# create external table e1_2(id smallint,name varchar(20),age smallint) location('gpfdist://o564gtser1:8001/e1.txt') format 'text';

CREATE EXTERNAL TABLE

gtlions=# select * from e1_2;

ERROR: invalid input syntax for integer: "" (seg1 slice1 o564gtser1:40001 pid=5458)

DETAIL: External table e1_2, line 4 of gpfdist://o564gtser1:8001/e1.txt, column id

提示在第4行有错误的输入格式，其实第2行也是错误的记录，我们可以这样来重新定义外部表，为空值指定一个格式：

gtlions=# drop external table e1_2;

DROP EXTERNAL TABLE

gtlions=# create external table e1_2(id smallint,name varchar(20),age smallint) location('gpfdist://o564gtser1:8001/e1.txt') format 'text' (delimiter E'\x09' null '');

CREATE EXTERNAL TABLE

gtlions=# select * from e1_2;

id | name | age

------+----------+-----

1001 | gtlions | 18

1002 | | 19

1003 | keyte | 32

| leon.lee | 35

(4 rows)

接下我们使用|来分割记录：

[gpadmin@o564gtser1 gpfdist]$ cat e1.txt

1001|gtlions|18

1002||19

1003|keyte|32

1004|leon.lee|35

gtlions=# drop external table e1_2;

DROP EXTERNAL TABLE

gtlions=# create external table e1_2(id smallint,name varchar(20),age smallint) location('gpfdist://o564gtser1:8001/e1.txt') format 'text' (DELIMITER '|' NULL 'empty');

CREATE EXTERNAL TABLE

gtlions=# select * from e1_2;

id | name | age

------+----------+-----

1001 | gtlions | 18

1002 | | 19

1003 | keyte | 32

1004 | leon.lee | 35

(4 rows)

只是很疑惑的是为什么null设置默认值不会生效~~！！！！！

接下来看下逗号分割：

[gpadmin@o564gtser1 gpfdist]$ cat e1.txt

1001,gtlions,18

1002,,19

1003,keyte,32

1004,leon.lee,35

gtlions=# drop external table e1_2;

DROP EXTERNAL TABLE

gtlions=# create external table e1_2(id smallint,name varchar(20),age smallint) location('gpfdist://o564gtser1:8001/e1.txt') format 'text' (DELIMITER ',' NULL 'empty');

CREATE EXTERNAL TABLE

gtlions=# select * from e1_2;

id | name | age

------+----------+-----

1001 | gtlions | 18

1002 | | 19

1003 | keyte | 32

1004 | leon.lee | 35

(4 rows)

gtlions=# create external table e1_3(id smallint,name varchar(20),age smallint) location('gpfdist://o564gtser1:8001/e1.txt') format 'csv';

CREATE EXTERNAL TABLE

gtlions=# select * from e1_3;

id | name | age

------+----------+-----

1001 | gtlions | 18

1002 | | 19

1003 | keyte | 32

1004 | leon.lee | 35

(4 rows)

我们可以看到这2个表是一样的记录，只是定义方式不一样，这里就引入了一种新的格式csv，其实我们都知道默认的csv是用逗号分割的。

接下来我们测试下如何直接修改外部表的字段定义，首先我们针对外部记录增加了一个note的列：

[gpadmin@o564gtser1 gpfdist]$ cat e1.txt

1001,gtlions,18,

1002,,19,note2

1003,keyte,32,

1004,leon.lee,13,note4

gtlions=# select * from e1_4;

ERROR: extra data after last expected column (seg0 slice1 o564gtser1:40000 pid=6700)

DETAIL: External table e1_4, line 1 of gpfdist://o564gtser1:8001/e1.txt: "1001,gtlions,18,"

gtlions=# alter external table e1_4 add column notes varchar(30);

ALTER EXTERNAL TABLE

gtlions=# select * from e1_4;

id | name | age | notes

------+----------+-----+-------

1001 | gtlions | 18 |

1002 | | 19 | note2

1003 | keyte | 32 |

1004 | leon.lee | 13 | note4

(4 rows)

可以看到和普通的heap表一样可以直接操作表结构的定义。

再来看异常的处理：

[gpadmin@o564gtser1 gpfdist]$ cat e1.txt

1001,gtlions,18,

1002,,19,note2

1003,keyte,32,

1004,leon.leeddddddddddddddddddddddddddddddddddddddddddddddddddd,13,note4

gtlions=# create external table e1_4(id smallint,name varchar(20),age smallint,notes varchar(30)) location('gpfdist://o564gtser1:8001/e1.txt') format 'text' log errors into err_e1_4 segment reject limit 2;

NOTICE: Error table "err_e1_4" does not exist. Auto generating an error table with the same name

CREATE EXTERNAL TABLE

gtlions=# select * from e1_4;

NOTICE: Found 1 data formatting errors (1 or more input rows). Rejected related input data.

id | name | age | notes

------+---------+-----+-------

1001 | gtlions | 18 |

1002 | | 19 | note2

1003 | keyte | 32 |

(3 rows)

提示我们有数据格式错误，我们上面有定义了一个异常格式的记录的处理方式是放入到err_e1_4，注意这个表对应的外部表每次查询触发的异常都会写入到这个日志表：

gtlions=# select * from err_e1_4;

rawdata | rawbytes

-------------------------------+---------+-----------------------------------------------------------------+---------+---------+------------------------------------------------------------+-

--------------------------------------------------------------------------+----------

2013-04-30 23:01:47.314301+08 | e1_4 | gpfdist://o564gtser1:8001/e1.txt [/home/gpadmin/gpfdist/e1.txt] | 1 | | missing data for column "name" |

1001,gtlions,18, |

2013-04-30 23:03:02.002321+08 | e1_4 | gpfdist://o564gtser1:8001/e1.txt [/home/gpadmin/gpfdist/e1.txt] | 4 | | value too long for type character varying(20), column name |

1004,leon.lee ddddddddddddddddddddd,13,note4 |

2013-04-30 23:04:11.169352+08 | e1_4 | gpfdist://o564gtser1:8001/e1.txt [/home/gpadmin/gpfdist/e1.txt] | 4 | | value too long for type character varying(20), column name |

1004,leon.leeddddddddddddddddddddddddddddddddddddddddddddddddddd,13,note4 |

2013-04-30 23:05:56.151371+08 | e1_4 | gpfdist://o564gtser1:8001/e1.txt [/home/gpadmin/gpfdist/e1.txt] | 4 | | value too long for type character varying(20), column name |

1004,leon.leeddddddddddddddddddddddddddddddddddddddddddddddddddd,13,note4 |

(4 rows)

还有一种特殊的格式就是把一样记录当成一个字段来处理：

gtlions=# create external table e1_5(txt text) location('gpfdist://o564gtser1:8001/e1.txt') format 'text' (delimiter 'off');

CREATE EXTERNAL TABLE

gtlions=# select * from e1_5;

txt

---------------------------------------------------------------------------

1001,gtlions,18,

1002,,19,note2

1003,keyte,32,

1004,leon.leeddddddddddddddddddddddddddddddddddddddddddddddddddd,13,note4