Skip to content

data.table spark/databases interface #1828

Closed
Closed
data.table spark/databases interface#1828
Labels
feature requesttop requestOne of our most-requested issues

Description

@ysgit
ysgit
opened on Aug 26, 2016

data.table is awesome but most people don't have 100GB memory in order to handle really large data sets in memory.

Big progress has been made making the Apache Spark framework available through R in the last couple of years. Two such projects are Apache's sparkr and Rstudio's sparklyr. Both of these provide a dplyr style interface to spark's data processing engine.

As a heavy data.table user it would be amazing if there were to be a data.table interface for spark. That would make it incredibly easy for data scientists to migrate their projects from the smaller CSV style data sets to the huge data sets that can be processed by spark.

A classic data pipeline for me is

  1. Bring the data into R by CSV
  2. Do some pre-processing (filters, joins, aggregation, feature extraction) of the data using data.table
  3. Build a model using one of R's many machine learning packages

I want to be able to migrate this to

  1. Connect to data on hadoop cluster
  2. Do some pre-processing (filters, joins, aggregation, feature extraction) of the data using data.table's spark interface
  3. Build a model using one of spark's many machine learning algorithm.

Activity

  1. mattdowle commented on Aug 26, 2016

    @mattdowle
    mattdowle
    on Aug 26, 2016
    Member

    Thanks for the encouragement. Fully agree.
    Yes there are lots of efforts in this space. Note that I (and now Jan Gorecki) work at H2O.
    I gave a recent presentation here: https://www.youtube.com/watch?v=5X7h1rZGVs0
    The slides are here : https://github.com/Rdatatable/data.table/wiki/Presentations
    Just to check whether you had seen these before? Before discussing further.

  2. ysgit commented on Aug 26, 2016

    @ysgit
    ysgit
    on Aug 26, 2016 · Hidden as outdated
    Author

  3. mattdowle commented on Aug 27, 2016

    @mattdowle
    mattdowle
    on Aug 27, 2016 · Hidden as resolved

  4. LeonardAukea commented on Apr 10, 2017

    @LeonardAukea
    LeonardAukea
    on Apr 10, 2017 · Hidden as duplicate

  5. sskarkhanis commented on Apr 11, 2017

    @sskarkhanis
    sskarkhanis
    on Apr 11, 2017

    I really hope DT syntax is available someday. Personally, I really prefer the DT syntax and would like to use it consistently in Spark rather than cringe with dplyr.
    Fingers crossed...

  6. fbala commented on Jun 25, 2017

    @fbala
    fbala
    on Jun 25, 2017 · Hidden as duplicate

  7. aadler commented on Nov 9, 2017

    @aadler
    aadler
    on Nov 9, 2017 · Hidden as duplicate

  8. petalatuy commented on Dec 1, 2017

    @petalatuy
    petalatuy
    on Dec 1, 2017 · Hidden as duplicate

  9. MichaelChirico commented on Dec 1, 2017

    @MichaelChirico
    MichaelChirico
    on Dec 1, 2017 via email · Hidden as outdated

  10. saptarshiguha commented on Dec 8, 2017

    @saptarshiguha
    saptarshiguha
    on Dec 8, 2017 · Hidden as duplicate

  11. sunsiyu commented on May 18, 2018

    @sunsiyu
    sunsiyu
    on May 18, 2018 · Hidden as duplicate

  12. MichaelChirico commented on May 18, 2018

    @MichaelChirico
    MichaelChirico
    on May 18, 2018
    Member

    I'm curious what ppl want out of this exactly.

    Just to be able to use [ on an RDD like you would on a data.table (namely, i/j/by)?

    certainly the full functionality is a ways away, but i imagine it wouldn't be too earth shaking to make an idiom for filtering, grouping, even joining by sending syntax within [] to the corresponding operations in sparkR.

    in particular, this would just amount to (in essence) aliasing sparkR functions in a syntax friendlier for data.table regulars.

    is this what people have in mind?

  13. jangorecki commented on May 18, 2018

    @jangorecki
    jangorecki
    on May 18, 2018
    Member

    No updates, there is a lot to dev around data.table itself, so external interfacing is not that high priority now. Instead of just spark integration it makes more sense to integrate to dplyr, something like dplyr.table (inverse of dtplyr). Then any dplyr backend will work on data.table syntax.

  14. harryprince commented on Aug 12, 2018

    @harryprince
    harryprince
    on Aug 12, 2018 · Hidden as duplicate

  15. DavidArenburg commented on Aug 12, 2018

    @DavidArenburg
    DavidArenburg
    on Aug 12, 2018
    Member

    @jangorecki The problem with that is that will also be slow/full of bugs/ and very unstable as dplyr interface changes on almost daily basis and sometimes they change the whole idiom at once like they did with lazyeval > rlang > tidyeval and G-d knows else what as I long time lost track. Not to mention that hadley, who once stated (with a "tongue in cheeck") that data.table uses "cryptic shortcuts" now masks few of these shortcuts and suddenly don't consider them so cryptic anymore. In short, creating such an API would be a full time job IMO.

    I think migrating a few main functionalities from data.table and keep adding them when there is time would be much safer/easier.

  16. 5 remaining items

  17. jangorecki
    removed
    High
    on Jun 3, 2020
  18. jangorecki
    added
    top requestOne of our most-requested issues
    on Jun 27, 2020

  19. griipen commented on Oct 27, 2020

    @griipen
    griipen
    on Oct 27, 2020

    Have you arrived at a conceivable roadmap for a spark integration project (reverse dtplyr or any other form) given that dplyr 1.0 has been released? It would be great to hear your thoughts now that some time has passed.

  20. ColeMiller1 commented on Nov 15, 2020

    @ColeMiller1
    ColeMiller1
    on Nov 15, 2020
    Contributor

    @jangorecki if the dplyr.table approach is key, or any backend for that matter, it seems like higher integration with data.table would be necessary. Take i which can be:

    1. A character that is NSE to a data.table to do key matching
    2. A logical that is length 1 or nrow(dt) with potential for notjoin NSE
    3. A numeric vector (or single-column numeric matrix) with potential notjoin NSE
    4. A list-like that produces a join or anti-join

    While working on #4585, I also worked on functions to process the isub to their end points but did not implement in the PR because with all the variables needed to process the isub, it was not clean. However, to implement a backend, a function to process the isub would be useful so that NSE would be processed consistently. Otherwise, it would be very easy for dplyr.table i processing to become out of sync of data.table processing.

  21. jangorecki commented on Nov 15, 2020

    @jangorecki
    jangorecki
    on Nov 15, 2020
    Member

    That make sense but for that we have to make multiple new helpers to process internal logic of understanding input arguments, and then export them so such tool can easily mimic this logic. Describing our current API with use of helpers is not that trivial task. See related #852

  22. drag05 commented on Nov 15, 2020

    @drag05
    drag05
    on Nov 15, 2020

    @ jangorecki
    I sincerely hope data.table will become a viable solution on itself and help to completely avoid the dplyr / tidyverse fluff when (and not only when!) interacting with large datasets and out-of-memory computing. So far I find data.table a marvel of clarity and efficiency, almost perfect and excellent integration with mlr3verse, to give an example.

  23. jangorecki commented on Jan 7, 2024

    @jangorecki
    jangorecki
    on Jan 7, 2024
    Member

    Proposed dplyr.table does not need to interact with data.table at all and can be completely standalone package.
    All it needs is to mimic data.table's API:

    DT[ subset|order, select, groupby ]

    That makes it much easier to deliver rather than trying to fit translation inside [.data.table.

    Then possible usage could look like:

    library(data.table)
dt = data.table(a=1:4, b=1:2)

library(dplyr.table)
dp = as.dplyr.table(dt)

all.equal(
  dt[, sum(a), b],
  dp[, sum(a), b] |> as.data.table
)

    The latter one dp could be theoretically any dplyr backend, spark, duckdb (personally I like duckdb a lot but lack of user friendly API is still pushing me away) and so on.

  24. jangorecki
    changed the title [-]data.table spark interface[/-] [+]data.table spark/databases interface[/+] on Jan 7, 2024

  25. grantmcdermott commented on Jan 7, 2024

    @grantmcdermott
    grantmcdermott
    on Jan 7, 2024
    Contributor

    I might be missing something (sorry: long, old thread with several hidden replies). But since we're ultimately talking syntax masking/mimicking, wouldn't it be easier in the long-run to create something like a database.table package that translates the [i, j, by] into the appropriate backend(s)? Or, perhaps easier for conversion, going through the new DT(i, j, by) functional syntax that Matt introduced not so long ago.

    Having a dedicated database.table frontend package that directly controls the syntax generics is probably more aligned with the data.table way than going through d(b)plyr in the end.

    (This be might Jan's point, so again apologies if I'm just quibbling over the name.)

    +1 on DuckDB, although I do think their SQL API is much better than the alternatives.

  26. jangorecki commented on Jan 7, 2024

    @jangorecki
    jangorecki
    on Jan 7, 2024
    Member

    Integration of various sources/targets is a lot of dev and maintenance, therefore doing single integration to dplyr, and via it having another backends feels like much more likely to be achieved. If we want to target only spark, or only duckdb, then I agree it's better to translate directly rather than via dplyr.

    An off topic: DT() has been pulled back for the moment from exported API.

  27. tdhock commented on Jan 8, 2024

    @tdhock
    tdhock
    on Jan 8, 2024
    Member

    I agree that it would be possible and preferable to implement this in a separate package, which hopefully would get the seal of approval #5723

  28. tdhock commented on Jan 8, 2024

    @tdhock
    tdhock
    on Jan 8, 2024
    Member

    also based on the new governance this is out of scope -- "Functionality that is out of current scope...Manipulating out-of-memory data, e.g. data stored on disk or remote SQL DB, (as opposed e.g. to sqldf / dbplyr)" and consensus seems to be that this should be implemented in another package, so I am closing this issue. (feel free to re-open if I have mis-understood)

  29. tdhock
    closed this as completedon Jan 8, 2024

  30. lucasmation commented on Jan 30, 2024

    @lucasmation
    lucasmation
    on Jan 30, 2024

    Bump for the "dplyr.table (inverse of dtplyr)". For me, that would be a game changer, letting me use arrow with the DT syntax. I naively added a feature request for DT syntax on the arrow github page

  31. grantmcdermott commented on Jan 11, 2025

    @grantmcdermott
    grantmcdermott
    on Jan 11, 2025
    Contributor

    For those who haven't seen it: https://cran.r-project.org/package=dbi.table

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    feature requesttop requestOne of our most-requested issues

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions