I-ETL

The ETL algorithm creates interoperable databases for the BETTER project. It relies on MongoDB to store the data and reads a .env file for the configuration. The code is written in Python and encapsulated in a Docker container.

1. Requirements

• Docker Desktop is installed on the host machine.
• All tabular input files are in the same folder on your machine, and they have the exact same name as specified in the metadata.
• Any additional payload (VCF files, imaging folders, ... i.e., separate files matched to patients, not columns of a table) lives under a shared root folder (HOST_MOUNT_DIR), described in the section Additional payload.
• In your data files: variables are columns, patients are rows and patients have identifiers (which will be further anonymized by I-ETL).
• The column name for the patient ID, respectively the sample ID, is the same for all the files used to build the database.

2. Use I-ETL to create an interoperable database with your data

1. Get the image of the I-ETL:

• Either download the TAR image available in the repository (recommended)
  • Go to the deployment artifacts page: https://git.rwth-aachen.de/padme-development/external/better/data-cataloging/etl/-/artifacts
  • Click on the "folder" icon of the latest valid build (the most recent one with a green ☑)
  • Download the TAR archive named the-ietl-image.tar (be sure to download a .tar, not a .zip)
• Or build it from the repository (not recommended, see section "For developers")

2. Download the compose.yaml file, available in the repository (https://git.rwth-aachen.de/padme-development/external/better/data-cataloging/etl/-/blob/main/compose.yaml?ref_type=heads)
3. Download the settings file .env.example file, available in the repository (https://git.rwth-aachen.de/padme-development/external/better/data-cataloging/etl/-/blob/main/.env.example?ref_type=heads) and rename it to .env
4. Download your metadata file in the Better Drive: https://drive.google.com/drive/u/1/folders/1J-3C2g06WbC1gUE_3KaDp3_v1uKHXxFV

• ES-HSJD-variables.xlsx for SJD hospital
• IT-BUZZI-variables.xlsx for BUZZI hospital in use-case 1
• RS-IMGGE-variables.xlsx for IMGGE hospital
• UC2-variables.xlsx for LAFE and HMC hospitals
• UC3-variables-02-04-2025.xlsx for TERRASSA, UKK and BUZZI hospitals

4. Create a folder, e.g., named better, with:

• The I-ETL Docker (TAR) image
• The .env file template
• The compose.yaml file
• The metadata file

5. In that folder, load the TAR image within the Docker: docker load < the-ietl-image.tar
6. In that folder, fill the .env file with your own settings (see Section 3)
7. In that folder, launch I-ETL by running the following commands:

• export CONTEXT_MODE=DEV
• export ETL_ENV_FILE_NAME=.env
• export ABSOLUTE_PATH_ENV_FILE=X where X is the absolute path to your .env file
• docker compose --env-file ${ABSOLUTE_PATH_ENV_FILE} up -d (-d stands for --daemon, meaning that I-ETL will run as a background process).
• In case the mongodb & etl are started separately from respective compose files (e.g. compose.etl.yaml and compose.mongodb.yaml run with command targeting each compose file: docker compose -f compose.mongodb.yaml --env-file ${ABSOLUTE_PATH_ENV_FILE} up -d, docker compose -f compose.etl.yaml --env-file ${ABSOLUTE_PATH_ENV_FILE} up)

8. To check whether I-ETL has finished, you can run docker ps: if the-etl does not show in the list, this means that it is done.
9. To check the logs of the ETL, you have two options:

• If you have specified the parameter SERVER_FOLDER_LOG_ETL in your .env, you can look at the log files produced in the folder you specified in that parameter;
• Otherwise, use docker logs the-etl.

3. Parameters in the .env file

The .env file is a file to specify several parameters that I-ETL needs to run properly. This includes parameters about the database, the files to use, the language, etc.

The provided .env file is a template: you have to fill each parameter with your own value.

About input data (synthetic or real) given to the ETL

About additional payload (VCF, imaging)

SERVER_FOLDER_DATA / SERVER_FOLDER_METADATA are the entry points for tabular data. Any additional payload, or files/folders that are matched to a patient, such as VCF files or imaging folders, should be accessible under a shared folder:

HOST_MOUNT_DIR is mounted read-only at DIND_MOUNT_DIR inside the container. All four variables are optional: if you have no additional payload, leave them all unset, e.g., commented out as in .env.example (if not specified, docker compose falls back to /dev/null no-op mount).

If you set VCF_DATA_DIRECTORY and/or IMAGING_DATA_DIRECTORY, you must also set HOST_MOUNT_DIR and DIND_MOUNT_DIR to the actual payload paths. Otherwise the ETL refuses to run.

⚠️ HOST_MOUNT_DIR and DIND_MOUNT_DIR must be the same values that PADME uses. I-ETL both reads the payload from DIND_MOUNT_DIR and registers DIND_MOUNT_DIR-based paths in the database. Because PADME mounts the same host folder (HOST_MOUNT_DIR) to the same target (DIND_MOUNT_DIR), the paths stored by I-ETL are directly usable by PADME. Keep these two variables synchronized between the two tools.

VCF files

Steps to manage VCF files linked to patient records:

1. Put the VCF files under the payload root and point VCF_DATA_DIRECTORY at them, e.g. vcf-folder/*.vcf (or vcf-folder/*.vcf.gz). If VCF_DATA_DIRECTORY is empty, no VCF field is added.

2. Name each file <patient_id>.vcf (or <patient_id>.vcf.gz), where <patient_id> matches the patient ID value in the data. Matching happens by filename only; VCF files whose <patient_id> doesn't appear in the data are skipped, and patient IDs without a matching VCF get an empty path.

3. Update the metadata so the GENOMIC profile contains both:

   • a row for the VCF-path field, with column_name path_vcf (or any specific name agreed with your hospital); and
   • a row for the PATIENT_ID column, if it is not already declared under GENOMIC.

   The registered path in Mongo then becomes DIND_MOUNT_DIR/vcf-folder/<patient_id>.vcf[.gz].

Imaging files

Imaging payload is matched on a folder basis (not by filename):

1. IMAGING_DATA_DIRECTORY points to the imaging folder under the payload root. Each of its sub-folders is matched to a patient ID; optionally, second level sub-folders are matched to that patient's sample IDs.

2. A glob may be used on the patient level, to restrict which patient folders are considered, e.g. imaging-folder/PREFIX*. Sample-level folders are always matched.

3. Matching and linking:

   • If imaging-folder/<patient_id> matches a patient, the path DIND_MOUNT_DIR/imaging-folder/<patient_id> is linked to every entry of that patient.
   • Addtionally, if imaging-folder/<patient_id>/<sample_id> matches one of that patient's clinical samples, the path DIND_MOUNT_DIR/imaging-folder/<patient_id>/<sample_id> is linked to that sample only (it takes precedence over the patient-level path for the matched sample).

4. Update the metadata so the IMAGING profile contains:

   • a row for the imaging-path field — column_name path_imaging (or any specific name agreed with your hospital);
   • a row for the patient-ID column (named by PATIENT_ID), if not already declared under IMAGING; and
   • optionally, a row for the sample-ID column (named by SAMPLE_ID), if you want sample-level linking and it is not already declared under IMAGING.

   As for VCF, only the columns described under the IMAGING profile are kept.

About the database

If you already run the ETL without specifying the Docker network name of your station, you should proceed as follows:

• Find the container ID of your currently running MongoDB container: docker-ps|grep the-mongo should list one entry with the container ID of the ETL MongoDB container.
• Find the Docker network name on which the Mongo DB is: list the Docker networks with docker network ls and find the right one (usually, it contains pht-net in its name).
• Disconnect the current Docker MongoDB container from the currently used Docker network using: docker network disconnect <currently-used-docker-network> <container-id-of-your-mongo-db>
• Find the Docker network name used by your PADME trains/station: list the Docker networks with docker network ls and find the right one (usually, it contains padme or station in its name).
• Connect your MongoDB container to your Docker network using docker network connect <your-Docker-network-name> <container-id-of-your-mongo-db>

About the ETL

About the catalogue

Access Requirements: Both the data ingestion service and the UI dashboard require an account. To request an account for accessing the dashboard, please contact the partner responsible for the Web Catalogue (NOOSWARE) at better@noosware.com and provide the email address you would like to use for this account. If your account will be responsible for submitting data, please also mention which hospital you would like your account to be associated with.

The CATALOGUE_ENV parameter selects the destination endpoint: TEST (the default) submits to the demo environment, while PRODUCTION submits to the production environment. If omitted or invalid, it falls back to TEST.

The TOKEN is mandatory to submit the catalogue data to the API that will ingest it and present it on the Web interface. Instructions for generating a TOKEN are available in Deliverable 4.5: https://drive.google.com/file/d/1iRsygeTaSOIetSjWBirTvY0Q_uA5hMHu/view

In brief, you can obtain a TOKEN using the provided Postman collection in catalogue.postman_collection.json which includes:

• A login request that returns a TOKEN.
• A /data-ingestion request configured for UC1 (paediatric) with dummy data which can be used to verify the submission process.

Notes

• Please set your email and password in the collection variables (these are used by the login request).
• After calling the login endpoint, you can paste the returned access token as the TOKEN parameter in the ETL configuration.
• For testing the /data-ingestion request, paste the returned access token into the retrievedToken collection variable.

4. Querying the ETL database within a train

DataRetriever — structured query mode

The class DataRetriever retrieves feature metadata or patient records from the I-ETL MongoDB database and loads them into a Pandas DataFrame accessible as the_dataframe.

It supports two query types:

• QueryTypes.METADATA — retrieves all feature metadata (ontology codes, data types, units, categories, visibility, etc.) as a DataFrame indexed by feature identifier
• QueryTypes.DATA — retrieves patient records for a specified set of features, returned as a DataFrame indexed by patient identifier

Retrieving metadata

from data_retriever.DataRetriever import DataRetriever
from enums.QueryTypes import QueryTypes

dr = DataRetriever(
    mongodb_url="mongodb://localhost:27018/",
    db_name="better_database",
    query_type=QueryTypes.METADATA
)
dr.run()
print(dr.the_dataframe)
dr.the_dataframe.to_csv("metadata.csv")

The resulting DataFrame contains one row per feature with columns for ontology system, ontology code, data type, unit, description, categories, and visibility.

Retrieving data

For QueryTypes.DATA, you supply a query_list: a list of dicts, each specifying which feature(s) to retrieve and how to name the output column. Each dict has up to three optional keys:

Each item must include at least one of "ontology_resource" or "feature_name".

Supported query patterns:

from data_retriever.DataRetriever import DataRetriever
from enums.QueryTypes import QueryTypes

dr = DataRetriever(
    mongodb_url="mongodb://localhost:27018/",
    db_name="better_database",
    query_type=QueryTypes.DATA,
    query_list=[
        {"label": "sex",      "ontology_resource": "snomedct:734000001"},
        {"label": "vcf_path", "feature_name": "vcf_path"},
        {"label": "hypotonia", "ontology_resource": "hp:0001252",
                               "feature_name": "hypotonia"},
        {"ontology_resource": "snomedct:398152000"},  # column name taken from DB
    ]
)
dr.run()
print(dr.the_dataframe)
dr.the_dataframe.to_csv("data.csv")

Join semantics

When more than one feature is requested in a query, records are joined on the patient identifier (has_subject). The join_mode parameter controls how missing values are handled.

If the aggregation produces no documents, the_dataframe is set to an empty DataFrame indexed by has_subject.

dr = DataRetriever(
    mongodb_url="mongodb://localhost:27018/",
    db_name="better_database",
    query_type=QueryTypes.DATA,
    query_list=[
        {"feature_name": "weight"},
        {"feature_name": "feature_with_missing_data"},  # may be sparse or fully empty
    ],
    join_mode="left",  # default; use "inner" for complete-cases-only
)
dr.run()

DataRetrieverLegacy — original query mode (deprecated)

Note: DataRetrieverLegacy is the original implementation. It is kept for backward compatibility. New code should use DataRetriever instead.

Given a user input (explained below), the class DataRetrieverLegacy takes care of:

1. Generating the MongoDB query to fetch the data or metadata in the database
2. Loading the retrieved data, respectively metadata, in a Pandas DataFrame

The user input is:

• the information to connect to the database (MongoDB URL and database name)
• the features (also called "variables") the user is interested in when collecting data
• the post-process methods to "flatten"/"normalize" the data values if needed

The package is available in pip meaning that it should be added to the requirements of the train (as in pip install data-retriever). To be sure that you have all the required packages:

1. Download the requirements.txt file in this repository
2. Create a virtual Python environment (using Python venv or Anaconda)
3. Activate that environment
4. Install all the required packages with pip install -r requirements.txt
5. Install the data-retriever package with pip install data-retriever (latest version is 1.7)
6. Then, you should adapt the main file query.py with your own settings (database name, etc.)
7. Finally, you can run the query.py file with python3 query.py

Example to retrieve data (legacy)

A main example is available in the query.py file (https://git.rwth-aachen.de/padme-development/external/better/data-cataloging/etl/-/blob/main/src/query.py).

Lines 9 to 11, respectively 21 to 23, contain the user input:

• The variable FEATURE_CODES is a dictionary (map) to associate a variable name to the ontology term that has been associated to it in the metadata (https://drive.google.com/drive/u/1/folders/1J-3C2g06WbC1gUE_3KaDp3_v1uKHXxFV)
• The variable FEATURE_FILTERS is a dictionary (map) to associate a variable name to (a) its code under the key code, and (b) the filter to apply under the key filter. The value of the filter should be a MongoDB match. Note that the variables mentioned in the filters may differ from the ones selected.
• The variable FEATURES_VALUE_PROCESS is a dictionary (map) to associate a variable to a MongoDB operator to process/flatten the fetched values. The values in this dictionary can be either get_label to get the human-readable name of a category, or any other MongoDB operator. It should be used for the variables leading to non-atomic values (especially dictionaries). If no process is needed (because the value is atomic) or you do not know which MongoDB operator to choose, use None.

The next lines create a new DataRetrieverLegacy with the information for the MongoDB connection, user variables, and the query type which is data. The method run() generates the MongoDB query to fetch the data from the specified database. Then, it loads the fetched data into a DataFrame. This DataFrame is accessible in the variable the_dataframe (see dataRetriever.the_dataframe). Finally, the dataframe is exported to a CSV file.

For instance, in the query.py file, the first query fetches, for all female patients, their associated VCF filepath and whether they have hypotonia. The second query retrieves the same information for the IMGGE hospital.

Example to retrieve metadata (legacy)

Line 33 contains the user input:

• The variable FEATURE_CODES is an array (list) of the variable codes we want to collect metadata for (to find the variable codes, look at the metadata: https://drive.google.com/drive/u/1/folders/1J-3C2g06WbC1gUE_3KaDp3_v1uKHXxFV)

The next lines create a new DataRetrieverLegacy with the information for the MongoDB connection, user variables, and the query type which is metadata. The method run() generates the MongoDB query to fetch the metadata from the specified database. Then, it loads the fetched metadata into a DataFrame. This DataFrame is accessible in the variable the_dataframe (see dataRetriever.the_dataframe). Finally, the dataframe is exported to a CSV file.

For instance, in the query.py file, the last query fetches all metadata information about variables hypotonia, vcf_path and gene. The retrieved information concerns the variable name, code, data type, categories, visibility, etc. (all information specified in the metadata: to find the variable codes, look at the metadata: https://drive.google.com/drive/u/1/folders/1J-3C2g06WbC1gUE_3KaDp3_v1uKHXxFV)

5. For developers

Build the Docker image

To be used when working with the I-ETL repository

1. Install Docker Desktop and open it
2. From the root of the project, run docker build . --tag ietl
3. If an error saying ERROR: Cannot connect to the Docker daemon at XXX. Is the docker daemon running? occurs, Docker Desktop has not started.
4. If an error saying error getting credentials occurs while building, go to your Docker config file (probably ~/.docker/config.json) and remove the line credsStore. Then, save the file and build again the image.

Steps to deploy the Docker image within a center

To be used when deploying I-ETL within a center

1. Locally, build the Docker image: see above section
2. Locally, create a TAR image of I-ETL (only with the ETL, not with the mongo): docker save ietl > the-ietl-image.tar
3. Send that TAR image to the host machine, e.g., using scp the-ietl-image.tar "username@A.B.C.D:/somewhere/in/host/machine"
4. Send the env file to the host machine in the same folder as the TAR image, e.g., using scp .env "username@A.B.C.D:/somewhere/in/host/machine"
5. Send the compose file to the host machine in the same folder as the TAR image, e.g., using scp compose.yaml "username@A.B.C.D:/somewhere/in/host/machine
6. In the host machine, move to /somewhere/in/host/machine/ using cd /somewhere/in/host/machine
7. In the host machine, load the TAR image within the Docker of the host machine: docker load < the-ietl-image.tar
8. In the host machine, follow any above scenario, i.e., tune the .env file and run I-ETL