Enhance healthcare companies by affected person 360: A zero-ETL method to allow close to real-time knowledge analytics

Healthcare suppliers have a possibility to enhance the affected person expertise by accumulating and analyzing broader and extra various datasets. This contains affected person medical historical past, allergic reactions, immunizations, household illness historical past, and people’ way of life knowledge similar to exercise habits. Accessing these datasets and forming a 360-degree view of sufferers permits healthcare suppliers similar to declare analysts to see a broader context about every affected person and personalize the care they supply for each particular person. That is underpinned by constructing a whole affected person profile that allows declare analysts to establish patterns, traits, potential gaps in care, and adherence to care plans. They will then use the results of their evaluation to know a affected person’s well being standing, therapy historical past, and previous or upcoming physician consultations to make extra knowledgeable selections, streamline the declare administration course of, and enhance operational outcomes. Reaching this will even enhance normal public well being by higher and extra well timed interventions, establish well being dangers by predictive analytics, and speed up the analysis and growth course of.

AWS has invested in a zero-ETL (extract, rework, and cargo) future in order that builders can focus extra on creating worth from knowledge, as an alternative of getting to spend time getting ready knowledge for evaluation. The answer proposed on this put up follows a zero-ETL method to knowledge integration to facilitate close to real-time analytics and ship a extra personalised affected person expertise. The answer makes use of AWS companies similar to AWS HealthLake, Amazon Redshift, Amazon Kinesis Information Streams, and AWS Lake Formation to construct a 360 view of sufferers. These companies allow you to gather and analyze knowledge in close to actual time and put a complete knowledge governance framework in place that makes use of granular entry management to safe delicate knowledge from unauthorized customers.

Zero-ETL refers to a set of options on the AWS Cloud that allow integrating completely different knowledge sources with Amazon Redshift:

Answer overview

Organizations within the healthcare trade are presently spending a major quantity of money and time on constructing complicated ETL pipelines for knowledge motion and integration. This implies knowledge can be replicated throughout a number of knowledge shops by way of bespoke and in some instances hand-written ETL jobs, leading to knowledge inconsistency, latency, and potential safety and privateness breaches.

With help for querying cross-account Apache Iceberg tables by way of Amazon Redshift, now you can construct a extra complete patient-360 evaluation by querying all affected person knowledge from one place. This implies you possibly can seamlessly mix data similar to medical knowledge saved in HealthLake with knowledge saved in operational databases similar to a affected person relationship administration system, along with knowledge produced from wearable gadgets in close to real-time. Accessing all this knowledge allows healthcare organizations to kind a holistic view of sufferers, enhance care coordination throughout a number of organizations, and supply extremely personalised take care of every particular person.

The next diagram depicts the high-level resolution we construct to realize these outcomes.

Deploy the answer

You should utilize the next AWS CloudFormation template to deploy the answer elements:

This stack creates the next assets and vital permissions to combine the companies:

AWS Answer setup

AWS HealthLake

AWS HealthLake allows organizations within the well being trade to securely retailer, rework, transact, and analyze well being knowledge. It shops knowledge in HL7 FHIR format, which is an interoperability customary designed for fast and environment friendly alternate of well being knowledge. Once you create a HealthLake knowledge retailer, a Quick Healthcare Interoperability Assets (FHIR) knowledge repository is made accessible by way of a RESTful API endpoint. Concurrently and as a part of AWS HealthLake managed service, the nested JSON FHIR knowledge undergoes an ETL course of and is saved in Apache Iceberg open desk format in Amazon S3.

To create an AWS HealthLake knowledge retailer, check with Getting began with AWS HealthLake. Be certain that to pick the choice Preload pattern knowledge when creating your knowledge retailer.

In real-world eventualities and whenever you use AWS HealthLake in manufacturing environments, you don’t must load pattern knowledge into your AWS HealthLake knowledge retailer. As a substitute, you should use FHIR REST API operations to handle and search assets in your AWS HealthLake knowledge retailer.

We use two tables from the pattern knowledge saved in HealthLake: affected person and allergyintolerance.

Question AWS HealthLake tables with Redshift Serverless

Amazon Redshift is the info warehousing service accessible on the AWS Cloud that gives as much as six occasions higher price-performance than another cloud knowledge warehouses out there, with a totally managed, AI-powered, massively parallel processing (MPP) knowledge warehouse constructed for efficiency, scale, and availability. With steady improvements added to Amazon Redshift, it’s now greater than only a knowledge warehouse. It allows organizations of various sizes and in several industries to entry all the info they’ve of their AWS environments and analyze it from one single location with a set of options underneath the zero-ETL umbrella. Amazon Redshift integrates with AWS HealthLake and knowledge lakes by Redshift Spectrum and Amazon S3 auto-copy options, enabling you to question knowledge immediately from recordsdata on Amazon S3.

Question AWS HealthLake knowledge with Amazon Redshift

Amazon Redshift makes it easy to question the info saved in S3-based knowledge lakes with automated mounting of an AWS Glue Information Catalog within the Redshift question editor v2. This implies you now not should create an exterior schema in Amazon Redshift to make use of the info lake tables cataloged within the Information Catalog. To get began with this characteristic, see Querying the AWS Glue Information Catalog. After it’s arrange and also you’re related to the Redshift question editor v2, full the next steps:

1. Validate that your tables are seen within the question editor V2. The Information Catalog objects are listed underneath the awsdatacatalog database.
   

FHIR knowledge saved in AWS HealthLake is very nested. To study easy methods to un-nest semi-structured knowledge with Amazon Redshift, see Tutorial: Querying nested knowledge with Amazon Redshift Spectrum.

2. Use the next question to un-nest the allergyintolerance and affected person tables, be a part of them collectively, and get affected person particulars and their allergic reactions:

   WITH patient_allergy AS 
   (
       SELECT
           resourcetype, 
           c AS allery_category,
           a."affected person"."reference",
           SUBSTRING(a."affected person"."reference", 9, LEN(a."affected person"."reference")) AS patient_id,
           a.recordeddate AS allergy_record_date,
           NVL(cd."code", 'NA') AS allergy_code,
           NVL(cd.show, 'NA') AS allergy_description
   
       FROM "awsdatacatalog"."datastore_01_179674d36391d68926a8d74c12599306_healthlake_view"."allergyintolerance" a
               LEFT JOIN a.class c ON TRUE
               LEFT JOIN a.response r ON TRUE
               LEFT JOIN r.manifestation m ON TRUE
               LEFT JOIN m.coding cd ON TRUE
   ), patinet_info AS
   (
       SELECT id,
               gender,
               g as given_name,
               n.household as family_name,
               pr as prefix
   
       FROM "awsdatacatalog"."datastore_01_179674d36391d68926a8d74c12599306_healthlake_view"."affected person" p
               LEFT JOIN p.title n ON TRUE
               LEFT JOIN n.given g ON TRUE
               LEFT JOIN n.prefix pr ON TRUE
   )
   SELECT DISTINCT p.id, 
           p.gender, 
           p.prefix,
           p.given_name,
           p.family_name,
           pa.allery_category,
           pa.allergy_code,
           pa.allergy_description
   from patient_allergy pa
       JOIN patinet_info p
           ON pa.patient_id = p.id
   ORDER BY p.id, pa.allergy_code
   ;
   

To get rid of the necessity for Amazon Redshift to un-nest knowledge each time a question is run, you possibly can create a materialized view to carry un-nested and flattened knowledge. Materialized views are an efficient mechanism to cope with complicated and repeating queries. They comprise a precomputed end result set, based mostly on a SQL question over a number of base tables. You’ll be able to difficulty SELECT statements to question a materialized view, in the identical manner which you can question different tables or views within the database.

3. Use the next SQL to create a materialized view. You utilize it later to construct a whole view of sufferers:

   CREATE MATERIALIZED VIEW patient_allergy_info AUTO REFRESH YES AS
   WITH patient_allergy AS 
   (
       SELECT
           resourcetype, 
           c AS allery_category,
           a."affected person"."reference",
           SUBSTRING(a."affected person"."reference", 9, LEN(a."affected person"."reference")) AS patient_id,
           a.recordeddate AS allergy_record_date,
           NVL(cd."code", 'NA') AS allergy_code,
           NVL(cd.show, 'NA') AS allergy_description
   
       FROM
           "awsdatacatalog"."datastore_01_179674d36391d68926a8d74c12599306_healthlake_view"."allergyintolerance" a
               LEFT JOIN a.class c ON TRUE
               LEFT JOIN a.response r ON TRUE
               LEFT JOIN r.manifestation m ON TRUE
               LEFT JOIN m.coding cd ON TRUE
   ), patinet_info AS
   (
       SELECT id,
               gender,
               g as given_name,
               n.household as family_name,
               pr as prefix
   
       FROM "awsdatacatalog"."datastore_01_179674d36391d68926a8d74c12599306_healthlake_view"."affected person" p
               LEFT JOIN p.title n ON TRUE
               LEFT JOIN n.given g ON TRUE
               LEFT JOIN n.prefix pr ON TRUE
   )
   SELECT DISTINCT p.id, 
           p.gender, 
           p.prefix,
           p.given_name,
           p.family_name,
           pa.allery_category,
           pa.allergy_code,
           pa.allergy_description
   from patient_allergy pa
       JOIN patinet_info p
           ON pa.patient_id = p.id
   ORDER BY p.id, pa.allergy_code
   ;
   

You’ve got confirmed you possibly can question knowledge in AWS HealthLake by way of Amazon Redshift. Subsequent, you arrange zero-ETL integration between Amazon Redshift and Amazon Aurora MySQL.

Arrange zero-ETL integration between Amazon Aurora MySQL and Redshift Serverless

Purposes similar to front-desk software program, that are used to schedule appointments and register new sufferers, retailer knowledge in OLTP databases similar to Aurora. To get knowledge out of OLTP databases and have them prepared for analytics use instances, knowledge groups may need to spend a substantial period of time to construct, take a look at, and deploy ETL jobs which are complicated to keep up and scale.

With the Amazon Redshift zero-ETL integration with Amazon Aurora MySQL, you possibly can run analytics on the info saved in OLTP databases and mix them with the remainder of the info in Amazon Redshift and AWS HealthLake in close to actual time. Within the subsequent steps on this part, we hook up with a MySQL database and arrange zero-ETL integration with Amazon Redshift.

Hook up with an Aurora MySQL database and arrange knowledge

Hook up with your Aurora MySQL database utilizing your editor of alternative utilizing AdminUsername and AdminPassword that you just entered when operating the CloudFormation stack. (For simplicity, it’s the identical for Amazon Redshift and Aurora.)

Once you’re related to your database, full the next steps:

1. Create a brand new database by operating the next command:

   CREATE DATABASE front_desk_app_db;

2. Create a brand new desk. This desk simulates storing affected person data as they go to clinics and different healthcare facilities. For simplicity and to exhibit particular capabilities, we assume that affected person IDs are the identical in AWS HealthLake and the front-of-office utility. In real-world eventualities, this could be a hashed model of a nationwide well being care quantity:

   CREATE TABLE patient_appointment ( 
         patient_id varchar(250), 
         gender varchar(1), 
         date_of_birth date, 
         appointment_datetime datetime, 
         phone_number varchar(15), 
         PRIMARY KEY (patient_id, appointment_datetime) 
   );

Having a major key within the desk is necessary for zero-ETL integration to work.

3. Insert new information into the supply desk within the Aurora MySQL database. To exhibit the required functionalities, ensure the patient_id of the pattern information inserted into the MySQL database match those in AWS HealthLake. Substitute [patient_id_1] and [patient_id_2] within the following question with those from the Redshift question you ran beforehand (the question that joined allergyintolerance and affected person):

   INSERT INTO front_desk_app_db.patient_appointment (patient_id, gender, date_of_birth, appointment_datetime, phone_number)
   
   VALUES([PATIENT_ID_1], 'F', '1988-7-04', '2023-12-19 10:15:00', '0401401401'),
   ([PATIENT_ID_1], 'F', '1988-7-04', '2023-09-19 11:00:00', '0401401401'),
   ([PATIENT_ID_1], 'F', '1988-7-04', '2023-06-06 14:30:00', '0401401401'),
   ([PATIENT_ID_2], 'F', '1972-11-14', '2023-12-19 08:15:00', '0401401402'),
   ([PATIENT_ID_2], 'F', '1972-11-14', '2023-01-09 12:15:00', '0401401402');

Now that your supply desk is populated with pattern information, you possibly can arrange zero-ETL and have knowledge ingested into Amazon Redshift.

Arrange zero-ETL integration between Amazon Aurora MySQL and Amazon Redshift

Full the next steps to create your zero-ETL integration:

1. On the Amazon RDS console, select Databases within the navigation pane.
2. Select the DB identifier of your cluster (not the occasion).
3. On the Zero-ETL Integration tab, select Create zero-ETL integration.
4. Comply with the steps to create your integration.

Create a Redshift database from the mixing

Subsequent, you create a goal database from the mixing. You are able to do this by operating a few easy SQL instructions on Amazon Redshift. Log in to the question editor V2 and run the next instructions:

1. Get the mixing ID of the zero-ETL you arrange between your supply database and Amazon Redshift:

   SELECT * FROM svv_integration;

   

2. Create a database utilizing the mixing ID:

   CREATE DATABASE ztl_demo FROM INTEGRATION '[INTEGRATION_ID ';

   

3. Query the database and validate that a new table is created and populated with data from your source MySQL database:

   SELECT * FROM ztl_demo.front_desk_app_db.patient_appointment;

It might take a few seconds for the first set of records to appear in Amazon Redshift.

This shows that the integration is working as expected. To validate it further, you can insert a new record in your Aurora MySQL database, and it will be available in Amazon Redshift for querying in near real time within a few seconds.

Set up streaming ingestion for Amazon Redshift

Another aspect of zero-ETL on AWS, for real-time and streaming data, is realized through Amazon Redshift Streaming Ingestion. It provides low-latency, high-speed ingestion of streaming data from Kinesis Data Streams and Amazon MSK. It lowers the effort required to have data ready for analytics workloads, lowers the cost of running such workloads on the cloud, and decreases the operational burden of maintaining the solution.

In the context of healthcare, understanding an individual’s exercise and movement patterns can help with overall health assessment and better treatment planning. In this section, you send simulated data from wearable devices to Kinesis Data Streams and integrate it with the rest of the data you already have access to from your Redshift Serverless data warehouse.

For step-by-step instructions, refer to Real-time analytics with Amazon Redshift streaming ingestion. Note the following steps when you set up streaming ingestion for Amazon Redshift:

1. Select wearables_stream and use the following template when sending data to Amazon Kinesis Data Streams via Kinesis Data Generator, to simulate data generated by wearable devices. Replace [PATIENT_ID_1] and [PATIENT_ID_2] with the affected person IDs you earlier when inserting new information into your Aurora MySQL desk:

   
      "patient_id": "random.arrayElement(["[PATIENT_ID_1]"," [PATIENT_ID_2]"])",
      "steps_increment": "random.arrayElement(
         [0,1]
      )",
      "heart_rate": random.quantity( 
         
            "min":45,
            "max":120
      )
   

2. Create an exterior schema known as from_kds by operating the next question and changing [IAM_ROLE_ARN] with the ARN of the function created by the CloudFormation stack (Patient360BlogRole):

   CREATE EXTERNAL SCHEMA from_kds
   FROM KINESIS
   IAM_ROLE '[IAM_ROLE_ARN]';

3. Use the next SQL when making a materialized view to eat knowledge from the stream:

   CREATE MATERIALIZED VIEW patient_wearable_data AUTO REFRESH YES AS 
   SELECT approximate_arrival_timestamp, 
         JSON_PARSE(kinesis_data) as Information FROM from_kds."wearables_stream" 
   WHERE CAN_JSON_PARSE(kinesis_data);

4. To validate that streaming ingestion works as anticipated, refresh the materialized view to get the info you already despatched to the info stream and question the desk to ensure knowledge has landed in Amazon Redshift:

   REFRESH MATERIALIZED VIEW patient_wearable_data;
   
   SELECT *
   FROM patient_wearable_data
   ORDER BY approximate_arrival_timestamp DESC;

Question and analyze affected person wearable knowledge

The leads to the info column of the previous question are in JSON format. Amazon Redshift makes it easy to work with semi-structured knowledge in JSON format. It makes use of PartiQL language to supply SQL-compatible entry to relational, semi-structured, and nested knowledge. Use the next question to flatten knowledge:

SELECT knowledge."patient_id"::varchar AS patient_id,       
      knowledge."steps_increment"::integer as steps_increment,       
      knowledge."heart_rate"::integer as heart_rate, 
      approximate_arrival_timestamp 
FROM patient_wearable_data 
ORDER BY approximate_arrival_timestamp DESC;

The end result seems like the next screenshot.

Now that you understand how to flatten JSON knowledge, you possibly can analyze it additional. Use the next question to get the variety of minutes a affected person has been bodily lively per day, based mostly on their coronary heart fee (better than 80):

WITH patient_wearble_flattened AS
(
   SELECT knowledge."patient_id"::varchar AS patient_id,
      knowledge."steps_increment"::integer as steps_increment,
      knowledge."heart_rate"::integer as heart_rate,
      approximate_arrival_timestamp,
      DATE(approximate_arrival_timestamp) AS date_received,
      extract(hour from approximate_arrival_timestamp) AS    hour_received,
      extract(minute from approximate_arrival_timestamp) AS minute_received
   FROM patient_wearable_data
), patient_active_minutes AS
(
   SELECT patient_id,
      date_received,
      hour_received,
      minute_received,
      avg(heart_rate) AS heart_rate
   FROM patient_wearble_flattened
   GROUP BY patient_id,
      date_received,
      hour_received,
      minute_received
   HAVING avg(heart_rate) > 80
)
SELECT patient_id,
      date_received,
      COUNT(heart_rate) AS active_minutes_count
FROM patient_active_minutes
GROUP BY patient_id,
      date_received
ORDER BY patient_id,
      date_received;

Create a whole affected person 360

Now that you’ll be able to question all affected person knowledge with Redshift Serverless, you possibly can mix the three datasets you used on this put up and kind a complete affected person 360 view with the next question:

WITH patient_appointment_info AS
(
      SELECT "patient_id",
         "gender",
         "date_of_birth",
         "appointment_datetime",
         "phone_number"
      FROM ztl_demo.front_desk_app_db.patient_appointment
),
patient_wearble_flattened AS
(
      SELECT knowledge."patient_id"::varchar AS patient_id,
         knowledge."steps_increment"::integer as steps_increment,
         knowledge."heart_rate"::integer as heart_rate,
         approximate_arrival_timestamp,
         DATE(approximate_arrival_timestamp) AS date_received,
         extract(hour from approximate_arrival_timestamp) AS hour_received,
         extract(minute from approximate_arrival_timestamp) AS minute_received
      FROM patient_wearable_data
), patient_active_minutes AS
(
      SELECT patient_id,
         date_received,
         hour_received,
         minute_received,
         avg(heart_rate) AS heart_rate
      FROM patient_wearble_flattened
      GROUP BY patient_id,
         date_received,
         hour_received,
         minute_received
         HAVING avg(heart_rate) > 80
), patient_active_minutes_count AS
(
      SELECT patient_id,
         date_received,
         COUNT(heart_rate) AS active_minutes_count
      FROM patient_active_minutes
      GROUP BY patient_id,
         date_received
)
SELECT pai.patient_id,
      pai.gender,
      pai.prefix,
      pai.given_name,
      pai.family_name,
      pai.allery_category,
      pai.allergy_code,
      pai.allergy_description,
      ppi.date_of_birth,
      ppi.appointment_datetime,
      ppi.phone_number,
      pamc.date_received,
      pamc.active_minutes_count
FROM patient_allergy_info pai
      LEFT JOIN patient_active_minutes_count pamc
            ON pai.patient_id = pamc.patient_id
      LEFT JOIN patient_appointment_info ppi
            ON pai.patient_id = ppi.patient_id
GROUP BY pai.patient_id,
      pai.gender,
      pai.prefix,
      pai.given_name,
      pai.family_name,
      pai.allery_category,
      pai.allergy_code,
      pai.allergy_description,
      ppi.date_of_birth,
      ppi.appointment_datetime,
      ppi.phone_number,
      pamc.date_received,
      pamc.active_minutes_count
ORDER BY pai.patient_id,
      pai.gender,
      pai.prefix,
      pai.given_name,
      pai.family_name,
      pai.allery_category,
      pai.allergy_code,
      pai.allergy_description,
      ppi.date_of_birth DESC,
      ppi.appointment_datetime DESC,
      ppi.phone_number DESC,
      pamc.date_received,
      pamc.active_minutes_count

You should utilize the answer and queries used right here to increase the datasets utilized in your evaluation. For instance, you possibly can embody different tables from AWS HealthLake as wanted.

Clear up

To scrub up assets you created, full the next steps:

1. Delete the zero-ETL integration between Amazon RDS and Amazon Redshift.
2. Delete the CloudFormation stack.
3. Delete AWS HealthLake knowledge retailer

Conclusion

Forming a complete 360 view of sufferers by integrating knowledge from varied completely different sources provides quite a few advantages for organizations working within the healthcare trade. It allows healthcare suppliers to realize a holistic understanding of a affected person’s medical journey, enhances medical decision-making, and permits for extra correct analysis and tailor-made therapy plans. With zero-ETL options for knowledge integration on AWS, it’s easy to construct a view of sufferers securely, cost-effectively, and with minimal effort.

You’ll be able to then use visualization instruments similar to Amazon QuickSight to construct dashboards or use Amazon Redshift ML to allow knowledge analysts and database builders to coach machine studying (ML) fashions with the info built-in by Amazon Redshift zero-ETL. The result’s a set of ML fashions which are educated with a broader view into sufferers, their medical historical past, and their way of life, and due to this fact allow you make extra correct predictions about their upcoming well being wants.

In regards to the Authors

Saeed Barghi is a Sr. Analytics Specialist Options Architect specializing in architecting enterprise knowledge platforms. He has intensive expertise within the fields of information warehousing, knowledge engineering, knowledge lakes, and AI/ML. Based mostly in Melbourne, Australia, Saeed works with public sector clients in Australia and New Zealand.

Satesh Sonti is a Sr. Analytics Specialist Options Architect based mostly out of Atlanta, specialised in constructing enterprise knowledge platforms, knowledge warehousing, and analytics options. He has over 17 years of expertise in constructing knowledge belongings and main complicated knowledge platform packages for banking and insurance coverage shoppers throughout the globe.