1 / 32

Federated Databases for the Geosciences

Federated Databases for the Geosciences. CSIG July 21, 2005 Douglas S. Greer. Overview. Database Federation Primer Basic concepts and principles DB2 Information Integrator The CHRONOS Federated Database Integration of 7 independently developed geoscience databases. Applications.

enrico
Télécharger la présentation

Federated Databases for the Geosciences

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Federated Databases for the Geosciences CSIG July 21, 2005 Douglas S. Greer

  2. Overview • Database Federation Primer • Basic concepts and principles • DB2 Information Integrator • The CHRONOS Federated Database • Integration of 7 independently developed geoscience databases

  3. Applications Top-Level View of a Federated Database Federated Database Data Source A Data Source B Data Source C Data Source D

  4. Federated DB Data Sources • Geographically Distributed Data Sources • Heterogeneous Data Sources • Relational Databases – most common • Non-relational Sources • Web Pages / Web Services • Flat Files

  5. Federated Databases • May or may not actually contain data • Federated database can create Global Views that define data in a uniform way across the data sources • Applications can then access data through the global view using the standardized SQL schema

  6. IBM DB2 Information Integrator • Provides a framework for strategic information integration to help applications access, manipulate and integrate diverse and distributed data sources across multiple servers in real time. • Can access structured and unstructured data types including relational databases such as Oracle, MySQL, PostgreSQL and MS SQL Server

  7. Connecting to the Remote Database • Step 1 – Create WRAPPER • Mechanism that the federated server uses to communicate with a data source • Identifies “Driver” code • Step 2 – Identify SERVER • Identifies the connection to a data source • Specifies which WRAPPER to use • Directly or Indirectly specifies the server name, server type, version, database name and special parameters

  8. Connecting to the Remote Database • Step 3 – Specify USER MAPPING • Maps between a federated database user and an authorized user (account and password) of a data source • Step 4 – Define NICKNAMES • Pointer to a table or view in a data source • Creates a binding between a local name and the data source name and hides the associated metadata details

  9. A Simple Federated View CREATE VIEW <Table_Name> AS SELECT (Database #1 SQL Command) UNION SELECT (Database #2 SQL Command) UNION SELECT (Database #3 SQL Command)

  10. Identifying Data Sources CREATE VIEW <Table_Name> AS SELECT ‘PALEOSTRAT’AS db_name genus_idAS genus … FROMPSTRAT.tbl_taxonomy … UNION SELECT ‘PALEOBIOLOGY’AS db_name genus_nameAS genus …

  11. Materialized Views • Federated databases normally do not store data locally. Data from remote sites is fetched as needed. • Materialized Views create a local copy of a Global-View. • Advantage: faster access • Disadvantages: Data may be stale. Refreshes required • Several of the CHRONOS Global-Views have versions that use materialized views to increase performance

  12. CHRONOS Project • Create a dynamic, interactive and time-calibrated framework for Earth history • Network of chronostratigraphy databases • Online stratigraphic record • Visualization and analytical tools • Develop a better understanding of fundamental Earth processes through time

  13. CHRONOS Federated Databases • The following databases are all part of the CHRONOS Federated Database at SDSC based on IBM’s DB2 Information Integrator • Neptune • PaleoStrat • PaleoBiology • Janus • TimeScale • FAUNMAP • MIOMAP

  14. Neptune Database • Developed at ETH Zürich and currently hosted by Iowa State University • Contains microfossil occurrences reported in DSDP and ODP samples • PostgreSQL based • Contains four basic types of data: Fossil Records, Taxonomy, Age models and Biogeography data • Schema contains approximately 20 tables with hundreds of thousands of taxonomic occurrences

  15. PaleoStrat Database • Developed at Boise State University in collaboration with the CHRONOS • Designed to support geoscience tools with broad applicability • Contains sedimentary, paleontologic and stratigraphic data • MS SQL Server based • Approximately 120 tables with thousands taxonomic occurrences • Data from other databases currently being loaded

  16. PaleoBiology Database • Hosted by the National Center for Ecological Analysis and Synthesis (NCEAS) at the University of California at Santa Barbara • Contains collection-based occurrence and taxonomic information about marine and terrestrial animals and plants • MySQL based • 16 tables with hundreds of thousands of taxonomic occurrences

  17. Janus Database • Database for the Integrated Ocean Drilling Program (IODP) hosted at Texas A&M University • Contains numerous types of ocean drilling data collected by United States, Japanese and European ships • Oracle based • Approximately 580 tables with millions of taxonomic occurrences

  18. TimeScale Database • Contains data and information from the 2004 Global Time Scale of the International commission on Stratigraphy and 19 other time scales • Supports web service conversions tools • PostgreSQL based • Approximately 25 tables with thousands of data records

  19. FAUNMAP Database • Hosted by Illinois State Museum • Contains information about the historical distribution of mammal species in the United States • MySQL based • Approximately 30 tables with tens of thousands of data records

  20. MIOMAP Database • Hosted by University of California, Berkeley • Contains comprehensive spatial and temporal analysis of Miocene mammal taxa for the Western United States • MySQL based • Thousands of records in a relatively small number of tables

  21. The Taxa Global-View • Simple View to list taxa in all of the databases • CHRONOS Taxa • Database Name • Table_Name • Taxon_ID • Genus • Species

  22. Taxa Global View Example

  23. Conop9 Application • Developed by Peter M. Sadler, Dept. of Earth Sciences, Univ. of California Riverside • Correlates stratigraphic sections by minimizing the number of inconsistencies in the order of first and last occurrences of fossils between sections • Originally developed for flat files then adapted to CHRONOS DB2/II global-views

  24. CONOP9 Data Correlation

  25. Conop9 Global View • Developed for the Conop9 Application • The Conop9 SDSC global-view provides a much larger collection of data than that available in the older flat file system • The CHRONOS global-view presents exactly the data needed by Conop9 but uses different SQL statements for each database – this involves joins across four tables in Neptune, seven tables in PaleoStrat and five tables in Janus

  26. Conop9 Global-View Attributes • CHRONOS Conop Global View Fields • Database Name • Genus • Species • Taxon_id – Used to create Conop9 input tables • Hole_id – Which stratigraphic section does this come from • LAD – Last Appearance Datum, newest observation of this taxa for this hole • FAD – First Appearance Datum, oldest observation of this taxa for this hole • LAD and FAD are the result of an SQL computation

  27. Conop9 Global View Example

  28. Age-Depth Plot

  29. Age/Depth Plot Global-Views • Uniform Global-View of hole location for ADP application • Surprisingly there are significant differences between databases • CHRONOS Hole_Summary • Database Name • Hole_ID • Latitude • Longitude

  30. Age/Depth Plot Views • Uniform Global-View for Hole/Taxa Description for ADP application • CHRONOS Hole_Desc • Database Name • Hole_ID • Elevation • Meters_of_Section • Taxa_Count

  31. Age/Depth Global View Example

  32. Questions ?

More Related