Exploring unique values in iDigBio using Apache Spark¶

Matthew Collins and Alex Thompson

Data exploration for large datasets is always challenging. Often you are left with deciding between subsetting the dataset (randomly or on some facet), making slow progress waiting for results just to find that something needs to be fixed, or optimizing code for performance when you don't even know if the result is going to be interesting. Having a high-performance system capable of ad-hoc investigation has always been difficult and/or expensive.

iDigBio currently has over 45 million records with more than 200 data fields. Most are described by the Darwin Core and Audubon Core standards, some are unique to a particular provider's data use, and a handful are used internally by iDigBio. One question that comes up often is "What are the unique values in a field?"

As it turns out, that is a computationally expensive question to answer for a data set with 45 million records. Considering the dwc:country field, your first thought might be to write a SQL query like:

SELECT country, count(*) 
FROM all_idigbio GROUP BY country;

iDigBio's data flexibility and per-record performance comes from storing provided data as PostgreSQL JSON data types. In such a schema, there is no country column to be indexed and grouped by. In our testing, an analogous query on the JSON column takes far to long to be practical. For a searchable store, we use Elastic Search which unfortunately is also not optimal for this type of query. Elastic Search's indexes are tuned for returning the most common occurrences and not exhaustive lists. (See our top API endpoints for an example.)

Another option might be to get a CSV copy of iDigBio (which is around 32 GB) and write some code in Python or another language to iterate through lines and keep track of unique values. In our experience it is quite expensive to keep track of so many unique values, and adding multi-threading and multi-processing into the mix to manage the computational cost adds a great deal of operational complexity.

So what to do?

Apache Spark¶

If you have looked in to the big data analytics ecosystem in the past few years, you might have come across Apache Hadoop. Traditionally Hadoop has been hard: hard to install, hard to maintain, and hard to write code for. But the computation pattern that Hadoop implements, referred to as map-reduce, is a great pattern for doing computations over very large datasets. Hadoop was often the best solution for working with large datasets.

In the last two years a new computation framework has been open sourced and is growing in popularity: Apache Spark. Spark is much easier to use than Hadoop and in most cases performs faster. It can perform map and reduce operations similar to Hadoop (though the implementation is very different) which allows for a convenient and flexible way to express complex operations.

Three easy parts about how Spark works are:

1. It is happy to use text files for input
2. You don't need to write special code to make your analysis happen in parallel
3. The same code can be run on one local machine or a whole cluster of machines

This means we can get close to the convenience of the Python and CSV option above and also run it in parallel on either a local development machine or a whole compute cluster. All we need is a running install of Spark and a few lines of Python. (Spark also has libraries for Scala and R if you prefer those languages.)

During the iDigBio API Hackathon, Jorrit Poelen from Global Biotic Interactions put together some test code to join iDigBio records to Global Names Index names. Based on this trial, we decided to invest more into Spark.

To try Spark out on a larger scale, we built a 6-node, 96 thread Spark cluster. This is a test setup based on Cloudera's bundle of Spark and other Apache tools. We then wrote a Python script to read in both the raw and as-served occurrence CSV files that are in the Darwin Core Archive of all iDigBio data that can be downloaded from our full record set exports site. For each field, the script produces a CSV file with all of the unique values in that field and the number of records that had that value.

An abbreviated excerpt from the Python script showing the core Spark operations is below.

This still takes a while to run since it loops over each field. To do the 32 GB data set containing all data as indexed in Elastic Search and served in the portal, it loops about 75 times in a little over 2 hours with translates to about 750,000 records per second. For the raw data as originally provided to us, there are over 200 fields and that takes closer to 8 hours. Much of that time, perhaps as much as 25%, is the inefficient but convenient way we chose to write the results to CSV. Our code is nowhere near optimal and this is just a test cluster. Spark scales really well and we have about 275 servers here at the Advanced Computing and Information Systems Lab (ACIS) machine room.

Preliminary characterization¶

We made Python Pandas data frames by importing those text files into this iPython notebook. For some initial analysis we wanted to look at how many unique values there were, the cardinality, of each of the fields in the indexed data. Not all the fields were interesting. Many were provided by only one collection, some fields are designed to be unique, etc. So we trimmed off fields where more than 95% of the records had unique values and fields that had fewer than 100 unique values.

The result is a bar graph of how many unique values are present in iDigBio for about 55 of the indexed fields: (You can view this full-size by right-clicking on the image and choosing "View Image".)

The most interesting thing to notice is that dwc:occurenceID and dwc:catalogNumber were not more than 95% unique. The dwc:occurrenceID bar is a touch over 40 million (note the log scale) and there are 45 million records in this data dump. We're planning a deeper look at things that are used as identifiers in the future.

Some other highly unique fields were dwc:scientificName and dwc:recordedBy. There is a lot of room for exploration in these fields. With maybe 1.2 million species being classified, does iDigBio have a representative sample of all known species or is our scientific name field really messy? Did 1.1 million different people collect museum specimens in the last 200 years?

Comparing raw and cleaned data using frequency¶

In addition to the cardinality, we can also look at the frequency with which each value occurs within a field. And to look a little at our cleaning efficiency, we can look at the frequency distributions of values both before and after data goes through iDigBio's data cleaning efforts.

(An aside about iDigBio's cleaning: From the raw data, we try to standardize values before we put data in our searchable index. The reason is that when people are searching, it is best to guide them to using a canonical value like "united states" instead of having everyone construct something like "us or usa or united s. or...". We colloquially call this "cleaned" but really it is our best guess as to the intention of the provider. We do not change the raw data provided to us. We flag these changes in our portal and data api so you can see what we've done: http://portal.idigbio.org/portal/records/dd01dd35-ff49-4a7f-b445-851a918096b9)

Below are graphs showing the cumulation of records over each unique value for a given field. The green lines show the cleaned values for the field and the black line shows the raw data provided to us. The unique values along the x-axis are sorted from least common to most common to convey how rapidly the frequency of values changes.

Note that the actual values are not shown on the x-axis, there isn't room. The numbers on the x-axis are an increasing integer but you can tell how many unique values there are in a field from it. Also note that blank values in fields are not considered on the y-axis.

Here's the graph of dwc:country:

This is a typical curve for many of the fields in iDigBio. There is a long flat part representing many values that occur in only a few records followed by an upward curve for a few values that are common. Then finally the line spikes upward rapidly showing that a few values are in most of the data. You can see a slight knee where the line becomes vertical at the most frequent value: "united states".

From looking at the scales of this graph you can see that there are a lot of values in this field that you wouldn't expect. There are only about 195 countries in the world but in the raw data there are nearly 11,000 different values. When the field is cleaned up, the number of unique values drops significantly as multiple representations of the same data get merged.

Even after cleaning there are more then 7,000 unique values in the field. The second most common value, "brasil", is the Portuguese spelling of Brazil. Which is "correct"? There are lots of heavy misspellings that are hard to automatically correct and there are values that don't fit the definition of the field like "off shore norway" and "afrique centrale".

This is a good graph to keep in mind when you are trying to "just find the records in country X".

With the dwc:order field, things are a little different. Much of the data in iDigBio does not have higher taxonomy filled in so the raw data has fewer unique orders. Our standardization actually adds data to records from lookups in GBIF's backbone taxonomy. You can see the corrected curve ends at a higher cumulative number of records. However, even though we're adding data, the cleaned curve shows fewer unique values because we're adding in valid orders that likely already exist in the dataset and getting rid of non-standard orders. When running some test records sets through this notebook, we found some individual record sets where the corrected curve was to right of the raw curve indicating that we were adding more orders than that specific provider was sending us.

Here is the dwc:catalogNumber field. If catalog number was globally unique this would be a line with a slope of 1. Of course, they aren't but the lower section of the line does show that there are a lot of unique catalog numbers out there. Then you can see some knees and segments with slope 2, 3, etc. The big spike at the end is really interesting and we'll be looking at that soon. What few hundred thousand values of dwc:catalogNumber are responsible for nearly 20 million records? Can you answer this question using the iDigBio API?

We don't clean this field so there's no change between raw and indexed.

Locality shows some similar trends with the lower part of the curve being several line segments clearly showing values that appear exactly once, twice, and three times.

What is interesting about this graph is that we don't clean locality strings. All we do is lowercase them in the index. The cleaned line shows fewer unique values simply because of the number of locality strings that differ by case only.

Next steps¶

We are working on exposing our Spark cluster publicly so you don't have to transfer 30+ GB text files and manage Java memory settings to run iDigBio though your own Spark code. If that is something that interests you, send an email to idigbio@idigbio.org or join our API mailing list where we announce programming interfaces.

Spark isn't difficult to get started with yourself if you prefer. Installation of a stand-alone setup requires Java and any of the pre-build downloads extracted somewhere. (You don't need to worry about Hadoop version if you're developing locally.) The official quickstart has examples in Scala and Python.

Footnote¶

This blog post was written from an iPython Notebook. You can view the notebook through Github, clone or fork the github repository containing the notebook and Spark code, or look at the code in-line by clicking the button below.

If you want to play with this notebook without figuring out Spark, you can get a .zip file containing all the cleaned unique values in each field (5 GB) or the raw unique values (3 GB) in iDigBio. Just extract it in the directory after you clone the Github repository. The full iDigBio dump (20 GB) used to generate these values is also availible.