Выберите строки, где массив содержит одно из нескольких значений в bigquery (в идеале с dbplyr)

У меня есть большой набор твитов на bigquery, и теперь я хочу отфильтровать те, которые содержат хотя бы один из списка хэштегов. Хэштеги сохраняются в столбце массива (загружаются из столбца списка в R). Как я могу выбрать строки, содержащие одно из нескольких значений в любом месте этого массива?

Ниже код, который я бы использовал для анализа в R. Неудивительно, что dbplyr не может перевести часть purrr, и я рад научиться создавать SQL самостоятельно, но пока не нашел хорошей отправной точки. Спасибо за любые указатели.

PS: я еще не загрузил твиты в bigquery, в настоящее время они живут в 80 ГБ файла RDS. Если бы какое-либо простое преобразование данных облегчило бы эту задачу, я бы все равно мог включить это при загрузке.

tweets_sample <- tibble::tribble(
  ~text, ~hashtags,
  "Hello", list("World", "You"),
  "Goodbye", list("Friend", "You"),
  "Not", list("interested")
)

hashtag_list <- c("World", "interested")

tweets_sample %>% filter(purrr::map_lgl(hashtags, ~ .x %in% hashtag_list %>%
                                   any()))

sql google-bigquery dbplyr
person Lukas Wallrich    schedule 14.10.2020    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Сложность здесь в том, что ваш столбец хэштегов имеет тип списка или массива. В соответствии с этим вопросом перевод dbplyr для более сложных типов данных, таких как массивы, не отображается быть хорошо зарекомендовавшим себя.

Два альтернативных подхода:

  1. Преобразуйте свои хэштеги в строку символов и используйте текстовый поиск (grep).

  2. Напишите запрос bigquery в виде строки символов в R и прикрепите его к существующему соединению. Вот пример:

db_connection = DBI::dbConnect( ... ) # connect to database
remote_tbl = dplyr::tbl(db_connection, from = "remote_table_name")

# build SQL query
sql_query <- glue::glue("SELECT *\n",
                        "FROM (\n",
                        "{dbplyr::sql_render(remote_tbl)}\n",
                        ") alias\n",
  
new_remote_table = dplyr::tbl(db_connection, dbplyr::sql(sql_query))
person Simon.S.A.    schedule 14.10.2020
comment
Спасибо! В этом случае (1) действительно может работать хорошо, особенно потому, что это упрощает работу с похожими, но не идентичными хэштегами. Касательно 2, просто для справки, я немного покопался и обнаружил, что SQL для bigquery должен быть примерно таким: SELECT * FROM tweets1 WHERE («Женский день» В UNNEST (хэштеги) ИЛИ «Евровидение» В UNNEST (хэштеги) ИЛИ 'brexit' В UNNEST(хэштеги)) - person Lukas Wallrich; 15.10.2020