Using Presto constructs with dbplyr

[spaceisonlynoise]

I'm analyzing whether I'll be able to use dbplyr with Presto. Suppose I want to right the following query using dbplyr:

    SELECT evs.device_ids['google_advertising_id'] AS device_id, count(*) AS evs_count
    FROM hive.aleph.impressions_daily AS imps
    JOIN hive.aleph.events_daily AS evs ON evs.trans_id = imps.trans_id
    WHERE imps.is_rtb = 1 AND imps.month = '201902' AND imps.day = '20190211' AND
          evs.is_rtb = TRUE AND evs.app_partition = 1087 AND evs.month = '201902' AND evs.day = '20190211' AND
          date_diff('minute', from_iso8601_timestamp(imps.created), from_iso8601_timestamp(evs.created)) < 5
    GROUP BY evs.device_ids['google_advertising_id']
    ORDER BY evs_count DESC

So now I want to write this using dbplyr. How do I go about accessing a key in a map column and how can I call Presto's built-in functions ?

q <- inner_join(imps, evs, by='trans_id', suffix=c('.imps', '.evs')) %>%
  filter(is_rtb.imps == 1, month.imps == '201902', day.imps == '20190211') %>%
  filter(app_partition == 1087, month.evs == '201902', day.evs > '20190211') %>%
  group_by(device_ids) %>%
  summarise(n = n())

[martin.R]

I don't know whether Presto is supported:
https://db.rstudio.com/databases

dbplyr allows you to use native functions of the database. You can check the actual query sent to the db using show_query().

[spaceisonlynoise]

Perhaps what I'm asking doesn't make sense, but is there a way to combine dbplyr with custom sql ?
Something like this perhaps ?

date_filter <- function() {
  return("date_diff('minute', from_iso8601_timestamp(created.imps), from_iso8601_timestamp(created.evs)) < 5")
}


q <- inner_join(imps, evs, by='trans_id', suffix=c('.imps', '.evs')) %>%
  filter(is_rtb.imps == 1, month.imps == '201902', day.imps == '20190211') %>%
  filter(app_partition == 1087, month.evs == '201902', day.evs > '20190211') %>%
  date_filter() %>%
  group_by(device_ids) %>%
  summarise(n = n())

[mishabalyasin]

Take a look at cross-posting FAQ:

FAQ: Is it OK if I cross-post?

Cross-Posting between Community and Other External Sites First, note that cross-posting to multiple forums is often considered as impolite. If you do, always link to those other posts, and update with any solutions. Cross-post sparingly - Rather than post the same thing here and elsewhere from the get-go, post one place, and if you have trouble getting help/get redirected, post here, but be sure to link to the other posting/keep both ends updated with any solution. Don't just dump links to other FAQ sites - If you posted elsewhere but didn't find a solution, please don't just drop a link to that original post here. It is missing details that makes useful and discoverable to people with s…

In general, it's completely fine to cross-post, but make it clear that this question is asked in multiple places so people don't waste their time answering question that might have been answered already somewhere else.

Link to GH issue:

Issue: Presto Support