If you prefer a graphic interface, use pgAdmin III for Greenplum Database. This GUI client supports PostgreSQL databases with all standard pgAdmin III features, while adding support for Greenplum-specific features.
pgAdmin III for Greenplum Database supports the following Greenplum-specific features:
• External tables
• Append-optimized tables, including compressed append-optimized tables
• Table partitioning
• Resource queues
• Graphical EXPLAIN ANALYZE
• Greenplum server configuration parameters
Supported Client Applications 21 Figure 3.1 Greenplum Options in pgAdmin III
Installing pgAdmin III for Greenplum Database
The installation package for pgAdmin III for Greenplum Database is available for download from the official pgAdmin III download site (http://www.pgadmin.org).
Installation instructions are included in the installation package.
Documentation for pgAdmin III for Greenplum Database
For general help on the features of the graphical interface, select Help contents from the Help menu.
For help with Greenplum-specific SQL support, select Greenplum Database Help from the Help menu. If you have an active internet connection, you will be directed to online Greenplum SQL reference documentation. Alternately, you can install the Greenplum Client Tools package. This package contains SQL reference
documentation that is accessible to the help links in pgAdmin III.
Performing Administrative Tasks with pgAdmin III
This topic highlights two of the many Greenplum Database administrative tasks you can perform with pgAdmin III: editing the server configuration, and viewing a graphical representation of a query plan.
Supported Client Applications 22 Editing Server Configuration
The pgAdmin III interface provides two ways to update the server configuration in postgresql.conf: locally, through the File menu, and remotely on the server through the Tools menu. Editing the server configuration remotely may be more convenient in many cases, because it does not require you to upload or copy postgresql.conf.
To edit server configuration remotely
1. Connect to the server whose configuration you want to edit. If you are connected to multiple servers, make sure that the correct server is highlighted in the object browser in the left pane.
2. Select Tools > Server Configuration > postgresql.conf. The Backend Configuration Editor opens, displaying the list of available and enabled server configuration parameters.
3. Locate the parameter you want to edit, and double click on the entry to open the Configuration settings dialog.
4. Enter the new value for the parameter, or select/deselect Enabled as desired and click OK.
5. If the parameter can be enabled by reloading server configuration, click the green reload icon, or select File > Reload server. Many parameters require a full restart of the server.
Viewing a Graphical Query Plan
Using the pgAdmin III query tool, you can run a query with EXPLAIN to view the details of the query plan. The output includes details about operations unique to Greenplum distributed query processing such as plan slices and motions between segments. You can view a graphical depiction of the plan as well as the text-based data output.
To view a graphical query plan
1. With the correct database highlighted in the object browser in the left pane, select Tools > Query tool.
2. Enter the query by typing in the SQL Editor, dragging objects into the Graphical Query Builder, or opening a file.
3. Select Query > Explain options and verify the following options:
• Verbose — this must be deselected if you want to view a graphical depiction of the query plan
• Analyze — select this option if you want to run the query in addition to viewing the plan
4. Trigger the operation by clicking the Explain query option at the top of the pane, or by selecting Query > Explain.
The query plan displays in the Output pane at the bottom of the screen. Select the Explain tab to view the graphical output. For example:
Supported Client Applications 23 Figure 3.2 Graphical Query Plan in pgAdmin III
Database Application Interfaces
You may want to develop your own client applications that interface to Greenplum Database. PostgreSQL provides a number of database drivers for the most commonly used database application programming interfaces (APIs), which can also be used with Greenplum Database. These drivers are not packaged with the Greenplum Database base distribution. Each driver is an independent PostgreSQL development project and must be downloaded, installed and configured to connect to Greenplum Database. The following drivers are available:
Table 3.3 Greenplum Database Interfaces
API PostgreSQL Driver Download Link
ODBC pgodbc Available in the Greenplum Database
Connectivity package, which can be downloaded from Pivotal Network.
JDBC pgjdbc Available in the Greenplum Database
Connectivity package, which can be downloaded from Pivotal Network.
Perl DBI pgperl http://gborg.postgresql.org/project/pgperl
Python DBI pygresql http://www.pygresql.org
General instructions for accessing a Greenplum Database with an API are:
Supported Client Applications 24 1. Download your programming language platform and respective API from the
appropriate source. For example, you can get the Java development kit (JDK) and JDBC API from Sun.
2. Write your client application according to the API specifications. When programming your application, be aware of the SQL support in Greenplum Database so you do not include any unsupported SQL syntax. See the Greenplum Database Reference Guide for more information.
Download the appropriate PostgreSQL driver and configure connectivity to your Greenplum Database master instance. Greenplum provides a client tools package that contains the supported database drivers for Greenplum Database. Download the client tools packagefrom Pivotal Network and documentation from Pivotal Documentation.
Third-Party Client Tools
Most third-party extract-transform-load (ETL) and business intelligence (BI) tools use standard database interfaces, such as ODBC and JDBC, and can be configured to connect to Greenplum Database. Greenplum has worked with the following tools on previous customer engagements and is in the process of becoming officially certified:
• Business Objects
• Microstrategy
• Informatica Power Center
• Microsoft SQL Server Integration Services (SSIS) and Reporting Services (SSRS)
• Ascential Datastage
• SAS
• Cognos
Greenplum Professional Services can assist users in configuring their chosen third-party tool for use with Greenplum Database.