BioDWH2-SQL-Exporter

BioDWH2 is an easy-to-use, automated, graph-based data warehouse and mapping tool for bioinformatics and medical informatics. The main repository can be found here.

This repository contains the BioDWH2-SQL-Exporter utility which can be used to export a BioDWH2 graph database into a relational SQL database. There is no need for any SQL installation to run the exporter.

Download

The latest release version of BioDWH2-SQL-Exporter can be downloaded here.

Usage

BioDWH2-SQL-Exporter requires the Java Runtime Environment version 11 or higher. The JRE 11 is available here.

Creating an SQL database from any workspace is done using the following command. Every time the workspace is updated or changed, the create command has to be executed again.

> java -jar BioDWH2-SQL-Exporter.jar --create /path/to/workspace

By default, a MySQL compatible SQL syntax is exported. To change the SQL syntax target, add the --target parameter. Supported targets are MySQL, MariaDB, PostgreSQL, Sqlite, and MSSQL.

> java -jar BioDWH2-SQL-Exporter.jar --create /path/to/workspace --target sqlite

Limitations

Depending on the selected SQL target DBMS, certain limitations need to be adhered to:

• Most SQL DBMS limit the allowed length of identifiers for tables, columns, indices, and more.
• Relational databases usually think of values in a column as singular data points. As array properties are allowed for graph node and edge properties, these arrays need to be represented somehow. Some DBMS added support for JSON data types which are suitable for representing arrays.

DBMS	Identifier length	Array properties	Indices
MySQL	64 (ref)	YES (JSON data type)	No array indices
MariaDB	64 (ref)	~ (Stored as text with JSON functions)
PostgreSQL	63 (ref)	YES (JSON data type)
Sqlite	- (ref)	YES (JSON data type)	-
MSSQL	128 (ref)	~ (Stored as text with JSON functions)

Help

Usage: BioDWH2-SQL-Exporter.jar [-h]
                                [-c=<workspacePath>]
                                [--insert-batch-size=<batchSize>]
                                [--schema-name=<schemaName>]
                                [--target=<target>]
  -c, --create=<workspacePath>
               Create an SQL database from the workspace graph
  -h, --help   print this message
      --insert-batch-size=<batchSize>
               Batch size of insert statements (default: 100)
      --schema-name=<schemaName>
               SQL schema name (default: biodwh2)
      --target=<target>
               SQL DBMS target [mysql, mariadb, sqlite, postgresql, mssql] (default: mysql)