Changing analysis database from MonetDB to DuckDB

Overview 

DuckDB is a lightweight, embedded analytics database that provides efficient in-memory analytics without requiring a separate database installation. Using DuckDB as the Analysis Database can improve performance while simplifying deployment and maintenance.

This article describes how to switch the analysis database from MonetDB to DuckDB in Wyn across different deployment environments.

Configuring DuckDB in Azure 

1. Stop the Wyn service
2. Prepare configuration and storage
   • 2.1. Ensure the config file path is mounted to shared, persistent storage.
   • 2.2. Mount the path /wyn/analysis.db to shared, persistent storage.
3. Modify the Wyn.conf file
   • 3.1. Download the file (creating a backup by copying it is always recommended).
   • 3.2. Modify it locally. Modify it locally. Add or update the following section:

The Data Warehouse configuration inside the global settings should look like this:

<DataWarehouse>

  <Provider>DuckDB</Provider>

  <ConnectionString>Data source=/wyn/analysis.db</ConnectionString>

</DataWarehouse>

          3.3. Save the changes and upload the updated file back to the same location in your mounted storage to replace the old one.

4. Start the Wyn service again
5. After startup
   • 5.1. Go to the Admin Portal.
   • 5.2. Refresh the dataset cache and data model cache.

---

Configuring DuckDB in Linux Using Docker 

These steps apply to Docker deployments on any Linux distribution, including Ubuntu, RHEL, and CentOS.

1. Stop and Remove the Docker Container
   sudo docker stop {wyn_docker_container_name}
   sudo docker rm {wyn_docker_container_name}
2. Modify the Configuration File

Add the DataWarehouse configuration under the GlobalSettings node:

<GlobalSettings>

  <IdentityServerUrl>http://localhost:51980</IdentityServerUrl>

  <DataWarehouse>

    <Provider>DuckDB</Provider>

    <ConnectionString>Data source=/wyn/analysis.db</ConnectionString>

  </DataWarehouse>

</GlobalSettings>

    3. Add a new mount volume to your deployment script:

sudo docker run \

--name wyn \

-v {your_mount_path}:/wyn/analysis.db \

... \

-d wynenterprise/wyn-enterprise

    4. Run Docker Container
Run your Docker container.

    5. Refresh the Data Cache

After the container runs successfully:

• Go to the Admin Portal.
• Refresh the data model cache and dataset cache manually.

---

Configuring DuckDB in Linux (Non-Docker) 

1. Stop the Wyn Service
   sudo systemctl stop wyn
2. Modify the Configuration File
   Locate the Wyn configuration file (commonly under /opt/Wyn/conf, /opt/Wyn/, or the installation directory).

Add the DataWarehouse section under GlobalSettings:

<GlobalSettings>

  <IdentityServerUrl>http://localhost:51980</IdentityServerUrl>

  <DataWarehouse>

    <Provider>DuckDB</Provider>

    <ConnectionString>Data source=/wyn/analysis.db</ConnectionString>

  </DataWarehouse>

</GlobalSettings>
Ensure that the path /wyn/analysis.db exists and is writable by the Wyn process.

     3. Start the Wyn Service
sudo systemctl start wyn

        Check service status:

         sudo systemctl status wyn

      4. Refresh the Data Cache

        After the service is running:

• Go to the Wyn Admin Portal.
• Refresh both the data model cache and the dataset cache manually.

---

Configuring DuckDB in AKS Using Kubernetes Deployment Scripts 

1. Modify the Configuration File
   Update the DataWarehouse section in Wyn.conf as follows:

<DataWarehouse>

  <Provider>DuckDB</Provider>

  <ConnectionString>Data source=/app/analysis.db;</ConnectionString>

</DataWarehouse>

2. Update Deployment Scripts
2.1 Remove the deployment script analysisdb.yaml from the services folder.
2.2 Add Persistent Volume (PV) and Persistent Volume Claim (PVC) definitions to pv.yaml:

---

apiVersion: v1

kind: PersistentVolume

metadata:

name: wyn-analysisdb-pv-volume

spec:

capacity:

    storage: 50Gi

accessModes:

    - ReadWriteMany

azureFile:

    secretName: wyn-aks-secret

    secretNamespace: default

    shareName: analysisdb

    readOnly: false

mountOptions:

    - dir_mode=0755

    - file_mode=0755

    - uid=1000

    - gid=1000

    - mfsymlinks

    - nobrl

---

apiVersion: v1

kind: PersistentVolumeClaim

metadata:

name: wyn-analysisdb-pv-claim

spec:

storageClassName: ""

accessModes:

    - ReadWriteMany

resources:

    requests:

      storage: 50Gi

3. Add volume mounts in server.yaml and cot-worker.yaml:

volumeMounts:

- mountPath: /app/analysis

    name: analysisdb-persistent-storage

4. Add volume definitions in server.yaml and cot-worker.yaml:

volumes:

- name: analysisdb-persistent-storage

    persistentVolumeClaim:

      claimName: wyn-analysisdb-pv-claim

5. Re-deploy Wyn on AKS

Re-deploy Wyn to apply the changes.

---

Note: In the configuration file, the node “<AnalysisDBService />” also needs to be deleted.

Conclusion 

After completing the configuration and refreshing the caches, Wyn will use DuckDB as the analysis database. Verify that the database file is stored on persistent storage and that all services start successfully to ensure reliable operation.