# Realtime Export Overview [LMA](/content/docs/hdp_product/maturity_model.html) The purpose of the Realtime Export module is to permit realtime extraction of FHIR data from Smile CDR into a remote database. The following diagram shows a quick overview of the architecture of the realtime export process. For targets that prefer few large writes over per-change streaming, [Bulk Batch Replication](/content/docs/realtime_export/bulk_batch_replication.html) is a scheduled batch sibling of this module: it periodically replicates windows of resource version history in bulk to a pluggable `IRepository` target. Before discussing configuration of the module, it is important to note that there are two main modes in which Realtime Export can operate. These two modes are `POINTCUT` and `CHANNEL`. ## Pointcut Mode In `POINTCUT` mode, the Smile CDR Storage module has an interceptor registered on it which forwards all Creates/Updates/Deletes to an internal channel, which Realtime Export consumes. The advantage of this mode is that it requires no external software, and works out of the box. To accomplish this, Realtime Export relies on a Storage module as a dependency. ## Channel Mode As of 2024.11.R01, support for Debezium has been removed. This means this setting is no longer read, and POINTCUT mode is always used. ## FHIR Storage Module (Pointcut) The FHIR Storage is the source of the data used by Realtime Export. If the source type is set to `POINTCUT`, then this dependency must be filled. If you are instead using `CHANNEL`, then Realtime Export module does not require a Storage module dependency. Note that if you do depend on the Storage module, you must enable the [Pointcut-based Realtime Export mode](/content/docs/configuration_categories/fhir_realtime_export.html#property-pointcut-based-realtime-export-mode-enabled/index.html) so that the module correctly registers all necessary interceptors. ## Cluster Manager Module The Cluster Manager module contains the configuration used to connect to the selected message broker. See the [Message Brokers](/content/docs/guide_installation/message_broker.html) documentation for information on how to select and configure a message broker. By default an embedded Apache ActiveMQ server will be used, and this is acceptable for testing purposes but an external broker should be used in a production scenario. # Operational Overview [LMA](/content/docs/hdp_product/maturity_model.html) At a high level, Realtime Export listens for all changes to FHIR resources. This includes creates, updates, and deletes. Upon detecting such a change, the Realtime Export module will apply any relevant transformations to the resource, in order to convert it into a form that can be easily consumed by an RDBMS. After this, it will generate and execute the SQL commands necessary to update the remote database. This transformation is done via user-configured `transformers`. # Limitations [LMA](/content/docs/hdp_product/maturity_model.html) Currently there are several limitations of Realtime Export. Firstly, the remote database schema must already exist. This means that in order to effectively use realtime export, it will be necessary to spend some time creating the schema manually. During schema creation, there are several needs that must be addressed. 1. Each top-level table must have an integer field called `version`. This is needed to handle logical updates of remote data. 2. Each child table must have a string field called `id`. This will be automatically filled with a UUID. 3. Each child table must have a string field called `parent_reference`, which is constrained by the ID of the parent table it refers to. 4. Each child table must have a string field called `source_resource_id.` This is needed to handle logical deletes. 5. While Realtime Export will generate and execute the SQL required to add data to the remote database, creation of foreign key constraints must be handled by the user. Next, it is important to note that each remote table is designed to hold exactly one resource type. This means that in its current implementation, remote tables cannot be partially filled by multiple resources. In general, one resource corresponds to one completed row in one or more remote tables. Here is an example of an appropriate schema for realtime export. Note that in future examples, some columns may be omitted for brevity. # Troubleshooting [LMA](/content/docs/hdp_product/maturity_model.html) The [Realtime Export Troubleshooting log](/content/docs/logging/troubleshooting_logs.html#realtime-export-troubleshooting-log/index.html) can be helpful in diagnosing issues relating to Realtime Export. Furthermore, if a message completely fails processing, the cause of the failure is stored along with the failed message. # Supported Databases [LMA](/content/docs/hdp_product/maturity_model.html) Realtime Export supports exporting data to the following database systems: | Database | Driver Type | Notes | | --- | --- | --- | | PostgreSQL | `POSTGRES_9_4` | Recommended for production use | | MySQL | `MYSQL_5_7` | Production ready | | MariaDB | `MARIADB_10_1` | Production ready | | Oracle | `ORACLE_12C` | Production ready | | Microsoft SQL Server | `MSSQL_2012` | Production ready | | H2 | `H2_EMBEDDED` | For testing only | | Derby | `DERBY_EMBEDDED` | For testing only | | Snowflake | `SNOWFLAKE` | Experimental, supports Hybrid Tables | ## Snowflake Support Snowflake is supported as a target database for Realtime Export, including support for [Snowflake Hybrid Tables](https://docs.snowflake.com/en/user-guide/tables-hybrid) which provide: - **ACID Transactions**: Full transactional guarantees with commit/rollback support - **Primary Key Enforcement**: Hybrid tables enforce primary key constraints - **Foreign Key Integrity**: Referential integrity between parent and child tables - **Row-level Locking**: Concurrent updates with row-level locking **Authentication Methods** Snowflake supports three authentication methods with the following precedence: 1. **Programmatic Access Token (PAT)** \- Recommended for MFA-enabled accounts. If set, takes precedence over all other methods. 2. **OAuth Token** \- For OAuth-based authentication. Used when PAT is not provided. 3. **Password** \- Standard username/password authentication. Used as fallback. **Configuration Examples** **Using Password Authentication:** ```properties module.realtime_export.config.db.driver=SNOWFLAKE module.realtime_export.config.db.url=jdbc:snowflake://.snowflakecomputing.com?db=&schema=&warehouse=&role=&JDBC_QUERY_RESULT_FORMAT=JSON module.realtime_export.config.db.username=myuser module.realtime_export.config.db.password=mypassword ``` Copy **Using PAT Authentication (recommended for MFA-enabled accounts):** ```properties module.realtime_export.config.db.driver=SNOWFLAKE module.realtime_export.config.db.url=jdbc:snowflake://.snowflakecomputing.com?db=&schema=&warehouse=&role=&JDBC_QUERY_RESULT_FORMAT=JSON module.realtime_export.config.db.username=myuser module.realtime_export.config.rte.snowflake.pat= ``` Copy **Using OAuth Token Authentication:** ```properties module.realtime_export.config.db.driver=SNOWFLAKE module.realtime_export.config.db.url=jdbc:snowflake://.snowflakecomputing.com?db=&schema=&warehouse=&role=&JDBC_QUERY_RESULT_FORMAT=JSON module.realtime_export.config.db.username=myuser module.realtime_export.config.rte.snowflake.token= ``` Copy When using Snowflake Hybrid Tables, ensure your schema uses `CREATE HYBRID TABLE` statements and includes appropriate PRIMARY KEY and FOREIGN KEY constraints. # Batch Processing [LMA](/content/docs/hdp_product/maturity_model.html) For improved performance, especially with databases like Snowflake, Realtime Export supports batch processing of multiple resources within a single transaction. Batch operations: - Group resources by target table for efficient batch inserts - Preserve insertion order to satisfy foreign key constraints (parent resources like Patient are inserted before child resources) - Use JDBC batch operations for improved throughput - Process all resources in a single transaction for atomicity Batch processing is automatically used when multiple resources are created together (e.g., when processing a Bundle). # Loading Existing Data If using Realtime Export, it is best to start with empty databases on both sides (both FHIR and remote repositories). However, if a FHIR database already exists, Smile provides a way to load existing FHIR resources into the remote repository. For details and limitations, see [loading existing Data](/content/docs/realtime_export/bootstrap_loading_rte.html) Realtime Export Overview Realtime Export [Realtime Export Overview](/content/docs/realtime_export/realtime_export_overview.html) [Bootstrapping RTE Data](/content/docs/realtime_export/bootstrap_loading_rte.html) [Realtime Export Rules Definition](/content/docs/realtime_export/realtime_export_rules_definition.html) [Using FHIRPath](/content/docs/realtime_export/using_fhirpath.html) [Delta Lake Export --EAP--](/content/docs/realtime_export/delta_lake_export.html) [Bulk Batch Replication --EAP--](/content/docs/realtime_export/bulk_batch_replication.html) [Bootstrapping RTE Data](/content/docs/realtime_export/bootstrap_loading_rte.html)