Query Data

info

• Follow the steps mentioned below to query data in Amorphic
• Total time taken for this task: 3 Minutes.
• Pre-requisites: Run Healthlake Import Job and Create AirQuality Dataset

Note

Update the Athena Workgroup to Amazon Athena Engine v3 to query Amorphic datasets

• Click on the gear icon to update the workgroup

• Select Amazon Athena Engine v3 from the workgroup dropdown and Click Update

Query AirQuality Dataset

• Navigate to Amorphic Query Engine

• To generate a sample query, Right Click on the Dataset on the left panel and Select Generate Sample Query

• Click Run Query to execute the query

Query Healthlake Datasets

• Generate a sample query on the encounter dataset and Click Run Query to check the results

Additional Sample Queries

Sample Healthalke Dataset Query

Note

Update the Healthlake domain name in the query

SELECT COUNT(start_enc) AS totalAsthmaEncounters, weekNumber
      FROM
        (SELECT en.period.start AS start_enc, FLOOR(day_of_year(from_iso8601_timestamp(en.period.start))/7) AS weekNumber
           FROM <Healthlake_domain>.encounter  AS en
          WHERE en.reasonCode[1].coding[1].code IN ('233678006','195967001')
            AND en.period.start > '2021-01-01' AND en.period.start < '2021-12-31')
       GROUP BY weekNumber
       ORDER BY weekNumber

Sample AirQuality Dataset Query

Note

Update the domain and dataset name in the query

SELECT
  SUM(aqi) AS aqiWeekTotal,
  weekNumber
FROM
  (
    SELECT
      "daily_aqi_value" AS aqi,
      CAST(
        FLOOR(DAY_OF_YEAR (DATE_PARSE (date, '%m/%d/%y')) / 7.0) AS INT
      ) AS weekNumber
    FROM
      weather<username>.airquality_<username>
  )
GROUP BY
  weekNumber
ORDER BY
  weekNumber