citizensadvice/db_migration_base repository overview

⁠Casebook ETL script

mytsql is the current ETL shell script for extracting, transforming and loading Casebook database data to other database targets e.g. the DataWarehousing database.

⁠Running locally

1. you must create and seed the PG Casebook database container as per the instructions here: https://github.com/citizensadvice/Casebook/blob/master/README.md⁠ e.g.

# From the Casebook repo root; make sure you are on the CB branch you want to test against!
#
# boot the DB
cd docker/shared-services && docker-compose up --no-deps -d db
# create the schema and seed.
# this WILL throw an error at the end 
# as Rails tries to push the updates to Elasticsearch
# but that's ok as it doesn't affect the data in the db.
cd ../../ && docker-compose -f docker-compose.yml run --rm casebook \
  bundle exec rake db:setup db:seed DISABLE_DATABASE_ENVIRONMENT_CHECK=true

1. Setup a local MS Sql Data warehouse. This:

• runs the DW container
• creates the 2 databases on the DW instance - Casebook and CABReporting_DW / check .env⁠ /
• runs all Liquibase migrations against the DW instance

The DW can be accessed on localhost:1433; the username / password are in the .env file⁠

# in the casebook_migration folder of the casebook-db-migration repo:
$ bin/setup_local_dw

$ DB_MIGRATION_TOOL=flyway bin/setup_local_dw

1. Run full ETL against DW

$ bin/local_autocbexe

# runs mytsql-stable autocbtempexe followed by
# mytsql-stable autocbtempcacheexe

$ bin/local_incremental

⁠Environment variables

There are a number of environment variables whose defaults are in the .env file. They can be overridden by:

• env vars in a docker-compose file
• supplying env vars with -e KEY=val when running docker-compose

NOTE The sqlcli docker-compose service uses some environment variables in its service.command. These must be set before compose makes env variables available to the container. This is the reason to have the .env file in the root of this folder - compose will evaluate any ${...} expressions in its config before it attempts to start services.

⁠Using different DBs

⁠Running liquibase / flyway against remote DB

• liquibase

$ docker-compose run -e  LIQUIBASE_DB_USER=admin \
  -e LIQUIBASE_DB_PASSWORD=1234  \
  -e LIQUIBASE_JDBC_URL="jdbc:sqlserver://${TSQLHOST}:1433;databaseName=${TSQLDATABASE}" \
  --rm liquibase

• flyway

$ docker-compose run --rm flyway \
  -url="jdbc:sqlserver://${TSQLHOST}:1433;databaseName=${TSQLDATABASE};integratedSecurity=false;" \
  -user=${TSQLUSER} -password=${SA_PASSWORD} -connectRetries=60 migrate

⁠Running mytsql against:

• Different source PG database

docker-compose run -e PGPASSWORD=<pg_pass> -e PGHOST=<pg_host> -e ... mytsql

• Different DW instance

docker-compose run -f docker-compose.yml -e SA_PASSWORD=<dw_pass> -e TSQLHOST=<dw_host> -e ... mytsql

• Different source / DW databases

$ docker-compose run -f docker-compose.yml -e PGPASSWORD=<pg_pass> -e PGHOST=<pg_host>  \
    -e SA_PASSWORD=<dw_pass> -e TSQLHOST=<dw_host> -e ...  mytsql