Perl脚本访问Greenplum数据库安装指导

安装前准备

（1）操作系统(系统上面要安装一些必备的开发工具(比如gcc等))

linux-82:/home/PlODBC # cat/etc/SuSE-release

SUSE Linux EnterpriseServer 11 (x86_64)

VERSION = 11

PATCHLEVEL = 1

（2）安装所需的软件包

greenplum-connectivity-4.3.0.0-build-2-SuSE10-x86_64.zip

--GP官网下载，GP的JDBC和ODBC驱动

DBI-1.631.tar.gz                         

 --DBI是数据库的通用接口

DBD-ODBC-1.48.tar.gz   

--DBD是相应数据库的驱动程序unixODBC-2.2.12-204.3.1.x86_64.rpm                

--unixODBC的驱动管理器

（3）将上面的包上传到搭建环境的服务器上面，比如/home/PlODBC

安装GP驱动包

（1）      解压greenplum-connectivity-4.3.0.0-build-2-SuSE10-x86_64.zip

unzipgreenplum-connectivity-4.3.0.0-build-2-SuSE10-x86_64.zip

（2）      执行解压后得到greenplum-connectivity-4.3.0.0-build-2-SuSE10-x86_64.bin可执行文件

linux-82:/home/PlODBC # ./greenplum-connectivity-4.3.0.0-build-2-SuSE10-x86_64.bin

********************************************************************

    You must read and accept the EMCConnectivity license agreement

                             before installing

********************************************************************

                 SOFTWARE LICENSE ANDMAINTENANCE AGREEMENT

           *** IMPORTANT INFORMATION - PLEASE READ CAREFULLY  ***

ThisSoftware contains computer programs and other proprietary material and

information,the use of which is subject to and expressly conditioned upon

acceptanceof this Software License and Maintenance Agreement (the "Agreement").

ThisAgreement is a legally binding document between you (meaning the individual

person orthe entity that the individual represents that has obtained the

Softwarefor its internal productive use and not for outright resale) (the

"Customer")and EMC (which means (i) EMC Corporation, if Customer is located in

theUnited States; (ii) the local EMC sales subsidiary, if Customer is located

in acountry in which EMC Corporation has a local sales subsidiary; and (iii)

EMCInformation Systems International ("EISI"), if Customer is locatedoutside

theUnited States and in a country in which EMC Corporation does not have a

localsales subsidiary). Unless EMC agrees otherwise in writing, this Agreement

governsCustomer‘s use of the Software except to the extent all or any portion

of theSoftware is: (a) the subject of a separate written agreement; or (b)

governedby a third party licensor‘s terms and conditions. Capitalized terms

havemeaning stated in the Agreement.

IfCustomer does not have a currently enforceable, written and separately signed

softwarelicense agreement directly with EMC or the Distributor from whom

Customerobtained this Software, then by clicking on the "Agree" or"Accept" or

similarbutton at the end of this Agreement, or proceeding with the

********************************************************************

    Do you accept the EMC Connectivity licenseagreement? [yes | no]

********************************************************************

yes     ---------同意许可

********************************************************************

Providethe installation path for Greenplum Connectivity or press ENTER to

acceptthe default installation path:/usr/local/greenplum-connectivity-4.3.0.0-build-2

********************************************************************

********************************************************************

InstallGreenplum Connectivity into</usr/local/greenplum-connectivity-4.3.0.0-build-2>? [yes | no]

********************************************************************************

yes       ----------------保持默认的安装路径，你也可以自由指定安装路径

********************************************************************

/usr/local/greenplum-connectivity-4.3.0.0-build-2does not exist.

Create/usr/local/greenplum-connectivity-4.3.0.0-build-2 ? [ yes | no ]

(Selectingno will exit the installer)

********************************************************************

yes      ----------------创建安装目录           

Extractingproduct to /usr/local/greenplum-connectivity-4.3.0.0-build-2

********************************************************************

Installationcomplete.

GreenplumConnectivity is installed in /usr/local/greenplum-connectivity-4.3.0.0-build-2

Greenplumdocumentation is available for download at http://powerlink.emc.com.

********************************************************************************

（3）      配置Greenplum DB数据库驱动

查看安装目录时，如下：

linux-82:/usr/local/greenplum-connectivity-4.3.0.0-build-2/drivers/odbc# ll

total 24

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:49 psqlodbc-08.02.0400

drwxr-xr-x 6 hadoop users 4096 2013-11-15 09:52 psqlodbc-08.02.0500

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:53 psqlodbc-08.03.0400

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:54 psqlodbc-08.04.0200

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:55 psqlodbc-09.00.0200

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:56psqlodbc-09.02.0100

我们会看到有好几个版本的驱动，我们可以选择psqlodbc-08.02.0500版本的，再查看如下目录：

linux-82:/usr/local/greenplum-connectivity-4.3.0.0-build-2/drivers/odbc/psqlodbc-08.02.0500# ll

total 48

drwxr-xr-x 3 hadoop users  4096 2013-11-15 09:51 datadirect-51sp2_64

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:51 datadirect-52_64

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:52 datadirect-53sp2_64

-r--r--r-- 1 hadoop users 25746 2013-11-15 09:50 license.txt

-r--r--r-- 1 hadoop users 1383 2013-11-15 09:50 readme.txt

drwxr-xr-x 4hadoop users  4096 2013-11-15 09:50unixodbc-2.2.12

同样我们可以看到驱动管理器。

鉴于GP基于Postgresql8.2版本，我们这里面选择驱动为psqlodbc-08.02.0500，驱动管理器选择为datadirect-52_64。

所以，我们修改greenplum_connectivity_path.sh文件中的内容：

GP_ODBC_DRIVER=psqlodbc-08.02.0500          --值与实际目录名称相同

GP_ODBC_DRIVER_MANAGER=datadirect-52_64    --值与实际目录名称相同

注：该文件默认权限位444，是不允许编辑的，你可以手动修改文件的权限，也可以修改整个安装目录的权限位755，如下：

chmod -R 755greenplum-connectivity-4.3.0.0-build-2

保存greenplum_connectivity_path.sh后，要记得source，使环境变量生效，如下：

source greenplum_connectivity_path.sh

安装unixODBC驱动

（1）      直接安装RPM包

rpm -ivhunixODBC-2.2.12-204.3.1.x86_64.rpm

（2）      查看RPM包安装路径

linux-82:/home/PlODBC# rpm -ql unixODBC-2.2.12-204.3.1

/etc/unixODBC

/etc/unixODBC/ODBCDataSources

/etc/unixODBC/odbc.ini

/etc/unixODBC/odbcinst.ini

/usr/bin/dltest

/usr/bin/isql

/usr/bin/iusql

/usr/bin/odbc_config

/usr/bin/odbcinst

/usr/lib64/libboundparam.so.1

/usr/lib64/libboundparam.so.1.0.0

/usr/lib64/libgtrtst.so.1

/usr/lib64/libgtrtst.so.1.0.0

/usr/lib64/libodbc.so

/usr/lib64/libodbc.so.1

/usr/lib64/libodbc.so.1.0.0

/usr/lib64/libodbccr.so.1

/usr/lib64/libodbccr.so.1.0.0

/usr/lib64/libodbcinst.so

/usr/lib64/libodbcinst.so.1

/usr/lib64/libodbcinst.so.1.0.0

/usr/lib64/unixODBC

/usr/lib64/unixODBC/libesoobS.so

/usr/lib64/unixODBC/libesoobS.so.1

/usr/lib64/unixODBC/libesoobS.so.1.0.0

/usr/lib64/unixODBC/libmimerS.so

/usr/lib64/unixODBC/libmimerS.so.1

/usr/lib64/unixODBC/libmimerS.so.1.0.0

/usr/lib64/unixODBC/libnn.so

/usr/lib64/unixODBC/libnn.so.1

/usr/lib64/unixODBC/libnn.so.1.0.0

/usr/lib64/unixODBC/libodbcdrvcfg1S.so

/usr/lib64/unixODBC/libodbcdrvcfg1S.so.1

/usr/lib64/unixODBC/libodbcdrvcfg1S.so.1.0.0

/usr/lib64/unixODBC/libodbcdrvcfg2S.so

/usr/lib64/unixODBC/libodbcdrvcfg2S.so.1

/usr/lib64/unixODBC/libodbcdrvcfg2S.so.1.0.0

/usr/lib64/unixODBC/libodbcminiS.so

/usr/lib64/unixODBC/libodbcminiS.so.1

/usr/lib64/unixODBC/libodbcminiS.so.1.0.0

/usr/lib64/unixODBC/libodbcmyS.so

/usr/lib64/unixODBC/libodbcmyS.so.1

/usr/lib64/unixODBC/libodbcmyS.so.1.0.0

/usr/lib64/unixODBC/libodbcnnS.so

/usr/lib64/unixODBC/libodbcnnS.so.1

/usr/lib64/unixODBC/libodbcnnS.so.1.0.0

/usr/lib64/unixODBC/libodbcpsql.so

/usr/lib64/unixODBC/libodbcpsql.so.1

/usr/lib64/unixODBC/libodbcpsql.so.1.0.0

/usr/lib64/unixODBC/libodbcpsql.so.2

/usr/lib64/unixODBC/libodbcpsql.so.2.0.0

/usr/lib64/unixODBC/libodbcpsqlS.so

/usr/lib64/unixODBC/libodbcpsqlS.so.1

/usr/lib64/unixODBC/libodbcpsqlS.so.1.0.0

/usr/lib64/unixODBC/libodbctxtS.so

/usr/lib64/unixODBC/libodbctxtS.so.1

/usr/lib64/unixODBC/libodbctxtS.so.1.0.0

/usr/lib64/unixODBC/liboplodbcS.so

/usr/lib64/unixODBC/liboplodbcS.so.1

/usr/lib64/unixODBC/liboplodbcS.so.1.0.0

/usr/lib64/unixODBC/liboraodbcS.so

/usr/lib64/unixODBC/liboraodbcS.so.1

/usr/lib64/unixODBC/liboraodbcS.so.1.0.0

/usr/lib64/unixODBC/libsapdbS.so

/usr/lib64/unixODBC/libsapdbS.so.1

/usr/lib64/unixODBC/libsapdbS.so.1.0.0

/usr/lib64/unixODBC/libtdsS.so

/usr/lib64/unixODBC/libtdsS.so.1

/usr/lib64/unixODBC/libtdsS.so.1.0.0

/usr/lib64/unixODBC/libtemplate.so

/usr/lib64/unixODBC/libtemplate.so.1

/usr/lib64/unixODBC/libtemplate.so.1.0.0

/usr/share/doc/packages/unixODBC

/usr/share/doc/packages/unixODBC/AUTHORS

/usr/share/doc/packages/unixODBC/COPYING

/usr/share/doc/packages/unixODBC/ChangeLog

/usr/share/doc/packages/unixODBC/NEWS

/usr/share/doc/packages/unixODBC/README

/usr/share/doc/packages/unixODBC/README.GTK

/usr/share/doc/packages/unixODBC/README.SuSE

/usr/share/doc/packages/unixODBC/index.html

/usr/share/doc/packages/unixODBC/smallbook.gif

/usr/share/doc/packages/unixODBC/unixODBC.gif

可以可看到unixODBC配置文件位于/etc/unixODBC下面：

linux-82:/etc/unixODBC # ll /etc/unixODBC

total 4

drwxr-xr-x 2 root root 4096 2011-06-15 00:32 ODBCDataSources

-rw-r--r-- 1 root root    02011-06-15 00:32 odbc.ini

-rw-r--r-- 1 root root    02011-06-15 00:32 odbcinst.ini

（3）      编辑unixODBC的两个配置文件，如下：

linux-82:/etc/unixODBC # cat /etc/unixODBC/odbc.ini

[GreenplumDSN]

Driver =Greenplum          ----值要和/etc/unixODBC/odbcinst.ini中名字一致

Trace = 1

Debug=1

Database = noas            ----GP数据库名

Servername = 10.41.24.102   ----GP的IP地址

UserName = noas           ----GP用户名

Password = noas            ----GP用户密码

Port = 5432                ----GP访问端口号

ReadOnly = No

RowVersioning = No

DisallowPremature = No

ShowSystemTables = Yes

ShowOidColumn = No

FakeOidIndex = No

useDeclareFetch = 1

Fetch = 4096

UpdatableCursors = Yes

Protocol = 7.4-1

linux-82:/etc/unixODBC # cat /etc/unixODBC/odbcinst.ini

[Greenplum]

Description = PostgreSQL driver forGreenplum

Driver=/usr/local/greenplum-connectivity-4.3.0.0-build-2/drivers/odbc/psqlodbc-08.02.0500/unixodbc-2.2.12/psqlodbcw.so                   ------GP的ODBC驱动

UsageCount=1

FileUsage= 1

（4）      使用isql测试

linux-82:/etc/unixODBC # isql GreenplumDSN

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

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                      |

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

SQL> select user;

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

| current_user                                                   |

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

| noas                                                           |

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

SQLRowCount returns -1

1 rows fetched

SQL>

注：如果你在执行isql时，出现如下情况：

linux-82:/etc/unixODBC # isql GreenplumDSN

[ISQL]ERROR: Could not SQLConnect

这个问题很大情况下是你没有source文件greenplum_connectivity_path.sh导致的，执行source greenplum_connectivity_path.sh文件后，再执行就OK了，最好的办法是将source该文件加入到系统环境变量中。

安装DBI

（1）      解压DBI安装包

linux-82:/home/PlODBC # tar -zxvfDBI-1.631.tar.gz

（2）      查看DBI解压的目录

进入解压的目录，可以查看到Perl模块的安装说明文件INSTALL，里面有段内容为：

perl Makefile.PL

make

make test

make test TEST_VERBOSE=1   (if any of the t/* tests fail)

make install (if the tests look okay)

Perl的模块安装，基本上都是这几个步骤，test的内容不做强求，但是建议执行。

（3）      执行DBI安装

linux-82:/home/PlODBC/DBI-1.631 # perlMakefile.PL

linux-82:/home/PlODBC/DBI-1.631 # make

linux-82:/home/PlODBC/DBI-1.631 #make install

（4）      测试DBI安装是否成功

linux-82:/home/PlODBC/DBI-1.631 # perl-e ‘use DBI 1.625;‘

如果没有访问信息，则说明安装成功。

安装DBD-ODBC驱动

（5）      解压DBD-ODBC安装包

linux-82:/home/PlODBC # tar -zxvfDBD-ODBC-1.48.tar.gz

（6）      执行DBD-ODBC安装

perl Makefile.PL

make

make install

（7）      测试DBD-ODBC安装是否成功

linux-82:/home/PlODBC # perl -e‘use DBD::ODBC;‘

如果没有访问信息，则说明安装成功。

查看DBI整个安装信息：

linux-82:/home/PlODBC # perl -MDBI -e‘DBI->installed_versions;‘

      Perl            : 5.010000     (x86_64-linux-thread-multi)

      OS             : linux   (2.6.32)

      DBI             : 1.631

      DBD::Sponge     : 12.010003

      DBD::Proxy      : install_driver(Proxy) failed: Can‘tlocate RPC/PlClient.pm in @INC

      DBD::ODBC       : 1.48

      DBD::Gofer      : 0.015327

      DBD::File       : 0.42

      DBD::ExampleP   : 12.014311

      DBD::DBM        : 0.08

     可以看到DBI和DBD-ODBC都已经安装好了。

注：在编译DBD-ODBC时，如果报如下的错误：

linux-82:/home/PlODBC/DBD-ODBC-1.48 # perlMakefile.PL

**********

         Rememberto actually *READ* the README file!

         Andre-read it if you have any problems.

**********

OSNAME: linux

LANG: en_US.UTF-8

ODBCHOME:

LD_LIBRARY_PATH: /usr/local/greenplum-connectivity-4.3.0.0-build-2/drivers/odbc/psqlodbc-08.02.0500/datadirect-52_64/:/usr/local/greenplum-connectivity-4.3.0.0-build-2/lib:/sybaseclient/IQ-16_0/lib64:

DBROOT:

WINDIR:

II_SYSTEM:

Perl: 5.010000

ExtUtils::MakeMaker: 6.56

Command line options:

  u!= undef

  w!= undef

  e!= undef

  g!= 0

  x!= undef

  o=s=

Your LANG environment variable is set to"en_US.UTF-8"

This is known to cause problems in someperl installations - even stopping

this Makefile.PL from running withouterrors. If you have problems please

try re-running with LANG unset or with theutf part of LANG removed.

You are using a Perl configured withthreading enabled.

Please read the warnings in DBI about this.

You should also be aware that onnon-Windows platforms ODBC drivers come

in two forms, thread-safe andnon-thread-safe drivers and you may need

to make sure you are using the right one.

Press return to continue...  Looking for odbc_config in : nowhere

Looking for odbc_config in (PATH)/usr/local/greenplum-connectivity-4.3.0.0-build-2/bin:/sybaseclient/IQ-16_0/bin64:/sbin:/usr/sbin:/usr/local/sbin:/root/bin:/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/X11R6/bin:/usr/games:/usr/lib/mit/bin:/usr/lib/mit/sbin:/usr/local/freetds0.91/bin:/usr/bin:/sybaseclient/IQ-16_0/bin64:/home/hadoop/nodesetup/java/jdk1.6.0_29/bin:/sybaseclient/IQ-16_0/lib64

 Found odbc_config (via odbc_config) version 2.2.12

 odbc_config reports --prefix=/usr

 odbc_config reports --include-prefix=/usr/include

 odbc_config reports --lib-prefix=/usr/lib64

  butcannot find header files sql.h,sqlext.h,sqltypes.h in that path so ignoring

NOTE: Have you installed the unixodbc-devpackage

Looking for iodbc-config in PATH /usr/local/greenplum-connectivity-4.3.0.0-build-2/bin:/sybaseclient/IQ-16_0/bin64:/sbin:/usr/sbin:/usr/local/sbin:/root/bin:/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/X11R6/bin:/usr/games:/usr/lib/mit/bin:/usr/lib/mit/sbin:/usr/local/freetds0.91/bin:/usr/bin:/sybaseclient/IQ-16_0/bin64:/home/hadoop/nodesetup/java/jdk1.6.0_29/bin:/sybaseclient/IQ-16_0/lib64

 iodbc_config not found

odbc_config not found - ok, there are otherthings I can do

Still trying to guess ODBCHOME - lookingfor headers now

 trying /usr/include

 trying /usr/local/include

 trying /usr/pkg/include

 trying /usr/local/easysoft/unixODBC/include

I cannot find drivermanager header files. Perhaps you need to install the

unixodbc-dev package orthe iodbc-dev package

The DBD::ODBC moduleneeds to link with an ODBC ‘Driver Manager‘.

(The Driver Manager, inturn, needs one or more database specific ODBC

drivers. The DBD::ODBCmodule does _not_ include any ODBC drivers!)

You need to indicate where your ODBC DriverManager is installed.

You can do this by:

o setting the ODBCHOME environment variable

o running ‘perl Makefile.PL -o odbcdir‘

o adding path to odbc_config/iodbc_configto PATH

If you do not have an ODBC Driver Manageryou should try to get hold of

the unixODBC packages for your system orbuild it from source (see

http://www.unixodbc.org). If you installdriver manager packages you

need to include the "XXX-dev"package which includes the C header files.

上面的主要问题是没有设置如下变量导致的：

export ODBCHOME=/usr/local/unixODBC

export LD_LIBRARY_PATH=/usr/local/unixODBC/lib

但是我们安装的unixODBC是RPM形式的包，安装后的目录不满足要求，所以我们需要重新编译安装unixODBC：

解压unixODBC-2.3.2.tar.gz包并安装

tar -zxvf unixODBC-2.3.2.tar.gz

./configure --prefix=/usr/local/unixODBC

make

make install

unixODBC安装好之后，设置环境变量：

export ODBCHOME=/usr/local/unixODBC

export LD_LIBRARY_PATH=/usr/local/unixODBC/lib

然后再重新安装DBD-ODBC驱动，就可以成功安装了。

同时不要忘记，将之前配置的/etc/unixODBC/odbc.ini和/etc/unixODBC/odbcinst.ini文件都拷贝到/usr/local/unixODBC/etc目录下面，因为Perl脚本将/usr/local/unixODBC/etc这里获取GP的数据库配置驱动信息。

测试perl脚本

（1）      准备perl测试脚本，如下：

linux-82:/etc/unixODBC

#!/usr/bin/perl

####################################################################

# name:hellokitty.pl  

#describe: 测试perl访问Greenplum数据库

####################################################################

use DBI;

usestrict;

usePOSIX;

#判断入参个数：dbname数据库名; dbuser 用户名; password 密码

unless(@ARGV == 3) {

    die "Usage: $0 dbname dbuserpassword\n";

}

#接收传入的参数   

my$dbname          = $ARGV[0];

my$dbuser           = $ARGV[1];

my$password         =$ARGV[2];

my $sth;

my$col_num;

my @cols;

my@row_ary;

my $i;

my $dbh =DBI->connect("dbi:ODBC:$dbname",$dbuser,$password,

                         {AutoCommit => 0,

                         RaiseError => 0,

                         PrintError => 0,}

                         )

or die"Can‘t connect to Greenplum database: $DBI::errstr\n";

$sth =$dbh->prepare("select * from hello1");

$sth->execute();

# Thenumber of columns

$col_num= $sth->{NUM_OF_FIELDS};

# outputcolumns

@cols  = @{$sth->{NAME}};

printjoin("\t",@cols),"\n";

while (@row_ary = $sth->fetchrow_array ) {

   for($i=0; $i<$col_num; $i++){

            my $len = length $cols[$i] ;

            printf "%-${len}s\t",$row_ary[$i];

   }

   print "\n";

}

$sth->finish;

$dbh->disconnect();

exit 0;

（2）      测试过程：

linux-82:/etc/unixODBC # perl hellokitty.plGreenplumDSN noas noas

id        name

2        hello2      

1        hello1      

3        hello3