• HFQL: Direct SQL Access
  • 37.0 HFQL/SQL Overview
  • 37.1 SQL Syntax
  • 37.2 SQL Syntax: Select
  • 37.3 SQL Syntax: Where
  • 37.4 SQL Syntax: Limitations
  • 37.5 SQL Syntax: Examples
    • 37.1    SQL Syntax   
       Table of Contents

    HFQL/SQL Overview

    37.0.1HFQL: Direct SQL Access to the FHIR Repository
    Experimental

     

    Smile CDR supports direct SQL access to the FHIR repository using the HAPI FHIR Query Language (HFQL), which is a SQL-like query syntax.

    37.0.2Overview

     

    HFQL aims to provide SQL-like database connectivity to existing FHIR repositories, using standard off the shelf JDBC-compliant database tools.

    A simple example query is shown below:

    SELECT
    name[0].family as family,
    name[0].given[0] as given, 
    birthDate,
    identifier.where(system='http://hl7.org/fhir/sid/us-ssn').first().value as SSN
FROM
    Patient
WHERE
    active = true

    See SQL Syntax for details on the HFQL syntax.

    37.0.3Goals and Architecture

     

    HFQL allows for relatively simple querying of data in the FHIR repository using standard database tools. These include database browsers as well as reporting engines.

    The HFQL driver is designed to allow users who are familiar with SQL to make queries against the FHIR repository using a familiar language. Because the FHIR data model is a document format and not a table+column format, there is a natural impedance mismatch when using SQL for query, but the driver tries to minimize issues that this causes.

    High performance query execution is not a goal of this driver. We are striving to make an engine that is powerful and flexible for mining data directly out of an online FHIR repository, but the system leverages the existing repository database tables and indexes and often has to rely on application-level code to perform analysis and extract data. In other words, this driver is a good candidate for jobs where useful data is a requirement but quick responses are not.

    The architecture for the HFQL driver is shown below.

    Architecture

    The JDBC driver may be installed in any database tool which supports the Java JDBC API. Only a few parts of the JDBC API have been implemented, so some tools may fail if they try to use a part of the API that has not yet been implemented. This driver has been extensively tested with DBeaver, which is a high quality database browser with an excellent free & open source Community Edition.

    Communication between the JDBC client and the server uses a custom FHIR REST operation. This means that the driver should be able to communicate with any Smile CDR server that exposes a REST endpoint and has the HFQL $hfql-execute operation enabled.

    37.0.3.1Security

    The HFQL driver uses HTTP Basic Auth for authentication. Every query happens under the authority and permissions of the authenticated user. The authorization and consent layers fully apply to HFQL queries, meaning that requests will be blocked if they try to access data that the user does not have appropriate permissions to access. Data may be filtered, redacted, or omitted by consent services and these redactions will also apply to the returned HFQL data.

    You may wish to create a dedicated endpoint that exists only to serve this operation as opposed to using an existing FHIR endpoint module. This allows the HFQL endpoint to use a different security model (especially if your existing endpoint uses SMART on FHIR / OIDC as opposed to HTTP Basic Auth), and allows you to specify longer request timeouts on the HFQL endpoint.

    Audit and transaction log entries are not generated for HFQL queries.

    37.0.4JDBC Driver

     

    When HFQL is enabled on the server, a JDBC-compatible driver is available. This can be used to query the FHIR server directly from a JDBC compliant database browser.

    This module has been tested with DBeaver, which is a free and excellent database browser. Other JDBC compatible database tools may also work. Note that not all JDBC API methods have been implemented in the driver, so other tools may use methods that have not yet been implemented. Please let us know in the Google Group if you encounter issues or have suggestions.

    The JDBC driver can be downloaded from the GitHub Releases site.

    To import this driver into your database tool, import the JDBC JAR and use the following settings:

    SettingDescription
    Class Nameca.uhn.fhir.jpa.fql.jdbc.JdbcDriver
    URLjdbc:hapifhirql:[server_base_url]
    Username / PasswordIf provided, the username/password will be added as an HTTP Basic Authorization header on all requests to the server.

    37.0.5Enabling Server Support

     

    To enable HFQL support on a Smile CDR instance:

    • Create (or use an existing) a FHIR Storage (RDBMS) module.
    • Create (or use an existing) FHIR Endpoint module which has the FHIR Storage (RDBMS) module as a dependency.
    • On the FHIR Endpoint module, enable the HFQL/SQL Endpoint Enabled setting.
    • On the FHIR Endpoint module, enable HTTP Basic Security.
    • Ensure that a user exists with the HFQL_EXECUTE permission.
  • HFQL: Direct SQL Access
  • 37.0 HFQL/SQL Overview
  • 37.1 SQL Syntax
  • 37.2 SQL Syntax: Select
  • 37.3 SQL Syntax: Where
  • 37.4 SQL Syntax: Limitations
  • 37.5 SQL Syntax: Examples
    • 37.1    SQL Syntax