接口篇 – Perl连接Greenplum

Perl是一种功能丰富的计算机编程语言。借取了C、sed、awk、shell脚本语言以及很多其他程序语言的特性,其中最重要的特性是它内部集成了正则表达式的功能。它就像C一样强大,像awk、sed等脚本描述语言一样方便,被Perl语言爱好者称之为“一种拥有各种语言功能的梦幻脚本语言”、“Unix中的王牌工具”。

Perl语言连接Greenplum也是通过底层C接口libpq进行交互,如果我们要使用Perl做数据库交互,首先我们需要安装Perl的数据库标准模块Perl DBI,然后再安装相应的DBD驱动,这里连接Greenplum采用的是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驱动已经将连接方式抽象为易于理解的格式,下面通过几个例子来展示,读者可以关注如何配置连接方式、如何创建连接、如何增删改查等操作。

示例一 – 建表

该示例主要演示连接字符串定义,数据库连接打开关闭几在数据库中创建表。

#!/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

示例二 – 插入数据

#!/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

示例三 – 查询数据

!/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语句即可。

发表评论

电子邮件地址不会被公开。 必填项已用*标注