r_jdbc_athena.R

#install.packages("rJava")
#install.packages("RJDBC")

library(RJDBC)

## Warning: package 'RJDBC' was built under R version 3.4.3

## Loading required package: DBI

## Loading required package: rJava

## Warning: package 'rJava' was built under R version 3.4.3

library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

fil <- "E:\\hong\\Project\\96. R_study\\datascientist\\AthenaJDBC41-1.1.0.jar"
drv <- JDBC(driverClass="com.amazonaws.athena.jdbc.AthenaDriver", fil, identifier.quote="'")

con <- jdbcConnection <- dbConnect(drv, 'jdbc:awsathena://athena.ap-northeast-1.amazonaws.com:443/',
                                   s3_staging_dir="s3://aws-athena-query-results-511442695781-ap-northeast-1",
                                   user="AKIAJ4CV6YZL632IMLOA",
                                   password="XajDv5WMjELYjZwwOxt3XscTLat3tgZpRIFZ73vT")

dbListTables(con)

##  [1] "bil_report_hst"     "bil_ri_mst"         "endoscope_sensor"  
##  [4] "elb_logs"           "billing_report_csv" "rimaster"          
##  [7] "bil_report_hst"     "apache_log"         "apache_log_json"   
## [10] "emp_salary"

q <- dbSendQuery(con, "SELECT * FROM igp_ihi.endoscope_sensor where dt = '2010'")
dataset <- fetch(q, n=-1, block=500)

head(dataset, 5)

##      model serial_n program_v       inst_l    send_d send_t radio_f
## 1 OED-1000   100015     01.03              2011/3/31  15:47       1
## 2 OED-1000   100005     01.03   TOKYO-DEMO 2011/3/31  11:22       2
## 3 OED-1000   100003     01.03 FUKUOKA-DEMO 2011/3/31  11:10       3
## 4 OED-1000   100071     01.04     NAGASAKI 2011/3/31  10:24       1
## 5 OED-1000   100103     01.03        AKITA 2011/3/31  10:21       3
##   process_c worker_n    name_w scop_no    scop_nm attention_c1
## 1        76       00                00                    0000
## 2        25       00                00                    0000
## 3        41       00                00                    0000
## 4        41       02 YAMASHITA      01 GIF-H260-1         0000
## 5        41       02  S-KIYOK0      04 GIF-XQ-NO1         0000
##   attention_c2 error_c1 error_c2 error_c3 water_s add_w raw_w ozone_w
## 1         0000     0000     0000     0000       0     0     0       0
## 2         0000     0000     0000     0000      44     0    35      63
## 3         0000     0000     0000     0000     135     0    10      56
## 4         0000     0000     0000     0000      59     0    20      64
## 5         0000     0000     0000     0000     141     0    10      97
##   ozone_g avg_o drainage_t init_p raw_w_p water_t leak_c leak_d op_f_s
## 1       0     0          0      0       0       0      0      0    148
## 2      18    76         78    306     344      12      5      8     19
## 3      38    71         72    369     547      13     11     14    161
## 4      35    71         68    308     342      13      1     11    530
## 5      44    76         92    307     403      15      9     13     95
##   op_f_s_a op_f_l op_f_self op_f_al op_f_air     num_o     insp_d   dt
## 1       27      0        73       0        0 1900/6/23  2010/9/15 2010
## 2        0      1         1       1        1 1900/1/19 2010/12/13 2010
## 3      142     10        18       1        5 1900/5/15   2011/3/2 2010
## 4      418    426       127      83        6 1901/1/18 2010/11/26 2010
## 5      104    160         0      12        0 1900/7/14  2011/1/28 2010

dbDisconnect(con)

## [1] TRUE

?Startup

## starting httpd help server ...

##  done

?.libPaths