本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 使用 SQL 查詢 HealthLake 資料當您將 FHIR 資料匯入 HealthLake 資料存放區時，巢狀 JSON FHIR 資料會同時進行 ETL 程序，並以 Apache Iceberg 開放資料表格式儲存在 Amazon S3 中。HealthLake 資料存放區中的每個 FHIR 資源類型都會轉換為資料表，可使用 Amazon Athena 來查詢。資料表可以個別查詢或使用 SQL 型查詢做為群組。由於資料存放區的結構，您的資料會以多種不同的資料類型匯入 Athena。若要進一步了解如何建立可存取這些資料類型的 SQL 查詢，請參閱《*Amazon Athena 使用者指南*》中的[查詢具有複雜類型和巢狀結構的陣列](https://docs.aws.amazon.com/athena/latest/ug/rows-and-structs.html)。 **注意** 本主題中的所有範例都使用使用 Synthea 建立的虛構資料。若要進一步了解如何建立預先載入 Synthea 資料的資料存放區，請參閱 [建立 HealthLake 資料存放區](managing-data-stores-create.md)。對於資源類型中的每個元素，FHIR 規格會定義基數。元素的基數會定義此元素可顯示多少次的下限和上限。建構 SQL 查詢時，您必須將此納入考量。例如，讓我們看看[資源類型：病患](https://hl7.org/fhir/R4/patient.html)中的一些元素。 + **元素：名稱** FHIR 規格會將基數設定為 `0..*`。元素會擷取為陣列。 ``` [{ id = null, extension = null, use = official, _use = null, text = null, _text = null, family = Wolf938, _family = null, given = [Noel608], _given = null, prefix = null, _prefix = null, suffix = null, _suffix = null, period = null }] ``` 在 Athena 中，若要查看如何擷取資源類型，請在**資料表和檢視**下搜尋它。若要存取此陣列中的元素，您可以使用點表示法。以下是存取 `given`和值的簡單範例`family`。 ``` SELECT name[1].given as FirstName, name[1].family as LastName FROM Patient ``` + **元素：MaritalStatus** FHIR 規格會將基數設定為 `0..1`。此元素會擷取為 JSON。 ``` { id = null, extension = null, coding = [ { id = null, extension = null, system = http: //terminology.hl7.org/CodeSystem/v3-MaritalStatus, _system = null, version = null, _version = null, code = S, _code = null, display = Never Married, _display = null, userSelected = null, _userSelected = null } ], text = Never Married, _text = null } ``` 在 Athena 中，若要查看如何擷取資源類型，請在**資料表和檢視**下搜尋它。若要存取 JSON 中的鍵/值對，您可以使用點表示法。由於這不是陣列，因此不需要陣列索引。以下是存取值的簡單範例`text`。 ``` SELECT maritalstatus.text as MaritalStatus FROM Patient ``` 若要進一步了解如何存取和搜尋 JSON，請參閱 *Athena 使用者指南*中的[查詢 JSON](https://docs.aws.amazon.com//athena/latest/ug/querying-JSON.html)。 Athena Data Manipulation Language (DML) 查詢陳述式是以 Trino 為基礎。Athena 不支援 Trino 的所有功能，而且有*很大*的差異。若要進一步了解，請參閱《*Amazon Athena 使用者指南*》中的 [DML 查詢、函數和運算子](https://docs.aws.amazon.com/athena/latest/ug/functions-operators-reference-section.html)。此外，Athena 支援您在建立 HealthLake 資料存放區查詢時可能遇到的多種資料類型。若要進一步了解 Athena 中的資料類型，請參閱《[Amazon Athena 使用者指南》中的 Amazon Athena 中的資料類型](https://docs.aws.amazon.com/athena/latest/ug/data-types.html)。 *Amazon Athena * 若要進一步了解 SQL 查詢如何在 Athena 中運作，請參閱[《Amazon Athena 使用者指南》中的 Amazon Athena 的 SQL 參考](https://docs.aws.amazon.com/athena/latest/ug/ddl-sql-reference.html)。 *Amazon Athena * 每個索引標籤顯示如何使用 Athena 搜尋指定資源類型和相關聯元素的範例。 ------ #### [ Element: Extension ] 元素`extension`用於在資料存放區中建立自訂欄位。此範例說明如何存取 `Patient` 資源類型中找到的 `extension`元素功能。當您的 HealthLake 資料存放區匯入 Athena 時，資源類型的元素會以不同的方式剖析。由於的結構`element`是可變的，因此無法在結構描述中完整指定。為了處理該變異性，陣列內的元素會以字串形式傳遞。在的資料表描述中`Patient`，您可以看到`extension`描述為的元素`array`，這表示您可以使用索引值來存取陣列的元素。不過，若要存取字串的元素，您必須使用 `json_extract`。以下是在患者資料表中找到的 `extension`元素的單一項目。 ``` [{ "valueString": "Kerry175 Cummerata161", "url": "http://hl7.org/fhir/StructureDefinition/patient-mothersMaidenName" }, { "valueAddress": { "country": "DE", "city": "Hamburg", "state": "Hamburg" }, "url": "http://hl7.org/fhir/StructureDefinition/patient-birthPlace" }, { "valueDecimal": 0.0, "url": "http://synthetichealth.github.io/synthea/disability-adjusted-life-years" }, { "valueDecimal": 5.0, "url": "http://synthetichealth.github.io/synthea/quality-adjusted-life-years" } ] ``` 即使這是有效的 JSON，Athena 仍會將其視為字串。此 SQL 查詢範例示範如何建立包含 `patient-mothersMaidenName`和 `patient-birthPlace`元素的資料表。若要存取這些元素，您需要使用不同的陣列索引和 `json_extract.` ``` SELECT extension[1], json_extract(extension[1], '$.valueString') AS MothersMaidenName, extension[2], json_extract(extension[2], '$.valueAddress.city') AS birthPlace FROM patient ``` 若要進一步了解涉及 JSON 的查詢，請參閱《*Amazon Athena 使用者指南*》中的[從 JSON 擷取資料](https://docs.aws.amazon.com/athena/latest/ug/extracting-data-from-JSON.html)。 ------ #### [ Element: birthDate (Age) ] 年齡*不是* FHIR 中病患資源類型的元素。以下是根據年齡篩選的搜尋的兩個範例。由於年齡不是元素，因此我們會將 `birthDate`用於 SQL 查詢。若要查看元素如何導入 FHIR，請在資料表**和檢視下搜尋資料表**名稱。您可以看到它是類型**字串**。 **範例 1**：計算存留期的值在此範例 SQL 查詢中，我們使用內建的 SQL 工具和 `current_date``year`來擷取這些元件。然後，我們將它們減去，以傳回患者的實際年齡作為稱為的資料欄`age`。 ``` SELECT (year(current_date) - year(date(birthdate))) as age FROM patient ``` **範例 2**：篩選之前出生且`2019-01-01`為的患者`male`。 SQL 查詢說明如何使用 `CAST`函數將`birthDate`元素轉換為類型 `DATE`，以及如何根據 `WHERE`子句中的兩個條件進行篩選。由於元素預設會擷取為類型**字串**，因此我們必須`CAST`將其擷取為類型 `DATE`。然後，您可以使用 `<` 運算子將其與不同的日期進行比較`2019-01-01`。透過使用 `AND`，您可以將第二個條件新增至 `WHERE`子句。 ``` SELECT birthdate FROM patient -- we convert birthdate (varchar) to date > cast that as date too WHERE CAST(birthdate AS DATE) < CAST('2019-01-01' AS DATE) AND gender = 'male' ``` ------ #### [ Resource type: Location ] 此範例顯示搜尋城市名稱為 Attleboro 的位置資源類型內的位置。 ``` SELECT * FROM Location WHERE address.city='ATTLEBORO' LIMIT 10; ``` ------ #### [ Element: Age ] ``` SELECT birthdate FROM patient -- we convert birthdate (varchar) to date > cast that as date too WHERE CAST(birthdate AS DATE) < CAST('2019-01-01' AS DATE) AND gender = 'male' ``` ------ #### [ Resource type: Condition ] 資源類型條件會存放與問題相關的診斷資料，這些問題已上升到關注層級。HealthLake 的整合醫療自然語言處理 (NLP) 會根據 DocumentReference `Condition` 資源類型中找到的詳細資訊產生*新*資源。產生新資源時，HealthLake 會將標籤附加`SYSTEM_GENERATED`至 `meta`元素。此 SQL 查詢範例示範如何搜尋條件資料表並傳回結果，`SYSTEM_GENERATED`其中的結果已移除。若要進一步了解 HealthLake 的整合自然語言處理 (NLP)，請參閱 [HealthLake 的整合式自然語言處理 (NLP)](integrating-nlp.md)。 ``` SELECT * FROM condition WHERE meta.tag[1] is NULL ``` 您也可以在指定的字串元素內搜尋，進一步篩選查詢。`modifierextension` 元素包含有關哪些`DocumentReference`資源用於產生一組條件的詳細資訊。同樣地，您必須使用 `json_extract`來存取做為字串帶入 Athena 的巢狀 JSON 元素。此範例 SQL 查詢示範如何搜尋根據特定 `Condition`產生的所有 `DocumentReference`。使用將 JSON 元素`CAST`設定為字串，以便您可以使用 `LIKE` 進行比較。 ``` SELECT meta.tag[1].display as SystemGenerated, json_extract(modifierextension[4], '$.valueReference.reference') as DocumentReference FROM condition WHERE meta.tag[1].display = 'SYSTEM_GENERATED' AND CAST(json_extract(modifierextension[4], '$.valueReference.reference') as VARCHAR) LIKE '%DocumentReference/67aa0278-8111-40d0-8adc-43055eb9d18d%' ``` ------ #### [ Resource type: Observation ] 資源類型 Observation 會存放對病患、裝置或其他主體所做的測量和簡單聲明。HealthLake 的整合自然語言處理 (NLP) 會根據在`Observation`資源中找到的詳細資訊產生*新*`DocumentReference`資源。此範例 SQL 查詢包含`WHERE meta.tag[1] is NULL`註解，這表示會包含`SYSTEM_GENERATED`結果。 ``` SELECT valueCodeableConcept.coding[1].code FROM Observation WHERE valueCodeableConcept.coding[1].code = '266919005' -- WHERE meta.tag[1] is NULL ``` 此欄已匯入為 [https://iceberg.apache.org/spec/#schemas-and-data-types](https://iceberg.apache.org/spec/#schemas-and-data-types)。因此，您可以使用點符號存取其中的元素。 ------ #### [ Resource type: MedicationStatement ] MedicationStatement 是一種 FHIR 資源類型，可用來存放患者已使用、正在使用或未來將使用之藥物的詳細資訊。HealthLake 的整合醫療自然語言處理 (NLP) 會根據 DocumentReference 資源類型中找到的文件產生新的MedicationStatement」資源。產生新資源時，HealthLake 會將標籤附加`SYSTEM_GENERATED`至 `meta`元素。此範例 SQL 查詢示範如何建立查詢，使用其識別符根據單一病患進行篩選，並尋找 HealthLake 整合 NLP 已新增的資源。 ``` SELECT * FROM medicationstatement WHERE meta.tag[1].display = 'SYSTEM_GENERATED' AND subject.reference = 'Patient/0679b7b7-937d-488a-b48d-6315b8e7003b'; ``` 若要進一步了解 HealthLake 的整合自然語言處理 (NLP)，請參閱 [HealthLake 的整合式自然語言處理 (NLP)](integrating-nlp.md)。 ------