InMemory, Columnar database - DuckDb integration with Qt for data analytics

For the complete repository download link, visit: https://github.com/chilarai/qt-duckdb

How DuckDB fits the role in OLAP processing

1. In data analytics, Columnar databases help with Online Analytical Processing (OLAP) workloads since the queries touch across a subset of columns but a large number of rows for those columns.
2. Also often, an embeddable database is needed that can be integrated into the program without any dependencies, just like Sqlite (a dynamic library and a header file are the only things needed to integrate with your application).
3. It supports SQL syntax for data query
4. Lastly, if the database can reside inside memory (ie, in the system's RAM), a lot of time in I/O ops is saved, and hence it's fast.

Qt5 project integration

This demo project will cover the compilation on Mac only. However, it will not be very difficult to compile on other platforms. All that is needed is to download the required binaries (linked libraries, eg, .dll, .so files) for the respective platforms.

Link to the required binaries: https://duckdb.org/docs/installation/

The following steps are already done inside the project. This is only required for fresh projects. After downloading, the following configuration needs to be done.

• Copy the libduckdb.dylib & duckdb.hpp files in the Qt project root.

• Adjust the .pro file to add the library. The following line needs to be added in the file for Mac
  

macx: LIBS += -L$$PWD/./ -lduckdb

The path can be adjusted in -L\$\$PWD/./.

For other platforms, follow the instructions on https://doc.qt.io/qtcreator/creator-project-qmake-libraries.html

• Import duckdb.hpp file in the project

The DuckCRUD class

This class contains all the methods to connect to an InMemory instance of DuckDb and perform CRUD operations

• Import duckdb.hpp file

    #include "duckdb.hpp"

• Contains Q_INVOKABLE methods for CRUD operations

    Q_INVOKABLE void insertData();
    Q_INVOKABLE QStringList readTableData();
    Q_INVOKABLE void updateData();
    Q_INVOKABLE void deleteData();

• A signal to notify if data is updated in the class. This signal is received on the QML code to update the view

signals:
    void dataUpdated();

• Instantiate duckdb object and a connection

private:
    duckdb::DuckDB db;
    duckdb::Connection con;

• In the constructor, default in-memory database, connection are defined. Also, a default table with some data is created

DuckCRUD::DuckCRUD(QObject *parent) : QObject(parent),
    db(nullptr), con(db), counter(1)
{

    // Create a table in the constructor and insert some default values

    con.Query("CREATE TABLE people(id INTEGER, name VARCHAR)");
    con.Query("INSERT INTO people VALUES (0,'Mark'), (1, 'Hannes')");
}

That's it. It was easy, wasn't it?