# \[ANN\] DataFrameDBs.jl

**URL:** <https://discourse.julialang.org/t/ann-dataframedbs-jl/35718>\
**Category:** Data\
**Tags:** package, announcement\
**Created:** [March 8, 2020, 1:50pm UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718 "2020-03-08T13:50:46Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![waralex](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/waralex/32/13415_2.png) [@waralex](https://discourse.julialang.org/u/waralex)\
**Post date:** [March 16, 2020, 9:14am UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/21 "2020-03-16T09:14:40Z")

</div>

> [@Yifan\_Liu](#):
>
> considering the short time period of development, the package’s performance is actually very amazing.

Thanks, @Yifan_Liu !  
I had an"optimization day" yesterday, and here is the result

```julia
>clickhouse-client --max_threads=1
SELECT count()
FROM ecomm
WHERE (price > 100) AND (price < 200)

┌──count()─┐
│ 24898078 │
└──────────┘

1 rows in set. Elapsed: 0.699 sec. Processed 109.95 million rows, 879.61 MB (157.32 million rows/s., 1.26 GB/s.)

```

```julia
julia> @time size(t[:price => (p)->p > 100 && p < 200, :])
Time: 0:00:0.7438 readed: 109.95 MRows (147.82 MRows/sec)
  1.013283 seconds (519.70 k allocations: 29.687 MiB, 0.75% gc time)
(24898078, 9)

```

```julia
SELECT sum((category_id + user_id) / category_id)
FROM ecomm
WHERE (price > 100) AND (price < 200)

┌─sum(divide(plus(category_id, user_id), category_id))─┐
│ 24898078.00147663 │
└──────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 2.555 sec. Processed 109.95 million rows, 398.37 MB (43.03 million rows/s., 155.90 MB/s.)

```

```julia
julia> view = t[:price => (p)->p > 100 && p < 200, :]
julia> col = @. (view.category_id + view.user_id)/view.category_id
julia> @time sum(col)
Time: 0:00:3.2785 readed: 109.95 MRows (33.54 MRows/sec)
  3.425695 seconds (50.06 M allocations: 1.505 GiB, 3.49% gc time)
2.489807800147663e7

```

Julia is amazing language 🙂

There is still a performance problem with string columns due to the materialization of strings (unsafe\_string). I think what to do with it.

> [@Yifan\_Liu](#):
>
> could you get it registered first?

I think I should implement aggregation (probably by integrating OnlineStats.jl) before registering it. Without aggregations, IMHO, this is not very useful for real tasks

---

<div class="post-metadata">

**Author:** ![waralex](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/waralex/32/13415_2.png) [@waralex](https://discourse.julialang.org/u/waralex)\
**Post date:** [March 16, 2020, 10:22am UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/22 "2020-03-16T10:22:42Z")

</div>

> [@xiaodai](#):
>
> Is this an effective strategy if the file totals billions of rows e.g. 2 billion?

I will try to explain it in my poor English, if something is not clear, ask.

Block structure is a tradeoff between memory consumption and performance.

Of course a in memory database has the highest performance, but for 2 billion rows you just might not have enough memory to materialize even one column.

On the other hand, you can theoretically read one value from a file at a time and perform the required calculations on it. In this case, memory consumption is minimal, but performance is low . Due to poor cache locality, inability to use simd, etc. In addition, file compression is not possible in this case.

A block structure is something in the middle. You only need enough memory to process a single block , i.e. even for 10 billion rows in the database , you need enough memory to store only 65,536 (in case of DataFramesDBs) rows to perform calculations and iterations.  
Since the block is large enough, good cache locality and simd optimizations are possible.  
Blocks are also well compressed when writing to a file. Each block is compressed separately before being written to a file. When reading a single compressed block is loaded from file and unpacked to memory. Compression is very important not only for saving disk space, but also for performance. IO operations are slow, and the fewer bytes you have to read from disk, the higher the performance. The cost of unpacking in the case of lz4 is fully recouped by reducing the disk read time

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [March 16, 2020, 12:39pm UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/23 "2020-03-16T12:39:08Z")

</div>

So theoretically, depending on hardward and data size, 2^16 may or may not be the “optimal” chunk size. But it is definitely better than everything in one chunk or one row per chunk

---

<div class="post-metadata">

**Author:** ![waralex](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/waralex/32/13415_2.png) [@waralex](https://discourse.julialang.org/u/waralex)\
**Post date:** [March 16, 2020, 12:51pm UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/24 "2020-03-16T12:51:35Z")

</div>

Yes. The optimal chunk depends on the hardware and the requests you plan to make. In DataFrameDBs you can specify block\_size on table creation. 2^16 is default. I take it from ClickHouse defaults and maybe it’s too small. Because of ClickHouse run each request in several threads and at the same time can execute several requests, so memory limit for each thread is small. In DataFrameDBs, which run in one thread locally memory limit is bigger

---

<div class="post-metadata">

**Author:** ![joshday](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/joshday/32/368_2.png) [@joshday](https://discourse.julialang.org/u/joshday)\
**Post date:** [March 16, 2020, 6:37pm UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/25 "2020-03-16T18:37:05Z")

</div>

This looks very cool! Please reach out if you need any help hooking up to OnlineStats!

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [March 17, 2020, 1:50pm UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/26 "2020-03-17T13:50:34Z")

</div>

does this package try to be something like JuliaDB?

---

<div class="post-metadata">

**Author:** ![waralex](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/waralex/32/13415_2.png) [@waralex](https://discourse.julialang.org/u/waralex)\
**Post date:** [March 17, 2020, 1:57pm UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/27 "2020-03-17T13:57:24Z")

</div>

To be honest, I don’t really understand the JuliaDB philosophy ☹

Rather something like ClickHouse without network interface on pure Julia. I.e. persistent relational DB with optimizations to do requests directly on disk storage.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [March 18, 2020, 4:12am UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/28 "2020-03-18T04:12:10Z")

</div>

I tried to import a large data set using the code:

```julia
using DataFrameDBs, CSV

t = create_table("trial", from = CSV.Rows("opprcd2018.csv", reuse_row=true), show_progress=true)

```

but get the error message:

```julia
ERROR: MethodError: no method matching findfirst(::Char, ::SubString{String})
Closest candidates are:
  findfirst(::Function, ::Union{AbstractString, AbstractArray}) at array.jl:1742
  findfirst(::Function, ::Any) at array.jl:1735
  findfirst(::AbstractString, ::AbstractString) at strings/search.jl:104

```

---

<div class="post-metadata">

**Author:** ![waralex](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/waralex/32/13415_2.png) [@waralex](https://discourse.julialang.org/u/waralex)\
**Post date:** [March 18, 2020, 7:02am UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/29 "2020-03-18T07:02:04Z")

</div>

Can you show me full error trace?

```julia
findfirst(ch::AbstractChar, string::AbstractString)

  Find the first occurrence of character ch in string.

  │ Julia 1.3
  │
  │ This method requires at least Julia 1.3.

```

You have version 1.2?

---

<div class="post-metadata">

**Author:** ![yakir12](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yakir12/32/297_2.png) [@yakir12](https://discourse.julialang.org/u/yakir12)\
**Post date:** [March 18, 2020, 7:22am UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/30 "2020-03-18T07:22:31Z")

</div>

(Just a remark)  
If you do get a really nice API, which seems like where this is going, it might be great to decouple the interface from specifically DataFrames.jl and be able to serve all Tables.jl compatible containers.

---

<div class="post-metadata">

**Author:** ![waralex](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/waralex/32/13415_2.png) [@waralex](https://discourse.julialang.org/u/waralex)\
**Post date:** [March 18, 2020, 7:34am UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/31 "2020-03-18T07:34:56Z")

</div>

What do you have in mind? Now any container, that support `Tables.schema` and `Tables.rows` can be imported to DataFrameDBs. DataFrame is using as default materialization of views. Make DataFrameDBs itself support the Tables interface is one of my TODOs

---

<div class="post-metadata">

**Author:** ![yakir12](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yakir12/32/297_2.png) [@yakir12](https://discourse.julialang.org/u/yakir12)\
**Post date:** [March 18, 2020, 8:53am UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/32 "2020-03-18T08:53:15Z")

</div>

> [@waralex](#):
>
> Make DataFrameDBs itself support the Tables interface

Awesome!

---

<div class="post-metadata">

**Author:** ![Steven\_Sagaert](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/steven_sagaert/32/29578_2.png) [@Steven\_Sagaert](https://discourse.julialang.org/u/Steven_Sagaert)\
**Post date:** [March 18, 2020, 10:16am UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/33 "2020-03-18T10:16:35Z")

</div>

Just a little nitpick: the past tense of to read is read not readed. Otherwise great work!

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [March 18, 2020, 10:56am UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/34 "2020-03-18T10:56:03Z")

</div>

> [@Steven\_Sagaert](#):
>
> read is read not readed

there are many such special cases in English

---

<div class="post-metadata">

**Author:** ![waralex](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/waralex/32/13415_2.png) [@waralex](https://discourse.julialang.org/u/waralex)\
**Post date:** [March 18, 2020, 11:03am UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/35 "2020-03-18T11:03:45Z")

</div>

If studying Julia will advance me in learning English, then this will be an excellent result. 🙂

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [March 18, 2020, 3:41pm UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/36 "2020-03-18T15:41:12Z")

</div>

yes, i am using julia 1.2

---

<div class="post-metadata">

**Author:** ![waralex](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/waralex/32/13415_2.png) [@waralex](https://discourse.julialang.org/u/waralex)\
**Post date:** [March 18, 2020, 3:43pm UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/37 "2020-03-18T15:43:45Z")

</div>

If it’s critical, than I can try to make it compatible with 1.2

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [March 18, 2020, 3:44pm UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/38 "2020-03-18T15:44:27Z")

</div>

> [@waralex](#):
>
> 1.2

that would be great!

---

<div class="post-metadata">

**Author:** ![waralex](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/waralex/32/13415_2.png) [@waralex](https://discourse.julialang.org/u/waralex)\
**Post date:** [March 18, 2020, 3:51pm UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/39 "2020-03-18T15:51:07Z")

</div>

Ok. I originally wrote it in 1.3 and didn 't track what features are supported in 1.2. findfirst is a small thing, and if there is no another critical features that requires 1.3, then I will soon add support for 1.2

---

<div class="post-metadata">

**Author:** ![waralex](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/waralex/32/13415_2.png) [@waralex](https://discourse.julialang.org/u/waralex)\
**Post date:** [March 18, 2020, 5:51pm UTC](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718/40 "2020-03-18T17:51:03Z")

</div>

Now master should work with 1.2.  
If you test this with your data sets and let me know about the problems, it will be very cool. And it will greatly help further development. I apologize in advance for any errors that may occur - the package is quite complex and there may be many different unrecorded problems.

[Previous page](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718.md?page=1)

[Next page](https://discourse.julialang.org/t/ann-dataframedbs-jl/35718.md?page=3)
