使用亚马逊 Athena 和亚马逊分析和可视化嵌套的 JSON 数据 QuickSight

由 Anoop Singh (AWS) 创建

摘要

此模式说明了如何使用 Amazon Athena 将嵌套的、JSON 格式的数据结构转换为表格视图，然后在 Amazon 中可视化数据。 QuickSight

您可以将 JSON 格式的数据用于来自操作系统的 API 驱动的数据源，以创建数据产品。这些数据还可以帮助您更好地了解客户及其与产品的互动，因此您可以量身定制用户体验并预测结果。

先决条件和限制

先决条件

活跃的 AWS 账户
表示嵌套数据结构的 JSON 文件（此模式提供了示例文件）

限制:

JSON 功能与 Athena 中现有的面向 SQL 的函数很好地集成。但是，它们不兼容 ANSI SQL，并且 JSON 文件应将每条记录放在单独的行中。您可能需要使用 Athena 中的 ignore.malformed.json 属性来指示是否应将格式错误的 JSON 记录转换为空字符或生成错误。有关更多信息，请参阅 Athena 文档中的读取 JSON 数据的最佳实践。
此模式仅考虑简单且少量 JSON 格式的数据。如果您想大规模使用这些概念，可以考虑应用数据分区并将数据整合到更大的文件中。

架构

下图显示了此模式的架构和工作流程。嵌套的数据结构以 JSON 格式存储在亚马逊简单存储服务 (Amazon S3) 中。在 Athena 中，JSON 数据映射到 Athena 数据结构。然后，您可以创建一个视图来分析数据，并在中可视化数据结构 QuickSight。

工具

Amazon Web Services

Amazon Simple Storage Service(Amazon S3) 是一项基于云的对象存储服务，可帮助您存储、保护和检索任意数量的数据。此模式使用 Amazon S3 来存储 JSON 文件。
Amazon Athena 是一种交互式查询服务，可帮助您使用标准 SQL 直接在 Amazon S3 中分析数据。此模式使用 Athena 来查询和转换 JSON 数据。只需在中执行一些操作 AWS Management Console，您就可以将 Athena 指向您在 Amazon S3 中的数据，然后使用标准 SQL 来运行一次性查询。Athena 是无服务器的，因此无需设置或管理基础架构，您只需为运行的查询付费。Athena 会自动扩展并行运行查询，因此即使使用大型数据集和复杂查询，也能快速获得结果。
Amazon QuickSight 是一项云规模的商业智能 (BI) 服务，可帮助您在单个控制面板上可视化、分析和报告数据。 QuickSight 允许您轻松创建和发布包含机器学习 (ML) 见解的交互式仪表板。您可以从任何设备访问这些仪表板，并将其嵌入到您的应用程序、门户和网站中。

示例代码

以下 JSON 文件提供了可以在此模式中使用的嵌套数据结构。


{
  "symbol": "AAPL",
  "financials": [
      "reportDate": "2017-03-31",
      "grossProfit": 20591000000,
      "costOfRevenue": 32305000000,
      "operatingRevenue": 52896000000,
      "totalRevenue": 52896000000,
      "operatingIncome": 14097000000,
      "netIncome": 11029000000,
      "researchAndDevelopment": 2776000000,
      "operatingExpense": 6494000000,
      "currentAssets": 101990000000,
      "totalAssets": 334532000000,
      "totalLiabilities": 200450000000,
      "currentCash": 15157000000,
      "currentDebt": 13991000000,
      "totalCash": 67101000000,
      "totalDebt": 98522000000,
      "shareholderEquity": 134082000000,
      "cashChange": -1214000000,
      "cashFlow": 12523000000,
      "operatingGainsLosses": null
}

操作说明

Task	描述	所需技能
创建 S3 存储桶。	要创建用于存储 JSON 文件的存储桶，请登录并打开 Amazon S3 控制台，然后选择创建存储桶。 AWS Management Console有关更多信息，请参阅 Amazon S3 文档中的创建存储桶。	系统管理员
添加嵌套的 JSON 数据。	将您的 JSON 文件上传到 S3 存储桶。有关 JSON 文件的示例，请参阅上一节。有关说明，请参阅 Amazon S3 文档中的上传对象。	系统管理员

Task	描述	所需技能
创建用于映射 JSON 数据的表。	打开 Athena 控制台。按照 A thena 文档中的说明创建数据库。从 “ 数据库 ” 菜单中，选择您创建的数据库。在查询编辑器中，输入如下 `CREATE TABLE` 语句： `CREATE EXTERNAL TABLE financials_json ( symbol string, financials array< struct<reportdate: string, grossprofit: bigint, totalrevenue: bigint, totalcash: bigint, totaldebt: bigint, researchanddevelopment: bigint>> ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION 's3://s3bucket-for-athena/'` 其中， `LOCATION` 指定包含 JSON 文件的 S3 存储桶的位置。选择 Run 创建表。有关创建表的更多信息，请参阅 Athena 文档。	开发人员
创建用于数据分析的视图。	打开 Athena 控制台。按照 A thena 文档中的说明创建数据库。从 “ 数据库 ” 菜单中，选择您创建的数据库。在查询编辑器中，输入如下 `CREATE VIEW` 语句： `CREATE OR REPLACE VIEW financial_json_view AS SELECT symbol, financials[1].reportdate one_report_date, -- indexes start with 1 financials[1].totalrevenue one_total_revenue,`