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

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

Sys.getenv()

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)

Fillist.files()

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

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"))

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

View(dfelb)

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/';")

year<-dbGetQuery(con,"SELECT year,         COUNT(globaleventid) AS nb_eventsFROM gdelt.eventsGROUP BY yearORDER BY year ASC;")str(year)head(year,2)View(year)

dOBAMA<-dbGetQuery(con,"SELECT year,COUNT(globaleventid) AS nb_eventsFROM gdelt.eventsWHERE actor1name='BARACK OBAMA'GROUP BY yearORDER BY year ASC;")View(dOBAMA)

### [](https://amazonaws-china.com/cn/blogs/china/tag/%E8%B0%B7%E9%9B%B7/)
AWS APN 合作伙伴解决方案架构师，主要负责 AWS (中国)合作伙伴的方案架构咨询和设计工作，同时致力于 AWS 云服务在国内的应用及推广。