How to store efficiently 1B of new rows every day with a possibility to query/index?

user_4170095 0
Viewed 154

Database question. I have data of this structure. 

   duration       caller       callee                   datetime
0      1110  33146060603  33238020000 2020-06-03 09:05:50.703710
1         0  33319185000  33447982364 2020-06-03 09:05:50.703710
2         0  33319185000  33447982364 2020-06-03 09:05:50.703710
3         0  33319185000  33447982364 2020-06-03 09:05:50.703710
4         0  33406780224  33407748000 2020-06-03 09:05:50.703710
5         0  33407816615  33483579000 2020-06-03 09:05:50.703710
6         0  33411703064  33455518000 2020-06-03 09:05:50.703710

That I receive every 5min (via rsync). The total number of rows received every day is around 1e9 (1B) rows. In total, I expect to gather around 100 days minimum. Ideally, it should be easy to remove one full day to save space over time.

I want to be able to query per caller, callee and datetime.

Almost all of my queries are SELECT * from DB where caller = 33319185000 OR callee 33319185000 and datetime IN (2020-06-03) (Selecting all the rows matching a phone number for say 50K numbers at a time).

Is there any type of efficient database to do this task?

I've researched quite a lot but I still haven't found a suitable way to solve this problem efficiently.

deep-learning database
3 Answers

You can try Vaex indeed!

This article give you an example of the usage, and it is using variety of common operations like filtering and selections, on a 1 billion rows in total: https://towardsdatascience.com/how-to-analyse-100s-of-gbs-of-data-on-your-laptop-with-python-f83363dda94

There is also a guide on getting your data in, and the docs should have enough examples to get your started: https://docs.vaex.io/en/latest/

I hope this helps!

(disclaimer: I am the author of the article above, and a core contributor to the project).

user_29965760
user_110737370

After reviewing your requirements, it's not clear if you wish to keep these 100 days (100B) of data 'online' at all moments or if you wish to load in RAM 1 day at a time (and hence remove it easily).

Moreover it may appear that one row of data may take say 34 bytes and hence, these 100B rows could take up to 3+ TB uncompressed.

A possible solution for your use case could be to leverage technologies that allow to keep 100 days of data online, compressed on storage and still with efficient management such as columnar processing, B-tree index, partition management, external storage access...

One such technology could be the Oracle Autonomous Data Warehouse.

Also noticing your tags, it has Python drivers and Advanced Machine Learning capabilities.

Finally, reviewing your query, there may be missing parenthesis:

SELECT * from DB where (caller = 33319185000 OR callee 33319185000) and datetime IN (2020-06-03)

HTH

user_114234760
Related