Mynodbcsv: Lightweight Zero-Config Database Solution for Handling Very Large CSV Files

Abstract

Volumes of data used in science and industry are growing rapidly. When researchers face the challenge of analyzing them, their format is often the first obstacle. Lack of standardized ways of exploring different data layouts requires an effort each time to solve the problem from scratch. Possibility to access data in a rich, uniform manner, e.g. using Structured Query Language (SQL) would offer expressiveness and user-friendliness. Comma-separated values (CSV) are one of the most common data storage formats. Despite its simplicity, with growing file size handling it becomes non-trivial. Importing CSVs into existing databases is time-consuming and troublesome, or even impossible if its horizontal dimension reaches thousands of columns. Most databases are optimized for handling large number of rows rather than columns, therefore, performance for datasets with non-typical layouts is often unacceptable. Other challenges include schema creation, updates and repeated data imports. To address the above-mentioned problems, I present a system for accessing very large CSV-based datasets by means of SQL. It's characterized by: “no copy” approach – data stay mostly in the CSV files; “zero configuration” – no need to specify database schema; written in C++, with boost [1], SQLite [2] and Qt [3], doesn't require installation and has very small size; query rewriting, dynamic creation of indices for appropriate columns and static data retrieval directly from CSV files ensure efficient plan execution; effortless support for millions of columns; due to per-value typing, using mixed text/numbers data is easy; very simple network protocol provides efficient interface for MATLAB and reduces implementation time for other languages. The software is available as freeware along with educational videos on its website [4]. It doesn't need any prerequisites to run, as all of the libraries are included in the distribution package. I test it against existing database solutions using a battery of benchmarks and discuss the results.

An Analysis of Standardized Data for Fog Computing Storage Capacity using Non-Relational Database

“…The relational database is a type of database that stores data in a table and information should be normalized [14] which mean the information must be separated in different table to ensure data is not redundant and also to ensure data integrity. Each table could be linked together with a foreign key [7].…”

“…Both of the format can be stored as a plain text file without having to rely on specific engine or compiler which is suitable to be placed in devices of different hardware and operating system [18]. A research done by [14] who also focus on storing data in plain text having format of comma separated Mohd Hariz Naim @ Mohayat et al, International Journal of Advanced Trends in Computer Science and Engineering, 9(1.3), 2020, 43 -49 value(CSV), however the author discover that CSV file type even though have smaller size but it is much complex to perform information retrieval and eventually take more processing resources such as memory and processor. A research done by [19] where the authors mentioned about using Microsoft Excel with enhanced user interface to perform query for users that do not familiar with SQL statement.…”

“…In term of file size, the standardized data format would depend on number of characters that present in the file [14]. This means the more characters it contains; the number of file size will also increase.…”

“…Since the data is paired with a key, the process of data retrieval could be simplified and applications may access the data by referencing the key as opposed by CSV where applications need to loop through many lines of data. In additions, the technique proposed by [14] also prone to logical error due to misinformation to map the information to which data. <user> <u_fname>John</u_fname> <u_lname>Doe</u_lname> <u_gender>Male</male> </user> { "u_fname":"John", "u_lname":"Doe", "u_gender":"Male" } According to research done by [20], the term fog computing is referred as the moving the computation process from the cloud to the edge of network where IoT devices have the capabilities to perform process such as logical process for detecting and sending notification direct to user without having to wait for cloud processing.…”

Data Integration and Database Formation of Historical Geographical Datasets

“…Database provides solution to both the problems, one of storing data permanently and incrementally besides having to access data in a rich, uniform manner, e.g. using Structured Query Language (SQL) would offer expressiveness and user-friendliness [2][6] [7][8][16-20].…”