Tuesday, April 1, 2014

RxNorm mysql loading errors

I recently had a task that required me to load RxNorm into a database to perform a large number of queries programmatically.  Having worked with RRF files from the UMLS in the past I thought that this would be easy.  I fired up my mac, started mysql and tried out the load scripts from https://www.nlm.nih.gov/research/umls/rxnorm/docs/rxnormfiles.html but was immediately met with errors.

Here's the fix.

  • The paths in the sql scripts for loading the RRF files assume that the RRF files are contained in the same directory as the load scripts.  I added ../../RRF to the RRF filenames in RxNorm_full_03032014/scripts/mysql/Load_scripts_mysql_rxn_unix.sql 
  • The index create scripts did not property truncate long strings for indexing using the InnoDB engine.  The fix was to edit the index create sql to the following:
    • CREATE INDEX X_RXNCONSO_STR ON RXNCONSO(STR(767));
    • CREATE INDEX X_RXNSAT_ATV ON RXNSAT(ATV(767));
    • CREATE INDEX X_RXNSAT_ATN ON RXNSAT(ATN(767));