Enhance healthcare providers by means of affected person 360: A zero-ETL method to allow close to real-time information analytics

Healthcare suppliers have a chance to enhance the affected person expertise by gathering and analyzing broader and extra numerous datasets. This consists of affected person medical historical past, allergic reactions, immunizations, household illness historical past, and people’ life-style information corresponding to exercise habits. Gaining access to these datasets and forming a 360-degree view of sufferers permits healthcare suppliers corresponding 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 determine patterns, tendencies, potential gaps in care, and adherence to care plans. They will then use the results of their evaluation to grasp a affected person’s well being standing, remedy historical past, and previous or upcoming physician consultations to make extra knowledgeable selections, streamline the declare administration course of, and enhance operational outcomes. Attaining this may also enhance common public well being by means of higher and extra well timed interventions, determine well being dangers by means of predictive analytics, and speed up the analysis and growth course of.

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

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

Answer overview

Organizations within the healthcare business are at present spending a big quantity of money and time on constructing advanced ETL pipelines for information motion and integration. This implies information will likely be replicated throughout a number of information shops by way of bespoke and in some circumstances hand-written ETL jobs, leading to information inconsistency, latency, and potential safety and privateness breaches.

With assist 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 information from one place. This implies you may seamlessly mix data corresponding to scientific information saved in HealthLake with information saved in operational databases corresponding to a affected person relationship administration system, along with information produced from wearable gadgets in close to real-time. Gaining access to all this information permits healthcare organizations to kind a holistic view of sufferers, enhance care coordination throughout a number of organizations, and supply extremely personalised look after every particular person.

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

Deploy the answer

You need to use the next AWS CloudFormation template to deploy the answer parts:

This stack creates the next sources and needed permissions to combine the providers:

AWS Answer setup

AWS HealthLake

AWS HealthLake permits organizations within the well being business to securely retailer, remodel, transact, and analyze well being information. It shops information in HL7 FHIR format, which is an interoperability commonplace designed for fast and environment friendly trade of well being information. While you create a HealthLake information retailer, a Quick Healthcare Interoperability Assets (FHIR) information 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 information undergoes an ETL course of and is saved in Apache Iceberg open desk format in Amazon S3.

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

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

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

Question AWS HealthLake tables with Redshift Serverless

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

Question AWS HealthLake information with Amazon Redshift

Amazon Redshift makes it easy to question the information saved in S3-based information lakes with computerized mounting of an AWS Glue Knowledge Catalog within the Redshift question editor v2. This implies you not must create an exterior schema in Amazon Redshift to make use of the information lake tables cataloged within the Knowledge Catalog. To get began with this characteristic, see Querying the AWS Glue Knowledge 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 Knowledge Catalog objects are listed below the awsdatacatalog database.
   

FHIR information saved in AWS HealthLake is extremely nested. To find out about tips on how to un-nest semi-structured information with Amazon Redshift, see Tutorial: Querying nested information 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.identify 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 remove the necessity for Amazon Redshift to un-nest information each time a question is run, you may create a materialized view to carry un-nested and flattened information. Materialized views are an efficient mechanism to cope with advanced and repeating queries. They include a precomputed end result set, primarily based on a SQL question over a number of base tables. You may challenge SELECT statements to question a materialized view, in the identical means which you could 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.identify 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 have got confirmed you may question information 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

Functions corresponding to front-desk software program, that are used to schedule appointments and register new sufferers, retailer information in OLTP databases corresponding to Aurora. To get information out of OLTP databases and have them prepared for analytics use circumstances, information groups may need to spend a substantial period of time to construct, check, and deploy ETL jobs which can be advanced to take care of and scale.

With the Amazon Redshift zero-ETL integration with Amazon Aurora MySQL, you may run analytics on the information saved in OLTP databases and mix them with the remainder of the information 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 information

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.)

While 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 display 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 obligatory for zero-ETL integration to work.

3. Insert new data into the supply desk within the Aurora MySQL database. To display the required functionalities, ensure that the patient_id of the pattern data 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 data, you may arrange zero-ETL and have information 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. Observe the steps to create your integration.

Create a Redshift database from the combination

Subsequent, you create a goal database from the combination. 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 combination ID of the zero-ETL you arrange between your supply database and Amazon Redshift:

   SELECT * FROM svv_integration;

   

2. Create a database utilizing the combination 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 data 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 devour information from the stream:

   CREATE MATERIALIZED VIEW patient_wearable_data AUTO REFRESH YES AS 
   SELECT approximate_arrival_timestamp, 
         JSON_PARSE(kinesis_data) as Knowledge 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 information you already despatched to the information stream and question the desk to verify information 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 information

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

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

The end result seems to be like the next screenshot.

Now that you know the way to flatten JSON information, you may analyze it additional. Use the next question to get the variety of minutes a affected person has been bodily energetic per day, primarily based on their coronary heart charge (better than 80):

WITH patient_wearble_flattened AS
(
   SELECT information."patient_id"::varchar AS patient_id,
      information."steps_increment"::integer as steps_increment,
      information."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 information with Redshift Serverless, you may mix the three datasets you used on this publish 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 information."patient_id"::varchar AS patient_id,
         information."steps_increment"::integer as steps_increment,
         information."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 need to use the answer and queries used right here to develop the datasets utilized in your evaluation. For instance, you may embody different tables from AWS HealthLake as wanted.

Clear up

To wash up sources 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 information retailer

Conclusion

Forming a complete 360 view of sufferers by integrating information from numerous totally different sources presents quite a few advantages for organizations working within the healthcare business. It permits healthcare suppliers to achieve a holistic understanding of a affected person’s medical journey, enhances scientific decision-making, and permits for extra correct analysis and tailor-made remedy plans. With zero-ETL options for information integration on AWS, it’s easy to construct a view of sufferers securely, cost-effectively, and with minimal effort.

You may then use visualization instruments corresponding to Amazon QuickSight to construct dashboards or use Amazon Redshift ML to allow information analysts and database builders to coach machine studying (ML) fashions with the information built-in by means of Amazon Redshift zero-ETL. The result’s a set of ML fashions which can be educated with a broader view into sufferers, their medical historical past, and their life-style, 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 information platforms. He has intensive expertise within the fields of information warehousing, information engineering, information lakes, and AI/ML. Primarily based in Melbourne, Australia, Saeed works with public sector clients in Australia and New Zealand.

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