Как использовать пользовательскую функцию SQL в dbplyr?

Я хотел бы рассчитать расстояние строки Яро-Винклера в базе данных. Если я перенесу данные в R (с collect), я могу легко использовать функцию stringdist из пакета stringdist.

Но мои данные очень большие, и я хотел бы отфильтровать расстояния Яро-Винклера перед переносом данных в R.

Есть код SQL для Яро-Винклера (https://androidaddicted.wordpress.com/2010/06/01/jaro-winkler-sql-code/ и версию для T-SQL), но я не знаю, как лучше всего заставить этот код SQL работать с dbplyr. Я счастлив попробовать сопоставить функцию stringdist с кодом sql Яро-Винклера, но я не знаю, с чего начать. Но было бы замечательно даже что-то более простое, например выполнение кода SQL непосредственно из R на удаленных данных.

Я надеялся, что перевод SQL в dbplyr документации может помочь, но я не я так не думаю.


r dbplyr stringdist
person jfeigenbaum    schedule 02.06.2018    source источник
comment
Не могли бы вы предоставить минимальный, полный, поддающийся проверке пример с врезанным в него образцом кода данных? stackoverflow.com/help/mcve   -  person Hack-R    schedule 03.06.2018
comment
Можете ли вы просто сделать вызов SQL напрямую и использовать dplyr после того, как вы введете свои данные?   -  person Dason    schedule 03.06.2018

Ответы (1)


arrow_upward
4
arrow_downward

Вы можете создавать свои собственные функции SQL в R. Им просто нужно создать строку, которая является допустимым запросом SQL. Я не знаю расстояния Яро-Винклера, но могу предоставить вам пример для построения:

union_all = function(table_a,table_b, list_of_columns){
  # extract database connection
  connection = table_a$src$con

  sql_query = build_sql(con = connection,
                      sql_render(table_a),
                      "\nUNION ALL\n",
                      sql_render(table_b)
  )

  return(tbl(connection, sql(sql_query)))
}

unioned_table = union_all(table_1, table_2, c("who", "where", "when"))

Вот две ключевые команды:

  • sql_render, который берет таблицу dbplyr и возвращает код SQL, который ее создает.
  • build_sql, который собирает запрос из строк.

У вас есть выбор для вашей команды выполнения:

  • tbl(connection, sql(sql_query)) вернет результирующую таблицу
  • dbExecute(db_connection, as.character(sql_query)) выполнит запрос без возврата результата (полезно для удаления таблиц, создания индексов и т. Д.)
person Simon.S.A.    schedule 18.09.2018
comment
Я опробовал ваше решение, и оно работает. Но меня беспокоит одно: в первый раз, когда dbplyr подключается к базе данных через table_a <-tbl(con, "table_a"), ему нужно выполнить фиктивный запрос, т.е. выбрать * из table_a, где 0 = 1. Если я теперь использую объект R table_a в sql_render / build_sql, он снова выполнит тот же тип запроса WHERE 0 = 1. Есть ли способ запретить dbplyr сделать это и вместо этого повторно использовать метаданные из первого вызова tbl (...)? - person Aris Koning; 17.01.2019
comment
Я видел, как dbplyr создавал SQL-запросы, содержащие WHERE 0 = 1, но они никогда не влияли на мои пользовательские функции SQL. Это может быть связано с тем, как вы подключаетесь к таблицам SQL, или потому, что вы вызываете render_sql(tbl(con, "table_a")) вместо того, чтобы сначала назначать tbl_a = tbl(con, "table_a") перед вызовом render_sql(tbl_a). Но я не могу сказать о деталях, которые вы предоставили. Подумайте об открытии нового вопроса и размещении ссылки на него здесь - я могу рассмотреть более подробно. - person Simon.S.A.; 19.01.2019