Persistent file based catalog

[alamb]

This is my own personal aspirations / goals for a "file based catalog"

Usecase

Usecase 1: pre-configured EXTERNAL TABLES

I would like to be able to setup some table definitions in dft and then reuse them from session to session

For example

CREATE EXTERNAL TABLE ... STORED AS DELTA TABLE WITH CREDENTIALS ....

And then have this configuration available to any dft session

I believe this usecase is already partly handled by the config file feature. However, there are some other things I would like:

1. Issue SQL commands to create these tables in a session and then have those tables available in the session without having to copy/paste the DDL into a new file
2. Store data that is not DDL (such as parquet metadata fetched from a remote object store to save having to access it again)

Usecase 1: ephemeral data

Today when you run queries like this in dft

CREATE TABLE foo(x int);
INSERT INTO foo VALUES (1);
SELECT * FROM foo;

If you start another session of dft foo is gone:

│select * from foo;   
│Error during planning: table 'datafusion.public.foo' not found

The issue is that the default catalog in datafusion is an ephemeral file based one so there is no place to store data such as shown above.

Desired Behavior

What I would like is for dft to operate similarly to sqlite or duckdb.

By default, an ephemeral in memory catalog is used and nothing is saved after the session quits

However a database file can be "opened" and if so then all changes made to the catalog are stored in that file. If the file is reopened on a subsequent invocation of the program all the DDL / catalog information is still present

Something like

.open mycatalog.dat
-- foo is now saves into mycatalog.dat
CREATE TABLE foo(x int)

[matthewmturner]

Regarding the point on storing data - i wonder if the CacheManager could be extended / used to serialize the files / file_stats caches.

[alamb]

Regarding the point on storing data - i wonder if the CacheManager could be extended / used to serialize the files / file_stats caches.

That is an excellent idea -- it would be really sweet to have an excuse to work on that API (I bet it is not used anywhere near as much as it could be)

[alamb]

I have subsequently learned about the dft config file system that has certain overlap with what is described above

However, one difference is that the config file basically applies to all sessions, where this catalog would be very explicitly selected by a user when running

Thus it seem like the config file would be a great place to put credentials, for example, that you wanted to apply to all catalogs / sessions

[matthewmturner]

There are two different setups that I think are relevant to this feature:

• The config which is for app configuration options (everything from s3 credentials, flightsql host details, to rendering (i.e. FPS), and I have several ideas for additional configuration options. As a starting point, I think we need to document the all the parameters that are available to be set in the config. I will create a ticket for this.
• There is a the datafusionrc file which is simply a SQL file at ~/.datafusion/.datafusionrc that is meant to have DDL that users would want available at start up.

For example, this is mine:

CREATE SCHEMA staging;
CREATE SCHEMA production;

CREATE EXTERNAL TABLE staging.min_aggs STORED AS PARQUET LOCATION 'ny2://sip/aggregates/minute_by_ticker_monthly_v2/year_month=2015-08/data.01.parquet';
CREATE EXTERNAL TABLE staging.trades_v2 STORED AS PARQUET LOCATION 'ny2://atlas/sip/trades_v2/date=2024-08-30/2024-08-30.01.parquet';

CREATE EXTERNAL TABLE "production".min_aggs STORED AS PARQUET LOCATION 'ny5://sip/aggregates/minute_by_ticker_monthly/year_month=2015-08/data.01.parquet';

CREATE TABLE wide AS VALUES (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);

I think the setup for this could be improved (its still based on the old setup from a couple years ago). I think moving the file to ~/.config/dft (so all config in same location) and renaming to something like ddl.sql in that directory would be better.

One thing to note, I have been having trouble getting the custom catalog / schemas to work (created an issue for it. Maybe just a user error, but i havent had time to look too deeply into it.

[alamb]

I agree that there is an importatnt distinction between "configuration" (with e.g. credentials) that potentially apply to all sessions and the DDL/catalog setup part

I think some systems permit creating credentials that are stored as part of the catalog (CREATE CREDENTIALS ....). This kind of makes sense if you think about the catalog / table definitions as application configuration. However this seems like a substantial security risk to me as well. 🤔

I'll have to mess with it to see what is happening

[claudino-kognita]

I have a similar solution that I use internally in my company, but we are opensourcing that in a tool named (Kapôt)[https://github.com/andreclaudino/datafusion-kapot] (a fork of ballista). The idea is to allow YAML definitions for tables, including or not partition, fields and types. What do you think about this approach? Would be great to make a pull request.

(Actually, I am considering to change the datafusion distribution of (Kapôt)[https://github.com/andreclaudino/datafusion-kapot] to datafusion-dft as it already support some things I have plans to include like WASM UDFs.

[matthewmturner]

@claudino-kognita would you be able to make an issue to discuss the YAML / config idea in more detail? we use toml internally where i work for defining tables and it works pretty well so im not strictly opposed to extending the config here to having table definitions but i would have to think about it more. Might be able to have a feature for determining toml or yaml config format as well so it could be up to the user to decide the format of their config

for reference on the WASM UDFs - that is quite new functionality that is under active development and still needs proper testing. Right now it only works with WASM primitives (i32, i64, f32, f64) per row. I think i am close to getting Arrow IPC to work - hoping to get something done by end of this week.

after i get that working i plan looping the wasm udf work back into the flightsql server to try integrating some notion of auth / users only have access to WASM functions they uploaded.