I’m connecting Starburst Enterprise Platform (SEP) to an ADLS table.
If the ADLS table gets updated with new rows, should the change be reflected automatically in Starburst, or do we need to take any specific action?
My understanding is that since Starburst only maintains metadata, the new rows should be visible without any manual steps. However, we have faced an issue where we had to run a metadata flush command before the new data appeared.
Is this expected behavior by design, or could it be a configuration issue? Also, what is the recommended way to handle this in Starburst?
Here are some of the commands which i found in documentation? can i get more detail information when to use them. difference between file system cache and metadata cache.
system.flush_metadata_cache()Flush all Hive metadata caches.
system.flush_metadata_cache(schema_name => ..., table_name => ...)Flush Hive metadata caches entries connected with selected table. Procedure requires named parameters to be passed
system.flush_metadata_cache(schema_name => ..., table_name => ..., partition_columns => ARRAY[...], partition_values => ARRAY[...])Flush Hive metadata cache entries connected with selected partition. Procedure requires named parameters to be passed.
system.flush_filesystem_cache()Flushes filesystem cache of a specific table. By default, this function accepts a schema name and a table name as parameters. You can flush the filesystem cache for specific partitions. For example, the following system call flushes the filesystem cache of a specific partition of the MY_TABLE table.
Sure, metadata cache could affect you. As you can see in Metastores — Starburst Enterprise there are a number of properties with the word cache as part of them including these two.
But… depending on the table format (Hive, Iceberg, etc) things could be different since they do store all of their metadata differently. For example, Hive MD mostly knows the location where files should be and Iceberg MD points to the metadata.json file on the data lake itself. USUALLY, in both of those situations if you simply added more records I’d expect to see the results immediately.
But, again, like you I would expect the results to show up immediately afterwards. Can you verify in the explain plan output that your actually ADLS data source was read (should be at the very bottom of the output in the SOURCE stage output)?
Thanks for the inputs . i have to check if this setting Query result caching — Starburst Enterprise is causing the issue, You also mentioned that table formats also have might have some effect. do we have this detailed information in the documentation.
@lester I’m going through this forum post (Best practice for hive metastore settings)and observed that most common parameters are setting hive.metastore-cache-ttl to 5mins, and hive.metastore-refresh-interval to 1min. Can i do this setting as by default parameter as a best practice in my Starburst enterprise setup. does this setting have any impact making the queries more costlier as we are reducing the cache ttl?
Yep, those seem a reasonable change from the 20m default setting for the metastore cache TTL value. If you are suggesting that the value be changed in the core trino project (or as part of Starburst Enterprise) then I think the best course of action would be to open a support case.
From my experience years ago from working in the Hadoop ecosystem, the defaults were often considering the fact the software could be installed on a number of different infrastructure setups and for a variety of purposes, but personally 5m & 1m do seem reasonable to me as defaults that you could ask about being modified with a support case. Or for Trino, you could always work through the contribution process detailed at Trino | Development: Contribute to ultimately bring a PR to the project.
As for why having cache at all, it is a both a performance and scalability issue that are trying to be avoided. Most catalogs are backed by persistent stores such as actual databases. For low-concurrency scenarios this shouldn’t add a bunch of time to a query, but simply avoiding the need to make the necessary calls to the DB does save time which can surely be felt when we are talking about fast-running queries. Prolly not much a big deal for a query that takes 10+ seconds generally for a tiny bit more. Additionally, the concerns at high-concurrencies is that more and more folks are hitting that underlying DB and performance can start to degrade at some point based on how the metastore’s persistence back-end is setup.
@lester yes , As you mentioned those properties are the issue . After updating those catalog properties to have 0m then the files are getting reflected immediately.
Sounds like the mystery has been solved. That said, probably (at least worth considering) that some amount of metadata caching would make sense overall. Either way, I’m glad it makes sense.