[This article was first published on Dynalytics, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)

---

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Then we should store our Amazon credentials in our system environment for ease of retrieval (optional) . You can put this in the JDBC request:

Lastly call the dbConnect function from the RJDBC package and pass in both the JDBC driver for Athena, the URL and your AWS credentials :

Now if we don’t have any data loaded in Athena, we can do some with the sample data set that Amazon provides. The below code will create the table in the sampledb database. Do note that to create tables from S3 we need to use the dbSendQuery function. 

	dbSendQuery(athenaconnect, "CREATE EXTERNAL TABLE IF NOT EXISTS sampledb.elb_logs_raw_native (
	request_timestamp string,
	elb_name string,
	request_ip string,
	request_port int,
	backend_ip string,
	backend_port int,
	request_processing_time double,
	backend_processing_time double,
	client_response_time double,
	elb_response_code string,
	backend_response_code string,
	received_bytes bigint,
	sent_bytes bigint,
	request_verb string,
	url string,
	protocol string,
	user_agent string,
	ssl_cipher string,
	ssl_protocol string )
	ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
	WITH SERDEPROPERTIES (
	'serialization.format' = '1','input.regex' = '([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*):([0-9]*) ([.0-9]*) ([.0-9]*) ([.0-9]*) (-|[0-9]*) (-|[0-9]*) ([-0-9]*) ([-0-9]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (\"[^\"]*\") ([A-Z0-9-]+) ([A-Za-z0-9.-]*)$' )
	LOCATION 's3://athena-examples/elb/raw/';")

view raw createathenatable hosted with ❤ by GitHub

Once that completed, we can simply query the data using the dbGetQuery function: 

Conclusion

These R code snippets will now allow you to create and query tables for S3 data via Athena. Quite a holiday present from Amazon if I do say so myself.  This begins to make ‘big data’ a little more accessible. 

---

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.

---

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.