{"id":12138,"date":"2019-04-10T06:57:42","date_gmt":"2019-04-10T11:57:42","guid":{"rendered":"https:\/\/rud.is\/b\/?p=12138"},"modified":"2019-04-10T06:57:42","modified_gmt":"2019-04-10T11:57:42","slug":"lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends","status":"publish","type":"post","link":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/","title":{"rendered":"Lost In [SQL] Translation: Charting d[b]plyr Mapped SQL Function Support Across All Backends"},"content":{"rendered":"<p>Like more posts than I care to admit, this one starts innocently enough with a tweet by @gshotwell:<\/p>\n<blockquote class=\"twitter-tweet\">\n<p lang=\"en\" dir=\"ltr\">Is there a reference document somewhere of which dplyr commands work on various database backends? <a href=\"https:\/\/twitter.com\/hashtag\/rstats?src=hash&amp;ref_src=twsrc%5Etfw\">#rstats<\/a><\/p>\n<p>&mdash; Gordon Shotwell (@gshotwell) <a href=\"https:\/\/twitter.com\/gshotwell\/status\/1115653121269796865?ref_src=twsrc%5Etfw\">April 9, 2019<\/a><\/p><\/blockquote>\n<p><script async src=\"https:\/\/platform.twitter.com\/widgets.js\" charset=\"utf-8\"><\/script><\/p>\n<p>Since I use at least 4 different <code>d[b]plyr<\/code> backends every week, this same question surfaces in my own noggin on occasion and I couldn&#8217;t resist going all Columbo on this mystery.<\/p>\n<p>I should note that if you only really care about the backends that come with <code>dbplyr<\/code> @paleolimbot has you covered <a href=\"https:\/\/apps.fishandwhistle.net\/archives\/1503\">with this post<\/a>, which also shows you the translated SQL!<\/p>\n<h3>Executing The Plan<\/h3>\n<p>There are at least 24 separate backends for <code>dbplyr<\/code>. Most folks won&#8217;t need more than one if their databases all have a decent ODBC or JDBC driver. To be able to use <code>dplyr<\/code> idioms with databases there needs to be a way to translate R code (e.g. function calls) into SQL. <a href=\"https:\/\/github.com\/tidyverse\/dbplyr\/blob\/master\/R\/backend-.R\">A ton of functions are pre-mapped<\/a> in <code>dbplyr<\/code> already and most backend implementations start by relying on these defaults. Furthermore, since SQL is not nearly as &#8220;standard&#8221; across installations as one might think, some common tasks \u2014 such as string manipulation \u2014 <a href=\"https:\/\/github.com\/tidyverse\/dbplyr\/blob\/master\/R\/backend-.R#L236-L267\">have a default noop translation<\/a>.<\/p>\n<p>If you do have to switch across backends with any frequency, knowing which backend provides support for which functions might be nice, but there hasn&#8217;t been a reference for this until Dewey &amp; I accepted Gordon&#8217;s challenge. What makes this a &#8220;challenge&#8221; is that you first have to figure out what packages provide a <code>d[b]plyr<\/code> backend interface then figure out what SQL translations they offer (they don&#8217;t necessarily have to inherit from the ones provided by <code>dbplyr<\/code> and may add other ones to account for SQL clauses that aren&#8217;t in functional form). So the first step was just a look through CRAN for which packages import <code>dbplyr<\/code> and also adding in some I knew were on GitHub:<\/p>\n<pre><code class=\"language-r\">library(stringi)\nlibrary(hrbrthemes)\nlibrary(tidyverse)\n\n# All the pkgs from the home CRAN mirror that import 'dbplyr'\nc(\n  \"arkdb\", \"bigrquery\", \"childesr\", \"chunked\", \"civis\", \"corrr\", \"cytominer\", \"dbplot\",\n  \"dbplyr\", \"dexter\", \"dexterMST\", \"dlookr\", \"dplyr\", \"dplyr.teradata\", \"etl\",\n  \"healthcareai\", \"hydrolinks\", \"implyr\", \"infuser\", \"ipumsr\", \"macleish\", \"mdsr\",\n  \"mlbgameday\", \"modeldb\", \"MonetDBLite\", \"mudata2\", \"parsemsf\", \"pivot\", \"pleiades\",\n  \"pool\", \"poplite\", \"RClickhouse\", \"replyr\", \"RPresto\", \"sergeant\", \"sparklyr\",\n  \"sqlscore\", \"srvyr\", \"taxizedb\", \"valr\", \"wordbankr\", \"metis.tidy\"\n) -&gt; pkgs\n<\/code><\/pre>\n<p>I ended up doing <code>install.pkgs(pkgs)<\/code> which was easy since I have a home CRAN mirror and use macOS (so binary package installs).<\/p>\n<p>The presence of a <code>dbplyr<\/code> import does not mean a package implements a backend, so we have to load their namespaces and see if they have the core &#8220;tell&#8221; (i.e. they implement <code>sql_translate()<\/code>):<\/p>\n<pre><code class=\"language-r\">(map_df(pkgs, ~{\n  tibble(\n    pkg = .x,\n    trans = loadNamespace(.x) %&gt;%\n      names() %&gt;%\n      keep(stri_detect_fixed, \"sql_translate\")\n  )\n}) -&gt; xdf)\n## # A tibble: 28 x 2\n##    pkg       trans\n##    &lt;chr&gt;     &lt;chr&gt;\n##  1 bigrquery sql_translate_env.BigQueryConnection\n##  2 civis     sql_translate_env.CivisConnection\n##  3 dbplyr    sql_translate_env.ACCESS\n##  4 dbplyr    sql_translate_env.Oracle\n##  5 dbplyr    sql_translate_env.SQLiteConnection\n##  6 dbplyr    sql_translate_env.Impala\n##  7 dbplyr    sql_translate_env.OdbcConnection\n##  8 dbplyr    sql_translate_env.MySQLConnection\n##  9 dbplyr    sql_translate_env.PqConnection\n## 10 dbplyr    sql_translate_env.PostgreSQLConnection\n## # \u2026 with 18 more rows\n<\/code><\/pre>\n<p>Now we know the types of connections that package has SQL translation support for. But, we&#8217;re looking for the actual functions they provide. To discover that, we&#8217;re going to make dummy classed connection objects and get the translations they offer.<\/p>\n<p><em>However<\/em>, some may take the defaults from <code>dbplyr<\/code> and not override them so we also need to test if they use the <code>sql_not_supported()<\/code> noop, which we can do by seeing if the function body <a href=\"https:\/\/github.com\/tidyverse\/dbplyr\/blob\/e867e8b64be285bf7fc137f5d5add9ff7e1c4b1f\/R\/translate-sql-helpers.R#L217\">has a call to <code>stop()<\/code><\/a> in it. We&#8217;re also going to ignore maths operators along the way:<\/p>\n<pre><code class=\"language-r\">(filter(xdf, stri_detect_fixed(trans, \".\")) %&gt;%\n  filter(trans != \"sql_translate_env.NULL\") %&gt;% # ignore NULL\n  filter(trans != \"sql_translate_env.Pool\") %&gt;% # ignore db connection pooling \n  filter(trans != \"sql_translate_env.PrestoConnection\") %&gt;% # this one errored out\n  mutate(\u0192 = map(trans, ~{\n\n    # get the sql translate functions\n    con &lt;- NA\n    cls &lt;- stri_replace_first_fixed(.x, \"sql_translate_env.\", \"\")\n    class(con) &lt;- cls\n\n    env &lt;- sql_translate_env(con)\n\n    # but ^^ rly isn't a nice, tidy object, it's a list of environments\n    # with functions in it so we have to iterate through it to extract\n    # the function names.\n\n    map_df(env, ~{\n\n      part &lt;- .x\n      fs &lt;- names(part)\n\n      # but it's not just good enough to do that b\/c a given function name\n      # might just implement the \"sql_not_supported()\" pass through. So we have\n      # to actually look to see if the function body has a \"stop()\" call in it\n      # and ignore it if it does.\n\n      map_df(fs, ~{\n        tibble(\u0192 = .x, src = paste0(as.character(body(part[[.x]])), collapse = \"; \")) %&gt;% # this gets the body of the function\n          filter(!stri_detect_fixed(src, \"stop(\")) %&gt;%\n          filter(stri_detect_regex(\u0192, \"[[:alpha:]]\")) %&gt;% # and we rly don't care about maths\n          select(-src)\n      })\n    })\n  })) %&gt;%\n  unnest(\u0192) %&gt;%\n  mutate(trans = stri_replace_first_fixed(trans, \"sql_translate_env.\", \"\")) -&gt; xdf)\n## # A tibble: 1,318 x 3\n##    pkg       trans              \u0192\n##    &lt;chr&gt;     &lt;chr&gt;              &lt;chr&gt;\n##  1 bigrquery BigQueryConnection median\n##  2 bigrquery BigQueryConnection gsub\n##  3 bigrquery BigQueryConnection as.logical\n##  4 bigrquery BigQueryConnection is.null\n##  5 bigrquery BigQueryConnection case_when\n##  6 bigrquery BigQueryConnection is.na\n##  7 bigrquery BigQueryConnection if_else\n##  8 bigrquery BigQueryConnection str_replace_all\n##  9 bigrquery BigQueryConnection as.integer\n## 10 bigrquery BigQueryConnection as.character\n## # \u2026 with 1,308 more rows\n<\/code><\/pre>\n<p>The rest is all just <code>ggplot2<\/code> basics:<\/p>\n<pre><code class=\"language-r\"> mutate(xdf, db = glue::glue(\"{pkg}\\n{trans}\")) %&gt;% # make something useful to display for the DB\/conn\n  mutate(n = 1) %&gt;% # heatmap block on\n  complete(db, \u0192) %&gt;% # complete the heatmap\n  arrange(\u0192) %&gt;%\n  mutate(\u0192 = factor(\u0192, levels=rev(unique(\u0192)))) %&gt;% # arrange the Y axis in the proper order\n  ggplot(aes(db, \u0192)) +\n  geom_tile(aes(fill = n), color=\"#2b2b2b\", size=0.125, show.legend=FALSE) +\n  scale_x_discrete(expand=c(0,0.1), position = \"top\") +\n  scale_fill_continuous(na.value=\"white\") +\n  labs(\n    x = NULL, y = NULL,\n    title = \"SQL Function Support In Known d[b]plyr Backends\"\n  ) +\n  theme_ipsum_ps(grid=\"\", axis_text_size = 9) + # you'll need to use the dev version of hrbrthemes for this function; just sub out a diff theme if you already have hrbrthemes loaded\n  theme(axis.text.y = element_text(family = \"mono\", size = 7))\n<\/code><\/pre>\n<p>Which makes:<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?ssl=1\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" data-attachment-id=\"12139\" data-permalink=\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/backend-heatmap\/\" data-orig-file=\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=4096%2C2251&amp;ssl=1\" data-orig-size=\"4096,2251\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"backend-heatmap\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=300%2C165&amp;ssl=1\" data-large-file=\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=510%2C280&amp;ssl=1\" src=\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?resize=510%2C280&#038;ssl=1\" alt=\"\" width=\"510\" height=\"280\" class=\"aligncenter size-full wp-image-12139\" \/><\/a><\/p>\n<p>(WP wouldn&#8217;t make the featured image linkable so I had to stick it in again to enabled the link so folks can make it full size which is absolutely necessary to see it).<\/p>\n<h3>FIN<\/h3>\n<p>If you do play with the above, don&#8217;t forget to go one more step and incorporate <a href=\"https:\/\/apps.fishandwhistle.net\/archives\/1503\">Dewey&#8217;s actual SQL mapping<\/a> to see just how unstandardized the SQL standard is.<\/p>\n<p>Contiguous code for the above is over at <a href=\"https:\/\/paste.sr.ht\/~hrbrmstr\/f245b4da31493c53196592432454e8dc8bbf608a\">SourceHut<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Like more posts than I care to admit, this one starts innocently enough with a tweet by @gshotwell: Is there a reference document somewhere of which dplyr commands work on various database backends? #rstats &mdash; Gordon Shotwell (@gshotwell) April 9, 2019 Since I use at least 4 different d[b]plyr backends every week, this same question [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":12139,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"activitypub_content_warning":"","activitypub_content_visibility":"","activitypub_max_image_attachments":3,"activitypub_interaction_policy_quote":"anyone","activitypub_status":"","footnotes":""},"categories":[779,91],"tags":[],"class_list":["post-12138","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dplyr","category-r"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Lost In [SQL] Translation: Charting d[b]plyr Mapped SQL Function Support Across All Backends - rud.is<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Lost In [SQL] Translation: Charting d[b]plyr Mapped SQL Function Support Across All Backends - rud.is\" \/>\n<meta property=\"og:description\" content=\"Like more posts than I care to admit, this one starts innocently enough with a tweet by @gshotwell: Is there a reference document somewhere of which dplyr commands work on various database backends? #rstats &mdash; Gordon Shotwell (@gshotwell) April 9, 2019 Since I use at least 4 different d[b]plyr backends every week, this same question [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/\" \/>\n<meta property=\"og:site_name\" content=\"rud.is\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-10T11:57:42+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=4096%2C2251&ssl=1\" \/>\n\t<meta property=\"og:image:width\" content=\"4096\" \/>\n\t<meta property=\"og:image:height\" content=\"2251\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"hrbrmstr\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"hrbrmstr\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/\"},\"author\":{\"name\":\"hrbrmstr\",\"@id\":\"https:\/\/rud.is\/b\/#\/schema\/person\/d7cb7487ab0527447f7fda5c423ff886\"},\"headline\":\"Lost In [SQL] Translation: Charting d[b]plyr Mapped SQL Function Support Across All Backends\",\"datePublished\":\"2019-04-10T11:57:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/\"},\"wordCount\":555,\"commentCount\":6,\"publisher\":{\"@id\":\"https:\/\/rud.is\/b\/#\/schema\/person\/d7cb7487ab0527447f7fda5c423ff886\"},\"image\":{\"@id\":\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=4096%2C2251&ssl=1\",\"articleSection\":[\"dplyr\",\"R\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/\",\"url\":\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/\",\"name\":\"Lost In [SQL] Translation: Charting d[b]plyr Mapped SQL Function Support Across All Backends - rud.is\",\"isPartOf\":{\"@id\":\"https:\/\/rud.is\/b\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=4096%2C2251&ssl=1\",\"datePublished\":\"2019-04-10T11:57:42+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#primaryimage\",\"url\":\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=4096%2C2251&ssl=1\",\"contentUrl\":\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=4096%2C2251&ssl=1\",\"width\":4096,\"height\":2251},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/rud.is\/b\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Lost In [SQL] Translation: Charting d[b]plyr Mapped SQL Function Support Across All Backends\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/rud.is\/b\/#website\",\"url\":\"https:\/\/rud.is\/b\/\",\"name\":\"rud.is\",\"description\":\"&quot;In God we trust. All others must bring data&quot;\",\"publisher\":{\"@id\":\"https:\/\/rud.is\/b\/#\/schema\/person\/d7cb7487ab0527447f7fda5c423ff886\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/rud.is\/b\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/rud.is\/b\/#\/schema\/person\/d7cb7487ab0527447f7fda5c423ff886\",\"name\":\"hrbrmstr\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2023\/10\/ukr-shield.png?fit=460%2C460&ssl=1\",\"url\":\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2023\/10\/ukr-shield.png?fit=460%2C460&ssl=1\",\"contentUrl\":\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2023\/10\/ukr-shield.png?fit=460%2C460&ssl=1\",\"width\":460,\"height\":460,\"caption\":\"hrbrmstr\"},\"logo\":{\"@id\":\"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2023\/10\/ukr-shield.png?fit=460%2C460&ssl=1\"},\"description\":\"Don't look at me\u2026I do what he does \u2014 just slower. #rstats avuncular \u2022 ?Resistance Fighter \u2022 Cook \u2022 Christian \u2022 [Master] Chef des Donn\u00e9es de S\u00e9curit\u00e9 @ @rapid7\",\"sameAs\":[\"http:\/\/rud.is\"],\"url\":\"https:\/\/rud.is\/b\/author\/hrbrmstr\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Lost In [SQL] Translation: Charting d[b]plyr Mapped SQL Function Support Across All Backends - rud.is","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/","og_locale":"en_US","og_type":"article","og_title":"Lost In [SQL] Translation: Charting d[b]plyr Mapped SQL Function Support Across All Backends - rud.is","og_description":"Like more posts than I care to admit, this one starts innocently enough with a tweet by @gshotwell: Is there a reference document somewhere of which dplyr commands work on various database backends? #rstats &mdash; Gordon Shotwell (@gshotwell) April 9, 2019 Since I use at least 4 different d[b]plyr backends every week, this same question [&hellip;]","og_url":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/","og_site_name":"rud.is","article_published_time":"2019-04-10T11:57:42+00:00","og_image":[{"width":4096,"height":2251,"url":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=4096%2C2251&ssl=1","type":"image\/jpeg"}],"author":"hrbrmstr","twitter_card":"summary_large_image","twitter_misc":{"Written by":"hrbrmstr","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#article","isPartOf":{"@id":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/"},"author":{"name":"hrbrmstr","@id":"https:\/\/rud.is\/b\/#\/schema\/person\/d7cb7487ab0527447f7fda5c423ff886"},"headline":"Lost In [SQL] Translation: Charting d[b]plyr Mapped SQL Function Support Across All Backends","datePublished":"2019-04-10T11:57:42+00:00","mainEntityOfPage":{"@id":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/"},"wordCount":555,"commentCount":6,"publisher":{"@id":"https:\/\/rud.is\/b\/#\/schema\/person\/d7cb7487ab0527447f7fda5c423ff886"},"image":{"@id":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#primaryimage"},"thumbnailUrl":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=4096%2C2251&ssl=1","articleSection":["dplyr","R"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/","url":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/","name":"Lost In [SQL] Translation: Charting d[b]plyr Mapped SQL Function Support Across All Backends - rud.is","isPartOf":{"@id":"https:\/\/rud.is\/b\/#website"},"primaryImageOfPage":{"@id":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#primaryimage"},"image":{"@id":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#primaryimage"},"thumbnailUrl":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=4096%2C2251&ssl=1","datePublished":"2019-04-10T11:57:42+00:00","breadcrumb":{"@id":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#primaryimage","url":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=4096%2C2251&ssl=1","contentUrl":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=4096%2C2251&ssl=1","width":4096,"height":2251},{"@type":"BreadcrumbList","@id":"https:\/\/rud.is\/b\/2019\/04\/10\/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/rud.is\/b\/"},{"@type":"ListItem","position":2,"name":"Lost In [SQL] Translation: Charting d[b]plyr Mapped SQL Function Support Across All Backends"}]},{"@type":"WebSite","@id":"https:\/\/rud.is\/b\/#website","url":"https:\/\/rud.is\/b\/","name":"rud.is","description":"&quot;In God we trust. All others must bring data&quot;","publisher":{"@id":"https:\/\/rud.is\/b\/#\/schema\/person\/d7cb7487ab0527447f7fda5c423ff886"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/rud.is\/b\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/rud.is\/b\/#\/schema\/person\/d7cb7487ab0527447f7fda5c423ff886","name":"hrbrmstr","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2023\/10\/ukr-shield.png?fit=460%2C460&ssl=1","url":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2023\/10\/ukr-shield.png?fit=460%2C460&ssl=1","contentUrl":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2023\/10\/ukr-shield.png?fit=460%2C460&ssl=1","width":460,"height":460,"caption":"hrbrmstr"},"logo":{"@id":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2023\/10\/ukr-shield.png?fit=460%2C460&ssl=1"},"description":"Don't look at me\u2026I do what he does \u2014 just slower. #rstats avuncular \u2022 ?Resistance Fighter \u2022 Cook \u2022 Christian \u2022 [Master] Chef des Donn\u00e9es de S\u00e9curit\u00e9 @ @rapid7","sameAs":["http:\/\/rud.is"],"url":"https:\/\/rud.is\/b\/author\/hrbrmstr\/"}]}},"jetpack_featured_media_url":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2019\/04\/backend-heatmap.jpg?fit=4096%2C2251&ssl=1","jetpack_shortlink":"https:\/\/wp.me\/p23idr-39M","jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":4753,"url":"https:\/\/rud.is\/b\/2016\/12\/20\/sergeant-a-r-boot-camp-for-apache-drill\/","url_meta":{"origin":12138,"position":0},"title":"sergeant : An R Boot Camp for Apache Drill","author":"hrbrmstr","date":"2016-12-20","format":false,"excerpt":"I recently mentioned that I've been working on a development version of an Apache Drill R package called sergeant. Here's a lifted \"TLDR\" on Drill: Drill supports a variety of NoSQL databases and file systems, including HBase, MongoDB, MapR-DB, HDFS, MapR-FS, Amazon S3, Azure Blob Storage, Google Cloud Storage, Swift,\u2026","rel":"","context":"In &quot;Apache Drill&quot;","block_context":{"text":"Apache Drill","link":"https:\/\/rud.is\/b\/category\/apache-drill\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":10121,"url":"https:\/\/rud.is\/b\/2018\/04\/20\/painless-odbc-dplyr-connections-to-amazon-athena-and-apache-drill-with-r-odbc\/","url_meta":{"origin":12138,"position":1},"title":"Painless ODBC  + dplyr Connections to Amazon Athena and Apache Drill with R &#038; odbc","author":"hrbrmstr","date":"2018-04-20","format":false,"excerpt":"I spent some time this morning upgrading the JDBC driver (and changing up some supporting code to account for changes to it) for my metis package? which connects R up to Amazon Athena via RJDBC. I'm used to JDBC and have to deal with Java separately from R so I'm\u2026","rel":"","context":"In &quot;Apache Drill&quot;","block_context":{"text":"Apache Drill","link":"https:\/\/rud.is\/b\/category\/apache-drill\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2018\/04\/today-is-a-good-day-to-query.jpg?fit=700%2C535&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2018\/04\/today-is-a-good-day-to-query.jpg?fit=700%2C535&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2018\/04\/today-is-a-good-day-to-query.jpg?fit=700%2C535&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2018\/04\/today-is-a-good-day-to-query.jpg?fit=700%2C535&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":6091,"url":"https:\/\/rud.is\/b\/2017\/06\/17\/replicating-the-apache-drill-yelp-academic-dataset-with-sergeant\/","url_meta":{"origin":12138,"position":2},"title":"Replicating the Apache Drill &#8216;Yelp&#8217; Academic Dataset Analysis with sergeant","author":"hrbrmstr","date":"2017-06-17","format":false,"excerpt":"The Apache Drill folks have a nice walk-through tutorial on how to analyze the Yelp Academic Dataset with Drill. It's a bit out of date (the current Yelp data set structure is different enough that the tutorial will error out at various points), but it's a great example of how\u2026","rel":"","context":"In &quot;Apache Drill&quot;","block_context":{"text":"Apache Drill","link":"https:\/\/rud.is\/b\/category\/apache-drill\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3784,"url":"https:\/\/rud.is\/b\/2015\/11\/11\/using-monetdblite-with-real-world-csv-files\/","url_meta":{"origin":12138,"position":3},"title":"Using MonetDB[Lite] with real-world CSV files","author":"hrbrmstr","date":"2015-11-11","format":false,"excerpt":"[MonetDBLite](https:\/\/www.monetdb.org\/blog\/monetdblite-r) (for R) was announced\/released today and, while the examples they provide are compelling there's a \"gotcha\" for potential new folks using SQL in general and SQL + MonetDB + R together. The toy example on the site shows dumping `mtcars` with `dbWriteTable` and then doing things. Real-world CSV files\u2026","rel":"","context":"In &quot;monetdb&quot;","block_context":{"text":"monetdb","link":"https:\/\/rud.is\/b\/category\/monetdb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":5131,"url":"https:\/\/rud.is\/b\/2017\/03\/10\/making-a-case-for-case_when\/","url_meta":{"origin":12138,"position":4},"title":"Making a Case for case_when","author":"hrbrmstr","date":"2017-03-10","format":false,"excerpt":"This is a brief (and likely obvious, for some folks) post on the dplyr::case_when() function. Part of my work-work is dealing with data from internet scans. When we're performing a deeper inspection of a particular internet protocol or service we try to capture as much system and service metadata as\u2026","rel":"","context":"In &quot;dplyr&quot;","block_context":{"text":"dplyr","link":"https:\/\/rud.is\/b\/category\/dplyr\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2017\/03\/Cursor_and_RStudio.png?fit=1200%2C464&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2017\/03\/Cursor_and_RStudio.png?fit=1200%2C464&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2017\/03\/Cursor_and_RStudio.png?fit=1200%2C464&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2017\/03\/Cursor_and_RStudio.png?fit=1200%2C464&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/rud.is\/b\/wp-content\/uploads\/2017\/03\/Cursor_and_RStudio.png?fit=1200%2C464&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":11070,"url":"https:\/\/rud.is\/b\/2018\/07\/14\/alleviating-aws-athena-aggravation-with-asynchronous-assistance\/","url_meta":{"origin":12138,"position":5},"title":"Alleviating AWS Athena Aggravation with Asynchronous Assistance","author":"hrbrmstr","date":"2018-07-14","format":false,"excerpt":"I've blogged about how to use Amazon Athena with R before and if you are a regular Athena user, you've likely run into a situation where you prepare a dplyr chain, fire off a collect() and then wait. And, wait. And, wait. And, wait. Queries that take significant processing time\u2026","rel":"","context":"In &quot;athena&quot;","block_context":{"text":"athena","link":"https:\/\/rud.is\/b\/category\/athena\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/rud.is\/b\/wp-json\/wp\/v2\/posts\/12138","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rud.is\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rud.is\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rud.is\/b\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rud.is\/b\/wp-json\/wp\/v2\/comments?post=12138"}],"version-history":[{"count":0,"href":"https:\/\/rud.is\/b\/wp-json\/wp\/v2\/posts\/12138\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/rud.is\/b\/wp-json\/wp\/v2\/media\/12139"}],"wp:attachment":[{"href":"https:\/\/rud.is\/b\/wp-json\/wp\/v2\/media?parent=12138"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rud.is\/b\/wp-json\/wp\/v2\/categories?post=12138"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rud.is\/b\/wp-json\/wp\/v2\/tags?post=12138"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}