UMLS Metathesaurs Tool – Part 3 (Loading UMLS Schema/Data to MySQL)by Nadeem Nazeer
In this post, we will discuss how to load subset of UMLS Metathesaurus into a MySQL database by using the scripts and files created by MetamorphoSys. To generate the necessary files and scripts, select MySQL database in “Write Database Load Scripts” section of “Output Options” tab, as discussed in the previous post here.
So before we start, we need:
- MySQL up and running.
- Scripts to create Schema and Indexes.
- Data Files to load.
For anything related to MySQL check MySQL docs.
Scripts to Create Schema and Indexes:
Scripts for creating schema and indexes are located in the sub-folder named “config/2014AB/DB_RRF” inside the main folder, where all files were extracted in 3rd step of the first part of this tutorial. You can also have a quick recap of Rich Release format here.
So where are data files located?
If you are following this tutorial series, and want to refresh your memory, go through the 7th step of second part of this tutorial to know the destination location of the extracted files.
Creating a Database:
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 to account for when creating a MySQL database are, the default character set and collation settings. NLM docs 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;
Now, UMLS docs here recommend doing all creating/loading stuff by using bash/shell scripts, but many are instances where we just want to play around with only some of the tables or we just want to do it in a simple, traditional way of importing delimited data files as we always do it in MySQL.
So as an example we set up one of tables:
We will create schema, indexes. and load data for MRCONSO table (containing Concepts, Concept Names, and their sources). Click HERE! to read more about other files as well.
So, lets get started:
- Open up the file named mysql_tables.sql in “config/2014AB/DB_RRF/Mysql5.5/mysql_tables.sql”, and here you can see schema definition for various tables to create.
- Copy the part for MRCONSO:
DROP TABLE IF EXISTS MRCONSO;
CREATE TABLE MRCONSO (
CUI char(8) NOT NULL,
LAT char(3) NOT NULL,
) CHARACTER SET utf8;
- For creating indexes we open file named mysql_tables.sql in “config/2014AB/DB_RRF/Mysql5.5/mysql_indexes.sql”, and copy the part for MRCONSO again:
CREATE INDEX X_MRCONSO_CUI ON MRCONSO(CUI);ALTER TABLE MRCONSO ADD CONSTRAINT X_MRCONSO_PK PRIMARY KEY BTREE (AUI);CREATE INDEX X_MRCONSO_SUI ON MRCONSO(SUI);…………………………………
- And for loading the data files, note down data files folder location as discussed above, and then:
again open file named mysql_tables.sql in “config/2014AB/DB_RRF/Mysql5.5/mysql_tables.sql” and copy the lines for loading data file which are just after the table definition.
load data local infile ‘MRCONSO.RRF’ into table MRCONSO fields terminated by ‘|’ ESCAPED BY ” lines terminated by …………………………….
So, in this way we create MRCONSO table with indexes and load data in it. We can now use it as needed in our customized apps and tools.
Drop a comment here for any queries.