Transition from a hierarchial file model to a "flat list" model stored in a column database for a geological-geophysical archive
UDC: 004.652
DOI: 10.33285/2782-604X-2023-2(595)-20-24
Authors:
SLINKINA ELENA V.
1,
APARIN SEMEN N.1
1 Tyumen Oil Research Center, Tyumen, Russia
Keywords: geological-geophysical data, hierarchial data model, flat table, relational database, columnar database, PostgreSQL, ClickHouse
Annotation:
The article describes the current data model for storing files in the archive of geological-geophysical information. The archive stores information on the following levels: organizations that own data, fields, wells, types of studies and dates of their implementation. The shortcomings of the hierarchical data model in case of storing and accessing files that have several links with levels are described. It is proposed to supplement the current storage model with a flat list placed in the database. Each entry will be a link between a file and a level in the archive. This approach will make it possible to implement multi-object binding of files and quick attribute search for files in the archive. The choice of a database for storing a flat list of the file links is analyzed. The time of executing queries on getting the same data from various databases was studied as well. The queries were executed in PostgreSQL relational database and ClickHouse column database. It was empirically confirmed that the most suitable way to store "flat table" data structure is a database that uses OLAP technology – ClickHouse.
Bibliography:
1. Hesselink W.H., Lali M.I. Formalizing a Hierarchical File System // Electronic Notes in Theoretical Computer Science. – 2009. – Vol. 259. – P. 67–85. – DOI: 10.1016/j.entcs.2009.12.018
2. Trubacheva S.I. Osobennosti postroeniya faylovykh sistem // Vestn. Volzhskogo un-ta im. V.N. Tatishcheva. – 2013. – № 4(22). – S. 97–104. – URL: https://cyberleninka.ru/article/n/osobennosti-postroeniya-faylovyh-sistem (data obrashcheniya 13.10.2022).
3. PostgreSQL Documentation. – URL: https://www.postgresql.org/docs/ (data obrashcheniya 13.10.2022).
4. ClickHouse. The Open Source OLAP database management system. – URL: https://clickhouse.com/clickhouse (data obrashcheniya 13.10.2022).
5. ClickHouse. ClickHouse Docs and Knowledge Base. – URL: https://clickhouse.com/docs/en/home/ (data obrashcheniya 13.10.2022).
Back to issue