Developer's Guide to the Database

The WRES database is used by the WRES to support ingest and pairing of observed, predicted, and baseline data when conducting an evaluation. It is an application specific database setup and updated automatically by Liquibase upon executing an evaluation, with the schema modified to support changes to the WRES by the development team as needed. The WRES database allows for very large evaluations to be conducted using relatively limited RAM. While its rate of change may not be high, with potentially years between table changes, there will still be development cycles where changes are made to the schema of the database as part of the continuous delivery, iterative development approach employed by the WRES team.

How do I incorporate a database in an execution of the WRES?

See Instructions for using a database in a WRES standalone execution.

What is the schema of the database?

This is a view of the explicitly related data tables from WRES Release 6.0:

What are some useful queries?

How can I view a listing of recently performed evaluations?

The query below will output this information about past evaluations:

• the name (i.e., label) of the evaluation project, if it has one;
• the evaluation_id assigned by the WRES upon executing the evaluation (this is visible in the stdout logging of an execution);
• a substring that shows the command executed using the WRES (e.g., execute, cleandatabase, etc.);
• a flag indicating if the evaluation failed (t indicates failure, f success);
• and timing information: start time, end time, and duration.

SELECT el.project_name, el.evaluation_id, substring(el.arguments,0,12) , el.failed, el.start_time, el.end_time, (extract (epoch from el.end_time) - extract (epoch from el.start_time)) / 60 as duration_minutes
FROM wres.executionlog el
WHERE start_time > '2025-01-30 12:00:00Z'
ORDER BY start_time;

Note that the time period of the query is controlled by the WHERE start_time > ... condition; specify the date appropriately. Example output:

                  project_name                   |        evaluation_id        |  substring  | failed |         start_time         |          end_time          |   duration_minutes    
-------------------------------------------------+-----------------------------+-------------+--------+----------------------------+----------------------------+-----------------------
                                                 |                             | cleandataba | f      | 2025-01-31 00:59:01.041167 | 2025-01-31 00:59:10.546344 |    0.1584196170171102
 RFC_AHPS_Flow_30day_CSV                         | JmH3GDkBibJqYVROi51FwmvpXrQ | execute <?x | f      | 2025-01-31 03:13:13.500023 | 2025-01-31 03:33:08.286589 |     19.91310943365097
 RFC_AHPS_Flow_90day_CSV                         | PSZFZS3XP8m1jPtffRDcF0IghJo | execute <?x | f      | 2025-01-31 03:33:40.002301 | 2025-01-31 04:13:19.298909 |     39.65494346618652
                                                 |                             | cleandataba | f      | 2025-02-01 00:59:01.503902 | 2025-02-01 00:59:22.801637 |   0.35496224959691364
 NWM_MR_Flow_90day_CSV                           | CvRKdK6TyuNJTXQkHLz-UzF6KTs | execute <?x | f      | 2025-02-01 01:18:49.39509  | 2025-02-01 04:29:53.268882 |    191.06456319888431
 RFC_AHPS_Flow_30day_CSV                         | cBOiO1ROs01rzoXGoOF1WysN3jA | execute <?x | f      | 2025-02-01 04:30:25.848034 | 2025-02-01 04:50:34.11684  |    20.137813433011374
 RFC_AHPS_Flow_90day_CSV                         | rYK4C94wXUEE7dzJczSiSTev_4s | execute <?x | f      | 2025-02-01 04:50:51.867989 | 2025-02-01 05:31:20.303222 |     40.47392054796219
                                                 |                             | cleandataba | f      | 2025-02-02 00:59:00.461623 | 2025-02-02 00:59:04.273611 |   0.06353313525517781
...

How can I look at the declaration that was posted for a specific evaluation?

Find the evaluation_id associated with that specific evaluation using the query mentioned above. Then execute this query:

select el.arguments
from wres.executionlog el
WHERE evaluation_id = '[evaluation_id]';

The output will typically include the keyword execute followed by the in-band declaration YAML posted with the execution. For example:

execute label: USGS Stages against LMRFC Forecast Stages
observed:
  label: USGS NWIS Stage Observations
  sources:

... SNIPPED FOR BREVITY ...

output_formats:
  - csv2
  - pairs
  - png