Introduction
The emtellipro database schema provides a high-degree of flexibility for developing SQL queries that retrieve data for various clinical and biomedical research studies from the NLP-extracted structured data in the database. Fortunately these can be organized into a few commonly used patterns. Some patterns use the NLP API’s core data extraction features, while others use the more advanced relation data extraction features. Because of the variety of data questions and query patterns that can be employed, users new to the Output Database may find it challenging to know where to start.
This query cookbook provides an entry point by:
- Cataloging the most useful query patterns and providing some background with relevant clinical questions.
- Providing examples that showcase all the data extraction features that NLP API provides.
- Providing a few advanced query examples. These combine simpler query patterns, with additional customer-specific knowledge of the source report features, or DBMS-specific SQL capabilities.
- Providing fully-functional SQL code that can be copied and pasted by the user.
The SQL queries provided were developed and tested on these RDBMS systems:
- PostgreSQL version 12
- Snowflake on Azure
- Microsoft SQL Server version 17
Where platform-specific SQL features are used, a sample query for those database system(s) is provided.
System-Specific SQL Considerations
Snowflake
Note to Snowflake users: Snowflake’s file-based data architecture supports a high throughput uploading of the NLP API csv output into an Output Database. In this data loading model, the uniqueness constraint is not enforced by Snowflake, and the concept table will contain duplicate entries unless a data pipeline step was implemented to remove duplicates. You can easily work around this issue by using a CTE (common table expression) to return distinct rows whenever you perform joins using the concept table. An example is shown in this query.
Please note, since this treatment is required only in cases where the concept table was not deduplicated after load, we leave it to the discretion of the reader to adjust their queries accordingly.
How to Use this Document
Before attempting to run the SQL queries in the cookbook, you should familiarize yourself with the core concepts. Additionally, some familiarity with the entity-level and document-level tables may be helpful ahead of time; however, you may also choose to refer to the database schema of these core tables as needed.
Before you can run the sample queries provided, you will need to have completed the following:
- Set up an Output Database
- Populated it by processing some medical reports
- Depending on the query pattern you choose, additional processing requirements may be required. Each cookbook example lists the processing features that are needed.
How the NLP API is setup to process reports may vary depending on your organization’s technology platform and requirements. We recommend that you reach out to our technical support to provide some recommendations on how to proceed - whether you are in the evaluation or system integration phase.
It is not necessary to specify the processing options using the --features flag. When this flag is omitted in the processing command, all processing features that have been enabled for your license key will be used. To find out if your license was set up to allow use of a specific processing feature, please contact support@emtelligent.com. The available processing options are documented here.