OneAD On AWS와 (mjs0428계정의 AWS 상의) 원격지 R과의 JDBC를 통한 Athena Query 연동 테스트
# JDBC Connect 연결 테스트
if(!require(rJava)){install.packages("rJava", dependencies = TRUE); library(rJava)}## Loading required package: rJavaif(!require(RJDBC)){install.packages("RJDBC", dependencies = TRUE); library(RJDBC)}## Loading required package: RJDBC## Loading required package: DBIif(!require(DBI)){install.packages("DBI", dependencies = TRUE); library(DBI)}
if(!require(AWR.Athena)){install.packages("AWR.Athena", dependencies = TRUE); library(AWR.Athena)}## Loading required package: AWR.Athenacon <- dbConnect(AWR.Athena::Athena(), region='ap-northeast-2', S3OutputLocation='s3://onead-rstudio/', Schema='default')
dbListTables(con)## [1] "nepa_seg"
## [2] "onead_req_log"
## [3] "oneadlog"
## [4] "oneadparing"
## [5] "oneadparing2"
## [6] "shoppingch"
## [7] "skmagic"
## [8] "ad"
## [9] "ad_assign_info"
## [10] "ad_category"
## [11] "ad_day"
## [12] "ad_media_content"
## [13] "ad_media_cp"
## [14] "ad_media_pp"
## [15] "ad_placement"
## [16] "ad_region"
## [17] "ad_request_impression_meta_view"
## [18] "ad_request_impression_meta_view_bak"
## [19] "ad_request_impression_meta_view_empty"
## [20] "ad_request_impression_meta_view_no_partition"
## [21] "ad_request_impression_meta_view_partition"
## [22] "ad_request_log"
## [23] "ad_request_log_v2"
## [24] "ad_request_meta_view"
## [25] "campaign"
## [26] "category"
## [27] "content"
## [28] "content_source_v2"
## [29] "cp"
## [30] "enforcement_history_analysis"
## [31] "impression_meta_v3_view"
## [32] "impression_meta_view"
## [33] "impression_meta_view_test"
## [34] "impression_meta_view_test2"
## [35] "impression_row"
## [36] "impression_row_v2"
## [37] "media"
## [38] "nad_user"
## [39] "placement"
## [40] "pp"
## [41] "product"
## [42] "region"
## [43] "elb_logs"dfelb1 = dbGetQuery(con, "SELECT placementid AS placement from onead_bi.ad_request_log_v2 limit 10")
head(dfelb1)## placement
## 1 btv/vod-preroll
## 2 btv/vod-preroll
## 3 btv/vod-cliproll
## 4 btv/vod-cliproll
## 5 oksusu/live-preroll
## 6 oksusu/vod-prerollsql_1 = "SELECT A.date as date, A.request as REQ, A.inven as INV, A.response as RES, B.imp as IMP
FROM (select
placementid AS placement,
CAST(date_format(timestamp, '%Y%m%d') AS integer) AS date,
COUNT(*) AS request, SUM(maxplaycnt) AS inven, SUM(cardinality(extractedadids)) AS response
FROM onead_bi.ad_request_log_v2
WHERE placementid = 'btv/vod-preroll'
AND (CAST(date_format(timestamp, '%Y%m%d') AS integer) >= 20191001 and CAST(date_format(timestamp, '%Y%m%d') AS integer) <= 20191003)
GROUP BY placementid, date_format(timestamp, '%Y%m%d')) A
JOIN (select
placementid AS placement,
impressiondate AS date,
SUM(CAST(impression AS tinyint)) AS imp
FROM onead_bi.impression_row_v2
WHERE placementid = 'btv/vod-preroll'
AND (impressiondate >= 20191001 and impressiondate<=20191003)
GROUP BY placementid, impressiondate) B
ON A.date = B.date;"
dfelb2 = dbGetQuery(con, sql_1)
head(dfelb2)## date REQ INV RES IMP
## 1 20191002 3444281 5805499 3553503 3230614
## 2 20191001 2952507 4790778 3048795 2953773
## 3 20191003 3786882 6408945 3834163 3803552