DuckDB: Workspace db is Empty, Local Path Works

KNIME Analytics Platform
1

I am exploring the use of DuckDB within KNIME. The experience was pretty straight forward:

  • Created a custom Database Driver using the DuckDB Jar
  • I can connect to a file on my machine via jdbc:duckdb:/Users/<me>/<path>/<class>/sessions2024/10-db/music.duckdb
  • The reader node can successfully list the tables in this simple database via a SQL query

Instead of having students reference a full path on their machine, I was hoping that I could drop the folder into the Project Workspace to simplify the path.

  • jdbc:duckdb:music.duckdb can recognize the file in the DB Connection node
  • The same query does not return any data via Reader

My long winded question: Why is that a fully qualified path for the same database works as expected, but when I drop the file into the workspace, a shorter, easier path does not?

Context: I am hoping to simplify the process of connecting to a duckdb database file as much as possible for my upcoming class this spring. File paths will be a point of friction for this set of students.

1 Like
2

Have you considered using SQLite or H2 as local databases. They will be easier to maintain and can also be used in memory.

1 Like
3

Thanks for the prompt response!

Despite the popularity of sqlite, later in the course students will be using Tableau and connecting to the sqlite is much harder than it should be in that BI tool. Ironically, connecting to duckdb or the cloud hosted Motherduck is actually pretty easy to show students. I could change database formats but I am hoping to keep continuity from theme-to-theme in the course.

In the end, I can show how to connect using the full path, I just find it odd that the local workspace reference is giving me fits as described above.

4

@Brock_Tibert you can create a DuckDB using a current JAR driver and prepare a database entry in KNIME preferences:

image
image1920Ă—840 106 KB

You can then either use an in-memory database with a unique URL

jdbc:duckdb::memory:

(yes double ::)

or create a dynamic path for a local database:

image
image2322Ă—1074 218 KB

1 Like
5

Thanks! I realized that my thought of the local file relative to the workspace is likely off. My frame of reference was dragging a CSV file to the workspace and KNIME just “knowing what to do”. Inspecting that setup I can see that it’s defined relative to the workflow, and that option is not the same for databases, naturally.

My gut is that I will have to bite the bullet and show them how to reference file paths. Not the end of the world, but for Windows machines, it can get gnarly.

Thanks!

1 Like
6

Maybe you can try my workflow on a windows machine and see if the path might work. Otherwise you could prepare such a dynamic setting of a URL.

Also your students might benefit from the insight that there are different variations of SQL databases and not all have the same syntax and abilities besides using SQL.