在单独的linux服务器安装gpfdist之后，运行报错：
[gpadmin@g01~]$ gpfdist gpfdist: error while loading shared libraries: libyaml-0.so.1: cannot open shared object file: No such file or directory [gpadmin@g01~]$ /data/greenplum/bin/gpfdist: error while loading shared libraries: libyaml-0.so.1: cannot open shared object file: No such file or directory [gpadmin@g01~]$ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
查看，已经存在了，只是不识别而已：
[gpadmin@g01~]$ find /data/greenplum/ -name libyaml-0.so.1 /data/greenplum/lib/libyaml-0.so.1 [gpadmin@g01~]$ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
Google到一篇文章https://discuss.pivotal.io/hc/en-us/articles/218036377-gpfdist-shows-error-message-while-loading-shared-libraries-libyaml-0-so-1-cannot-open-shared-object-file-No-such-file-or-directory-，说可能是版本比较低引起的
Cause
As explained in the 4.3.7.x release note shownbelow, the library file libyaml-0.so.1 was missed in the releases prior to4.3.7.0.
26075 | Loaders: gpload | 4.3.7.0 | The Greenplum Database Loaders for Red Hat Enterprise 5 and 6 did not contain the library libyaml-0.so.1. This issue has been resolved. |
查看安装的loaders版本信息：
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [gpadmin@g01lib]$ ll /home/gpadmin/ total 29200 -rw-rw-r-- 1 gpadmin gpadmin 163 May 11 17:43 1 -rw-rw-r-- 1 gpadmin gpadmin 141 May 12 09:50 gpfdist.log -rwxr-xr-x 1 gpadmin gpadmin 15027400 May 10 2016 greenplum-loaders-4.3.8.2-build-1-RHEL5-x86_64.bin -rw-r--r-- 1 gpadmin gpadmin 14863550 May 11 16:43 greenplum-loaders-4.3.8.2-build-1-RHEL5-x86_64.zip [gpadmin@g01lib]$ [gpadmin@g01lib]$ ll /data/greenplum/lib/libyaml* lrwxrwxrwx 1 gpadmin gpadmin 18 May 11 17:17 /data/greenplum/lib/libyaml-0.so.1 -> libyaml-0.so.1.0.0 -r-xr-xr-x 1 gpadmin gpadmin 319651 Feb 1 2014 /data/greenplum/lib/libyaml-0.so.1.0.0 lrwxrwxrwx 1 gpadmin gpadmin 18 May 11 17:17 /data/greenplum/lib/libyaml.so -> libyaml-0.so.1.0.0 [gpadmin@g01lib]$ |
看版本是已经是4.3.8.x了，而且lib目录下也已经有了libyaml的版本了，只是不识别，看来不是版本过低的问题导致的。
继续google了下，查看到文章https://discuss.pivotal.io/hc/en-us/community/posts/206003588-Load-Tools-error-while-loading-shared-libraries-libyaml-0-so-1上面dillon网友有说添加一个lib的path路径LD_LIBRARY_PATH ，尝试，问题解决，不会报错了。
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [gpadmin@g01~]$ vim .bash_profile …… export GPHOME=/data/greenplum export LD_LIBRARY_PATH=/data/greenplum/lib PATH=$PATH:$HOME/bin:$GPHOME/bin #export GPHOME=/data/greenplum export PATH [gpadmin@g01~]$ gpfdist 2017-05-12 13:48:53 13870 INFO Before opening listening sockets - following listening sockets are available: 2017-05-12 13:48:53 13870 INFO IPV6 socket: [::]:8080 2017-05-12 13:48:53 13870 INFO IPV4 socket: 0.0.0.0:8080 2017-05-12 13:48:53 13870 INFO Trying to open listening socket: 2017-05-12 13:48:53 13870 INFO IPV6 socket: [::]:8080 2017-05-12 13:48:53 13870 INFO Opening listening socket succeeded 2017-05-12 13:48:53 13870 INFO Trying to open listening socket: 2017-05-12 13:48:53 13870 INFO IPV4 socket: 0.0.0.0:8080 Serving HTTP on port 8080, directory /home/gpadmin |
[gpadmin@g01~]$ gpload -f gpload.yml
/data/greenplum/bin/gpload: line 3:/data/greenplum/greenplum_path.sh: No such file or directory
[gpadmin@g01~]$
解决：自己重建一个
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [root@g01~]# more /data/greenplum/greenplum_path.sh
GPHOME=/data/greenplum
# Replace with symlink path if it is present and correct if [ -h ${GPHOME}/../greenplum-db ]; then GPHOME_BY_SYMLINK=`(cd ${GPHOME}/../greenplum-db/ && pwd -P)` if [ x"${GPHOME_BY_SYMLINK}" = x"${GPHOME}" ]; then GPHOME=`(cd ${GPHOME}/../greenplum-db/ && pwd -L)`/. fi unset GPHOME_BY_SYMLINK fi #setup PYTHONHOME if [ -x $GPHOME/ext/python/bin/python ]; then PYTHONHOME="$GPHOME/ext/python" fi PYTHONPATH=$GPHOME/lib/python PATH=$GPHOME/bin:$PYTHONHOME/bin:$PATH LD_LIBRARY_PATH=$GPHOME/lib:$PYTHONHOME/lib:$LD_LIBRARY_PATH OPENSSL_CONF=$GPHOME/etc/openssl.cnf export GPHOME export PATH export LD_LIBRARY_PATH export PYTHONPATH export PYTHONHOME export OPENSSL_CONF
[root@g01~]# |
blog源地址：http://blog.csdn.net/mchdba/article/details/72848708，作者mchdba 黄杉，谢绝转载。
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [gpadmin@g01~]$ gpload -f gpload.yml 2017-05-12 16:15:01|INFO|gpload session started 2017-05-12 16:15:01 2017-05-12 16:15:01|ERROR|could not connect to database: FATAL: no pg_hba.conf entry for host "192.168.2.72", user "gpadmin", database "yueworld_db", SSL off . Is the Greenplum Database running on port 5432? 2017-05-12 16:15:01|INFO|rows Inserted = 0 2017-05-12 16:15:01|INFO|rows Updated = 0 2017-05-12 16:15:01|INFO|data formatting errors = 0 2017-05-12 16:15:01|INFO|gpload failed [gpadmin@g01~]$ |
解决办法：gpload是在greenplum的数据库服务器上面执行的，不是在单独的gpfdist的服务器上执行。
[gpadmin@dwhm01_2_111 ~]$ gpload -fgpload.yml
2017-05-12 16:57:41|INFO|gpload sessionstarted 2017-05-12 16:57:41
2017-05-12 16:57:41|INFO|setting schema'public' for table 't01'
2017-05-12 16:57:41|INFO|started gpfdist -p8090 -P 8091 -f "/home/gpadmin/gpdextdata/t01.txt/home/gpadmin/gpdextdata/t02.txt" -t 30
2017-05-12 16:57:42|ERROR|ERROR: cannot change a readable external table"t01"
encountered while running INSERT INTOpublic."t01" ("id","name") SELECT"id","name" FROMext_gpload_0e8b9f06_36f1_11e7_907a_0017fa009565
2017-05-12 16:57:42|INFO|rows Inserted = 0
2017-05-12 16:57:42|INFO|rows Updated = 0
2017-05-12 16:57:42|INFO|data formattingerrors = 0
2017-05-12 16:57:42|INFO|gpload failed
[gpadmin@dwhm01_2_111 ~]$
【问题解决】：需要提前建立的可读写的外部表，sql语句如：create writable external table public.t01……
[gpadmin@g01~]$ gpload -f gpload.yml 2017-05-12 17:35:42|INFO|gpload session started 2017-05-12 17:35:42 2017-05-12 17:35:42|INFO|setting schema 'public' for table 't01' 2017-05-12 17:35:42|INFO|started gpfdist -p 8090 -P 8091 -f "/home/gpadmin/gpdextdata/t01.txt /home/gpadmin/gpdextdata/t02.txt" -t 30 2017-05-12 17:35:42|ERROR|could not run SQL "create external table ext_gpload_5e20945e_36f6_11e7_a5f0_0017fa004ce3(id int,name text)location('gpfdist://192.168.2.72:8091//home/gpadmin/gpdextdata/t01.txt%20/home/gpadmin/gpdextdata/t02.txt') format'text' (delimiter ',' null '\\N' escape '\\' ) segment reject limit 25 ": ERROR: permission denied: no privilege to create a readable gpfdist(s) external table
2017-05-12 17:35:42|INFO|rows Inserted = 0 2017-05-12 17:35:42|INFO|rows Updated = 0 2017-05-12 17:35:42|INFO|data formatting errors = 0 2017-05-12 17:35:42|INFO|gpload failed [gpadmin@g01~]$
|
【解决办法：】
赋予ssb用户创建外部表权限：
要允许用户创建外部表，否则建外部表时会得到错误
ERROR: permission denied: no privilege to create a readable gpfdistexternal table
修改配置文件/data/greenplum-db-4.3.12.0/gpconfig/gpinitsystem_config，添加参数
gp_external_enable_exec = on # enable external tables withEXECUTE.
gp_external_grant_privileges = on #enable create http/gpfdist for non su's
允许非超级管理员创建外部表，必须重启数据库服务（使用gpstop –u命令该参数文件修改不生效），才能生效。
重启greenplum后，查看是否有权限：
yueworld_db=# show gp_external_enable_exec ; gp_external_enable_exec ------------------------- on (1 row)
yueworld_db=# yueworld_db=# show gp_external_grant_privileges; gp_external_grant_privileges ------------------------------ off (1 row)
yueworld_db=# |
看到gp_external_grant_privileges还是off的，证明在gpinitsystem_config里面配置gp_external_grant_privileges无效的，所以直接使用命令行设置：
[gpadmin@dwhm01_2_111 gpconfig]$ gpconfig -c gp_external_grant_privileges -v on 20170515:10:50:05:017519 gpconfig:dwhm01_2_111:gpadmin-[INFO]:-completed successfully [gpadmin@dwhm01_2_111 gpconfig]$ gpconfig -c gp_external_enable_exec -v on 20170515:10:50:12:017592 gpconfig:dwhm01_2_111:gpadmin-[INFO]:-completed successfully [gpadmin@dwhm01_2_111 gpconfig]$ |
添加配置完后，再重启greenplum集群，生效：
[gpadmin@dwhm01_2_111 gpconfig]$ gpstop -r 20170515:10:51:08:017869 gpstop:dwhm01_2_111:gpadmin-[INFO]:-Starting gpstop with args: -r 20170515:10:51:08:017869 gpstop:dwhm01_2_111:gpadmin-[INFO]:-Gathering information and validating the environment... 20170515:10:51:08:017869 gpstop:dwhm01_2_111:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20170515:10:51:08:017869 gpstop:dwhm01_2_111:gpadmin-[INFO]:-Obtaining Segment details from master... 20170515:10:51:27:017869 gpstop:dwhm01_2_111:gpadmin-[INFO]:-Cleaning up leftover shared memory 20170515:10:51:33:017869 gpstop:dwhm01_2_111:gpadmin-[INFO]:-Restarting System... [gpadmin@dwhm01_2_111 gpconfig]$ [gpadmin@dwhm01_2_111 gpconfig]$ [gpadmin@dwhm01_2_111 gpconfig]$ psql -d yueworld_db psql (8.2.15) Type "help" for help.
yueworld_db=# show gp_external_grant_privileges; gp_external_grant_privileges ------------------------------ on (1 row)
yueworld_db=# show gp_external_enable_exec ; gp_external_enable_exec ------------------------- on (1 row)
yueworld_db=# |
然后再执行数据导入操作，OK，不会报权限错误了：
[gpadmin@g01~]$ gpload -f gpload.yml 2017-05-15 10:53:47|INFO|gpload session started 2017-05-15 10:53:47 2017-05-15 10:53:47|INFO|setting schema 'public' for table 't01' 2017-05-15 10:53:47|INFO|started gpfdist -p 8090 -P 8091 -f "/home/gpadmin/gpdextdata/t01.txt /home/gpadmin/gpdextdata/t02.txt" -t 30 2017-05-15 10:53:48|INFO|running time: 0.37 seconds 2017-05-15 10:53:48|INFO|rows Inserted = 0 2017-05-15 10:53:48|INFO|rows Updated = 0 2017-05-15 10:53:48|INFO|data formatting errors = 6 2017-05-15 10:53:48|INFO|gpload succeeded [gpadmin@g01~]$ |
[gpadmin@g01~]$ gpload -f g2.yml 2017-05-19 17:02:38|INFO|gpload session started 2017-05-19 17:02:38 2017-05-19 17:02:38|INFO|started gpfdist -p 8090 -P 8091 -f "/data/greenplum/gpextdata/t21.txt /data/greenplum/gpextdata/t22.txt" -t 30 2017-05-19 17:02:38|ERROR|ERROR: permission denied for schema dw encountered while running INSERT INTO "dw"."t02" ("id","name") SELECT "id","name" FROM ext_gpload_e8104a8a_3c71_11e7_bb89_0017fa004ce3 2017-05-19 17:02:38|INFO|rows Inserted = 0 2017-05-19 17:02:38|INFO|rows Updated = 0 2017-05-19 17:02:38|INFO|data formatting errors = 0 2017-05-19 17:02:38|INFO|gpload failed [gpadmin@g01~]$ |
【解决方案】，在greenplum集群中，赋予mch账号权限。
先在greenplum集群的master上执行权限赋予操作 yueworld_db=# grant all on schema dw to mch; GRANT yueworld_db=#
然后去gpload服务器上执行 [gpadmin@g01~]$ gpload -f g2.yml 2017-05-19 17:09:59|INFO|gpload session started 2017-05-19 17:09:59 2017-05-19 17:09:59|INFO|started gpfdist -p 8090 -P 8091 -f "/data/greenplum/gpextdata/t21.txt /data/greenplum/gpextdata/t22.txt" -t 30 2017-05-19 17:09:59|INFO|running time: 0.32 seconds 2017-05-19 17:09:59|INFO|rows Inserted = 8 2017-05-19 17:09:59|INFO|rows Updated = 0 2017-05-19 17:09:59|INFO|data formatting errors = 0 2017-05-19 17:09:59|INFO|gpload succeeded [gpadmin@g01~]$ |