Evaluation of Columnar Database Tables for Producing Climate Aggregates on Demand
Dáire Casey, Conor Lally, Dára Elliot
Met Éireann, Maynooth University
Modern automated observing networks generally produce observations at a time resolution of one minute or less. For climatological and other purposes, longer time resolutions are required such as ten minute, hourly, daily, and monthly. Using traditional relational databases, this necessitates the creation of derived values, i.e. aggregates computed from a set of individual observations such as accumulation, averages and extremes.
With the potential for quality control processes to subsequently modify observation values, ensuring consistency between the different time granularities is exceptionally difficult. This can lead to undesirable variations between related original values, derived, and aggregates.
New columnar databases and related analytical query engines provide massive performance enhancements for certain data-types. This introduces the possibility of determining climatological derived values and aggregates on demand as opposed to pre-calculating them. This eliminates the potential for inconsistency between time granularities. But considering the huge size of many observations and climate datasets, is the performance of columnar databases good enough to meet the operational requirements of a National Meteorological Organisation? This will be determined by querying a columnar database and an equivalent row-oriented database with a set of varied queries and comparing the speed at which results are returned. This research will have important implications for observations and climate data storage systems used by National Meteorological Organisations such as Ireland’s Met Éireann. Moreover, this approach may prove advantageous to those wishing to use data science applications on climate and weather data sets on the cloud.
How to cite: Casey, D., Lally, C., and Elliott, D.: Evaluation of Columnar Database Tables for Producing Climate Aggregates on Demand, EMS Annual Meeting 2022, Bonn, Germany, 5–9 Sep 2022, EMS2022-255, https://doi.org/10.5194/ems2022-255, 2022.