Overview
The CWMS database is a zone provided for local Corps offices to store time series and relational data used in modeling CWMS watersheds. Oracle was chosen as the database facilitator because it is a widely used database and follows the corporate guidelines for USACE. The CWMS system provides a variety of ways to interact with the CWMS database, including PL/SQL, the CWMS API, and various application programs. The CWMS database schema also stores procedures used to write and receive data to the database. Updates to the procedures and database information to fix bugs and provide added functionality are accomplished through changes to the CWMS schema (CWMS_20). Schema updates are created and tested at HEC and then distributed to districts databases.
Funded Activities
| Activity |
|---|
| Minor Improvements and Field Support |
| Cloud Migration - Migrate Goldengate to CWBI testing/production |
| Cloud Migration - Data Acquisition and National Database Administration |
| Monitor Performance and Improve as Needed |
| Authentication of Public versus Private Data |
| Versioning of Time Series Data |
| Cloud Migration - Migration of Historic Public Data into CWBI |
| Cloud Migration - Move Current Build System to Flyway and Gradle |
|
Accomplishments
Minor Improvements and Field Support
Database improvements were developed based on issues reported by the field. These improvements were incorporated into the latest database schema released to the field in August 2024.
Cloud Migration - Migrate Goldengate to CWBI testing/production
Goldengate processes were created between district T7 servers and the CWBI databases in test and production. The direct connections bypass the CPC in preparation for the CPC decommission in 2025.
Cloud Migration - Data Acquisition and National Database Administration
Data acquisition needs in the Cloud were addressed. This includes developing a new getUSGS scrip that uses cwms-python to ingest data from the USGS. A new server python 3 build was compiled for solaris to be used with cwms-python and data acquisition scripts. This build was deployed to districts to help convert district specific data acquisition scripts to python 3. A new SHEF processor and loader were developed in python 3 to process SHEF files in the the cloud through LDM. This process has been integrated with the enterprise cloud LDM application for processing SHEF files.
Database performance improvements have been made, including improvements to retrieving timeseries data from the database. These improvements have been incorporated in the latest database schema update that was deployed to districts in August and September of 2024.
Authentication of Public versus Private Data
A new contract was developed to solicit contractors to develop a new authorization scheme for the cloud database. This contract work will begin in FY25.
Versioning of Time Series Data
A new structure and API for the CWMS database was created that can be used to store any type of forecast data (CWWS, NWS, etc…) that has time series of values. It uses versioned time series to track the time series for a single location/parameter across multiple forecasts.
Cloud Migration - Migration of Historic Public Data into CWBI
A plan for migrating historical data for timeseries utilized by Access to Water has been developed.
Cloud Migration - Move Current Build System to Flyway and Gradle
Technology Transfer - Data Workshop
A data workshop was conducted in September 2024 to assist districts in migrating data acquisition scripts to python 3 using the new cwms-python library. This was a 3 day workshop attended by 24 district staff.