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

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

chilarai profile image Chilarai ・2 min read

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
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode
  • Contains Q_INVOKABLE methods for CRUD operations
    Q_INVOKABLE void insertData();
    Q_INVOKABLE QStringList readTableData();
    Q_INVOKABLE void updateData();
    Q_INVOKABLE void deleteData();
Enter fullscreen mode Exit fullscreen mode
  • A signal to notify if data is updated in the class. This signal is received on the QML code to update the view
    void dataUpdated();
Enter fullscreen mode Exit fullscreen mode
  • Instantiate duckdb object and a connection
    duckdb::DuckDB db;
    duckdb::Connection con;
Enter fullscreen mode Exit fullscreen mode
  • 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')");
Enter fullscreen mode Exit fullscreen mode

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


Editor guide