ETL Water Quality Data from the Legacy STORETW System
This ETL is unique in that:
-
The transformation SQL in encapsulated in a PL/SQL package.
-
Primary key values are manipulated to avoid conflicts with the STORET WQX data.
-
The output is fed into another ETL for actual inclusion in the WQP.
These scripts are run by the OWI Jenkins Job Runners. The job name is WQP_STORETW_ETL. They follow the general OWI ETL pattern using ant to control the execution of PL/SQL scripts.
The basic flow is:
-
Rebuild the ETL PL/SQL Package. (create_storet_objects_stage.sql)
-
Copy the data download and cleanup scripts to the database (nolog) server.
-
Make sure they have the correct end-of-line characters.
-
Download data files as needed. (storet_dump.sh)
-
Import the data into the storetw schema of the nolog database using impdp.
-
Grant select on the storetw tables to wqp_core. (storetw_grants.sql)
-
Transform the data into the station_no_source, activity_no_source, and result_no_source tables. (runWeeklyPackageStage.sql)
-
Mark the current version of data as processed. (storet_finish.sh)