Description
...
- For the Quarterly refresh only, update the latest RxNorm data for the medication subject area processing.
- Login using PuTTY terminal (or SSH GUI) to same server.
- Change directory to /dwhome/airflow_ci/master/dags/CDRN_ETL/RxNormLoad/
- Run file RxNormLoad.sh
- Login to the appropriate Airflow server via a web browser
- Development: http://plt-nosql01.dhe.duke.edu:8083/admin/
- Production: http://plp-nosql01.dhe.duke.edu:8083/admin/
- Edit the Airflow variable CDRN_ETL_Options_STAR
- Click Admin→Variables at the top of the Airflow web page.
- Click the Edit icon (pencil icon) for the entry CDRN_ETL_Options_STAR Image Modified
- Make the following changes to these entries in the Airflow variable CDRN_ETL_Options_STAR
- loadToDt to the previous day for the mini-CDM refresh or the last day of the previous month for the quarterly refresh in YYYY-MM-DD format
network to CVD19 for the mini-CDM refresh or STAR for the quarterly refresh
prevPrefix to CVDNEW_ for the mini-CDM refresh or the previous V##_ prefix used for the last STAR refresh
newPrefix to CVDNEW_ for the mini-CDM refresh or the next number available for V##_ prefix for the the current STAR refresh
tempPrefix to ETL_
- Start DAG process
- Click DAGs at the top of the Airflow web page.
- Scroll down the DAG list to the entry for STAR_ETL_Master.
- On the right side of the line, there are several icons. Click the Play button (Trigger DAG).
- Click OK on the confirmation popup window. This will start the DAG process.
- Monitor DAG process
- Click on the link for the DAG STAR_ETL_Master from the main Airflow dashboard. This will open the DAG details page.
- Click either Graph View or Tree View to see the progress status of the DAG.
- Graph View - Will display a graph representation of all task being run and status of each task (Running, Finished, Failed, etc.) Does not display Task Duration Time correctly when hovering over the task.
- Tree View - Will display a tree representation of specific amount of executions, including current execution. Task Duration Time does display correctly here when hovering over the task.
- Usual run time for entire process is 10-12 hours.
- For quarterly refresh only, generate STAR hash token data.
- Login using PuTTY terminal (or SSH GUI) to same server.
- Change directory to /dwhome/airflow_ci/master/dags/CDRN_ETL/Datavant/
- Run file hash_token_load_master.sh STAR
- For quarterly refresh only, execute post-ETL process
- Click DAGs at the top of the Airflow web page.
- Scroll down the DAG list to the entry for STAR_ETL_PostOp
- Run following SQL to backfill any missing hash tokens from previous step:
INSERT INTO HSC_PCOR.<<V##>>_HASH_TOKEN
SELECT PATID, 'XXX - S00000', 'XXX - S00000', 'XXX - S00000', 'XXX - S00000', 'XXX - S00000', 'XXX - S00000', 'Backfill'
FROM HSC_PCOR.<<V##>>_DEMOGRAPHIC WHERE PATID IN
(
SELECT PATID FROM HSC_PCOR.<<V##>>_DEMOGRAPHIC vd
minus
SELECT PATID FROM HSC_PCOR.<<V##>>_HASH_TOKEN
);
- Once DAG finishes, login using PuTTY terminal (or SSH GUI) to same server.
- Change directory to /dwhome/apps/cdrnLoad
- Run following command to generate optimization stats for PCORNet tables
nohup sqlplus 'HSC_PCOR/<<PASSWORD>>'@eclrprd1 @gatherStats_HSC_PCOR.sql &
- For mini-CDM only, generate and send N3C data
- Login to the Windows server vwp-pmndmc.dhe.duke.edu via Remote Desktop Protocol (RDP).
- Open Windows File Explorer.
- Navigate to directory to D:\Apps\N3C\Project\
- Double click on file N3C_Main.bat to start the N3C data script.
- Usual run time for batch file is 8-10 hours.
- Can check for errors looking at log file H:\N3C\Project\N3C_debug.log
- For mini-CDM only, generate and send N3C hash tokens
- Go back to the Airflow server from step 1.
- Scroll down the DAG list to the entry for STAR_N3C_Hash_Tokens.
- Login using PuTTY terminal (or SSH GUI) to same server.
- Change directory to /dwhome/airflow_ci/master/dags/CDRN_ETL/Datavant/
- Run file hash_token_load_master.sh N3C
- Transfer ZIP file created from previous step to local computer
- Log into N3C hash token SFTP site (Setup required), 34.132.183.26:2222
- Need SFTP private key file
- Transfer ZIP file to N3C hash token SFTP site
Detailed Information
SQL Script Migration
https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/STAR_create_indexes.sql?ref_type=heads https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/STAR_replace_etl_views.sql?ref_type=heads https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/cdrn_addr_info.sql?ref_type=headsclin.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/clin?ref_type=headsall_enc_hosp.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/cdrn_all_enc_hosp.sql?ref_type=headscdrn_all_enc_oa.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/cdrn_all_enc_oa.sql?ref_type=heads | cdrn_meds_info.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/cdrn_meds_info.sql?ref_type=heads | compare_rpt.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/compare_rpt.sql?ref_type=heads | condition.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/condition.sql?ref_type=heads | death.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/death.sql?ref_type=heads | death_cause.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/death_cause.sql?ref_type=heads | demo_rpt.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/demo_rpt.sql?ref_type=heads | demographic.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/demographic.sql?ref_type=heads | diagnosis.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/diagnosis.sql?ref_type=heads | dispensing_per_year.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/dispensing_per_year.sql?ref_type=heads | dispensing_per_year_Surescript_Step1.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/dispensing_per_year_Surescript_Step1.sql?ref_type=heads | dispensing_per_year_Surescript_Step2.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/dispensing_per_year_Surescript_Step2.sql?ref_type=heads | dispensing_use_inc_load.sql https://gitlab.dhe.duke.edu/edw-foundations/cdrn/airflowetl/-/blob/master/projSql/CDRN_ETL/STAR/MainETL/dispensing_use_inc_load.sql?ref_type=headsmeds_info.sql |
|
|
12 | Infrastructure | compare_rpt.sql |
|
|
13 | Condition | condition.sql |
|
|
14 | Death | death.sql |
|
|
15 | Death Cause | death_cause.sql |
|
|
16 | Infrastructure | demo_rpt.sql |
|
|
17 | Demographic | demographic.sql |
|
|
18 | Diagnosis | diagnosis.sql |
|
|
19 | Dispensing | dispensing_per_year.sql |
|
|
20 | Dispensing | dispensing_per_year_Surescript_Step1.sql |
|
|
21 | Dispensing | dispensing_per_year_Surescript_Step2.sql |
|
|
22 | Dispensing | dispensing_use_inc_load.sql |
|
|
23 | Encounter | encounter.sql |
|
|
24 | Encounter | encounter_dedup.sql |
|
|
25 | Enrollment | enrollment.sql |
|
|
26 | Harvest | harvest.sql |
|
|
27 | Harvest | harvest_CDM61.sql |
|
|
28 | Hash Token | hash_token.sql |
|
|
29 | Hash Token | hash_token_CDM61.sql |
|
|
30 | Hash Token | hash_token_N3C.sql |
|
|
31 | Hash Token | hash_token_backfill.sql |
|
|
32 | Immunization | immunization.sql |
|
|
33 | Lab Result | lab_result_cm_all_years.sql |
|
|
34 | Address History | lds_address_history.sql |
|
|
35 | Medication Administration | med_admin.sql |
|
|
36 | Observation, Clinical | obs_clin_all.sql |
|
|
37 | Observation, Clinical | obs_clin_flowsheet.sql |
|
|
38 | Observation, Clinical | obs_clin_pft.sql |
|
|
39 | Observation, Clinical | obs_clin_smoking.sql |
|
|
40 | Observation, Clinical | obs_clin_vitals.sql |
|
|
41 | Observation, Clinical | obs_clin_vitals_legacy.sql |
|
|
42 | Observation, General | obs_gen.sql |
|
|
43 | Observation, General | obs_gen_adt.sql |
|
|
44 | Observation, General | obs_gen_sdoh.sql |
|
|
45 | Observation, General | obs_gen_vent.sql |
|
|
46 | PCORNet Trial | pcornet_trial.sql |
|
|
47 | Prescribing | prescribing.sql |
|
|
48 | Prescribing | prescribing_mixture_broken_out.sql |
|
|
49 | Prescribing | prescribing_update_rx_basis.sql |
|
|
50 | Address History | private_address_geocode.sql |
|
|
51 | Address History | private_address_history.sql |
|
|
52 | Demographic | private_demographic.sql |
|
|
53 | Demographic | private_demographic_dedup.sql |
|
|
54 | PRO CM | pro_cm.sql |
|
|
55 | PRO CM | pro_cm_sdoh.sql |
|
|
56 | Procedure | procedures.sql |
|
|
57 | Procedure | procedures_caboodle.sql |
|
|
58 | Provider | provider.sql |
|
|
59 | Referral | referral.sql |
|
|
60 | Infrastructure | refresh_stats_update.sql |
|
|
61 | Infrastructure | remove_dup_keys.sql |
|
|
62 | Infrastructure | rename_etl_tables_no_drop.sql |
|
|
63 | Infrastructure | rename_etl_tables_w_drop.sql |
|
|
64 | Vital | vital.sql |
|
|