search

glamod / glamod-ingest

Database preparation and ingestion for GLAMOD
BSD 2-Clause "Simplified" License
2 stars 1 forks source link

Fix deleted land sub-daily tables: 1755-1781 #79

Closed agstephens closed 3 years ago

agstephens commented 3 years ago

I accidentally deleted some old (small) tables:

lite_2_0.observations_1754_land_0 ... lite_2_0.observations_1781_land_0

Resolved with:

  1. regenerate them, and their indexes etc, from the scripts, I can just use the scripts in: /usr/local/database_scripts/sql/lite_2_0

  2. repopulate them, working out which data is relevant, from:

    $ ls | wc -l
19588

[astephen@sci3.jasmin.ac.uk sub_daily]$ pwd
/gws/nopw/j04/c3s311a_lot2/data/level2/land/r202005/cdm_lite/sub_daily

  3. Work out which are relevant:

    $ for year in $(seq 1755 1781); do grep COPY /gws/smf/j04/c3s311a_lot2/workflow/r2.0/lite/land/sql/outputs/0/load-0-${year}.sql | cut -d\' -f2 | cut -d_ -f5 >> /tmp/items.txt; done
[astephen@sci3.jasmin.ac.uk sub_daily]$ sort -u /tmp/items
sort: cannot read: /tmp/items: No such file or directory
[astephen@sci3.jasmin.ac.uk sub_daily]$ sort -u /tmp/items.txt
SZUbasel-.psv

  4. So: this is the path: "sub_daily-CDM_lite_SecondRelease_SZUbasel"

  5. How many match? Just:

    /gws/nopw/j04/c3s311a_lot2/data/level2/land/r202005/cdm_lite/sub_daily/CDM_lite_SecondRelease_SZUbasel-10.psv.gz
/gws/nopw/j04/c3s311a_lot2/data/level2/land/r202005/cdm_lite/sub_daily/CDM_lite_SecondRelease_SZUbasel-20.psv.gz

  6. Rerun with:

    
cd ~/glamod/
. setup-env.sh
cd glamod-ingest/

Checkout version that still overwrites source_id etc

$ git checkout e2683ffda9306eeec43e1f7c6a9030a1c6cf4c69

for year in $(seq 1750 1781) ; do rm -f /gws/smf/j04/c3s311a_lot2/workflow/r2.0/lite/land/outputs/log/success/0/0-${year}-sub_daily-CDM_lite_SecondRelease_SZUbasel-.psv ; done

./scripts/land/restructure-land.py -r r2.0 -b sub_daily-CDM_lite_SecondRelease_SZUbasel-

Get local version again

$ git checkout master


 7. Recreate tables and indexes from scripts...messsy

  1. Write SQL files, already exist!!!

    $ pwd
/home/users/astephen/glamod/glamod-ingest
$ for year in $(seq 1755 1781) ; do $PSQL_PREFIX -f /gws/smf/j04/c3s311a_lot2/workflow/r2.0/lite/land/sql/outputs/0/load-0-${year}.sql ; done

  2. Re-add date column: 

    ./scripts/manage/add-date-column.sh r2.0 land 0