How to move a file system ducklake to another location?

[sebastianjanisch]

Hi,

I am using ducklake against the filesystem and am wondering what the correct way is to move the ducklake with its data to a different location on the filesystem.
My intuition was that this should easily be possible but as it turns out the absolute path of the data files gets baked into the ducklake metadata.

So, when I moved my entire ducklake from folder A to folder B, and then read against it, I was presented with this error:

Error: DATA_PATH parameter "file:///A/delta/" does not match existing data path in the catalog "file:///B/delta/".
You can override the DATA_PATH by setting OVERRIDE_DATA_PATH to True.

I took a look at the OVERRIDE_DATA_PATH which seems to come with some strings attached:

https://ducklake.select/docs/stable/duckdb/usage/connecting

If OVERRIDE_DATA_PATH is used, data under the original DATA_PATH will not be able to be queried in the current connection. This behavior may be changed in the future to allow to query data in a catalog regardless of the current write DATA_PATH.

Generally, when permanently moving a ducklake I suppose I would actually want to also permanently change the meta data entries.

Is there a recommended way of doing this?

Many thanks!

[valmunos]

Maybe a better solution would be to create a new ducklake in the folder that you wanted, then attach both and copy the data from one to the other.

[sebastianjanisch]

@valmunos thanks much for replying!
that would wipe out my entire version history right? I think generally I wonder how come absolute paths are used when both ducklake metadata and actual data are sitting on the filesystem. Even just renaming a parent folder would render the entire dataset unusable, or am I missing something?

[fdcastel]

I’m running into the same issue. Any guidance from the DuckLake team?

I was reading the DuckLakes for Multi-User, Serverless Data Access article and using the sample ducklake:https://raw.githubusercontent.com/marhar/frozen/main/space.ducklake.

However, once the file is downloaded locally, it appears to become unusable. 😟

• On the initial ATTACH attempt, we get the same error @sebastianjanisch reported above.

• Then, trying to work around this with OVERRIDE_DATA_PATH leads to another issue:

  SQL Error: IO Error: Cannot open file "https:\\raw.githubusercontent.com\marhar\frozen\main\space\mission_crew.p1.parquet": The filename, directory name, or volume label syntax is incorrect.
  

I understand that this may stem from early design decisions and that DuckLake is still in a very early v0.x stage.

What we’re looking for here is any workaround that would allow us a very basic operation: to relocate the data storage location.

[staticlibs]

@fdcastel

Hi! About the "Cannot open file" - this may be #239, please try LOAD httpfs;. I know there was an autoloading regression and more fixes around it in DuckLake recently, don't know if this one particular is fixed on the latest.

[fdcastel]

Thanks @staticlibs, that did the trick and the command works now. 👍🏻

That said, does this imply the query was run on the remote dataset instead of the local one?

If so, the OP’s problem still persists. We cannot relocate any ducklake from its original position.

Apologies for not sharing the full repro code earlier:

INSTALL ducklake;
LOAD ducklake;

-- curl -L https://raw.githubusercontent.com/marhar/frozen/main/space.ducklake -O /Temp/space_ducklake/space.ducklake
ATTACH 'ducklake:/Temp/space_ducklake/space.ducklake' AS space_ducklake (DATA_PATH '/Temp/space_ducklake/data_files', OVERRIDE_DATA_PATH TRUE);
USE space_ducklake;

LOAD httpfs;    -- avoid "IO Error: Cannot open file ..."
SELECT
    m.name as mission, 
    count(DISTINCT a.nationality) AS nationalities,
    string_agg(DISTINCT a.nationality, ', ') AS countries_represented
FROM missions m
JOIN mission_crew mc ON m.mission_id = mc.mission_id
JOIN astronauts a ON mc.astronaut_id = a.astronaut_id
WHERE mc.primary_crew = true
GROUP BY m.mission_id, m.name
HAVING count(DISTINCT a.nationality) > 1
ORDER BY nationalities DESC
LIMIT 4;

[cbility]

Hi @sebastianjanisch,

I've managed to move a file system ducklake (to a different drive) using these steps:

1. Copy the data files from the existing file storage to their new home
2. Overwrite the 'data_path' value in the ducklake_metadata table in the catalog with the new root directory, e.g. update ducklake_metadata set value = '/new/data/path' where key = 'data_path';
3. Attach to the ducklake as normal. Note you don't need to specify the new data path when attaching as it's specified in the catalog.

This works because all of the (relative) file paths are accessed relative to the path in ducklake_metadata: see https://ducklake.select/docs/stable/duckdb/usage/paths. It will not change anything for files with absolute paths - they will continue to be accessed at whatever path is stored in the catalog.

If doing this it's probably a good idea to create a copy of your catalog database at the same time as you copy the data, and then overwrite the data_path in the copy.

Note: the caveat If OVERRIDE_DATA_PATH is used, data under the original DATA_PATH will not be able to be queried in the current connection. would have been ok in this situation because I was moving all of the files across and did not need to access files in the old data path. Changing the path in the catalog is just better because it means clients don't need to specify OVERRIDE_DATA_PATH every time they connect. OVERRIDE_DATA_PATH is still useful for us when e.g. a client has the ducklake file store mounted to a non-standard path.