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 dbuserpasswordn"; } ? #接收传入的参数??? 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::errstrn"; ? $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}st",$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?????? （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |