Vocabulary_ID=46 The following describes the procedure to update the NLM MeSH vocabulary with the latest release of the source. The load process is a complete refresh of data. All new vocabulary entieties will be added. All vocabulary entities that do not exist in the latest files set will be marked as deprecated. 1.1 Scripts Transfer All scripts and files listed at the bottom of this page should be transferred to specially created directory (eg /data/NLMMESH).
1.2 Download data from sourceThe most current international release is located at the following URL: http://www.nlm.nih.gov/research/umls/rxnorm/docs/rxnormfiles.html Historical archives located at: http://www.nlm.nih.gov/research/umls/rxnorm/docs/rxnormarchive.html Note: Make sure you perform updates in chronological order. So, if you want to process a January and April release, execute the below steps for January, and after successful completion, for April. Download the latest release file (e.g. RxNorm_full_08062012.zip) Zip file suffix contains release date (e.g. 08062012=06-Aug-2012) Note: For the rest of the document we will be referring to current schema as NLMMESH_20120806 you would have to adjust this entry to correspond to current release date file 1.3 Build Database SchemaAll tables will be located in the schema NLMMESH_<DateOfSourceFile> as following: NLMMESH_20120131 In addition to the schema, it will create tables to load raw data: RXNATOMARCHIVE,RXNCONSO,RXNCUI,RXNCUICHANGES,RXNDOC,RXNREL,RXNSAB,RXNSAT and RXNSTY as well as staging tables: CONCEPT_STAGE, CONCEPT_ANCESTOR_STAGE, CONCEPT_RELATIONSHIP_STAGE, SOURCE_TO_CONCEPT_MAP_STAGE
Staging tables match Vocabulary schema version 4.0 with slight modification. Modifications made to the tables are: CONCEPT_STAGE.CONCEPT_ID is nullable CONCEPT_RELATIONSHIP_STAGE .REL_ID is nullable1.4 Raw data load into Oracle tables1.4.1 Extract files from the zip archive into the current directory $ unzip -u RxNorm_full_08062012.zip -d rxnorm_20120806 1.4.2 Verify that the following 2 files have been created: RXNCONSO.RRF (Latest file size ~96MB) RXNATOMARCHIVE.RRF (Latest file size ~42MB) RXNCUI.RRF (Latest file size ~1MB) RXNCUICHANGES.RRF (Latest file size ~1MB) RXNDOC.RRF (Latest file size ~1MB) RXNREL.RRF (Latest file size ~230MB) RXNSAB.RRF (Latest file size ~8KB) RXNSAT.RRF (Latest file size ~385MB) RXNSTY.RRF (Latest file size ~15MB) 1.4.3 Load raw data into Oracle. This sqlldr process will run for about 2min. $ ./46_load_to_prestage.sh /data/NLMMESH/rxnorm_20120806 <NLMMESH_Schema>/<Pass_NLMMESH_Schema> 1.4.4 Verify that files with extension .bad are empty, no records have been rejected. 1.4.5 Copy data and intermediate files to backup area. In this step, downloaded files will be archived to preserve source of the data. Discuss a location of backup area with your system administrator. $ cp -Ru /data/NLMMESH/*.zip /data/backup_area ; cp -Ru /data/NLMMESH/*.log /data/backup_area 1.4.6 Verify that number of records loaded is equivalent to prior production load $ sqlplus NLMMESH_20120806/myPass@DEV_VOCAB Execute the following SQL commands:
1.5 Loading Staging Tables from raw 1.5.1 Convert and store in staging table maps
d. How many active DEV records will be marked for deletion In most cases number of records in stage (a) should be greater than number of active records in production (b) In rare occasion that might not be the case. It is responsibility of the developer running the scripts to verify that that is the the valid. $ sqlplus NLMMESH_20120806/myPass@DEV_VOCAB
Execute the following SQL commands:
Current Result: - Num Rec in stage 2987 |
Drug Domain >