Sinatra app to parse people names from structured biodiversity occurrence data, apply basic regular expressions and heuristics to disambiguate them, and then allow them to be claimed by authenticated users via ORCID. Authenticated users may also help other users that have either ORCID or Wikidata identifiers. The web application lives at https://bionomia.net.
Strings of text in the user interface are translatable via config/locales. Large pages of text are fully translatable in the views/static_i18n/ directory.
- ruby 2.7.1+
- Elasticsearch 7.5.0+
- MySQL 8.0.21
- Redis 4.0.9+
- Apache Spark 3+
- Unix-based operating system to use GNU parallel to process GBIF downloads
$ git clone https://github.com/bionomia/bionomia.git
$ cd bionomia
$ gem install bundler
$ bundle install
$ mysql -u root bionomia < db/bionomia.sql
$ cp config/settings/development.yml.sample config/settings/development.yml
# Adjust content of development.yml
# Copy and edit production.yml and test.yml as above
$ rackup -p 4567 config.ru
There are occasional hiccups at either the data publisher's end or at GBIF. BEFORE each successive data refresh routine outlined below, first produce a list of datasets whose total number of records here are greater than what is currently available at GBIF. If they are, this is a first indication that republication has gone wrong, harvesting at GBIF's end has skipped a number of records, or a dataset has recently been unpublished. The script below assumes a previous, full round of data processing has already been executed and that there are counts on datasets produced from Step 7.
$ RACK_ENV=production ./bin/gbif_datasets.rb --verify
See the Apache Spark recipes here and here for quickly importing into MySQL the occurrence csv from a DwC Archive downloaded from GBIF or from a custom Bionomia download. Apache Spark is used to produce the necessary source csv files for the "Parse & Populate Agents" and "Populate Taxa" steps below.
Unfortunately, gbifIDs are not persistent. These occasionally disappear through processing at GBIF's end. As a result, claims may no longer point to existing occurrence records. The following produces a csv file for how many claims and attributions will be orphaned. An alternative, more efficient process is found in an Apache Spark script.
$ RACK_ENV=production ./bin/csv_dump.rb -d ~/Desktop -o
$ RACK_ENV=production ./bin/populate_agents.rb --truncate --directory /directory-to-spark-csv-files/
# Can start 2+ workers, each with 40 threads to help speed-up processing
$ RACK_ENV=production sidekiq -c 40 -q agent -r ./application.rb
# For remote client, point to the server REDIS_URL and likewise, adjust MySQL connection strings in config
$ REDIS_URL=redis://192.168.2.4:6379 RACK_ENV=production sidekiq -c 40 -q agent -r ./application.rb
$ RACK_ENV=production ./bin/populate_taxa.rb --truncate --directory /directory-to-spark-csv-files/
# Can start 2+ workers, each with 40 threads to help speed-up processing
$ RACK_ENV=production sidekiq -c 40 -q taxon -r ./application.rb
First, import all users and user_occurrences content from production.
$ RACK_ENV=production ./bin/populate_existing_claims.rb --truncate --directory /directory-to-spark-csv-files/
# Can start 2+ workers, each with 40 threads to help speed-up processing
# might need to increase ulimit
$ ulimit -n 8192
$ RACK_ENV=production sidekiq -c 40 -q existing_claims -r ./application.rb
The above workers will produce a sidekiq dead set for all recordedByID, identifiedByID values that could not be resolved, which can then be exported:
$ RACK_ENV=production ./bin/populate_existing_claims.rb --dead "dead_identifiers.csv"
Export a csv pivot table (for import performance) of all claims made by User::GBIF_AGENT_ID.
$ RACK_ENV=production ./bin/populate_existing_claims.rb --export "gbif_claims.csv"
Finally, import the bulk claims on production (will create users & make public if wikidata):
$ RACK_ENV=production ./bin/bulk_claim.rb --file "gbif_claims.csv"
$ RACK_ENV=production ./bin/populate_search.rb --index agent
$ RACK_ENV=production ./bin/populate_search.rb --index taxon
Or from scratch:
$ RACK_ENV=production ./bin/populate_search.rb --rebuild
$ RACK_ENV=production ./bin/gbif_datasets.rb --new
$ RACK_ENV=production ./bin/gbif_datasets.rb --flush
$ RACK_ENV=production ./bin/gbif_datasets.rb --remove-without-agents
$ RACK_ENV=production ./bin/gbif_datasets.rb --counter
Or from scratch:
$ RACK_ENV=production ./bin/gbif_datasets.rb --populate
# For best performance, first rebuild the Elasticsearch user index
# RACK_ENV=production ./bin/populate_search.rb --index user
$ RACK_ENV=production ./bin/populate_occurrence_count.rb -t -a -j
# Can start 2+ workers, each with 40 threads to help speed-up processing
$ RACK_ENV=production sidekiq -c 40 -q occurrence_count -r ./application.rb
Unfortunately, gbifIDs are not persistent. These occasionally disappear through processing at GBIF's end. As a result, claims may no longer point to an existing occurrence record and these must then be purged from the user_occurrences table. The following are a few methods to produce a csv file of affected users and to then delete the orphans:
# csv dump requires approx. 15min for 20M attributions
$ RACK_ENV=production ./bin/orphaned_user_occurrences.rb -d ~/Desktop -o
Then use this orphaned.csv file to run through the orphaned records and delete them:
$ RACK_ENV=production ./bin/orphaned_user_occurrences.rb -f orphaned.csv
This misses the ignored attributions, so also execute:
mysql> DELETE user_occurrences FROM user_occurrences LEFT JOIN occurrences ON user_occurrences.occurrence_id = occurrences.gbifID WHERE occurrences.gbifID IS NULL AND user_occurrences.visible = false;
mysql> DELETE article_occurrences FROM article_occurrences LEFT JOIN occurrences ON article_occurrences.occurrence_id = occurrences.gbifID WHERE occurrences.gbifID IS NULL;
To migrate tables, use mydumper and myloader. But for even faster data migration, drop indices before mydumper then recreate indices after myloader. This is especially true for the three largest tables: occurrences, occurrence_recorders, and occurrence_determiners whose indices are (almost) larger than the tables themselves.
$ brew install mydumper
$ mydumper --user root --password <PASSWORD> --database bionomia --tables-list agents,occurrences,occurrence_recorders,occurrence_determiners,occurrence_counts,taxa,taxon_occurrences --compress --threads 8 --rows 1000000 --outputdir /Users/dshorthouse/Documents/bionomia_dump
$ apt-get install mydumper
# Restore tables use nohup into a new database `bionomia_restore`. See https://blogs.oracle.com/jsmyth/apparmor-and-mysql if symlinks might be used in the MySQL data directory to another partition.
$ nohup myloader --database bionomia_restore --user bionomia --password <PASSWORD> --threads 2 --queries-per-transaction 100 --compress-protocol --overwrite-tables --directory /home/dshorthouse/bionomia_restore &
One way to make this even faster is to copy database files from one database to another rather than dropping/truncating and importing, but this has to be done with a bit of care.
Take site offline and in the bionomia database, remove the tablespaces from the tables that will be overwritten. Before removing, it's a good idea to keep the *.ibd files on-hand in the event something bad happens and they need to be restored.
mysql> ALTER TABLE `agents` DISCARD TABLESPACE;
mysql> ALTER TABLE `occurrences` DISCARD TABLESPACE;
mysql> ALTER TABLE `occurrence_determiners` DISCARD TABLESPACE;
mysql> ALTER TABLE `occurrence_recorders` DISCARD TABLESPACE;
mysql> ALTER TABLE `occurrence_counts` DISCARD TABLESPACE;
mysql> ALTER TABLE `taxa` DISCARD TABLESPACE;
mysql> ALTER TABLE `taxon_occurrences` DISCARD TABLESPACE;
Now copy the *.ibd files for the above 6 tables from the bionomia_restore database into the bionomia database data directory, reset the permissions, then import the tablespaces:
mysql> ALTER TABLE `agents` IMPORT TABLESPACE;
mysql> ALTER TABLE `occurrences` IMPORT TABLESPACE;
mysql> ALTER TABLE `occurrence_determiners` IMPORT TABLESPACE;
mysql> ALTER TABLE `occurrence_recorders` IMPORT TABLESPACE;
mysql> ALTER TABLE `occurrence_counts` IMPORT TABLESPACE;
mysql> ALTER TABLE `taxa` IMPORT TABLESPACE;
mysql> ALTER TABLE `taxon_occurrences` IMPORT TABLESPACE;
The MIT License (MIT)
Copyright (c) David P. Shorthouse
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.