periodic automated ETL

[jesteria]

ETL should be run periodically by an automated process to (re)load data into the database.

• Elastic Beanstalk supports configuration of cron on (one of its) servers.
• Additionally, Elastic Beanstalk supports "worker" servers.
• Appy is a Docker-deployed application, which could be run as an AWS ECS Task.

---

In 2019, ETL was run during the application stage from the management CLI, with variations of the following:

manage etl --all --stage=application

…and in the review stage:

manage etl --all --stage=review

Note, however, that Appy does not ship with the repo's manage.py. The above CLI commands wrap and document the applicable underlying commands, (run through docker run --rm --user webapp -eDATABASE_URL -eWUFOO_API_KEY appyreviews_web):

./manage.py loadwufoo -v 1 --stage application -

./manage.py loadapps execute

and:

./manage.py loadwufoo -v 1 --stage review -

./manage.py loadapps --closed execute

[jesteria]

ETL is now documented here.

[jesteria]

This work was begun in 2020, with the intention of utilizing AWS Batch to execute ETL, using the existing Docker image. Provisioning of the periodic job(s) would be controlled by manage command(s).

See: https://github.com/dssg/appy-reviews/compare/jsl/auto-etl

• It would be best that the provisioning is not automatic; but rather, that it can be enabled, with review, (and perhaps, if possible, with an expiration/schedule) – especially since ETL should be tested and reviewed manually, at least at first.
  • manage command(s) could:
    1. execute a one-off run of an ETL command in the cloud
    2. schedule runs (via CloudWatch cron)
    3. list existing schedules of above
  • would be nice to have expiration on a cron job (but might not be possible)
  • at highest level, jobs must reflect two stages:
    1. application-stage ETL, which is configured (but by default) to run until some time on Feb. 1
    2. review-stage ETL, to start immediately after the above, and until some safe time later (2 weeks later?)
• commands to execute should be generated from same logic that generates existing ETL (of the form ./manage.py ...) – (i.e. this logic should remain in the argcmdr manage.py, and the Batch job should likely be left generic).
  • (though it's possible that the job queue and/or compute env will need be background-specific, at least in so far as will never be high-priority, will use spot instances, etc.)
  • job command can combine ETL steps or can just have two rules that run them staggered sufficiently – whatever is expedient – (since it's not critical that steps are coordinated)

• but, at least when run in the cloud, should likely be decorated…

  • with chamber (i.e. params/secrets should be moved to AWS Parameter Store)

  • and with the following for reporting:

    slack-report --channel appy \ /usr/bin/time \ annotate-output \ ./manage.py $@