smileutil: Realtime Export Generation

EAP

The generate-rte-schema command generates a minimal Realtime Export (RTE) json ruleset and an SQL schema creation script based on a package-spec.json which identifies a source Implementation Guide (IG) which has been saved to the classpath where smileutil is running.

See Realtime Export for information on RTE and and Packages and Implementation Guides for information on IGs and package-spec.json.

Usage

Example using FHIR Version R4 and POSTGRES database engine.

bin/smileutil generate-rte-schema
-i "./path/to/package-spec.json"
-o "./output/files/path"
-v "R4"
-s "POSTGRES_9_4"
-c "/path/to/child/tables/file.txt"

and the package-spec.json file should look like:

{
  "name": "hl7.fhir.r4.core",
  "version": "4.0.1",
  "packageUrl": "classpath:hl7.fhir.r4.core-4.0.1.tgz",
  ...
}

The packageUrl parameter MUST be specified and be pointing at a valid IG NPM package in either the classes or customerlib folders.

The following output files will be saved in the specified directory (./outputfiles/path):

• output.json the RTE schema
• output.sql the SQL scripts for the specified engine to create the desired tables
• unmapped_columns.csv a CSV file containing a list of columns that could not be mapped and the reasons why

Options

• -i [implementation-guide] – The input package-spec.json defining the IG to use.
• -o [output] – Path where output json and sql files should be saved.
• -v [version] – The FHIR version that is to be used. Currently, only supported value is R4
• -s [sql-engine] – The SQL engine to target for table creation scripts. Supported values are [MSSQL_2012, ORACLE_12C, POSTGRES_9_4, SNOWFLAKE, SNOWFLAKE_HYBRID]
• -p [table-name-prefix] – (optional) Tables are named after the resource type with some prefix. The default prefix is rte_; but another can be specified with this parameter.
• -c [child-tables] – (optional) An input text file containing a list of FHIR paths (one path per line) defining child tables to create. For example, specifying Patient.name would generate a child patient name table (provided Patient.name is a FHIR path utilized by a SearchParameter in the given IG). If not provided, no child tables will be created.

Snowflake Support

Smile CDR supports two types of Snowflake table generation for RTE: standard tables and hybrid tables.

Choosing Between SNOWFLAKE and SNOWFLAKE_HYBRID

Snowflake Standard Tables

Standard Snowflake tables are optimized for analytical workloads and append-only data.

Usage

bin/smileutil generate-rte-schema \
  -i "./path/to/package-spec.json" \
  -o "./output/files/path" \
  -v "R4" \
  -s "SNOWFLAKE" \
  -p "rte_"

The generated DDL will use CREATE TABLE syntax. PRIMARY KEY and FOREIGN KEY constraints are optional.

Limitations

• Updates and deletes are not optimized (full table scans)
• No row-level locking - not suitable for concurrent writes
• Foreign key constraints are not enforced
• Best suited for write-once, read-many analytical workloads

Snowflake Hybrid Tables

Snowflake hybrid tables provide transactional ACID guarantees with row-level locking, making them suitable for transactional workloads while maintaining the performance characteristics of Snowflake.

Features

• PRIMARY KEY enforcement - All hybrid tables must have a primary key constraint
• FOREIGN KEY constraints - Referential integrity is enforced at the database level
• UNIQUE constraints - Uniqueness is enforced
• Row-level locking - Concurrent updates are supported with proper isolation
• ACID transactions - Full transactional support via JDBC COMMIT/ROLLBACK

Usage

To generate DDL for Snowflake hybrid tables, use the SNOWFLAKE_HYBRID SQL engine:

bin/smileutil generate-rte-schema \
  -i "./path/to/package-spec.json" \
  -o "./output/files/path" \
  -v "R4" \
  -s "SNOWFLAKE_HYBRID" \
  -p "rte_"

The generated DDL will use CREATE HYBRID TABLE syntax with explicit PRIMARY KEY and FOREIGN KEY constraints.

Requirements

• All tables must have a PRIMARY KEY defined (enforced by Snowflake)
• Use the id column as the primary key (standard RTE pattern)
• JDBC driver: net.snowflake.client.jdbc.SnowflakeDriver

Configuration Example

For runtime RTE execution with hybrid tables:

# Snowflake Connection (same configuration for both regular and hybrid tables)
module.realtime_export.config.db.driver=SNOWFLAKE
module.realtime_export.config.db.url=jdbc:snowflake://<snowflake-account>.snowflakecomputing.com?db=<snowflake-db-name>&schema=<snowflake-schema>&warehouse=<snowflake-warehouse>&role=<snowflake-role>&JDBC_QUERY_RESULT_FORMAT=JSON
module.realtime_export.config.db.username=<snowflake-usr>
module.realtime_export.config.rte.snowflake.pat=<snowflake-programmatic-access-token>

Prerequisites:

• Database and schema must exist before RTE starts (CREATE DATABASE mydb; CREATE SCHEMA myschema;)
• Warehouse must be created and accessible to the user
• User must have appropriate permissions (CREATE TABLE, INSERT, UPDATE, DELETE on schema)
• For hybrid tables, ensure warehouse has sufficient compute resources for transactional workloads

Naming Conventions:

• Database names: lowercase recommended (e.g., cdr_production)
• Schema names: lowercase recommended (e.g., rte_exports)
• Table prefix: use the -p parameter when generating the schema to namespace tables (default is rte_)

Performance Considerations

• Batch processing is automatically used when multiple resources are processed together
• Hybrid tables are optimized for mixed OLTP/OLAP workloads
• Consider using regular Snowflake tables (SNOWFLAKE engine) for append-only analytical workloads