使用Quicksight进行可视化

在本实验中，我们将测试Athena Fedaration查询，并将结果展示在QuickSight

配置Quicksight权限

编辑QuickSight的权限：

image-20231201211455235

Quicksight需要有访问athena-federation-workshop-<account_id> 存储桶的权限：

image-20231201201646277

另外Quicksight的role需要有调用lambda的权限(前面章节创建了几个connector)，这里直接在aws-quicksight-service-role-v0中添加Lambda FullAccess：

image-20231201211416564

添加数据源并展示

在QuickSight中新建一个dataset：

image-20231201203848891

选择Athena类型。

为data source命名为athena-federation-data-source, 点击Create data source:

image-20231201203943862

这里选择使用custom SQL:

image-20231201204015918

输入以下SQL：

SELECT nation,
       o_year,
       sum(amount) AS sum_profit
       FROM 
       (SELECT n_name AS nation,
               year(cast(o_orderdate AS date)) AS o_year,
               "line:l_extendedprice" * (1 - "line:l_discount") - cast(ps_supplycost AS double) * "line:l_quantity" AS amount
        FROM "lambda:dynamo".default.part, "lambda:mysql".sales.supplier, "lambda:hbase".default.lineitem, 
             "lambda:dynamo".default.partsupp, "lambda:mysql".sales.orders, "lambda:redis".redis.nation
        WHERE s_suppkey = "line:l_suppkey"
        AND ps_suppkey = "line:l_suppkey"
        AND ps_partkey = "line:l_partkey"
        AND p_partkey = "line:l_partkey"
        AND o_orderkey = "line:l_orderkey"
        AND s_nationkey = cast(Regexp_extract(_key_, '.*-(.*)', 1) AS int)
        AND p_name LIKE '%green%' ) AS profit
        GROUP BY  nation, o_year
        ORDER BY  nation, o_year desc;

上面的SQL将dynamodb, mysql, hbase, redis几种类型的数据源都联接起来。

image-20231201204035481

选择使用SPICE，然后点击Visualize：

image-20231201204101924

在可视化页面，分别点击nation和sum_profit字段，QuickSight 根据所选字段自动选择最佳可视化类型：

image-20231201204235950

选择饼图展示：

image-20231201204318600

Analysis准备就绪后，可以创建仪表板并在组织内共享。