add_data.sh related Python scripts - flexible data loading

[drotheram]

Dynamically load raw model datasets which may have changes to the types of fields by reading and loading those fields. Implement tests on inputs with some reasonable constraints on what the stack will load

See also Issue #48

Major Priorities

PSRA:

• PSRA_copyTables.py (Preview WIP flexible-csv-headers branch)
  • Shrink the list of expected_headers (though these should probably be removed altogether eventually)
  • pylint, flake8
  • Skip OpenQuake comment header (and deal with U+FEFF BOM). (Originally taken care off by sed `add_data.sh
  • Read CSV header from PostgreSQL dynamically, something like psql opendrr -c 'COPY (SELECT * FROM psra_BC.psra_BC_hcurves_pga WHERE FALSE ) TO STDOUT WITH CSV HEADER;'
  • Quote SQL identifiers when necessary for column names with upper-case letters, ., (), etc.
  • Restore reading config.ini and add error checking if file not found (POSTGRES_* variables not defined)
  • Write some kind of unit test?

DSRA:

• DSRA_outputs2postgres_lfs.py (consider refactoring)
• DSRA_ruptures2postgres.py

Minor Priorities

Exposure:

• copyAncillaryTables.py

[jvanulde]

@anthonyfok is this done? If not please move to Sprint 33 Milestone.

[anthonyfok]

@jvanulde Thanks for the reminder! Done, and am in the process of moving other outstanding tasks to Sprint 33 too.

[anthonyfok]

Notes

Wed 2021-02-24 Skype meetings

IIRC, this issue was first discussed on Wednesday, 24 February 2021, over Skype meetings with Will and Drew.

add_data.sh is the main orchestration of the whole thing. It has gotten a lot better over time, but it used to be extremely brittle, so anytime anyone changed any little thing, the whole thing would break. So, we've been spending time and effort trying to make this more flexible...

• opendrr-api/python/add_data.sh

For example: Will wrote the following SQL script to pull in the social vulnerability data:

• model-factory/scripts/Create_table_sovi_census_canada.sql

If the upstream CSV file (created by e.g. Murray or Tiegan) were changed, e.g. the headers Lon and Lat were changed to lowercase lon and lat, this whole SQL script would break.

• model-factory/scripts/Create_risk_dynamics_indicators.sql (where a lot of headers are explicitly defined)
• model-factory/scripts/Create_table_shakemap.sql (a version of which had a COPY statement where the header fields are dynamic)
• model-factory/scripts/DSRA_runCreateTableShakemap.py (SQL statements in lines 62–67)

So, instead of explicitly defining those header files, headerFields,
we actually read them in (in some cases) from the CSV itself.

And that way when the CSV files are changed, then we end up just loading the whole CSV as it is with the headerFields dynamically generated.

... Some fields are critical...

Mon 2021-05-10 Zoom meeting

About model-factory/scripts/PSRA_copyTables.py

The tables are defined in model-factory/scripts/psra_1.Create_tables.sql.

These Python and SQL scripts are called from opendrr-api/python/add_data.sh like so:

# PSRA_1-8
for PT in ${PT_LIST[@]}
do
  python3 PSRA_runCreate_tables.py --province=${PT} --sqlScript="psra_1.Create_tables.sql"
  python3 PSRA_copyTables.py --province=${PT}
  python3 PSRA_sqlWrapper.py --province=${PT} --sqlScript="psra_2.Create_table_updates.sql"
  python3 PSRA_sqlWrapper.py --province=${PT} --sqlScript="psra_3.Create_psra_building_all_indicators.sql"
  python3 PSRA_sqlWrapper.py --province=${PT} --sqlScript="psra_4.Create_psra_sauid_all_indicators.sql"
  python3 PSRA_sqlWrapper.py --province=${PT} --sqlScript="psra_5.Create_psra_sauid_references_indicators.sql"
done