Database client usage | emtelligent Documentation

Here we show how to use the provided emtellipro-db-client database client to:

Build a set of database tables used to store the NLP API output
Submit a document for processing, and store the output in the tables built in step 1, or retrieve a document from a database for processing, and store the output in the tables built in step 1

The goal of the database client is to allow users to pull documents from a database (or, like the basic client, process files from disk), and then store the NLP API’s output structured data in a database. Once stored in a database, you can focus on app development or writing queries to answer the questions you want from your data.

For full documentation of the database client’s configuration options, see Database Client Options.

End-to-end Report Processing and Data Extraction Example
Demonstration of end-to-end processing and data extraction using the NLP API, the SDK’s database client, and an NLP API database.

Database Client Options
Documentation for all the database client’s configuration options.

Using the Database Client with Snowflake
How to use the database client to read and write documents to Snowflake databases.

Using the Database Client with Microsoft SQL Server
How to use the database client to store results in a SQL Server database (either running on Windows or Linux).

Simple client to database client migration
Examples showing how to migrate from the deprecated basic client from Python SDK v5.x to the more full-featured database client.

Prerequisites

Set up the environment variables used in this guide for access to the NLP API server. You will need to set the following environment variables for the NLP API server and your access and secret keys. If you do not have access to the appropriate values for these variables please contact us.

export EMTELLIPRO_SERVER=...
export EMTELLIPRO_ACCESS_KEY=...
export EMTELLIPRO_SHARED_SECRET=...

Installing the database client

The advanced database client uses the SQLAlchemy ORM to work with databases, and additional modules to be able to connect to the RDBMS you’re using. There’s a table to represent all the information available in the AnnotatedDocument instance. Technically, this client should support any database that SQLAlchemy can connect to.

This client is installed automatically when you install the emtellipro wheel file. If you’ve already followed the Installation instructions, you’re nearly there. Your next step is to install the relevant database connector:

PostgreSQL

MySQL/MariaDB

SQL Server

Snowflake

DuckDB

pip install psycopg2

Testing the connection to NLP API

After installing the database client, we should first test the connection to the NLP API, confirming our keys are correct and the connection to the server works. The easiest way to do this is to retrieve our own username.

emtellipro-db-client \
    get-user \
    --access-key $EMTELLIPRO_ACCESS_KEY \
    --shared-secret $EMTELLIPRO_SHARED_SECRET \
    --server $EMTELLIPRO_SERVER

If successful and your access is set up, this command will print out your username with a response like:

Testing emtelliPro connection...
Successful connection to https://emtellipro/
Connected as USERNAME

If the documents you’re processing are stored in files, and not coming from a database (see below for igesting documents from a database), you can also quickly test that processing is working fine using the following command.

Note that the --output and --state parameters refer to files that will be created, so they must point to paths that are writable.

emtellipro-db-client \
  --output raw+jsonl://PATH/TO/RESULT.jsonl \
  process \
  --access-key $EMTELLIPRO_ACCESS_KEY \
  --shared-secret $EMTELLIPRO_SHARED_SECRET \
  --server $EMTELLIPRO_SERVER \
  --recursive \
  FILEPATHS...

The state file

The database client stores its state and a cache of the documents to be processed in a state file, specified using the --state option in all the commands on this page. This is necessary to ensure reliability of the client, allowing it to be restarted after an error or crash and have it continue where it left off.

There are 2 stages the client executes when processing documents: (1) ingesting documents, and (2) processing and storing them to the target output.

In the ingesting documents stage, the client first creates the state file containing some of the command-line parameters and some general metadata (the access keys are not stored in the state file). Then the client reads input documents and stores a cache of them in the state file. The document ingesting stage is all-or-nothing: caching documents in the state file happens in a transaction, so if there was any error reading an input document, then when restarting the client the ingesting stage will start from the beginning.

The state file’s document cache will be roughly as big as the input documents were since a copy of the input documents is stored in the cache, along with some metadata about each document. Make sure that the filesystem where the state file is stored has sufficient free space for all your documents.

After documents are all cached in the state file, the client will proceed to read batches of them from the cache, send them to the NLP API for processing, and upon getting the processed results, it will transform the results if needed and store it into the target database or output path. The processing state for each document (whether it was sent to the NLP API or is already stored in the output path) is set in the state file, so restarting the client during this stage will lead to continuing processing exactly where it left off.

When restarting the client with the same state file, you should pass the same options as the the initial run. Most command-line options are stored in the state file, but depending on where the client stopped in the initial run, changing parameters may have unexpected behaviour.

The authentication keys are not stored in the state file.

For short one-off processing runs, specifying a state file path using --state is optional; a temporary state file will be created and the path will be printed out when you run the process command. Unlike when the state file is explicitly set, this temporary state file will be automatically deleted when processing completes successfully.

Preparing the database for storing NLP API output

As the database client stores the output from the NLP API into a set of database tables, you first need to create the database in your RDBMS, then use the client itself to create the tables to store the data.

For an example of creating a database called example_db in MySQL (or PostgreSQL, the command is the same), you can log into the console of your database server and run the following command:

CREATE DATABASE example_db;

Next, we’ll have to use the database client to create the database tables inside of the example_db database that we’re going to use to store the NLP API output. In order to tell the database client which database to use to store the data, we use a URL. Identify what your URL should be for your database using the examples below. As a general reminder, if you’re getting connectivity errors, remember that the RDBMS has to be enabled on a port, any firewall needs to permit the traffic, and the database user may have to be enabled for remote connectivity.

For ease of use, set the output database as an environment variable. Update the variables in \<\> as appropriate for your environment.

PostgreSQL

MySQL/MariaDB

SQL Server

Snowflake

export EMTELLIPRO_DATABASE=postgresql://<user>:<password>@<host>:5432/example_db

We’ll now use the defined database environment variable $EMTELLIPRO_DATABASE to create the tables for our output by executing:

emtellipro-db-client create-db --output $EMTELLIPRO_DATABASE

Upgrading the database

If you have a previous version of the database, created using an older version of the database client (going back to v4.3.0), you can upgrade to the latest version using the migrate subcommand, as follows:

emtellipro-db-client migrate --output $EMTELLIPRO_DATABASE

Please ensure you’ve backed up the data before running this command. The migrate command will copy data over if columns change, and all changes will execute within a transaction (which should roll back if anything goes wrong), but it’s recommended to have a backup just in case.

Input Sources

Next we have to decide what our input source will be, i.e. where we’re going to get the reports from. The database client supports two ingestion modes:

Reading input documents as .txt, .pdf, or .json (or .jsonl) files from disk
Reading input documents from a database

These use different command-line options which are explained below.

For a complete list of NLP API client commands, please review the command-line help. For available report submission options, run the following commands:

emtellipro-db-client process --help

It’s important to note here that loading documents is an all-or-nothing process: if there were any errors loading any of the input documents into the state file, the state file will not contain any input documents. This allows you to fix the error with the input documents and re-try the command with no risk of accidentally re-processing the same documents.

The config file

The database client can be configured using either command-line options, or by placing the equivalent parameter in a configuration file. Placing parameters in the config file allows you to easily re-use the same parameters between multiple invocations of the client without having an unwieldy list of command-line options.

The format of the config file is TOML. We only make use of a restricted subset of its functionality, though: we’re only grouping simple key-value options in sections.

The config file has multiple sections:

The [emtellipro-db-client] section
This contains global options that are passed to the client as a whole (such as logging information). Even if there are no global options you wish to pass, this section must be present.

The [<COMMAND>] sections
Replacing <COMMAND> with the specific command you wish to configure (such as [create-db] to configure the create-db command).

The [general] section
This contains command-specific options that are shared by multiple commands, so you don’t have to repeat them in separate command-specific sections.

For example the --output option is shared by both create-db and process, so instead of placing it in both [create-db] and [process] and risk forgetting to update one of them, we can place it in [general] and have both those command use the same database.

For this tutorial, we will use the following config file. Make sure you replace the $EMTELLIPRO_SERVER, $EMTELLIPRO_ACCESS_KEY, and $EMTELLIPRO_SHARED_SECRET with the appropriate values, and store it as config.toml.

config.toml

[emtellipro-db-client]
log-level = "INFO"
[general]
server = "$EMTELLIPRO_SERVER"                # replace this
access-key = "$EMTELLIPRO_ACCESS_KEY"        # replace this
shared-secret = "$EMTELLIPRO_SHARED_SECRET"  # replace this
[process]
store-reports = true
store-sections = true
store-sentences = true
category = "Radiology"
subcategory = "CT"
features  = "snomed-ontology,entity-polarity,entity-uncertainty,followup-relations,measurement-relations,text"

The --store-reports option is used to store the raw report in the database’s document table. If this option is not used, the default behaviour does not store the source report; however, if the input type is PDF, the report text returned by the NLP API is always stored.

Overriding parameters

When using both command-line options and the config file, the following precedence rules are followed:

The defaults for each option are read first.
The parameters in the config file override any defaults.
Any options passed explicitly on the command-line override the config file and the defaults for that option.

Database Client Options
Documentation for all the database client’s configuration options.

Processing Text or PDF Files from Disk

The final line of the command block that we use to run the database client is the name of the file or directory that we want to process. Notice the following:

The --save-opt job-id=\<uuid\> option is used to store a unique identifier for the job in the document table of the database. If job-id isn’t specified, a unique identifier will be randomly generated.
The --state option is used to specify a file path in which the client will store its state and a cache of the input documents. This allows restarting the client if it’s stopped or encountered an error part way through processing. You must set a different state file path for each distinct run of the client.

Here’s the syntax for the command:

emtellipro-db-client \
    -c config.toml \
    process \
    --output $EMTELLIPRO_DATABASE \
    --state ./state1.dat \
    --save-opt job_id=test_run_1 \
    example-data/*.txt

If your test is successful, you’ll see output that looks like this:

Using state file path: state1.dat (fresh)
Testing emtelliPro connection...
Successful connection to https://emtellipro/
Connected as USERNAME
Looking for input documents...
Reading:    100%|██████████████████████████████████████| 4/4 files [00:00<00:00]
Loaded 4 files containing 4 documents.
Submitting 4 documents...
Processing: 100%|███████████████████████████████████████| 4/4 docs [00:30<00:00]
Storing:    100%|███████████████████████████████████████| 4/4 docs [00:30<00:00]
Processed 4 reports in 29.9s for an average per report processing time of 7.474949s per report
Saved 4 documents to database in .17s (.042273s / document)

Processing Text or PDF Documents from a Database

In this use case, we are going to retrieve CT scan reports which are stored as plain text, or PDFs (binary data) from a database, submit them to the NLP API for processing, and store the NLP API output into the tables that we had previously created in the example_db database.

So before we create a command for processing, we need to decide on two things:

The URL of the database for the source reports
The SQL query string that we’re going to use for retrieving the reports from that database

Ingesting Text from source database

For this example, we assume that the database containing the source documents is a MySQL database named reports_db. This database runs on the same database server and uses the same user permissions as the destination database: example_db.

For ease of reading, let’s set our input database as an environment variable:

export EMTELLIPRO_INPUT=mysql+pymysql://username:password@localhost:3306/reports_db

And the SQL query we’ll use to retrieve reports from is:

SELECT report_id AS id, report AS text FROM reports WHERE category='Radiology' and subcategory='CT' ORDER BY report_id ASC LIMIT 1

The two fields the NLP API requires are id and text, thus our use of the SQL SELECT ... AS ... to rename the source columns to the NLP API-required column names id and text. Also note that our clause contains single quotes for the WHERE clause, which necessitates our using double quotes for this SQL statement on the command line.

Putting our source URL and SQL statement together, here is the final command block for the database client - note the use of the --sql-query directive and the lack of a filename/directory name at the end of the command:

emtellipro-db-client \
    -c config.toml \
    process \
    --output $EMTELLIPRO_DATABASE  \
    --state ./state2.dat \
    --sql-query "SELECT report_id AS id, report AS text FROM reports WHERE category='Radiology' and subcategory='CT' ORDER BY report_id ASC LIMIT 1" \
    --job-id ct_single_test \
    $EMTELLIPRO_INPUT

By default, the database client will submit as many documents as possible so as not to exceed the maximum request size limit for the NLP API server. You can adjust the number of documents to send in each batch to the NLP API server by using the --max-submit-shard-size option for the process command. By choosing a smaller number the client will start insertion into the database as soon as each batch has been processed by the NLP API server.

If this is successful, we should see client output:

Using state file path: state2.dat (fresh)
Testing emtelliPro connection...
Successful connection to https://emtellipro/
Connected as USERNAME
Looking for input documents...
Reading:      0%|                                      | 1/? files [00:00<00:00]
Loaded 1 files containing 1 documents.
Submitting 1 documents...
Processing: 100%|███████████████████████████████████████| 1/1 docs [00:17<00:00]
Storing:    100%|███████████████████████████████████████| 1/1 docs [00:17<00:00]
Processed 1 reports in 17.43s for an average per report processing time of 17.432515s per report
Saved 1 documents to database in .05s (.05209s / document)

Ingesting PDFs from source database

If your source database contains PDFs instead of plaintext documents, the emtellipro-db-client can still process them, but you’ll need to pass the --text-type=pdf option to let it know the text column now contains PDF bytes instead of plaintext.

The SQL query will look the same as above (note that the PDF column in the source database is still renamed to text):

SELECT report_id AS id, report_pdf AS text FROM reports WHERE category='Radiology' and subcategory='CT' ORDER BY report_id ASC LIMIT 1

And the processing command is similar to the one above, but this time it contains the --text-type option:

emtellipro-db-client \
    -c config.toml \
    process \
    --output $EMTELLIPRO_DATABASE \
    --state ./state3.dat \
    --sql-query "SELECT report_id AS id, report AS text FROM reports WHERE category='Radiology' and subcategory='CT' ORDER BY report_id ASC LIMIT 1" \
    --text-type pdf \
    --job-id ct_single_pdf_test \
    $EMTELLIPRO_INPUT

For this to work, the source database’s PDF column must contain unencoded PDF byte data.

Data types for storing PDFs in databases

The NLP API Database client supports ingestion of PDFs as byte data from report databases.

All SQL DBMS provide data types for storing binary data of variable length. The data type you select to store PDF reports will depend on your system’s requirements: the maximum size of the PDF reports that must be stored, storage and other database performance considerations. It should also be noted, however, that the default maximum size of a submission that can be processed by the NLP API is 500 MB, although this can be changed upon request.

The following table shows the commonly-used variable-length binary data types available on postgreSQL, MySQL and Microsoft SQL Server. These data types have been tested with NLP API Python database client to ensure that it can retrieve PDF documents stored using these data types.

DBMS	Data Type	Max PDF Size (approx)
postgreSQL	bytea	1 GB
MySQL	BLOB	16 KB
MySQL	MEDIUMBLOB	16 MB
MySQL	LONGBLOB	4 GB
MS SQL server	varbinary(max)	2 GB

Exact storage requirements for the data types above is database engine-dependent. You should refer to the documentation for the specific DBMS engine that you are using.

For MySQL, in addition to BLOB, MySQL supports the VARBINARY data type. VARBINARY is a variable-length binary that is essentially equivalent to BLOB with respect to max storage size and SQL function support. However, VARBINARY columns store data in row memory. Hence, it’s maximum storage limit of 65585 bytes is shared with all row columns so that the effective storage limit may be less than 65585 bytes. For this reason, BLOB may be preferred over VARBINARY.

Ingesting Input Documents from JSON Files

An alternative to reading documents from a database or plaintext or PDF files is to read them from a JSON or JSONL file. This provides an alternative method of reading additional document metadata and storing it into the NLP API database documentmetadata table.

The JSON file must contain a list of objects, and each object must contain at least an “id”, and “text” key. A category and subcategory may also be specified in the file; if this is done, the engine will use these for processing the document. If these fields are omitted, they must be specified as command-line options when running the client. If both a category and subcategory exist in the JSON/JSONL file, and are specified on the command line, the category and subcategory specified in the file will be used by the engine for processing and the command-line options will be ignored. If there are any extra keys which have the same name as columns in the documentmetadata table, their values will be stored in that table. Any extra keys which are not known columns in the documentmetadata table will be ignored.

Please note however, that the use of additional document metadata keys is completely optional. A minimalist JSON/JSONL file that can still be processed would just contain an id and text key with their associated values; in this case, you would have to specify the category and subcategory on the command line of the emtellipro-db-client at runtime.

The JSON file(s) can use either UNIX (\n) or DOS (\r\n) line endings.

The following is a sample JSON file which also specifies a number of the optional fields from the documentmetadata table:

[
  {
    "id": "18",
    "source_document_id": "1000011",
    "category": "Cardiology",
    "subcategory": "ECG",
    "original_category": "Cardiology",
    "original_subcategory": "ECG",
    "description": "ECG",
    "text": "Atrial fibrillation with RVR.  Left axis.  Non-specific ST-T wave abnormalities.",
    "subject_id": "11110011",
    "institution": "City Hospital",
    "hadm_id": "110001121",
    "chartdate": "2022-02-16 20:48:09",
    "author_name": "Russell Sano",
    "subject_name": "Chioma Okonkwo",
    "subject_dob": "1967-01-05 03:24:04",
    "subject_gender": "female",
    "requestor": "Eun-Young Abategiovanni"
  }
]

If you’d like to store PDFs in the JSON file instead of plaintext, you should replace the text key with a pdf key whose value is a Base64-encoded PDF. If the text key remains in the JSON the client will treat it as a regular JSON containing a plaintext report. You can find a sample of such a JSON file in example-data/sample_ct_pdf_in_json.json. The PDF within it is example-data/sample_ct_imagebased_report.pdf.

Assuming your file is stored as document.json, and you wanted to save the results after processing it through the NLP API to a database, the processing command could look something like:

emtellipro-db-client \
    -c config.toml \
    process \
    --output $EMTELLIPRO_DATABASE \
    --state ./state4.dat \
    ./document.json

And a query on the corresponding PostgreSQL documentmetadata table from the NLP API database would contain the following information:

 id  | document_id | source_document_id | original_category | original_subcategory |  institution  | subject_id | subject_id_2 | description |  hadm_id  |      chartdate      | author_name  |  subject_name  |     subject_dob     | subject_gender | subject_address |        requestor        | document_summary 
-----+-------------+--------------------+-------------------+----------------------+---------------+------------+--------------+-------------+-----------+---------------------+--------------+----------------+---------------------+----------------+-----------------+-------------------------+------------------
 138 |         138 | 1000011            | Cardiology        | ECG                  | City Hospital | 11110011   |              | ECG         | 110001121 | 2022-02-16 20:48:09 | Russell Sano | Chioma Okonkwo | 1967-01-05 03:24:04 | female         |                 | Eun-Young Abategiovanni | 
(1 row)

An alternative to JSON is the JSONL format which will store each document object on a separate line:

{"id": "1", "text": "Document 1 text"}
{"id": "2", "text": "Second document text"}

Note that when using this format, the file extension must be .jsonl.

Loading files is an all-or-nothing process. If there are any format issues in any of your JSON files, an error will be returned and no files will be loaded into the state file.

If file loading has completed successfully, then all the input files were read and had the correct format.

This behaviour gives you an opportunity to fix any issues with your JSON input files ahead of time, ensuring you don’t accidentally re-process your documents.

Full format definition

There are two supported formats form the JSON-style input.

JSONL, where each line in the file is a JSON object (the “input JSON object”). The file extension must be .jsonl.
JSON, where the file contains a single JSON list with each element in the list being a JSON object (the “input JSON object”). The file extension must be .json.

Each input JSON object is a mapping describing a single input document.

The following keys are required:

id
(string) Each object must have a unique ID in that file. This is used for matching input documents to the output results from the NLP API.

The ID set here will be stored in the documentmetadata.source_document_id column.

If the process --doc-id-filepath option is used, the document ID sent to the NLP API will be formatted as {filepath}[{id}], which can be helpful when storing results as raw JSON/JSONL instead of storing them in a database.

This ID will also be combined with the filepath to be stored in the document.filename column.

The following keys specify what the contents of each document are. Usually only one should be specified, but using text and pdf together has a special meaning.

text
(string) The exact plaintext contents of the document.

If pdf is also specified, the data in text will be used for processing, but the data in pdf will be stored in the documentsourcechunk table if the process --store-pdf flag is used.

pdf
(string) Base64 encoded contents of a PDF file. If text is also specified, then text will have priority as the document text used for processing, but the value for this key will be stored in the documentsourcechunk table if the process --store-pdf flag is used.

filepath
(string) The path to a file containing the input document. The only supported extensions are .txt (for a plaintext document) and .pdf (for a PDF document). The file type is determined exclusively using the file extension.

If the document source is a PDF, you may also specify it:

pdf_path
(string) The path to the source PDF. This will be stored in document.source_path, and is useful largely as metadata (since the actual PDF data may be specified using the pdf key).

This should either be a filesystem path or standard URL for a remote location (e.g. https).

The following are optional keys specifying extra processing information:

category
This is the category used for the document when processing using the NLP API. If not specified, then the category from process --category will be used.

subcategory
This is the subcategory used for the document when processing using the NLP API. If not specified, then the category from process --subcategory will be used.

The input JSON object may also contain metadata about the input document. This will be stored either in the documentmetadata or documentstructuredmetadata tables, depending on which type of metadata it is.

Structured metadata refers to arbitrary key-value pairs which will be stored as-is in the documentstructuredmetadata table. These are used for storing any metadata that isn’t explicitly covered by the columns in the documentmetadata table.

structured_metadata
(mapping) All the key-value pairs in this mapping will be stored in the documentstructuredmetadata table. This is optional.

The following keys are a pre-defined set of metadata options which will be stored in the documentmetadata table, in columns matching the key name.

source_document_id
This can be used for storing your unique document identifier, e.g. an accession number or study UUID. This is different than the id key described earlier which is simply used to differentiate the individual JSON objects.

original_category
This is your internal category for the document. This can be something different than category since it’s not sent to the NLP API.

original_subcategory
This is your internal category for the document. This can be something different than subcategory since it’s not sent to the NLP API.

institution
This can be some short institution identifier, like a hospital name code if in a multi-institution environment.

subject_id
This represents a unique patient identifier / MRN / PID.

subject_id_2
This represents a secondary patient identifier.

description
This can store a short study description, e.g. “Urine Cytopathology” or “CT Lumbar Spine”.

hadm_id
This is for the hospital admission ID.

chartdate
This is for the datetime of the original study. This should be in a standard format to guarantee successful parsing, such as ISO-8601, RFC-3339, or RFC-2822 (or 5322).

If this is an empty string, this will be considered NULL for storage to the database.

author_name
This is for the author’s name.

subject_name
This is for the subject’s name.

subject_dob
This is for the subject’s date of birth. This should be in a standard format, such as ISO-8601, RFC-3339, or RFC-2822 (or 5322).

If this is an empty string, this will be considered NULL for storage to the database.

subject_gender
This is for the subject’s gender.

subject_address
This is for the subject’s address.

requestor
This is for the document’s requestor.

document_summary
This can be used for storing a summary of the document.

Ingesting Input Documents from CCD Files

A HL7 CCD file contains both human-readable text elements, and XML elements which provide structured data. The client extracts text to pass through the NLP API, and metadata from the structured data elements.

Processing CCD files was previously handled by a plugin, which has now been merged into the SDK.

CCD files are expected to have file extensions .ccd.xml, .xml, or .ccd. For these files the client will handle extraction of text and structured metadata from the file. See CCD Features for more details.

Storing Extended Report Metadata

If you’re building an app, let’s say where you want to search all your radiology reports for those where a pulmonary embolism was present, you’ll want to be able to just search for the reports by modality code (e.g. CT), or the report description (e.g. CTA Chest W/Contrast). In order to do this without doing complex cross-schema or cross-database joins, you’ll want to make use of the NLP API Database’s documentmetadata table. This table has the following structure (in PostgreSQL):

                                             Table "public.documentmetadata"
        Column        |            Type             | Collation | Nullable |                   Default                    
----------------------+-----------------------------+-----------+----------+----------------------------------------------
 id                   | integer                     |           | not null | nextval('documentmetadata_id_seq'::regclass)
 document_id          | integer                     |           |          | 
 source_document_id   | character varying(255)      |           |          | 
 original_category    | character varying(255)      |           |          | 
 original_subcategory | character varying(255)      |           |          | 
 institution          | character varying(255)      |           |          | 
 subject_id           | character varying(255)      |           |          | 
 subject_id_2         | character varying(255)      |           |          | 
 description          | character varying(255)      |           |          | 
 hadm_id              | character varying(255)      |           |          | 
 chartdate            | timestamp without time zone |           |          | 
 author_name          | character varying(255)      |           |          | 
 subject_name         | character varying(255)      |           |          | 
 subject_dob          | timestamp without time zone |           |          | 
 subject_gender       | character varying(255)      |           |          | 
 subject_address      | text                        |           |          | 
 requestor            | character varying(255)      |           |          | 
 document_summary     | text                        |           |          | 
Indexes:
    "pk_documentmetadata" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_documentmetadata_document_id_document" FOREIGN KEY (document_id) REFERENCES document(id)

If you are retrieving reports from a database, the metadata table allows storage of a number of additional metadata fields with your processed data to make app building and integration easier. An explanation of these fields is provided here:

id
this field is just an auto-numbered index field

document_id
this field is the foreign key link to the document.id field

source_document_id
this field is supposed to be used for storing your unique identifier - e.g. an accession number or study UUID

original_category
this is suggested (if you’re in a multi-document-type environment) to be something like Radiology or Pathology, etc.

original_subcategory
this is suggested to represent the sub-category of your report type - in Pathology it might be cytopath or in Radiology it might be the 0008,0061 (modalities in study) field

institution
this can be null if just in a single-institution environment or could represent a hospital name code if in a multi-institution environment

subject_id
this is intended to allow storage of a unique patient identifier/MRN/PID

description
this is intended to store the study description, e.g. Urine Cytopathology or CT Lumbar Spine

hadm_id
this is intended to store a hospital admission ID

chartdate
this is intended to store a datetime from the original study

author_name
this is intended to store the author’s name

subject_name
this is intended to store the subject’s name

subject_dob
this is intended to store the subject’s date of birth

subject_gender
this is intended to store the subject’s gender

requestor
this is intended to store the document’s requestor

If storing a value for chartdate or subject_dob in an SQLite database, it will first be parsed using dateutil, because the sqlite driver requires it. For all other databases, the value being inserted will have the same format as the one coming from the source database.

So, a SQL query from our source reports_db database that would store data in these fields would be:

SELECT
    report_id AS id,
    report_id AS source_document_id,
    category AS original_category,
    subcategory AS original_subcategory,
    institution,
    subject_id,
    description,
    hadm_id,
    chartdate,
    author_name,
    subject_name,
    subject_dob,
    subject_gender,
    requestor,
    report AS text
FROM reports_db
WHERE category='Radiology' AND subcategory='CT'
ORDER BY report_id ASC

And if we were going to use this same query in a processing string for the DB client, it would look like:

emtellipro-db-client \
    -c config.toml \
    process \
    --output $EMTELLIPRO_DATABASE \
    --state ./state5.dat \
    --sql-query "SELECT report_id AS id, report_id AS source_document_id, category AS original_category, subcategory AS original_subcategory, institution, subject_id, description, hadm_id, chartdate, author_name, subject_name, subject_dob, subject_gender, requestor, report AS text FROM reports_db WHERE category='Radiology' AND subcategory='CT' ORDER BY report_id ASC" \
    --job-id ct_reports \
    $EMTELLIPRO_INPUT

And if we were going to query the documentmetadata table to look at how the data from our source document database was stored, it would look something like:

 id | document_id | source_document_id | original_category | original_subcategory | institution | subject_id |              description               |  hadm_id  |      chartdate      | author_name | subject_name | subject_dob | subject_gender | requestor
----+-------------+--------------------+-------------------+----------------------+-------------+------------+----------------------------------------+-----------+---------------------+-------------+--------------+-------------+----------------+-----------
  1 |           1 |          111111111 | Radiology         | CT                   | city        | 231523354  | CTA CHEST W&W/O C&RECONS, NON-CORONARY | AZ928913  | 2019-04-10 00:00:00 |             |              |             |                |
  2 |           2 |          111111112 | Radiology         | CT                   | city        | 231523354  | CT PELVIS W/O CONTRAST                 | AZ928913  | 2019-04-22 00:00:00 |             |              |             |                |
(2 rows)

Now we could build an application where we were just looking for diseases in CT reports, or just in reports that were done in a certain date range or just with a certain description.

Next steps

Assuming that you started at Installation, let’s recap what we’ve done in this page:

Installed the package & tested that it’s set up properly.
Used the emtellipro-db-client database client to build database tables
Used the emtellipro-db-client database client to process a test file and store the output into a database, or retrieve a document from a database, process it, and store the output in a database

To get started building your own client, your next step is to visit the Quick overview of the SDK page for python code examples using the Emtellipro library.