r - 如何使用 dplyr 语法通过 bigrquery 选择嵌套字段？

Question

如果可能的话，我想使用 dplyr 语法(而不是 SQL)通过 bigrquery 探索 Google Analytics 360 数据。要点是我想了解用户旅程 - 我有兴趣找到用户级别(甚至跨 session )最常见的页面序列。

我想我可以这样做:

sample_query <- ga_sample %>%
  select(fullVisitorId, date, visitStartTime, totals, channelGrouping,
  hits.page.pagePath) %>% 
  collect()

但我收到一条错误，指出未找到 hits.page.pagePath。然后我尝试了:

sample_query <- ga_sample %>%
  select(fullVisitorId, date, visitStartTime, totals, channelGrouping, hits) %>% 
  collect() %>% 
  unnest_wider(hits)

但结果是错误:请求的资源太大而无法返回[responseTooLarge]，这是完全有道理的。

根据我收集的信息，使用 SQL 语法，解决方法是远程取消嵌套，并仅选择hits.page.pagePath code> 字段(而不是整个 hits 顶级字段)。

例如，类似这样的内容(这是一个不同的查询，但传达了要点):

SELECT
  hits.page.pagePath
FROM
  'bigquery-public-data.google_analytics_sample.ga_sessions_20160801' AS GA,
  UNNEST(GA.hits) AS hits
GROUP BY
  hits.page.pagePath

是否可以使用 dplyr 语法执行类似的操作？如果不可能，SQL 的最佳方法是什么？

谢谢!

更新:实际查询/代码

SELECT DISTINCT
fullVisitorId, visitId, date, visitStartTime, hits.page.pagePath, hits.time, geoNetwork.networkDomain
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_*' AS GA, UNNEST(GA.hits) AS hits
WHERE _TABLE_SUFFIX BETWEEN "20191101" AND "20191102"
AND geoNetwork.networkDomain NOT LIKE "%google%"

Answer 1

从 R 转换为 BigQuery(或您使用的任何数据库语言)时，dbplyr 可以创建的查询类型取决于 R 和 BigQuery 之间定义的转换。我找不到任何表明在现有 dbplyr 包中为 UNNEST 定义翻译的示例。 Reference 1 , Reference 2

一种解决方法是定义一个自定义函数，不在 dbplyr 内进行翻译，而是与 dbplyr 一起作为翻译器。之前，当我在 SQL 中需要 PIVOT 但找不到 tidyr::spread 的翻译时，我曾成功地使用过这种方法。

该方法有效，因为 dbplyr 中的远程表由两件事定义:(1) 到远程数据库的连接，(2) 返回表的当前 View 的代码/查询。因此，一旦 dbplyr 将 R 转换为 BigQuery 或 SQL，它就会更新定义的后半部分。

我们可以使用自定义函数来做到这一点:

unnest <- function(input_tbl, select_columns, array_column, unnested_columns){

  # extract connection
  db_connection <- input_tbl$src$con

  select_columns = paste0(select_columns, collapse = ", ")
  unnested_columns = paste0(paste0("un.", unnested_columns), collapse = ", ")

  # build SQL unnest query
  sql_query <- dbplyr::build_sql(
    con = db_connection
    ,"SELECT ", select_columns, ", ", position, ", ", unnested_columns, "\n"
    ,"FROM (\n"
    ,dbplyr::sql_render(input_tbl)
    ,"\n) AS src\n"
    ,"CROSS JOIN UNNEST(", array_column, ") AS un WITH OFFSET position"
  )

  return(dplyr::tbl(db_connection, dbplyr::sql(sql_query)))
}

请注意，我是 dbplyr 用户，但不是 BigQuery 用户，因此上面的语法可能不太完美。我已关注this question和 this one用于语法。

使用示例:

remote_table = tbl(bigquery_connection, from = "table_name")
unnested_table = unnest(remote_table, "ID", "array_col", "list")

# check syntax of dbplyr query
unnested_table %>% show_query()
# if this is not a valid bigquery query then next command will error

# view top 10 rows
unnested_table %>% head(10)

如果remote_table看起来像:

ID ARRAY_COL
01 list = [a,b,c]
02 list = [d,e]
03 list = [q]

然后 unnested_table 应该如下所示:

ID POSITION un.list
01    0        a
01    1        b
01    2        c
02    0        d
02    1        e
03    0        q

并且 unnested_table %>% show_query() 应该类似于:

<SQL>
SELECT *, position, un.list
FROM (
    SELECT *
    FROM table_name
) AS src
CROSS JOIN UNNEST(ARRAY_COL) AS un WITH OFFSET position

更新以匹配目标查询

我知道没有 dbplyr 功能可以轻松翻译“20191101”和“20191102”之间的_TABLE_SUFFIX，因此您必须以另一种方式处理此问题 - 也许是循环R 中的日期列表。

第一步是让 dbplyr 在取消嵌套之前呈现查询。大概是这样的:

for(date in c("20191101", "20191102")){
    table_name = paste0("bigquery-public-data.google_analytics_sample.ga_sessions_",date)

    remote_table = tbl(bigquery_connection, from = table_name)

    remote_table = remote_table %>%
        filter(! (geoNetwork.networkDomain %like% "%google%")) %>%
        select(fullVisitorId, visitId, date, visitStartTime, hits, geoNetwork.networkDomain) %>%
        distinct()
}

调用 show_query(remote_table) 应该会生成与以下内容等效的内容。但它不会完全相同，因为 dbplyr 编写代码的方式与人类不同。

SELECT DISTINCT fullVisitorId, visitId, date, visitStartTime, hits, geoNetwork.networkDomain
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_20191101'
WHERE NOT(geoNetwork.networkDomain LIKE "%google%")

第二步是调用自定义取消嵌套函数”

remote_table = unnest(remote_table,
                      select_columns = c("fullVisitorId", "visitId", "date", "visitStartTime", "geoNetwork.networkDomain"),
                      array_column = "hits",
                      unnested_columns = c("page.pagePath", "time")
               )

调用 show_query(remote_table) 应产生以下结果:

SELECT fullVisitorId, visitId, date, visitStartTime, geoNetwork.networkDomain, position, un.page.pagePath, un.time, 
FROM (

the_query_from_the_first_step

) AS src
CROSS JOIN UNNEST(src.hits) AS un WITH OFFSET position

这可能是我所能提供的帮助，因为我自己没有 bigquery 环境来测试这一点。您可能需要调整自定义 unnest 函数以使其完全匹配您的上下文。希望以上内容足以帮助您入门。