Live demo plan and related needs

[gracebrownecodes]

The setup is:

• PEDSnet 2.2 database with 5 sites already loaded
• One window open with database logging all statements

The demo is:

• infomodels expand a data archive
• infomodels validate the data directory
• infomodels load the data directory (this adds the foreign keys, right?)
• pedsnetdcc check_fact_relationship
• pedsnetdcc sync_observation_period
• pedsnetdcc transform
• pedsnetdcc merge

Which means the needs are:

• Prepare the database with 5 sites in it
• Wire up the transform command to the pedsnetdcc CLI
• Wire up the merge command to the pedsnetdcc CLI

What did I miss?

[murphyke]

Yes, load includes adding indexes and FKs.

How about a document that describes and provides examples for all of the steps, including ones we are not actually demo'ing. This could be the basis of the "PEDSnet database management" documentation.

Re: the load command, there is an open PR that should be merged since it was used in production. I just made another ticket with some of the known TODO items for the load command.

Re: the demo environment, if we use db01, we can either create a special demo cluster or we can let Ritu et al know we are co-opting pedsnet_dcc_v22 temporarily and rename the original v22 database.

Steps that aren't demo'd that could appear in our document:

• create cluster
• fetch file from sftp, create directory
• prepdb
• create and/or copy id-mapping schemas
• pedsnetdcc cleanup

[murphyke]

So my script is something along the lines of:

Steps for initial loading

• Grab the data package file from transfer.chop.edu.
• Create a directory named according to the upload timestamp of the file, e.g. /data/site_data/chop/2016-05-17T08:03 (in this case, the directory is just /data/site_data/demo).
• Place the file in the newly created data submission directory (in this case, the file is demo_v22.tar.gz).
• Change to this directory.

• Expand the data package into an arbitrarily named subdirectory (here: demo_v22, but perhaps best named after the data cycle)

  infomodels expand -o demo_v22 demo_v22.tar.gz

• Validate the data package:

  infomodels validate --modelv 2.2.0 demo_v22

• Load the data for the appropriate site (here: chop, and we are using the special database instance on port 5433)

  infomodels --model pedsnet-core --modelv 2.2.0 load -s chop_pedsnet,vocabulary -d postgresql://loading_user@localhost:5433/pedsnet_dcc_v22?sslmode=disable demo_v22

Things to note: currently the --modelv switch is required, even though the tool should be able to derive the model version from metadata.csv.

[gracebrownecodes]

And then I'll pick up with my script, which goes something like.

• I've exported the PGPASSWORD variable, but the password prompting works as well.
• Check the referential integrity of the fact relationship table:

pedsnetdcc check_fact_relationship -s chop_pedsnet postgresql://loading_user@reslnpedsndb01.research.chop.edu:5433/pedsnet_dcc_v22

• Generate observation period rows:

pedsnetdcc sync_observation_period -s chop_pedsnet postgresql://loading_user@reslnpedsndb01.research.chop.edu:5433/pedsnet_dcc_v22

• Create the ID mapping tables and copy pre-existing mappings:
  • Run this script to create the tables.
  • Manually move (dump and load) the ID mappings for domains with persistent IDs (person and visit) from the previous dataset into the current dataset.
  • Move all rows from the ID mapping table for each domain for each site (<site>_id_maps.<domain>_ids).
  • Delete the default 0 value in the DCC last ID table for each domain and insert the value from the previous dataset (dcc_ids.dcc_<domain>_id).
• Transform the data into DCC format:

pedsnetdcc transform -s chop_pedsnet,chop_id_maps,dcc_ids,vocabulary --site chop -v 2.2.0 postgresql://loading_user@reslnpedsndb01.research.chop.edu:5433/pedsnet_dcc_v22

• Merge the site datasets into a DCC dataset:

pedsnetdcc merge -v 2.2.0 postgresql://loading_user@reslnpedsndb01.research.chop.edu:5433/pedsnet_dcc_v22

• Note that some future improvements to the user experience include
  • inferring the correct search_path by default based on the site name
  • inferring the correct database name based on the version or some other method
  • automatically assuming the correct role when database objects are created
  • less noisy log message output

[murphyke]

Done.