Skip to content

Latest commit

 

History

History
executable file
·
31 lines (16 loc) · 1.11 KB

README.md

File metadata and controls

executable file
·
31 lines (16 loc) · 1.11 KB
Raw

etl-storet

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)