SQL templates

[niquola]

We can introduce SQL templates with a macro to write portable queries:

SELECT   ({$ p.birthDate })
FROM  patient p, encounter e
WHERE ({$ p.id }) = ({$ e.subject.sof_id })

List of possible macro functions:

• $(path) - extract element by path
• unnest(path) - unnest elements from the path
• contains(path, value) - check that values are in an array
• age(path) - calculate age function

BNF for path expression:

expression:  (column path-item+) | ( table path-item+) | path-item+
column: '!' path-item
table: identifier
path-item:  index | element
element: '.' identifier
index: '[' integer ']'
identifier: [a-zA-Z0-9]+
integer: [0-9]+

Examples:

SELECT
   ({$ .name[0].family }) as family
   ({$ p.name[0].given[0] }) as last
   ({$ !addr.code })

FROM patient p, ({unnest .address}) addr

!col.path is required for JSON databases and have same behavior as .col.path in columnar dbs

[ir4y]

Here is a Python implementation of macro language https://github.com/beda-software/sql-on-fhir-py/blob/main/macro.py

[johngrimes]

Yep, you're right - that needs updating 🤦

In most cases, you can just use:

from pathling import PathlingContext

pc = PathlingContext.create(enable_delta=True)

If you want to customise the Spark session, you probably want something like this:

from pyspark.sql import SparkSession
from pathling import PathlingContext

spark = (
    SparkSession.builder.config(
        "spark.jars.packages",
        "au.csiro.pathling:library-runtime:6.4.2,io.delta:delta-core_2.12:2.4.0",
    )
    .config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension")
    .config(
        "spark.sql.catalog.spark_catalog",
        "org.apache.spark.sql.delta.catalog.DeltaCatalog",
    )
    .config("spark.driver.memory", "4g")
    .getOrCreate()
)

pc = PathlingContext.create(spark)

[dkapitan]

Thanks for this, makes me feel less of a noob ... 😄. I will keep you posted on my project. In fact, are you in for a co-authorship for a paper with the title 'Solution designs for composable and scaleable analytics on FHIR platforms'. Am trying to get people together from different backgrounds and countries (Netherlands and Kenya) to help spread the word.

[johngrimes]

Sounds good Daniel, send me the details at John.Grimes at csiro.au and we can discuss.

[dkapitan]

Will do @johngrimes. Give me a couple of weeks to setup a first draft and involve the other authors and will come back to you after that.

[ir4y]

@dkapitan got it.
Yes, I would be interested in building a view runner for Duckdb.
It should be an easy task since I already have a full-featured FHIRPath engine implementation:
https://github.com/beda-software/fhirpath-py
We just need to integrate it with Duckdb.

[dkapitan]

I have had a chat with someone deeply involved in openEHR. Their AQL may be an inspiration for our work:
https://specifications.openehr.org/releases/QUERY/Release-1.1.0/AQL.html