Tuesday, 17 May 2011
Software quality control at GBIF
GBIF projects have always been open source and hosted at their Google Code sites (e.g. GBIF Occurrencestore or the IPT). So in theory it was always possible for everyone to check every commit and review it. We've set up a Jenkins server however that does continuous integration for us which means that every time a change is made to one of our projects it is checked out and a full build is being run including all tests, code quality measurements (I'm going to get back to those later), web site creation (e.g. Javadocs) and publishing of the results to our Maven repository.
This is the first step in our new process. Every commit is checked in this way and we've had great success improving the stability of our builds in this way. Our Jenkins server is publicly visible at the URL http://hudson.gbif.org (background on the Hudson name in the URL can be found on Wikipedia).
As part of the process Jenkins also calls a code quality server called Sonar. Our Sonar instance is public as well. Take a look at the metrics for the IPT for example. You'll see a lot of information about our code, good and bad. We're not yet using this information extensively but are looking into useful metrics to incorporate them more closely into our development process. One example are some Coding Conventions to make the code consistent and easier to understand for everybody.
Once the build has finished the Sonar stage the results of the build are pushed to our Maven repository (which is running a Nexus server). That means we now have up to date SNAPSHOT builds of all our projects available (to use in our and your projects).
At the moment we don't have a lot of code contributions from outside of the GBIF to our projects but we hope that by making our development process more transparent we can encourage others to take a look as well.
We're always open for suggestions, questions and comments about our code base.
Monday, 16 May 2011
Here be dragons - mapping occurrence data
Approximately 82% of the GBIF occurrence records have latitude and longitude recorded, but these often contain errors - typically typos, and often one or both of lat and long reversed. Map 1, below, plots all of the verbatim (i.e. completely unprocessed) records that have a latitude and longitude and claim to be in the USA. Note the common mistakes, which result in glaring errors: reversed longitude produces the near-perfect mirror over China; reversed latitude produces a faint image over the Pacific off the coast of Chile; reversing both produces an even fainter image off Australia; setting 0 for lat or long produces tell tale straight lines over the Prime Meridian and the equator.
![]() | |
| Map 1: Verbatim (unprocessed) occurrence data coordinates for the USA |
The results of doing a lookup against the overlay are shown in Map 2, where a number of bugs in the processing are still visible: parts of the mirror over China are still visible; none of the coastal waters that are legally US territory (i.e. Exclusive Economic Zone of 200 nautical miles off shore) are shown; the Aleutian Islands off the coast of Alaska are not shown; and, some spots around the world are allowed through, including 0,0 and a few seemingly at random.
![]() |
| Map 2: Results of current data portal processing for occurrences in the USA |
While not an especially difficult query to formulize, a word to the wise: if you're doing this kind of reverse geocode lookup, remember to build your query by scoping the distance query within its enclosing polygon, like so
where the_geom && ST_GeomFromText(#{point}, 4326) and distance(the_geom, geomfromtext(#{point}, 4326)) < 0.001This buys an order of magnitude improvement in query response time!
With a thin webservice wrapper from Jersey, we have the GIS pieces built. We opted for a webservice approach to allow us to ultimately expose this quality control utility externally in the future. Since we process in Hadoop, we experienced huge stress on this web service - we were DDOS'ing ourselves. I mentioned a similar approach in my last entry, where we alleviated the problem with load balancing across multiple machines. And in case anyone is wondering why we didn't just use Google's reverse-geocoding webservice, the answer is twofold - first, it violates their terms of use, and second, even if we were allowed, they hold a rate limit on how many queries you can send over time, and that would have brought our workflow to its knees.
The last piece of the puzzle is adding the call to the webservice from a Hive UDF and adding it to our workflow, which is reasonably straight forward. The result of the new processing is shown in Map 3, where the problems of Map 2 are all addressed.
![]() |
| Map 3: Results of new processing workflow for occurrences in the USA |
You can find the source of the reverse-geocode webservice at the Google code site for the occurrence-spatial project. Similarly you can browse the source of the Hadoop/Hive workflow and the Hive UDFs.
Wednesday, 11 May 2011
The GBIF Spreadsheet Processor - an easy option to publish data
Most of data publishers in the GBIF Network use software wrappers to make data available on the web. To set up those tools, usually an institution or an individual needs to have certain degrees of technical capacity, and this more or less raises the threshold for publishing biodiversity data.
Imaging an entomologist who deals with collections and monographs everyday, the only thing s/he does on a PC is Word or Excel. S/he's got no student to help with, but keen to share the data before s/he retires. What is s/he going to do?
One of our tools is built to support this kind of scenario - the GBIF Darwin Core Archive Spreadsheet Processor, usually we just call it "the Spreadsheet Processor."
The Spreadsheet Processor is a web application that one can:
- Use templates provided on the web site;
- Fill and upload(or email) the xls file;
- Get a Darwin Core Archive file as the result.
This is a pretty straight-forward approach to prepare data for publishing, because the learning curve is flat if users already know how to use Excel, how to upload a file on a web site.
When the spreadsheet template is uploaded to the page, the web app first parses the values in the metadata sheet to generate an eml.xml, and then the occurrence or checklist sheet to generate an meta.xml and csv file. These files are then collected and zipped according to Darwin Core Archive standard - ready to download.
With a DwC-A file, the data is in a standardized format and ready to be published. In the example scenario above, this entomologist can either only share them among colleagues, or, send them to the nearest GBIF node which hosts IPT. Since IPT can digest a DwC-A file and publish it, the entomologist doesn't need to know the usage of IPT. To update it, s/he can revise the spreadsheet, create and send DwC-A to the node again.
P.S. This manual explains how to publish and register data in DwC-A format.
Tuesday, 10 May 2011
Reworking the HIT, after reworking the Portal processing
- harvesting specimen and occurrence data from data publishers,
- writing that data in its raw form to the database, and
- transforming raw data into its processed form running quality assurance routines (such as date and terrestrial point validation) and tying it to the backbone "nub" taxonomy.
When it is complete, the new Portal processing is actually going to do step 3. In the new processing, data will be extracted from the MySQL database into HBase (using sqoop) where quality assurance routines can be run much more quickly. Running outside of the MySQL database means that there won't be any more competition between steps 2 and 3 - step 3 constantly locking the raw data table in order to run its routines. That will mean the HIT will be able to write raw data uninterrupted to the database.
Friday, 6 May 2011
Improving Hive join performance using Oozie
Please note that this was adopted using Hive version 0.5 but in Hive 0.7 there are significant improvements to joinsFor the purposes of this explanation, let's consider the following simple example, where a table of verbatim values is being processed into four tables in a star schema:
To generate the leaves of the star, we have three simple queries (making use of a simple UDF to produce the increment IDs):
CREATE TABLE institution_code AS
SELECT rowSequence(), institution_code
FROM verbatim_record
GROUP BY institution_code;
CREATE TABLE collection_code AS
SELECT rowSequence(), collection_code
FROM verbatim_record
GROUP BY collection_code;
CREATE TABLE catalogue_number AS
SELECT rowSequence(), catalogue_number
FROM verbatim_record
GROUP BY catalogue_number;
To build the core of the star the simple approach is to issue the following SQL:
CREATE TABLE parsed_content AS
SELECT v.id AS id, ic.id AS institution_code_id,
cc.id AS collection_code_id, cn.id AS catalogue_number_id
FROM verbatim_record v
JOIN institution_code ic ON v.institution_code=ic.institution_code
JOIN collection_code cc ON v.collection_code=cc.collection_code
JOIN catalogue_number cn ON v.catalogue_number=cn.catalogue_number;
What is important to note is that the JOIN is across 3 different values, and this results in a query plan with three sequential MR jobs, a very large intermediate result set, which is ultimately passed through the final Reduce in the Hive planning.
By using Oozie (see the bottom of this post for pseudo workflow config), we are able to produce three temporary join tables, in a parallel fork, and then do a single join to bring it all back together.
# parallel join 1
CREATE TABLE t1 AS
SELECT v.id AS id, ic.id AS institution_code_id
FROM verbatim_record v JOIN institution_code ic ON v.institution_code=ic.institution_code;
# parallel join 2
CREATE TABLE t2 AS
SELECT v.id AS id, cc.id AS collection_code_id
FROM verbatim_record v JOIN collection_code cc ON v.collection_code=cc.collection_code
# parallel join 3
CREATE TABLE t3 AS
SELECT v.id AS id, ic.id AS institution_code_id
FROM verbatim_record v JOIN catalogue_number cn ON v.catalogue_number=cn.catalogue_number;
CREATE TABLE parsed_content AS
SELECT v.id AS id, t1.institution_code_id
t2.collection_code_id, t3.catalogue_number_id
FROM verbatim_record v
JOIN t1 ic ON v.id=t1.id
JOIN t2 cc ON v.id=t2.id
JOIN t3 cn ON v.id=t3.id;
Because we have built the join tables in parallel, and join on the foreign key only, Hive compiles to a single MR job, and runs much quicker.
In reality our tables are far more complex, and we use a Map side JOIN for the institution_code since it is small, but for our small cluster and the following table sizes we saw a reduction from several hours to 40 minutes to compute these tables.
- verbatim_record: 284 million
- collection_code: 1.5 million
- catalogue_number: 199 million
- institution_code: 8 thousand
Pseudo workflow config for this:
... ... ... ...
Wednesday, 4 May 2011
Line terminating characters breaking Darwin Core Archive
Recently I have been bug hunting a large dataset (DwC - Archive) that from a casual glance would look OK at the publisher side, but upon hitting the parser several records would be rejected because of the occurrence of line terminating characters in the records themselves (Hex value 0A). On top of that the individual record would be replaced by one empty line due to the illegal line termination AND another empty line would be added to that due to the tail end of the record appearing to the parser as the start of a new record, which of course would not be well-formed (thus being replaced with blank line number two). The parser will see a line that has too few columns and drop it. Since the line was bisected the tail end will also be conceived of as an individual line with an insufficient number of columns.
Here is an example of a record that would be replaced by an empty line:
The line terminating characters seems to have been escaped but without achieving the desired result. The secondary effect of this error is that the record count is miscalculated since the parser merely counts the lines and therefore ends up with a larger number than the publisher expected (remember that the line terminating character breaks the data file by producing two lines with an incorrect number of columns). Incidentally this example can sometimes explain why we harvest MORE than 100% of the target records.
By using the Integrated Publishing Toolkit (IPT) illegal characters can be avoided and the publishers will benefit from a faster transition into data appearing live in the GBIF portal. http://www.gbif.org/informatics/infrastructure/publishing/
Fortunately I am working in a joint effort with the publisher’s team on ironing out the bumps on this resource so we can get the data published fast and prevent future errors of this sort.
Friday, 29 April 2011
The evolution of the GBIF Registry


The main disadvantages (for our concerns) of the UDDI Specification:
- Its lack of contact information at the BusinessService(Resource) level (contacts can only be added at the BusinessEntity(Organisation) level)
- Lack of more descriptive metadata on Organisation and Resources (lacking fields such as the address, homepage, phone of the organisation - sure you could provide all of this information through a complex use of UDDI's capabilities, but will result in unnecessary complexity to extract this information for third-party tools.
- Limited to a fixed specification and to a fixed API (although, the UDDI client libraries available are quite straightforward to use)
- General purpose specification, not easily adaptable for modeling the complexity of GBIF's network.
- Our software dated back to the beginning of the past decade (Systinet WASP UDDI).
- Third party consumers will need to know how to talk UDDI
- Create any kind of entities
- Create any kind of relation among them
- More detailed metadata (for entities and contacts)
- Ability to tag entities
- Individual credentials for each Institution/Organisation to provide the ability to add new or delete existing resources under their own Organisations (this is currently only available through the APIs or via admin management)
- Enhanced maintenance features (for admins)




