![0a2202414302470a2afec1bf56934160.png](https://i-blog.csdnimg.cn/blog_migrate/15fedeb2d555e54a09a2d9ae8c77c917.jpeg)
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.rpmrpm -ivh perl-DBI-1.627-4.el7.x86_64.rpmrpm -ivh perl-version-0.99.07-3.el7.x86_64.rpmrpm -ivh perl-DBD-Pg-2.19.3-4.el7.x86_64.rpm
Perl 连接 Greenplum
连接整体比较简单,DBI 驱动已经将连接方式抽象为易于理解的格式,下面通过几个例子来展示,读者可以关注如何配置连接方式、如何创建连接、如何增删改查等操作。
示例
–、建表
该示例主要演示连接字符串定义,数据库连接打开关闭几在数据库中创建表。
#!/usr/bin/perluse 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";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";}$dbh->disconnect();
执行结果如下:
[root@gp1 ~]# ./create.plOpened database successfullyNOTICE: 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/perluse 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";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";$dbh->disconnect();
执行结果如下:
[root@gp1 ~]# vim insert.pl[root@gp1 ~]# chmod +x insert.pl[root@gp1 ~]# ./insert.plOpened database successfullyRecords created successfully
三、查询数据
!/usr/bin/perluse 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";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] . ""; print "NAME = ". $row[1] ."";}print "Operation done successfully";$dbh->disconnect();
执行结果如下:
[gpadmin@gp1 ~]$ vim select.pl[gpadmin@gp1 ~]$ chmod +x select.pl[gpadmin@gp1 ~]$ ./select.plOpened database successfullyID = 2NAME = JennyID = 1NAME = ChrisOperation done successfully
有关修改和删除的操作,基本与上面例子一致,只需要将代码中 qq() 中的内容替换为 DELETE 和 UPDATE 语句即可。
End~