本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。

# 使用 SQL 查询 HealthLake 数据
<a name="integrating-athena-query-sql"></a>

当你将 FHIR 数据导入 HealthLake 数据存储时，嵌套的 JSON FHIR 数据会同时经过 ETL 处理，并以 Apache Iceberg 开放表格式存储在 Amazon S3 中。您的 HealthLake 数据存储中的每种 FHIR 资源类型都将转换为一个表，可以在该表中使用 Amazon Athena 进行查询。可以使用基于 SQL 的查询对表进行单独查询，也可以按组进行查询。由于数据存储结构的原因，您的数据将作为多种不同的数据类型导入 Athena。要详细了解如何创建可访问这些数据类型的 SQL 查询，请参阅 *Amazon Athen* a 用户指南中的[具有复杂类型和嵌套结构的查询数组](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 数据操纵语言 (DML) 查询语句基于 Trino。*Athena并不支持Trino的所有功能，并且存在显著差异。*要了解更多信息，请参阅《A *mazon Athena* 用户指南》中的 [DML 查询、函数和运算符](https://docs.aws.amazon.com/athena/latest/ug/functions-operators-reference-section.html)。

此外，Athena 支持您在创建数据存储查询时可能会遇到的 HealthLake 多种数据类型。*要了解有关 Athena 中数据类型的更多信息，[请参阅亚马逊 Athena 用户指南中的亚马逊 Athena 中的数据](https://docs.aws.amazon.com/athena/latest/ug/data-types.html)类型。*

*要详细了解 SQL 查询在 Athena 中的工作原理，[请参阅《亚马逊 Athena 用户指南》中的 Amazon Athena 的 SQL 参](https://docs.aws.amazon.com/athena/latest/ug/ddl-sql-reference.html)考。*

每个选项卡都显示了如何使用 Athena 搜索指定资源类型和关联元素的示例。

------
#### [ Element: Extension ]

该元素`extension`用于在数据存储中创建自定义字段。

此示例向您展示如何访问在`Patient`资源类型中找到的`extension`元素的功能。

将 HealthLake 数据存储导入 Athena 时，对资源类型的元素的解析会有所不同。由于 `element` is 的结构变量，因此无法在架构中对其进行完全指定。为了处理这种可变性，数组中的元素作为字符串传递。

在的表描述中`Patient`，您可以看到`extension`描述为的元素`array<string>`，这意味着您可以使用索引值访问数组的元素。但是，要访问字符串的元素，必须使用`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 的查询，请参阅*亚马逊 Athena* 用户指南中的[从 JSON 中提取数据](https://docs.aws.amazon.com/athena/latest/ug/extracting-data-from-JSON.html)。

------
#### [ Element: birthDate (Age) ]

在 FHIR 中，年龄*不*是 “患者” 资源类型的要素。以下是根据年龄进行筛选的两个搜索示例。

因为年龄不是一个元素，所以我们使用 SQL 查询。`birthDate`要查看元素是如何被引入 FHIR 的，请在表格**和视图下搜索表**名。你可以看到它的类型是**字符串**。

**示例 1**：计算年龄值

在这个示例 SQL 查询中，我们使用内置的 SQL 工具`year`来提取这些组件。`current_date`然后，我们减去它们以返回患者的实际年龄，列名为`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) 会根据`Condition`资源类型中的详细信息生成*新* DocumentReference 资源。生成新资源时， HealthLake 将标签附加`SYSTEM_GENERATED`到`meta`元素。此示例 SQL 查询演示了如何搜索条件表并返回已删除`SYSTEM_GENERATED`结果的结果。

要了解有关 HealthLake集成自然语言处理 (NLP) 的更多信息，请参阅[集成的自然语言处理 (NLP) 用于 HealthLake](integrating-nlp.md)。

```
SELECT *
FROM condition
WHERE meta.tag[1] is NULL
```

您也可以在指定的字符串元素中进行搜索以进一步筛选查询。该`modifierextension`元素包含有关使用哪个`DocumentReference`资源生成一组条件的详细信息。同样，您必须使用`json_extract`来访问作为字符串引入 Athena 的嵌套 JSON 元素。

此示例 SQL 查询演示了如何搜索根据特定内容生成的所有内容`DocumentReference`。`Condition`用于`CAST`将 JSON 元素设置为字符串，以便您可以`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 ]

资源类型 “观察” 存储有关患者、设备或其他受试者的测量结果和简单断言。 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) 会根据MedicationStatement 资源类型中的文档生成新 DocumentReference资源。生成新资源时， 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) 的更多信息，请参阅[集成的自然语言处理 (NLP) 用于 HealthLake](integrating-nlp.md)。

------