SQL on FHIR Concepts

SQL on FHIR allows you to view your FHIR data in relational format, and access it over standard SQL connections. Before you use SQL on FHIR, we recommend that you review some basic concepts that will help understand how the product works, such as resources, resource types, and data mapping.

FHIR Resources

The FHIR specification defines over a hundred (and counting) types of data that form the basis of interoperability for different healthcare use cases. Commonly used FHIR resources include, for example, Patient, Encounter, ExplanationOfBenefit and Provider.

Every type of resource in SQL on FHIR is represented by a table. A row in one of those tables represents a single resource of that type.

FHIR Resource Identifiers

FHIR resources can refer to each other, such as when an Encounter resource refers to a particular Patient resource. In the 1upHealth FHIR server, these references use local resource identifiers, in the form of RESOURCE_TYPE/ID. For example, if an Encounter references a Patient, the reference is in this format: Patient/abc123.

How FHIR Maps to Relational Data

Every FHIR resource is stored as its own table in SQL on FHIR. These tables follow the fhir_r4 schema are of the format fhir_r4.resourcetype. For example, Patient resources can be found in the fhir_r4.patient table and Encounter resources are in the fhir_r4.encounter table.

The columns in each table directly relate to the FHIR specification for each resource. For example, the FHIR attribute meta is represented by the SQL on FHIR column meta.

Commonly referenced data that is of a simple data type have been unnested into their own columns. To use complex data types, such as arrays and structs, you must unnest them in your SQL queries.

SQL

Structured Query Language (SQL) is used to query relational database systems. It is built into most common BI tools including Tableau, Looker, and Power BI.

SQL on FHIR’s query engine is built on Amazon Redshift, and has a documented list of data types and supported features. SQL queries from other databases might not work without modification.

For information about the supported SQL dialect, see the Amazon Redshift SQL Reference.

SQL on FHIR only provides read-only access to data. You cannot modify data using SQL.