Add a way to show the contents of the ListFilesCache in datafusion-cli

@BlakeOrth

Is your feature request related to a problem or challenge?

Related to Adds memory-bound DefaultListFilesCache #18855
part of [EPIC] ListingTable object store usage improvements #17214

As we roll out the ListingFileCache from @BlakeOrth in #18855 it would be very helpful to be able to see its contents to debug any potential issues we see

@nuno-faria made a really nice feature to view the contents of the cache: metadata_cache()

For example:

> select * from metadata_cache();
+---------------------------------------------------+---------------------+-----------------+--------------------------------------+---------+---------------------+------+------------------+
| path                                              | file_modified       | file_size_bytes | e_tag                                | version | metadata_size_bytes | hits | extra            |
+---------------------------------------------------+---------------------+-----------------+--------------------------------------+---------+---------------------+------+------------------+
| hits_compatible/athena_partitioned/hits_1.parquet | 2022-07-03T15:33:57 | 174965044       | "1f5da68e097309811a675c849491ac48-9" | NULL    | 165128              | 0    | page_index=false |
+---------------------------------------------------+---------------------+-----------------+--------------------------------------+---------+---------------------+------+------------------+
1 row(s) fetched.
Elapsed 0.005 seconds.

Describe the solution you'd like

I would like a table function similar to metadata_cache() for the listing files cache. Since each entry is a Vec<ObjectMeta> one option would be to flatten the entries so there is one entry per ObjectMeta stored:

Someting like

select * from list_files_cache();

path	file_modified	file_size_bytes	e_tag	version	metadata_size_bytes	expires
/foo/bar	2022-07-03T15:33:57	1234	...	...	132	NULL
/foo/baz	2022-07-03T15:33:57	5678	...	...	3112	2026-07-03T15:33:57
...	...	...	...	...	...	...

Where metadata_size_bytes shows the size of the statistics, in bytes and expires shows when the entry expires

This would mean that a single ListFilesEntry object is displayed as multiple rows.

It would also mean we would have to find some way to represent a ListFilesEntry that had no entries (e.g. metas is an empty Vec). Perhaps it could have a row entirely of nulls:

path	file_modified	file_size_bytes	e_tag	version	metadata_size_bytes	expires
NULL	NULL	NULL	NULL	NULL	NULL	2026-07-03T15:33:57

Describe alternatives you've considered

No response

Additional context

No response

@alamb

@alamb Can you help me understand the benefit of flattening the Vec<ObjectMeta> into individual rows?

I'm concerned this could make the output table difficult to read (the s3://clickhouse-public-datasets/hits_compatible/athena_partitioned/ dataset would result in 100 rows, for instance) and I'm not sure what benefit it would provide since the TTL and metadata size for each element of the Vec will be, by and large, uniform. Additionally the cache (at least currently) evicts the entire Vec as opposed to individual entries, so we wouldn't expect any fragmentation issues with entries within a collection of metadatas.

@alamb Can you help me understand the benefit of flattening the Vec<ObjectMeta> into individual rows?

The core benefit in my opinion is that it would be possible to easily see and search for individual files in the cache

However, now that DataFusion's structured type support (aka ListArray) is much better, returning a ListArray<> is probably better

take

@BlakeOrth

Hi @BlakeOrth , it seems like currently ListFilesCache does not have a list_entries method. So for this change, as I understand, I would need to first add a list_entries method similar to that of FileMetadataCache, and then use it to populate a ListFilesCacheTable for table function list_files_cache(). Please let me know if that sounds correct. Thank you.

@jizezhang

Hi @BlakeOrth , it seems like currently ListFilesCache does not have a list_entries method. So for this change, as I understand, I would need to first add a list_entries method similar to that of FileMetadataCache, and then use it to populate a ListFilesCacheTable for table function list_files_cache(). Please let me know if that sounds correct. Thank you.

@jizezhang Yes, this sounds right to me! A list_entries method wasn't written initially because we wouldn't have really had any use for it until something like this issue was implemented.

@BlakeOrth

Thank you @BlakeOrth. There is one more thing I would like to confirm. It seems like ListFilesCache defaults to None and the moment and there is no ExecutionOptions to enable that for user to actually use and view it with CLI? If that sounds right, wanted to know if we want to add an option, and whether that should be included as part of this issue or is tracked separately. Thanks!

@jizezhang

@jizezhang Good observation! This is actually a temporary "problem" that will hopefully be resolved soon.

Enables DefaultListFilesCache by default #19366

We initially left the cache disabled by default until users could configure it, as it needed to be disabled for several integration tests to pass. Once the above PR is merged it will be enabled by default, so I don't think you need to worry about additional options for this effort.

@BlakeOrth

Hey @BlakeOrth , I have one more question regarding the logic here

datafusion/datafusion/execution/src/cache/cache_manager.rs

Lines 193 to 200 in 0a0416d

    
           let list_files_cache = config 
        
               .list_files_cache 
        
               .as_ref() 
        
               .inspect(|c| { 
        
                   // the cache memory limit or ttl might have changed, ensure they are updated 
        
                   c.update_cache_limit(config.list_files_cache_limit); 
        
                   c.update_cache_ttl(config.list_files_cache_ttl); 
        
               })

It seems that if user creates a ListFilesCache with ttl set, e.g. DefaultListFilesCache::new(1024, Some(Duration::from_secs(1)), but does not setCacheManagerConfig::list_files_cache_ttl, this logic would unset ttl in the list_files_cache. Is that intended? e.g. should user always set ttl with config.list_files_cache_ttl? Thanks!

(I saw your PR #19366 has some update on this part but this logic seems to remain the same, hence wanted to ask.)

@jizezhang

@jizezhang This is actually a great catch and something I hadn't thought of. As of right now the DefaultListFilesCacheis largely designed to mirror the behavior of the DefaultFilesMetadataCache to keep the user experience the same. Now that you've brought this up, I believe the metadata cache exhibits the same behavior for its limit setting.

The behavior does feel a bit odd to me. I think the thought behind the behavior is that it's possible a cache already exists and a user may have changed the value in the config, so the caches should be updated with that value. You've noticed the other side of that, where it's possible for a constructed value to be overridden by the config value even if it was set explicitly. This seems unexpected to me; I would personally say that a new issue should be opened to address this.

@jizezhang

The behavior does feel a bit odd to me. I think the thought behind the behavior is that it's possible a cache already exists and a user may have changed the value in the config, so the caches should be updated with that value. You've noticed the other side of that, where it's possible for a constructed value to be overridden by the config value even if it was set explicitly. This seems unexpected to me; I would personally say that a new issue should be opened to address this

Yes please! Nice catch @jizezhang

Cut an issue for that #19396 :)

Also, to add a test for this change, I think I need a directory of test files with the same schema, as it looks like the cache is used only when table url is a collection

datafusion/datafusion/datasource/src/url.rs

Lines 257 to 258 in d493f3d

    
           let list: BoxStream<'a, Result<ObjectMeta>> = if self.is_collection() { 
        
               list_with_cache(ctx, store, &self.prefix, prefix.as_ref()).await?

but currently there is no such directory of files under parquet-testing repo. I tested locally with files from benchmark, but to actually add a unit test, I wonder if I should first add test files in parquet-testing repo, or if you have other suggestions. Thank you!

@jizezhang

I wonder if I should first add test files in parquet-testing repo, or if you have other suggestions. Thank you!

@jizezhang I don't think adding files to parquet-testing is necessary here. The caching functionality works equally as well with temporary in-memory object stores, so there's no technical reasons to prefer files on disk or some other remote storage for testing. There are quite a few ways to accomplish getting some valid test files.

I would probably recommend you take inspiration from the object_store_access.rs tests which set up various in-memory tables programmatically:

datafusion/datafusion/core/tests/datasource/object_store_access.rs

Line 505 in 2e3707e

fn new() -> Self {

If you're looking to do some integration level testing similar to the tests here:

datafusion/datafusion-cli/tests/cli_integration.rs

Line 402 in 2e3707e

async fn test_object_store_profiling() {

You can use DataFusion to create parquet files by using the COPY command. You can see various usage of COPY with parquet files in the sqllogic tests:

datafusion/datafusion/sqllogictest/test_files/parquet.slt

Line 18 in 2e3707e

# TESTS FOR PARQUET FILES

For testing I think COPY is preferable to INSERT because it will allow you to have well defined names for your files. The subtle caveat here is that when the cache is enabled tables that are already defined won't pick up changes from the COPY commands, so you'd have to make sure you write all the files first prior to creating a table in DataFusion.

@BlakeOrth

Hi @BlakeOrth , please take a look at the PR #19388 when you get a chance. When the content looks good, I'll make another commit to add docs. Thank you.

feat: add list_files_cache table function for `datafusion-cli` (#19388)

feat: add list_files_cache table function for `datafusion-cli` (apach…

Navigation Menu

Repository navigation

Add a way to show the contents of the ListFilesCache in `datafusion-cli` #19055

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	let list_files_cache = config
	.list_files_cache
	.as_ref()
	.inspect(\|c\| {
	// the cache memory limit or ttl might have changed, ensure they are updated
	c.update_cache_limit(config.list_files_cache_limit);
	c.update_cache_ttl(config.list_files_cache_ttl);
	})

	let list: BoxStream<'a, Result<ObjectMeta>> = if self.is_collection() {
	list_with_cache(ctx, store, &self.prefix, prefix.as_ref()).await?

Add a way to show the contents of the ListFilesCache in datafusion-cli #19055

Description

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Activity

BlakeOrth commented on Dec 3, 2025

alamb commented on Dec 3, 2025

jizezhang commented on Dec 15, 2025

jizezhang commented on Dec 16, 2025

BlakeOrth commented on Dec 16, 2025

jizezhang commented on Dec 17, 2025

BlakeOrth commented on Dec 18, 2025

jizezhang commented on Dec 18, 2025

BlakeOrth commented on Dec 18, 2025

alamb commented on Dec 18, 2025

jizezhang commented on Dec 19, 2025

jizezhang commented on Dec 19, 2025

BlakeOrth commented on Dec 19, 2025

jizezhang commented on Dec 22, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Add a way to show the contents of the ListFilesCache in `datafusion-cli` #19055