Skip to content

Add ability to use @select on SQL data sources #309

Open
Open
Add ability to use @select on SQL data sources#309
Labels
question
Milestone
Backlog

Description

@tlnagy
tlnagy
opened on Jul 15, 2020

I have a very large table in a SQL database that I'm working with and it would be great to ignore some columns before loading, but currently I can't drop columns using @select:

julia> r = DBInterface.execute(db, "SELECT * FROM fxm");

julia> r |> @select(-:c)
MethodError: no method matching remove(::SQLite.Row, ::Val{:c})
Closest candidates are:
  remove(!Matched::NamedTuple{an,T} where T<:Tuple, ::Val{bn}) where {an, bn} at /home/tlnagy/.julia/packages/QueryOperators/g4G21/src/NamedTupleUtilities.jl:27

Stacktrace:
 [1] (::var"#52#54")(::SQLite.Row) at /home/tlnagy/.julia/packages/Query/AwBtd/src/query_translation.jl:58
 [2] iterate at /home/tlnagy/.julia/packages/QueryOperators/g4G21/src/enumerable/enumerable_map.jl:25 [inlined]
 [3] iterate at ./iterators.jl:641 [inlined]
 [4] iterate at ./iterators.jl:639 [inlined]
 [5] _collect at ./array.jl:614 [inlined]
 [6] collect at ./array.jl:603 [inlined]
 [7] |> at ./operators.jl:823 [inlined]
 [8] printtable(::IOContext{Base.GenericIOBuffer{Array{UInt8,1}}}, ::QueryOperators.EnumerableMap{Union{},QueryOperators.EnumerableIterable{SQLite.Row,SQLite.Query},var"#52#54"}, ::String; force_unknown_rows::Bool) at /home/tlnagy/.julia/packages/TableShowUtils/ImkA9/src/TableShowUtils.jl:16
 [9] printtable(::IOContext{Base.GenericIOBuffer{Array{UInt8,1}}}, ::QueryOperators.EnumerableMap{Union{},QueryOperators.EnumerableIterable{SQLite.Row,SQLite.Query},var"#52#54"}, ::String) at /home/tlnagy/.julia/packages/TableShowUtils/ImkA9/src/TableShowUtils.jl:7
 [10] show(::IOContext{Base.GenericIOBuffer{Array{UInt8,1}}}, ::QueryOperators.EnumerableMap{Union{},QueryOperators.EnumerableIterable{SQLite.Row,SQLite.Query},var"#52#54"}) at /home/tlnagy/.julia/packages/QueryOperators/g4G21/src/enumerable/show.jl:35
 [11] show(::IOContext{Base.GenericIOBuffer{Array{UInt8,1}}}, ::MIME{Symbol("text/plain")}, ::QueryOperators.EnumerableMap{Union{},QueryOperators.EnumerableIterable{SQLite.Row,SQLite.Query},var"#52#54"}) at ./multimedia.jl:47
 [12] limitstringmime(::MIME{Symbol("text/plain")}, ::QueryOperators.EnumerableMap{Union{},QueryOperators.EnumerableIterable{SQLite.Row,SQLite.Query},var"#52#54"}) at /home/tlnagy/.julia/packages/IJulia/DrVMH/src/inline.jl:43
 [13] display_mimestring(::MIME{Symbol("text/plain")}, ::QueryOperators.EnumerableMap{Union{},QueryOperators.EnumerableIterable{SQLite.Row,SQLite.Query},var"#52#54"}) at /home/tlnagy/.julia/packages/IJulia/DrVMH/src/display.jl:67
 [14] display_dict(::QueryOperators.EnumerableMap{Union{},QueryOperators.EnumerableIterable{SQLite.Row,SQLite.Query},var"#52#54"}) at /home/tlnagy/.julia/packages/IJulia/DrVMH/src/display.jl:96
 [15] #invokelatest#1 at ./essentials.jl:712 [inlined]
 [16] invokelatest at ./essentials.jl:711 [inlined]
 [17] execute_request(::ZMQ.Socket, ::IJulia.Msg) at /home/tlnagy/.julia/packages/IJulia/DrVMH/src/execute_request.jl:112
 [18] #invokelatest#1 at ./essentials.jl:712 [inlined]
 [19] invokelatest at ./essentials.jl:711 [inlined]
 [20] eventloop(::ZMQ.Socket) at /home/tlnagy/.julia/packages/IJulia/DrVMH/src/eventloop.jl:8
 [21] (::IJulia.var"#15#18")() at ./task.jl:358

It works fine if I first convert to a DataFrame

julia> r |> DataFrame |> @select(-:c)
3x2 query result
a │ b
──┼──
1 │ 3
2 │ 4
3 │ 5

Activity

  1. davidanthoff commented on Jul 15, 2020

    @davidanthoff
    davidanthoff
    on Jul 15, 2020
    Member

    The tabular query commands like @select expect a stream of named tuples, but here you are getting a stream of SQLite.Row elements instead. If you convert to a DataFrame first then it will iterate named tuples, so that is why it works in that case.

    Two ideas: you could either try to convert these Row instances into named tuples somehow. I don't know if they have that ability built in, but maybe something like @map(NamedTuple(_)) or @map({_...}) might work? I haven't tried that.

    You could also give https://github.com/queryverse/QuerySQLite.jl a shot. That is an alternative Query.jl backend that translates your Query.jl query into SQL. You would write something like this:

    import QuerySQLite

db = QuerySQLite.Database(db)

db.fxm |> @select(-:c)

    And in theory that should then run a SQL statement that never even loads the c column from the database at all.

    I'm not sure, though, whether this works with the -:c construction, @bramtayl would know.

  2. davidanthoff
    added
    question
    on Jul 15, 2020
  3. davidanthoff
    added this to the Backlog milestone on Jul 15, 2020

  4. bramtayl commented on Jul 16, 2020

    @bramtayl
    bramtayl
    on Jul 16, 2020
    Contributor

    I haven't done any performance testing on QuerySQL at all, but it's definitely worth a shot! If you don't see performance improvements (highly possible), open an issue and I can try to see what I can do (it might just be a matter of optimizing the generated SQL code a bit).

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

    question

    Type

    No type

    Projects

    No projects

      Milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions