wiki:MolgenisFromExisting

Version 2 (modified by Morris Swertz, 15 years ago) (diff)

--

[[TOC(...)]] Generate a MOLGENIS from existing database

There is legacy of existing databases, which you may inherit or get your hands on via SQL dumps/TEXT downloads. Below we will first generate a MOLGENIS for an existing database by automatically extracting the MOLGENIS data model from it. Second you will learn how to batch-load existing data sets from delimited text files.

Generating a MOLGENIS for an existing database

  1. Edit the database connection settings in the molgenis.properties file to refer to an existing database. See MolgenisPropertiesFile. In this example it is assumed you connect to the Address Book database from the AddressBookExample. Alternatively you can download a database dump of your choice or connect to a remote database (assuming proper DatabasePermissions).
  2. Run !handwritten/java/MolgenisExtractModel.java. The extraction tool will connect to your selected database and extract a model from its table structure. An example output for the Address Book Application

    todo screenie
  3. Copy-paste the parts of XML you want to use in your *_db.xml file. Then add a suitable *_ui.xml. Then adapt the molgenis.properties file accordingly and generate,compile,run to view the results (as learned in the previous section).

Batch loading of data

MOLGENIS also provides methods to quickly load large data sets from comma or tab separated data files, both in the MolgenisUserInterfaceas? well as in the MolgenisJavaApi? . This works if:

  • The column headers match the entity field definitions as defined in MolgenisDatabaseXml file(order doesn’t matter)
  • A special case are XrefFields that either can use the xref_field or the xref_label. For example in the case of AddressBookExample? ‘contact_id’ and ‘contact_displayname’.

For example: Load data for Contact. Within the MOLGENIS user interface for Contacts choose ‘File’ and then ‘Add in batch’.

In the CSV data dialog paste the following comma seperated values:

displayname,lastname,firstname,mid_initials,birthday

Prof.dr. R Bischoff,Bischoff,Rainer,,

Dr. R Breitling,Breitling,Rainer,,

Prof.dr. RC Jansen,Ritsert,Jansen,C,

Load data for Address. In the dialog for Example data for Address again choose ‘File’ and then ‘Add in batch’. Set the ‘address_type’ to ‘work’ as constant for all addresses to be loaded (otherwise you get an error!). Then paste in the CSV data dialog:

phone,contact_displayname

+31 (0)50 3633338,Prof.dr. R Bischoff

+31 (0)50-3638088,Dr. R Breitling

+31 (0)50-3638089,Prof.dr. RC Jansen

Notice the cross reference by ‘xref_label’ using ‘contact_displayname’!


[1] These example data was copied from http://www.nbic.nl/nbic/network/?city=Groningen

Attachments (1)

Download all attachments as: .zip