Bizgres Clickstream Demo

As part of this series I have been reviewing and creating Amazon Machine Images (AMI) of some of the open source data mining, business intelligence software out there.

Apart from some earlier issues with getting java installed it has been straight-forward.

Please check this articles for the background

http://blog.vmdatamine.com/2007/07/bizgres-greenplum-on-ec2.html
https://wiki.dbadojo.com/howto-install-bizgres-on-centos
http://blog.vmdatamine.com/2007/07/running-bizgres-demo.html

To run the Bizgres Clickstream demo you will need to download this tarball and use the Clickstream User Guide.

My only issue was the environment variables I had a Postgresql database specified and it caused the demo to fail during the test of the database. Under the hood it was using a similar test suite (IVP) as outlined in the article on the demo.

After that everything was good. I had to open up the 8080 port on EC2 for the security group the instance was running to be able to view the Jasper reports.

I will write another article about my thoughts after running through the demo and clickstream demo.

I have included some screen shots as well (edited to remove my Firefox bar)

Have Fun

Paul


Here is the .bash_profile and bizgres_path.sh I setup

[bgadmin@domU-12-31-35-00-1C-C1 bizgresClickStream]$ cat ~/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
    . ~/.bashrc
fi

# User specific environment and startup programs

unset USERNAME


# For Bizgres

# This is the installed location of the Bizgres binaries. For example:

BIZHOME=/usr/local/bizgres
export BIZHOME

# Bizgres is installed with a Java Development Kit (JDK) installation that is compatible with Bizgres.

JAVA_HOME=/usr/local/jdk1.5.0_12/
export JAVA_HOME

# The LD_LIBRARY_PATH environment variable should point to the location of the PostgreSQL library files.
# For Solaris, this also points to the GNU compiler and readline library files as well.

LD_LIBRARY_PATH=$BIZHOME/pgsql/lib:$BIZHOME/lib
export LD_LIBRARY_PATH

# The default port number of the Bizgres/PostgreSQL database server.

#PGPORT=5432
#export PGPORT

# The location of the PostgreSQL manual pages.

MANPATH=$BIZHOME/doc:$MANPATH
export MANPATH

# The name of the default Bizgres/PostgreSQL database to use.

#PGDATABASE=bizdb
#export PGDATABASE

PGPATH=$BIZHOME
export PGPATH

# The host name of the Bizgres/PostgreSQL database server that clients use to connect to the database.

#PGHOST=`hostname`
#export PGHOST

#PGUSER=bgadmin
#export PGUSER

# Your PATH environment variable should point to the location of your JDK bin directory (listed first),
# the location of the Bizgres Loader bin directory,
# and the location of the Bizgres database engine (PostgreSQL) bin directory.

PATH=$JAVA_HOME/bin:$BIZHOME/pgsql/bin:$BIZHOME/client/loader/bin:$PATH
#PATH=$PATH:$HOME/bin

export PATH
unset USERNAME

 The bizgres_path.sh file 

[bgadmin@domU-12-31-35-00-1C-C1 bizgresClickStream]$ cat /usr/local/bizgres/bizgres_path.sh
BIZHOME=/usr/local/bizgres
PATH=$BIZHOME/pgsql/bin:$BIZHOME/client/loader/bin:$PATH
LD_LIBRARY_PATH=$BIZHOME/lib
MANPATH=$BIZHOME/doc:$MANPATH
PGPATH=$BIZHOME

export BIZHOME
export PATH
export LD_LIBRARY_PATH
export MANPATH
export PGPATH

 The screen dump of running make under the bizgresClickstream directory 

[bgadmin@domU-12-31-35-00-1C-C1 bizgresClickStream]$ make
Tomcat package is complete, good.
bizgresClickStream package is complete, good.
Found sed, good.
Found gtar, good.
Found JDK version 1.5, good.
Port 8080 appears to be free, good.
Tomcat appears not to be running, good.
Port 5432 appears to be free, good.
Port 10000 appears to be free, good.
Bizgres is installed at /usr/local/bizgres, good.
Testing Bizgres installation...
Bizgres test passes, good
Installing Tomcat...
Using CATALINA_BASE:   /usr/local/bizgres/demo/solutions/bizgresClickStream/tomcat
Using CATALINA_HOME:   /usr/local/bizgres/demo/solutions/bizgresClickStream/tomcat
Using CATALINA_TMPDIR: /usr/local/bizgres/demo/solutions/bizgresClickStream/tomcat/temp
Using JRE_HOME:       /usr/local/jdk1.5.0_12/
Waiting 5 secs for WAR deployment
Uncompressing database dump files...
done.
CREATE DATABASE
Starting...
nohup: appending output to `nohup.out'
Started.
KETL startup succeeded.
Bizgres clickstream installation complete.  Please read the documentation for next steps.

 Starting KETL and running the job to generate the reports 

[bgadmin@domU-12-31-35-00-1C-C1 bizgresClickStream]$ cd bin
[bgadmin@domU-12-31-35-00-1C-C1 bin]$ source clicksenv.sh

[bgadmin@domU-12-31-35-00-1C-C1 bin]$ ketl_ctl
KETL Console - Version 0.9 beta release

->connect localhost
Connected to domU-12-31-35-00-1C-C1.z-2.compute-1.internal
->job RUN_REPORTS execute 1 multi ignoredependencies
RUN_REPORTS
Job submitted to server for direct execution.

->status jobs
Executing
---------

Failed
------

Just Failed
-----------

Ready To Run
------------


->quit
[bgadmin@domU-12-31-35-00-1C-C1 bin]$ ls $WEBAPP/jasper
CumulativeEntryPages.jasper          DailyGeographicActivity.jrxml         ReferrersByWeek.jrxml
CumulativeEntryPages.jrxml           DailySiteActivity.jasper              SearchEngineReferrersByWeek.jasper
CumulativeExitPages.jasper           DailySiteActivity.jrxml               SearchEngineReferrersByWeek.jrxml
CumulativeExitPages.jrxml            DailyTopReferrers.jasper              WeeklyEntryPages.jasper
CumulativeGeographicActivity.jasper  DailyTopReferrers.jrxml               WeeklyEntryPages.jrxml
CumulativeGeographicActivity.jrxml   DailyTopSearchEngineReferrers.jasper  WeeklyExitPages.jasper
CumulativeSiteActivity.jasper        DailyTopSearchEngineReferrers.jrxml   WeeklyExitPages.jrxml
CumulativeSiteActivity.jrxml         DailyTrafficActivity.jasper           WeeklyGeographicActivity.jasper
CumulativeTopReferrers.jasper        DailyTrafficActivity.jrxml            WeeklyGeographicActivity.jrxml
CumulativeTopReferrers.jrxml         Daily_10_2004_11_18.html              WeeklySiteActivity.jasper
CumulativeTrafficActivity.jasper     Daily_13_2004_11_18.html              WeeklySiteActivity.jrxml
CumulativeTrafficActivity.jrxml      Daily_16_2004_11_18.html              WeeklyTopReferrers.jasper
Cumulative_12.pdf                    Daily_19_2004_11_18.html              WeeklyTopReferrers.jrxml
Cumulative_15.pdf                    Daily_1_2004_11_18.html               WeeklyTopSearchEngineReferrers.jasper
Cumulative_18.pdf                    Daily_4_2004_11_18.html               WeeklyTopSearchEngineReferrers.jrxml
Cumulative_21.pdf                    Daily_7_2004_11_18.html               WeeklyTrafficActivity.jasper
Cumulative_3.pdf                     EntryPagesForWeek.jasper              WeeklyTrafficActivity.jrxml
Cumulative_6.pdf                     EntryPagesForWeek.jrxml               Weekly_11_2004_11_15.html
Cumulative_9.pdf                     ExitPagesForWeek.jasper               Weekly_14_2004_11_15.html
DailyEntryPages.jasper               ExitPagesForWeek.jrxml                Weekly_17_2004_11_15.html
DailyEntryPages.jrxml                GeographicByWeek.jasper               Weekly_20_2004_11_15.html
DailyExitPages.jasper                GeographicByWeek.jrxml                Weekly_2_2004_11_15.html
DailyExitPages.jrxml                 README.txt                            Weekly_5_2004_11_15.html
DailyGeographicActivity.jasper       ReferrersByWeek.jasper                Weekly_8_2004_11_15.html

Make sure port 8080 is open

 After I was finished, shutdown KETL and check the status 

[bgadmin@domU-12-31-35-00-1C-C1 bin]$ ketl_ctl
KETL Console - Version 0.9 beta release

->connect localhost
Connected to domU-12-31-35-00-1C-C1.z-2.compute-1.internal
->shutdown
...

->status
KETL Cluster Status
Registered Servers: 2
Alive Servers     : 0
Pending Jobs

Server    : domU-12-31-35-00-1C-C1.z-2.compute-1.internal
Status    : Shutdown
Start Time: 2007-07-20 08:01:06.234
Last Ping : 2007-07-20 08:16:59.446735
Executors (Stats)
    SQL: (Total: 2)
    KETL: (Total: 2)
    XMLSESSIONIZER: (Total: 1)
    OSJOB: (Total: 2)

Running Bizgres demo

So I got Bizgres installed on CentOS 4 running on EC2 (Xen Virtualization).
Here is the HOWTO recipe for doing the build.
Next I went through the Bizgres User Guide and ran the Instance Verification Program (IVP).

The first attempt through yesterday was dogged by some environment variables problems and a missing file.

The IVP Makefile expects to find a file in $BIZHOME called bizgres_path.sh. I checked and it does not exist, nor was it in the bizgres-0_9_GA.tar.bz2 tarball.

So I had to create that file. Save a file called bizgres_path.sh where you installed the software.

BIZHOME=/usr/local/bizgres
PATH=$BIZHOME/pgsql/bin:$BIZHOME/client/loader/bin:$PATH
LD_LIBRARY_PATH=$BIZHOME/lib
MANPATH=$BIZHOME/doc:$MANPATH
PGPATH=$BIZHOME

export BIZHOME
export PATH
export LD_LIBRARY_PATH
export MANPATH
export PGPATH

The next issue was the path.sh file which is part of the IVP.tar file.
If you installed the software in something other than /usr/local/bizgres or like me are using a symbolic link /usr/local/bizgres which points at /usr/local/bizgres-0.9_GA it will fail.

I needed to add the -follow option to the find to make that follow any symbolic links.

Here is my path.sh

#
# If BIZHOME is alreay set use its value to source bizgres_path.sh
# ow, start looking at /usr/local
#
if [ x"$BIZHOME" == x ]; then
 BIZHOME='/usr/local/bizgres'
fi

PATHSH=`find "$BIZHOME" -follow -name bizgres_path.sh | sort | tail -1`

if [ x"$PATHSH" != x ]; then
source $PATHSH
else
echo "Can not find the path variables file, exiting."
#exit 1
fi

After those two problems, I had some issues getting the PostgreSQL instance running, which I think was related to running initdb early.

After that the demo ran perfectly.

Next task is to get streamSQL running as these products have a time limited evaluation license.

I will run through the master and slave node process probably early next week.

Have Fun

Paul

Here is my screen dump of the demo run

[bgadmin@domU-12-31-36-00-3D-83 IVP]$ source path.sh
[bgadmin@domU-12-31-36-00-3D-83 IVP]$ make
./create_db.sh


This is an Installation Validation Program for Bizgres.
It will install a sample Bizgres Database on one machine.

After this script runs successfully,
you should have a running Bizgres Database named
dgtestdb, that you can connect to using the command:
psql dgtestdb

You can also load sample data into dgtestdb using the command:
./load-data.sh

If this script fails to execute, look in the file "IVP.log" for the reason why.


Creating the dgtestdb data directory...
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.
done.
Starting the instance located in dgtestdb ... done.
Creating the database "dgtestdb" ... done.
Creating table "bigtable1" in "dgtestdb"... done.
---------------------------------------------------------------
---------------------------------------------------------------
---------------------------------------------------------------

Congratulations: A Bizgres database has been installed successfully!

You can now connect to the database using the command:
psql -p 5432 dgtestdb

You can now load sample data into dgtestdb using the command:
./load-data.sh

./load-data.sh
make[1]: Entering directory `/home/bgadmin/demo/IVP/data-generator'
gcc -O3 -c main.c
gcc -O3 -c utils.c
gcc -o generator -O3 main.o utils.o
./generator seedfile.txt 1000000 > dbdata.txt
make[1]: Leaving directory `/home/bgadmin/demo/IVP/data-generator'

Loader Ver 2.0.13
======================
Time: 07-12-2007 20:01:49
Host: LOCALHOST
Port: 5432
Database: dgtestdb
Username: bgadmin
Password: 
Control File: file:/home/bgadmin/demo/IVP/copy.ctl
Destination: database
Batching: OFF
======================

Contacting entry DB... jdbc:postgresql://LOCALHOST:5432/dgtestdb?user=bgadmin

validating input streams...
Control file found | Data file found : '/home/bgadmin/demo/IVP/data-generator/dbdata.txt'

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Found control command...
LOAD ivp.bigtable1( a ,  b ,  c ,  d ,  e ,  f ,  g ,  h ,  i ,  j ,  k ,  l ,  m ,  n , o)
FROM '/home/bgadmin/demo/IVP/data-generator/dbdata.txt' WITH DELIMITER AS '|' NULL AS '' ESCAPE AS '\'

Checking if control command is valid...
Control command verified
DBWriter trying to connect to database host=LOCALHOST,port=5432,dbname=dgtestdb
DBWriter successfully connected to database host=LOCALHOST,port=5432,dbname=dgtestdb
DBWriter testing an empty COPY command
Test passed. COPY accepted by the backend
DBWriter trying to connect to database host=LOCALHOST,port=5432,dbname=dgtestdb
DBWriter successfully connected to database host=LOCALHOST,port=5432,dbname=dgtestdb
DBWriter testing an empty COPY command
Test passed. COPY accepted by the backend
DBWriter trying to connect to database host=LOCALHOST,port=5432,dbname=dgtestdb
DBWriter successfully connected to database host=LOCALHOST,port=5432,dbname=dgtestdb
DBWriter testing an empty COPY command
Test passed. COPY accepted by the backend
DBWriter trying to connect to database host=LOCALHOST,port=5432,dbname=dgtestdb
DBWriter successfully connected to database host=LOCALHOST,port=5432,dbname=dgtestdb
DBWriter testing an empty COPY command
Test passed. COPY accepted by the backend
Starting to load data.

..................
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Database dgtestdb - on LOCALHOST:5432
- Sent 250000 rows to this segment
- Sent 35667923 bytes to this segment

Database dgtestdb - on LOCALHOST:5432
- Sent 250000 rows to this segment
- Sent 35643409 bytes to this segment

Database dgtestdb - on LOCALHOST:5432
- Sent 250000 rows to this segment
- Sent 35662826 bytes to this segment

Database dgtestdb - on LOCALHOST:5432
- Sent 250000 rows to this segment
- Sent 35647055 bytes to this segment

TOTALS:

- Time          : 56.522 secs
- Rows Read     : 1000000 (non blank)
- Rows Loaded   : 1000000
- Bytes Loaded  : 142621213
- Load rate     : 2.4063938 Mbytes/sec
- Loaded Batches: 18
- Failed Batches: 0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=


No more control commands.
Application terminated with return code 0

The result from this next query should be 1000000

time psql -p 5432 dgtestdb -c "select count(*) from IVP.bigtable1"
count
---------
1000000
(1 row)


real    0m2.045s
user    0m0.000s
sys     0m0.000s

HOWTO Install bizgres on CentOS

As I mentioned before I have created an howto recipe for installing Bizgres onto CentOS.

This was a build rather than an install using the binary install provided of Greenplum's website mainly due to being CentOS rather than Redhat and the whole thing running on top of Xen virtual OS running on EC2.

The next step is the follow through the documentation to build the master and data nodes.

Also on the agenda for this week is getting a version of the StreamSQL database running.

More later...

Bizgres (Greenplum) on EC2

First cab of the rank to build was a greenplum or bizgres EC2 image.

I was following the install from source documentation from Greenplum's site, using a base CentOS 4 public EC2 image.

Java 1.5 SDK/JDK wasn't installed, so that was the first thing. I unfortunately found a bad lead in the form of this wiki on JavaOnCentOS.

In the end i ditched the rpmbuild method, ran the install file jdk-1_5_0_12-linux-i586.bin and created a symbolic link to /usr/local/bin.

The next thing missing from the doco was you need to set the JAVA_HOME environment variable and that PostgreSQL needs the devel package of both readline and zlib to build ok.

So 2 hours later I have bizgres installed on EC2. The actual ant build took 10 minutes.

I will write up a recipe/HOW-TO based on my screen dumps from putty over the weekend.

The next thing is to actually run the demo scripts.

Have Fun

Paul