> For clean Markdown of any page, append .md to the page URL. > For a complete documentation index, see https://docs.emtelligent.com/llms.txt. > For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.emtelligent.com/_mcp/server. # Database client usage Here we show how to use the provided `emtellipro-db-client` database client to: 1. Build a set of database tables used to store the NLP API output 2. 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](/nlp/python/database-client/options). > **Info** > > [End-to-end Report Processing and Data Extraction Example](/nlp/python/database-client/end-to-end-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](/nlp/python/database-client/options)\ > Documentation for all the database client's configuration options. > > [Using the Database Client with Snowflake](/nlp/python/database-client/snowflake)\ > How to use the database client to read and write documents to Snowflake databases. > > [Using the Database Client with Microsoft SQL Server](/nlp/python/database-client/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](/nlp/python/database-client/migrating-from-simple-client)\ > 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. ```bash 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](/nlp/python/reference/data#emtelliprodataannotateddocument) 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](/nlp/python/installation) instructions, you're nearly there. Your next step is to install the relevant database connector: #### PostgreSQL ```bash pip install psycopg2 ``` #### MySQL/MariaDB ```bash pip install pymysql ``` If you'll be using a MySQL database, there are some extra dependencies you'll need (example shown for Debian/Ubuntu below): ```bash sudo apt install libssl-dev ``` #### SQL Server See [Using the Database Client with Microsoft SQL Server](/nlp/python/database-client/sql-server) for more information about using SQL Server. You also need to install Microsoft's ODBC drivers. ```bash pip install pyodbc ``` #### Snowflake See [Using the Database Client with Snowflake](/nlp/python/database-client/snowflake) for more information about using Snowflake. ```bash pip install --upgrade 'snowflake-sqlalchemy>=1.6' ``` #### DuckDB ```bash pip install duckdb-engine ``` ## 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. ```bash 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: ```text Testing emtelliPro connection... Successful connection to https://emtellipro/ Connected as USERNAME ``` > **Tip** > > 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. ```bash 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. > **Tip** > > 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. > **Warning** > > 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: ```sql 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 ```bash export EMTELLIPRO_DATABASE=postgresql://:@:5432/example_db ``` #### MySQL/MariaDB ```bash export EMTELLIPRO_DATABASE=mysql+pymysql://:@:3306/example_db ``` #### SQL Server See [Using the Database Client with Microsoft SQL Server](/nlp/python/database-client/sql-server) for more information about using SQL Server. ```bash export EMTELLIPRO_DATABASE=mssql+pyodbc://:@:1433/emtellipro_output?driver=ODBC+Driver+17+for+SQL+Server ``` #### Snowflake See [Using the Database Client with Snowflake](/nlp/python/database-client/snowflake) for more information about using Snowflake. ```bash export EMTELLIPRO_DATABASE=snowflake://:@/EMTELLIPRO/PUBLIC?warehouse=COMPUTE_WH&role=SYSADMIN ``` We'll now use the defined database environment variable `$EMTELLIPRO_DATABASE` to create the tables for our output by executing: ```bash 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: ```bash emtellipro-db-client migrate --output $EMTELLIPRO_DATABASE ``` > **Warning** > > 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: 1. Reading input documents as .txt, .pdf, or .json (or .jsonl) files from disk 2. Reading input documents from a database These use different command-line options which are explained below. > **Tip** > > For a complete list of NLP API client commands, please review the command-line help. For available report submission options, run the following commands: ```text 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 `[]` sections\ Replacing `` 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`** ```toml title="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" ``` > **Note** > > * 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: 1. The defaults for each option are read first. 2. The parameters in the config file override any defaults. 3. Any options passed explicitly on the command-line override the config file and the defaults for that option. > **Info** > > [Database Client Options](/nlp/python/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=\` 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: ```text 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: ```text 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: 1. The URL of the database for the source reports 2. 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: ```bash export EMTELLIPRO_INPUT=mysql+pymysql://username:password@localhost:3306/reports_db ``` And the SQL query we'll use to retrieve reports from is: ```sql SELECT report_id AS id, report AS text FROM reports WHERE category='Radiology' and subcategory='CT' ORDER BY report_id ASC LIMIT 1 ``` > **Warning** > > 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: ```text 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 ``` > **Note** > > 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: ```text 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`): ```sql 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: ```text 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: ```json [ { "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" } ] ``` > **Note** > > 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: ```text 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: ```text 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: ```json {"id": "1", "text": "Document 1 text"} {"id": "2", "text": "Second document text"} ``` Note that when using this format, the file extension must be `.jsonl`. > **Warning** > > 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. 1. JSONL, where each line in the file is a JSON object (the "input JSON object"). The file extension must be `.jsonl`. 2. 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](https://build.fhir.org/ig/HL7/CDA-ccda-2.2/StructureDefinition-2.16.840.1.113883.10.20.22.1.2.html) 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. > **Note** > > 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](/nlp/python/ccd/overview#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): ```text 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 > **Note** > > If storing a value for `chartdate` or `subject_dob` in an SQLite database, it will first be parsed using [dateutil](https://dateutil.readthedocs.io/en/stable/), 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: ```sql 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: ```text 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: ```text 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](/nlp/python/installation), let's recap what we've done in this page: 1. Installed the package & tested that it's set up properly. 2. Used the `emtellipro-db-client` database client to build database tables 3. 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](/nlp/python/introduction) page for python code examples using the `Emtellipro` library.