This is the sixth in a series of “How do I do X in Greenplum database” and today’s topic is How do I use Java in Greenplum.
Greenplum supports many languages and one of these is Java. This is defined as a language called “pljava”. Because it is Java, it still requires you to compile the code and put it in a jar that is your classpath. There are a couple of additional steps to configure this in Greenplum so I will go through these steps.
Prerequisites:
1. JDK installed either on the master or a desktop. If on a desktop, you’ll need to copy the jar file to the master so you can then copy it to the segments.
2. Logged into the master (mdw) as user gpadmin.
3. Methods must be public and static.
First step, write some Java and here is an example of a file named Example.java:
public class Example { public static String substring(String text, int beginIndex, int endIndex) { return text.substring(beginIndex, endIndex); } }
Create a manifest file named manifest.txt:
Manifest-Version: 1.0 Main-Class: Example Specification-Title: "Example" Specification-Version: "1.0" Created-By: 1.6.0_35-b10-428-11M3811 Build-Date: 09/28/2012 10:09 AM
Compile the Java:
javac *.java
Jar the file:
jar cfm analytics.jar manifest.txt *.class
Copy the analytics.jar to all Greenplum servers. The gphosts_file contains a list of all of your severs. An example of that is:
mdw smdw sdw1 sdw2 sdw3 sdw4
And the command to copy the Jar file:
gpscp -f gphosts_file analytics.jar =:/usr/local/greenplum-db/lib/postgresql/java/
Set your classpath variable inside Greenplum:
gpconfig -c pljava_classpath -v \'analytics.jar\'
Apply the configuration change:
gpstop -u
Install the pljava language and in this example, I’m putting it into the gpdb database. Note: this is a one time activity per database.
psql gpdb -f $GPHOME/share/postgresql/pljava/install.sql
Make sure the classpath is set correctly:
show pljava_classpath
It should say ‘analytics.jar’.
And finally, a working example:
create table temp (a varchar) distributed randomly; insert into temp values ('a string'); --Example function create or replace function java_substring(varchar, int, int) returns varchar as 'Example.substring' language java; --Example execution select java_substring(a, 1, 3) from temp;
This was a pretty simple example of using PL/Java. Enjoy!