# Using an SQLite table as a Query.jl data source (or, best way to left-join a DataFrame or Vector with a very large sqlite table)

**URL:** <https://discourse.julialang.org/t/using-an-sqlite-table-as-a-query-jl-data-source-or-best-way-to-left-join-a-dataframe-or-vector-with-a-very-large-sqlite-table/116410>\
**Category:** Data\
**Tags:** sqlite, queryverse\
**Created:** [June 30, 2024, 6:21am UTC](https://discourse.julialang.org/t/using-an-sqlite-table-as-a-query-jl-data-source-or-best-way-to-left-join-a-dataframe-or-vector-with-a-very-large-sqlite-table/116410 "2024-06-30T06:21:51Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sleak](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sleak/32/15447_2.png) [@sleak](https://discourse.julialang.org/u/sleak)\
**Post date:** [June 30, 2024, 6:21am UTC](https://discourse.julialang.org/t/using-an-sqlite-table-as-a-query-jl-data-source-or-best-way-to-left-join-a-dataframe-or-vector-with-a-very-large-sqlite-table/116410/1 "2024-06-30T06:21:51Z")

</div>

I have a vector (of strings) that I want to left-join with an SQLite table (to find an “id” corresponding to each string). The SQLite table is too big to load into memory - but the left join should return only as many rows as my smaller vector of strings. I _think_ Query.jl can do this, but I’m getting stuck trying to use the SQLite table in the query.

I have:

```julia
cursor = DBInterface.execute(db, "SELECT * from paths") # schema has (id INTEGER, path TEXT) 
q = @from p1 in my_paths begin # my_paths is a Vector{String}
    @left_outer_join p2 in cursor on p1 equals p2.path
    @select {p2.id, p2.path}
    @collect DataFrame
end

```

Using `@allocated`, it looks like the `DBInterface.execute` query here doesn’t immediately load the table into memory (which is good). But my Query.jl block fails with:

```julia
The keys in the join clause have different types, String and Any

```

It seems the SELECT query returns columns of type `Any`, instead of the types in the SQLite schema

How do I get the SQLite cursor to return more specifically-typed data? Or, get Query.jl to convert the columns in the cursor from `Any`, `Any` to `Int64`, `String` ?

---

<div class="post-metadata">

**Author:** ![drizk1](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/drizk1/32/208422_2.png) [@drizk1](https://discourse.julialang.org/u/drizk1)\
**Post date:** [July 3, 2024, 10:30pm UTC](https://discourse.julialang.org/t/using-an-sqlite-table-as-a-query-jl-data-source-or-best-way-to-left-join-a-dataframe-or-vector-with-a-very-large-sqlite-table/116410/2 "2024-07-03T22:30:26Z")

</div>

I think you might be able to use TidierDB.jl to do this via SqLite or duckdb.

If you convert the vector of strings to a df and copy it to your database, you should then be able to do the left join.

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [July 3, 2024, 10:50pm UTC](https://discourse.julialang.org/t/using-an-sqlite-table-as-a-query-jl-data-source-or-best-way-to-left-join-a-dataframe-or-vector-with-a-very-large-sqlite-table/116410/3 "2024-07-03T22:50:24Z")

</div>

The easiest is to just do N queries like `"select * from paths where path = yourpath1"`. SQLite is specifically designed to do this performantly, compared to client-server databases.