Perl 是一种功能丰富的计算机编程语言。借取了 C、sed、awk、shell 脚本语言以及很多其他程序语言的特性，其中最重要的特性是它内部集成了正则表达式的功能。它就像 C 一样强大，像 awk、sed 等脚本描述语言一样方便，被 Perl 语言爱好者称之为“一种拥有各种语言功能的梦幻脚本言”、“Unix中的王牌工具”。
Perl 语言连接 Greenplum 也是通过底层 C 接口 libpq 进行交互，如果我们要使用 Perl 做数据库交互，首先我们需要安装 Perl 的数据库标准模块 Perl DBI，然后再安装相应的 DBD 驱动，这里连接 Greenplum 采用的是 DBD::pg ( https://metacpan.org/pod/DBD::Pg ) 模块。
安装
这里采用较为简单的 RPM 安装方式。
rpm -ivh postgresql-libs-9.2.24-1.el7_5.x86_64.rpm
rpm -ivh perl-DBI-1.627-4.el7.x86_64.rpm
rpm -ivh perl-version-0.99.07-3.el7.x86_64.rpm
rpm -ivh perl-DBD-Pg-2.19.3-4.el7.x86_64.rpm
Perl 连接 Greenplum
连接整体比较简单，DBI驱动已经将连接方式抽象为易于理解的格式，下面通过几个例子来展示，读者可以关注如何配置连接方式、如何创建连接、如何增删改查等操作。
示例
1、建表
该示例主要演示连接字符串定义，数据库连接打开关闭几在数据库中创建表。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";
my $database = "postgres";
my $dsn = "DBI:$driver:dbname=$database;host=172.16.142.192;port=5432";
my $userid = "gpadmin";
my $password = "gpadmin";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(CREATE TABLE TC(ID INT, NAME TEXT););
my $rv = $dbh->do($stmt);
if($rv < 0){
print $DBI::errstr;
} else {
print "Table created successfully\n";
}
$dbh->disconnect();执行结果如下：
[root@gp1 ~]# ./create.pl
Opened database successfully
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
Table created successfully
2、插入数据
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";
my $database = "postgres";
my $dsn = "DBI:$driver:dbname=$database;host=172.16.142.192;port=5432";
my $userid = "gpadmin";
my $password = "gpadmin";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(INSERT INTO TC (ID,NAME)
VALUES (1, 'Chris'));
my $rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO TC (ID,NAME)
VALUES (2, 'Jenny'));
$rv = $dbh->do($stmt) or die $DBI::errstr;
print "Records created successfully\n";
$dbh->disconnect();执行结果如下：
[root@gp1 ~]# vim insert.pl
[root@gp1 ~]# chmod +x insert.pl
[root@gp1 ~]# ./insert.pl
Opened database successfully
Records created successfully
3、查询数据
!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";
my $database = "postgres";
my $dsn = "DBI:$driver:dbname=$database;host=172.16.142.192;port=5432";
my $userid = "gpadmin";
my $password = "gpadmin";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(SELECT id, name from TC;);
my $sth = $dbh->prepare( $stmt );
my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0){
print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n";
}
print "Operation done successfully\n";
$dbh->disconnect();执行结果如下：
[gpadmin@gp1 ~]$ vim select.pl
[gpadmin@gp1 ~]$ chmod +x select.pl
[gpadmin@gp1 ~]$ ./select.pl
Opened database successfully
ID = 2
NAME = Jenny
ID = 1
NAME = Chris
Operation done successfully
有关修改和删除的操作，基本与上面例子一致，只需要将代码中 qq() 中的内容替换为 DELETE 和 UPDATE 语句即可。
本文从CSDN（点击查看原文）转载而来。不代表烟海拾贝立场，如若转载，请注明出处：https://somirror.com/3693.html
