使用 R 完成基于 Amazon Athena 交互分析

R是用于统计分析、绘图的语言和操作环境,是一个用于统计计算和统计制图的优秀工具。RStudio是为R语言设计的一种跨平台集成开发环境，包括可定制化的软件界面以及同团队开发的一系列数据可视化与出版工具。RStudio有免费的自由软件版本及收费的专业版本，并分为在本地电脑上执行的桌面版和与在服务器上执行而可由浏览器连接后使用的服务器版。

Amazon Athena 是一种交互式查询服务，可直接查询 S3 中存储的数据，这使得使用标准 SQL 分析数据变得轻松，而无需管理基础设施。企业数据可存储在高效、廉价、可靠的S3中，并构成企业单一数据源。R 与 Amazon Athena及S3 的集成为数据科学家提供了构建交互式分析解决方案的强大平台。

在本博文中，将通过在 Amazon EC2 实例上运行的 R/RStudio Server 与 Athena 相连接，之后通过浏览器连接RStudio Server执行SQL语句查询S3内的数据。

1. 先决条件

在开始之前，请完成以下步骤。

1.1. 权限设置

通过 Amazon 的 Identity and Access Management (IAM)，让您的 AWS 账户管理员为您的 AWS 账户授予访问 Athena所需的权限。通过将关联的 Athena 策略附加到 IAM 中的数据科学家用户组来完成。

需要为新建的IAM role“rstatus”添加3个policy： AWSQuicksightAthenaAccess、AmazonS3FullAccess、AmazonAthenaFullAccess

1.2. 准备访问aws资源需要的凭证

准备RStudio Server访问aws资源所需要的凭证。

在“IAM-Users-具体用户-security_credentials”处

详见：https://docs.thinkwithwp.com/zh_cn/general/latest/gr/aws-access-keys-best-practices.html

1.3. 存储桶设置

建立 Amazon S3 中的存储目录 s3://athena-gdelt/output，Athena 将使用它来查询数据集并存储结果，另外建立mid文件夹来存放中间数据。

1.4. Amazon Athena设置

设置Athena的workgroup，点击get start进入Athena后，点击右上角的Workgroup: primary,选择primary后，点击”View detail”,之后选择edit，在“Query result location” 选择上节建立的存储桶目录，保存退出。

注意：在本博文中，所有AWS服务都创建在 us-west-2区域。使用区域表来检查 Athena 在其他区域的可用性。

如果使用另外一个区域的Athena，需要在另外一个区域重新建立S3存储桶和目录，并设置给Athena使用，否则后续查询可能会报错。

2. 在 EC2 上设置 R 和 RStudio Server及相关环境

可以手工安装RStudio Server的开源版本，也可以在aws marketplace里面选择RStudio Server Pro版本（https://thinkwithwp.com/marketplace/pp/RStudio-RStudio-Server-Pro-for-AWS/B06W2G9PRY），下面介绍开源版本的安装。

2.1. 配置EC2实例

进入EC2服务页面，launch instance，选择 “Amazon Linux 2 AMI (HVM), SSD Volume Type – ami-0c5204531f799e0c6”操作系统，Amazon linux官方提供了常用的管理工具以及R的安装包，使用比较简便。

选择“Instance Type”，实例类型和相关使用场景的一般对应如下：

实例类型

使用场景

多用户支持

T2.micro

t3.medium

t3.large

免费实例，功能体验

日常开发

小数据集分析

不推荐

m5.large

m5.2xlarge

m5.12xlarge

日常开发和分析

并发分析

并发分析及大数据集

不推荐

2.2. 安装R 和 RStudio Server

首先安装R，使用Amazon Linux extras提供的R3.4，相关的依赖也会一并安装，具体说明见下面链接。

https://thinkwithwp.com/cn/amazon-linux-2/faqs/#Amazon_Linux_Extras

配置Java环境：

$ sudo amazon-linux-extras install R3.4
$ sudo /usr/sbin/alternatives --config java

选择2：

“/usr/lib/jvm/java-11-amazon-corretto.x86_64/bin/java”

$ sudo R CMD javareconf

安装RStudio Server

详细介绍参考：https://rstudio.com/products/rstudio/#rstudio-server

$ wget https://download2.rstudio.org/server/centos6/x86_64/rstudio-server-rhel-1.2.5019-x86_64.rpm
$ sudo yum install rstudio-server-rhel-1.2.5019-x86_64.rpm

添加登录Rstudio的用户并设置密码

$ sudo useradd rstudio
$ sudo passwd rstudio

检查rstudio-server的状态,应该为active（running）

配置Rstudio server访问aws资源需要的凭证 .Renviron，详见1.2

$ sudo vi /home/rstudio/.Renviron

ATHENA_USER=< AWS_ACCESS_KEY_ID >
ATHENA_PASSWORD=< AWS_SECRET_ACCESS_KEY>

3. 在Rstudio Server上配置与Amazon Athena的连接

3.1. 登录Rstudio Server

从EC2控制台，获得EC2的公网IP，在浏览器内输入：

http:// RstudioServer-ec2-IP:8787，登录页面输入2.2节配置的用户名及密码即可登录。

3.2. 安装R packages

在Rstudio的Console中输入：

install.packages("DBI")
install.packages("rJava")
install.packages("RJDBC")
install.packages(c("plyr","dplyr"))
library(DBI)
library(rJava)
library(RJDBC)
library(plyr)
library(dplyr)

3.3. 连接到Athena

AWS提供了连接到JDBC的驱动，详见：

https://docs.thinkwithwp.com/athena/latest/ug/connect-with-jdbc.html

这里使用JDBC 驱动程序版本 2.0.9版本，符合 JDBC API 4.1 和 4.2 数据标准。更多的安装说明可参考页面中的：JDBC 驱动程序安装和配置指南。

检查访问Athena的凭证即环境，即.Renviron文件中保存的凭证，在Rstudio的Console中输入：

Sys.getenv()

下载Athena JDBC驱动：

URL <- 'https://s3.amazonaws.com/athena-downloads/drivers/JDBC/SimbaAthenaJDBC_2.0.9/AthenaJDBC42_2.0.9.jar'
fil <- basename(URL)
if (!file.exists(fil)) download.file(URL, fil)

检查下载：

Fil
list.files()

设置驱动：

drv <- JDBC(driverClass="com.simba.athena.jdbc.Driver", fil, identifier.quote="'")

连接到Athena，AwsRegio表明当前Athena所在的区域，并设置中间文件及output文件的存储位置：

con <- jdbcConnection <- dbConnect(drv, 'jdbc:awsathena://AwsRegion=us-west-2',
                                   s3_staging_dir="s3://athena-gdelt/mid/",
                                   S3OutputLocation="s3://athena-gdelt/output/",
                                   user=Sys.getenv("ATHENA_USER"),
                                   password=Sys.getenv("ATHENA_PASSWORD"))

查询Athena自带样例库中的elb_logs表：

dbListTables(con)
dfelb=dbGetQuery(con, "SELECT * FROM sampledb.elb_logs limit 10")
head(dfelb,2)

在Rstudio控制台显示结果

View(dfelb)

4. 调用Athena进行交互查询

GDELT是一个大数据存储项目，它提供了自1979年至今的全球广播、印刷和网络新闻媒体报道的事件，并按时间和位置索引。其数据量十分庞大，类别也很丰富。

Aws提供公开数据集，后续以此为例进行Athena的使用。

https://registry.opendata.aws/gdelt/

接下来，您将练习从 R 交互式查询 Athena 以进行分析和可视化。

创建数据库和表，这一步也可以在Athena界面中完成。

dbSendUpdate(con,"CREATE DATABASE IF NOT EXISTS gdelt")

dbSendUpdate(con,
"
CREATE EXTERNAL TABLE IF NOT EXISTS gdelt.events (
  `globaleventid` INT,`day` INT,`monthyear` INT,`year` INT,`fractiondate` FLOAT,
  `actor1code` string,`actor1name` string,`actor1countrycode` string,`actor1knowngroupcode` string,
  `actor1ethniccode` string,`actor1religion1code` string,`actor1religion2code` string,
  `actor1type1code` string,`actor1type2code` string,`actor1type3code` string,
  `actor2code` string,`actor2name` string,`actor2countrycode` string,`actor2knowngroupcode` string,
  `actor2ethniccode` string,`actor2religion1code` string,`actor2religion2code` string,
  `actor2type1code` string,`actor2type2code` string,`actor2type3code` string,
  `isrootevent` BOOLEAN,`eventcode` string,`eventbasecode` string,`eventrootcode` string,
  `quadclass` INT,`goldsteinscale` FLOAT,`nummentions` INT,`numsources` INT,`numarticles` INT,`avgtone` FLOAT,
  `actor1geo_type` INT,`actor1geo_fullname` string,`actor1geo_countrycode` string,`actor1geo_adm1code` string,
  `actor1geo_lat` FLOAT,`actor1geo_long` FLOAT,`actor1geo_featureid` INT,
  `actor2geo_type` INT,`actor2geo_fullname` string,`actor2geo_countrycode` string,`actor2geo_adm1code` string,
  `actor2geo_lat` FLOAT,`actor2geo_long` FLOAT,`actor2geo_featureid` INT,
  `actiongeo_type` INT,`actiongeo_fullname` string,`actiongeo_countrycode` string,`actiongeo_adm1code` string,
  `actiongeo_lat` FLOAT,`actiongeo_long` FLOAT,`actiongeo_featureid` INT,
  `dateadded` INT,`sourceurl` string) 
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
  WITH SERDEPROPERTIES ('serialization.format' = '	','field.delim' = '	') 
LOCATION 's3://gdelt-open-data/events/';
"
)

在rstudio中针对新创建的表运行查询，查找每年发生的事件：

year<-dbGetQuery(con,"SELECT year,
         COUNT(globaleventid) AS nb_events
FROM gdelt.events
GROUP BY year
ORDER BY year ASC;")
str(year)
head(year,2)
View(year)

查询的时候可能遇到错误

[Simba][AthenaJDBC](100081) Query execution got cancelled for this query:

这时可在Athena界面内设置“数据使用控制限制”的范围

进一步查询包含OBAMA的事件：

dOBAMA<-dbGetQuery(con,"SELECT year,
COUNT(globaleventid) AS nb_events
FROM gdelt.events
WHERE actor1name='BARACK OBAMA'
GROUP BY year
ORDER BY year ASC;")
View(dOBAMA)

更多rjdbc语法的使用，请参考：https://www.rforge.net/doc/packages/RJDBC/00Index.html

4.1. 进一步优化

针对Athena的性能和成本的优化，可阅读《Amazon Athena的十大性能优化技巧》：

https://thinkwithwp.com/cn/blogs/china/top-10-performance-tuning-tips-for-amazon-athena/

为保证Rstudio server的安全、稳定，可结合Amazon Elastic Load Balancing及Auto-scaling来实现，并启用https传输。

在多用户的环境，可以结合ADFS或OKTA进行用户认证。详见JDBC Driver Installation and Configuration 中认证部分。Guide: https://docs.thinkwithwp.com/athena/latest/ug/connect-with-jdbc.html

进一步可以使用Rstudio server Pro，这个版本对多用户的管理和共享做的更完善。

https://thinkwithwp.com/marketplace/pp/RStudio-RStudio-Server-Pro-for-AWS/B06W2G9PRY

5. 结论

上面如何使用 Athena 和 R 构建简单的交互式应用程序。Athena 可用于使用标准 SQL 存储和查询大数据应用程序的基础数据，而 R 可用于交互式查询 Athena 并使用 R 提供的功能强大的数据洞察，而数据则安全高效的存放在S3上，您只需要为您的使用付费。

亚马逊AWS官方博客