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中输入：

Bash

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.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html

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

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

Bash

Sys.getenv()

下载Athena JDBC驱动：

Bash

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)

检查下载：

Bash

Fil
list.files()

设置驱动：

Bash

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

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

Bash

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表：

Bash

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

在Rstudio控制台显示结果

Bash

View(dfelb)

4. 调用Athena进行交互查询

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

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

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

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

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

Bash

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中针对新创建的表运行查询，查找每年发生的事件：

Bash

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 Query execution got cancelled for this query:

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

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

Bash

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://aws.amazon.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.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html

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

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

5. 结论

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

作者介绍：

!

### 谷雷

AWS APN 合作伙伴解决方案架构师，主要负责 AWS (中国)合作伙伴的方案架构咨询和设计工作，同时致力于 AWS 云服务在国内的应用及推广。

本文转载自AWS技术博客。

原文链接：https://amazonaws-china.com/cn/blogs/china/complete-amazon-athena-based-interaction-analysis-using-r/

创作场景

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