How to use SQLite Connector in the Business Hub?

[laughsmile]

Hello everyone

It seems SQLite Connector can not used in the Business Hub.
System architecture :

SQLite Connector Setting:

But After clicking the OK button, the Database file path is changed into windows-style path automatically.

And I do not think the application in the Business Hub can access SQLite DB file directly.
How to solve the problem.

Thanks in advance.
Ryu

[AlexanderFillbrunn]

Hi Ryu,
Is the sqlite.db file available within the executor container? If not, I recommend first downloading the file into the workflow, e.g. using our FTP Connector and a Transfer Files node, then making changes or queries, then sending the file back to the original location. However, you will lose “transactional safety” that way when multiple workflows access the file at the same time. This is an inherent problem with SQLite, though. If you want to avoid that, the file share with the file needs to be mounted inside the executor container.
Kind regards,
Alexander

[mlauber71]

@AlexanderFillbrunn , @laughsmile might it also be possible that you would have to access the “Local file system access by KNIME workflows”?

KNIME Server Administration Guide

[laughsmile]

Thank you for your answer.

@AlexanderFillbrunn
We are thinking install the Java App and SQLite into the Business Hub
Docker Container. Is it possible? Do you know how to do it?

@mlauber71
We are using KNIME Business Hub , not KNIME Server. The workflow in the KNIME Business Hub can not access files in the local disk.

Best regards,
Ryu

[AlexanderFillbrunn]

Hi @laughsmile,
I think @mlauber71 is correct still. The executor blocks access to the local file system, but if you want to access the SQLite DB mounted inside your executor container, you will need to “unblock” it by changing the settings linked by @mlauber71.

There are different ways of adding files and software to KNIME’s executor containers, but I am not sure we have one that suits your needs here. The easiest one is to change the Docker image for the container to contain the extra files. However, for your DB this would mean that it is not shared and every executor has its own instance and every time the container is terminated and recreated, e.g. because the executor settings change, all changes to the DB are lost. So this option is only well-suited for static files.
The other option would be to mount a network drive into the container, but this would require changing some Kubernetes resources and they would be reset at least with every update of the Hub. I will ask internally if there is a recommended approach and will update here.
Kind regards,
Alexander

[laughsmile]

Hello @AlexanderFillbrunn

Thank you for the solution.
would you please tell me how to mount a network drive into the container?
When updating the version of the Hub , we can reset the setting.

Thanks in advance.
Ryu

[AlexanderFillbrunn]

Hi Ryu,
I think it depends on the type of network drive you have. If it is a NFS drive, that would be ideal because Kubernetes has built-in support for mounting that. Have a look at this guide: Mounting NFS to a container running on Kubernetes | rudimartinsen.com. To mount the NFS, you need to adapt KNIME’s execution contexts:

Get list of ECs:

kubectl -n hub-execution get executioncontexts.hub.knime.com

Retrieve YAML object for EC

kubectl -n hub-execution get executioncontext.hub.knime.com/<ec-id> -o yaml > ec.yaml

Add volume and volume mount to EC

kubectl -n hub-execution apply -f ec.yaml

apiVersion: hub.knime.com/v1alpha1
kind: ExecutionContext
metadata:
  ...
spec:
  ...
  executor:
    ...
    volumeMounts:
    - mountPath: /external-tools
      name: additional-execution-mount-pvc
    volumes:
    - name: additional-execution-mount-pvc
      persistentVolumeClaim:
        claimName: additional-execution-mount-pvc
status:
  ...

In the last document, the volume mounts and volumes in the executor part would need to be adapted so they use NFS instead.
Kind regards,
Alexander