Greenplum外部表操作.docx
《Greenplum外部表操作.docx》由会员分享,可在线阅读,更多相关《Greenplum外部表操作.docx(5页珍藏版)》请在冰豆网上搜索。
![Greenplum外部表操作.docx](https://file1.bdocx.com/fileroot1/2023-1/30/0b0f63c4-1d97-4d8d-a12f-d96e54ec6a58/0b0f63c4-1d97-4d8d-a12f-d96e54ec6a581.gif)
Greenplum外部表操作
Greenplum外部表操作
本文将首先测试使用外部表的方式进行加载和卸载数据。
外部表-externaltables,分为只读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@o564gtser1gpfdist]$ls/usr/local/greenplum-db/bin/gpfdist-l-rwxr-xr-x1gpadmingpadmin81999401-3006:
31/usr/local/greenplum-db/bin/gpfdist几个常用的参数:
-d指定目录-p指定端口-l指定日志文件[gpadmin@o564gtser1gpfdist]$pwd/home/gpadmin/gpfdist[gpadmin@o564gtser1gpfdist]$gpfdist-d./-p8001-l./gpfdist.log&[1]4382[gpadmin@o564gtser1gpfdist]$[2013-04-3021:
46:
21][WRNgpfdist.c:
2049]CreatingthesocketfailedServingHTTPonport8001,directory/home/gpadmin/gpfdist【测试gpfdist服饰是否开启:
wgethttp:
//10.10.2.6:
8001/e1.txt8001端口后面的目录默认是从gpfdist启动的目录开始的不是从/根目录开始netstat-anp|grep8001{查看端口监听状况}正常显示:
tcp00:
:
:
8001:
:
:
*LISTEN64186/gpfdistgpadmin用户在操作时不能操作root用户的文件夹和文件,用ll查看文件夹和文件属于哪个用户;chown-Rgpadmin:
gpadmin文件名;修改文件的用户和用户组-R递归】[gpadmin@o564gtser1gpfdist]$jobs[1]+Runninggpfdist-d./-p8001-l./gpfdist.log&上面我手工启动了一个gpfdist服务器,目录是/home/gpadmin/gpfdist,端口8001,日志文件是/home/gpadmin/gpfdist/gpfdist.log,将下来我们将生产一个文本文件e1.txt,并手工输入一些记录,然后我们从数据库中创建一个外部表读取这些记录。
[gpadmin@o564gtser1gpfdist]$cate1.txt1001'gtlions'
'leon.lee'35
[gpadmin@o564gtser1~]$psql-Ugtlionsgtlionspsql(8.2.15)Type"help"forhelp.--创建外部表e1gtlions=#
createexternaltablee1(idsmallint,namevarchar(30),agesmallint)location('gpfdist:
//o564gtser1:
8001/e1.txt')format'text';CREATEEXTERNALTABLE--读取外部表的数据gtlions=#select*frome1;id|name|age++1001|'gtlions'|181002|
'keven'|191003|'keyte'|321004|'leon.lee'|35(4rows)--我们发现name字段貌似存在了一点问题:
多了引号,实际上在平面文件中并不需要特别的加引号,因为我们在定义表结构的时候已经指明的字段类型,我们修改下外部记录,重新读取下就ok了:
gtlions=#select*frome1;id|name|age++1001|gtlions|18
1002|keven|191003|keyte|321004|
leon.lee|35(4rows)我们增加一个文件看看如何同时读取
外部表,我直接cp了e1.txt:
[gpadmin@o564gtser1gpfdist]$cpe1.txte1_a.txt[gpadmin@o564gtser1gpfdist]$cate*.txt1001gtlions181002keven
191003keyte321004leon.lee351001
leon.lee35由于外部表无法重新定义位置和格式信息,因此我们只能新加一个表:
gtlions=#createexternal
tablee1_1(idsmallint,namevarchar(20),agesmallint)location('gpfdist:
//o564gtser1:
8001/e1*.txt')format'text';CREATEEXTERNALTABLEgtlions=#select*frome1_1;id|name|age++1001|
gtlions|181002|keven|191003|keyte|
321004|leon.lee|351001|gtlions|181002|keven
|191003|keyte|321004|leon.lee|35(8rows)
这样我们可以看到外部表是支持C通配符的,接下里我们看下如何处理不同格式的记录,因为我们上面指定的是text,
默认的text用的是tab分隔符,我们将尝试使用别的分隔符,比如|、空格、逗号、星号等等来分割的记录,还有空值的情况。
首先我们看下空值的情况如何处理,来看下这样的情况:
[gpadmin@o564gtser1gpfdist]$cate1.txt1001gtlions
181002191003keyte32leon.lee35第2行和第4行分别有空值的列,我们要怎么处理这个情况下表呢?
我们先试试默认的情况下会是怎么读取的:
gtlions=#createexternaltablee1_2(idsmallint,namevarchar(20),agesmallint)location('gpfdist:
//o564gtser1:
8001/e1.txt')format'text';CREATEEXTERNALTABLEgtlions=#select*from
e1_2;ERROR:
invalidinputsyntaxforinteger:
""(seg1slice1o564gtser1:
40001pid=5458)DETAIL:
Externaltablee1_2,line4ofgpfdist:
//o564gtser1:
8001/e1.txt,columnid提示在第4行有错误的输入格式,其实第2行也是错误的记录,我们可以这样来重新定义外部表,为空值指定一个格式:
gtlions=#dropexternaltablee1_2;DROPEXTERNALTABLEgtlions=#createexternaltablee1_2(idsmallint,namevarchar(20),agesmallint)location('gpfdist:
//o564gtser1:
8001/e1.txt')format'text'(delimiterE'\x09'null'');CREATEEXTERNALTABLEgtlions=#select*frome1_2;id|name|age++1001|gtlions|181002|
|191003|keyte|32|leon.lee|35(4rows)接下我们使用|来分割记录:
[gpadmin@o564gtser1gpfdist]$cate1.txt1001|gtlions|181002||191003|keyte|321004|leon.lee|35gtlions=#dropexternaltablee1_2;DROPEXTERNALTABLEgtlions=#createexternaltablee1_2(idsmallint,namevarchar(20),agesmallint)location('gpfdist:
//o564gtser1:
8001/e1.txt')format'text'(DELIMITER'|'NULL'empty');CREATE
id
EXTERNALTABLEgtlions=#select*frome1_2;
|name|age++1001|gtlions|18
1002||191003|keyte|321004|leon.lee|35(4rows)只是很疑惑的是为什么null设置默认值不会生效~~!
!
!
!
!
接下来看下逗号分割:
[gpadmin@o564gtser1gpfdist]$cate1.txt1001,gtlions,181002,,191003,keyte,321004,leon.lee,35gtlions=#dropexternaltablee1_2;DROPEXTERNALTABLEgtlions=#createexternaltablee1_2(idsmallint,namevarchar(20),agesmallint)location('gpfdist:
//o564gtser1:
8001/e1.txt')format'text'(DELIMITER','NULL'empty');CREATEEXTERNALTABLEgtlions=#select*frome1_2;id|name|age++1001|gtlions|181002|
|191003|keyte|321004|leon.lee|35(4rows)gtlions=#createexternaltablee1_3(idsmallint,namevarchar(20),agesmallint)location('gpfdist:
//o564gtser1:
8001/e1.txt')format'csv';CREATEEXTERNALTABLEgtlions=#select*frome1_3;id|name|age++1001|
gtlions|181002||191003|keyte|
321004|leon.lee|35(4rows)我们可以看到这2个表是一
样的记录,只是定义方式不一样,这里就引入了一种新的格
式csv,其实我们都知道默认的csv是用逗号分割的。
接下来我们测试下如何直接修改外部表的字段定义,首先我们针对外部记录增加了一个note的列:
[gpadmin@o564gtser1gpfdist]$cate1.txt1001,gtlions,18,1002,,19,note21003,keyte,32,1004,leon.lee,13,note4gtlions=#select*frome1_4;ERROR:
extradataafterlastexpectedcolumn(seg0slice1
o564gtser1:
40000pid=6700)DETAIL:
Externaltablee1_4,line1ofgpfdist:
//o564gtser1:
8001/e1.txt:
"1001,gtlions,18,"gtlions=#alterexternaltablee1_4addcolumnnotesvarchar(30);ALTEREXTERNALTABLEgtlions=#select*frome1_4;id|name|
18
age|notes+++1001|gtlions|
|1002||19|note21003|keyte|32|
1004|leon.lee|13|note4(4rows)可以看到和普通的heap表一样可以直接操作表结构的定义。
再来看异常的处理:
[gpadmin@o564gtser1gpfdist]$cate1.txt1001,gtlions,18,1002,,19,note21003,keyte,32,1004,leon.leeddddddddddddddddddddddddddddddddddddddddddddddddddd,13,note4gtlions=#createexternaltablee1_4(idsmallint,namevarchar(20),agesmallint,notesvarchar(30))location('gpfdist:
//o564gtser1:
8001/e1.txt')
format'text'logerrorsintoerr_e1_4segmentrejectlimit2;NOTICE:
Errortable"err_e1_4"doesnotexist.AutogeneratinganerrortablewiththesamenameCREATEEXTERNALTABLEgtlions=#select*frome1_4;NOTICE:
Found1dataformattingerrors(1ormoreinputrows).Rejectedrelatedinputdata.id|name|age|
notes+++1001|gtlions|18|1002|
|19|note21003|keyte|32|(3rows)提示我们有数
据格式错误,我们上面有定义了一个异常格式的记录的处理方式是放入到err_e1_4,注意这个表对应的外部表每次查询触发的异常都会写入到这个日志表:
gtlions=#select*from
err_e1_4;
cmdtime|relname|
filename
|linenum|
bytenum|
errmsg
rawdata
rawbytes
++
+
+2013-04-30
23:
01:
47.314301+08|e1_4|gpfdist:
//o564gtser1:
8001/e1.txt
[/home/gpadmin/gpfdist/e1.txt]|1||missingdataforcolumn"name"
|1001,gtlions,18,
|2013-04-3023:
03:
02.002321+08|e1_4|gpfdist:
//o564gtser1:
8001/e1.txt
[/home/gpadmin/gpfdist/e1.txt]|4||valuetoolongfortypecharactervarying(20),columnname|1004,leon.leeddddddddddddddddddddd,13,note4
|2013-04-3023:
04:
11.169352+08|e1_4|gpfdist:
//o564gtser1:
8001/e1.txt
[/home/gpadmin/gpfdist/e1.txt]|4||valuetoolongfortypecharactervarying(20),columnname|1004,leon.leeddddddddddddddddddddddddddddddddddddddddddddddddddd,13,note4|2013-04-3023:
05:
56.151371+08|e1_4|
gpfdist:
//o564gtser1:
8001/e1.txt
[/home/gpadmin/gpfdist/e1.txt]|4||valuetoolongfortypecharactervarying(20),columnname|1004,leon.leeddddddddddddddddddddddddddddddddddddddddddddddddddd,13,note4|(4rows)还有一种特殊的格
式就是把一样记录当成一个字段来处理:
gtlions=#createexternaltablee1_5(txttext)
location('gpfdist:
//o564gtser1:
8001/e1.txt')format'text'(delimiter'off');CREATEEXTERNAL
TABLEgtlions=#select*frome1_5;
txt
1001,gtlions,18,1002,,19,note21003,keyte,32,1004,leon.leeddddddddddddddddddddddddddddddddddddddddddddddddddd,13,note4(4rows)
相关参数:
gp_external_max_segs,用于设置可以有多少个节点同时地连接到外部表的文件,默认64[gpadmin@o564gtser1~]$gpconfig-sgp_external_max_segsValuesonallsegmentsareconsistentGUC:
gp_external_max_segsMastervalue:
64Segmentvalue:
64-EOF-