Unified Medical Language System | |
This document is a guide to loading an ORF subset of the UMLS Metathesaurus into a MySQL database by using the scripts and files created by MetamorphoSys. To generate, the necessary files and scripts, select the "Write MySQL Load Script" checkbox on the "Output Options" tab.
Recommended Software Version
MySQL Server 5.0.
In order to load your subset into a MySQL database, you will
have to either use an existing database or create a new one. Two
important considerations when creating a MySQL database are the default
character set and collation settings. We recommend using UTF8 and
the
utf8_unicode_ci collation setting. For example,
CREATE DATABASE IF NOT EXISTS
umls CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Windows
If you are running MetamorphoSys in a Windows environment, in your output directory (after running MetamorphoSys with the "Write MySQL Load Script" box checked) you should see a batch file script called "populate_mysql_db.bat". This script can be used to load your subset into a MySQL database. The directory will also contain an SQL script for creating the tables, an SQL script for creating the indexes, and a script for creating the join of MRCON and MRSO (MRCONSO).
Edit the script and configure the four parameters at the top of the
script. These are:
set MYSQL_HOME=<path to
MYSQL_HOME>
set user=<username>
set password=<password>
set db_name=<db_name>
MYSQL_HOME should be set to the installation directory of your local
MySQL server. This is likely to be something like "C:\Program
Files\MySQL\MySQL Server 5.0". Remember to enclose the value in
quotes if the directory name contains spaces. After
configuring these four parameters, this section of your script may look
something like the following.
set MYSQL_HOME="C:\Program
Files\MySQL\MySQL Server 5.0"
set user=myuser
set password=p4ssw0rd
set db_name=umls
You are now ready to run the script. To do so, navigate to
your subset directory using the Windows explorer and double-click on
the "populate_mysql_db.bat" file you just finished editing.
Here are some things to keep in mind when using this script:
Linux, Macintosh, or Solaris
If you are running MetamorphoSys in a Linux, Macintosh, or Solaris environment, in your output directory (after running MetamorphoSys with the "Write MySQL Load Script" box checked) you should see a .sh shell script called "populate_mysql_db.sh". This script can be used to load your subset into a MySQL database. The directory will also contain an SQL script for creating the tables, an SQL script for creating the indexes, and a script for creating the join of MRCON and MRSO (MRCONSO).
Edit the script and configure the four parameters at the top of the script. These are:MYSQL_HOME=<path to
MYSQL_HOME>
user=<username>
password=<password>
db_name=<db_name>
MYSQL_HOME should be set to the installation directory of your local
MySQL server. Another way to think of this is that the setting of
MYSQL_HOME should contain a bin/ directory that contains the "mysql"
command. This may even be something like "/usr". After
configuring these four
parameters, this section of your script may look something like the
following.
MYSQL_HOME=/usr
user=myuser
password=p4ssw0rd
db_name=umls
You are now ready to run the script. To do so, navigate to
your
subset directory and invoke the script from the command line. It
may be necessary to change the file permissions to make the script
executable before it can be started. For example:
% cd <subset directory>
% chmod 775 populate_mysql_db.sh
% populate_mysql_db.sh &
Here are some things to keep in mind when using this script:
Sample Queries
We have provided a number of sample queries to demonstrate how to access certain kinds of data.
Get the English preferred name for a CUI.
SELECT str FROM MRCON
WHERE cui = ? AND stt = 'PF'
AND ts = 'P' AND lat = 'ENG';
Get MRCONSO rows (atoms) for a SAB and CODE.
SELECT * FROM MRCONSO
WHERE sab = ? AND code = ?
ORDER BY cui, lat, lower(ts), lui, stt;
Get all MRCONSO rows (atoms) for a CUI, in precedence order.
SELECT * FROM MRCONSO
WHERE cui = ? ORDER BY cui, lat, lower(ts), lui, stt;
Get all MRCONSO rows (atoms), with STYs, in precedence order.
SELECT a.*, sty FROM MRCONSO a. MRSTY b
WHERE cui = ?
ORDER BY cui, lat, lower(ts), lui, stt;
Perform a norm string search in MRXNS_ENG to get CUI.
SELECT a.* FROM MRCONSO a, MRXNS_ENG b
WHERE a.cui = b.cui and nstr = ? and a.sui = b.sui;
Find all Semantic Types for a particular string.
SELECT sty FROM MRCON
a, MRSTY b WHERE a.cui = b.cui AND str = ?;
Find all contexts for a particular "atom". (Note: this requires creating an ORF subset
with a MRCXT file).
SELECT * FROM MRCXT
WHERE cui = ? AND sui = ?
ORDER BY cui, sui, code, cxn, cxl, rnk;
Find all definitions for a particular string.
SELECT def FROM MRCON a, MRDEF b
WHERE a.cui = b.cui AND str = ?;
Find preferred MUI of a MeSH D#.
SELECT atv AS mui FROM MRSAT a, MRSO b
WHERE a.cui = b.cui
AND a.sui = b.sui
AND b.sab = 'MSH'
AND b.tty = 'MH'
AND atn = 'MUI'
AND b.code = ?;
Last reviewed: 05 July 2007
Last updated: 05 July 2007
First published: 05 July 2007
Metadata| Permanence level: Permanent: Dynamic Content